select -- Group Columns c.branch_code branch_code, tem.client_code client_code, c.client_name client_name, emp.first_name||' '||emp.last_name TRADER_NAME, nvl(max(fmm.FUTURE_MARK),0) FUTURE_MM, nvl(max(pmm.PROV_MARK),0) PROV_MM, nvl(max(bmm.badla_mtm),0) badla_MM, -- Detail Columns max(nvl(main.Outstanding_Amount,0)) Outstanding_Amount, --1 max(nvl(main.Future_Cash_Balance,0)) Future_Cash_Balance, max(nvl(main.Cash_Balance,0)) Actual_Cash, (max(nvl(main.custody_reduced_value,0)) * -1) Custody_Reduced_Val, max(nvl(actual_custody_val,0)) Actual_Custody, max(nvl(main.short_sale,0)) short_sale, max(nvl(main.Custody_Percentage,0)) Custody_Percentage, max(nvl(main.Provisional_Cash_Balance,0)) Provisional_Cash_Balance, max(nvl(main.Provisional_Market_Value,0)) Provisional_Market_Value, --3 MAX(nvl(TEM.CASH_MARGIN,0)) CASH_MARGIN_PERCENT, -- nvl(max(get_profit_loss_squared_pos(tem.client_code,:to_date)),0) pl_square_pos, Max(Nvl(bmm.pl_square_pos, 0)) Pl_square_pos, max(nvl(main.FUTURE_MARKET_VALUE,0)) FUTURE_MARKET_VALUE --2 from ( select Client_Group.*, -- ONLINE/OFFLINE Clients separate Percentages check decode(online_client,0,ONLINE_CLIENT_MARGIN.get_custody_percentage(Client_Group.client_code,null),ONLINE_CLIENT_MARGIN.GET_OL_CUSTODY_PERCENTAGE(Client_Group.client_code,null)) Custody_Percentage from ( select tem.client_code, nvl(ci.online_client,0) online_client, -- Cash Balance excluding Provisional 20 % (max(tem.cash_balance)/*-nvl(max(tem.equity_current_position),0)*/) Cash_Balance, --max(tem.cash_balance) Cash_Balance, max(nvl(tem.Future_Cash_Balance,0)) Future_Cash_Balance, max(tem.cash_margin) cash_margin, max(custody_margin_group.custody_reduced_value) custody_reduced_value, max(tem.outstnd_amount) outstanding_amount, --before it was sum sum(tem.short_sale_value) short_sale, max(nvl(custody_margin_group.actual_custody,0)) actual_custody_val, max(tem.Equity_Current_Position) Provisional_Cash_Balance, max(tem.Normal_Cash_Balance) Provisional_Market_Value, max(future_mkt.future_mkt_value) future_market_value from temp_equity_margin tem,client c,client_info ci, ( select tem.client_code,sum(tem.cust_buying_power) custody_reduced_value, sum(tem.cust_balance*tem.market_rate) actual_custody from temp_equity_margin tem,client c,security s where c.client_code = tem.client_code and tem.isin = s.isin(+) and tem.prov_trade is null and tem.cust_balance > 0 and tem.client_code = decode(:client,'ALL',tem.client_code,:client) and c.client_nature = decode(:p_margin_applicable,0,decode(:p_client_nature,0,c.client_nature,1, 'T',2,'I',3,'J'),c.client_nature) and c.margin_applicable = decode(:p_margin_applicable, 2, c.margin_applicable, :p_margin_applicable) and tem.future_period_desc is null group by tem.client_code ) custody_margin_group, ( select client_code,sum(abs(future_mkt_value)) future_mkt_value from ( select tem.client_code,tem.isin, sum(tem.cust_balance * tem.market_rate) future_mkt_value from temp_equity_margin tem,client c,security s where tem.isin=s.isin(+) and c.client_code = tem.client_code and tem.client_code = decode(:client,'ALL',tem.client_code,:client) and c.client_nature = decode(:p_margin_applicable,0,decode(:p_client_nature,0,c.client_nature,1, 'T',2,'I',3,'J'),c.client_nature) and c.margin_applicable = decode(:p_margin_applicable, 2, c.margin_applicable, :p_margin_applicable) and tem.future_period_desc is not null group by tem.client_code,tem.isin ) group by client_code )future_mkt where c.client_code = tem.client_code and c.client_code = ci.client_code(+) and tem.client_code = custody_margin_group.client_code(+) and tem.client_code = future_mkt.client_code(+) and tem.client_code = decode(:client,'ALL',tem.client_code,:client) and c.client_nature = decode(:p_margin_applicable,0,decode(:p_client_nature,0,c.client_nature,1, 'T',2,'I',3,'J'),c.client_nature) and c.margin_applicable = decode(:p_margin_applicable, 2, c.margin_applicable, :p_margin_applicable) group by tem.client_code,nvl(ci.online_client,0) ) Client_Group ) main ,temp_equity_margin tem,client c, branch_client bc,employee emp,trader t, (select client_code,max(future_mm) future_mark from temp_equity_margin where future_period_desc is not null group by client_code) fmm, (select client_code,max(prov_mm) prov_mark from temp_equity_margin group by client_code) pmm, (Select Client_code, Sum(badla_mtm) Badla_Mtm, Sum(pl_square_pos) Pl_square_pos From Equity_temp_capital_g_l_sum Group by Client_code ) bmm where main.client_code = tem.client_code and c.client_code = tem.client_code and bc.area_code = (select equity_area_code from equity_system) and c.client_code = bc.client_code and c.branch_code = bc.branch_code and t.employee_code = emp.employee_code and bc.trader_code = t.trader_code and tem.client_code = bc.client_code and tem.client_code = decode(:client,'ALL',tem.client_code,:client) and c.client_nature = decode(:p_margin_applicable,0,decode(:p_client_nature,0,c.client_nature,1, 'T',2,'I',3,'J'),c.client_nature) and c.margin_applicable = decode(:p_margin_applicable, 2, c.margin_applicable, :p_margin_applicable) and main.client_code =fmm.client_code(+) and main.client_code = pmm.client_code(+) and main.client_code = bmm.client_code(+) and t.trader_code = Decode(:P_Trader, '%', t.Trader_Code, :P_Trader) group by c.branch_code,tem.client_code,c.client_name,emp.first_name||' '||emp.last_name --having ((nvl(max(fmm.FUTURE_MARK),0) + nvl(max(pmm.PROV_MARK),0) + nvl(max(bmm.badla_mtm),0) + max(nvl(main.Cash_Balance,0)) + nvl(max(get_profit_loss_squared_pos--(tem.client_code,:to_date)),0))* (-1)) <= 0 order by tem.client_code,c.branch_code,emp.first_name||' '||emp.last_name