How to send the Short Text & Long Text Attachments as Attachment to the Oracle Workflow Notification.

Oracle Apps Technical.

How to send the Short Text & Long Text Attachments as Attachment to the Oracle Workflow Notification.

1) Create a File for Short Text and Long text.

DECLARE
CURSOR c_file_table (p_req_header_id NUMBER)
                      IS
select fad.pk1_value ,fdst.media_id, (fdst.short_text), fad.creation_date,fad.entity_name
from fnd_attached_documents fad, fnd_documents_tl fdt, FND_DOCUMENTS_SHORT_TEXT fdst
              where fad.document_id = fdt.document_id
              and fdt.language = 'US'
              and fdt.media_id = fdst.media_id
              and fad.pk1_value = p_req_header_id
              and fad.entity_name in ('REQ_HEADERS','REQ_LINES')
              order by length(fdst.short_text) desc;
              f utl_file.file_type;
    ln_req_header_id      NUMBER;
    l_file_content_type   fnd_lobs.file_content_type%TYPE   :=NULL;
BEGIN
    ln_req_header_id := 12345;
    l_file_content_type :=NULL;
    for s in c_file_table(ln_req_header_id)
    loop
        f := utl_file.fopen('USER_DIR',s.media_id||'.txt','W');
        utl_file.put_line(f,s.SHORT_TEXT);
         utl_file.fclose(f);
    COMMIT;
    INSERT INTO XX_SHORT_LONG_TABLE (MEDIA_ID,PK1_VALUE,ENTITY_NAME) VALUES (S.MEDIA_ID,S.PK1_VALUE,S.ENTITY_NAME);
    COMMIT;
     Loading_BLOB_From_File('USER_DIR', s.media_id||'.txt',s.media_id);
     COMMIT;
     utl_file.fremove('USER_DIR',s.media_id||'.txt');
    end loop;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.put_line ('In others ' || SQLERRM);
END;


==================

CREATE OR REPLACE PROCEDURE Loading_BLOB_From_File ( P_DIR        IN VARCHAR2
                                    ,P_FILE_NAME IN VARCHAR2
                                    ,p_rowid  IN VARCHAR2)
AS

    dest_loc  BLOB;
    src_loc   BFILE := BFILENAME(P_DIR, P_FILE_NAME);

BEGIN

    -- +-------------------------------------------------------------+
    -- | INSERT INITIAL BLOB VALUE (an text file) INTO THE TABLE    |
    -- +-------------------------------------------------------------+

   DBMS_OUTPUT.put_line ('Before Updating');
  update XX_SHORT_LONG_TABLE
  set TEXT = EMPTY_BLOB()
  where media_id = p_rowid
  RETURNING text INTO dest_loc;
DBMS_OUTPUT.put_line ('after Updating');
    -- +-------------------------------------------------------------+
    -- | OPENING THE SOURCE BFILE IS MANDATORY                       |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(src_loc, DBMS_LOB.LOB_READONLY);
DBMS_OUTPUT.put_line ('src_loc open');
    -- +-------------------------------------------------------------+
    -- | OPENING THE LOB IS OPTIONAL                                 |
    -- +-------------------------------------------------------------+
    DBMS_LOB.OPEN(dest_loc, DBMS_LOB.LOB_READWRITE);
DBMS_OUTPUT.put_line ('dest_loc open');
    -- +-------------------------------------------------------------+
    -- | SIMPLY CALL "loadfromfile" TO LOAD FILES INTO A LOB COLUMN  |
    -- +-------------------------------------------------------------+
    DBMS_LOB.LOADFROMFILE(
          dest_lob => dest_loc
        , src_lob  => src_loc
        , amount   => DBMS_LOB.getLength(src_loc));
DBMS_OUTPUT.put_line ('dest_loc load file');
    -- +-------------------------------------------------------------+
    -- | CLOSING ANY LOB IS MANDATORY IF YOU HAVE OPENED IT          |
    -- +-------------------------------------------------------------+
    DBMS_LOB.CLOSE(dest_loc);
    DBMS_LOB.CLOSE(src_loc);
DBMS_OUTPUT.put_line ('close all db');

    COMMIT;
Exception
when others then
DBMS_OUTPUT.put_line (' Exception in Load_BLOB_From_File'||SQLERRM);

END Loading_BLOB_From_File;
/

Thank you..
Sai..