VIEW: APPS.PQP_GB_PENSION_TEMPLATE_V

Source

View Text - Preformatted

SELECT petf.ELEMENT_TYPE_ID ,petf.BUSINESS_GROUP_ID ,petf.LEGISLATION_CODE ,petf.CLASSIFICATION_ID ,pec.CLASSIFICATION_NAME ,petf.ELEMENT_NAME ,petf.REPORTING_NAME ,petf.DESCRIPTION ,petei.eei_information1 SCHEME_TYPE ,petei.eei_information2 CONTRIBUTION_METHOD ,petei.eei_information3 ADDITIONAL_CONTRIBUTIONS ,petei.eei_information4 EXCEPTIONAL_CONTRIBUTIONS ,decode(petei.eei_information5, null, 'N', 'Y') EMPLOYER_CONTRIBUTIONS ,petei.eei_information5 EMPLOYER_CONTRIBUTION_RATE ,petei.eei_information6 EMPLOYER_CONTRIBUTION_TYPE ,hrl.meaning EMPLOYER_CONTRIBUTION_TYPE_NM ,petei.eei_information7 BUY_BACK_ADDED_YEARS ,petei.eei_information8 FAMILY_WIDOWER_BENEFIT ,decode(petei.eei_information9, null, 'N', 'Y') AVC_PERCENTAGE ,petei.eei_information9 AVC_PERCENTAGE_PROVIDER ,decode(petei.eei_information10, null, 'N', 'Y') AVC_FIXED_RATE ,petei.eei_information10 AVC_FIXED_RATE_PROVIDER ,decode(petei.eei_information11, null, 'N', 'Y') LIFE_ASSURANCE ,petei.eei_information11 LIFE_ASSURANCE_PROVIDER ,petei.eei_information12 EMPLOYEE_DEFAULT_CONTRIBUTION ,petf.LAST_UPDATE_DATE ,petf.LAST_UPDATED_BY ,petf.LAST_UPDATE_LOGIN ,petf.CREATED_BY ,petf.CREATION_DATE FROM PAY_ELEMENT_TYPES_F PETF, PAY_ELEMENT_TYPE_EXTRA_INFO PETEI, PAY_ELEMENT_CLASSIFICATIONS PEC, HR_LOOKUPS HRL, FND_SESSIONS SES WHERE PETF.ELEMENT_TYPE_ID = PETEI.ELEMENT_TYPE_ID AND PETF.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID AND PETEI.EEI_INFORMATION6 = HRL.LOOKUP_CODE(+) AND HRL.LOOKUP_TYPE(+) = 'PQP_GB_EMP_CONTRIBUTION_TYPES' AND PETEI.INFORMATION_TYPE ='PQP_GB_PENSION_INFORMATION' AND SES.SESSION_ID = USERENV('sessionid') AND SES.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE AND PETF.EFFECTIVE_END_DATE

View Text - HTML Formatted

SELECT PETF.ELEMENT_TYPE_ID
, PETF.BUSINESS_GROUP_ID
, PETF.LEGISLATION_CODE
, PETF.CLASSIFICATION_ID
, PEC.CLASSIFICATION_NAME
, PETF.ELEMENT_NAME
, PETF.REPORTING_NAME
, PETF.DESCRIPTION
, PETEI.EEI_INFORMATION1 SCHEME_TYPE
, PETEI.EEI_INFORMATION2 CONTRIBUTION_METHOD
, PETEI.EEI_INFORMATION3 ADDITIONAL_CONTRIBUTIONS
, PETEI.EEI_INFORMATION4 EXCEPTIONAL_CONTRIBUTIONS
, DECODE(PETEI.EEI_INFORMATION5
, NULL
, 'N'
, 'Y') EMPLOYER_CONTRIBUTIONS
, PETEI.EEI_INFORMATION5 EMPLOYER_CONTRIBUTION_RATE
, PETEI.EEI_INFORMATION6 EMPLOYER_CONTRIBUTION_TYPE
, HRL.MEANING EMPLOYER_CONTRIBUTION_TYPE_NM
, PETEI.EEI_INFORMATION7 BUY_BACK_ADDED_YEARS
, PETEI.EEI_INFORMATION8 FAMILY_WIDOWER_BENEFIT
, DECODE(PETEI.EEI_INFORMATION9
, NULL
, 'N'
, 'Y') AVC_PERCENTAGE
, PETEI.EEI_INFORMATION9 AVC_PERCENTAGE_PROVIDER
, DECODE(PETEI.EEI_INFORMATION10
, NULL
, 'N'
, 'Y') AVC_FIXED_RATE
, PETEI.EEI_INFORMATION10 AVC_FIXED_RATE_PROVIDER
, DECODE(PETEI.EEI_INFORMATION11
, NULL
, 'N'
, 'Y') LIFE_ASSURANCE
, PETEI.EEI_INFORMATION11 LIFE_ASSURANCE_PROVIDER
, PETEI.EEI_INFORMATION12 EMPLOYEE_DEFAULT_CONTRIBUTION
, PETF.LAST_UPDATE_DATE
, PETF.LAST_UPDATED_BY
, PETF.LAST_UPDATE_LOGIN
, PETF.CREATED_BY
, PETF.CREATION_DATE
FROM PAY_ELEMENT_TYPES_F PETF
, PAY_ELEMENT_TYPE_EXTRA_INFO PETEI
, PAY_ELEMENT_CLASSIFICATIONS PEC
, HR_LOOKUPS HRL
, FND_SESSIONS SES
WHERE PETF.ELEMENT_TYPE_ID = PETEI.ELEMENT_TYPE_ID
AND PETF.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PETEI.EEI_INFORMATION6 = HRL.LOOKUP_CODE(+)
AND HRL.LOOKUP_TYPE(+) = 'PQP_GB_EMP_CONTRIBUTION_TYPES'
AND PETEI.INFORMATION_TYPE ='PQP_GB_PENSION_INFORMATION'
AND SES.SESSION_ID = USERENV('SESSIONID')
AND SES.EFFECTIVE_DATE BETWEEN PETF.EFFECTIVE_START_DATE
AND PETF.EFFECTIVE_END_DATE