VIEW: APPS.PAY_ELEMENT_TYPES_DENORM_V

Source

View Text - Preformatted

SELECT pet.rowid row_id , pet.effective_start_date , pet.effective_end_date , pet.element_type_id , pet.business_group_id + 0 business_group_id , pet.legislation_code , pet.input_currency_code , pet.output_currency_code , pet.closed_for_entry_flag , pet.additional_entry_allowed_flag , pet.indirect_only_flag , pet.adjustment_only_flag , pet.multiple_entries_allowed_flag , pet.multiply_value_flag , pet.process_in_run_flag , pet.standard_link_flag , pet.processing_type , pet.qualifying_units , hr.meaning qualifying_unit_name , pet.post_termination_rule , pettl.element_name , pet.element_name base_element_name , pet.processing_priority , pettl.description , pet.legislation_subgroup , pet.qualifying_age , pet.qualifying_length_of_service , pettl.reporting_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.last_update_date , pet.last_updated_by , pet.last_update_login , pet.created_by , pet.creation_date , 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 , ff.formula_name , pet.formula_id , pectl.classification_name , pec.default_low_priority , pec.default_high_priority , pet.classification_id , pec.non_payments_flag , pec.balance_initialization_flag , pet.comment_id , ben.benefit_classification_name , ben.cobra_flag , ben.contributions_used , pet.benefit_classification_id , pet.third_party_pay_only_flag , pet.retro_summ_ele_id , pettl1.element_name , pet.iterative_flag , pet.iterative_formula_id , ff1.formula_name , pet.iterative_priority , pet.process_mode , pet.grossup_flag , decode (pet.process_mode, 'Y' , 'Y', 'N' , 'N', 'P' , 'N', 'S' , 'Y') separate_payment , decode (pet.process_mode, 'Y' , 'Y', 'N' , 'N', 'P' , 'Y', 'S' , 'Y') process_separate_flag , pet.advance_indicator , pet.advance_payable , pet.advance_deduction , pet.process_advance_entry , pet.creator_type , hrl2.meaning d_creator_type , pet.proration_group_id , peg.event_group_name , pet.proration_formula_id , ff2.formula_name , pet.recalc_event_group_id , peg1.event_group_name , pec.freq_rule_enabled , pet.once_each_period_flag , decode (pet.time_definition_type, 'N' , NULL, pet.time_definition_type) time_definition_type , pet.time_definition_id , pet.advance_element_type_id , pet.deduction_element_type_id FROM pay_element_types_f_tl pettl1 , pay_element_types_f pet1 , pay_event_groups peg , pay_element_types_f_tl pettl , pay_element_types_f pet , pay_element_classifications_tl pectl , pay_element_classifications pec , ben_benefit_classifications ben , fnd_sessions fnd , ff_formulas_f_vl ff , ff_formulas_f_vl ff1 , ff_formulas_f_vl ff2 , pay_event_groups peg1 , ( SELECT lookup_code , meaning FROM hr_lookups WHERE lookup_type = 'QUALIFYING_UNITS' ) hr , ( SELECT lookup_code , meaning FROM hr_lookups WHERE lookup_type = 'ELEMENT_CREATOR_TYPE' ) hrl2 WHERE pet.element_type_id = pettl.element_type_id AND pettl.language = userenv ('LANG') AND pec.classification_id = pectl.classification_id AND pectl.language = userenv ('LANG') AND pet.classification_id = pec.classification_id AND pet.formula_id = ff.formula_id (+) AND pet.benefit_classification_id = ben.benefit_classification_id (+) AND pet.qualifying_units = hr.lookup_code (+) AND pet.creator_type = hrl2.lookup_code (+) AND fnd.session_id = userenv ('sessionid') AND fnd.effective_date BETWEEN pet.effective_start_date AND pet.effective_end_date AND fnd.effective_date BETWEEN nvl (ff.effective_start_date, fnd.effective_date) AND nvl (ff.effective_end_date, fnd.effective_date) AND pet.retro_summ_ele_id = pet1.element_type_id (+) AND ( ( fnd.effective_date BETWEEN nvl (pet1.effective_start_date, fnd.effective_date) AND nvl (pet1.effective_end_date, fnd.effective_date) ) OR EXISTS ( SELECT NULL FROM pay_legislation_rules WHERE rule_type = 'ADVANCED_RETRO' AND rule_mode = 'Y' AND legislation_code = nvl (pet.legislation_code, pay_core_utils.get_legislation_code (pet.business_group_id)) ) ) AND decode (pettl1.element_type_id, NULL, '1', pettl1.language) = decode (pettl1.element_type_id, NULL, '1', userenv ('LANG')) AND pet.proration_group_id = peg.event_group_id (+) AND fnd.effective_date BETWEEN nvl (ff1.effective_start_date, fnd.effective_date) AND nvl (ff1.effective_end_date, fnd.effective_date) AND pet.iterative_formula_id = ff1.formula_id (+) AND fnd.effective_date BETWEEN nvl (ff2.effective_start_date, fnd.effective_date) AND nvl (ff2.effective_end_date, fnd.effective_date) AND pet.proration_formula_id = ff2.formula_id (+) AND pet.recalc_event_group_id = peg1.event_group_id (+) AND pet1.element_type_id = pettl1.element_type_id (+)

View Text - HTML Formatted

SELECT PET.ROWID ROW_ID
, PET.EFFECTIVE_START_DATE
, PET.EFFECTIVE_END_DATE
, PET.ELEMENT_TYPE_ID
, PET.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, PET.LEGISLATION_CODE
, PET.INPUT_CURRENCY_CODE
, PET.OUTPUT_CURRENCY_CODE
, PET.CLOSED_FOR_ENTRY_FLAG
, PET.ADDITIONAL_ENTRY_ALLOWED_FLAG
, PET.INDIRECT_ONLY_FLAG
, PET.ADJUSTMENT_ONLY_FLAG
, PET.MULTIPLE_ENTRIES_ALLOWED_FLAG
, PET.MULTIPLY_VALUE_FLAG
, PET.PROCESS_IN_RUN_FLAG
, PET.STANDARD_LINK_FLAG
, PET.PROCESSING_TYPE
, PET.QUALIFYING_UNITS
, HR.MEANING QUALIFYING_UNIT_NAME
, PET.POST_TERMINATION_RULE
, PETTL.ELEMENT_NAME
, PET.ELEMENT_NAME BASE_ELEMENT_NAME
, PET.PROCESSING_PRIORITY
, PETTL.DESCRIPTION
, PET.LEGISLATION_SUBGROUP
, PET.QUALIFYING_AGE
, PET.QUALIFYING_LENGTH_OF_SERVICE
, PETTL.REPORTING_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.LAST_UPDATE_DATE
, PET.LAST_UPDATED_BY
, PET.LAST_UPDATE_LOGIN
, PET.CREATED_BY
, PET.CREATION_DATE
, 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
, FF.FORMULA_NAME
, PET.FORMULA_ID
, PECTL.CLASSIFICATION_NAME
, PEC.DEFAULT_LOW_PRIORITY
, PEC.DEFAULT_HIGH_PRIORITY
, PET.CLASSIFICATION_ID
, PEC.NON_PAYMENTS_FLAG
, PEC.BALANCE_INITIALIZATION_FLAG
, PET.COMMENT_ID
, BEN.BENEFIT_CLASSIFICATION_NAME
, BEN.COBRA_FLAG
, BEN.CONTRIBUTIONS_USED
, PET.BENEFIT_CLASSIFICATION_ID
, PET.THIRD_PARTY_PAY_ONLY_FLAG
, PET.RETRO_SUMM_ELE_ID
, PETTL1.ELEMENT_NAME
, PET.ITERATIVE_FLAG
, PET.ITERATIVE_FORMULA_ID
, FF1.FORMULA_NAME
, PET.ITERATIVE_PRIORITY
, PET.PROCESS_MODE
, PET.GROSSUP_FLAG
, DECODE (PET.PROCESS_MODE
, 'Y'
, 'Y'
, 'N'
, 'N'
, 'P'
, 'N'
, 'S'
, 'Y') SEPARATE_PAYMENT
, DECODE (PET.PROCESS_MODE
, 'Y'
, 'Y'
, 'N'
, 'N'
, 'P'
, 'Y'
, 'S'
, 'Y') PROCESS_SEPARATE_FLAG
, PET.ADVANCE_INDICATOR
, PET.ADVANCE_PAYABLE
, PET.ADVANCE_DEDUCTION
, PET.PROCESS_ADVANCE_ENTRY
, PET.CREATOR_TYPE
, HRL2.MEANING D_CREATOR_TYPE
, PET.PRORATION_GROUP_ID
, PEG.EVENT_GROUP_NAME
, PET.PRORATION_FORMULA_ID
, FF2.FORMULA_NAME
, PET.RECALC_EVENT_GROUP_ID
, PEG1.EVENT_GROUP_NAME
, PEC.FREQ_RULE_ENABLED
, PET.ONCE_EACH_PERIOD_FLAG
, DECODE (PET.TIME_DEFINITION_TYPE
, 'N'
, NULL
, PET.TIME_DEFINITION_TYPE) TIME_DEFINITION_TYPE
, PET.TIME_DEFINITION_ID
, PET.ADVANCE_ELEMENT_TYPE_ID
, PET.DEDUCTION_ELEMENT_TYPE_ID
FROM PAY_ELEMENT_TYPES_F_TL PETTL1
, PAY_ELEMENT_TYPES_F PET1
, PAY_EVENT_GROUPS PEG
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, BEN_BENEFIT_CLASSIFICATIONS BEN
, FND_SESSIONS FND
, FF_FORMULAS_F_VL FF
, FF_FORMULAS_F_VL FF1
, FF_FORMULAS_F_VL FF2
, PAY_EVENT_GROUPS PEG1
, ( SELECT LOOKUP_CODE
, MEANING
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'QUALIFYING_UNITS' ) HR
, ( SELECT LOOKUP_CODE
, MEANING
FROM HR_LOOKUPS
WHERE LOOKUP_TYPE = 'ELEMENT_CREATOR_TYPE' ) HRL2
WHERE PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV ('LANG')
AND PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV ('LANG')
AND PET.CLASSIFICATION_ID = PEC.CLASSIFICATION_ID
AND PET.FORMULA_ID = FF.FORMULA_ID (+)
AND PET.BENEFIT_CLASSIFICATION_ID = BEN.BENEFIT_CLASSIFICATION_ID (+)
AND PET.QUALIFYING_UNITS = HR.LOOKUP_CODE (+)
AND PET.CREATOR_TYPE = HRL2.LOOKUP_CODE (+)
AND FND.SESSION_ID = USERENV ('SESSIONID')
AND FND.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND FND.EFFECTIVE_DATE BETWEEN NVL (FF.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL (FF.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND PET.RETRO_SUMM_ELE_ID = PET1.ELEMENT_TYPE_ID (+)
AND ( ( FND.EFFECTIVE_DATE BETWEEN NVL (PET1.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL (PET1.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE) ) OR EXISTS ( SELECT NULL
FROM PAY_LEGISLATION_RULES
WHERE RULE_TYPE = 'ADVANCED_RETRO'
AND RULE_MODE = 'Y'
AND LEGISLATION_CODE = NVL (PET.LEGISLATION_CODE
, PAY_CORE_UTILS.GET_LEGISLATION_CODE (PET.BUSINESS_GROUP_ID)) ) )
AND DECODE (PETTL1.ELEMENT_TYPE_ID
, NULL
, '1'
, PETTL1.LANGUAGE) = DECODE (PETTL1.ELEMENT_TYPE_ID
, NULL
, '1'
, USERENV ('LANG'))
AND PET.PRORATION_GROUP_ID = PEG.EVENT_GROUP_ID (+)
AND FND.EFFECTIVE_DATE BETWEEN NVL (FF1.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL (FF1.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND PET.ITERATIVE_FORMULA_ID = FF1.FORMULA_ID (+)
AND FND.EFFECTIVE_DATE BETWEEN NVL (FF2.EFFECTIVE_START_DATE
, FND.EFFECTIVE_DATE)
AND NVL (FF2.EFFECTIVE_END_DATE
, FND.EFFECTIVE_DATE)
AND PET.PRORATION_FORMULA_ID = FF2.FORMULA_ID (+)
AND PET.RECALC_EVENT_GROUP_ID = PEG1.EVENT_GROUP_ID (+)
AND PET1.ELEMENT_TYPE_ID = PETTL1.ELEMENT_TYPE_ID (+)