select * from (
SELECT
PAPF.PERSON_NUMBER ,
PPNF.FULL_NAME EMPLOYEE_NAME ,
PAAM.ASS_ATTRIBUTE2 PAYGROUP,
TO_CHAR(PDCCF.EFFECTIVE_START_DATE,'YYYY/MM/DD') "EFFECTIVE_START_DATE",
TO_CHAR(PDCCF.EFFECTIVE_END_DATE,'YYYY/MM/DD') "EFFECTIVE_END_DATE",
--PRIF.VALUE1,
PVDTL.NAME "VALUE_DEFN_NAME",
case when PVDTL.NAME ='Filing Status' then
(select
a1.meaning
from fnd_lookup_values a1,fnd_lookup_types_VL a2
where a1.lookup_type='HRX_US_STATE_FILING_STATUS_MN'
and a1.lookup_type=a2.lookup_type
and a1.LOOKUP_CODE = PRIF.VALUE1)
else PRIF.VALUE1 end VALUE1
FROM
PAY_DIR_CARD_COMPONENTS_F PDCCF,
PAY_DIR_CARD_DEFINITIONS_TL PDCDTL,
PAY_DIR_CARD_COMP_DEFS_TL PDCCDTL,
PAY_DIR_CARDS_F PDCF,
PAY_REL_GROUPS_DN PRGD,
PAY_VALUE_DEFINITIONS_F PVDF,
PAY_VALUE_DEFINITIONS_TL PVDTL,
PAY_RANGE_ITEMS_F PRIF,
PER_ALL_ASSIGNMENTS_M PAAM,
PER_ALL_PEOPLE_F PAPF,
PER_PERSON_NAMES_F PPNF
WHERE
PAAM.ASSIGNMENT_ID = PRGD.ASSIGNMENT_ID
AND PAAM.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PAAM.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'
-- AND paam.assignment_type = 'E'
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 PDCCDTL.DIR_CARD_COMP_DEF_ID = PDCCF.DIR_CARD_COMP_DEF_ID
AND PVDF.SOURCE_ID = PDCCF.DIR_CARD_COMP_ID
AND PRIF.VALUE_DEFN_ID = PVDF.VALUE_DEFN_ID
AND PVDTL.VALUE_DEFN_ID = PVDF.VALUE_DEFN_ID
AND PDCCDTL.LANGUAGE = 'US'
AND PDCDTL.LANGUAGE = 'US'
AND PVDTL.LANGUAGE = 'US'
and paam.person_id = papf.person_id
and papf.person_id = ppnf.person_id
and ppnf.name_type = 'GLOBAL'
AND TRUNC(SYSDATE) BETWEEN PAAM.EFFECTIVE_START_DATE AND PAAM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PDCCF.EFFECTIVE_START_DATE AND PDCCF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PDCF.EFFECTIVE_START_DATE AND PDCF.EFFECTIVE_END_DATE
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 PAAM.ASSIGNMENT_NUMBER <> 'E5'
AND PDCCDTL.COMPONENT_NAME = 'State Taxes'
) a
pivot
(
max (a.VALUE1) for VALUE_DEFN_NAME
in(
'Filing Status',
'Allowances',
'Additional Tax Amount',
'Deductions Amount',
'Exempt from both federal and Minnesota income tax withholding',
'State for Disability Calculation',
'State for Unemployment Calculation',
'US Employee Withholding Certificate',
'User Interface Preference'
)
)
--ORDER BY ASSIGNMENT_NUMBER
