How to attach document in Oracle Apps Received from External System

Objective  :

This article describes the process of attaching document in Oracle Apps received from external system.

The Source System sends the Binary File (Blob) through middleware. This file is then stored in Oracle Apps Database in a common table and then it is attached by calling an  API to corresponding Form.

Process  :

1. Insert binary file (blob) value in fnd_lobs, which as an output would give file_id in sequence. Fnd_lobs table contains the binary files in blob format which is referenced by each and every attachment.
2. Normally Attachments are attached to Oracle Forms in the incremental of 10’s. So extract for maximum sequence no + 10 for that particular Function from using

SELECT NVL (MAX (SEQ_NUM), 0) + 10

  FROM FND_ATTACHED_DOCUMENTS FAD

WHERE FAD.PK1_VALUE = P_PKVAL AND ENTITY_NAME = 'Function Name';

Attachment to the Particular Function is done by following command :
FND_WEBATTCH.Add_Attachment

      (seq_num                          => ‘sequence retrieved from step 2’,

       category_id                      => '1',

       document_description      => ‘Document Description’,

       datatype_id                       => '6',

       TEXT                                => NULL,

       file_name                     => ‘file name’,

       url                           => NULL,

       function_name             => ‘Function Name’,

       entity_name               => ‘Entity Name’,

       pk1_value                 => l_INCIDENT_ID,

       pk2_value                 => NULL,

       pk3_value                 => NULL,

       pk4_value                 => NULL,

        pk5_value                => NULL,

        MEDIA_ID                 => ‘file id retrieved from step 1’,                                          

        user_id                      => 0,

        usage_type               => 'O');

        commit;

Source Code  :

Source Code for this process is attached here with.

CREATE OR REPLACE PROCEDURE XX_ATTACHEXTFILE(P_BLOBVAL       BLOB,

                                             P_FILE_NAME     VARCHAR2,

                                             P_FILE_TYPE     VARCHAR2,

                                             P_PK_VALUE      VARCHAR2,

                                             P_ENTITY_NAME   VARCHAR2,

                                             P_FUNCTION_NAME VARCHAR2,

                                             P_DESCRIPTION   VARCHAR2) IS

  --+==============================================================================+--

  --|                        TATA CONSULTANCY SERVICES LTD                         |--

  --+==============================================================================+--

  /*--------------------------------------------------------------------------------*\

  --                                                                                --

  -- Source   : XX_ATTACHEXTFILE                                                    --

  --                                                                                --

  -- Purpose  : Attach document in Oracle Apps Received from External System        --

  \*--------------------------------------------------------------------------------*/

  l_file_id number;

  CURSOR C_SEQ IS

    SELECT NVL(MAX(SEQ_NUM), 0) + 10

      FROM FND_ATTACHED_DOCUMENTS FAD

     WHERE FAD.PK1_VALUE = P_PK_VALUE AND FAD.ENTITY_NAME = P_ENTITY_NAME;

  L_SEQ_NUM NUMBER;

BEGIN

  select apps.fnd_lobs_S.nextval into l_file_id from dual;

  insert into fnd_lobs

    (file_id,

     file_name,

     file_content_type,

     file_data,

     upload_date,

     expiration_date,

     program_name,

     program_tag,

     language,

     oracle_charset,

     file_format)

  values

    (l_file_id,

     P_FILE_NAME,

     P_FILE_TYPE,

     P_BLOBVAL,

     sysdate,

     null,

     'FND_ATTACH',

     'CS:100',

     'US',

     'US7ASCII',

     'binary');

  commit;

  OPEN C_SEQ;

  FETCH C_SEQ

    INTO L_SEQ_NUM;

  CLOSE C_SEQ;

  FND_WEBATTCH.Add_Attachment(seq_num              => L_SEQ_NUM,

                              category_id          => '1',

                              document_description => P_DESCRIPTION,

                              datatype_id          => '6',

                              TEXT                 => NULL,

                              file_name            => P_FILE_NAME,

                              url                  => NULL,

                              function_name        => P_FUNCTION_NAME,

                              entity_name          => P_ENTITY_NAME,

                              pk1_value            => P_PK_VALUE,

                              pk2_value            => NULL,

                              pk3_value            => NULL,

                              pk4_value            => NULL,

                              pk5_value            => NULL,

                              MEDIA_ID             => l_FILE_ID,

                              user_id              => 0,

                              usa