Noticed a few posts on Oracle forums about FND Attached Documents i.e. Oracle Applications/eBusiness Suite Attachments. Thought I'd post some
unsupported code here just for reference, and a query for getting the attachments (text/documents) themselves.
How to attach a "Web Page" URL attachment to a Payables Invoice via FND Document Attachment Private API.
To use this code there are a few things that you'll need to change:
- In the fnd_global call change the variables to your required user_id, resp_id, resp_appl_id, or comment the call if not needed see my post if you're unsure of this
- The l_pk1_value which is ap_invoices_all.invoice_id
- The l_filename and l_description variables
- Possibly l_category_id: create an real example, check out examine and find the category_id you need to use.
- Remember to commit!
- Updated 6-Mar-2012: This code has been tested against 11.5.10.2, 12.0 and 12.1 (code differences noted in comments - only queries tested for R12.0/12.1), if you have a different version this may have changed. Note this is a customization via a private API so is unsupported.
declare
l_rowid rowid;
l_attached_document_id number;
l_document_id number;
l_media_id number;
l_category_id number := 291; -- Invoice Internal
l_pk1_value fnd_attached_documents.pk1_value%TYPE := '63227';
l_description fnd_documents_tl.description%TYPE := 'Gareth Document Attach Demo';
l_filename fnd_documents_tl.file_name%TYPE := 'http://www.oracle.com/index.html';
l_seq_num number;
begin
-- Setup applications session
-- User = OPERATIONS
-- Resp = Payables, Vision Operations
-- App = Payables
fnd_global.apps_initialize(1318,50554,200);
select FND_DOCUMENTS_S.nextval
into l_document_id
from dual;
select FND_ATTACHED_DOCUMENTS_S.nextval
into l_attached_document_id
from dual;
select nvl(max(seq_num),0) + 10
into l_seq_num
from fnd_attached_documents
where pk1_value = l_pk1_value
and entity_name = 'AP_INVOICES';
fnd_documents_pkg.insert_row
( X_ROWID => l_rowid
, X_DOCUMENT_ID => l_document_id
, X_CREATION_DATE => sysdate
, X_CREATED_BY => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE => sysdate
, X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
, X_DATATYPE_ID => 5 -- Web Page
, X_CATEGORY_ID => l_category_id
, X_SECURITY_TYPE => 2
, X_PUBLISH_FLAG => 'Y'
, X_USAGE_TYPE => 'O'
, X_LANGUAGE => 'US'
, X_DESCRIPTION => l_description
, X_FILE_NAME => l_filename
, X_MEDIA_ID => l_media_id
);
fnd_documents_pkg.insert_tl_row
( X_DOCUMENT_ID => l_document_id
, X_CREATION_DATE => sysdate
, X_CREATED_BY => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE => sysdate
, X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
, X_LANGUAGE => 'US'
, X_DESCRIPTION => l_description
, X_FILE_NAME => l_filename
, X_MEDIA_ID => l_media_id
);
fnd_attached_documents_pkg.insert_row
( X_ROWID => l_rowid
, X_ATTACHED_DOCUMENT_ID => l_attached_document_id
, X_DOCUMENT_ID => l_document_id
, X_CREATION_DATE => sysdate
, X_CREATED_BY => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_DATE => sysdate
, X_LAST_UPDATED_BY => fnd_profile.value('USER_ID')
, X_LAST_UPDATE_LOGIN => fnd_profile.value('LOGIN_ID')
, X_SEQ_NUM => l_seq_num
, X_ENTITY_NAME => 'AP_INVOICES'
, X_COLUMN1 => null
, X_PK1_VALUE => l_pk1_value
, X_PK2_VALUE => null
, X_PK3_VALUE => null
, X_PK4_VALUE => null
, X_PK5_VALUE => null
, X_AUTOMATICALLY_ADDED_FLAG => 'N'
, X_DATATYPE_ID => 5
, X_CATEGORY_ID => l_category_id
, X_SECURITY_TYPE => 2
, X_PUBLISH_FLAG => 'Y'
, X_LANGUAGE => 'US'
, X_DESCRIPTION => l_description
, X_FILE_NAME => l_filename
, X_MEDIA_ID => l_media_id
);
end;
/
Where to the actual attachments/documents get stored for attachments?
Here are some queries to get back the Payables Invoice attachments of category "Invoice Internal" for a given invoice.
Attachments of type Web Page (URL or Filename)
select fad_f.seq_num
, fdt_f.file_name media_data_text
from fnd_attached_documents fad_f
, fnd_documents fd_f
, fnd_documents_tl fdt_f
, fnd_document_categories_tl fdct_f
where 1 = 1
and fad_f.document_id = fd_f.document_id
and fad_f.document_id = fdt_f.document_id
and fdct_f.category_id = fd_f.category_id
and fdct_f.user_name = 'Invoice Internal'
and fd_f.datatype_id = 5
and fad_f.entity_name = 'AP_INVOICES'
and fad_f.pk1_value = '63227';
Attachments of type "Long Text"
select fad_f.seq_num
, fdlt_f.media_id media_id
, fdlt_f.long_text media_data_text
from fnd_attached_documents fad_f
, fnd_documents fd_f
, fnd_documents_tl fdt_f
, fnd_document_categories_tl fdct_f
, fnd_documents_long_text fdlt_f
where 1 = 1
and fad_f.document_id = fd_f.document_id
and fad_f.document_id = fdt_f.document_id
and fdct_f.category_id = fd_f.category_id
and fdt_f.media_id = fdlt_f.media_id /* R11i */
-- and fd_f.media_id = fdlt_f.media_id /* R12 */
and fdct_f.user_name = 'Invoice Internal'
and fad_f.entity_name = 'AP_INVOICES'
and fad_f.pk1_value = '63227';
Attachments of type "Short Text"
select fad_f.seq_num
, fdst_f.media_id media_id
, fdst_f.short_text media_data_text
from fnd_attached_documents fad_f
, fnd_documents fd_f
, fnd_documents_tl fdt_f
, fnd_document_categories_tl fdct_f
, fnd_documents_short_text fdst_f
where 1 = 1
and fad_f.document_id = fd_f.document_id
and fad_f.document_id = fdt_f.document_id
and fdct_f.category_id = fd_f.category_id
and fdt_f.media_id = fdst_f.media_id /* R11i */
-- and fd_f.media_id = fdst_f.media_id /* R12 */
and fdct_f.user_name = 'Invoice Internal'
and fad_f.entity_name = 'AP_INVOICES'
and fad_f.pk1_value = '63227';
Attachments of type "File"
select fad_l.seq_num
, fl_l.file_id media_id
, fl_l.file_data media_data_blob
from fnd_attached_documents fad_l
, fnd_documents fd_l
, fnd_documents_tl fdt_l
, fnd_document_categories_tl fdct_l
, fnd_lobs fl_l
where 1 = 1
and fad_l.document_id = fd_l.document_id
and fad_l.document_id = fdt_l.document_id
and fdct_l.category_id = fd_l.category_id
and fdt_l.media_id = fl_l.file_id /* Release 11i */
-- and fd_l.media_id = fl_l.file_id /* Release 12 */
and fad_l.entity_name = 'AP_INVOICES'
and fdct_l.user_name = 'Invoice Internal'
and fad_l.pk1_value = '63227';
Update: Added extra line to file query for R12.
Update: In case anyone needs to delete any attachments here's an example (thanks to Mark L):
declare
l_entity_name varchar2(20):= 'PO_LINES'; -- This defines the PO_LINES entity
l_pk1_value varchar2 (20) := '200487'; -- PO_LINE_ID items are attached to
l_delete_document_flag varchar2 (1) := 'Y'; -- Delete the document
begin
--fnd_global.apps_initialize(0,20420,1); -- SYSADMIN System Administrator
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;
Happy Attaching!
