Attach document from Backend

on Sunday, September 19, 2010

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

No comments: