SELECT A.ACCOUNT_TERM, A.ITEM_TYPE, B.DESCR, B.KEYWORD1, SUM ( A.ITEM_AMT),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 A.BUSINESS_UNIT = B.SETID AND A.ITEM_AMT <> 0 GROUP BY A.ACCOUNT_TERM, A.ITEM_TYPE, B.DESCR, B.KEYWORD1,B.SETID,B.ITEM_TYPE,TO_CHAR(B.EFFDT,'YYYY-MM-DD') |