Text Attachment content print

Various files are attached in the documents like PO, Requisition, Invoice etc. If an attachment is a text type then  it is possible to print the content of the attachment .

One of the example various PO terms and conditions are added in the PO header or line level. The following script will help print in the report.


/* Formatted on 2013/01/11 23:36 (Formatter Plus v4.8.8) */
DECLARE
  CURSOR cur_file_info(i_hdr_id NUMBER) IS
    SELECT DISTINCT seq_num, media_id
      FROM fnd_attached_docs_form_vl
     WHERE pk1_value = i_hdr_id
       AND category_description = 'To Supplier'
       AND datatype_name = 'File'
     ORDER BY seq_num;
BEGIN
  BEGIN
    l_file_content := NULL;
 
    FOR get_file_content IN cur_file_info(:po_header_id) LOOP
      SELECT file_id, file_name
        INTO l_file_id, l_file_name
        FROM fnd_lobs
       WHERE file_content_type = 'text/plain'
         AND file_id = get_file_content.media_id;
   
      populate_file_content(l_file_id, l_file_content);
    END LOOP;
  END;
END;





PROCEDURE populate_file_content(i_file_id      NUMBER,
                                i_file_content OUT VARCHAR2) IS
  l_blob         BLOB;
  l_clob         CLOB := EMPTY_CLOB();
  l_src_offset   NUMBER := 1;
  l_dest_offset  NUMBER := 1;
  l_blob_csid    INTEGER := 0;
  v_lang_context INTEGER := 0;
  l_warning      NUMBER;
  l_amount       BINARY_INTEGER;
  l_buffer_size CONSTANT BINARY_INTEGER := 32767;
  l_buffer VARCHAR2(32767) := NULL;
  --l_file_content           VARCHAR2 (32767) := NULL;
  l_offset NUMBER := 1;
BEGIN
  l_buffer := NULL;
  DBMS_LOB.createtemporary(l_blob, TRUE);
  DBMS_LOB.createtemporary(l_clob, TRUE);

  SELECT file_data INTO l_blob FROM fnd_lobs WHERE file_id = i_file_id;

  IF DBMS_LOB.getlength(l_blob) > 0 THEN
    DBMS_LOB.converttoclob(l_clob,
                           l_blob,
                           DBMS_LOB.getlength(l_blob),
                           l_dest_offset,
                           l_src_offset,
                           1,
                           v_lang_context,
                           l_warning);
  END IF;

  l_amount := l_buffer_size;

  WHILE l_amount >= l_buffer_size LOOP
    BEGIN
      DBMS_LOB.READ(lob_loc => l_clob,
                    amount  => l_amount,
                    offset  => l_offset,
                    buffer  => l_buffer);
      l_buffer := REPLACE(l_buffer, CHR(13), '');
      l_offset := l_offset + l_amount;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;

  i_file_content := l_buffer;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END;