VIEW: APPS.PAY_ALL_DEDUCTION_TYPES_V

Source

View Text - Preformatted

SELECT ET.ELEMENT_TYPE_ID , ET.EFFECTIVE_START_DATE , ET.EFFECTIVE_END_DATE , ET.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID , ET.LEGISLATION_CODE , ETTL.ELEMENT_NAME , ETTL.REPORTING_NAME , ETTL.DESCRIPTION , ECTL.CLASSIFICATION_NAME, hr_general.decode_lookup(DECODE(EC.CLASSIFICATION_NAME, 'Voluntary Deductions', 'US_VOLUNTARY_DEDUCTIONS', 'Involuntary Deductions', 'US_INVOLUNTARY_DEDUCTIONS', 'Pre-Tax Deductions', 'US_PRE_TAX_DEDUCTIONS', NULL), ET.ELEMENT_INFORMATION1) CATEGORY , BC.BENEFIT_CLASSIFICATION_NAME BEN_CLASS_NAME , ET.PROCESSING_TYPE , ET.PROCESSING_PRIORITY , ET.STANDARD_LINK_FLAG , NVL(ET.ELEMENT_INFORMATION2, 'N') DEDUCT_PARTIAL , NVL(ET.ELEMENT_INFORMATION3, 'REG') PROCESSING_RUN_TYPE , ET.ELEMENT_INFORMATION6 PAYROLL_TABLE , ET.ELEMENT_INFORMATION7 PAYROLL_TABLE_ROW_TYPE , ET.ELEMENT_INFORMATION9 MIX_CATEGORY , hr_user_init_dedn.get_assoc_ele(ET.element_type_id, 'ER', ET.effective_start_date, ET.business_group_id) , hr_user_init_dedn.get_assoc_ele(ET.element_type_id, 'AT', ET.effective_start_date, ET.business_group_id) , ET.ELEMENT_INFORMATION10 PRIMARY_BALTYPE_ID , ET.ELEMENT_INFORMATION11 ACCRUED_BALTYPE_ID , ET.ELEMENT_INFORMATION12 ARREARS_BALTYPE_ID , ET.ELEMENT_INFORMATION13 NOT_TAKEN_BALTYPE_ID , ET.ELEMENT_INFORMATION14 TOBONDPURCH_BALTYPE_ID , ET.ELEMENT_INFORMATION15 ABLE_BALTYPE_ID , ET.ELEMENT_INFORMATION16 ADDITIONAL_BALTYPE_ID , ET.ELEMENT_INFORMATION17 REPLACEMENT_BALTYPE_ID , ET.ELEMENT_INFORMATION8 VOL_DEDNS_BALTYPE_ID , ET.ELEMENT_INFORMATION18 SPECIAL_INPUTS_ELE_ID , ET.ELEMENT_INFORMATION19 SPECIAL_FEATURES_ELE_ID , ET.ELEMENT_INFORMATION20 VERIFIER_ELE_ID , ET.ELEMENT_INFORMATION5 CALCULATION_ELE_ID , ET.LAST_UPDATE_DATE , ET.LAST_UPDATED_BY , ET.LAST_UPDATE_LOGIN , ET.CREATED_BY , ET.CREATION_DATE FROM PAY_ELEMENT_TYPES_F ET, PAY_ELEMENT_TYPES_F_TL ETTL, PAY_ELEMENT_CLASSIFICATIONS EC, PAY_ELEMENT_CLASSIFICATIONS_TL ECTL, BEN_BENEFIT_CLASSIFICATIONS BC WHERE ec.classification_id = ectl.classification_id and ectl.language= userenv('LANG') and et.element_type_id = ettl.element_type_id and ettl.language= userenv('LANG') and ET.EFFECTIVE_START_DATE <= (SELECT S.EFFECTIVE_DATE FROM FND_SESSIONS S WHERE S.SESSION_ID = USERENV('SESSIONID')) AND ET.EFFECTIVE_END_DATE >= (SELECT S.EFFECTIVE_DATE FROM FND_SESSIONS S WHERE S.SESSION_ID = USERENV('SESSIONID')) AND EC.CLASSIFICATION_NAME IN ( 'Voluntary Deductions', 'Involuntary Deductions', 'Pre-Tax Deductions') AND EC.CLASSIFICATION_ID = ET.CLASSIFICATION_ID AND BC.BENEFIT_CLASSIFICATION_ID(+) = ET.BENEFIT_CLASSIFICATION_ID AND EC.LEGISLATION_CODE = 'US'

View Text - HTML Formatted

SELECT ET.ELEMENT_TYPE_ID
, ET.EFFECTIVE_START_DATE
, ET.EFFECTIVE_END_DATE
, ET.BUSINESS_GROUP_ID + 0 BUSINESS_GROUP_ID
, ET.LEGISLATION_CODE
, ETTL.ELEMENT_NAME
, ETTL.REPORTING_NAME
, ETTL.DESCRIPTION
, ECTL.CLASSIFICATION_NAME
, HR_GENERAL.DECODE_LOOKUP(DECODE(EC.CLASSIFICATION_NAME
, 'VOLUNTARY DEDUCTIONS'
, 'US_VOLUNTARY_DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'US_INVOLUNTARY_DEDUCTIONS'
, 'PRE-TAX DEDUCTIONS'
, 'US_PRE_TAX_DEDUCTIONS'
, NULL)
, ET.ELEMENT_INFORMATION1) CATEGORY
, BC.BENEFIT_CLASSIFICATION_NAME BEN_CLASS_NAME
, ET.PROCESSING_TYPE
, ET.PROCESSING_PRIORITY
, ET.STANDARD_LINK_FLAG
, NVL(ET.ELEMENT_INFORMATION2
, 'N') DEDUCT_PARTIAL
, NVL(ET.ELEMENT_INFORMATION3
, 'REG') PROCESSING_RUN_TYPE
, ET.ELEMENT_INFORMATION6 PAYROLL_TABLE
, ET.ELEMENT_INFORMATION7 PAYROLL_TABLE_ROW_TYPE
, ET.ELEMENT_INFORMATION9 MIX_CATEGORY
, HR_USER_INIT_DEDN.GET_ASSOC_ELE(ET.ELEMENT_TYPE_ID
, 'ER'
, ET.EFFECTIVE_START_DATE
, ET.BUSINESS_GROUP_ID)
, HR_USER_INIT_DEDN.GET_ASSOC_ELE(ET.ELEMENT_TYPE_ID
, 'AT'
, ET.EFFECTIVE_START_DATE
, ET.BUSINESS_GROUP_ID)
, ET.ELEMENT_INFORMATION10 PRIMARY_BALTYPE_ID
, ET.ELEMENT_INFORMATION11 ACCRUED_BALTYPE_ID
, ET.ELEMENT_INFORMATION12 ARREARS_BALTYPE_ID
, ET.ELEMENT_INFORMATION13 NOT_TAKEN_BALTYPE_ID
, ET.ELEMENT_INFORMATION14 TOBONDPURCH_BALTYPE_ID
, ET.ELEMENT_INFORMATION15 ABLE_BALTYPE_ID
, ET.ELEMENT_INFORMATION16 ADDITIONAL_BALTYPE_ID
, ET.ELEMENT_INFORMATION17 REPLACEMENT_BALTYPE_ID
, ET.ELEMENT_INFORMATION8 VOL_DEDNS_BALTYPE_ID
, ET.ELEMENT_INFORMATION18 SPECIAL_INPUTS_ELE_ID
, ET.ELEMENT_INFORMATION19 SPECIAL_FEATURES_ELE_ID
, ET.ELEMENT_INFORMATION20 VERIFIER_ELE_ID
, ET.ELEMENT_INFORMATION5 CALCULATION_ELE_ID
, ET.LAST_UPDATE_DATE
, ET.LAST_UPDATED_BY
, ET.LAST_UPDATE_LOGIN
, ET.CREATED_BY
, ET.CREATION_DATE
FROM PAY_ELEMENT_TYPES_F ET
, PAY_ELEMENT_TYPES_F_TL ETTL
, PAY_ELEMENT_CLASSIFICATIONS EC
, PAY_ELEMENT_CLASSIFICATIONS_TL ECTL
, BEN_BENEFIT_CLASSIFICATIONS BC
WHERE EC.CLASSIFICATION_ID = ECTL.CLASSIFICATION_ID
AND ECTL.LANGUAGE= USERENV('LANG')
AND ET.ELEMENT_TYPE_ID = ETTL.ELEMENT_TYPE_ID
AND ETTL.LANGUAGE= USERENV('LANG')
AND ET.EFFECTIVE_START_DATE <= (SELECT S.EFFECTIVE_DATE
FROM FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID'))
AND ET.EFFECTIVE_END_DATE >= (SELECT S.EFFECTIVE_DATE
FROM FND_SESSIONS S
WHERE S.SESSION_ID = USERENV('SESSIONID'))
AND EC.CLASSIFICATION_NAME IN ( 'VOLUNTARY DEDUCTIONS'
, 'INVOLUNTARY DEDUCTIONS'
, 'PRE-TAX DEDUCTIONS')
AND EC.CLASSIFICATION_ID = ET.CLASSIFICATION_ID
AND BC.BENEFIT_CLASSIFICATION_ID(+) = ET.BENEFIT_CLASSIFICATION_ID
AND EC.LEGISLATION_CODE = 'US'