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_PAYMENT
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_CHARGE = 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
  • No labels