Loading valid data into base table(s) without using sql*loader & staging table

USING UTL_FILE LOADING & VALIDATING THE FLAT FILE DATA AND INSERTING INTO BASE TABLES WITH api approche
---------------------------------------------------------------------------------------------------------------

create or replace procedure FRD_SUPP_CONV_PROD
(V_ERRBUF OUT VARCHAR2,V_RETCODE OUT VARCHAR2) is
v_file_type  utl_file.file_type;
v_location      VARCHAR2(90):='C:\temp';
v_filename      VARCHAR2(90):='Supplier_Data.txt';
v_open_mode     VARCHAR2(90):='r';
v_data          varchar2(4000);
v_pipe1  number;
v_pipe2  number;
v_pipe3  number;
v_pipe4  number;
v_pipe5  number;
v_VENDOR_NUMBER VARCHAR2(90);
v_VENDOR_NAME VARCHAR2(90);
v_SHIP_TO_LOCATION VARCHAR2(90);
v_BILL_TO_LOCATION VARCHAR2(90);
v_INVOICE_CURRENCY VARCHAR2(90);
v_PAYMENT_CURRENCY VARCHAR2(90);
v_cnt number:=0;
v_valid_flag varchar2(90);
v_dup_cnt number;
v_ship_to_loc_id number;
v_bill_to_loc_id number;
v_invcurr_cnt number:=0;
v_paycurr_cnt number:=0;
x_return_status        VARCHAR2(90);
    x_msg_count         NUMBER;
    x_msg_data        VARCHAR2(90);
 p_vendor_rec  AP_VENDOR_PUB_PKG.r_vendor_rec_type;
 x_vendor_id   AP_SUPPLIERS.VENDOR_ID%TYPE;
 x_party_id   HZ_PARTIES.PARTY_ID%TYPE;
begin
v_file_type:=utl_file.fopen(location       =>v_location,
                 filename     =>v_filename,
                 open_mode    =>v_open_mode);
execute immediate 'truncate table FRD_SUPP_CONV_STG';
loop
begin
v_cnt:=v_cnt+1;
begin
utl_file.get_line(v_file_type,v_data);
dbms_output.put_line(v_data);
v_pipe1:=instr(v_data,'|',1,1);
v_pipe2:=instr(v_data,'|',1,2);
v_pipe3:=instr(v_data,'|',1,3);
v_pipe4:=instr(v_data,'|',1,4);
v_pipe5:=instr(v_data,'|',1,5);
v_vendor_number:=substr(v_data,1,v_pipe1-1);
v_VENDOR_NAME:=substr(v_data,v_pipe1+1,v_pipe2-v_pipe1-1);
v_SHIP_TO_LOCATION:=substr(v_data,v_pipe2+1,v_pipe3-v_pipe2-1);
v_BILL_TO_LOCATION:=substr(v_data,v_pipe3+1,v_pipe4-v_pipe3-1);
v_INVOICE_CURRENCY:=substr(v_data,v_pipe4+1,v_pipe5-v_pipe4-1);
v_PAYMENT_CURRENCY:=substr(v_data,v_pipe5+1);
--DBMS_OUTPUT.PUT_LINE(v_vendor_name || v_valid_flag);
if v_cnt>1 then
/*insert into FRD_SUPP_CONV_STG(VENDOR_NUMBER ,
                               VENDOR_NAME ,
                               SHIP_TO_LOCATION ,
                               BILL_TO_LOCATION ,
                               INVOICE_CURRENCY ,
                               PAYMENT_CURRENCY )
                values(v_VENDOR_NUMBER ,
                               v_VENDOR_NAME ,
                               v_SHIP_TO_LOCATION ,
                               v_BILL_TO_LOCATION ,
                               v_INVOICE_CURRENCY ,
                            v_PAYMENT_CURRENCY ); */
v_valid_flag:='Y';
--DBMS_OUTPUT.PUT_LINE(v_vendor_name || v_valid_flag);
/**************validation for vendor_name starts here*************/
select count(*) into v_dup_cnt
from ap_suppliers
where upper(vendor_name)=upper(v_VENDOR_NAME);
if v_dup_cnt>0 then
v_valid_flag:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_cnt||'   '||v_vendor_name||'    '||'duplicate vendor');
end if;
/**************validation for vendor_name ends here*************/

/**************validation for invoice currency starts here*************/
select count(*) into v_invcurr_cnt
from fnd_currencies
where upper(currency_code)=upper(v_INVOICE_CURRENCY);
if v_invcurr_cnt=0 then
v_valid_flag:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_cnt||'   '||v_INVOICE_CURRENCY||'    '||'invalid invoice currency');
end if;
/**************validation for invoice currency  ends here*************/
/**************validation for payment currency starts here*************/
select count(*) into v_paycurr_cnt
from fnd_currencies
where upper(currency_code)=upper(v_payment_CURRENCY);
if v_paycurr_cnt=0 then
v_valid_flag:='N';
FND_FILE.PUT_LINE(FND_FILE.LOG,v_cnt||'   '||v_payment_CURRENCY||'    '||'invalid payment currency');
end if;
/**************validation for invoice currency  ends here*************/
DBMS_OUTPUT.PUT_LINE(v_vendor_name || v_valid_flag);
p_vendor_rec.SEGMENT1:=v_vendor_number;
p_vendor_rec.VENDOR_NAME:=v_VENDOR_NAME;
--p_vendor_rec.SHIP_TO_LOCATION_ID:=v_ship_to_loc_id;
--p_vendor_rec.BILL_TO_LOCATION_ID:=v_BILL_to_loc_id;
--p_vendor_rec.INVOICE_CURRENCY_CODE:=v_vendor_number;
--p_vendor_rec.PAYMENT_CURRENCY_CODE:=v_vendor_number;
AP_VENDOR_PUB_PKG.Create_Vendor
(     p_api_version           =>1.0,
      p_init_msg_list        => FND_API.G_TRUE,
    p_commit            => FND_API.G_TRUE,
    p_validation_level    => FND_API.G_VALID_LEVEL_FULL,
    x_return_status       =>x_return_status,
    x_msg_count        =>x_msg_count,
    x_msg_data       =>x_msg_data,
 p_vendor_rec  =>p_vendor_rec,
 x_vendor_id  =>x_vendor_id,
 x_party_id  => x_party_id);
DBMS_OUTPUT.PUT_LINE(v_vendor_name || x_return_status);
COMMIT;
end if;
exception
when no_data_found then
exit;
end;
end;
end loop;
commit;
end LOAD_UTL;
/