VIEW: APPS.PAY_US_CONTRIBUTION_HISTORY_V

Source

View Text - Preformatted

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' )

View Text - HTML Formatted

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' )