Extract PO Attachments

CREATE OR REPLACE PROCEDURE dsdpo_extract_po_files
AS

  v_file     utl_file.file_type;
  v_line     varchar2(1000);
  v_blob_len number;
  v_pos      number;
  v_buffer   raw(32764);
  v_amt      binary_integer := 32764;

  cursor cur_files
  is
    SELECT ad.entity_name, ad.seq_num Seq , ad.pk1_value, h.segment1, fl.file_name, fl.file_data
    FROM fnd_documents d,
    fnd_attached_documents ad,
    fnd_documents_vl dtl,
    po_headers_all h,
    fnd_lobs fl,
    fnd_document_datatypes fdd
    WHERE d.document_id = ad.document_id
    AND ad.entity_name = 'PO_HEAD'
    AND ad.pk1_value = to_char(h.po_header_id)
--    AND h.segment1 = '436223'
    AND d.document_id = dtl.document_id
    AND fl.file_id = d.media_id
    AND fdd.user_name = 'File'
    and h.po_header_id in(

    select po_header_id from apps.po_headers_all
    where vendor_id =7951
    and creation_date >'01-JAN-2012' and type_lookup_code='STANDARD')
    and dtl.file_name like '%.pdf';

begin
  -- File Attachments
  for c_file in cur_files
  loop
    v_file := utl_file.fopen('/applcsf/ebsdev/temp','dsd_'||c_file.file_name, 'wb', 32764);

    v_blob_len := dbms_lob.getlength(c_file.file_data);
    v_pos := 1;
    while v_pos < v_blob_len
    loop
      dbms_lob.read(c_file.file_data,v_amt,v_pos,v_buffer);
      utl_file.put_raw(v_file,v_buffer,true);
      v_pos := v_pos + v_amt;
    end loop;
    utl_file.fclose(v_file);
  end loop;

end;