USING UTL_FILE PACKAGE(LOAD THE DATA IN TO THE TABLE)

 

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;
/