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 FF, FF_FORMULAS_F FF1, FF_FORMULAS_F 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) 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 FF
, FF_FORMULAS_F FF1
, FF_FORMULAS_F 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)
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(+)