Outbound

PO Outbound(UTL_FILE):


create or replace procedure cb9_po_pur_orddet_prc
(errbuf out varchar2,
 retcode out varchar2, -- 0,1,2
 p_directory_name   in varchar2,
 p_file_name        in varchar2
)
is

cursor cur_po
is
select pov.SEGMENT1 vendor_number,
pov.vendor_name ,
poh.SEGMENT1,
poh.type_lookup_code,
sum(unit_price*quantity) amount
from po_vendors pov,
--po_vendor_sites_all pvs,
po_headers_all   poh,
po_lines_all     pol
where pov.VENDOR_ID = poh.vendor_id
--and   pvs.vendor_site_id = poh.vendor_site_id
and   trunc(poh.creation_date) = trunc(sysdate)
and   poh.po_header_id   = pol.po_header_id
group by pov.SEGMENT1,
pov.vendor_name ,
poh.SEGMENT1,
poh.type_lookup_code;

v_file_type   utl_file.file_type;

begin

   v_file_type  := utl_file.fopen(p_directory_name--'/usr/tmp'
                                 ,p_file_name--'CB9_PO_PUR_DET.sql'
                                 ,'w');
      utl_file.put_line(v_file_type,'Vendor Number'||'~'||
                                    'Vendor Name'||'~'||
                                    'PO Number'||'~'||
                                    'PO Type'||'~'||
                                    'PO Amount'
                        );  
   for rec_po in cur_po
   loop
      utl_file.put_line(v_file_type,rec_po.vendor_number||'~'||
                                    rec_po.vendor_name||'~'||
                                    rec_po.SEGMENT1||'~'||
                                    rec_po.type_lookup_code||'~'||
                                    rec_po.amount
                        );
   end loop;

--   utl_file.put_line(v_file_type,'Test');
   utl_file.fclose(v_file_type);
exception
   when utl_file.invalid_path then
      retcode := 1 ;
      fnd_file.put_line(fnd_file.log,'Invalid directory path given');
   when utl_file.invalid_mode then
      fnd_file.put_line(fnd_file.log,'file operation mode is invalid');
   when others then
      retcode := 1 ;
      fnd_file.put_line(fnd_file.log,'Exception in procedure ->>'||sqlerrm);
end;