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:
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,
where FILE_NAME like '%test.doc%'
FILE_NAME FILE_CONTENT_TYPE SIZE_BYTES
----------------------- ------------------ ----------
test.doc application/msword 5120
- 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.
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.
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
This holds information about the blocks within those forms forwhich the attachments feature can be enabled;
This holds information on the attachments that can be used in a particular form block.
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.
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(expiration_date ,'dd-mon-rr hh24:mi:ss'),
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
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