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_textfnd_documents_long_text Do you think this Article is useful?
Subscribe to:
Post Comments (Atom)
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