SELECT A.SESSION_CODE, A.ITEM_TYPE, A.ACCOUNT_TYPE_SF, A.ITEM_TYPE_CD, B.DESCR, B.KEYWORD1, A.ACCOUNT_NBR, sum ( A.ITEM_AMT), sum ( A.APPLIED_AMT), sum ( A.ITEM_AMT) - sum ( A.APPLIED_AMT), TO_CHAR(SYSDATE,'YYYY-MM-DD'),B.SETID,B.ITEM_TYPE,TO_CHAR(B.EFFDT,'YYYY-MM-DD') FROM PS_ITEM_SF A, PS_ITEM_TYPE_TBL B WHERE ( A.BUSINESS_UNIT = :1 AND A.ACCOUNT_TERM = :2 AND B.ITEM_TYPE = A.ITEM_TYPE AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_TBL B_ED WHERE B.SETID = B_ED.SETID AND B.ITEM_TYPE = B_ED.ITEM_TYPE AND B_ED.EFFDT <= SYSDATE) AND B.SETID = A.BUSINESS_UNIT AND ( A.ITEM_TYPE = :3 OR :3 IS NULL) AND ( B.KEYWORD1 = :4 OR :4 IS NULL) ) GROUP BY A.SESSION_CODE, A.ITEM_TYPE, A.ACCOUNT_TYPE_SF, A.ITEM_TYPE_CD, B.DESCR, B.KEYWORD1, A.ACCOUNT_NBR,B.SETID,B.ITEM_TYPE,TO_CHAR(B.EFFDT,'YYYY-MM-DD') |