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 ;
