Attach document from Backend

--- 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

 

Declare

v_category_id                  NUMBER;

v_attached_doc_id           NUMBER;

v_invoice_id                    NUMBER;

v_invoice_image_url         VARCHAR2(500) := 'http://rafi-oracle.blogspot.com';

v_function_name            VARCHAR2(50)   := 'APXINWKB';

v_category_name            VARCHAR2(100) := 'FromSupplier';

v_description                  VARCHAR2(300) := 'Test script for attaching image url to AP invoice';

v_entity_name                VARCHAR2(100) := 'AP_INVOICES'

v_file_name                    VARCHAR2(100) := NULL;

v_user_id                        NUMBER          := 12345; 

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

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;

EXCEPTION

   WHEN OTHERS THEN

         DBMS_OUTPUT.PUT_LINE('Error occured : '||SQLERRM);

   END attach_invoice;