Join the OracleApps88 Telegram group @OracleApps88to get more information on Oracle EBS R12/Oracle Fusion applications.

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.

Monday, October 10, 2016

Adding Attachments to Invoices/Purchase Orders Through PL/SQL

1        SET UP REQUIREMENTS

1..1     Document Category Setup

Document Category Define the type of attachment .An attachment can be from : Short Text, Long Text,
Image, File, Web Page.

This Document gives illustration of attaching Short Text, Long Text,File Type of Attachments.
Steps for Setting up Document Category are :

  1. Log Into Application Developer Responsibility
  2. Navigate to Attachement => Document Categories.
  3. Create an record for Desired Attachement Category ( Mostly Standard Document Categories
are available .
Invoice Internal is the Standard Document category for Invoices.
Internal to Purchase Order is the Standard Document category for Purchase Orders.




























  1. Click on Assignments.
  2. Category Assignment  Screen opens. This Screen enables an attachement to be viewed in an Form
       Form Function. Assign the Document Category to the Desired Form / Form Function.
      In this Document ,
      Invoices have been assigned to Invoices Overview  Form / Invoices Function
      Thus , the Invoices Attachements can be viewed in Invoices Overview  Form in AP Application.

      Purchase Orders have been assigned to Purchase Orders Summary Function
      Thus , the Purchase Order can be viewed in Purchase Orders Summary Function  in PO
      Application.






















1..2    Document Entity Setup

Document Entity defines the Table Name, which contains the Key Values for the Attachements,
For Invoices and Purchase Orders , Standard Document Entities have been defined.
For AP Invoices , AP_INVOICES is Standard Document Entitiy,
Base Table is FND_DOCUMENT_ENTITIES_VL
pk1_column of this table contains INVOICE_ID.

For Purchase Order Headers , PO_HEADERS is Standard Document Entitiy,
Base Table is FND_DOCUMENT_ENTITIES_VL
pk1_column of this table contains PO_HEADER_ID.


2        PL/SQL Code For Adding Attachments

2..1    Invoice Attachments

2..1.1                Attaching text to invoices

DECLARE
    p_out      VARCHAR2(100);
    p_doc_id   NUMBER;
    p_attach   NUMBER;
    p_media_id NUMBER;
    p_text     LONG := 'This can be a Long text having a length more than 4000 char';
    ln_number  NUMBER;           

BEGIN
  SELECT (FND_ATTACHED_DOCUMENTS_S.NEXTVAL+1000)
  INTO  ln_number 
  FROM dual;

  FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
      (
          X_Rowid    => p_out,
          X_attached_document_id => ln_number,
          X_document_id  => p_doc_id,
          X_creation_date => SYSDATE,
          X_created_by  =>1318,
          X_last_update_date =>SYSDATE ,
          X_last_updated_by  =>1318,
          X_last_update_login  =>1318,
          X_seq_num  =>700,
          X_entity_name  => 'AP_INVOICES',
          X_column1    => '',
          X_pk1_value  =>10807,
          X_pk2_value  => '',
          X_pk3_value  => '',
          X_pk4_value  => '',
          X_pk5_value  => '',
          X_automatically_added_flag => 'N',
          X_datatype_id=>2,
          X_category_id=>327,
          X_usage_type  => 'S', 
          X_language => 'US',
          X_media_id  => p_media_id,
          X_security_type => 2,
          X_publish_flag => 'Y',
           X_storage_type => 1
     );
      
   INSERT INTO FND_DOCUMENTS_LONG_TEXT
        (
          media_id,
          long_text
        )
        VALUES
        (
          p_media_id,
          p_text
        );
     
      COMMIT;
EXCEPTION    
WHEN OTHERS THEN  
   dbms_output.put_line('An exception occurred');
   dbms_output.put_line(sqlcode || sqlerrm);
END;

X_last_updated_by
User Id from FND_USER
X_created_by
User Id from FND_USER
X_last_updated_by
User Id from FND_USER
X_last_update_login
User Id from FND_USER
X_seq_num
Any Number
X_pk1_value
INVIOCE_ID of Target Invoices in AP_INVOICES/ AP_INVOICES_ALL
X_entity_name
From the Setup
X_datatype_id
Data type id for Long Text Data Type in FND_DOCUMENT_DATATYPES
X_category_id
291 is the Category Id For Invoice Internal Category
X_usage_type
Look up code from Lookup ATCHMT_DOCUMENT_TYPE
program_name
Oracle Reads this Program Name to Attach the Document


2..1.2              Attaching JPEG/WORD Documents to invoices

1.      Create  a schema table  to be used for storing the Document
CREATE TABLE blobtab (index_col NUMBER, blob_col BLOB)  .
/
INSERT INTO BLOBTAB (INDEX_COL, BLOB_COL) VALUES (2,EMPTY_BLOB ());
/

2.      Place the JPEG/BMP/WORD Document in an Drectory accessible to Oracle.
      This can be found by querying in ALL_DIRECTORIES.

3.      Exceute the Folowing PL/SQL Code .
DECLARE
   p_out         VARCHAR2 (100);
   p_doc_id      NUMBER;
   p_attach      NUMBER;
   p_media_id    NUMBER;
   ln_number     NUMBER;
   file_s        BFILE          := BFILENAME ('XXI_OM_UTL', 'test.jpg');
  
   /* BFILENAME Function takes Directory Name and File Name as Input Parameters */
   src_offset    INTEGER        := 1;
   dest_offset   INTEGER        := 1;
   p_blob        BLOB;
   asize         INTEGER;
BEGIN
   SELECT (fnd_attached_documents_s.NEXTVAL + 1000)
     INTO ln_number
     FROM DUAL;

   SELECT     blob_col
         INTO p_blob
         FROM blobtab
        WHERE index_col = 2
   FOR UPDATE;

   DBMS_LOB.FILEOPEN (file_s, DBMS_LOB.file_readonly);
   asize := DBMS_LOB.GETLENGTH (file_s);
  
   DBMS_LOB.LOADBLOBFROMFILE (p_blob, file_s, asize, dest_offset, src_offset);
  
   FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
   (   x_rowid                         => p_out,
       x_attached_document_id          => ln_number,
       x_document_id                   => p_doc_id,
       x_creation_date                 => SYSDATE,
       x_created_by                    => 1318,
       x_last_update_date              => SYSDATE,
       x_last_updated_by               => 1318,
       x_last_update_login             => 1318,
       x_seq_num                       => 900,
       x_entity_name                   => 'AP_INVOICES',
       x_column1                       => '',
       x_pk1_value                     => 10807,
       x_pk2_value                     => '',
       x_pk3_value                     => '',
       x_pk4_value                     => '',
       x_pk5_value                     => '',
       x_automatically_added_flag      => 'N',
       x_datatype_id                   => 6,
       x_category_id                   => 291,
       x_usage_type                    => 'S',
       x_language                      => 'US',
       x_media_id                      => p_media_id,
       x_security_type                 => 2,
       x_publish_flag                  => 'Y',
       x_storage_type                  => 1,
       x_file_name                     => 'XYZ'
   );

   INSERT INTO fnd_lobs
               (file_id, file_name, file_content_type, upload_date,
                file_data, file_format, program_name
               )
        VALUES (p_media_id, 'XYZ', 'text/plain', SYSDATE,
                p_blob, 'text', 'FNDATTCH'
               );

   COMMIT;
   DBMS_LOB.fileclose (file_s);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('An exception occurred');
      DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
END;

X_last_updated_by
User Id from FND_USER
X_created_by
User Id from FND_USER
X_last_updated_by
User Id from FND_USER
X_last_update_login
User Id from FND_USER
X_seq_num
Any Number
X_pk1_value
INVIOCE_ID of Target Invoice in AP_INVOICES/ AP_INVOICES_ALL
X_entity_name
From the Setup
X_datatype_id
Data type id for File Data Type in FND_DOCUMENT_DATATYPES
X_category_id
291 is the Category Id For Invoice Internal Category
X_usage_type
Look up code from Lookup ATCHMT_DOCUMENT_TYPE
program_name
Oracle Reads this Program Name to Attach the Document

2..2    Purchase Order Attachements

2..2.1              Attaching text to Purchase Orders


DECLARE
   p_out        VARCHAR2 (100);
   p_doc_id     NUMBER;
   p_attach     NUMBER;
   p_media_id   NUMBER;
   p_text       LONG := 'This can be a Long text having a length more than 2000 char';
   ln_number    NUMBER;
BEGIN
   SELECT (fnd_attached_documents_s.NEXTVAL + 1000)
     INTO ln_number
     FROM DUAL;

   FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
   (   x_rowid                         => p_out,
        x_attached_document_id          => ln_number,
        x_document_id                   => p_doc_id,
        x_creation_date                 => SYSDATE,
        x_created_by                    => 1318,
        x_last_update_date              => SYSDATE,
        x_last_updated_by               => 1318,
        x_last_update_login             => 1318,
        x_seq_num                       => 200,
        x_entity_name                   => 'PO_HEADERS',
        x_column1                       => '',
        x_pk1_value                     => 1,
        x_pk2_value                     => '',
        x_pk3_value                     => '',
        x_pk4_value                     => '',
        x_pk5_value                     => '',
        x_automatically_added_flag      => 'N',
        x_datatype_id                   => 2,
        x_category_id                   => 39,
        x_usage_type                    => 'S',
        x_language                      => 'US',
        x_media_id                      => p_media_id,
        x_security_type                 => 2,
        x_publish_flag                  => 'Y',
        x_storage_type                  => 1
   );

   INSERT INTO fnd_documents_long_text
               (media_id, long_text
               )
        VALUES (p_media_id, p_text
               );

   COMMIT;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('An exception occurred');
      DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
END;

X_last_updated_by
User Id from FND_USER
X_created_by
User Id from FND_USER
X_last_updated_by
User Id from FND_USER
X_last_update_login
User Id from FND_USER
X_seq_num
Any Number
X_pk1_value
PO_HEADER_ID of Target PO in PO_HEADERS_ALL
X_entity_name
From the Setup
X_datatype_id
Data type id for Long Text Type in FND_DOCUMENT_DATATYPES
X_category_id
39 is Category Id for Internal for Purchase Orders in  FND_DOCUMENT_CATEGORIES_L
X_usage_type
Look up code from Lookup ATCHMT_DOCUMENT_TYPE

2..2.2              Attaching JPEG/WORD Documents to Purchase Orders

        1.     Create  a schema table  to be used for storing the Document
CREATE TABLE blobtab(index_col NUMBER , blob_col BLOB)  .

INSERT INTO BLOBTAB(INDEX_COL,BLOB_COL) VALUES(3,EMPTY_BLOB());

        2.     Place the JPEG/BMP/WORD Document in an Drectory accessible to Oracle.
             This can be found by querying in ALL_DIRECTORIES.

        3.     Execute the Folowing PL/SQL Code .

DECLARE
   p_out         VARCHAR2 (100);
   p_doc_id      NUMBER;
   p_attach      NUMBER;
   p_media_id    NUMBER;
   ln_number     NUMBER;
   file_s        BFILE          := BFILENAME ('XXI_OM_UTL', 'test.jpg');
   src_offset    INTEGER        := 1;
   dest_offset   INTEGER        := 1;
   p_blob        BLOB;
   asize         INTEGER;
BEGIN
   SELECT (fnd_attached_documents_s.NEXTVAL + 1000)
     INTO ln_number
     FROM DUAL;

   SELECT     blob_col
         INTO p_blob
         FROM blobtab
        WHERE index_col = 3
   FOR UPDATE;

   DBMS_LOB.fileopen (file_s, DBMS_LOB.file_readonly);
   asize := DBMS_LOB.getlength (file_s);
   DBMS_LOB.loadblobfromfile (p_blob, file_s, asize, dest_offset, src_offset);
  
   FND_ATTACHED_DOCUMENTS_PKG.INSERT_ROW
   (   x_rowid                         => p_out,
        x_attached_document_id          => ln_number,
        x_document_id                   => p_doc_id,
        x_creation_date                 => SYSDATE,
        x_created_by                    => 1318,
        x_last_update_date              => SYSDATE,
        x_last_updated_by               => 1318,
        x_last_update_login             => 1318,
        x_seq_num                       => 100,
        x_entity_name                   => 'PO_HEADERS',
        x_column1                       => '',
        x_pk1_value                     => 1,
        x_pk2_value                     => '',
        x_pk3_value                     => '',
        x_pk4_value                     => '',
        x_pk5_value                     => '',
        x_automatically_added_flag      => 'N',
        x_datatype_id                   => 6,
        x_category_id                   => 39,
        x_usage_type                    => 'S',
        x_language                      => 'US',
        x_media_id                      => p_media_id,
        x_security_type                 => 2,
        x_publish_flag                  => 'Y',
        x_storage_type                  => 1,
        x_file_name                     => 'XYZ'
   );

   INSERT INTO fnd_lobs
               (file_id, file_name, file_content_type, upload_date,
                file_data, file_format, program_name
               )
        VALUES (p_media_id, 'XYZ', 'text/plain', SYSDATE,
                p_blob, 'text', 'FNDATTCH'
               );

   COMMIT;
   DBMS_LOB.fileclose (file_s);
  
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('An exception occurred');
      DBMS_OUTPUT.put_line (SQLCODE || SQLERRM);
END;

X_last_updated_by
User Id from FND_USER
X_created_by
User Id from FND_USER
X_last_updated_by
User Id from FND_USER
X_last_update_login
User Id from FND_USER
X_seq_num
Any Number
X_pk1_value
PO_HEADER_ID of Target PO in PO_HEADERS_ALL
X_entity_name
From the Setup
X_datatype_id
Data type id for File Data Type in FND_DOCUMENT_DATATYPES
X_category_id
39 is Category Id for Internal for Purchase Orders in  FND_DOCUMENT_CATEGORIES_L
X_usage_type
Look up code from Lookup ATCHMT_DOCUMENT_TYPE
program_name
Oracle Reads this Program Name to Attach the Document


2 comments:

life goldens said...

Hello dear,

Wow excellent work... thank you for sharing..

Could you please share the AR invoice attachment extraction query in R12.

Phenv said...

Thanks for sharing

Post a Comment

If you are facing any issues while copying the Code/Script or any issues with Posts, Please send a mail to OracleApp88@Yahoo.com or message me at @apps88 or +91 905 957 4321 in telegram.
Best Blogger TipsGet Flower Effect