Oracle eBusiness Suite

April 28, 2008

Bringing in a PDF file as an attachment to an entity using APIs

Attachments in Oracle eBusiness Suite provide a great functionality to attach a file of type, text, word, excel or pdf against a specific entity. This entity can be an Item or BOM. For that matter attachments can be created for any entity be it master or transactional data. And from the very screen where this data is seen  attachments are available to view as file.

A number of times when we are bringing this data from either other applications or converting data from the legacy systems, we need to load attachments against that entity. A classic example is to load a PDF file (instruction manual) that gives you a lot of information about an item. Or an assembly diagram of the product against its BOM is also a good example.

This article provides a way on how to bring in an attachment along with that entity.

In this example I will be creating an item using an API first and then attach a PDF file to that item. This article assumes some level of understanding of attachments functionality. Here are few steps in enabling attachments against an entity.

  1. Register document entities. Here you need to know what is the primary key of this document entity. In this example it is inventory_item_id and organization_id and document entity is MTL_SYSTEM_ITEMS (The table behind the items).
  2. Enable the attachments for a form or a function where you want attachment to be seen. As we have chosen item master, we need to find out the form function of the item master. You can find that from the menu.
  3. If there are many blocks in the form you need to know in which block you want this attachment to be seen. Choose that block.
  4. If you have any security like Operating Unit, enable that as well.
  5. Make sure to choose the primary keys of the document entity in the entity declaration. In this case if you have chose Key1 for organization_id, the value of organization_id from the item master form will be stored in the pk1_value field and so on.
  6. Assign attachment categories to form functions. This decides what kind of document cartegories need to be assigned to form functions.

I have used  Vision instance to perform this test (12.0.4). I loaded this item into V1 (master organization of Vision Operations). To load item I have used Engineering API ego_item_pub.process_item.

Here are the steps involved:

First create a directory where you want the file to come and sit. If your partner system is sending the files everyday along with the data, you need to decide where to store that data on the server. Data file should contain the entity name (item number) and file name (file to be attached). File will come and sit on the server along with the data file. From here you can start loading into a staging table and process them.

Step 1:

Decide the location where the file is going to sit and create that directory using this script. In this example I am using  /tmp as my directory. Code is here.

Step 2:

Now we need to read this file in this directory and load that into a table. For that purpose I have created this table. Code is here.

Step 3:

Now load the file into this table. Code is here.

Step 4:

Now that we have file in the staging table, we will start loading the file into FND schema of Oracle Applications using this code.

Step 5:

Now let us use item API to load item and get the item_id. Use this code.

Step 6:

Use this inventory_item_id, organization_Id, x_access_id and x_file_id to load PDF file against this item. Use this code.

Now you should be able to see this attachment in the item master against this item TEST111 which is created.

 

About these ads

21 Comments »

  1. This is simply awesome .. Thanks a lot .. I was always wandering how to load file attachments .. Have not tried it yet, will definetely do that.
    Thanks again and appreciate you sharing such a nice thing.

    Comment by Suresh — May 1, 2008 @ 4:49 pm

  2. Also please let us know if there is any way by which we can download the PDF file through PL/SQL code.

    Thanks in Advance,
    Suresh

    Comment by Suresh — May 2, 2008 @ 1:37 pm

  3. Hi,

    Thanks for the information.

    I tried to attach document in R12, At step 4 i am getting null value for v_file_id from fnd_gfm.confirm_upload API call.

    After…
    v_access_id := fnd_gfm.authorize (NULL); call
    I am getting the access_id and new record in fnd_lob_access table with null value for file_id.
    After…
    v_file_id := fnd_gfm.confirm_upload API call
    to get the v_file_id, in fnd_gfm API code for R12 the file_id value extracting from fnd_lob_access table which was NULL in previous call.

    Please let me know if i am missing any step to the v_file_id.

    Thanks
    Ram

    Comment by Ram — May 28, 2008 @ 11:13 pm

  4. Ram,
    Thanks for pointing out. Wrong code sneaked in. This code is good for the 11i (for fnd_gfm). I will upload another file for 12 soon.

    Thanks.

    Comment by Nagamohan — May 29, 2008 @ 11:21 am

  5. Thanks Nagamohan,

    If you get a chance could you please post code for R12 asap, currently i am working on this.

    Thanks
    Ram

    Comment by Ram — May 29, 2008 @ 2:29 pm

  6. Thanks, it worked, I have been looking for a solution like this.

    Greetings

    Comment by Arturo L — June 25, 2008 @ 4:15 pm

  7. Hi,
    Thnx a lot for sharin the code….
    apparantly….i m also facing the same prob discussed earlier( by ram) in 11i itself.
    I have got the record inserted in fnd_lobs_document,
    i get the value of v_access_id but after that, while tryin to run fnd_gfm.confirm_upload API for getting a file_id,
    it goes into exception no_data_found.
    the table fnd_lob_access has file_id as null.

    Plz assist.

    Thnx

    Comment by techie — July 3, 2008 @ 11:52 am

  8. Hi, this is a nice article to have. Can you tell me whether the same can be used for transferring attachments from Order Management module to Receivables?

    Comment by sankarshan — July 10, 2008 @ 2:37 am

  9. Sankarshan
    You do not have to transfer the attachements between OM and AR, although you can do this. You can use Related attachements functionality(oe_order_lines_all.line_Id=ra_customer_trx_lnes_all.interface_line_attribute6) hence you can view the same attachment that is attacheed to either order or line against the invoice header or line. Helps saving the database size.

    Comment by Nagamohan — July 10, 2008 @ 11:08 am

  10. Hi Nagamohan,

    Did you get chance to look into the code for R12 as it is not working in R12.

    Please send me the workaround if you hae for it.
    Thanks in advance.

    Regards,
    Sreejit

    Comment by Sreejit — September 25, 2008 @ 9:36 am

  11. Hi Nagmohan
    Iam using this code for an invocie in AP Payable,for uploading pdf documents related to invoice.
    This code is for one pdf file ,i,e we are hardcoding the file name ,but if we have more pdf files stored in the servers place how can we process them one after one for a particular invoice.
    can you please suggest how to process multiple files on the server place.

    Thanks

    Rahul Verma

    Comment by Rahul — November 24, 2008 @ 12:06 pm

  12. Iam using this code for an invocie in AP Payable,for uploading pdf documents related to invoice.
    This code is for one pdf file ,i,e we are hardcoding the file name ,but if we have more pdf files stored in the servers place how can we process them one after one for a particular invoice.
    can you please suggest how to process multiple files on the server place.

    Thanks

    Comment by Rahul — November 25, 2008 @ 4:54 pm

  13. Hey Guys…

    Just I wanted to get the R12 code , Can you share please ?

    Okmman

    Comment by Okmman — November 25, 2008 @ 6:55 pm

  14. Hi nagmohan
    Iam using this code for an invocie in AP Payable,for uploading pdf documents related to invoice.
    This code is for one pdf file ,i,e we are hardcoding the file name ,but if we have more pdf files stored in the servers place how can we process them one after one for a particular invoice.
    can you please suggest how to process multiple files on the server place.

    Comment by Rahul — December 3, 2008 @ 4:04 pm

  15. Hi nagmohan
    IS this rel 12i version code or 11i.If no 11i
    Can i have the code of 11i version .

    Thanks
    Rahul

    Comment by Rahul — December 3, 2008 @ 4:07 pm

  16. I was under the impression that FND_LOBS_DOCUMENT is a staging table and that once you have loaded the document it should be deleted from the FND_LOBS_TABLE, otherwise it will just keep growing (FND_LOBS_DOCUMENT is an unindexed table). Is this something that should be in your PLSQL code or have I missed something?

    Comment by Steve — December 15, 2008 @ 9:11 pm

  17. Steve
    fnd_gfm.confirm_uload (which is used in upload logic)deletes the record from the fnd_lobs_document after inserting into fnd_lobs tables. We do not need that statement explicitly.
    Thanks
    Nagamohan

    Comment by Nagamohan — December 15, 2008 @ 10:36 pm

  18. Hello Nagmohan,

    Thanks for sharing the code.

    I am getting the error as ” The following error has occurred:

    ORA-22285: non-existent directory or file for FILEOPEN operation
    ORA-06512: at “SYS.DBMS_LOB”, line 504
    ORA-06512: at line 7
    ” while running the Load File into Stg.sql.

    Can you please help me in this regard?

    Appreciate your help. Thanks in advance.

    Comment by Sachin — March 9, 2009 @ 4:21 pm

  19. nice post, thanx pal

    Comment by agas — March 23, 2009 @ 7:54 am

  20. Hi,

    I am also facing same problem as mentioned by Ram. Please help me out to resolve this out.
    I tried to attach document in R12, At step 4 i am getting null value for v_file_id from fnd_gfm.confirm_upload API call.

    After…
    v_access_id := fnd_gfm.authorize (NULL); call
    I am getting the access_id and new record in fnd_lob_access table with null value for file_id.
    After…
    v_file_id := fnd_gfm.confirm_upload API call
    to get the v_file_id, in fnd_gfm API code for R12 the file_id value extracting from fnd_lob_access table which was NULL in previous call.

    Please let me know if i am missing any step to the v_file_id.

    Thanks

    Comment by rahul — March 30, 2009 @ 5:49 am

  21. Ram,

    I have figured outthe solution for the problem mentioned by you in the blog.
    You must be joiing the parameters to provide it as input parameter for fnd_function.execute:

    l_parameters :=’access_id=’||access_id||’ l_server_url=’||l_server_url;

    just change it to the mentioned beow format. This will work for R12.

    l_parameters :=’access_id=’||access_id||’&l_server_url=’||l_server_url;

    Comment by Rahul — April 10, 2009 @ 11:55 am


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Shocking Blue Green Theme. Create a free website or blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: