Document Attachments: Private Stuff

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:

1. 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
2. The l_pk1_value which is ap_invoices_all.invoice_id
3. The l_filename and l_description variables
4. Possibly l_category_id: create an real example, check out examine and find the category_id you need to use.
5. Remember to commit!
6. 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!