/*Below lines commeneted during DT Fix
SELECT
ELTT.ELEMENT_NAME ELEMENT_NAME,
VAR.HIGH,
VAR.LOW,
VAR.AVERAGE,
NVL(DET.HOURS,0) DET_HRS,
HXT_HXT953A_XMLP_PKG.high1formula(:TOT_HOURS, VAR.HIGH) HIGH1,
HXT_HXT953A_XMLP_PKG.low1formula(:TOT_HOURS, VAR.LOW) LOW1,
HXT_HXT953A_XMLP_PKG.average1formula(:TOT_HOURS, VAR.AVERAGE) AVERAGE1
FROM HXT_VARIANCES VAR,
PAY_ELEMENT_TYPES_F ELT,
PAY_ELEMENT_TYPES_F_TL ELTT,
HXT_PAY_ELEMENT_TYPES_F_DDF_V ELTV,
HXT_DET_HOURS_WORKED_X DET,
PER_ASSIGNMENTS_F ASM
WHERE VAR.VAR_TYPE='EAR'
and VAR.FCL_PERIOD = :period_type
and VAR.VAR_TYPE_ID = ELT.ELEMENT_TYPE_ID
and DET.ASSIGNMENT_ID=ASM.ASSIGNMENT_ID
and DET.DATE_WORKED between ASM.EFFECTIVE_START_DATE
and ASM.EFFECTIVE_END_DATE
and ELT.ELEMENT_TYPE_ID=DET.ELEMENT_TYPE_ID
and DET.DATE_WORKED between ELT.EFFECTIVE_START_DATE
and ELT.EFFECTIVE_END_DATE
and ELT.element_type_id = ELTT.element_type_id
and ELTT.language = userenv('LANG')
and ELT.element_type_id = ELTV.element_type_id
and det.date_worked between eltv.effective_start_date and eltv.effective_end_date
and ELTV.HXT_EARNING_CATEGORY in ('REG','OVT','ABS')
*/
select
ELEMENT_NAME,HIGH,LOW,AVERAGE,DET_HRS,
HXT_HXT953A_XMLP_PKG.high1formula(TOT_HRS, HIGH) HIGH1,
HXT_HXT953A_XMLP_PKG.low1formula(TOT_HRS, LOW) LOW1,
HXT_HXT953A_XMLP_PKG.average1formula(TOT_HRS, AVERAGE) AVERAGE1
from (
SELECT
ELTT.ELEMENT_NAME ELEMENT_NAME,
VAR.HIGH HIGH,
VAR.LOW LOW,
VAR.AVERAGE AVERAGE,
NVL(DET.HOURS,0) DET_HRS,
sum(NVL(DET.HOURS,0)) over(partition by ELTT.ELEMENT_NAME,VAR.HIGH ,VAR.LOW,VAR.AVERAGE) TOT_HRS
FROM HXT_VARIANCES VAR,
PAY_ELEMENT_TYPES_F ELT,
PAY_ELEMENT_TYPES_F_TL ELTT,
HXT_PAY_ELEMENT_TYPES_F_DDF_V ELTV,
HXT_DET_HOURS_WORKED_X DET,
PER_ASSIGNMENTS_F ASM
WHERE VAR.VAR_TYPE='EAR'
and VAR.FCL_PERIOD = :period_type
and VAR.VAR_TYPE_ID = ELT.ELEMENT_TYPE_ID
and DET.ASSIGNMENT_ID=ASM.ASSIGNMENT_ID
and DET.DATE_WORKED between ASM.EFFECTIVE_START_DATE
and ASM.EFFECTIVE_END_DATE
and ELT.ELEMENT_TYPE_ID=DET.ELEMENT_TYPE_ID
and DET.DATE_WORKED between ELT.EFFECTIVE_START_DATE
and ELT.EFFECTIVE_END_DATE
and ELT.element_type_id = ELTT.element_type_id
and ELTT.language = userenv('LANG')
and ELT.element_type_id = ELTV.element_type_id
and det.date_worked between eltv.effective_start_date and eltv.effective_end_date
and ELTV.HXT_EARNING_CATEGORY in ('REG','OVT','ABS')
) |
