ATTACHMENT FUNCTIONALITY & SCRIPTS
why do i need a attachment functionality?The attachments feature enables users to link unstructured data, such as images, wordprocessing documents, spreadsheets, or text to their application data. For example,users can link images to items or video to operations as operation instructions.
Can i enable it to any form i want?
For most of the standar you can do that..i hope custom form done in standard way can also have this feauture enabled
where should i start???
Lets Define the terms first
1.Entity:An entity is an object within Oracle Applications data, such as an item, an order, or anorder line. The attachments feature must be enabled for an entity before users can link attachments to the entity.
2.Category:A document category is a label that users apply to individual attachments anddocuments. Document categories provide security by restricting the documents that canbe viewed or added via a specific form or form function.
In the below example i am tryign to enable the attachment funcionality for the USer form..I want to upload the approval copy for creating a user or attaching new responsibility
what are the steps to do that??
Step1:First Define a Entity for which you are creating the attachments.In my case it is users.
define the base table and entityid and others things
Step2:Define a category(optional).if the attachments belongs logically to different group.
In this example i am addding to the miscellenous category.
Make the assignments of the Function once you define it.
step3:Find the Form Name,Function Name and block name to which the attachment feauture need to be enabled.
Step4:Define the Attachment Function .Seelct the type either you want the attachmetn feature at form level or Function level
Press the Blocks Button. and Enter the Block name you want to enable the attachment feauture.
Press the Entities Button
Enter the entity and other settings and see what fuctionalties are required for this form.like query,insert,update and delete allowed can be done or not from this form
Enter the primary Key so that it show the document based on the primary key.
If it a common document like across users then primary key should not be given.
Press the categories button to select the caegories
Press the Assignments Button in document categories and attach the Function.
Now the attache button is enabled..press the button and upload the document..
uploading document is self explainable
for developing some complex one..read tthe application developer for more info..
more or less it depends on the entity definition and the primary
***********************************************
1. Script to attach a document using API
http://shareoracleapps.blogspot.com/2010/03/how-to-attach-documents-from-backend.html
Attachment using backend scripts:
Declare
v_category_id NUMBER;
v_attached_doc_id NUMBER;
v_invoice_id NUMBER;
v_invoice_image_url VARCHAR2(500) := 'http://shareoracleapps.blogspot.com';
v_function_name VARCHAR2(50) := 'APXINWKB';
v_category_name VARCHAR2(100) := 'FromSupplier';
v_description VARCHAR2(300) := 'Test script for attaching scanned image url to AP invoice';
v_entity_name VARCHAR2(100) := 'AP_INVOICES'
v_file_name VARCHAR2(100) := NULL;
v_user_id NUMBER := 1234;
TYPE result_set_type IS REF CURSOR;
v_result_set_curr result_set_type;
--Here for example we are using "FromSupplier" as a category
--and AP_INVOICES_ALL.invoice_id as primary key value
CURSOR cur_cat_id
IS
SELECT fdc.category_id
FROM fnd_document_categories fdc
WHERE fdc.name = v_category_name;
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
-- Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here
END set_context;
BEGIN
-- Setting the context ----
v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
IF v_context = 'F'
THEN
DBMS_OUTPUT.PUT_LINE('Error while setting the context');
END IF;
DBMS_OUTPUT.PUT_LINE('2');
--- context done ------------
OPEN cur_cat_id;
FETCH cur_cat_id INTO v_category_id;
CLOSE cur_cat_id;
-- Invoke the fnd_webattach api for attaching the URL to the invoice
fnd_webattch.add_attachment ( seq_num => 100
,category_id => v_category_id
,document_description => v_description
,datatype_id => 5
,text => NULL
,file_name => v_file_name
,url => v_invoice_image_url
,function_name => v_function_name
,entity_name => v_entity_name
,pk1_value => v_invoice_id
,pk2_value => NULL
,pk3_value => NULL
,pk4_value => NULL
,pk5_value => NULL
,media_id => x_file_id
,user_id => v_user_id
,usage_type => 'O'
);
SELECT count(fad.attached_document_id)
INTO v_attached_doc_id
FROM fnd_attached_documents fad
WHERE fad.pk1_value = v_invoice_id;
IF v_attached_doc_id > 0
DBMS_OUTPUT.PUT_LINE('Attached sucessfully');
THEN
DBMS_OUTPUT.PUT_LINE('Failed to Link the Attacment.');
END IF; --IF v_attached_doc_id > 0
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occured : '||SQLERRM);
END attach_invoice;
****************************************************
2. Script to Delete a Attachment using API
declare
l_entity_name varchar2(20):= 'PO_LINES';
l_pk1_value varchar2 (20) := '200487';
l_delete_document_flag varchar2 (1):= 'Y';
begin
fnd_global.apps_initialize ( user_id => &v_user_id ,resp_id => &v_resp_id ,resp_appl_id => &v_resp_appl_id);
fnd_attached_documents2_pkg.delete_attachments
( X_entity_name => l_entity_name
, X_pk1_value => l_pk1_value
, X_delete_document_flag => l_delete_document_flag);
end;
/
commit;
**********************************************************
3. Queries
As we know, we can attach documents in different formats like WEBPAGE, FILE, LONG_TEXT and SHORT_TEXT.Hence, i had given queries to find the attached documents FOR EACH format separately.
-- WEBPAGE ATTACHMENT
SELECT DISTINCT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
D.STORAGE_TYPE ,
D.FILE_NAME ,
D.IMAGE_TYPE ,
D.USAGE_TYPE ,
AD.AUTOMATICALLY_ADDED_FLAG ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
D.DATATYPE_ID ,
DAT.NAME ,
D.DOCUMENT_ID ,
D.START_DATE_ACTIVE ,
D.END_DATE_ACTIVE ,
D.SECURITY_TYPE ,
D.SECURITY_ID ,
D.PUBLISH_FLAG ,
AD.CREATED_BY ,
DET.USER_ENTITY_PROMPT ,
AD.ENTITY_NAME ,
AD.COLUMN1 ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.CATEGORY_ID ,
D.URL ,
DT.TITLE
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DAT.name = 'WEB_PAGE';
-- LONG_TEXT ATTACHMENT
SELECT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
DLT.LONG_TEXT
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_DOCUMENTS_LONG_TEXT DLT
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID = D.MEDIA_ID
AND DAT.NAME = 'LONG_TEXT';
-- SHORT_TEXT ATTACHMENT
SELECT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
DST.SHORT_TEXT
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_DOCUMENTS_SHORT_TEXT DST
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DsT.MEDIA_ID = D.MEDIA_ID
AND DAT.NAME = 'SHORT_TEXT';
-- FILE ATTACHMENT
SELECT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
DBMS_LOB.SUBSTR(L.file_data,1,10) file_data
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_LOBS L
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND L.FILE_ID = D.MEDIA_ID
AND DAT.name = 'FILE';
-- This query will help you to find the functions using attachments
SELECT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
AF.FUNCTION_NAME ,
AF.FUNCTION_TYPE
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_DOC_CATEGORY_USAGES DCU ,
FND_ATTACHMENT_FUNCTIONS AF ,
FND_ATTACHED_DOCUMENTS AD
WHERE 1 = 1
AND D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND DCU.CATEGORY_ID = D.CATEGORY_ID
AND DCU.ATTACHMENT_FUNCTION_ID = AF.ATTACHMENT_FUNCTION_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DCU.ENABLED_FLAG = 'Y';
Can i enable it to any form i want?
For most of the standar you can do that..i hope custom form done in standard way can also have this feauture enabled
where should i start???
Lets Define the terms first
1.Entity:An entity is an object within Oracle Applications data, such as an item, an order, or anorder line. The attachments feature must be enabled for an entity before users can link attachments to the entity.
2.Category:A document category is a label that users apply to individual attachments anddocuments. Document categories provide security by restricting the documents that canbe viewed or added via a specific form or form function.
In the below example i am tryign to enable the attachment funcionality for the USer form..I want to upload the approval copy for creating a user or attaching new responsibility
what are the steps to do that??
Step1:First Define a Entity for which you are creating the attachments.In my case it is users.
define the base table and entityid and others things
Step2:Define a category(optional).if the attachments belongs logically to different group.
In this example i am addding to the miscellenous category.
Make the assignments of the Function once you define it.
step3:Find the Form Name,Function Name and block name to which the attachment feauture need to be enabled.
Step4:Define the Attachment Function .Seelct the type either you want the attachmetn feature at form level or Function level
Press the Blocks Button. and Enter the Block name you want to enable the attachment feauture.
Press the Entities Button
Enter the entity and other settings and see what fuctionalties are required for this form.like query,insert,update and delete allowed can be done or not from this form
Enter the primary Key so that it show the document based on the primary key.
If it a common document like across users then primary key should not be given.
Press the categories button to select the caegories
Press the Assignments Button in document categories and attach the Function.
Now the attache button is enabled..press the button and upload the document..
uploading document is self explainable
for developing some complex one..read tthe application developer for more info..
more or less it depends on the entity definition and the primary
***********************************************
1. Script to attach a document using API
http://shareoracleapps.blogspot.com/2010/03/how-to-attach-documents-from-backend.html
Attachment using backend scripts:
Declare
v_category_id NUMBER;
v_attached_doc_id NUMBER;
v_invoice_id NUMBER;
v_invoice_image_url VARCHAR2(500) := 'http://shareoracleapps.blogspot.com';
v_function_name VARCHAR2(50) := 'APXINWKB';
v_category_name VARCHAR2(100) := 'FromSupplier';
v_description VARCHAR2(300) := 'Test script for attaching scanned image url to AP invoice';
v_entity_name VARCHAR2(100) := 'AP_INVOICES'
v_file_name VARCHAR2(100) := NULL;
v_user_id NUMBER := 1234;
TYPE result_set_type IS REF CURSOR;
v_result_set_curr result_set_type;
--Here for example we are using "FromSupplier" as a category
--and AP_INVOICES_ALL.invoice_id as primary key value
CURSOR cur_cat_id
IS
SELECT fdc.category_id
FROM fnd_document_categories fdc
WHERE fdc.name = v_category_name;
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
-- Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here
END set_context;
BEGIN
-- Setting the context ----
v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
IF v_context = 'F'
THEN
DBMS_OUTPUT.PUT_LINE('Error while setting the context');
END IF;
DBMS_OUTPUT.PUT_LINE('2');
--- context done ------------
OPEN cur_cat_id;
FETCH cur_cat_id INTO v_category_id;
CLOSE cur_cat_id;
-- Invoke the fnd_webattach api for attaching the URL to the invoice
fnd_webattch.add_attachment ( seq_num => 100
,category_id => v_category_id
,document_description => v_description
,datatype_id => 5
,text => NULL
,file_name => v_file_name
,url => v_invoice_image_url
,function_name => v_function_name
,entity_name => v_entity_name
,pk1_value => v_invoice_id
,pk2_value => NULL
,pk3_value => NULL
,pk4_value => NULL
,pk5_value => NULL
,media_id => x_file_id
,user_id => v_user_id
,usage_type => 'O'
);
SELECT count(fad.attached_document_id)
INTO v_attached_doc_id
FROM fnd_attached_documents fad
WHERE fad.pk1_value = v_invoice_id;
IF v_attached_doc_id > 0
DBMS_OUTPUT.PUT_LINE('Attached sucessfully');
THEN
DBMS_OUTPUT.PUT_LINE('Failed to Link the Attacment.');
END IF; --IF v_attached_doc_id > 0
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occured : '||SQLERRM);
END attach_invoice;
****************************************************
2. Script to Delete a Attachment using API
declare
l_entity_name varchar2(20):= 'PO_LINES';
l_pk1_value varchar2 (20) := '200487';
l_delete_document_flag varchar2 (1):= 'Y';
begin
fnd_global.apps_initialize ( user_id => &v_user_id ,resp_id => &v_resp_id ,resp_appl_id => &v_resp_appl_id);
fnd_attached_documents2_pkg.delete_attachments
( X_entity_name => l_entity_name
, X_pk1_value => l_pk1_value
, X_delete_document_flag => l_delete_document_flag);
end;
/
commit;
**********************************************************
3. Queries
As we know, we can attach documents in different formats like WEBPAGE, FILE, LONG_TEXT and SHORT_TEXT.Hence, i had given queries to find the attached documents FOR EACH format separately.
-- WEBPAGE ATTACHMENT
SELECT DISTINCT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
D.STORAGE_TYPE ,
D.FILE_NAME ,
D.IMAGE_TYPE ,
D.USAGE_TYPE ,
AD.AUTOMATICALLY_ADDED_FLAG ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
D.DATATYPE_ID ,
DAT.NAME ,
D.DOCUMENT_ID ,
D.START_DATE_ACTIVE ,
D.END_DATE_ACTIVE ,
D.SECURITY_TYPE ,
D.SECURITY_ID ,
D.PUBLISH_FLAG ,
AD.CREATED_BY ,
DET.USER_ENTITY_PROMPT ,
AD.ENTITY_NAME ,
AD.COLUMN1 ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.CATEGORY_ID ,
D.URL ,
DT.TITLE
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DAT.name = 'WEB_PAGE';
-- LONG_TEXT ATTACHMENT
SELECT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
DLT.LONG_TEXT
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_DOCUMENTS_LONG_TEXT DLT
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DLT.MEDIA_ID = D.MEDIA_ID
AND DAT.NAME = 'LONG_TEXT';
-- SHORT_TEXT ATTACHMENT
SELECT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
DST.SHORT_TEXT
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_DOCUMENTS_SHORT_TEXT DST
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DsT.MEDIA_ID = D.MEDIA_ID
AND DAT.NAME = 'SHORT_TEXT';
-- FILE ATTACHMENT
SELECT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
DBMS_LOB.SUBSTR(L.file_data,1,10) file_data
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_ATTACHED_DOCUMENTS AD ,
FND_LOBS L
WHERE D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND L.FILE_ID = D.MEDIA_ID
AND DAT.name = 'FILE';
-- This query will help you to find the functions using attachments
SELECT
AD.SEQ_NUM ,
DCT.USER_NAME ,
DAT.USER_NAME ,
AD.ATTACHED_DOCUMENT_ID ,
DET.USER_ENTITY_NAME ,
DAT.NAME ,
D.DOCUMENT_ID ,
AD.ENTITY_NAME ,
AD.PK1_VALUE ,
D.MEDIA_ID ,
D.URL ,
DT.TITLE ,
AF.FUNCTION_NAME ,
AF.FUNCTION_TYPE
FROM FND_DOCUMENT_DATATYPES DAT,
FND_DOCUMENT_ENTITIES_TL DET ,
FND_DOCUMENTS_TL DT ,
FND_DOCUMENTS D ,
FND_DOCUMENT_CATEGORIES_TL DCT ,
FND_DOC_CATEGORY_USAGES DCU ,
FND_ATTACHMENT_FUNCTIONS AF ,
FND_ATTACHED_DOCUMENTS AD
WHERE 1 = 1
AND D.DOCUMENT_ID = AD.DOCUMENT_ID
AND DT.DOCUMENT_ID = D.DOCUMENT_ID
AND DCT.CATEGORY_ID = D.CATEGORY_ID
AND DCU.CATEGORY_ID = D.CATEGORY_ID
AND DCU.ATTACHMENT_FUNCTION_ID = AF.ATTACHMENT_FUNCTION_ID
AND D.DATATYPE_ID = DAT.DATATYPE_ID
AND AD.ENTITY_NAME = DET.DATA_OBJECT_CODE
AND DCU.ENABLED_FLAG = 'Y';
References:
http://vasikarjayas.blogspot.com/2011/05/attachment-functionality-scripts.html
http://www.shareoracleapps.com/2010/06/fnd-attachments-in-oracle-apps-r12.html
