create or replace
PACKAGE "FDS_FIN_LOAD" as
procedure LoadUTOpen (iFileID number);
end FDS_FIN_LOAD;

and body for that procedure:

create or replace
PACKAGE BODY "FDS_FIN_LOAD" as

input_file utl_file.file_type;
rsequence number(15, 0);
rcurrent number(12, 0);
rsuccess number(12, 0);
rreject number(25, 0);
txt varchar2(5000);
err varchar2(25);
pos varchar2(10);
val varchar2(40);
nfound number(15, 0);
dupdate date;
vMAXSEQ number;
vSEQ number;
vACCEPT number;
vREJECT number;
vCNT number;
vCNTCOMMIT number := 300000; --300000

batch varchar2(12) := to_char(sysdate, 'YYMMDDHH24MISS');
r binary_integer := 0;
s binary_integer := 0;
f binary_integer := 0;
daction timestamp(6) := sysdate;
vsql varchar2(5000);
loadcnt number(1, 0);
vFUND varchar2(7);
vDirectory varchar2(100);
vFILENAME varchar2(100);

--changes done
strtemp varchar2(2);
DUPDATE12 varchar2(50);

procedure LoadUTOpen (iFileID number)
is
type tzutsfilerow is table of number index by binary_integer;
type tzfundid is table of number index by binary_integer;
type tzscheme is table of varchar2(1) index by binary_integer;
type tzaccountnumber is table of varchar2(9) index by binary_integer;
type tzaccountstatus is table of varchar2(2) index by binary_integer;
type tzpassbooknumber is table of varchar2(9) index by binary_integer;
type tzopenpassbook is table of varchar2(14) index by binary_integer;
type tzcurrpassbook is table of varchar2(14) index by binary_integer;
type tzopencert is table of varchar2(14) index by binary_integer;
type tzcurrcert is table of varchar2(14) index by binary_integer;
type tzopenblocked is table of varchar2(14) index by binary_integer;
type tzcurrblocked is table of varchar2(14) index by binary_integer;
type tzopendividend is table of varchar2(14) index by binary_integer;
type tzcurrdividend is table of varchar2(14) index by binary_integer;
type tzacumdividend is table of varchar2(14) index by binary_integer;
type tzopensalary is table of varchar2(14) index by binary_integer;
type tzcurrsalary is table of varchar2(14) index by binary_integer;
type tzopenbonus is table of varchar2(14) index by binary_integer;
type tzcurrbonus is table of varchar2(14) index by binary_integer;
type tzacumbonus is table of varchar2(14) index by binary_integer;
type tzopenincentive is table of varchar2(14) index by binary_integer;
type tzcurrincentive is table of varchar2(14) index by binary_integer;
type tzlastupdatedate is table of varchar2(8) index by binary_integer;
type tzlasttransdate is table of varchar2(8) index by binary_integer;
type tzlaststatusdate is table of varchar2(8) index by binary_integer;
type tzlastincentivedate is table of varchar2(8) index by binary_integer;
type tzacumtransmission is table of varchar2(14) index by binary_integer;


tccnt tzutsfilerow ;
tcutsfilerow tzutsfilerow ;
tcfundid tzfundid ;
tcscheme tzscheme ;
tcaccountnumber tzaccountnumber ;
tcaccountstatus tzaccountstatus ;
tcpassbooknumber tzpassbooknumber ;
tcopenpassbook tzopenpassbook ;
tccurrpassbook tzcurrpassbook ;
tcopencert tzopencert ;
tccurrcert tzcurrcert ;
tcopenblocked tzopenblocked ;
tccurrblocked tzcurrblocked ;
tcopendividend tzopendividend ;
tccurrdividend tzcurrdividend ;
tcacumdividend tzacumdividend ;
tcopensalary tzopensalary ;
tccurrsalary tzcurrsalary ;
tcopenbonus tzopenbonus ;
tccurrbonus tzcurrbonus ;
tcacumbonus tzacumbonus ;
tcopenincentive tzopenincentive ;
tccurrincentive tzcurrincentive ;
tclastupdatedate tzlastupdatedate ;
tclasttransdate tzlasttransdate ;
tclaststatusdate tzlaststatusdate ;
tclastincentivedate tzlastincentivedate ;
tcacumtransmission tzacumtransmission ;

type tyutsfilerow is table of UTOPEN.UTSFILEROW%TYPE index by binary_integer;
type tyfundid is table of UTOPEN.FUNDID%TYPE index by binary_integer;
type tyscheme is table of UTOPEN.SCHEME%TYPE index by binary_integer;
type tyaccountnumber is table of UTOPEN.ACCOUNTNUMBER%TYPE index by binary_integer;
type tyaccountstatus is table of UTOPEN.ACCOUNTSTATUS%TYPE index by binary_integer;
type typassbooknumber is table of UTOPEN.PASSBOOKNUMBER%TYPE index by binary_integer;
type tyopenpassbook is table of UTOPEN.OPENPASSBOOK%TYPE index by binary_integer;
type tycurrpassbook is table of UTOPEN.CURRPASSBOOK%TYPE index by binary_integer;
type tyopencert is table of UTOPEN.OPENCERT%TYPE index by binary_integer;
type tycurrcert is table of UTOPEN.CURRCERT%TYPE index by binary_integer;
type tyopenblocked is table of UTOPEN.OPENBLOCKED%TYPE index by binary_integer;
type tycurrblocked is table of UTOPEN.CURRBLOCKED%TYPE index by binary_integer;
type tyopendividend is table of UTOPEN.OPENDIVIDEND%TYPE index by binary_integer;
type tycurrdividend is table of UTOPEN.CURRDIVIDEND%TYPE index by binary_integer;
type tyacumdividend is table of UTOPEN.ACUMDIVIDEND%TYPE index by binary_integer;
type tyopensalary is table of UTOPEN.OPENSALARY%TYPE index by binary_integer;
type tycurrsalary is table of UTOPEN.CURRSALARY%TYPE index by binary_integer;
type tyopenbonus is table of UTOPEN.OPENBONUS%TYPE index by binary_integer;
type tycurrbonus is table of UTOPEN.CURRBONUS%TYPE index by binary_integer;
type tyacumbonus is table of UTOPEN.ACUMBONUS%TYPE index by binary_integer;
type tyopenincentive is table of UTOPEN.OPENINCENTIVE%TYPE index by binary_integer;
type tycurrincentive is table of UTOPEN.CURRINCENTIVE%TYPE index by binary_integer;
type tylastupdatedate is table of UTOPEN.LASTUPDATEDATE%TYPE index by binary_integer;
type tylasttransdate is table of UTOPEN.LASTTRANSDATE%TYPE index by binary_integer;
type tylaststatusdate is table of UTOPEN.LASTSTATUSDATE%TYPE index by binary_integer;
type tylastincentivedate is table of UTOPEN.LASTINCENTIVEDATE%TYPE index by binary_integer;
type tyacumtransmission is table of UTOPEN.ACUMTRANSMISSION%TYPE index by binary_integer;

tbcnt tyutsfilerow ;
tbutsfilerow tyutsfilerow ;
tbfundid tyfundid ;
tbscheme tyscheme ;
tbaccountnumber tyaccountnumber ;
tbaccountstatus tyaccountstatus ;
tbpassbooknumber typassbooknumber ;
tbopenpassbook tyopenpassbook ;
tbcurrpassbook tycurrpassbook ;
tbopencert tyopencert ;
tbcurrcert tycurrcert ;
tbopenblocked tyopenblocked ;
tbcurrblocked tycurrblocked ;
tbopendividend tyopendividend ;
tbcurrdividend tycurrdividend ;
tbacumdividend tyacumdividend ;
tbopensalary tyopensalary ;
tbcurrsalary tycurrsalary ;
tbopenbonus tyopenbonus ;
tbcurrbonus tycurrbonus ;
tbacumbonus tyacumbonus ;
tbopenincentive tyopenincentive ;
tbcurrincentive tycurrincentive ;
tblastupdatedate tylastupdatedate ;
tblasttransdate tylasttransdate ;
tblaststatusdate tylaststatusdate ;
tblastincentivedate tylastincentivedate ;
tbacumtransmission tyacumtransmission ;

vFUND varchar2(7);
vDirectory varchar2(100);
vFILENAME varchar2(100);


begin
select fund,filename into vFUND,vFILENAME from ADMFINFILE where fileid = iFILEID;
DBMS_OUTPUT.PUT_LINE(rpad('+ + + + + Loading UTOPEN data', 60, ' +'));
select count(1) into loadcnt from admloadingstatus where readingsource = Vfilename;
if loadcnt > 0 then
DBMS_OUTPUT.PUT_LINE(rpad('+ + + + + ' || vFILENAME || ' already loaded in previous execution...', 60, ' +'));
DBMS_OUTPUT.PUT_LINE(rpad('+ + + + + Procedure terminated!', 60, ' +'));
return;
end if;

rcurrent := 0;
rsuccess := 0;
rreject := 0;
r := 1;

vDirectory := substr(vFILENAME,1,regexp_instr(vFILENAME,'/',1,length(regexp_replace(vFILENAME, '[^/]'))));
vFileName := substr(vFILENAME,regexp_instr(vFILENAME,'/',1,length(regexp_replace(vFILENAME, '[^/]'))) +1);

input_file := utl_file.fopen (vDirectory, vFileName, 'R');

ora_pkref.pDoAudit('Begin UTOPEN data...', 'UTOPEN', vFUND );
loop
utl_file.get_line(input_file, txt);
rcurrent := rcurrent + 1;
s := rsuccess;
err := '';
pos := '';
val := '';
tbcnt(s) := r;
tbutsfilerow(s) := r;
tccnt(s) := r;
tcutsfilerow(s) := r;
/* assign value into variables by reading it's position */
tbfundid(s) := ora_pkRef.fGetFundId(vfund);
tcfundid(s) := ora_pkRef.fGetFundId(vfund);


tcscheme(s) := trim(substr(txt, 10, 1 ));
if err is null then
begin
tbscheme(s) := trim(substr(txt, 10, 1));
exception
when others then
err := 'SCHEME';
pos := ' 10, 1';
val := trim(substr(txt, 10, 1));
end;
end if;

tcaccountnumber(s) := trim(substr(txt, 1, 9));
if err is null then
begin
tbaccountnumber(s) := trim(substr(txt, 1, 9));
exception
when others then
err := 'ACCOUNT NUMBER';
pos := ' 1, 9';
val := trim(substr(txt, 1, 9));
end;
end if;

tcaccountstatus(s) := trim(substr(txt, 20, 2));
if err is null then
begin
tbaccountstatus(s) := to_number(trim(substr(txt, 20, 2)));
exception
when others then
err := 'ACCOUNT STATUS';
pos := ' 20, 2';
val := trim(substr(txt, 20, 2));
end;
end if;

tcpassbooknumber(s) := trim(substr(txt, 11, 9));
if err is null then
begin
tbpassbooknumber(s) := trim(substr(txt, 11, 9));
exception
when others then
err := 'PASSBOOK NUMBER';
pos := ' 11, 9';
val := trim(substr(txt, 11, 9));
end;
end if;

tcopenpassbook(s) := trim(substr(txt, 22, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 22, 14)), ':digit:') then
tbopenpassbook(s) := null;

else
tbopenpassbook(s) := to_number(trim(substr(txt, 22, 14))) / 100;
end if;
exception
when others then
err := 'OPEN PASSBOOK';
pos := ' 22, 14';
val := trim(substr(txt, 22, 14));
end;
end if;

tccurrpassbook(s) := trim(substr(txt, 120, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 120, 14)), ':digit:') then
tbcurrpassbook(s) := null;
else
tbcurrpassbook(s) := to_number(trim(substr(txt, 120, 14))) / 100;
end if;
exception
when others then
err := 'CURR PASSBOOK';
pos := '120, 14';
val := trim(substr(txt, 120, 14));
end;
end if;

tcopencert(s):= trim(substr(txt, 36, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 36, 14)), ':digit:') then
tbopencert(s) := null;

else
tbopencert(s) := to_number(trim(substr(txt, 36, 14))) / 100;
end if;
exception
when others then
err := 'OPEN FREE CERT';
pos := ' 36, 14';
val := trim(substr(txt, 36, 14));
end;
end if;

tccurrcert(s) := trim(substr(txt, 134, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 134, 14)), ':digit:') then
tbcurrcert(s) := null;
else
tbcurrcert(s) := to_number(trim(substr(txt, 134, 14))) / 100;
end if;
exception
when others then
err := 'CURR FREE CERT';
pos := '134, 14';
val := trim(substr(txt, 134, 14));
end;
end if;

tcopenblocked(s) := trim(substr(txt, 50, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 50, 14)), ':digit:') then
tbopenblocked(s) := null;
else
tbopenblocked(s) := to_number(trim(substr(txt, 50, 14))) / 100;
end if;
exception
when others then
err := 'OPEN BLOCKED';
pos := ' 50, 14';
val := trim(substr(txt, 50, 14));
end;
end if;

tccurrblocked(s) := trim(substr(txt, 148, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 148, 14)), ':digit:') then
tbcurrblocked(s) := null;
else
tbcurrblocked(s) := to_number(trim(substr(txt, 148, 14))) / 100;
end if;
exception
when others then
err := 'CURR BLOCKED';
pos := '148, 14';
val := trim(substr(txt, 148, 14));
end;
end if;

tcopendividend(s) := trim(substr(txt, 64, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 64, 14)), ':digit:') then
tbopendividend(s) := null;
else
tbopendividend(s) := to_number(trim(substr(txt, 64, 14))) / 100;
end if;
exception
when others then
err := 'OPEN DIVIDEND';
pos := ' 64, 14';
val := trim(substr(txt, 64, 14));
end;
end if;

tccurrdividend(s) := trim(substr(txt, 176, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 176, 14)), ':digit:') then
tbcurrdividend(s) := null;
else
tbcurrdividend(s) := to_number(trim(substr(txt, 176, 14))) / 100;
end if;
exception
when others then
err := 'CURR DIVIDEND';
pos := '176, 14';
val := trim(substr(txt, 176, 14));
end;
end if;

tcacumdividend(s) := trim(substr(txt, 218, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 218, 14)), ':digit:') then
tbacumdividend(s) := null;
else
tbacumdividend(s) := to_number(trim(substr(txt, 218, 14))) / 100;
end if;
exception
when others then
err := 'ACCUM DIVIDEND';
pos := '218, 14';
val := trim(substr(txt, 218, 14));
end;
end if;

tcopensalary(s) := trim(substr(txt, 78, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 78, 14)), ':digit:') then
tbopensalary(s) := null;
else
tbopensalary(s) := to_number(trim(substr(txt, 78, 14))) / 100;
end if;
exception
when others then
err := 'OPEN SALARY';
pos := ' 78, 14';
val := trim(substr(txt, 78, 14));
end;
end if;

tccurrsalary(s) := trim(substr(txt, 162, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 162, 14)), ':digit:') then
tbcurrsalary(s) := null;
else
tbcurrsalary(s) := to_number(trim(substr(txt, 162, 14))) / 100;
end if;
exception
when others then
err := 'CURR SALARY';
pos := '162, 14';
val := trim(substr(txt, 162, 14));
end;
end if;

tcopenbonus(s) := trim(substr(txt, 92, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 92, 14)), ':digit:') then
tbopenbonus(s) := null;
else
tbopenbonus(s) := to_number(trim(substr(txt, 92, 14))) / 100;
end if;
exception
when others then
err := 'OPEN BONUS';
pos := ' 92, 14';
val := trim(substr(txt, 92, 14));
end;
end if;

tccurrbonus(s) := trim(substr(txt, 190, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 190, 14)), ':digit:') then
tbcurrbonus(s) := null;
else
tbcurrbonus(s) := to_number(trim(substr(txt, 190, 14))) / 100;
end if;
exception
when others then
err := 'CURR BONUS';
pos := '190, 14';
val := trim(substr(txt, 190, 14));
end;
end if;

tcacumbonus(s) := trim(substr(txt, 232, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 232, 14)), ':digit:') then
tbacumbonus(s) := null;
else
tbacumbonus(s) := to_number(trim(substr(txt, 232, 14))) / 100;
end if;
exception
when others then
err := 'ACCUM BONUS';
pos := '232, 14';
val := trim(substr(txt, 232, 14));
end;
end if;

tcopenincentive(s) := trim(substr(txt, 106, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 106, 14)), ':digit:') then
tbopenincentive(s) := null;
else
tbopenincentive(s) := to_number(trim(substr(txt, 106, 14))) / 100;
end if;
exception
when others then
err := 'OPEN INCENTIVE';
pos := '106, 14';
val := trim(substr(txt, 106, 14));
end;
end if;

tccurrincentive(s) := trim(substr(txt, 204, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 204, 14)), ':digit:') then
tbcurrincentive(s) := null;
else
tbcurrincentive(s) := to_number(trim(substr(txt, 204, 14))) / 100;
end if;
exception
when others then
err := 'CURR INCENTIVE';
pos := '204, 14';
val := trim(substr(txt, 204, 14));
end;
end if;

tclastupdatedate(s) := trim(substr(txt, 260, 8));
if err is null then
begin
tblastupdatedate(s) := to_date(substr(txt, 260, 8), 'YYYYMMDD');
exception
when others then
tblastupdatedate(s) := ora_pkref.fGetFundDist(vfund, 2009) - 1;
/* err := 'LAST UPDATE DATE';
pos := '260, 8';
val := trim(substr(txt, 260, 8));
*/ end;
end if;

tclasttransdate(s) := trim(substr(txt, 268, 8));
if err is null then
begin
tblasttransdate(s) := to_date(substr(txt, 268, 8), 'YYYYMMDD');
exception
when others then
tblasttransdate(s) := ora_pkref.fGetFundDist(vfund, 2009) - 1;
/* err := 'LAST TRANS DATE';
pos := '268, 8';
val := trim(substr(txt, 268, 8));
*/ end;
end if;

tclaststatusdate(s) := trim(substr(txt, 276, 8));
if err is null then
begin
tblaststatusdate(s) := to_date(substr(txt, 276, 8), 'YYYYMMDD');
exception
when others then
tblaststatusdate(s) := ora_pkref.fGetFundDist(vfund, 2009) - 1;
/* err := 'LAST STATUS DATE';
pos := '276, 8';
val := trim(substr(txt, 276, 8));
*/ end;
end if;

tclastincentivedate(s) := trim(substr(txt, 284, 8));
if err is null then
begin
tblastincentivedate(s) := to_date(substr(txt, 284, 8), 'YYYYMMDD');
exception
when others then
tblastincentivedate(s) := ora_pkref.fGetFundDist(vfund, 2009) - 1;
/* err := 'LAST INCENTIVE DATE';
pos := '284, 8';
val := trim(substr(txt, 284, 8));
*/ end;
end if;

tcacumtransmission(s) := trim(substr(txt, 246, 14));
if err is null then
begin
if not regexp_like(trim(substr(txt, 246, 14)), ':digit:') then
tbacumtransmission(s) := null;
else
tbacumtransmission(s) := to_number(trim(substr(txt, 246, 14))) / 100;
end if;
exception
when others then
err := 'ACCUM TRANSMISSION';
pos := '246, 14';
val := trim(substr(txt, 246, 14));
end;
end if;

if err is not null then
insert into utopen_audit ( execbatchid,
executedate,
executetime,
executefund,
executename,
executeposn,
executernum,
executevals,
executeuser)
values ( batch,
to_char(sysdate, 'DD-MON-YYYY'),
to_char(sysdate, 'DD-MON-YYYY HH12:MI:SS'),
vfund,
err,
pos,
rcurrent,
val,
'MIGRATION');
commit;
rreject := rreject + 1;
else
r := r + 1;
rsuccess := rsuccess + 1;
end if;
end loop;
exception
/* the loop will definitely produce exception when the cursor reach End Of File
so we do all finalization in the exception to update BDEC_IMPORT and mark last loaded file with current datetime */

when others then

ora_pkref.pDoAudit('Start process of UTOPEN data movement...', 'UTOPEN', vFUND );
execute immediate 'TRUNCATE TABLE UOP'|| vfund || '';
execute immediate 'TRUNCATE TABLE UTOPEN' || vfund || '';
execute immediate 'TRUNCATE TABLE UOPUTS' || vfund || '';
select fund into vFUND from admfinfile where fileid = iFILEID;
execute immediate 'create or replace synonym UOP for UOP'||vFUND;
execute immediate 'create or replace synonym UTOPEN for UTOPEN'||vFUND;
execute immediate 'create or replace synonym UOPUTS for UOPUTS'||vFUND;


select nvl(max(sequenceno),0) into rsequence from UTOPEN;

insert into admloadingstatus ( executetime, readingsource, writtingtable, startingsequence, endingsequence, totalsuccess, totalreject)
values ( sysdate, vFileName, 'UTOPENLOAD', rsequence + 1, (rsequence + rsuccess + rreject), rsuccess, rreject);

update ADMFINFILE set regdate= sysdate, fundtype= 'UOP', loaddate = sysdate, START_SEQ = rsequence + 1, END_SEQ = rsequence + rsuccess + rreject,
UTS_ACCEPT = rsequence + rsuccess + rreject, UTS_REJECT = rreject, LOAD_REC# = rsequence + rsuccess + rreject where fileid = iFILEID;

commit;

vCNT := 1;
dbms_output.put_line('value of 1st .... '||tccnt(0)||' last ...'||r);
for d in tccnt.first..tccnt.last loop

dbms_output.put_line('value of d .... '||d);

insert /*+ append nologging parallel */ into UOP
( sequenceno, utsfilerow, fundid, scheme, accountnumber, accountstatus,
passbooknumber, openpassbook, currpassbook, opencert, currcert,
openblocked, currblocked, opendividend, currdividend, acumdividend,
opensalary, currsalary, openbonus, currbonus, acumbonus,
openincentive, currincentive, lastupdatedate, lasttransdate,
laststatusdate, lastincentivedate, acumtransmission )
values
( rsequence + tccnt(d), tcutsfilerow(d), tcfundid(d), tcscheme(d), tcaccountnumber(d), tcaccountstatus(d),
tcpassbooknumber(d), tcopenpassbook(d), tccurrpassbook(d), tcopencert(d), tccurrcert(d),
tcopenblocked(d), tccurrblocked(d), tcopendividend(d), tccurrdividend(d), tcacumdividend(d),
tcopensalary(d), tccurrsalary(d), tcopenbonus(d), tccurrbonus(d), tcacumbonus(d),
tcopenincentive(d), tccurrincentive(d), tclastupdatedate(d), tclasttransdate(d),
tclaststatusdate(d), tclastincentivedate(d), tcacumtransmission(d) );


insert into UOPUTS ( UTSFILEROW, SCHEME, UTSFILEID, TEXT)
values ( tcutsfilerow(d), tcscheme(d), iFILEID, txt);


if ( tbscheme.exists(d) ) then
insert /*+ append nologging parallel */ into UTOPEN
( sequenceno, utsfilerow, fundid, scheme, accountnumber, accountstatus,
passbooknumber, openpassbook, currpassbook, opencert, currcert,
openblocked, currblocked, opendividend, currdividend, acumdividend,
opensalary, currsalary, openbonus, currbonus, acumbonus,
openincentive, currincentive, lastupdatedate, lasttransdate,
laststatusdate, lastincentivedate, acumtransmission )
values
( rsequence + tbcnt(d), tbutsfilerow(d), tbfundid(d), tbscheme(d), tbaccountnumber(d), tbaccountstatus(d),
tbpassbooknumber(d), tbopenpassbook(d), tbcurrpassbook(d), tbopencert(d), tbcurrcert(d),
tbopenblocked(d), tbcurrblocked(d), tbopendividend(d), tbcurrdividend(d), tbacumdividend(d),
tbopensalary(d), tbcurrsalary(d), tbopenbonus(d), tbcurrbonus(d), tbacumbonus(d),
tbopenincentive(d), tbcurrincentive(d), tblastupdatedate(d), tblasttransdate(d),
tblaststatusdate(d), tblastincentivedate(d), tbacumtransmission(d) );
dbms_output.put_line('Finish insert into UOP .... ');
end if;

vCNT := vCNT + 1;
if ( vCNT = vCNTCOMMIT) then
ora_pkref.pDoAudit('Commit UTOPEN data extraction record# of '||d, 'UTOPEN', vFUND );
vCNT := 1;
end if;

end loop;
ora_pkref.pDoAudit('Complete UTOPEN data extraction', 'UTOPEN', vFUND );
ora_pkref.pDoAudit('Complete UTOPEN data update status', 'UTOPEN', vFUND );


utl_file.fclose(input_file);

end LoadUTOpen;
end fds_fin_load;