HXT Organization Variance Calc

Description
Categories: BI Publisher
Application: Time and Labor
Source: Organization Variance Calc (XML)
Short Name: HXT951A_XML
DB package: HXT_HXT951A_XMLP_PKG

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select NAME,HIGH,LOW,AVERAGE,DET_HRS,
	HXT_HXT951A_XMLP_PKG.high1formula(TOT_HRS, HIGH) HIGH1, 
	HXT_HXT951A_XMLP_PKG.low1formula(TOT_HRS, LOW) LOW1, 
	HXT_HXT951A_XMLP_PKG.average1formula(TOT_HRS, AVERAGE) AVERAGE1
from
(	
SELECT 
ORG.NAME NAME, 
VAR.HIGH HIGH, 
VAR.LOW LOW, 
VAR.AVERAGE AVERAGE,
NVL(DET.HOURS,0) DET_HRS, 
sum(NVL(DET.HOURS,0)) over (partition by  ORG.NAME,VAR.HIGH,VAR.LOW,VAR.AVERAGE) TOT_HRS
FROM HXT_VARIANCES VAR,
HR_ORGANIZATION_UNITS ORG,
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='ORG1'
and VAR.FCL_PERIOD = :period_type
and VAR.ORGANIZATION_ID=ORG.ORGANIZATION_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 = ELTV.ELEMENT_TYPE_ID                   
and DET.DATE_WORKED between ELTV.EFFECTIVE_START_DATE     
                                             and ELTV.EFFECTIVE_END_DATE         
and ASM.ORGANIZATION_ID=VAR.ORGANIZATION_ID
and ELTV.HXT_EARNING_CATEGORY in ('REG','OVT','ABS')
)
Parameter Name SQL text Validation
Period Type
 
LOV Oracle
Start Date
 
Date
End Date
 
Date