HXT Earning Variance Calculation

➜ Back to Library
Description
Categories: BI Publisher
Application: Time and Labor
Source: Earning Variance Calculation (XML)
Short Name: HXT953A_XML
DB package: HXT_HXT953A_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

/*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
Period Type 
 
 LOV Oracle
Start Date 
 
 Date
End Date 
 
 Date
Download
 
Related Blitz Reports
HXT Location Variance Calculation HXT Organization Variance Calc HXT Actual and Rounded Time Punch HXT Total Hours To BEE HXT OTLR Missing Timecard HXT Person/Assignment Table List HXT Periodic Late Clock-In HXT Work Plan Table Listing PAY Tax Payments Listing
Blitz Report™

Blitz Report™ provides multiple benefits: