USING UTL_FILE PACKAGE (OUT BOUND)

 

USING UTL_FILE PACKAGE (OUT BOUND)

CREATE OR REPLACE procedure
APPS.xx_po_out(x_errbuf out varchar2
,p_retcode out varchar2
,p_file_path in varchar2
,p_file_name in varchar2
)
is
g_org_id number := fnd_profile.value('ORG_ID');
g_conc_request_id number := fnd_profile.value('CONC_REQUEST_ID');
cursor cur_podet
is
select vendor_name
,pov.segment1 vendor_number
,povs.VENDOR_SITE_CODE
,povs.ADDRESS_LINE1||' '||povs.ADDRESS_LINE2 address
,povs.country
,poh.SEGMENT1 po_number
from po_vendors pov,
po_headers_all poh,
po_vendor_sites_all povs
where pov.vendor_id = poh.vendor_id
and poh.vendor_site_id = povs.vendor_site_id
and poh.org_id = g_org_id;
v_file utl_file.file_type;
v_file_name varchar2(100) ;

begin
    fnd_file.put_line(fnd_file.log,'Concurrent Request Id => '||p_file_name||'_'||g_conc_request_id||'.txt');
    v_file_name  := p_file_name||'_'||g_conc_request_id||'.txt';
    v_file := utl_file.fopen(p_file_path,v_file_name ,'W');
    for rec_podet in cur_podet
    loop
        begin
        utl_file.PUT_LINE(v_file,
         rec_podet.vendor_name
        ||','||rec_podet.vendor_number
        ||','||rec_podet.VENDOR_SITE_CODE
        ||','||rec_podet.address
        ||','||rec_podet.country
        ||','||rec_podet.po_number
        );
    exception
        when utl_file.invalid_path then
            fnd_file.put_line(fnd_file.log,'Invalid Path');
        when utl_file.invalid_mode  then
            fnd_file.put_line(fnd_file.log,'Invalid Mode');
        when utl_file.invalid_filehandle then
            fnd_file.put_line(fnd_file.log,'Invalid file handle');
        when utl_file.invalid_operation  then
            fnd_file.put_line(fnd_file.log,'Invalid Operation');
        when utl_file.write_error        then
            fnd_file.put_line(fnd_file.log,'Write error');
        when others then
            fnd_file.put_line(fnd_file.log,'exception in loop => '||SQLERRM);
        end;  
    end loop;
    utl_file.FCLOSE(v_file);
exception
    when others then
        fnd_file.put_line(fnd_file.log,'exception in procedure => '||SQLERRM);  
end ;