SELECT distinct
PAPF.PERSON_NUMBER ,
PPNF.FULL_NAME EMPLOYEE_NAME ,
PAAM.ASSIGNMENT_NUMBER,
PDCDTL.DISPLAY_NAME,
TO_CHAR(PDCCF.EFFECTIVE_START_DATE,'YYYY/MM/DD') "EFFECTIVE_START_DATE",
TO_CHAR(PDCCF.EFFECTIVE_END_DATE,'YYYY/MM/DD') "EFFECTIVE_END_DATE",
PDCF.CARD_SEQUENCE,
PDCCF.COMPONENT_SEQUENCE,
PDCCDTL.COMPONENT_NAME,
PRIF.VALUE1,
PVDTL.NAME
FROM PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF,
PAY_REL_GROUPS_DN PRGD,
PAY_DIR_CARDS_F PDCF,
PAY_DIR_CARD_COMPONENTS_F PDCCF,
PAY_DIR_CARD_DEFINITIONS_TL PDCDTL,
PAY_DIR_CARD_COMP_DEFS_TL PDCCDTL,
PAY_VALUE_DEFINITIONS_F PVDF,
PAY_VALUE_DEFINITIONS_TL PVDTL,
PAY_RANGE_ITEMS_F PRIF
WHERE 1= 1
and paam.person_id = papf.person_id
and papf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
AND PAAM.PRIMARY_FLAG = 'Y'
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PAAM.ASSIGNMENT_TYPE NOT LIKE '%T'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
AND PAAM.ASSIGNMENT_ID = PRGD.ASSIGNMENT_ID
AND PRGD.PAYROLL_RELATIONSHIP_ID = PDCF.PAYROLL_RELATIONSHIP_ID
AND PDCF.DIR_CARD_ID = PDCCF.DIR_CARD_ID
AND PDCF.DIR_CARD_DEFINITION_ID = PDCDTL.DIR_CARD_DEFINITION_ID
AND PDCDTL.LANGUAGE = 'US'
AND PDCCDTL.LANGUAGE = 'US'
AND PVDTL.LANGUAGE = 'US'
AND PDCDTL.DISPLAY_NAME= 'Benefits and Pensions'
AND PDCCDTL.DIR_CARD_COMP_DEF_ID = PDCCF.DIR_CARD_COMP_DEF_ID
AND PVDF.SOURCE_ID = PDCCF.DIR_CARD_COMP_ID
AND PVDTL.VALUE_DEFN_ID = PVDF.VALUE_DEFN_ID
AND PRIF.VALUE_DEFN_ID = PVDF.VALUE_DEFN_ID
AND TRUNC(SYSDATE) BETWEEN TRUNC(PAPF.EFFECTIVE_START_DATE) AND TRUNC(PAPF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN TRUNC(PPNF.EFFECTIVE_START_DATE) AND TRUNC(PPNF.EFFECTIVE_END_DATE)
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PDCF.EFFECTIVE_START_DATE AND PDCF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PDCCF.EFFECTIVE_START_DATE AND PDCCF.EFFECTIVE_END_DATE
AND TO_CHAR(PRIF.EFFECTIVE_START_DATE,'YYYY-MM-DD') =
(SELECT MAX(TO_CHAR(PRIF1.EFFECTIVE_START_DATE,'YYYY-MM-DD'))
FROM PAY_RANGE_ITEMS_F PRIF1
WHERE PRIF1.VALUE_DEFN_ID = PRIF.VALUE_DEFN_ID
AND PRIF1.VALUE_DEFN_ID = PVDF.VALUE_DEFN_ID)
-- AND PAPF.PERSON_NUMBER = 108814
ORDER BY PAAM.ASSIGNMENT_NUMBER
Thinkapace Admin
