Load PDF File attachment using API
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.
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.
CREATE OR REPLACE DIRECTORY in_file_loc AS '/tmp';
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.
CREATE TABLE test_files (p_id NUMBER,
pl_name VARCHAR2(100),
pl_pict BLOB);
Step 3:
Now load the file into this table.
DECLARE
x_blob BLOB;
fils BFILE := BFILENAME ('IN_FILE_LOC', 'G74822LRs.pdf');
blob_length INTEGER;
BEGIN
-- Obtain the size of the blob file
DBMS_LOB.fileopen (fils, DBMS_LOB.file_readonly);
blob_length := DBMS_LOB.getlength (fils);
DBMS_LOB.fileclose (fils);
-- Insert a new record into the table containing the
-- filename you have specified and a LOB LOCATOR.
-- Return the LOB LOCATOR and assign it to x_blob.
INSERT INTO test_files (pl_id,pl_name,pl_pict)
VALUES (4, 'G74822LRs.pdf', EMPTY_BLOB ())
RETURNING pl_pict
INTO x_blob;
-- Load the file into the database as a BLOB
DBMS_LOB.OPEN (fils, DBMS_LOB.lob_readonly);
DBMS_LOB.OPEN (x_blob, DBMS_LOB.lob_readwrite);
DBMS_LOB.loadfromfile (x_blob, fils, blob_length);
-- Close handles to blob and file
DBMS_LOB.CLOSE (x_blob);
DBMS_LOB.CLOSE (fils);
COMMIT;
-- Confirm insert by querying the database
-- for LOB length information and output results
blob_length := 0;
SELECT DBMS_LOB.getlength (pl_pict)
INTO blob_length
FROM test_files
WHERE pl_name = 'G74822LRs.pdf';
DBMS_OUTPUT.put_line ('Successfully inserted BLOB ''' ||'G74822LRs.pdf' || ''' of size ' || blob_length || ' bytes.');
END;
Step 4:
Now that we have file in the staging table, we will start loading the file into FND schema of Oracle Applications/
DECLARE
TYPE result_set_type IS REF CURSOR;
l_name VARCHAR2 (100);
l_doc_size NUMBER;
l_result_set_curr result_set_type;
x_access_id NUMBER;
x_file_id NUMBER;
p_file_name VARCHAR2 (100) := 'G74822LRs.pdf';
PROCEDURE load_file_details (p_name IN VARCHAR2, result_set_curr OUT result_set_type)
AS
l_error VARCHAR2 (2000);
BEGIN
INSERT INTO fnd_lobs_document
(NAME, mime_type, doc_size, content_type, blob_content)
SELECT pl_name, 'application/pdf', DBMS_LOB.getlength (pl_pict), 'BINARY', pl_pict
FROM test_files
WHERE pl_name = p_name;
OPEN result_set_curr FOR
SELECT blob_content
FROM fnd_lobs_document
WHERE NAME = p_name;
EXCEPTION
WHEN OTHERS
THEN
NULL;
l_error := 'LOAD_FILE_DETAILS - OTHERS' || SUBSTR (SQLERRM, 2000);
DBMS_OUTPUT.put_line (l_error);
END load_file_details;
PROCEDURE upload_file (v_filename IN VARCHAR2, x_access_id OUT NUMBER, x_file_id OUT NUMBER)
AS
v_access_id NUMBER;
v_file_id NUMBER;
x_errbuf VARCHAR2 (200);
BEGIN
v_access_id := fnd_gfm.authorize (NULL);
x_access_id := v_access_id;
DBMS_OUTPUT.put_line ('Access id :' || v_access_id);
-- The function fnd_gfm.confirm_upload return the file id
v_file_id :=
fnd_gfm.confirm_upload (access_id => v_access_id
, file_name => v_filename
, program_name => 'TEST'
, program_tag => 'TEST'
, expiration_date => NULL
, LANGUAGE => 'US'
, wakeup => TRUE
);
x_file_id := v_file_id;
DBMS_OUTPUT.put_line ('File id :' || x_file_id);
EXCEPTION
WHEN OTHERS
THEN
x_errbuf := 'Procedure upload_file errored out with the following error : ' || SQLERRM;
DBMS_OUTPUT.put_line (x_errbuf);
END upload_file;
BEGIN
fnd_global.apps_initialize (1318, 20420, 1);
load_file_details (p_name => p_file_name, result_set_curr => l_result_set_curr);
upload_file (v_filename => p_file_name, x_access_id => x_access_id, x_file_id => x_file_id);
COMMIT;
END;
Step 5:
Now let us use item API to load item and get the item_id.
CREATE OR REPLACE PACKAGE xx_create_item
IS
g_miss_num CONSTANT NUMBER := 9.99e125;
g_miss_char CONSTANT VARCHAR2 (1) := CHR (0);
g_miss_date CONSTANT DATE := TO_DATE ('1', 'j');
g_false CONSTANT VARCHAR2 (1) := fnd_api.g_false;
g_true CONSTANT VARCHAR2 (1) := fnd_api.g_true;
PROCEDURE create_item (
p_item_number IN VARCHAR2
, p_description IN VARCHAR2
, p_organization_id IN NUMBER
, p_item_type IN VARCHAR2
, x_inventory_item_id OUT NUMBER
, x_organization_id OUT NUMBER
, x_return_status OUT VARCHAR2
, x_msg_count OUT NUMBER
, x_msg_data OUT VARCHAR2
);
PROCEDURE create_item1 (
p_item_number IN VARCHAR2
, p_description IN VARCHAR2
, p_organization_id IN NUMBER
, p_item_type IN VARCHAR2
, x_inventory_item_id OUT NUMBER
, x_organization_id OUT NUMBER
, x_return_status OUT VARCHAR2
, x_msg_count OUT NUMBER
, x_msg_data OUT VARCHAR2
);
END xx_create_item;
/
CREATE OR REPLACE PACKAGE BODY xx_create_item
IS
PROCEDURE create_item (
p_item_number IN VARCHAR2
, p_description IN VARCHAR2
, p_organization_id IN NUMBER
, p_item_type IN VARCHAR2
, x_inventory_item_id OUT NUMBER
, x_organization_id OUT NUMBER
, x_return_status OUT VARCHAR2
, x_msg_count OUT NUMBER
, x_msg_data OUT VARCHAR2
)
IS
l_template_id NUMBER;
x_item_id NUMBER;
x_org_id NUMBER;
l_item_number VARCHAR2 (100);
l_description VARCHAR2 (4000);
l_organization_id NUMBER;
v_msg_index_out NUMBER;
v_message VARCHAR2 (100);
BEGIN
SELECT template_id
INTO l_template_id
FROM mtl_item_templates_b
WHERE template_name = p_item_type;
l_item_number := p_item_number;
l_description := p_description;
l_organization_id := l_organization_id;
ego_item_pub.process_item (p_api_version => 1.0
, p_transaction_type => 'CREATE'
, p_language_code => 'US'
, p_template_id => 207
, p_organization_id => 204
, p_master_organization_id => 204
, p_description => 'TEST113'
, p_long_description => 'TEST113'
, p_item_number => 'TEST113'
, p_segment1 => 'TEST113'
, x_inventory_item_id => x_inventory_item_id
, x_organization_id => x_organization_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
v_message := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line ('============================================================');
END LOOP;
DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
DBMS_OUTPUT.put_line ('============================================================');
END IF;
END;
PROCEDURE create_item1 (
p_item_number IN VARCHAR2
, p_description IN VARCHAR2
, p_organization_id IN NUMBER
, p_item_type IN VARCHAR2
, x_inventory_item_id OUT NUMBER
, x_organization_id OUT NUMBER
, x_return_status OUT VARCHAR2
, x_msg_count OUT NUMBER
, x_msg_data OUT VARCHAR2
)
IS
l_template_id NUMBER;
l_item_number VARCHAR2 (100);
l_description VARCHAR2 (4000);
l_organization_id NUMBER;
v_msg_index_out NUMBER;
v_message VARCHAR2 (100);
BEGIN
l_item_number := p_item_number;
l_description := p_description;
l_organization_id := p_organization_id;
ego_item_pub.process_item (p_api_version => 1.0
, p_transaction_type => 'CREATE'
, p_language_code => 'US'
, p_template_name => p_item_type
, p_item_number => l_item_number
, p_segment1 => l_item_number
, p_organization_id => l_organization_id
, p_description => l_item_number
, p_long_description => l_item_number
, x_inventory_item_id => x_inventory_item_id
, x_organization_id => x_organization_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
);
END;
END xx_create_item;
/
SHOW errors
/
DECLARE
v_msg_index_out NUMBER;
x_item_id NUMBER;
x_org_id NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
v_message VARCHAR2 (4000);
BEGIN
fnd_global.apps_initialize (1318, 50583, 401);
inv_globals.set_org_id (204);
xx_create_item.create_item1 (p_item_number => 'TEST111'
, p_description => 'TEST111'
, p_organization_id => 204
, p_item_type => 'Finished Good'
, x_inventory_item_id => x_item_id
, x_organization_id => x_org_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ('Return Status is :'||x_return_status);
DBMS_OUTPUT.put_line ('Message Count is :'||x_msg_count);
DBMS_OUTPUT.put_line ('Create Item ID is :'||x_item_id);
DBMS_OUTPUT.put_line ('Created in Organization is :'||x_org_id);
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
v_message := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line ('============================================================');
END LOOP;
DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
DBMS_OUTPUT.put_line ('============================================================');
END IF;
END;
/
DECLARE
v_msg_index_out NUMBER;
x_item_id NUMBER;
x_org_id NUMBER;
x_return_status VARCHAR2 (1);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (4000);
v_message VARCHAR2 (4000);
BEGIN
--fnd_global.apps_initialize (1318, 50583, 401);
inv_globals.set_org_id (204);
xx_create_item.create_item1 (p_item_number => 'TEST111'
, p_description => 'TEST111'
, p_organization_id => 204
, p_item_type => 'Finished Good'
, x_inventory_item_id => x_item_id
, x_organization_id => x_org_id
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
);
DBMS_OUTPUT.put_line ('Return Status is :'||x_return_status);
DBMS_OUTPUT.put_line ('Message Count is :'||x_msg_count);
DBMS_OUTPUT.put_line ('Create Item ID is :'||x_item_id);
DBMS_OUTPUT.put_line ('Created in Organization is :'||x_org_id);
IF x_msg_count > 0
THEN
FOR v_index IN 1 .. x_msg_count
LOOP
fnd_msg_pub.get (p_msg_index => v_index, p_encoded => 'F', p_data => x_msg_data, p_msg_index_out => v_msg_index_out);
v_message := SUBSTR (x_msg_data, 1, 200);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line ('============================================================');
END LOOP;
DBMS_OUTPUT.put_line (SUBSTR (v_message, 1, 2000));
DBMS_OUTPUT.put_line ('============================================================');
END IF;
END;
Step 6:
Use this inventory_item_id, organization_Id, x_access_id and x_file_id to load PDF file against this item.
DECLARE
TYPE result_set_type IS REF CURSOR;
l_name VARCHAR2 (100);
l_doc_size NUMBER;
l_result_set_curr result_set_type;
x_access_id NUMBER;
x_file_id NUMBER;
p_file_name VARCHAR2 (100) := 'G74822LRs.pdf';
PROCEDURE attach_file (p_access_id IN NUMBER, p_file_Id in number, p_filename IN VARCHAR2)
IS
BEGIN
fnd_webattch.add_attachment (seq_num => 100
,category_id => 1
,document_description => 'Test'
,datatype_id => 6
,text => NULL
,file_name => p_filename
,url => NULL
,function_name => 'INVIDITM'
,entity_name => 'MTL_SYSTEM_ITEMS'
,pk1_value => 204 --organization_id
,pk2_value => 72627 --Inventory_item_Id
,pk3_value => NULL
,pk4_value => NULL
,pk5_value => NULL
,media_id => p_file_id
,user_id => 1318
,usage_type => 'O'
);
DBMS_OUTPUT.put_line ('File Attached!');
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line ('error in loading the attachement');
END attach_file;
BEGIN
fnd_global.apps_initialize (1318, 20420, 1);
attach_file (p_access_id => x_access_id, p_file_id=>x_file_id,p_filename => p_file_name);
COMMIT;
END;
Now you should be able to see this attachment in the item master against this item TEST111 which is created.
No comments:
Post a Comment