Product: | PAY - Payroll |
---|---|
Description: | Contribution History View |
Implementation/DBA Data: | APPS.PAY_US_CONTRIBUTION_HISTORY_V |
SELECT PCH.CONTR_HISTORY_ID
, PCH.PERSON_ID
, PCH.DATE_FROM
, PCH.DATE_TO
, PCH.LEGISLATION_CODE
, PCH.AMT_CONTR
, PCH.MAX_CONTR_ALLOWED
, PCH.INCLUDABLE_COMP
, PCH.TAX_UNIT_ID
, PCH.CONTR_TYPE
, PCH.SOURCE_SYSTEM
, PCH.CONTR_INFORMATION_CATEGORY
, PCH.CONTR_INFORMATION1
, PCH.CONTR_INFORMATION2
, PCH.CONTR_INFORMATION3
, PCH.CONTR_INFORMATION4
, PCH.CONTR_INFORMATION5
, PCH.CONTR_INFORMATION6
, PCH.CONTR_INFORMATION7
, PCH.CONTR_INFORMATION8
, PCH.CONTR_INFORMATION9
, PCH.CONTR_INFORMATION10
, PCH.CONTR_INFORMATION11
, PCH.CONTR_INFORMATION12
, PCH.CONTR_INFORMATION13
, PCH.CONTR_INFORMATION14
, PCH.CONTR_INFORMATION15
, PCH.CONTR_INFORMATION16
, PCH.CONTR_INFORMATION17
, PCH.CONTR_INFORMATION18
, PCH.CONTR_INFORMATION19
, PCH.CONTR_INFORMATION20
, PCH.CONTR_INFORMATION21
, PCH.CONTR_INFORMATION22
, PCH.CONTR_INFORMATION23
, PCH.CONTR_INFORMATION24
, PCH.CONTR_INFORMATION25
, PCH.CONTR_INFORMATION26
, PCH.CONTR_INFORMATION27
, PCH.CONTR_INFORMATION28
, PCH.CONTR_INFORMATION29
, PCH.CONTR_INFORMATION30
, PCH.LAST_UPDATE_DATE
, PCH.LAST_UPDATED_BY
, PCH.LAST_UPDATE_LOGIN
, PCH.CREATED_BY
, PCH.CREATION_DATE
, PCH.OBJECT_VERSION_NUMBER
, PCH.ROWID
, PPF.FULL_NAME
, HLV.MEANING
, HTU.NAME
, PCH.BUSINESS_GROUP_ID
FROM PAY_US_CONTRIBUTION_HISTORY PCH
, PER_PEOPLE_F PPF
, PER_PERSON_TYPES PTYPE
, HR_LOOKUPS HLV
, HR_TAX_UNITS_V HTU
WHERE (TO_NUMBER(TO_CHAR(PCH.DATE_FROM
, 'YYYY')) >= TO_NUMBER(TO_CHAR(PPF.EFFECTIVE_START_DATE
, 'YYYY'))
AND TO_NUMBER(TO_CHAR(PCH.DATE_TO
, 'YYYY')) <= TO_NUMBER(TO_CHAR(PPF.EFFECTIVE_END_DATE
, 'YYYY')))
AND PPF.PERSON_TYPE_ID = PTYPE.PERSON_TYPE_ID
AND PPF.PERSON_ID = PCH.PERSON_ID
AND PTYPE.SYSTEM_PERSON_TYPE = 'EMP'
AND HLV.LOOKUP_TYPE = 'US_PRE_TAX_DEDUCTIONS'
AND HLV.LOOKUP_CODE = PCH.CONTR_TYPE
AND HTU.BUSINESS_GROUP_ID(+) = PCH.BUSINESS_GROUP_ID
AND HTU.TAX_UNIT_ID(+) = PCH.TAX_UNIT_ID
AND PPF.BUSINESS_GROUP_ID = PCH.BUSINESS_GROUP_ID
AND PPF.EFFECTIVE_START_DATE = (SELECT MAX(A.EFFECTIVE_START_DATE)
FROM PER_PEOPLE_F A
, PER_PERSON_TYPES B
WHERE TO_NUMBER(TO_CHAR(PCH.DATE_FROM
, 'YYYY')) >= TO_NUMBER(TO_CHAR(A.EFFECTIVE_START_DATE
, 'YYYY'))
AND TO_NUMBER(TO_CHAR(PCH.DATE_TO
, 'YYYY')) <= TO_NUMBER(TO_CHAR(A.EFFECTIVE_END_DATE
, 'YYYY'))
AND A.PERSON_ID = PPF.PERSON_ID
AND A.PERSON_TYPE_ID = B.PERSON_TYPE_ID
AND A.BUSINESS_GROUP_ID = PCH.BUSINESS_GROUP_ID
AND B.SYSTEM_PERSON_TYPE = 'EMP' )