SELECT F.LAST_NAME, F.FIRST_NAME, F.MIDDLE_NAME, A.EMPLID, A.ITEM_TYPE, D.DESCR, A.ITEM_AMT, TO_CHAR(B.EFFDT,'YYYY-MM-DD'), C.ITEM_TYPE, E.DESCR, B.XREF_AMT,D.SETID,D.ITEM_TYPE,TO_CHAR(D.EFFDT,'YYYY-MM-DD'),E.SETID,E.ITEM_TYPE,TO_CHAR(E.EFFDT,'YYYY-MM-DD') FROM PS_ITEM_SF A, PS_ITEM_XREF B, PS_ITEM_SF C, PS_ITEM_TYPE_TBL D, PS_ITEM_TYPE_TBL E, PS_CU_BIO_VW F WHERE ( A.BUSINESS_UNIT = :1 AND A.ACCOUNT_TERM = :2 AND A.ITEM_TYPE = :3 AND B.EFFDT BETWEEN TO_DATE(:4,'YYYY-MM-DD') AND TO_DATE(:5,'YYYY-MM-DD') AND ( A.EMPLID = :6 OR :6 IS NULL) AND A.BUSINESS_UNIT = B.BUSINESS_UNIT AND A.COMMON_ID = B.COMMON_ID AND A.SA_ID_TYPE = B.SA_ID_TYPE AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_XREF B_ED WHERE B.BUSINESS_UNIT = B_ED.BUSINESS_UNIT AND B.COMMON_ID = B_ED.COMMON_ID AND B.SA_ID_TYPE = B_ED.SA_ID_TYPE AND B.ITEM_NBR_CHARGE = B_ED.ITEM_NBR_CHARGE AND B.ITEM_NBR_PAYMENT = B_ED.ITEM_NBR_PAYMENT AND B_ED.EFFDT <= SYSDATE) AND A.ITEM_NBR = B.ITEM_NBR_CHARGE AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.COMMON_ID = C.COMMON_ID AND B.SA_ID_TYPE = C.SA_ID_TYPE AND B.ITEM_NBR_PAYMENT = C.ITEM_NBR AND D.ITEM_TYPE = A.ITEM_TYPE AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_ITEM_TYPE_TBL D_ED WHERE D.SETID = D_ED.SETID AND D.ITEM_TYPE = D_ED.ITEM_TYPE AND D_ED.EFFDT <= SYSDATE) AND D.SETID = A.BUSINESS_UNIT AND E.ITEM_TYPE = C.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 E.SETID = C.BUSINESS_UNIT AND F.EMPLID = A.EMPLID ) ORDER BY 1, 2, 3 |