Saturday, August 18, 2007

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!

33 comments:

Glorfindel said...

Just what I needed. Thanks!

feng said...

The script for getting attachments of type "File" not works properly on R12,
change the line
>AND fdt_l.media_id = fl_l.file_id
to
>AND fd_l.media_id = fl_l.file_id
,then it does.

Gareth Roberts said...

Thanks Feng for the input!

Bharadwaj said...

Hello, I am trying to create the Attachment using PL/SQL code.

I have found the entire code for creating the Attachment, the issue is, how to pass the Data file into the Attachment.

I want to attach the file stored in $APPLCSF/$APPLOUT as a Attachment.

Could you please tell me how to attach the file path, and file content into the Attachment of PDF File type.

Thanks
Hari

Gareth said...

Hi Hari,

Some example code here for loading blob into PL/SQL variable and to database column ie procedure Load_BLOB_From_File_Image:
http://www.idevelopment.info/data/Oracle/DBA_tips/LOBs/LOBS_10.shtml

You could use a couple of other methods like sql*loader or even request the file via http and report review agent.

Gareth

Ilyas said...

Hello Gareth,

Nice article, thank you.

I am calling the FND_DOCUMENTS_PKG & FND_ATTACHED_DOCUMENTS_PKG for converting notes in AR at customer level. AS the notes are to be converted in free form text - not attachments - I am directly inserting into fnd_documents_short_text table's short_text field. This works, at least for the couple cases - but wanted to know if there was a cleaner or better way to do. I see the insert clause in the FND_DOCUMENTS_PKG that inserts short_text, but no way to pass it when calling the INSERT_ROW procedure....

Thanks, look forward to your feedback.

Gareth said...

Hi Ilyas,

If it works, use it - would be faster than private API as you could bulk load. Haven't had a chance to look for API for short_text ... if you find one post the details!

Looked at fnd_documents_pkg, saw the insert but it didn't look like a clear new row insert, more like a copy.

Regards,
Gareth

shobana said...

Hi Gareth,

Could you please let me know how to attach file to forms.

I wish to attach report output to forms.

How this can be achieved? Please advice.

Gareth said...

Hi Shobana,

If you're talking Concurrent Request Output = report file, you could open the request output file, save it locally, then upload using standard attachments functionality. Drop me an email if you want to discuss more. ggr888 [at] gmail . com.

Regards,
Gareth

Nav said...

Hi Gareth,

Thanks a bunch for the code.

However,I'm trying to attach a 'long text' type data to a PO header.

Would we need to call another API to insert the long text from staging tables to the interface/base tables? If yes, how would we do it?
Also, I changed the data_type_id to 2 and commented the file_name parameter(since this is not a file attachment)

Please let me know if I'm correct.

What are the changes I would need to do? Thanks for your help!

Gareth said...

Hi Nav,

Yes, give it a try, for R12 there is a java API. For R11i you may need direct insert, see if there is a private API first.

Regards,
Gareth

Czar said...

Hi Gareth,

Can you please let me know, what if I have to attach the file through Oracle Apps forms to UNIX, how can I achieve this?

Thanks,
John

Gareth said...

Hi John,
Either you have to attach the physical file itself by getting the files into fnd_lobs as a blob, or you need to have a web server dishing up the file by http.

Regards,
Gareth

Abhishek Sharma said...
This comment has been removed by the author.
Abhishek Sharma said...

In R12 (not sure about R11), in fnd_documents_pkg.insert_row, i had to add X_url => l_filename.

And I commented fnd_documents_pkg.insert_tl_row. Its not needed i believe.

Then it worked for Weblinks as attachment.

Naresh Kumar said...

Hello Gareth,

I want to refer a existing attachments to operation attachment in the routing.Is there any API for this.

Gareth said...

Hi Naresh,

In latest R12 there is a new attachment API. Otherwise use the above.

Regards,
Gareth

Nazi said...

Hi Gareth,

I successfully printed the long text of requisition lines using these tables
select pti.attribute_value attribute_value
from
por_template_attributes_v ptav, por_template_info pti
where ptav.attribute_code=pti.attribute_code
and pti.requisition_line_id=:requisition_line_id
and pti.attribute_value is not null

when the datatype_id=2 from fnd_attached_docs_form_vl

Now the issue is some of the certifications are not stored under attachments.In this case from where do we have to get that data from.
Plz help in resolving this issue as my report in production.
Thanks in advance

Gareth said...

Hi Nazi,
Sorry, can't help you unless you know where your data lives!

Srikanth said...

Hello Gareth,

We have a requirement to read the file attached at an Item level. We want to generate a report and print manufacturing work order on every page of the file attached along with the person who has crated the work order and the start and completion dates

The files attached are unique to the item and hence looking an option of having custom report layouts for each file will be very tedious

In anticipation of an encouraging response

Regards
Srikanth

Gareth said...

Hi Srikanth, depending on the type of files attached, this could be tricky. If you have a variety of attachments like word / excel docs etc, they can be problematic.
If simple attachments like PDF and JPG, then you could extract to operating system and print using O/S utilities.
Alternatively you could consider a 3rd party product like STR Software AventX. Please mention me if you contact STR Software.

Thanks,
Gareth

pranav p said...

I am not able to attach url with the above code

pranav p said...

Hi Feng,

Used your code to attach url, but not able to attach url can you please check the code once and give me the solution.

declare
l_rowid rowid;
l_attached_document_id number;
l_document_id number;
l_media_id number:=null;
l_category_id number := 1000685; -- Invoice Internal
l_pk1_value fnd_attached_documents.pk1_value%TYPE := '224307';
l_description fnd_documents_tl.description%TYPE := 'sample url attaching to invoice';
l_filename fnd_documents_tl.file_name%TYPE := 'http://in.yahoo.com/?p=us';
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;
/


Thanks in advance.

pranav p said...

sorry Gareth I posted it to you, please provide me the solution.

Gareth said...

Hi Pranav,

Did you commit ?

Regards,
Gareth

Saurabh Jethi said...

Hey Gareth,

As always... you are the savior :)

Just the thing I was looking for to delete attachments.

Cheers,
Saurabh

sshanmug said...

Hi Gareth,

Will fnd_attached_documents2_pkg.delete_attachments physically delete the row from fnd_lobs table or just remove the link?

If I need to remove records from fnd_lobs what should I do?

Kindly Clarify.

Thanks,
Sen

Gareth said...

Hi Sen, yes fnd_attached_documents2_pkg.delete_attachments deletes from fnd_lobs.
Regards,
Gareth

Arul said...

hi ,i am trying to match receipt screen with attachment screen in header level in form personalization.is it possible because in attachment screen all are fnd tables.

thanks in advance,
Arul

Ron D said...

Garth,
Thank you for taking the time to do this. We have a requirement for users, both INTERNAL and EXTERNAL to add notes to Items.
Will this work as well? Thanks!
rd

Gareth said...

Hi Ron, not sure I understand your question, if access is provided, then ability to provide for the same is there :-)

Regards,
Gareth

Sridevi Koduru said...

Regards
Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b

Janel said...

Just curious why you pass the URL in as the file name and not the X_URL parameter? When I save a web page attachment via the form, it stores it in the fd.URL field, not the file_name.