VIEW: APPS.PQP_GB_PENSION_SCHEMES_V

Source

View Text - Preformatted

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)

View Text - HTML Formatted

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)