Product: | HXT - Time and Labor |
---|---|
Description: | |
Implementation/DBA Data: | APPS.HXT_ADD_ELEM_INFO_D |
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(+)