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.
- 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).
- 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.
- 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.
- If you have any security like Operating Unit, enable that as well.
- 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.
- 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.
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.
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.
Now load the file into this table. Code is here.
Now that we have file in the staging table, we will start loading the file into FND schema of Oracle Applications using this code.
Now let us use item API to load item and get the item_id. Use this code.
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.