SELECT DISTINCT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ITEM_TYPE, E.DESCR, TO_CHAR(A.LAST_ACTIVITY_DATE,'YYYY-MM-DD'), A.ITEM_AMT, A.APPLIED_AMT, A.ITEM_AMT - A.APPLIED_AMT,E.SETID,E.ITEM_TYPE,TO_CHAR(E.EFFDT,'YYYY-MM-DD') FROM PS_ITEM_SF A, PS_CU_BIO_VW C, PS_ITEM_TYPE_TBL E WHERE A.BUSINESS_UNIT = :1 AND A.ACCOUNT_TERM = :2 AND A.ITEM_TYPE = :3 AND C.EMPLID = A.EMPLID AND E.ITEM_TYPE = A.ITEM_TYPE AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_ITEM_TYPE_TBL E_ED WHERE E.SETID = E_ED.SETID AND E.ITEM_TYPE = E_ED.ITEM_TYPE AND E_ED.EFFDT <= SYSDATE) AND A.BUSINESS_UNIT = E.SETID GROUP BY C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ITEM_TYPE, E.DESCR, TO_CHAR(A.LAST_ACTIVITY_DATE,'YYYY-MM-DD'), A.ITEM_AMT, A.APPLIED_AMT,E.SETID,E.ITEM_TYPE,TO_CHAR(E.EFFDT,'YYYY-MM-DD') ORDER BY 1, 2, 3 |