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 |