Attachments in Oracle Applications R12

What is attachments in Oracle Applications

• The attachments feature 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.
• Attachment information can flow through your entire application. For example, if you enable attachments for a part number, where users would attach images of the part, you can then enable attachments for all your other forms that refer to your part number. Users would then be able to see the image of the part wherever that part number occurs.
• You can provide security to limit which attachments users can see from particular forms by assigning document categories to your form functions. Users then assign individual attachments to particular categories.
• You can add the attachments feature to your application forms and functions without modifying form code, so long as your forms are built using Oracle E-Business Suite standards (starting with the Oracle E-Business Suite TEMPLATE form).

Table Information for Attachments

All of the tables which hold attachments-related data are owned by the FND schema.

There are a number of tables which hold information determining which area of
the application can use attachments:

FND_LOBS

Attachments are stored in FND_LOBS table, this table has a BLOB column FILE_DATA
where the attachment is stored.

How it is stored:

Oracle Applications does not use any compression technique and a file of 50K will
occupy 50K in the database. The size of the file in the database can be obtained
using DBMS_LOB package, the following example shows the details of a 5K file that
was attached to a requisition:

select FILE_NAME, FILE_CONTENT_TYPE,
DBMS_LOB.GETLENGTH(file_data) SIZE_BYTES
from FND_LOBS
where FILE_NAME like '%test.doc%'
FILE_NAME FILE_CONTENT_TYPE SIZE_BYTES
----------------------- ------------------ ----------
test.doc application/msword 5120

FND_DOCUMENTS

• This holds language-independent information about the document including CATEGORY_ID and DATATYPE_ID. It doesn’t hold the actual document.
• DATATYPE_ID is taken from FND_DOCUMENT_DATATYPES. This table holds a row for each supported datatype eg Short Text, File, etc.
• CATEGORY_ID is taken from FND_DOCUMENT_CATEGORIES. This table holds a row for each valid attachment category in an application. eg Comments and Resume are all valid for APPLICATION_ID = 800 and Miscellaneous is valid for all applications (APPLICATION_ID = 0). As each specific entity is seeded with valid categories, there is another table, FND_DOC_CATEGORY_USAGES, which holds a row for each CATEGORY_ID valid for an ATTACHMENT_FUNCTION_ID. It determines which categories can be used for attachments on a particular form.

FND_DOCUMENTS_TL

The translation table FND_DOCUMENTS_TL stores information about the documents in FND_DOCUMENTS and links to this table by the column DOCUMENT_ID. Rows in
FND_DOCUMENTS_TL include a description of the document.

FND_ATTACHMENT_FUNCTIONS

This table holds information about the forms for which the attachments function can be enabled

For example FND_ATTACHMENT_FUNCTIONS contains a row for FUNCTION_NAME =
‘PERWSHRG’ ie the combined Person & Assignment form.
FND_ATTACHMENT_BLOCKS links to FND_ATTACHMENT_FUNCTIONS via the column
ATTACHMENT_FUNCTION_ID and contains a row for BLOCK_NAME = ‘PERSON’.
FND_ATTACHMENT_BLK_ENTITIES holds a row for the ‘PERSON’ block with
DATA_OBJECT_CODE = ‘PER_PEOPLE_F’ and PK1_FIELD = ‘PERSON.PERSON_ID’ and
defines whether an attachment to this block can be queried, inserted, updated
and deleted.

FND_ATTACHMENT_BLOCK

This holds information about the blocks within those forms forwhich the attachments feature can be enabled;

FND_ATTACHMENT_BLK_ENTITIES

This holds information on the attachments that can be used in a particular form block.

FND_ATTACHED_DOCUMENTS

FND_ATTACHED_DOCUMENTS stores information relating a document to an entity and
in this example would hold ENTITY_NAME = ‘PER_PEOPLE_F’ and PK1_VALUE = 44.
PK1_VALUE is the value for FND_ATTACHMENT_BLK_ENTITIES.PK1_FIELD (ie
‘PERSON.PERSON_ID’) and so this attachment is for the person with
PER_PEOPLE_F.PERSON_ID = 44. The DOCUMENT_ID of the stored document is also
held on this table.

FND_ATTACHED_DOCS_FORM_VL
FND_DM_FOLDERATTACHMENTEXT

Queries Related to Attachments

Query to Find document data types

SQL> select USER_NAME from fnd_document_datatypes ;
USER_NAME
---------
Short Text
Long Text
Image
OLE Object
Web Page
File
Document Reference
Oracle File
Oracle Files Folder/Workspace

Query to find file attachment

select fad.entity_name
,fad.document_id
,fad.pk1_value
,fad.pk2_value
,fd.datatype_id
,(fad.entity_name || '' || fad.document_id ||'' || fl.file_name) file_name
,fl.file_data
from fnd_attached_documents fad
,fnd_documents fd
,fnd_lobs fl
where fad.document_id = fd.document_id
and fd.media_id = fl.file_id
and fad.entity_name = '&1'
and fad.pk1_value, fad.pk2_value;

Suppose we want an Iexpense Line attachment

select report_line_id
from apps.ap_expense_report_lines_all l
where report_header_id=:P_expense_report_number; -- expense report number

SELECT fl.*
FROM apps.fnd_documents_tl fdtl,
apps.fnd_documents fd,
apps.fnd_attached_documents fad,
apps.fnd_lobs fl
WHERE fdtl.document_id = fd.document_id
AND fd.document_id = fad.document_id
AND fad.entity_name = 'OIE_LINE_ATTACHMENTS'
AND fad.pk1_value = ':p_report_line_id' -- line_id from first query
AND fl.file_id = fd.media_id
and fdtl.language='US';

How To Determine The Size of a File In Table FND_LOBS?

select file_name, file_id, 
       to_char(upload_date,'dd-mon-rr hh24:mi:ss'),
       to_char(expiration_date ,'dd-mon-rr hh24:mi:ss'),
       file_content_type, 
       dbms_lob.getlength(file_data) size_byte
from   applsys.fnd_lobs
where  file_id= <your file id>;

How to see contents of the LOB?

-- Read from fnd_lobs a given file_id
set serveroutput on size 1000000;
declare
  my_lob BLOB;
  Buffer      RAW(255);
  Amount      BINARY_INTEGER := 255;
  Position    INTEGER := 1;
begin
   select file_data into my_lob
   from fnd_lobs
   where file_id = &enter_file_id;
 
   dbms_lob.open(my_lob, dbms_lob.lob_readonly);
   DBMS_OUTPUT.PUT_LINE('Start of data');

   loop
      DBMS_LOB.READ(my_lob, Amount, Position, Buffer);
      /* Process the buffer: */
      DBMS_OUTPUT.PUT_LINE(utl_raw.cast_to_varchar2(Buffer));
      Position := Position + Amount;
   end loop;

   dbms_lob.close(my_lob);
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      DBMS_OUTPUT.PUT_LINE('End of data');
end;
/

Hope you like this information on Attachments in Oracle Applications R12. Please do provide the feedback

Related Articles
FND_LOBS : FND_LOBS stores information about all LOBs managed by the Generic File Manager (GFM).it is a unique table that is used for both EBS temporary data and permanent user data (i.e. Attachments).
Oracle EBS Auditing : How to setup Oracle EBS Auditing, how to add tables in audit group, how to select column for auditing, audit trail update concurrent request
Create EBS user from backend : This post is about how to Create EBS user from backend, how to assign application responsibility to the user created