HXT Organization Variance Calc

➜ Back to Library
Description
Categories: BI Publisher
Application: Time and Labor
Source: Organization Variance Calc (XML)
Short Name: HXT951A_XML
DB package: HXT_HXT951A_XMLP_PKG
Run HXT Organization Variance Calc and other Oracle EBS reports with Blitz Report™ on our demo environment
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
Download
 
Related Blitz Reports
HXT Location Variance Calculation HXT Actual and Rounded Time Punch HXT Earning Variance Calculation HXT Periodic Late Clock-In HXT OTLR Missing Timecard HXT Total Hours To BEE HXT Person/Assignment Table List PAY Business Payrolls Survey PAY Payroll Reconciliation (New Zealand)
Blitz Report™

Blitz Report™ provides multiple benefits: