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,PROV_TRADE) select dq.client_code,dq.isin,0,SUM(dq.quantity) custody_balance,NULL,0,0,0,0,0,0,0,fpd,0,prov FROM ( SELECT * FROM ( 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 FROM Custody_master cm, Equity_temp_capital_g_l_sum etc 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<='06-SEP-08') AND cm.post = 1 Group by cm.client_code,cm.isin ) where quantity <> 0 UNION ALL select scm.client_code,scm.isin,scm.fpd,NULL prov,sum(scm.quantity) from ( 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 FROM custody_master cm, Equity_temp_capital_g_l_sum etc WHERE cm.client_Code = etc.client_code and cm.transaction_Date > '06-SEP-08' and cm.clearing_no is not null AND cm.post = 1 group by cm.clearing_no,cm.client_code,cm.isin,NULL 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) ) scm,clearing_calendar cc where scm.clearing_no=cc.clearing_no and cc.clearing_end_date<='06-SEP-08' group by scm.client_code,scm.isin,scm.fpd UNION ALL 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 FROM custody_opening_balances cob, custody_activity ca, Equity_temp_capital_g_l_sum etc WHERE cob.client_code = etc.client_code and cob.activity_code=ca.activity_code AND cob.post = 1 UNION ALL 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 From Equity_trade et, Clearing_calendar cc, Security sc, Equity_temp_capital_g_l_sum etc WHERE et.Client_code = etc.Client_code and et.trade_date <='06-SEP-08' and Nvl(et.bill_number,0) = 0 and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 group by et.client_code,et.isin,cc.future_period_desc,sc.symbol UNION ALL select et.client_code,et.isin,null fpd,'-PROW' prov,sum(decode(et.buy_or_sell,'B',et.volume,-et.volume)) quantity from   equity_trade et,clearing_calendar cc,security sc, Equity_temp_capital_g_l_sum etc WHERE et.client_code = etc.client_code and et.bill_number is null and et.trade_date > '06-SEP-08' and et.trade_type = (select es.release_cot_trade from equity_system es) and sc.symbol like '%-PRO' and et.bill_number is null and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 group by et.client_code,et.isin,cc.future_period_desc,sc.symbol UNION ALL 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 from    equity_trade et,clearing_calendar cc,security sc, Equity_temp_capital_g_l_sum etc WHERE et.client_code = etc.client_code and et.bill_number is null and et.trade_date > '06-SEP-08' and et.trade_type = (select es.release_cot_trade from equity_system es) and sc.symbol not like '%-PRO' and cc.future_period_desc is not null and et.clearing_no=cc.clearing_no and et.isin=sc.isin and et.post=1 group by et.client_code,et.isin,cc.future_period_desc,sc.symbol ) dq group by dq.client_code,dq.isin,fpd,prov having (SUM(dq.quantity)<>0) 


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 = :B2 AND C.GL_SL_CODE = ETC.CLIENT_CODE AND A.GL_FORM_DATE <= :B1 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 <= :B1 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


INSERT INTO TEMP_TBL (CLIENT_CODE, CL_AMT) SELECT CLIENT_CODE, SUM(AMOUNT) FROM ( 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) + 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 <=:B1 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 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) + 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 > :B1 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 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) + 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 > :B1 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 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 <=:B1 )) WHERE TEM2.FUTURE_PERIOD_DESC IS NOT NULL


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 C.POST = 1 AND C.CLIENT_CODE=ET.CLIENT_CODE AND ET.TRADE_DATE BETWEEN :B4 AND :B3 AND C.BRANCH_CODE = DECODE(:B1 ,'%',DECODE(:B2 ,(SELECT BRANCH_CODE FROM LOCATIONS WHERE LOCATION_CODE = (SELECT LOCATION_CODE FROM SYSTEM)),C.BRANCH_CODE,:B2 ),:B1 ) 


UPDATE TEMP_EQUITY_MARGIN SET FUTURE_CASH_BALANCE = GET_SL_BALANCE_EQUITY_FUTURE(:B3 ,CLIENT_CODE,:B2 ,'ALL','C',:B1 )