Friday, March 12, 2010
How to attach documents from backend? ( fnd_webattch.add_attachment )
Declare
v_category_id NUMBER;
v_attached_doc_id NUMBER;
v_invoice_id NUMBER;
v_invoice_image_url VARCHAR2(500) := 'http://shareoracleapps.blogspot.com';
v_function_name VARCHAR2(50) := 'APXINWKB';
v_category_name VARCHAR2(100) := 'FromSupplier';
v_description VARCHAR2(300) := 'Test script for attaching scanned image url to AP invoice';
v_entity_name VARCHAR2(100) := 'AP_INVOICES'
v_file_name VARCHAR2(100) := NULL;
v_user_id NUMBER := 1234;
TYPE result_set_type IS REF CURSOR;
v_result_set_curr result_set_type;
--Here for example we are using "FromSupplier" as a category
--and AP_INVOICES_ALL.invoice_id as primary key value
CURSOR cur_cat_id
IS
SELECT fdc.category_id
FROM fnd_document_categories fdc
WHERE fdc.name = v_category_name;
FUNCTION set_context( i_user_name IN VARCHAR2
,i_resp_name IN VARCHAR2
,i_org_id IN NUMBER)
RETURN VARCHAR2
IS
-- Inorder to reduce the content of the post I moved the implementation part of this function to another post and it is available here
END set_context;
BEGIN
v_context := set_context('&V_USER_NAME','&V_RESPONSIBILITY',82);
IF v_context = 'F'
THEN
DBMS_OUTPUT.PUT_LINE('Error while setting the context');
END IF;
DBMS_OUTPUT.PUT_LINE('2');
--- context done ------------
FETCH cur_cat_id INTO v_category_id;
CLOSE cur_cat_id;
-- Invoke the fnd_webattach api for attaching the URL to the invoice
fnd_webattch.add_attachment ( seq_num => 100
,category_id => v_category_id
,document_description => v_description
,datatype_id => 5
,text => NULL
,file_name => v_file_name
,url => v_invoice_image_url
,function_name => v_function_name
,entity_name => v_entity_name
,pk1_value => v_invoice_id
,pk2_value => NULL
,pk3_value => NULL
,pk4_value => NULL
,pk5_value => NULL
,media_id => x_file_id
,user_id => v_user_id
,usage_type => 'O'
);
SELECT count(fad.attached_document_id)
INTO v_attached_doc_id
FROM fnd_attached_documents fad
WHERE fad.pk1_value = v_invoice_id;
IF v_attached_doc_id > 0
DBMS_OUTPUT.PUT_LINE('Attached sucessfully');
THEN
DBMS_OUTPUT.PUT_LINE('Failed to Link the Attacment.');
END IF; --IF v_attached_doc_id > 0
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error occured : '||SQLERRM);
END attach_invoice;
--- Below are the main tables involved regarding attachments
fnd_attached_documents
fnd_documents
fnd_documents_tl
fnd_document_categories_tl
fnd_lobs
fnd_documents_short_text
fnd_documents_long_text
Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
Disclaimer
The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.
4 Responses to “How to attach documents from backend? ( fnd_webattch.add_attachment )”
July 20, 2013 at 3:18 PM
Hi,
From where I get the MEDIA_ID?
August 25, 2014 at 8:22 AM
Can this method be used in Oracle projects module (R12)? We have 30,000+ documents that need to be attached to invoice records and need a viable back end method.
August 10, 2016 at 1:46 PM
August 10, 2016 at 1:47 PM
x_file_id doesn't seem to be declared anywhere but you are passing it in your procedure call....what is it???
Post a Comment