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')
  • No labels