Attachments in OAF Pages: A Simple Case study

In Oracle Apps, we enable the attachments at logical entity level. This entity is different that entity in ER diagram or Entity Objects. An entity is an object within Oracle E-Business Suite data, such as an item, an order, or an order line. In the context of attachments, an entity can be considered either a base entity or a related entity.

A base entity is the main entity of the region. A related entity is an entity that is usually related to the region by a foreign-key relationship.

• Attachments are linked with the VO’s primary key (could be combination of columns).

When an attachment is stored, the values of these primary keys and the Entity ID are stored so the attachments can be uniquely retrieved. (We’ll discuss in details about the storage of this information a little later).

Attachment Styles in a Region

There are four attachment styles that you can render in a region:

• Display a View List Link and Add Button in a Single Row Region

      

• Display an Attachments Table on the Page of a Single Row Region

• Display In-line Links of Attachments in a Single Row Region

      

• Display an Attachments Column in a Multi-Row Table Region

           

Add Attachment Page:

On Clicking Add Attachment, users are navigated to this page.

It is clearly displaying the three types of attachments supported:

• URL
• File
• Text

Attention: All Text attachments are added as "Short Text" type, with a maximum length of 4000 bytes. If you need to add an attachment that is longer than 4000 bytes, put the text into a text file and add the attachment as a File.

Steps to add Attachments:

1. Add the UI widget for attachments. Following are the widgets:

• attachmentTable - for Attachments table
• attachmentLink - for View List link and Add button
• MessageInlineAttachment - for inline attachment links
• attachmentImage - For Attachment column in Table and Advance Table

2. Specify View Instance property

3. Select the default entityMap that is created under the attachment region item, set the Entity value to some unique value which will be used to identify all attachments.

4. Right Click on Entity Map and Add primary keys and specify the primary keys used to identify the attachment uniquely.

Some key SQL Queries:

Query 1: List of All Attachments

--------------------------------------------

select * from fnd_attached_documents where creation_date > sysdate - 1/24 ;/

// List of all Attachments attached in last 1 hr.

Query 2: List of All Attachments

--------------------------------------------

select * from fnd_attached_documents where entity_name = 'XXSL_GROUP' ;/

// List of all Attachments with entity_name = 'XXSL_GROUP'

Query 3: List of All Attachments

--------------------------------------------

select attached_document_id, -- Primary Key

document_id, -- foreign key for FND_DOCUMENTS

entity_name, -- Name of unique entity entered in OAF page's Attachment component

PK1_value, -- primary key attribute specified in OAF page's Attachment component.

-- There are 5 Primary Key columns here to store composite primary keys.

seq_num, -- sequence number of the attachment added.

category_id -- Category of the attachment, foreign key to FND_DOCUMENT_CATEGORIES

-- In case you are not able to see an attachment in the UI,

-- check the "Show All" property of the Entity Map

from fnd_attached_documents

WHERE ENTITY_NAME = '<your entity name>' and pk1_value = '<primary key of your VO>'; /

Query 4: Details of the document attached

---------------------------------------------------------

SELECT * FROM FND_DOCUMENTS where document_id in (7922696, 7922698, 7922700, 7922702); /

SELECT document_id, -- Primary Key

datatype_id, -- foreign key to FND_DOCUMENT_DATATYPES, tells about the type of attachment

category_id, -- Category of the attachment, foreign key to FND_DOCUMENT_CATEGORIES

url, -- URL field is populated in case of URL Type attachment

File_name, -- File name field is populated in case of a file type attachment

media_id -- Foreign key to fnd_lobs for datatype_id = 6

--

FROM FND_DOCUMENTS -- Attachment Title is stored in FND_DOCUMENTS_TL

where document_id in (select document_id from fnd_attached_documents where ENTITY_NAME = '<your entity name>' and pk1_value = '<primary key of your VO>');

/

Query 5: Types of Attachments

-------------------------------------------

select * from FND_DOCUMENT_DATATYPES;/

Short Text

Long Text

Image

OLE Object

Web Page

File

Document Reference

Oracle Files Folder/Workspace

Oracle File

select * from xxsl_lms_group_headers where group_id = 1722 ;/

Query 6: Types of File Attached

---------------------------------------------

--Record will be in FND_LOB only for datatype_id = 6

select * from fnd_lobs

where file_id in

(select media_id

from FND_DOCUMENTS

where document_id in

(select document_id from fnd_attached_documents where ENTITY_NAME = '<your entity name>' and pk1_value = '<primary key of your VO>' and datatype_id = 6)

); /

Query 7: for "Short Text" type:

------------------------------------------

-- Record will exist in FND_DOCUMENTS_SHORT_TEXT if datatype_id is 1

select * from FND_DOCUMENTS_SHORT_TEXT

where media_id in

(select media_id

from FND_DOCUMENTS

where document_id in

(select document_id from fnd_attached_documents where ENTITY_NAME = '<your entity name>' and pk1_value = '<primary key of your VO>' and datatype_id = 1)

); /

API Support:
For Api support refer to following packages - 

1. FND_DOCUMENTS_PKG,

2. FND_ATTACHED_DOCUMENTS_PKG

3. fnd_attached_documents2_pkg.delete_attachments (to delete attachments)

Followings are not covered in this post:

• Security
• Virus Scanning
• Multiple entity support
• Attachments to be visible on Forms as well as OAF. (FND_ATTACHMENT_FUNCTIONS)
• Document Catelog