Pages

Friday, August 21, 2015

Attachments in Oracle Application

What
is attachment in oracle application?

The attachments feature in oracle
application enables users to link unstructured data, such as images,
word-processing documents, spreadsheets, or text to their application data. For
example, users can link images to items or video to operations as operation
instructions.

Where
to find an attachment?

There is an attachment icon in the
oracle application toolbar that indicates whether the Attachments feature is
enabled in a form block. When the button is dimmed, the Attachment feature is
not available. When the Attachment feature is enabled in a form block, the icon
becomes a solid paper clip. The icon switches to a paper clip holding a paper
when the Attachment feature is enabled in a form lock and the current record
has at least one attachment.

An OLE Object that requires other
OLE server applications to view, such as Microsoft Word or Microsoft Excel.

5] Web Page

A URL reference to a web page which
you can view with your web browser.

Tables
Involved:

For Importing Attachments in oracle
application one has to populate following tables.

1. FND_DOCUMENTS

2. FND_ATTACHED_DOCUMENTS

3. FND_DOCUMENTS_TL

4. FND_DOCUMENT_DATATYPES.

5. FND_DOCUMENT_CATEGORIES

6. FND_DOCUMENTS_LONG_TEXT (Long
text type attachment).

7. FND_DOCUMENTS_SHORT_TEXT (Short
text type attachment).

8. FND_DOCUMENTS_LONG_RAW

9. FND_LOBS (File type attachments).

FND_DOCUMENTS:

FND_DOCUMENTS stores
language-independent information about a document. For example, each row
contains a document identifier, a category identifier, the method of security
used for the document (SECURITY_TYPE, where 1=Organization,2=Set of Books,
3=Business unit,4=None), the period in which the document is active, and a flag
to indicate whether or not the document can be shared outside of the security
type (PUBLISH_FLAG).

Other specifications in this table
include: datatype (DATATYPE_ID, where 1=short text,2=long text, 3=image, 4=OLE
object), image type, and storage type (STORAGE_TYPE, where 1=stored in the
database, 2=stored in the file system).

The document can be referenced by
many application entities and changed only in the define document form
(USAGE_TYPE=S); it can be used as a fill-in-the-blanks document, where each
time you use a template, you make a copy of it (USAGE_TYPE=T); or it can be
used only one time (USAGE_TYPE=O).Images and OLE Objects cannot be used as
templates.

FND_ATTACHED_DOCUMENTS:

FND_ATTACHED_DOCUMENTS stores
information relating a document to an application entity. For example, a
record may link a document to a sales order or an item. Each row contains
foreign keys to FND_DOCUMENTS and FND_DOCUMENT_ENTITIES. There is also a flag
to indicate whether or not an attachment was created automatically.

FND_DOCUMENTS_TL:

FND_DOCUMENTS_TL stores translated
information about the documents in FND_DOCUMENTS. Each row includes the
document identifier, the language the row is translated to, the description of
the document, the file in which the image is stored, and an identifier
(MEDIA_ID) of the sub-table in which the document is saved
(FND_DOCUMENTS_SHORT_TEXT, FND_DOCUMENTS_LONG_TEXT, or FND_DOCUMENTS_LONG_RAW).

FND_DOCUMENT_DATATYPES:

FND_DOCUMENT_DATATYPES stores the
document datatypes that are supported. Initial values are: short text, long
text, image, and OLE Object (DATATYPE_ID=1, 2, 3, or 4). Customers can add
datatypes to handle documents stored outside of Oracle and use non-native Forms
applications to view/edit their documents. The table uses a “duplicate record”
model for handling multi-lingual needs. That is, for each category there will
be one record with the same CATEGORY_ID and CATEGORY_NAME for each language.

FND_DOCUMENT_CATEGORIES:

FND_DOCUMENT_CATEGORIES stores
information about the categories in which documents are classified. For
example, documents may be considered “Bill of Material Comments”, “WIP Job
Comments”, etc. Document categories are used to provide a measure of security on
documents. Each form that enables the attachment feature lists which
categories of documents can be viewed in the form. This table uses a “duplicate
record” model for handling multi-lingual needs.

FND_DOCUMENTS_LONG_RAW stores images
and OLE Objects, such as Word Documents and Excel spreadsheets, in the
database.

FND_DOCUMENT_ENTITIES:

FND_DOCUMENT_ENTITIES lists each
entity to which attachments can be linked. For example, attachments can be
linked to Items, Sales Orders, etc. Since the table uses a “duplicate record”
model for handling multi-lingual needs, for each document entity there will be
one record with the same DOCUMENT_ENTITY_ID and DATA_OBJECT_CODE for each
language.

Queries:

To find all Long Text attachments:

SELECT

FAD.SEQ_NUM "Seq Number",

FDAT.USER_NAME "Data Type",

FDCT.USER_NAME "Category User Name",

FAD.ATTACHED_DOCUMENT_ID "Attached Document
Id",

FDET.USER_ENTITY_NAME "User Entity",

FD.DOCUMENT_ID "Document Id",

FAD.ENTITY_NAME "Entity Name",

FD.MEDIA_ID "Media Id",

FD.URL "Url",

FDT.TITLE "Title",

FDLT.LONG_TEXT "Attachment Text"

FROM

FND_DOCUMENT_DATATYPES FDAT,

FND_DOCUMENT_ENTITIES_TL FDET,

FND_DOCUMENTS_TL FDT,

FND_DOCUMENTS FD,

FND_DOCUMENT_CATEGORIES_TL
FDCT,

FND_ATTACHED_DOCUMENTS FAD,

FND_DOCUMENTS_LONG_TEXT FDLT

WHERE

FD.DOCUMENT_ID =
FAD.DOCUMENT_ID

AND FDT.DOCUMENT_ID =
FD.DOCUMENT_ID

AND FDCT.CATEGORY_ID =
FD.CATEGORY_ID

AND FD.DATATYPE_ID =
FDAT.DATATYPE_ID

AND FAD.ENTITY_NAME =
FDET.DATA_OBJECT_CODE

AND FDLT.MEDIA_ID =
FD.MEDIA_ID

AND FDAT.NAME='LONG_TEXT';

2] To find all Short Text
attachments:

SELECT

FAD.SEQ_NUM "Seq Number",

FDAT.USER_NAME "Data Type",

FDCT.USER_NAME "Category User Name",

FAD.ATTACHED_DOCUMENT_ID "Attached Document
Id",

FDET.USER_ENTITY_NAME "User Entity",

FD.DOCUMENT_ID "Document Id",

FAD.ENTITY_NAME "Entity Name",

FD.MEDIA_ID "Media Id",

FD.URL "Url",

FDT.TITLE "Title",

FDST.SHORT_TEXT "Attachment Text"

FROM

FND_DOCUMENT_DATATYPES FDAT,

FND_DOCUMENT_ENTITIES_TL FDET,

FND_DOCUMENTS_TL FDT,

FND_DOCUMENTS FD,

FND_DOCUMENT_CATEGORIES_TL FDCT,

FND_ATTACHED_DOCUMENTS FAD,

FND_DOCUMENTS_SHORT_TEXT FDST

WHERE

FD.DOCUMENT_ID =
FAD.DOCUMENT_ID

AND FDT.DOCUMENT_ID =
FD.DOCUMENT_ID

AND FDCT.CATEGORY_ID =
FD.CATEGORY_ID

AND FD.DATATYPE_ID =
FDAT.DATATYPE_ID

AND FAD.ENTITY_NAME =
FDET.DATA_OBJECT_CODE

AND FDST.MEDIA_ID =
FD.MEDIA_ID

AND FDAT.NAME='SHORT_TEXT';

Attachment
upload through API:

Attachments can also be uploaded
through an oracle provided API called FND_ATTACHED_DOCUMENTS_PKG.

It consist of three procedures

1) Insert Row

2) Update Row

3) Lock Row

Names of these procedures are self
explanatory. insert row is used to insert a new row for attachment data, update
row is used to update existing row for a particular row and Lock Row is used to
lock a existing row for further modification.