HXT Earning Variance Calculation

Description
Categories: BI Publisher, Human Resources
Application: Time and Labor
Source: Earning Variance Calculation (XML)
Short Name: HXT953A_XML
DB package: HXT_HXT953A_XMLP_PKG
/*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')
)
Parameter Name SQL text Validation
End Date
 
Date
Start Date
 
Date
Period Type
 
LOV Oracle