VIEW: APPS.HXT_ADD_ELEM_INFO_D

Source

View Text - Preformatted

SELECT aei.id ,aei.effective_start_date ,aei.effective_end_date ,eltt.element_name ,fcl1.meaning earning_category_name ,fcl2.meaning absence_type_name ,aei.absence_points ,fcl3.meaning premium_type_name ,aei.premium_amount ,aei.processing_order ,aei.expenditure_type ,aei.last_update_date , fuser.user_name last_updated_by FROM pa_expenditure_types_v pxtv, hr_lookups fcl3, hr_lookups fcl2, hr_lookups fcl1, pay_element_types_f_tl eltt, pay_element_types_f pet, hxt_add_elem_info_f aei, fnd_user fuser WHERE aei.element_type_id = pet.element_type_id AND eltt.element_type_id(+) = pet.element_type_id AND decode(eltt.element_type_id,NULL,'1',eltt.language) = decode(eltt.element_type_id,NULL,'1',userenv('LANG')) AND aei.effective_start_date between nvl(pet.effective_start_date,aei.effective_start_date) and nvl(pet.effective_end_date, aei.effective_end_date) AND aei.earning_category = fcl1.lookup_code AND fcl1.application_id = 808 AND fcl1.lookup_type = 'HXT_EARNING_CATEGORY' AND fcl1.enabled_flag = 'Y' AND aei.effective_start_date between nvl(fcl2.start_date_active, aei.effective_start_date) and nvl(fcl1.end_date_active, aei.effective_end_date) AND aei.absence_type = fcl2.lookup_code(+) AND fcl2.application_id(+) = 808 AND fcl2.lookup_type(+) = 'HXT_ABSENCE_TYPES' AND fcl2.enabled_flag(+) = 'Y' AND aei.effective_start_date between nvl(fcl2.start_date_active, aei.effective_start_date) and nvl(fcl2.end_date_active, aei.effective_end_date) AND aei.premium_type = fcl3.lookup_code(+) AND fcl3.application_id(+) = 808 AND fcl3.lookup_type(+) = 'HXT_PREMIUM_TYPE' AND fcl3.enabled_flag(+) = 'Y' AND aei.effective_start_date between nvl(fcl3.start_date_active , aei.effective_start_date) and nvl(fcl3.end_date_active, aei.effective_end_date) AND aei.expenditure_type = pxtv.expenditure_type(+) AND aei.last_updated_by = fuser.user_id(+)

View Text - HTML Formatted

SELECT AEI.ID
, AEI.EFFECTIVE_START_DATE
, AEI.EFFECTIVE_END_DATE
, ELTT.ELEMENT_NAME
, FCL1.MEANING EARNING_CATEGORY_NAME
, FCL2.MEANING ABSENCE_TYPE_NAME
, AEI.ABSENCE_POINTS
, FCL3.MEANING PREMIUM_TYPE_NAME
, AEI.PREMIUM_AMOUNT
, AEI.PROCESSING_ORDER
, AEI.EXPENDITURE_TYPE
, AEI.LAST_UPDATE_DATE
, FUSER.USER_NAME LAST_UPDATED_BY
FROM PA_EXPENDITURE_TYPES_V PXTV
, HR_LOOKUPS FCL3
, HR_LOOKUPS FCL2
, HR_LOOKUPS FCL1
, PAY_ELEMENT_TYPES_F_TL ELTT
, PAY_ELEMENT_TYPES_F PET
, HXT_ADD_ELEM_INFO_F AEI
, FND_USER FUSER
WHERE AEI.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND ELTT.ELEMENT_TYPE_ID(+) = PET.ELEMENT_TYPE_ID
AND DECODE(ELTT.ELEMENT_TYPE_ID
, NULL
, '1'
, ELTT.LANGUAGE) = DECODE(ELTT.ELEMENT_TYPE_ID
, NULL
, '1'
, USERENV('LANG'))
AND AEI.EFFECTIVE_START_DATE BETWEEN NVL(PET.EFFECTIVE_START_DATE
, AEI.EFFECTIVE_START_DATE)
AND NVL(PET.EFFECTIVE_END_DATE
, AEI.EFFECTIVE_END_DATE)
AND AEI.EARNING_CATEGORY = FCL1.LOOKUP_CODE
AND FCL1.APPLICATION_ID = 808
AND FCL1.LOOKUP_TYPE = 'HXT_EARNING_CATEGORY'
AND FCL1.ENABLED_FLAG = 'Y'
AND AEI.EFFECTIVE_START_DATE BETWEEN NVL(FCL2.START_DATE_ACTIVE
, AEI.EFFECTIVE_START_DATE)
AND NVL(FCL1.END_DATE_ACTIVE
, AEI.EFFECTIVE_END_DATE)
AND AEI.ABSENCE_TYPE = FCL2.LOOKUP_CODE(+)
AND FCL2.APPLICATION_ID(+) = 808
AND FCL2.LOOKUP_TYPE(+) = 'HXT_ABSENCE_TYPES'
AND FCL2.ENABLED_FLAG(+) = 'Y'
AND AEI.EFFECTIVE_START_DATE BETWEEN NVL(FCL2.START_DATE_ACTIVE
, AEI.EFFECTIVE_START_DATE)
AND NVL(FCL2.END_DATE_ACTIVE
, AEI.EFFECTIVE_END_DATE)
AND AEI.PREMIUM_TYPE = FCL3.LOOKUP_CODE(+)
AND FCL3.APPLICATION_ID(+) = 808
AND FCL3.LOOKUP_TYPE(+) = 'HXT_PREMIUM_TYPE'
AND FCL3.ENABLED_FLAG(+) = 'Y'
AND AEI.EFFECTIVE_START_DATE BETWEEN NVL(FCL3.START_DATE_ACTIVE
, AEI.EFFECTIVE_START_DATE)
AND NVL(FCL3.END_DATE_ACTIVE
, AEI.EFFECTIVE_END_DATE)
AND AEI.EXPENDITURE_TYPE = PXTV.EXPENDITURE_TYPE(+)
AND AEI.LAST_UPDATED_BY = FUSER.USER_ID(+)