Search This Blog

Saturday, December 7, 2013

FND_ATTACHED_DOCUMENTS

Files attached using Attachment Image / Attachment Link in OAF, will be stored in FND_ATTACHED_DOCUMENTS Table. 

FND_ATTACHED_DOCUMENTS stores information relating a document to an application entity. For example, a record may link a document to a sales order using Entity Name as Sales Order and PK1_VAlue with Order Number/Header_ID.

** To attach any file using Attachment Image Bean, follow the steps mentioned in below post:

http://vamsimantripragada.blogspot.com/2013/10/adding-attachments-to-oa-page.html

** To download the attached file from FND_ATTACHED_DOCUMENTS use below procedure:

CREATE OR REPLACE PROCEDURE Write_BLOB_To_File( p_entity_name IN VARCHAR2, p_PK1_VALUE IN VARCHAR2)
AS
    v_lob_loc      BLOB;
    v_buffer       RAW(32767);
    v_buffer_size  BINARY_INTEGER;
    v_amount       BINARY_INTEGER;
    v_offset       NUMBER(38) := 1;
    v_chunksize    INTEGER;
    v_out_file     UTL_FILE.FILE_TYPE;
    v_file_name varchar2(1000);

BEGIN

    -- +-------------------------------------------------------------+
    -- | SELECT THE LOB LOCATOR                                      |
    -- +-------------------------------------------------------------+
    BEGIN
    SELECT  flb.file_data , flb.file_name
    INTO    v_lob_loc, v_file_name
    FROM    fnd_lobs flb, 
fnd_attached_documents fad,
fnd_documents fd
WHERE 1=1
AND fad.entity_name = p_entity_name
AND fad.pk1_value = p_pk1_value
AND fad.document_id = fd.document_id
AND fd.media_id = flb.file_id;

EXCEPTION WHEN OTHERS THEN 
DBMS_OUTPUT.put_line('No file found with Given Entity Name and PK1_Value');
END;

   -- +-------------------------------------------------------------+
    -- | FIND OUT THE CHUNKSIZE FOR THIS LOB COLUMN                  |
    -- +-------------------------------------------------------------+
    v_chunksize := DBMS_LOB.GETCHUNKSIZE(v_lob_loc);

    IF (v_chunksize < 32767) THEN
        v_buffer_size := v_chunksize;
    ELSE
        v_buffer_size := 32767;
    END IF;

    v_amount := v_buffer_size;

    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(v_lob_loc, DBMS_LOB.LOB_READONLY);

    -- +-------------------------------------------------------------+
    -- | WRITE CONTENTS OF THE LOB TO A FILE                         |
    -- +-------------------------------------------------------------+
    v_out_file := UTL_FILE.FOPEN(
        location      => '/usr/tmp', 
        filename      => v_file_name, 
        open_mode     => 'wb',
        max_linesize  => 32767);

    WHILE v_amount >= v_buffer_size
    LOOP

      DBMS_LOB.READ(
          lob_loc    => v_lob_loc,
          amount     => v_amount,
          offset     => v_offset,
          buffer     => v_buffer);

      v_offset := v_offset + v_amount;

      UTL_FILE.PUT_RAW (
          file      => v_out_file,
          buffer    => v_buffer,
          autoflush => true);

      UTL_FILE.FFLUSH(file => v_out_file);

      
    END LOOP;

    UTL_FILE.FFLUSH(file => v_out_file);

    UTL_FILE.FCLOSE(v_out_file);

    -- +-------------------------------------------------------------+
    -- | CLOSING THE LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(v_lob_loc);

end write_blob_to_file;
/

Executing the Procedure: 

exec write_blob_to_file(p_entity_name => 'PO_VENDORS',
p_pk1_value => '52367');

File will be downloaded to /usr/tmp

1 comment:

  1. fd.media_id invalid identifier. Does the above procedure won't work in R11i

    ReplyDelete