|
SELECT DISTINCT Z.COMMON_ID , Z.BUSINESS_UNIT , D.CHARGE_PRIORITY , Z.ACCOUNT_NBR , Z.ACCOUNT_TERM , Z.ITEM_NBR , Z.ITEM_AMT , Z.APPLIED_AMT , Z.ITEM_TERM , Z.ITEM_TYPE , Z.ITEM_BALANCE , Z.CURRENCY_CD , Z.ITEM_AMT - Z.APPLIED_AMT FROM PS_ITEM_SF Z ,PSTREEDEFN A ,PSTREENODE B ,PSTREELEAF C ,PS_PMT_CHRG_TBL D ,PS_PMT_CHRG_PRIOR E ,PS_BUS_UNIT_TBL_SF F WHERE Z.BUSINESS_UNIT = F.BUSINESS_UNIT AND A.SETID = B.SETID AND A.SETID = C.SETID AND A.SETID = D.SETID AND D.CHARGE_PRIORITY = E.CHARGE_PRIORITY AND D.SETID = E.SETID AND D.SETID = ( SELECT D1.SETID FROM PS_SET_CNTRL_REC D1 WHERE D1.SETCNTRLVALUE = F.INSTITUTION AND D1.RECNAME = 'PMT_CHRG_TBL') AND D.EFFDT = E.EFFDT AND D.EFF_STATUS = 'A' AND D.EFFDT = ( SELECT MAX(D2.EFFDT) FROM PS_PMT_CHRG_TBL D2 WHERE D.SETID = D2.SETID AND D.CHARGE_PRIORITY = D2.CHARGE_PRIORITY AND D2.EFFDT <= %CurrentDateIn) AND B.TREE_NODE = E.TREE_NODE AND A.VALID_TREE = 'Y' AND A.TREE_NAME = B.TREE_NAME AND A.TREE_NAME = C.TREE_NAME AND A.EFFDT = B.EFFDT AND A.EFFDT = C.EFFDT AND A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PSTREEDEFN A1 WHERE A1.SETID = A.SETID AND A1.TREE_NAME = A.TREE_NAME AND A1.EFFDT <= %CurrentDateIn) AND C.TREE_NODE_NUM = B.TREE_NODE_NUM AND Z.ITEM_TYPE >= C.RANGE_FROM AND Z.ITEM_TYPE <= C.RANGE_TO AND (Z.ITEM_AMT >= 0) AND Z.ITEM_AMT - Z.APPLIED_AMT > 0 AND Z.ITEM_AMT > 0 AND (Z.ITEM_STATUS = 'A' OR Z.ITEM_STATUS = ' ')
|
COMMON_ID