|
SELECT %Substring(t.setid,1,5) , t.tree_node , SUM(c.bal_amt) , u.cr_limit FROM PSTREENODE t , ps_cust_data_vw c , pstreeleaf l , ps_corp_credit_ar u WHERE EXISTS ( SELECT 'x' FROM ps_set_cntrl_rec WHERE setid = t.setid AND setcntrlvalue = c.business_unit AND recname = 'CUSTOMER') AND c.cust_id BETWEEN l.range_from AND l.range_to AND t.tree_name = 'AR_CORPORATE_CUST' AND t.effdt = ( SELECT MAX(effdt) FROM PSTREENODE WHERE setid = t.setid AND tree_name=t.tree_name AND effdt <= %CurrentDateIn) AND t.tree_node_num > 1 AND l.setid = t.setid AND u.setid = t.setid AND u.tree_node = t.tree_node AND l.tree_name = t.tree_name AND l.effdt = t.effdt AND u.effdt = ( SELECT MAX(effdt) FROM ps_corp_credit_ar WHERE setid = t.setid AND tree_node = t.tree_node AND effdt <=%CurrentDateIn AND eff_status = 'A') AND l.tree_node_num BETWEEN t.tree_node_num AND t.tree_node_num_end GROUP BY t.setid, t.tree_node, u.cr_limit HAVING SUM(c.bal_amt_orig) > u.cr_limit
|