|
SELECT DISTINCT Z.COMMON_ID , Z.COMMON_ID , I.INSTITUTION_SET , Z.BUSINESS_UNIT , Z.ITEM_NBR , Z.ITEM_AMT , Z.ITEM_TYPE , Z.APPLIED_AMT , Z.ITEM_TERM , Z.ACCOUNT_TERM , Z.ACCOUNT_NBR , Z.ITEM_BALANCE , Z.CURRENCY_CD FROM PS_ITEM_SF Z ,PSTREEDEFN A ,PSTREENODE B ,PSTREELEAF C ,PS_PMT_CHRG_TBL D ,PS_PMT_CHRG_PRIOR E ,PS_ITEM_TYPE_TBL X ,PS_BUS_UNIT_TBL_SF F ,PS_ISET_BU_DTL I WHERE Z.BUSINESS_UNIT = I.BUSINESS_UNIT AND Z.BUSINESS_UNIT = F.BUSINESS_UNIT AND F.SF_ENABLE_WEB_PAY = 'Y' 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 = ' ') AND I.ITEM_TYPE = X.ITEM_TYPE AND X.CHARGE_PRIORITY = D.CHARGE_PRIORITY AND X.SETID = D.SETID AND X.EFF_STATUS = 'A' AND X.EFFDT = ( SELECT MAX(X2.EFFDT) FROM PS_ITEM_TYPE_TBL X2 WHERE X.SETID = X2.SETID AND X.ITEM_TYPE = X2.ITEM_TYPE AND X2.EFFDT <= %CurrentDateIn)
|
COMMON_ID