HXT Location Variance Calculation

         select LOCATION_CODE,LOCATION_ID,HIGH,LOW,AVERAGE,DET_HRS,
	HXT_HXT952A_XMLP_PKG.high1formula(TOT_HRS, HIGH) HIGH1,
	HXT_HXT952A_XMLP_PKG.low1formula(TOT_HRS, LOW) LOW1,
	HXT_HXT952A_XMLP_PKG.average1formula(TOT_HRS, AVERAGE) AVERAGE1
from
(
SELECT
loct.LOCATION_CODE LOCATION_CODE,
VAR.LOCATION_ID LOCATION_ID,
VAR.HIGH HIGH,
VAR.LOW LOW,
VAR.AVERAGE AVERAGE,
NVL(DET.HOURS,0) DET_HRS,
sum(NVL(DET.HOURS,0)) over (partition by  loct.LOCATION_CODE,VAR.LOCATION_ID,VAR.HIGH,VAR.LOW,VAR.AVERAGE) TOT_HRS
FROM HXT_VARIANCES VAR,
HR_LOCATIONS_ALL_TL LOCT,
HR_LOCATIONS_NO_JOIN LOC,
PAY_ELEMENT_TYPES_F ELT,
HXT_PAY_ELEMENT_TYPES_F_DDF_V ELTV,
HXT_DET_HOURS_WORKED_X DET,
PER_ASSIGNMENTS_F ASM
WHERE VAR.VAR_TYPE='LOC1'
and VAR.FCL_PERIOD = :period_type
and VAR.LOCATION_ID=LOC.LOCATION_ID
AND loc.location_id = loct.location_id(+)
        AND decode(loct.location_id,null,'1',loct.language) =
            decode(loct.location_id,null,'1',userenv('LANG'))
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 = 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')
)