VIEW: APPS.PAYFV_ELEMENT_TYPES

Source

View Text - Preformatted

SELECT petT.element_name element_name ,bgrT.name business_group_name ,pet.legislation_code ,decode(pet.formula_id,null,null, per_formula_functions.get_formula (pet.formula_id ,pet.effective_start_date)) formula_name ,pet.input_currency_code ,pet.output_currency_code ,pecT.classification_name ,bbc.benefit_classification_name ,hr_bis.bis_decode_lookup('YES_NO',pet.additional_entry_allowed_flag) additional_entry_allowed_flag ,hr_bis.bis_decode_lookup('YES_NO',pet.adjustment_only_flag) adjustment_only_flag ,hr_bis.bis_decode_lookup('YES_NO',pet.closed_for_entry_flag) closed_for_entry_flag ,hr_bis.bis_decode_lookup('YES_NO',pet.indirect_only_flag) indirect_only_flag ,hr_bis.bis_decode_lookup('YES_NO',pet.multiple_entries_allowed_flag) multiple_entries_allowed_flag ,hr_bis.bis_decode_lookup('YES_NO',pet.multiply_value_flag) multiply_value_flag ,hr_bis.bis_decode_lookup('TERMINATION_RULE',pet.post_termination_rule) post_termination_rule ,hr_bis.bis_decode_lookup('YES_NO',pet.process_in_run_flag) process_in_run_flag ,pet.processing_priority ,hr_bis.bis_decode_lookup('PROCESSING_TYPE',pet.processing_type) processing_type ,hr_bis.bis_decode_lookup('YES_NO',pet.standard_link_flag) standard_link_flag ,pet.qualifying_age ,pet.qualifying_length_of_service ,hr_bis.bis_decode_lookup('UNITS',pet.qualifying_units) qualifying_units ,pet.reporting_name ,hr_bis.bis_decode_lookup('YES_NO',pet.third_party_pay_only_flag) third_party_pay_only_flag ,hr_bis.bis_decode_lookup('YES_NO',pet.iterative_flag) iterative_flag ,decode(pet.iterative_formula_id,null,null, per_formula_functions.get_formula (pet.iterative_formula_id ,pet.effective_start_date)) iterative_formula_name ,pet.iterative_priority ,petT2.element_name retro_summ_element ,hr_bis.bis_decode_lookup('YES_NO',pet.grossup_flag) grossup_flag ,decode(pet.process_mode,'S','Y','N') separate_payment ,decode(pet.process_mode,'P','Y','S','Y','N') process_separate_flag ,decode(pet.proration_group_id,null,null, pay_payfv_element_types_pkg.get_event_group_name(pet.proration_group_id)) proration_group_name ,decode(pet.proration_formula_id,null,null, per_formula_functions.get_formula (pet.proration_formula_id ,pet.effective_start_date)) proration_formula_name ,decode(pet.recalc_event_group_id,null,null, pay_payfv_element_types_pkg.get_event_group_name(pet.recalc_event_group_id)) recalc_event_group_name ,pet.attribute_category ,pet.attribute1 ,pet.attribute2 ,pet.attribute3 ,pet.attribute4 ,pet.attribute5 ,pet.attribute6 ,pet.attribute7 ,pet.attribute8 ,pet.attribute9 ,pet.attribute10 ,pet.attribute11 ,pet.attribute12 ,pet.attribute13 ,pet.attribute14 ,pet.attribute15 ,pet.attribute16 ,pet.attribute17 ,pet.attribute18 ,pet.attribute19 ,pet.attribute20 ,pet.element_information_category ,pet.element_information1 ,pet.element_information2 ,pet.element_information3 ,pet.element_information4 ,pet.element_information5 ,pet.element_information6 ,pet.element_information7 ,pet.element_information8 ,pet.element_information9 ,pet.element_information10 ,pet.element_information11 ,pet.element_information12 ,pet.element_information13 ,pet.element_information14 ,pet.element_information15 ,pet.element_information16 ,pet.element_information17 ,pet.element_information18 ,pet.element_information19 ,pet.element_information20 ,pet.element_type_id ,pet.business_group_id ,pet.formula_id ,pet.classification_id ,pet.benefit_classification_id ,pet.adjustment_only_flag adjustment_only_flag_code ,pet.closed_for_entry_flag closed_for_entry_flag_code ,pet.indirect_only_flag indirect_only_flag_code ,pet.multiple_entries_allowed_flag multiple_entries_allowed_code ,pet.multiply_value_flag multiply_value_flag_code ,pet.process_in_run_flag process_in_run_flag_code ,pet.standard_link_flag standard_link_flag_code ,pet.third_party_pay_only_flag third_party_pay_only_flag_code ,pet.iterative_flag iterative_flag_code ,pet.grossup_flag grossup_flag_code ,pet.creation_date ,decode(pet.created_by,null,null, pay_payfv_element_types_pkg.get_applsys_user(pet.created_by)) created_by ,pet.last_update_date ,decode(pet.last_updated_by,null,null, pay_payfv_element_types_pkg.get_applsys_user(pet.last_updated_by)) last_updated_by FROM pay_element_types_f pet, hr_all_organization_units_tl bgrT, pay_element_types_f_tl petT, pay_element_types_f_tl petT2, pay_element_classifications_tl pecT, ben_benefit_classifications bbc WHERE pet.element_type_id = petT.element_type_id AND petT.language = USERENV('LANG') AND pet.retro_summ_ele_id = petT2.element_type_id (+) AND petT2.language (+) = USERENV('LANG') AND pet.business_group_id+0 = bgrT.organization_id (+) AND bgrT.language (+) = USERENV('LANG') AND pecT.language = USERENV('LANG') AND pecT.classification_id = pet.classification_id AND pet.benefit_classification_id = bbc.benefit_classification_id(+) AND trunc(sysdate) BETWEEN pet.effective_start_date AND pet.effective_end_date AND ((hr_general.get_business_group_id is not null AND (pet.business_group_id +0 = hr_general.get_business_group_id OR pet.legislation_code = hr_api.return_legislation_code(hr_general.get_business_group_id) )) OR (hr_general.get_business_group_id is null) ) WITH READ ONLY

View Text - HTML Formatted

SELECT PETT.ELEMENT_NAME ELEMENT_NAME
, BGRT.NAME BUSINESS_GROUP_NAME
, PET.LEGISLATION_CODE
, DECODE(PET.FORMULA_ID
, NULL
, NULL
, PER_FORMULA_FUNCTIONS.GET_FORMULA (PET.FORMULA_ID
, PET.EFFECTIVE_START_DATE)) FORMULA_NAME
, PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PECT.CLASSIFICATION_NAME
, BBC.BENEFIT_CLASSIFICATION_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.ADDITIONAL_ENTRY_ALLOWED_FLAG) ADDITIONAL_ENTRY_ALLOWED_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.ADJUSTMENT_ONLY_FLAG) ADJUSTMENT_ONLY_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.CLOSED_FOR_ENTRY_FLAG) CLOSED_FOR_ENTRY_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.INDIRECT_ONLY_FLAG) INDIRECT_ONLY_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.MULTIPLE_ENTRIES_ALLOWED_FLAG) MULTIPLE_ENTRIES_ALLOWED_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.MULTIPLY_VALUE_FLAG) MULTIPLY_VALUE_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('TERMINATION_RULE'
, PET.POST_TERMINATION_RULE) POST_TERMINATION_RULE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.PROCESS_IN_RUN_FLAG) PROCESS_IN_RUN_FLAG
, PET.PROCESSING_PRIORITY
, HR_BIS.BIS_DECODE_LOOKUP('PROCESSING_TYPE'
, PET.PROCESSING_TYPE) PROCESSING_TYPE
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.STANDARD_LINK_FLAG) STANDARD_LINK_FLAG
, PET.QUALIFYING_AGE
, PET.QUALIFYING_LENGTH_OF_SERVICE
, HR_BIS.BIS_DECODE_LOOKUP('UNITS'
, PET.QUALIFYING_UNITS) QUALIFYING_UNITS
, PET.REPORTING_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.THIRD_PARTY_PAY_ONLY_FLAG) THIRD_PARTY_PAY_ONLY_FLAG
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.ITERATIVE_FLAG) ITERATIVE_FLAG
, DECODE(PET.ITERATIVE_FORMULA_ID
, NULL
, NULL
, PER_FORMULA_FUNCTIONS.GET_FORMULA (PET.ITERATIVE_FORMULA_ID
, PET.EFFECTIVE_START_DATE)) ITERATIVE_FORMULA_NAME
, PET.ITERATIVE_PRIORITY
, PETT2.ELEMENT_NAME RETRO_SUMM_ELEMENT
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PET.GROSSUP_FLAG) GROSSUP_FLAG
, DECODE(PET.PROCESS_MODE
, 'S'
, 'Y'
, 'N') SEPARATE_PAYMENT
, DECODE(PET.PROCESS_MODE
, 'P'
, 'Y'
, 'S'
, 'Y'
, 'N') PROCESS_SEPARATE_FLAG
, DECODE(PET.PRORATION_GROUP_ID
, NULL
, NULL
, PAY_PAYFV_ELEMENT_TYPES_PKG.GET_EVENT_GROUP_NAME(PET.PRORATION_GROUP_ID)) PRORATION_GROUP_NAME
, DECODE(PET.PRORATION_FORMULA_ID
, NULL
, NULL
, PER_FORMULA_FUNCTIONS.GET_FORMULA (PET.PRORATION_FORMULA_ID
, PET.EFFECTIVE_START_DATE)) PRORATION_FORMULA_NAME
, DECODE(PET.RECALC_EVENT_GROUP_ID
, NULL
, NULL
, PAY_PAYFV_ELEMENT_TYPES_PKG.GET_EVENT_GROUP_NAME(PET.RECALC_EVENT_GROUP_ID)) RECALC_EVENT_GROUP_NAME
, PET.ATTRIBUTE_CATEGORY
, PET.ATTRIBUTE1
, PET.ATTRIBUTE2
, PET.ATTRIBUTE3
, PET.ATTRIBUTE4
, PET.ATTRIBUTE5
, PET.ATTRIBUTE6
, PET.ATTRIBUTE7
, PET.ATTRIBUTE8
, PET.ATTRIBUTE9
, PET.ATTRIBUTE10
, PET.ATTRIBUTE11
, PET.ATTRIBUTE12
, PET.ATTRIBUTE13
, PET.ATTRIBUTE14
, PET.ATTRIBUTE15
, PET.ATTRIBUTE16
, PET.ATTRIBUTE17
, PET.ATTRIBUTE18
, PET.ATTRIBUTE19
, PET.ATTRIBUTE20
, PET.ELEMENT_INFORMATION_CATEGORY
, PET.ELEMENT_INFORMATION1
, PET.ELEMENT_INFORMATION2
, PET.ELEMENT_INFORMATION3
, PET.ELEMENT_INFORMATION4
, PET.ELEMENT_INFORMATION5
, PET.ELEMENT_INFORMATION6
, PET.ELEMENT_INFORMATION7
, PET.ELEMENT_INFORMATION8
, PET.ELEMENT_INFORMATION9
, PET.ELEMENT_INFORMATION10
, PET.ELEMENT_INFORMATION11
, PET.ELEMENT_INFORMATION12
, PET.ELEMENT_INFORMATION13
, PET.ELEMENT_INFORMATION14
, PET.ELEMENT_INFORMATION15
, PET.ELEMENT_INFORMATION16
, PET.ELEMENT_INFORMATION17
, PET.ELEMENT_INFORMATION18
, PET.ELEMENT_INFORMATION19
, PET.ELEMENT_INFORMATION20
, PET.ELEMENT_TYPE_ID
, PET.BUSINESS_GROUP_ID
, PET.FORMULA_ID
, PET.CLASSIFICATION_ID
, PET.BENEFIT_CLASSIFICATION_ID
, PET.ADJUSTMENT_ONLY_FLAG ADJUSTMENT_ONLY_FLAG_CODE
, PET.CLOSED_FOR_ENTRY_FLAG CLOSED_FOR_ENTRY_FLAG_CODE
, PET.INDIRECT_ONLY_FLAG INDIRECT_ONLY_FLAG_CODE
, PET.MULTIPLE_ENTRIES_ALLOWED_FLAG MULTIPLE_ENTRIES_ALLOWED_CODE
, PET.MULTIPLY_VALUE_FLAG MULTIPLY_VALUE_FLAG_CODE
, PET.PROCESS_IN_RUN_FLAG PROCESS_IN_RUN_FLAG_CODE
, PET.STANDARD_LINK_FLAG STANDARD_LINK_FLAG_CODE
, PET.THIRD_PARTY_PAY_ONLY_FLAG THIRD_PARTY_PAY_ONLY_FLAG_CODE
, PET.ITERATIVE_FLAG ITERATIVE_FLAG_CODE
, PET.GROSSUP_FLAG GROSSUP_FLAG_CODE
, PET.CREATION_DATE
, DECODE(PET.CREATED_BY
, NULL
, NULL
, PAY_PAYFV_ELEMENT_TYPES_PKG.GET_APPLSYS_USER(PET.CREATED_BY)) CREATED_BY
, PET.LAST_UPDATE_DATE
, DECODE(PET.LAST_UPDATED_BY
, NULL
, NULL
, PAY_PAYFV_ELEMENT_TYPES_PKG.GET_APPLSYS_USER(PET.LAST_UPDATED_BY)) LAST_UPDATED_BY
FROM PAY_ELEMENT_TYPES_F PET
, HR_ALL_ORGANIZATION_UNITS_TL BGRT
, PAY_ELEMENT_TYPES_F_TL PETT
, PAY_ELEMENT_TYPES_F_TL PETT2
, PAY_ELEMENT_CLASSIFICATIONS_TL PECT
, BEN_BENEFIT_CLASSIFICATIONS BBC
WHERE PET.ELEMENT_TYPE_ID = PETT.ELEMENT_TYPE_ID
AND PETT.LANGUAGE = USERENV('LANG')
AND PET.RETRO_SUMM_ELE_ID = PETT2.ELEMENT_TYPE_ID (+)
AND PETT2.LANGUAGE (+) = USERENV('LANG')
AND PET.BUSINESS_GROUP_ID+0 = BGRT.ORGANIZATION_ID (+)
AND BGRT.LANGUAGE (+) = USERENV('LANG')
AND PECT.LANGUAGE = USERENV('LANG')
AND PECT.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PET.BENEFIT_CLASSIFICATION_ID = BBC.BENEFIT_CLASSIFICATION_ID(+)
AND TRUNC(SYSDATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND ((HR_GENERAL.GET_BUSINESS_GROUP_ID IS NOT NULL
AND (PET.BUSINESS_GROUP_ID +0 = HR_GENERAL.GET_BUSINESS_GROUP_ID OR PET.LEGISLATION_CODE = HR_API.RETURN_LEGISLATION_CODE(HR_GENERAL.GET_BUSINESS_GROUP_ID) )) OR (HR_GENERAL.GET_BUSINESS_GROUP_ID IS NULL) ) WITH READ ONLY