GetCalNseTaxFO(teamcode,tradestatus,instrument_type||'~'||f.exch||'~~'||to_char(f.expirydate,'ddMONyyyy'),symbol,optiontype,strikeprice,trade_date,trn_qty, tradeprice,adj_flag,tradetype) create or replace procedure P_PerForGetCalNseTaxFO( vclient in varchar2, vscrip in varchar2, vstn in varchar2, vtrade_date in date, vscriptag in number, vbranchtag in number, vfut_per out number, vopt_per out number, vstrike_premium out varchar2, vfut_per_active out number, vopt_per_active out number ) is sqlStatement varchar2(3500) :=''; sqlscrip varchar2(50) :=''; sqlbranchtag varchar2(50) :=''; c1 Fpkg1300.cursor_type; got pkg_split.t_array; vsymbol fotrxchargeignore.isymbol%type; vexpdate varchar2(20):=to_char(Null); vdays number:=0; vcount number:=0; begin sqlbranchtag := ' and branchtag is null '; if vbranchtag>0 then sqlbranchtag := ' and nvl(branchtag,''X'')=''B'' '; end if; got := pkg_split.GetSplit(vscrip, '~', null); for i in got.first .. got.last loop if i = 1 then vsymbol := got(i); -- symbol elsif i = 2 then vexpdate := got(i); -- expirydate ddMONyyyy end if; end loop; sqlscrip := ' and scripcode is null '; if vscriptag >0 then sqlscrip := ' and scripcode=:vsymbol '; end if; -- added for trxn charges %age fetchsing for near month contracts vcount :=0; vdays :=7; if vexpdate is not null then select to_date(vexpdate,'ddMONyyyy')- vtrade_date into vdays from dual; select COUNT(*) into vcount FROM cols where table_name='TRXCHARGEMST' AND COLUMN_NAME='BSEOPT_PER_NEARMONTH'; END IF; if vdays <= 6 and vcount >0 then -- fetch near month trxn charges percentage sqlStatement := 'select decode(:vstn,''B'',bsefut_per,''M'',mcxfut_per, nsefut_per) futpassive, decode(:vstn,''B'',nvl(bseopt_per_nearmonth,bseopt_per), ''M'', mcxopt_per, nseopt_per) optpassive, strike_premium, decode(:vstn,''B'',nvl(bsefut_per_active,bsefut_per), ''M'', nvl(mcxfut_per_active,mcxfut_per), nvl(nsefut_per_active,nsefut_per)) futactive, decode(:vstn,''B'', nvl(bseopt_per_nearmonth, nvl(bseopt_per_active,bseopt_per)), ''M'', nvl(mcxopt_per_active,mcxopt_per), nvl(nseopt_per_active,nseopt_per)) optactive from trxchargemst where clientcd= :vclient and :vtrade_date between datefr and dateto and segment= ''FO'' '||sqlscrip||' '||sqlbranchtag||' '; else sqlStatement := 'select decode(:vstn,''B'',bsefut_per,''M'',mcxfut_per, nsefut_per) futpassive, decode(:vstn,''B'',bseopt_per, ''M'', mcxopt_per, nseopt_per) optpassive, strike_premium, decode(:vstn,''B'',nvl(bsefut_per_active,bsefut_per), ''M'', nvl(mcxfut_per_active,mcxfut_per), nvl(nsefut_per_active,nsefut_per)) futactive, decode(:vstn,''B'', nvl(bseopt_per_active,bseopt_per), ''M'', nvl(mcxopt_per_active,mcxopt_per), nvl(nseopt_per_active,nseopt_per)) optactive from trxchargemst where clientcd= :vclient and :vtrade_date between datefr and dateto and segment= ''FO'' '||sqlscrip||' '||sqlbranchtag||' '; end if; if vscriptag >0 then open c1 for sqlStatement using vstn,vstn,vstn,vstn,vclient,vtrade_date,vsymbol; else open c1 for sqlStatement using vstn,vstn,vstn,vstn,vclient,vtrade_date; end if; loop fetch c1 into vfut_per, vopt_per, vstrike_premium,vfut_per_active, vopt_per_active; exit when c1%notfound; end loop; close c1; End P_PerForGetCalNseTaxFO; / create or replace FUNCTION GetCalNseTaxFO( vclient in varchar2, vtradestatus in varchar2, xinstrument_type in varchar2, xsymbol in varchar2, voptiontype in varchar2, vstrikeprice in number, vtrade_date in date, vtrn_qty in number, vtradeprice in number, vadj_flag in varchar2, vtradetype in varchar2) RETURN NUMBER is /* vtradetype =P FOR PASSIVE A FOR ACTIVE */ namt number(15,9) :=0; vfonsetax varchar2(1) :='Y'; vstn varchar2(1) :='N'; vfut_per number(15,9) :=0; vopt_per number(15,9) :=0; nCtr number(15) :=0; vstrike_premium varchar2(1) :='P'; vturnover number(15,2) :=0; vturntaxonexascldate date :='31-dec-2099'; vtrxrec number(15) :=0; vfut_per_active number(15,9) :=0; vopt_per_active number(15,9) :=0; vbranchcd branchmst.code%type; vsymbol fotrxchargeignore.isymbol%type; vinstrument_type varchar2(6); got pkg_split.t_array; vexchange varchar2(6) :=''; vclgcorp varchar2(6); vcontname varchar2(50); vexpirydate date; sexpdate varchar2(20); zsymbol varchar2(50); --added on 20.02.2024 begin /*if f_CheckSymbolExemptStatus('FO', vtrade_date, vcontname,vinstrument_type,xsymbol,vexpirydate) then return 0; end if;*/ -- vinstrument_type := substr(xinstrument_type,1,6); got := pkg_split.GetSplit(xinstrument_type, '~', null); for i in got.first .. got.last loop if i = 1 then vinstrument_type := got(i); -- InstrumentType elsif i = 2 then vexchange := got(i); -- Exchange elsif i = 3 then vclgcorp := got(i); -- Clearing Corporation elsif i = 4 then sexpdate := upper(got(i)); -- Clearing Corporation end if; end loop; -- print('instrument_type : '||vinstrument_type); -- print('exchange : '||vexchange); -- print('clg.corp : '||vclgcorp); vsymbol := nvl(xsymbol,'$$$$$$'); IF vtradestatus='BF' or vtradestatus='CF' or nvl(vadj_flag,'X') = 'A' or vtradeprice=0 THEN RETURN 0; END IF; select count(*) into nCtr from fotrxchargeignore where isymbol=vsymbol and vtrade_date between idatefr and idateto; if nCtr >0 then return 0; end if; select 1 into vtrxrec from trxchargemst where segment='FO' and rownum=1; if vtrxrec=0 or vtrxrec is null then return 0; end if; If (vinstrument_type='EQUFUT' or vinstrument_type='INDFUT' or vinstrument_type='EQUOPT' or vinstrument_type='INDOPT' or vinstrument_type='BSE') THEN vstn :='B'; Elsif vinstrument_type='FUMSTK' or vinstrument_type='FUMIDX' or vinstrument_type='OPMSTK' or vinstrument_type='OPMIDX' or vinstrument_type='MCX' or substr(vinstrument_type,1,1)='M' THEN vstn :='M'; Else vstn :='N'; END IF; -- Modified on 02.09.2019 as discussed with sir if vexchange is not null then vstn :='N'; if substr(vexchange,1,1)='B' then vstn :='B'; elsif substr(vexchange,1,1)='M' then vstn :='M'; end if; end if; -- added on 31.10.2020 for checking symbol exemption vcontname := vinstrument_type||vsymbol||sexpdate; if f_CheckSymbolExemptStatus('FO', vtrade_date, vcontname,vinstrument_type,xsymbol,vexpirydate) then return 0; end if; print('stn : '||vstn); select nvl(TURNTAXONEXASCLDATE,to_date('31-dec-2099')) into vturntaxonexascldate from para; if vtradestatus='EX' or vtradestatus='AS' or vtradestatus='CL' then if vtrade_date >=vturntaxonexascldate and vturntaxonexascldate is not null then return 0; end if; end if; SELECT nvl(fonsetax,'Y'), branchind into vfonsetax, vbranchcd from partymst where par_code=vclient; IF vfonsetax = 'N' THEN RETURN 0; END IF; zsymbol := vsymbol; if vsymbol='SENSEX' and vstrikeprice >0 then print('12222222'); zsymbol := zsymbol||'~'||sexpdate; end if; -- select branchind into vbranchcd from partymst where par_code=vclient; select count(*) into nCtr from trxchargemst where clientcd = vclient and vtrade_date between datefr and dateto and segment= 'FO' and scripcode=vsymbol and branchtag is null; if nCtr >0 then print('117'); -- P_PerForGetCalNseTaxFO(vclient,vsymbol||'~'||sexpdate,vstn,vtrade_date,1,0,vfut_per,vopt_per,vstrike_premium,vfut_per_active,vopt_per_active); P_PerForGetCalNseTaxFO(vclient,zsymbol,vstn,vtrade_date,1,0,vfut_per,vopt_per,vstrike_premium,vfut_per_active,vopt_per_active); else print('120'); select count(*) into nCtr from trxchargemst where clientcd = vclient and vtrade_date between datefr and dateto and segment= 'FO' and scripcode is null and branchtag is null; if nCtr > 0 then print('113'); P_PerForGetCalNseTaxFO(vclient,zsymbol,vstn,vtrade_date,0,0, vfut_per,vopt_per,vstrike_premium,vfut_per_active,vopt_per_active); else print('117'); select count(*) into nCtr from trxchargemst where clientcd=vbranchcd and vtrade_date between datefr and dateto and segment= 'FO' and scripcode=vsymbol and nvl(branchtag,'X')='B'; if nCtr >0 then print('122'); P_PerForGetCalNseTaxFO(vbranchcd,zsymbol,vstn,vtrade_date,1,1, vfut_per,vopt_per,vstrike_premium,vfut_per_active,vopt_per_active); else print('125'); select count(*) into nCtr from trxchargemst where clientcd=vbranchcd and vtrade_date between datefr and dateto and segment= 'FO' and scripcode is null and nvl(branchtag,'X')='B'; if nCtr >0 then print('132'); print(vbranchcd); P_PerForGetCalNseTaxFO(vbranchcd,zsymbol,vstn,vtrade_date,0,1, vfut_per,vopt_per,vstrike_premium,vfut_per_active,vopt_per_active); else print('136'); select count(*) into nCtr from trxchargemst where clientcd='ZZZZZZ' and vtrade_date between datefr and dateto and segment= 'FO' and scripcode=vsymbol and branchtag is null; if nCtr >0 then P_PerForGetCalNseTaxFO('ZZZZZZ',zsymbol,vstn,vtrade_date,1,0, vfut_per,vopt_per,vstrike_premium,vfut_per_active,vopt_per_active); else P_PerForGetCalNseTaxFO('ZZZZZZ',zsymbol,vstn,vtrade_date,0,0, vfut_per,vopt_per,vstrike_premium,vfut_per_active,vopt_per_active); end if; end if; end if; end if; end if; print('futper : '||vfut_per); -- ====================== if nvl(vtradetype,'P') ='A' then vfut_per := vfut_per_active; vopt_per := vopt_per_active; end if; If (vstn = 'N' and vinstrument_type = 'OPTSTK' or vinstrument_type = 'OPTIDX') OR (vstn = 'M' and vinstrument_type = 'OPMSTK' or vinstrument_type = 'OPMIDX') OR (vstn='B' and vinstrument_type = 'EQUOPT' or vinstrument_type = 'INDOPT') or substr(vinstrument_type,1,3) in ('OPT','OPM') or substr(vinstrument_type,4,3) in ('OPT','OPM') THEN If vstrike_premium = 'P' THEN vturnover := abs(vtrn_qty)*vtradeprice; ELSIF vstrike_premium = 'S' Then vturnover := abs(vtrn_qty)*vstrikeprice; ELSE vturnover := abs(vtrn_qty)*(vtradeprice+vstrikeprice); END IF; namt := vturnover*vopt_per/100; ELSE vturnover := abs(vtrn_qty)*vtradeprice; namt := vturnover*vfut_per/100; END IF; IF vfonsetax = 'Y' then namt := namt; ELSE namt := namt*-1; END IF; -- start for trxn. tax if vstn ='B' and vtrade_date >= to_date('03-apr-2017') and abs(namt) >0 then if vfonsetax = 'Y' then namt := namt + .01; else namt := namt -.01; end if; end if; -- end for trxn. tax RETURN namt; EXCEPTION WHEN OTHERS THEN RETURN 0; END GetCalNseTaxFO;