UTILFILE-INBOUND,OUTBOUND

=================== UTL FILE INBOUND===
USING UTL_FILE PACKAGE(LOAD THE DATA IN TO THE TABLE)
CREATE OR REPLACE package body APPS.xx_po_pu_det_pkg
is
/*
Procedure to read data from flat file
*/
    procedure pur_dat_prc(x_errbuf OUT VARCHAR2
                        ,X_RETCODE OUT VARCHAR2
                        ,P_FILE_PATH IN VARCHAR2
                        ,P_FIL_NAME IN VARCHAR2
                        )
is

v_file_type         utl_file.file_type;
v_data              varchar2(1000);

v_vendor_number         po_vendors.segment1%type;
v_vendor_name           po_vendors.vendor_name%type;
v_vendor_site_code      po_vendor_sites_all.vendor_site_code%type;
v_po_number             po_headers_all.segment1%type;

begin
    v_file_type := utl_file.fopen(P_FILE_PATH,P_FIL_NAME,'R');
    loop
        begin
 --       fnd_file.put_line(fnd_file.output,'Start Loop');
        utl_file.get_line(v_file_type,v_data);
        fnd_file.put_line(fnd_file.output,'Data => '||v_data);
    select substr(v_data,1,instr(v_data,',',1)-1)
    into v_vendor_number
    from dual;
   
    select substr(v_data,instr(v_data,',',1,1)+1,instr(v_data,',',1,2)-(instr(v_data,',',1,1)+1))
    into v_vendor_name
    from dual;

    select substr(v_data,instr(v_data,',',1,2)+1,instr(v_data,',',1,3)-(instr(v_data,',',1,2)+1))
    into v_vendor_site_code
    from dual;

    select substr(v_data,instr(v_data,',',1,3)+1,length(v_data)-(instr(v_data,',',1,3)))
    into v_po_number
    from dual;

    insert into XX_PO_PUR_DET_STG
    values(
    v_vendor_number
    ,v_vendor_name
    ,v_vendor_site_code
    ,v_po_number
    );
   
    exception
        when utl_file.invalid_path then
            fnd_file.put_line(fnd_file.output,'Invalid file path');      
        when utl_file.invalid_mode then
            fnd_file.put_line(fnd_file.output,'Invalid Mode');
        when utl_file.invalid_filehandle then
            fnd_file.put_line(fnd_file.output,'Invalid file handle');
        when utl_file.invalid_operation then
            fnd_file.put_line(fnd_file.output,'Invalid file operation');
        when utl_file.read_error then
            fnd_file.put_line(fnd_file.output,'Read error');
        when no_data_found then
            exit;
        when others then
           fnd_file.put_line(fnd_file.output,'Others exception => '||SQLERRM);    
        end;
    end loop;
--    fnd_file.put_line(fnd_file.output,'after end loop');
    utl_file.fclose(v_file_type);
--    fnd_file.put_line(fnd_file.output,'after close');
exception
    when others then
        fnd_file.put_line(fnd_file.log,'Exception in procedure pur_dat_prc => '||SQLERRM);
end pur_dat_prc;          
end xx_po_pu_det_pkg;
/



===================UTIL FILE 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 ;