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
 -- Setting the context ---- 
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 ------------
     OPEN cur_cat_id;
     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
 

3 Responses to “How to attach documents from backend? ( fnd_webattch.add_attachment )”

Anonymous said...
July 20, 2013 at 3:18 PM

Hi,

From where I get the MEDIA_ID?


Unknown said...
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.


Janel said...
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

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.