SELECT C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ITEM_TYPE, E.DESCR, A.ITEM_AMT, A.APPLIED_AMT, A.ITEM_AMT - A.APPLIED_AMT
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.EMPLID = :3
AND A.ITEM_AMT > 0
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)
GROUP BY C.LAST_NAME, C.FIRST_NAME, C.MIDDLE_NAME, A.EMPLID, A.ITEM_TYPE, E.DESCR, A.ITEM_AMT, A.APPLIED_AMT
UNION
SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, B.EMPLID, 'Totals = ', ' ', sum ( B.ITEM_AMT), sum ( B.APPLIED_AMT), sum ( B.ITEM_AMT) - sum ( B.APPLIED_AMT)
FROM PS_ITEM_SF B, PS_CU_BIO_VW D
WHERE B.BUSINESS_UNIT = :1
AND B.ACCOUNT_TERM = :2
AND B.EMPLID = :3
AND B.ITEM_AMT > 0
AND D.EMPLID = B.EMPLID
GROUP BY D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, B.EMPLID, 'Totals = ', ' '
ORDER BY 1, 2, 3
  • No labels