CREATE OR REPLACE  PROCEDURE 
    "ACMKHI"."EQUITY_MARGIN_OFF_LINE_NEW" (PS_BRANCH VARCHAR2,
                                                       PS_USER_BRANCH VARCHAR2,
                                                       VCLIENT VARCHAR2,
                                                       VFROM_DATE DATE,
                                                       VTO_DATE DATE,
                                                       PS_SUM_NET VARCHAR2,
                                                       PS_FUTURE_NORMAL VARCHAR2,
                                                       COT_ACCOUNT_MERGE VARCHAR2 := 'T',
                                                       P_MARING_APPLICABLE VARCHAR2 := 1,
                                                       PS_ON_OFF_CLIENT VARCHAR2,
                                                       V_MARGIN_PERC    VARCHAR2,
                                                       V_CUSTODY_PERC   VARCHAR2,
                                                       V_ALL_CLNT       NUMBER) IS
       vshort_sale                number;
       vcash_balance              number;
       ret_value                  number;
       vgl_sl_type_client         number;
       v_cot_client_code          varchar2(5);
       v_client_code              varchar2(12);
       v_bal                      number(15,4);
       v_cot_amt                    number(15,4);
       sql_string                 varchar2(10000);
       CURSOR V_CUR_1 IS SELECT client_code FROM equity_temp_capital_g_l_sum tem;
       CURSOR V_CUR_2 IS select ctem.client_code gl_sl_code,
                                op_amt bal,
                                cl_amt cot_amt
                          from equity_temp_capital_g_l_sum ctem;
begin
commit;
--Insert into t values(vclient||','|| v_all_clnt||', ' ||P_MARING_APPLICABLE||' , '||PS_ON_OFF_CLIENT||' , '||PS_BRANCH||' ,' ||PS_USER_BRANCH);
if ((VCLIENT = 'ALL' or vClient = '%') AND V_ALL_CLNT = 0 and P_MARING_APPLICABLE = 2) Then
       -- Build ALL client list which trade during the period.
       /*insert  	into equity_temp_capital_g_l_sum (client_code)
       select  	distinct c.client_code
       from    	client c ,client_info ci,equity_trade et
       where c.client_code = ci.client_code(+)
         --and decode(PS_ON_OFF_CLIENT,'ALL',1,decode(ci.online_client,null,0,ci.online_client)) = decode(PS_ON_OFF_CLIENT,'ALL',1,'ON',1,'OFF',0)
         and c.post = 1
         and c.client_code=et.client_code
         and et.trade_date between vfrom_date and vto_date
         --and c.margin_applicable = Decode(P_MARING_APPLICABLE, 2, c.margin_applicable, P_MARING_APPLICABLE)
         and c.branch_code = decode(PS_BRANCH,'%',decode(PS_USER_BRANCH,(select branch_code from locations where location_code = (select location_code from system)),c.branch_code,PS_USER_BRANCH),PS_BRANCH)
         --and c.badla_client=0
         ;*/
         insert  	into equity_temp_capital_g_l_sum (client_code)
         select  	distinct c.client_code
       from    	client c 
       where c.post = 1and c.branch_code = decode(PS_BRANCH,'%',decode(PS_USER_BRANCH,(select branch_code from locations where location_code = (select location_code from system)),c.branch_code,PS_USER_BRANCH),PS_BRANCH)
         --and c.badla_client=0
         ;
--Insert into t values(0);
Elsif ((VCLIENT = 'ALL' or vClient = '%') AND V_ALL_CLNT = 0) Then
       -- Build ALL client list which trade during the period.
       insert  	into equity_temp_capital_g_l_sum (client_code)
       select  	distinct c.client_code
       from    	client c ,client_info ci,equity_trade et
       where c.client_code = ci.client_code(+)
         and decode(PS_ON_OFF_CLIENT,'ALL',1,decode(ci.online_client,null,0,ci.online_client)) = decode(PS_ON_OFF_CLIENT,'ALL',1,'ON',1,'OFF',0)
         and c.post = 1
         and c.client_code=et.client_code
         and et.trade_date between vfrom_date and vto_date
         and c.margin_applicable = Decode(P_MARING_APPLICABLE, 2, c.margin_applicable, P_MARING_APPLICABLE)
         and c.branch_code = decode(PS_BRANCH,'%',decode(PS_USER_BRANCH,(select branch_code from locations where location_code = (select location_code from system)),c.branch_code,PS_USER_BRANCH),PS_BRANCH)
         and c.badla_client=0;
--Insert into t values(1);
ELSif (V_ALL_CLNT = 1) Then
       -- Build ALL client list which trade during the period.
       insert  	into equity_temp_capital_g_l_sum (client_code)
       select  	distinct c.client_code
       from    	client c ,client_info ci,equity_trade et
       where c.client_code = ci.client_code(+)
         and decode(PS_ON_OFF_CLIENT,'ALL',1,decode(ci.online_client,null,0,ci.online_client)) = decode(PS_ON_OFF_CLIENT,'ALL',1,'ON',1,'OFF',0)
         and c.post = 1
         and c.client_code=et.client_code
         and c.CLIENT_CODE=DECODE(VCLIENT,'ALL',C.CLIENT_CODE,VCLIENT)
         and et.trade_date <= vto_date
         and c.margin_applicable = Decode(P_MARING_APPLICABLE, 2, c.margin_applicable, P_MARING_APPLICABLE)
         and c.branch_code = decode(PS_BRANCH,'%',decode(PS_USER_BRANCH,(select branch_code from locations where location_code = (select location_code from system)),c.branch_code,PS_USER_BRANCH),PS_BRANCH)
         and c.badla_client=0;
--Insert into t values(2);
ELSif (V_ALL_CLNT = 2 or P_MARING_APPLICABLE = 2) Then
       -- Build ALL client list
       insert  	into equity_temp_capital_g_l_sum (client_code)
       select  	distinct c.client_code
       from    	client c ,client_info ci
       where c.client_code = ci.client_code(+)
         and decode(PS_ON_OFF_CLIENT,'ALL',1,decode(ci.online_client,null,0,ci.online_client)) = decode(PS_ON_OFF_CLIENT,'ALL',1,'ON',1,'OFF',0)
         and c.post = 1
         and c.CLIENT_CODE=DECODE(VCLIENT,'ALL',C.CLIENT_CODE,VCLIENT);
--         and c.branch_code = decode(PS_BRANCH,'%',decode(PS_USER_BRANCH,(select branch_code from locations where location_code = (select location_code from system)),c.branch_code,PS_USER_BRANCH),PS_BRANCH);
--Insert into t values(3);
else
       -- Build Single client list which trade during the period.
       insert   into equity_temp_capital_g_l_sum (client_code)
       select   distinct c.client_code
       from     client c ,client_info ci,equity_trade et
       where c.client_code = ci.client_code(+)
         and decode(PS_ON_OFF_CLIENT,'ALL',1,decode(ci.online_client,null,0,ci.online_client)) = decode(PS_ON_OFF_CLIENT,'ALL',1,'ON',1,'OFF',0)
         and c.CLIENT_CODE=VCLIENT
         and c.post = 1
         and c.client_code=et.client_code
         and et.trade_date between vfrom_date and vto_date;
--Insert into t values(4);
end if;
--Commit;
-------------------------
/*OPEN V_CUR_1;
FETCH V_CUR_1
  INTO v_client_code;
-------------------------
LOOP
    EXIT WHEN V_CUR_1%NOTFOUND;*/
	-- Insert client custody holdings in the temp table
	sql_string := ' insert     into   temp_Equity_margin ';
	sql_string := sql_string|| '             (CLIENT_CODE,ISIN,CASH_BALANCE,CUST_BALANCE,MARKET_DATE,MARKET_RATE,OUTSTND_AMOUNT, ';
	sql_string := sql_string|| '             CASH_MARGIN,CUST_MARGIN,CASH_BUYING_POWER,CUST_BUYING_POWER,SHORT_SALE_VALUE, ';
	sql_string := sql_string|| '             FUTURE_PERIOD_DESC,EQUITY_CURRENT_POSITION,PROV_TRADE) ';
	sql_string := sql_string|| ' select      dq.client_code,dq.isin,0,SUM(dq.quantity) custody_balance,NULL,0,0,0,0,0,0,0,fpd,0,prov ';
	sql_string := sql_string|| ' FROM ';
	sql_string := sql_string|| ' ( ';

	sql_string := sql_string|| ' SELECT * FROM ( ';
	sql_string := sql_string|| ' SELECT      cm.client_code,cm.isin,NULL fpd,NULL prov,sum(decode(cm.in_or_out,''I'',(nvl(cm.un_reg_quantity,0)+nvl(cm.reg_quantity,0)),-(nvl(cm.un_reg_quantity,0)+nvl(cm.reg_quantity,0)))) quantity ';
	sql_string := sql_string|| ' FROM Custody_master cm, Equity_temp_capital_g_l_sum etc';
	--sql_string := sql_string|| ' WHERE       cm.transaction_Date<='''|| vto_Date ||''' ';
	sql_string := sql_string|| ' WHERE  cm.client_code = etc.client_code and Exists (select 1 from custody_master cm2 where cm.transaction_id = cm2.transaction_id and cm2.transaction_Date<='''|| vto_Date ||''') ';
	--sql_string := sql_string|| ' AND cm.CLIENT_CODE= '''|| v_client_code ||''' ';
	sql_string := sql_string|| ' AND cm.post = 1 ';
	sql_string := sql_string|| ' Group by cm.client_code,cm.isin ';
	sql_string := sql_string|| ' ) where quantity <> 0 ';

	sql_string := sql_string|| ' UNION ALL ';

	sql_string := sql_string|| ' select      scm.client_code,scm.isin,scm.fpd,NULL prov,sum(scm.quantity) ';
	sql_string := sql_string|| ' from ( ';
	sql_string := sql_string|| '      SELECT        cm.clearing_no,cm.client_code,cm.isin,NULL fpd,sum(decode(cm.in_or_out,''I'',(nvl(cm.un_reg_quantity,0)+nvl(cm.reg_quantity,0)),-(nvl(cm.un_reg_quantity,0)+nvl(cm.reg_quantity,0)))) quantity ';
	sql_string := sql_string|| '      FROM   custody_master cm, Equity_temp_capital_g_l_sum etc';
	sql_string := sql_string|| ' WHERE cm.client_Code = etc.client_code and cm.transaction_Date > '''|| vto_Date ||''' ';
--	sql_string := sql_string||' AND   cm.CLIENT_CODE= '''|| v_client_code ||''' ';
	sql_string := sql_string|| ' and cm.clearing_no is not null ';
	sql_string := sql_string|| ' AND cm.post = 1 ';
	sql_string := sql_string|| ' group by cm.clearing_no,cm.client_code,cm.isin,NULL ';
	sql_string := sql_string|| ' having (sum(decode(cm.in_or_out,''I'',(nvl(cm.un_reg_quantity,0)+nvl(cm.reg_quantity,0)),-(nvl(cm.un_reg_quantity,0)+nvl(cm.reg_quantity,0))))<>0) ';
	sql_string := sql_string|| '      ) scm,clearing_calendar cc ';
	sql_string := sql_string|| ' where       scm.clearing_no=cc.clearing_no ';
	sql_string := sql_string|| ' and cc.clearing_end_date<='''||vto_date||''' ';
	sql_string := sql_string|| ' group by scm.client_code,scm.isin,scm.fpd ';
--	sql_string := sql_string|| ' having (sum(scm.quantity)<>0) ';

	sql_string := sql_string|| ' UNION ALL ';

	sql_string := sql_string|| ' SELECT cob.client_code,cob.isin,NULL fpd,NULL prov,decode(ca.in_or_out,''I'',(nvl(cob.un_reg_quantity,0)+nvl(cob.reg_quantity,0)),-(nvl(cob.un_reg_quantity,0)+nvl(cob.reg_quantity,0))) quantity ';
	sql_string := sql_string|| '   FROM custody_opening_balances cob, custody_activity ca, Equity_temp_capital_g_l_sum etc ';
	sql_string := sql_string|| '  WHERE cob.client_code = etc.client_code and cob.activity_code=ca.activity_code ';
	--sql_string := sql_string|| ' AND cob.CLIENT_CODE= '''|| v_client_code ||''' ';
	sql_string := sql_string|| '    AND cob.post = 1 ';

	sql_string := sql_string|| ' UNION ALL ';

	sql_string := sql_string|| ' Select et.client_code,et.isin,cc.future_period_desc fpd,decode(instr(sc.symbol,''-PRO''),0,null,''-PRO'') prov,sum(decode(et.buy_or_sell,''B'',et.volume,-et.volume)) quantity ';
	sql_string := sql_string|| ' From Equity_trade et, Clearing_calendar cc, Security sc, Equity_temp_capital_g_l_sum etc ';
	sql_string := sql_string|| ' WHERE et.Client_code = etc.Client_code and et.trade_date <='''||vto_date||''' ';
	sql_string := sql_string|| ' and Nvl(et.bill_number,0) = 0 ';
	--sql_string := sql_string|| ' AND et.CLIENT_CODE= '''|| v_client_code ||''' ';
	sql_string := sql_string|| ' and et.clearing_no=cc.clearing_no ';
	sql_string := sql_string|| ' and et.isin=sc.isin ';
	sql_string := sql_string|| ' and et.post=1 ';
	sql_string := sql_string|| ' group by et.client_code,et.isin,cc.future_period_desc,sc.symbol ';
--	sql_string := sql_string|| ' having sum(decode(et.buy_or_sell,''B'',et.volume,-et.volume)) <> 0 ';

	sql_string := sql_string|| ' UNION ALL ';

	-- Provisional Weekly checking (Back-Date Checking)
	sql_string := sql_string|| ' select et.client_code,et.isin,null fpd,''-PROW'' prov,sum(decode(et.buy_or_sell,''B'',et.volume,-et.volume)) quantity ';
	sql_string := sql_string|| ' from 	equity_trade et,clearing_calendar cc,security sc, Equity_temp_capital_g_l_sum etc ';

	sql_string := sql_string|| ' WHERE et.client_code = etc.client_code and et.bill_number is null ';
--	sql_string := sql_string|| ' AND 	et.CLIENT_CODE= '''|| v_client_code ||''' ';
	sql_string := sql_string|| ' and et.trade_date > '''||vto_date||''' ';
	sql_string := sql_string|| ' and  et.trade_type = (select es.release_cot_trade from equity_system es) ';
	sql_string := sql_string|| ' and sc.symbol like ''%-PRO'' ';
	sql_string := sql_string|| ' and et.bill_number is null ';
	sql_string := sql_string|| ' and et.clearing_no=cc.clearing_no ';
	sql_string := sql_string|| ' and et.isin=sc.isin ';
	sql_string := sql_string|| ' and et.post=1 ';
	sql_string := sql_string|| ' group by et.client_code,et.isin,cc.future_period_desc,sc.symbol ';

	sql_string := sql_string|| ' UNION ALL ';

	-- Future Weekly checking (Back-Date Checking)
	sql_string := sql_string|| ' select et.client_code,et.isin,cc.future_period_desc fpd,''-FUTW'' prov,sum(decode(et.buy_or_sell,''B'',et.volume,-et.volume)) quantity ';
	sql_string := sql_string|| ' from 	equity_trade et,clearing_calendar cc,security sc, Equity_temp_capital_g_l_sum etc ';
	sql_string := sql_string|| ' WHERE  et.client_code = etc.client_code and et.bill_number is null ';
--	sql_string := sql_string|| ' AND 	et.CLIENT_CODE= '''|| v_client_code ||''' ';
	sql_string := sql_string|| ' and et.trade_date > '''||vto_date||''' ';
	sql_string := sql_string|| ' and  et.trade_type = (select es.release_cot_trade from equity_system es) ';
	sql_string := sql_string|| ' and sc.symbol not like ''%-PRO'' ';
	sql_string := sql_string|| ' and cc.future_period_desc is not null ';
	sql_string := sql_string|| ' and et.clearing_no=cc.clearing_no ';
	sql_string := sql_string|| ' and et.isin=sc.isin ';
	sql_string := sql_string|| ' and et.post=1 ';
	sql_string := sql_string|| ' group by et.client_code,et.isin,cc.future_period_desc,sc.symbol ';
	sql_string := sql_string|| ' ) dq ';
	sql_string := sql_string|| ' group by dq.client_code,dq.isin,fpd,prov ';
	sql_string := sql_string|| ' having (SUM(dq.quantity)<>0) ';

	execute immediate sql_string;
---------------------------
/*FETCH V_CUR_1
  INTO v_client_code;
END LOOP;
CLOSE V_CUR_1;*/
---------------------------

-- INSERTING ONE ROW IF NO RECORD FOR A CLIENT EXISTS
-------------------------
--==================
--For Optimization
--==================

/*OPEN V_CUR_1;
FETCH V_CUR_1
  INTO v_client_code;
-------------------------
LOOP
    EXIT WHEN V_CUR_1%NOTFOUND;
   begin
    select 1 into ret_value from temp_equity_margin tem
    where tem.client_code= v_client_code;
   exception
   when no_data_found then
-- if only cash Balance exists and position is zero. record inserted to show his margin
    insert into temp_Equity_margin
    (CLIENT_CODE,ISIN,CASH_BALANCE,CUST_BALANCE,MARKET_DATE,MARKET_RATE,OUTSTND_AMOUNT,
    CASH_MARGIN,CUST_MARGIN,CASH_BUYING_POWER,CUST_BUYING_POWER,SHORT_SALE_VALUE,
    FUTURE_PERIOD_DESC,EQUITY_CURRENT_POSITION)
    values(v_client_code,'',0,0,NULL,0,0,0,0,0,0,0,'',0);
   when others then
     null;
  end;
---------------------------
FETCH V_CUR_1
  INTO v_client_code;
END LOOP;
CLOSE V_CUR_1;*/
---------------------------

Insert into temp_Equity_margin
    (CLIENT_CODE,ISIN,CASH_BALANCE,CUST_BALANCE,MARKET_DATE,MARKET_RATE,OUTSTND_AMOUNT,
    CASH_MARGIN,CUST_MARGIN,CASH_BUYING_POWER,CUST_BUYING_POWER,SHORT_SALE_VALUE,
    FUTURE_PERIOD_DESC,EQUITY_CURRENT_POSITION)
    Select Client_code, '', 0, 0, NULL, 0, 0, 0, 0, 0, 0, 0, '', 0
      From Equity_temp_capital_g_l_sum etc
      Where Not Exists (Select null from temp_Equity_margin tem
                          Where tem.client_code = etc.client_code);
--=======================
-- End For Optimization
--=======================

-- deleting clients which have balance in custody. clients has worked during period
-- period but have no custody balance will remain b/c they may have cash balance.

       Delete From Temp_equity_margin otem
        where otem.isin is null
          and 1< (Select count(*) from Temp_equity_margin item
                   where item.client_code=otem.client_code);

-- update market price for normal (excluding future)
update temp_equity_margin tem
set (tem.market_date,tem.market_rate)=   (select em.price_date,em.close_rate
                                          from equity_market em
                                           where em.price_date=(select max(price_date)
                                                                from equity_market
                                                                where isin=tem.isin
                                                                AND price_Date<=vto_date)
                                            and tem.future_period_desc is null
                                            AND em.isin=tem.isin);

-- update market price for future
update temp_equity_margin tem2
set (tem2.market_date,tem2.market_rate) = (select fem.price_date,fem.close_rate
                                           from temp_equity_margin tem,
                                                security s,
                                                forward_market fem
                                           where fem.symbol = s.symbol||tem.future_period_desc
                                           and tem.future_period_desc is not null
                                           and tem.isin = s.isin
                                           and tem2.isin = tem.isin
                                           and tem2.client_code=tem.client_code
                                           and s.symbol||tem2.future_period_desc = fem.symbol
                                           and fem.price_date =(select max(price_date)
                                                                from temp_equity_margin tem,
                                                                     security s,
                                                                     forward_market fem
                                                                where fem.symbol = s.symbol||tem.future_period_desc
                                                                and tem.future_period_desc is not null
                                                                and tem.client_code = tem2.client_code
                                                                and tem.isin = s.isin
                                                                and fem.price_date <=vto_date))
where tem2.future_period_desc is not null;

-- update custody holding percentage for ONLINE/OFFLINE CLIENTS
---------------------------------------
-- CHANGE FOR COSTODY PERCENTAGE
---------------------------------------
update temp_equity_margin tem
set tem.cust_margin=NVL(V_CUSTODY_PERC,(select  nvl(decode(tem.future_period_desc,null,
                        		decode(nvl(ci.online_client,0),0,order_validation.get_margin_holding_percentage(tem.client_code,tem.isin),ONLINE_CLIENT_margin.GET_OL_MARGIN_HOLD_PERCENTAGE(tem.client_code,tem.isin)),
                        		decode(nvl(ci.online_client,0),0,order_validation.GET_MARGIN_HOLDING_FWD_PRCT(tem.client_code,tem.isin),ONLINE_CLIENT_margin.GET_OL_MARGIN_HOLD_FWD_PRCT(tem.client_code,tem.isin))
                        		),0)
                        from 	client c,
                              client_info ci
                        where	c.client_code = ci.client_code(+)
                        and	c.client_code = tem.client_code));

---------------------------------------
-- update custody short sale
update temp_equity_margin tem
set tem.short_sale_value=decode(tem.cust_balance/decode(tem.cust_balance,0,1,abs(tem.cust_balance)),1,0,nvl(tem.cust_balance,0)*nvl(tem.market_rate,0));

update        temp_equity_margin tem set tem.short_sale_value=0
where tem.future_period_desc is not null
or isin in (select isin from security where isin=tem.isin and symbol like '%-PRO');

-- update custody buying power detail
update temp_equity_margin tem
set tem.cust_buying_power=(tem.cust_balance*tem.market_rate)*decode(tem.cust_balance,0,1,decode(tem.cust_balance/abs(tem.cust_balance),1,tem.cust_margin/100,1))
where nvl(tem.cust_balance,0)<>0;
begin
   select gl_sl_type_client into vgl_sl_type_client from system;
end;

-- For Determing Cash Balance Including COT Effect OR Excluding COT Effect
/* For Normal Client Cash Balance  */


 --  Update equity_temp_capital_g_l_sum set op_amt=GET_RISK_CLIENT_BALANCE(vgl_sl_type_client,client_code,vto_date,1,'C',PS_FUTURE_NORMAL);

Insert into temp_tbl
  (Client_code, Cl_amt)
  Select client_Code, Sum(Amt)
    from (Select c.gl_sl_code client_code, c.Amount amt
            from Gl_vouchers_details         c,
                 gl_vouchers                 a,
                 equity_temp_capital_g_l_sum etc
           where a.Gl_voucher_no = c.gl_voucher_no
             and c.Gl_sl_type = vgl_sl_type_client
             and c.gl_sl_Code = etc.client_code
             and a.Gl_form_date <= VTO_DATE
             and Nvl(a.Gl_form_type_code, 'X') <> 'ETB'
             and rownum > 0
          Union all
          Select et.client_code, Decode(et.buy_or_sell,
              'B',
              (et.volume * et.rate) + (et.brk_amount + nvl(eti.cvt, 0) +
              nvl(eti.wht, 0) + nvl(eti.wht_cot, 0)),
              ((et.volume * et.rate) -
              (et.brk_amount + nvl(eti.cvt, 0) + nvl(eti.wht, 0) +
              nvl(eti.wht_cot, 0))) * -1) amt
  From Equity_trade et,
       clearing_calendar cc,
       equity_trade_info eti,
       security,
       Equity_system es,
       Equity_temp_capital_g_l_sum etc
 Where et.trade_number = eti.trade_number(+)
   and et.isin = security.isin
   and et.clearing_no = cc.clearing_no
   and et.client_code = etc.client_code
   and et.trade_date <= VTO_DATE
   and decode('F' || nvl(et.bill_number, -1),
              'F-1',
              cc.clearing_type,
              0) <> decode('F' || nvl(et.bill_number, -1),
                           'F-1',
                           es.forward_clr_type,
                           1)
   and decode(Substr(Upper(Security.Symbol),
                     Length(Upper(Security.Symbol)) - 2),
              'PRO',
              decode(et.bill_number, null, 0, 1),
              1) = 1)
   Group by client_Code;

Update Equity_temp_capital_g_l_sum etc Set Op_amt = (Select cl_amt from temp_tbl tt
                                                      where etc.client_code = tt.client_code);
 Delete from temp_tbl;

Insert into Temp_Tbl (Client_code, Cl_Amt)
select Client_code, sum(amount)
			from
								(
								-- SQUARED POSITION (Future and Provisional)
								Select  et.client_code client_code,
												et.isin isin,cc.future_period_desc,
												sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity,
												--sum(decode(et.buy_or_sell,'B',((et.volume*et.rate)+(et.brk_amount+nvl(eti.cvt,0))),-((et.volume*et.rate)-(et.brk_amount+nvl(eti.wht,0)+nvl(eti.wht_cot,0))) )) amount
												sum(decode(et.buy_or_sell,'B',((et.volume*et.rate)+(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0) )),-((et.volume*et.rate)-(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0)+nvl(eti.wht_cot,0))) )) amount
								from 		equity_trade et,clearing_calendar cc,security sc,equity_trade_info eti, equity_temp_capital_g_l_sum etc
								WHERE 	et.trade_date <=VTO_DATE
								and   	nvl(et.bill_number,0) = 0
								AND 		et.CLIENT_CODE= etc.client_code
								and 		et.clearing_no=cc.clearing_no
								and 		et.isin=sc.isin
								and 		(nvl(cc.future_period_desc,'N') <> 'N'
												or sc.symbol like '%PRO')
								and 		et.post=1
								and			et.trade_number = eti.trade_number(+)
								group by et.client_code,et.isin,cc.future_period_desc,sc.symbol
								having 	sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) = 0

								UNION ALL

								-- SQUARED BACK-DATE FUTURE POSITION
								Select  et.client_code client_code,
												et.isin isin,cc.future_period_desc,
												sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity,
												--sum(decode(et.buy_or_sell,'B',((et.volume*et.rate)+(et.brk_amount+nvl(eti.cvt,0))),-((et.volume*et.rate)-(et.brk_amount+nvl(eti.wht,0)+nvl(eti.wht_cot,0))) )) amount
												sum(decode(et.buy_or_sell,'B',((et.volume*et.rate)+(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0) )),-((et.volume*et.rate)-(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0)+nvl(eti.wht_cot,0))) )) amount
								from 		equity_trade et,clearing_calendar cc,equity_trade_info eti, equity_temp_capital_g_l_sum etc
								WHERE 	et.trade_date > VTO_DATE
								and   	nvl(et.bill_number,0) = 0
								AND 		et.CLIENT_CODE= etc.CLIENT_CODE
								and 		et.clearing_no=cc.clearing_no
								and 		cc.future_period_desc is not null
								AND			et.ticket_number like 'FTR%'
								and 		et.post=1
								and			et.trade_number = eti.trade_number(+)
								group by et.client_code,et.isin,cc.future_period_desc
								having 	sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) = 0

								UNION ALL

								-- SQUARED BACK-DATE PROVISIONAL POSITION
								Select  et.client_code client_code,
												et.isin isin,null future_period_desc,
												sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity,
												--sum(decode(et.buy_or_sell,'B',((et.volume*et.rate)+(et.brk_amount+nvl(eti.cvt,0))),-((et.volume*et.rate)-(et.brk_amount+nvl(eti.wht,0)+nvl(eti.wht_cot,0))) )) amount
												sum(decode(et.buy_or_sell,'B',((et.volume*et.rate)+(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0) )),-((et.volume*et.rate)-(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0)+nvl(eti.wht_cot,0))) )) amount
								from 		equity_trade et,security sc,equity_trade_info eti, equity_temp_capital_g_l_sum etc
								WHERE 	et.trade_date > VTO_DATE
								and   	nvl(et.bill_number,0) = 0
								AND 		et.CLIENT_CODE= etc.CLIENT_CODE
								AND			et.ticket_number like 'FTR%'
								and 		et.isin = sc.isin
								and 		sc.symbol like '%PRO'
								and 		et.post=1
								and			et.trade_number = eti.trade_number(+)
								group by et.client_code,et.isin
								having 	sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) = 0
								)
Group by Client_code;

Update Equity_temp_capital_g_l_sum etc Set PL_Square_Pos = (Select cl_amt from temp_tbl tt
                                                      where etc.client_code = tt.client_code);

Delete from temp_tbl;

/* For Future Client Cash Balance  */
   Update temp_equity_margin set FUTURE_CASH_BALANCE = GET_SL_BALANCE_EQUITY_FUTURE(vgl_sl_type_client,client_code,vto_date,'ALL','C',PS_SUM_NET);

-- COT Client Cash Merge functionality
if COT_ACCOUNT_MERGE = 'Y' THEN

/* Selection of COT client Code */
   update equity_temp_capital_g_l_sum set op_amt=GET_RISK_CLIENT_BALANCE(vgl_sl_type_client,client_code,vto_date,1,'C',PS_FUTURE_NORMAL);
/* Selection of COT client Code */
   begin
      SELECT COT_CLIENT_CODE INTO V_COT_CLIENT_CODE
      FROM CLIENT C
      WHERE C.CLIENT_CODE = (select ETC.CLIENT_CODE from equity_temp_capital_g_l_sum ETC WHERE ROWNUM = 1);
   exception
      when no_data_found then
      V_COT_CLIENT_CODE := '00000';
      when TOO_MANY_ROWS then
      V_COT_CLIENT_CODE := '%';
   end;

/* COT client Cash Balance */
   UPDATE TEMP_EQUITY_MARGIN ETC SET COT_CASH_BALANCE = GET_RM_CLIENT_BALANCE(vgl_sl_type_client,client_code,vto_date,'ALL',1,'C');

/* COT client Cash Balance Merging in Normal Cash Balance*/
   UPDATE equity_temp_capital_g_l_sum ETC SET op_amt = nvl(op_amt,0)+GET_RM_CLIENT_BALANCE(vgl_sl_type_client,client_code,vto_date,'ALL',1,'C');
end if;

-- Reverse effect treat trades as Release(COT EFFECT)


------------- CFS MK II changes By (Nasir) -----------------
/*
update        equity_temp_capital_g_l_sum tem set tem.cl_amt=
              (select sum((volume*em.close_rate))
from   	equity_trade et,clearing_calendar cc,security s,clearing_type ct,equity_trade_info eti,equity_market em
where 	et.trade_date between VTO_DATE-100 and VTO_DATE
and    	et.clearing_no = cc.clearing_no
and		  et.trade_number = eti.trade_number(+)
and     et.isin = em.isin
and     em.price_date = (select max(em2.price_date) from equity_market em2 where em2.isin = em.isin and em2.price_date <= VTO_DATE)
and     et.isin = s.isin
and    	et.client_code = tem.client_code
and   	cc.clearing_type = ct.clearing_type
and   	ct.forwardable = 0
and    	et.trade_type=(select es.cot_trade from equity_system es)
and    	et.buy_or_sell='S'
and     s.symbol not like '%-PRO'
and    	cc.future_period_desc is null
and    	not exists    (select 1 from equity_trade etex,clearing_calendar c,security s
                     where etex.client_code=et.client_code
                     and   etex.trade_date <= vto_date
                     and   etex.clearing_no = c.clearing_no
                     and           etex.isin = s.isin
                     and    etex.isin=et.isin
                     and    buy_or_sell='B'
                     and           s.symbol not like '%-PRO'
                     and    etex.ticket_number=et.ticket_number
                     )
group by et.client_code);
*/

Insert into temp_tbl
  (Client_Code, cl_amt, badla_mtm)
  (Select et.client_code,
          Sum((et.volume - Nvl(etr.volume, 0)) * em.close_rate) cl_amt,
          Sum(Decode((et.volume - Nvl(etr.Volume, 0)), 0, 0, decode(et.buy_or_sell,
                     'S',
                     ((et.rate - em.close_rate) *
                     (et.volume - Nvl(etr.Volume, 0))) +
                     (et.brk_amount + nvl(et.cvt, 0) + nvl(et.wht, 0) +
                     nvl(et.wht_cot, 0)),
                     ((em.close_rate - et.rate) *
                     (et.volume - Nvl(etr.Volume, 0))) +
                     (et.brk_amount + nvl(et.cvt, 0) + nvl(et.wht, 0) +
                     nvl(et.wht_cot, 0))))) badla_mtm
     From (Select et.Client_code,
                  et.isin,
                  et.buy_or_sell,
                  et.Ticket_number,
                  et.clearing_no,
                  Sum(et.volume) Volume,
                  Sum(et.brk_Amount) brk_Amount,
                  Avg(et.rate) rate,
                  Sum(eti.cvt) cvt,
                  Sum(eti.wht) wht,
                  Sum(eti.wht_Cot) wht_Cot
             From Equity_trade et, Equity_Trade_Info eti, Equity_system es, Equity_temp_capital_g_l_sum etc
            Where et.trade_number = eti.trade_number
              and et.trade_type = es.cot_trade
              and et.client_code = etc.client_code
              and et.buy_or_sell = 'S'
              and et.trade_date between To_date(vto_date) - 100 and
                  To_date(vto_date)
            Group by et.Client_code,
                     et.isin,
                     et.buy_or_sell,
                     et.Ticket_number,
                     et.clearing_no) et,
          clearing_calendar cc,
          security s,
          clearing_type ct,
          equity_market em,

          (Select et.Client_code, Ticket_number, Sum(Volume) Volume
             from equity_trade et, Equity_temp_capital_g_l_sum etc
            Where Trade_type = (Select Release_cot_trade from Equity_system)
              and et.buy_or_sell = 'B'
              and et.client_code = etc.client_code
              and trade_date <= VTo_date
            Group by et.Client_code, Ticket_number) etr
    where et.clearing_no = cc.clearing_no
      and et.isin = em.isin
      and et.Ticket_number = etr.Ticket_number(+)
      and et.client_code = etr.client_code(+)
      and em.price_date =
          (select max(em2.price_date)
             from equity_market em2
            where em2.isin = em.isin
              and em2.price_date <= VTO_DATE)
      and et.isin = s.isin
      and cc.clearing_type = ct.clearing_type
      and ct.forwardable = 0
      and s.symbol not like '%-PRO'
      and cc.future_period_desc is null
    Group by et.client_code
    Having Nvl(Sum(et.Volume), 0) - Nvl(Sum(etr.Volume), 0) <> 0);

Update Equity_temp_capital_g_l_sum et
   Set et.Cl_amt = (Select cl_amt from Temp_tbl tt
                      Where tt.client_code = et.client_code),
       et.Badla_mtm = (Select Badla_mtm from Temp_tbl tt
                      Where tt.client_code = et.client_code);

------------- End CFS MK II changes By (Nasir) -----------------

-------------------------
OPEN V_CUR_2;
FETCH V_CUR_2
  INTO v_client_code,
       v_bal,
       v_cot_amt;
-------------------------
LOOP
    EXIT WHEN V_CUR_2%NOTFOUND;
    -- Update Cash Balance,Cash Margin Percentage in TEMP_EQUITY_MARGIN
-----------------------------------------------
-- CHANGE FOR MARGIN PERCENTAGE
-----------------------------------------------
    update temp_equity_margin tem
    set
    tem.cash_balance=v_bal,
    tem.cash_margin=NVL(V_MARGIN_PERC,nvl(order_validation.get_margin_percentage(tem.client_code,tem.isin),0))
    where tem.client_code=v_client_code;
-----------------------------------------------

		-- Update Online Cash Withdrawl in TEMP_EQUITY_MARGIN
		update	temp_equity_margin
		set			ol_cash_withdrawl =
															nvl((select 	sum(nvl(amount,0)) cash_withdrawl_amount
															from 		online_client_pay_req
															where 	status in ('IN PROCESS','ACCEPTED')
															and			client_code = v_client_code
															and			trunc(request_time) <= VTO_DATE
															),0)
		where		client_code = v_client_code;

		-- Update Online Cash Withdrawl impact on Cash Balance in TEMP_EQUITY_MARGIN
		update	temp_equity_margin
		set			cash_balance = cash_balance + ol_cash_withdrawl
		where		client_code = v_client_code;

    begin
         select sum(tem.short_sale_value),max(tem.cash_balance) into vshort_Sale,vcash_balance from temp_equity_margin tem
         where tem.client_code=v_client_code;
    exception
         when no_data_found then
         vshort_sale:=0;
         vcash_balance:=0;
         null;
    end;

    if vcash_balance>0 then
       update temp_equity_margin tem
       set
       tem.cash_buying_power=tem.cash_balance
       where tem.client_code=v_client_code;
    else
        vcash_balance:=v_bal+abs(vshort_Sale);
        IF vcash_balance>0 THEN
           update temp_equity_margin tem
           set
           tem.cash_buying_power=tem.cash_balance
           where tem.client_code=v_client_code;
        else
           update temp_equity_margin tem
           set
           tem.cash_buying_power=vcash_balance*tem.cash_margin/100
           where tem.client_code=v_client_code;
        end IF;
    end if;

    update temp_equity_margin tem
    set
    tem.outstnd_amount = v_cot_amt
    where tem.client_code = v_client_code;

    update    temp_equity_margin
    set       future_mm=(
	                       select  	sum(decode(et.buy_or_sell,'B',((et.rate - em.market_rate)*et.volume)+(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0)),((em.market_rate - et.rate)*et.volume)+(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0)))) amount
	                       from   	equity_trade et,temp_equity_margin em,clearing_calendar cc,equity_trade_info eti
	                       where  	em.client_code = v_client_code
	                       and    	et.client_code = em.client_code
	                       and    	et.trade_number = eti.trade_number(+)
	                       and 		et.isin = em.isin
	                       and 		et.isin||cc.future_period_desc=em.isin||em.future_period_desc
	                       and 		et.clearing_no = cc.clearing_no
	                       and 		et.trade_date <= vto_date
	                       and 		et.bill_number is null
	                       and 		cc.future_period_desc is not null
                         )
              where         temp_equity_margin.future_period_desc is not null
    and       temp_equity_margin.client_code=v_client_code;

    update    temp_equity_margin
    set       prov_mm=(
	                     select sum(decode(et.buy_or_sell,'B',((et.rate - em.market_rate)*et.volume)+(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0)),((em.market_rate - et.rate)*et.volume)+(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0)))) amount
	                     from   equity_trade et,temp_equity_margin em,security s,equity_trade_info eti
	                     where 	et.client_code = v_client_code
	                     and    et.client_code = em.client_code(+)
	                     and	et.trade_number = eti.trade_number(+)
	                     and    et.isin = em.isin(+)
	                     and    et.isin = s.isin
	                     and   	s.symbol like '%-PRO'
	                     and   	et.trade_date <= vto_date
	                     and   	et.bill_number is null
                      )
    where temp_equity_margin.client_code=v_client_code;

		/** PROVISIONAL CASH BALANCE in EQUITY_CURRENT_POSITION field **/
		update  temp_equity_margin tem set tem.EQUITY_CURRENT_POSITION=
		(
		       select 	sum(Decode(et.buy_or_sell,'B',(et.volume*et.rate)+(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0)),-((et.volume*et.rate)-(et.brk_amount + nvl(eti.cvt,0) + nvl(eti.wht,0) + nvl(eti.wht_cot,0))))) cash_balance
		       from 	equity_trade et,security s,equity_trade_info eti
		       where    et.isin=s.isin
		       and		et.client_code = v_client_code
		       and		et.trade_number = eti.trade_number(+)
		       and      et.trade_date<=vto_date
		       and      s.symbol like '%-PRO'
		       and      et.bill_number is null
		       group    by et.client_code
		)
		where 	tem.client_code=v_client_code;

		-- update Provisional Market Value in the table temp_equity_margin
		update  temp_equity_margin tem set tem.NORMAL_CASH_BALANCE=
		      (
		      select sum(abs(tem.cust_balance) * tem.market_rate) market_value
		       from temp_equity_margin tem,security s
		       where tem.isin=s.isin
		       and   tem.client_code=v_client_code
		       and   tem.prov_trade is not null
		       and   s.symbol like '%-PRO'
		      )
		where tem.client_code=v_client_code;


---------------------------
FETCH V_CUR_2
  INTO v_client_code,
       v_bal,
       v_cot_amt;
END LOOP;
CLOSE V_CUR_2;
---------------------------


END;