View: PQP_GB_PENSION_SCHEMES_V

Product:	PQP - Public Sector Payroll
Description:	View of all base elements created by the pension schemes element template.
Implementation/DBA Data:	APPS.PQP_GB_PENSION_SCHEMES_V

View Text

SELECT EEIT1.EEI_INFORMATION1 PENSION_SCHEME_NAME
, EEIT1.EEI_INFORMATION4 PENSION_CATEGORY
, FND_DATE.CANONICAL_TO_DATE(EEIT1.EEI_INFORMATION5) PENSION_YEAR_START_DT
, HOU.ORGANIZATION_ID PENSION_PROVIDER_ID
, HOU.NAME PENSION_PROVIDER_NAME
, PTY.PENSION_TYPE_ID PENSION_TYPE_ID
, PTY.PENSION_TYPE_NAME PENSION_TYPE_NAME
, EEIT1.EEI_INFORMATION6 EMPLOYEE_DEDUCTION_METHOD
, EEIT1.EEI_INFORMATION9 SCON
, EEIT1.EEI_INFORMATION19 ECON
, EEIT1.EEI_INFORMATION10 SCHEME_REFERENCE_NUMBER
, EEIT1.EEI_INFORMATION11 EMPLOYER_REFERENCE_NUMBER
, FND_NUMBER.CANONICAL_TO_NUMBER (EEIT1.EEI_INFORMATION16) ASSOCIATED_OCP_ELE_TYPE_ID
, (SELECT EEI_INFORMATION1
FROM PAY_ELEMENT_TYPE_EXTRA_INFO EEIT2
WHERE EEIT2.ELEMENT_TYPE_ID = FND_NUMBER.CANONICAL_TO_NUMBER (EEIT1.EEI_INFORMATION16)
AND EEIT2.INFORMATION_TYPE = 'PQP_GB_PENSION_SCHEME_INFO'
AND EEIT2.EEI_INFORMATION12 IS NULL)
, EEIT1.EEI_INFORMATION13 ADDITIONAL_CONTRIBUTION
, EEIT1.EEI_INFORMATION14 ADDED_YEARS
, EEIT1.EEI_INFORMATION15 FAMILY_WIDOWER
, DECODE(EEIT1.EEI_INFORMATION7
, NULL
, 'N'
, 'Y') EMPLOYER_COMPONENT
, EEIT1.EEI_INFORMATION7 EMPLOYER_DEDUCTION_METHOD
, EEIT1.EEI_INFORMATION18 SCHEME_PREFIX
, EEIT1.EEI_INFORMATION8 PENSION_SCHEME_TYPE
, PBT.BALANCE_TYPE_ID PENSIONABLE_SALARY_BALANCE_ID
, PBT.BALANCE_NAME PENSIONABLE_SALARY_BALANCE
, PET.ELEMENT_TYPE_ID ELEMENT_TYPE_ID
, PET.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PET.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PET.ELEMENT_NAME ELEMENT_NAME
, PET.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PET.LEGISLATION_CODE LEGISLATION_CODE
, PET.REPORTING_NAME REPORTING_NAME
, PET.DESCRIPTION DESCRIPTION
, PET.CLASSIFICATION_ID CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME CLASSIFICATION_NAME
, DECODE(PET.THIRD_PARTY_PAY_ONLY_FLAG
, 'Y'
, 'Y'
, 'N') THIRD_PARTY_PAYMENT
, PET.ITERATIVE_FLAG ITERATIVE_PROCESSING
, PET.POST_TERMINATION_RULE POST_TERMINATION_RULE
, PET.STANDARD_LINK_FLAG STANDARD_LINK
, EEIT3.EEI_INFORMATION1 ARREARAGE
, EEIT3.EEI_INFORMATION2 PARTIAL_DEDUCTION
, EEIT1.EEI_INFORMATION20 FWC_ADDED_YEARS
, PET.CREATED_BY CREATED_BY
, PET.CREATION_DATE CREATION_DATE
, PET.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PET.LAST_UPDATED_BY LAST_UPDATED_BY
, PET.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
FROM PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPE_EXTRA_INFO EEIT1
, PAY_ELEMENT_TYPE_EXTRA_INFO EEIT3
, HR_ALL_ORGANIZATION_UNITS HOU
, PQP_PENSION_TYPES_F PTY
, PAY_BALANCE_TYPES PBT
, PAY_ELEMENT_CLASSIFICATIONS PEC
WHERE EEIT1.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND EEIT1.INFORMATION_TYPE = 'PQP_GB_PENSION_SCHEME_INFO'
AND EEIT1.EEI_INFORMATION12 IS NULL
AND EEIT3.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND EEIT3.INFORMATION_TYPE = 'PQP_GB_ARREARAGE_INFO'
AND PBT.BALANCE_TYPE_ID = FND_NUMBER.CANONICAL_TO_NUMBER (EEIT1.EEI_INFORMATION17)
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND HOU.ORGANIZATION_ID = FND_NUMBER.CANONICAL_TO_NUMBER (EEIT1.EEI_INFORMATION2)
AND PTY.PENSION_TYPE_ID = FND_NUMBER.CANONICAL_TO_NUMBER (EEIT1.EEI_INFORMATION3)
AND PET.EFFECTIVE_START_DATE BETWEEN PTY.EFFECTIVE_START_DATE
AND PTY.EFFECTIVE_END_DATE
AND PET.EFFECTIVE_START_DATE BETWEEN HOU.DATE_FROM
AND NVL(HOU.DATE_TO
, PET.EFFECTIVE_START_DATE)

Columns