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