create or replace procedure p_TrfToMain_Fo(vdate in date, zexchange in varchar2,vcontracode in varchar2) is vSql varchar2(4000) :=''; vTable varchar2(30); type vtrade_date is table of sysadm.fotrnmast.trade_date%type; type vtrade_no is table of sysadm.fotrnmast.tradeno%type; type vtrade_status is table of sysadm.fotrnmast.tradestatus%type; type vclient_code is table of sysadm.fotrnmast.clientcode%type; type vinstrument_type is table of sysadm.fotrnmast.instrument_type%type; type vsymbol is table of sysadm.fotrnmast.symbol%type; type vexpirydate is table of sysadm.fotrnmast.expirydate%type; type vstrikeprice is table of sysadm.fotrnmast.strikeprice%type; type voptiontype is table of sysadm.fotrnmast.optiontype%type; type vsecurityname is table of sysadm.fotrnmast.securityname%type; type vuserid is table of sysadm.fotrnmast.userid%type; type vbranchid is table of sysadm.fotrnmast.branchid%type; type vbuysellind is table of sysadm.fotrnmast.buysellind%type; type vtrn_qty is table of sysadm.fotrnmast.trn_qty%type; type vtradeprice is table of sysadm.fotrnmast.tradeprice%type; type vopenclosflag is table of sysadm.fotrnmast.openclosflag%type; type vtrade_time is table of sysadm.fotrnmast.trade_time%type; -- custflag type vorderno is table of sysadm.fotrnmast.orderno%type; type vpostingflag is table of sysadm.fotrnmast.postingflag%type; type vorgclientid is table of sysadm.fotrnmast.orgclientid%type; type vtmid is table of sysadm.fotrnmast.tmid%type; type vremarkpro is table of sysadm.fotrnmast.remarkpro%type; type vorder_time is table of sysadm.fotrnmast.order_time%type; type vctcltermid is table of sysadm.fotrnmast.foctcltermid%type; type vtradetype is table of sysadm.fotrnmast.tradetype%type; type vexchange is table of sysadm.fotrnmast.exchange%type; type vtrnlot is table of number(15,2); type vcustflag is table of varchar2(10); lcustflag vcustflag; ltrnlot vtrnlot; lexchange vexchange; ltradetype vtradetype; lctcltermid vctcltermid; lorder_time vorder_time; lremarkpro vremarkpro; ltmid vtmid; lorgclientid vorgclientid; lpostingflag vpostingflag; lorderno vorderno; ltrade_time vtrade_time; lopenclosflag vopenclosflag; ltradeprice vtradeprice; lnetprice vtradeprice; ltrn_qty vtrn_qty; lbuysellind vbuysellind; lbranchid vbranchid; luserid vuserid; lsecurityname vsecurityname; loptiontype voptiontype; lstrikeprice vstrikeprice; lexpirydate vexpirydate; ltradedate vtrade_date; ltradeno vtrade_no; ltradestatus vtrade_status; lclientcode vclient_code; linstrument_type vinstrument_type; lsymbol vsymbol; c1 Fpkg1300.cursor_type; type vsequence is table of number(14); type vmkt_type is table of varchar2(2); type vbooktype is table of varchar2(2); -- type vcustflag is table of varchar2(15); type vtrn_brok is table of number(14); ltrn_brok vtrn_brok; ldiv vtrn_brok; lbrokround vtrn_brok; ltrn_slno vsequence; lbooktype vbooktype; lbooktypename vbooktype; lmkt_type vmkt_type; lf_broktype vbooktype; ltrn_lot vsequence; lcustodial_flag vcustflag; sqlExchange varchar2(100); begin sqlExchange :=' AND instrument_type IN (''FUTSTK'',''OPTSTK'',''FUTIDX'',''OPTIDX'',''FUTIVX'')'; if substr(zexchange,1,1)='B' then sqlExchange :=' AND instrument_type IN (''INDFUT'',''INDOPT'',''EQUFUT'',''EQUOPT'')'; Elsif substr(zexchange,1,1)='M' then sqlExchange :=' AND instrument_type IN (''FUMSTK'',''OPMSTK'',''FUMIDX'',''OPMIDX'')'; end if; -- vTable := 'FNO29102015' ; vTable := 'FNO'||to_char(vdate,'ddMMyyyy'); dbms_output.put_line('Getting data from Table : '||vTable); if substr(zexchange,1,1) ='N' then null; end if; vSql :='UPDATE sysadm.'||vTable||' SET trn_slno=sysadm.sysdbsequence.nextval where trn_slno is null'; execute immediate vSql; vSql := 'SELECT trade_date, tradeno, tradestatus, instrument_type, symbol,expirydate,strikeprice, optiontype, securityname, ''RL'',''RL'',''01'', userid,branchid,buysellind,trn_qty,tradeprice,tradeprice,clientcode, openclosflag,trade_time, orderno,trn_slno, postingflag,orgclientid,tmid, 0,0,0, remarkpro, order_time, NULL, ctcltermid,tradetype,exchange,trnlot,custflag FROM sysadm.'||vTable||' WHERE tr_status IS NULL AND (NVL(custflag,''N'') =''N'' OR (NVL(custflag,''N'')=''Y'' AND contracode IS NOT NULL)) '||sqlExchange||' '; open c1 for vSql; loop fetch c1 bulk collect into ltradedate, ltradeno, ltradestatus, linstrument_type, lsymbol, lexpirydate, lstrikeprice, loptiontype, lsecurityname, lbooktype,lbooktypename,lmkt_type, luserid,lbranchid,lbuysellind,ltrn_qty,ltradeprice,lnetprice,lclientcode, lopenclosflag,ltrade_time, lorderno,ltrn_slno,lpostingflag,lorgclientid,ltmid, ltrn_brok,ldiv,lbrokround, lremarkpro, lorder_time,lf_broktype,lctcltermid,ltradetype,lexchange,ltrn_lot,lcustodial_flag limit 250000; forall i in 1..ltradedate.count -- Client Record insert into sysadm.fotrnmast (trade_date, tradeno, tradestatus, instrument_type, symbol,expirydate,strikeprice, optiontype, securityname, booktype,booktypename,mkt_type,userid,branchid,buysellind,trn_qty, tradeprice,netprice,clientcode, openclosflag,trade_time,orderno,trn_slno,postingflag,orgclientid,tmid, trn_brok,div,brokround, remarkpro, order_time,f_broktype,foctcltermid,tradetype,exchange,trn_lot,custodial_flag) values (ltradedate(i), ltradeno(i),ltradestatus(i),linstrument_type(i), lsymbol(i), lexpirydate(i), lstrikeprice(i),loptiontype(i),lsecurityname(i), lbooktype(i),lbooktypename(i),lmkt_type(i),luserid(i),lbranchid(i),lbuysellind(i),ltrn_qty(i), ltradeprice(i),lnetprice(i),lclientcode(i),lopenclosflag(i),ltrade_time(i),lorderno(i), ltrn_slno(i),lpostingflag(i),lorgclientid(i),ltmid(i),ltrn_brok(i),ldiv(i),lbrokround(i), lremarkpro(i),lorder_time(i),lf_broktype(i),lctcltermid(i),ltradetype(i),lexchange(i), ltrn_lot(i),lcustodial_flag(i) ); commit; -- End of Client Record exit when c1%notfound; end loop; close c1; -- End of Client Record Insertion -- Start of Contra Code Insertion vSql := 'SELECT trade_date, tradeno, tradestatus, instrument_type, symbol,expirydate,strikeprice, optiontype, securityname, ''RL'',''RL'',''01'', userid,branchid,buysellind,-1*trn_qty,tradeprice,tradeprice,nvl(contracode,'''||vcontracode||'''), openclosflag,trade_time, orderno,trn_slno, postingflag,orgclientid,tmid, 0,0,0, remarkpro, order_time, NULL, ctcltermid,tradetype,exchange,trnlot,custflag FROM sysadm.'||vTable||' WHERE tr_status IS NULL AND (NVL(custflag,''N'') =''N'' OR (NVL(custflag,''N'')=''Y'' AND contracode IS NOT NULL)) '||sqlExchange||' '; open c1 for vSql; loop fetch c1 bulk collect into ltradedate, ltradeno, ltradestatus, linstrument_type, lsymbol, lexpirydate, lstrikeprice, loptiontype, lsecurityname, lbooktype,lbooktypename,lmkt_type, luserid,lbranchid,lbuysellind,ltrn_qty,ltradeprice,lnetprice,lclientcode, lopenclosflag,ltrade_time, lorderno,ltrn_slno,lpostingflag,lorgclientid,ltmid, ltrn_brok,ldiv,lbrokround, lremarkpro, lorder_time,lf_broktype,lctcltermid,ltradetype,lexchange,ltrn_lot,lcustodial_flag limit 250000; forall i in 1..ltradedate.count -- Client Record insert into sysadm.fotrnmast (trade_date, tradeno, tradestatus, instrument_type, symbol,expirydate,strikeprice, optiontype, securityname, booktype,booktypename,mkt_type,userid,branchid,buysellind,trn_qty, tradeprice,netprice,clientcode, openclosflag,trade_time,orderno,trn_slno,postingflag,orgclientid,tmid, trn_brok,div,brokround, remarkpro, order_time,f_broktype,foctcltermid,tradetype,exchange,trn_lot,custodial_flag) values (ltradedate(i), ltradeno(i),ltradestatus(i),linstrument_type(i), lsymbol(i), lexpirydate(i), lstrikeprice(i),loptiontype(i),lsecurityname(i), lbooktype(i),lbooktypename(i),lmkt_type(i),luserid(i),lbranchid(i),lbuysellind(i),ltrn_qty(i), ltradeprice(i),lnetprice(i),lclientcode(i),lopenclosflag(i),ltrade_time(i),lorderno(i), ltrn_slno(i),lpostingflag(i),lorgclientid(i),ltmid(i),ltrn_brok(i),ldiv(i),lbrokround(i), lremarkpro(i),lorder_time(i),lf_broktype(i),lctcltermid(i),ltradetype(i),lexchange(i), ltrn_lot(i),lcustodial_flag(i) ); commit; -- End of Client Record exit when c1%notfound; end loop; close c1; -- End of Contra Code Insertion commit; end p_TrfToMain_Fo;