HXT Actual and Rounded Time Punch

➜ Back to Library

Description
Categories: BI Publisher, Human Resources Application: Time and Labor Source: Actual and Rounded Time Punch (XML) Short Name: HXT958A_XML DB package: HXT_HXT958A_XMLP_PKG more less

Copy

SELECT  
  PER_PEOPLE_F.EMPLOYEE_NUMBER, 
  PER_PEOPLE_F.FULL_NAME,
  HXT_TIMECARDS_X.FOR_PERSON_ID,
  HXT_SUM_HOURS_WORKED_X.DATE_WORKED, 
  HXT_SUM_HOURS_WORKED_X.TIME_IN, 
  HXT_SUM_HOURS_WORKED_X.TIME_OUT, 
  HXT_SUM_HOURS_WORKED_X.ACTUAL_TIME_IN, 
  HXT_SUM_HOURS_WORKED_X.ACTUAL_TIME_OUT,
  HXT_SUM_HOURS_WORKED_X.HOURS, 
  HXT_SUM_HOURS_WORKED_X.ID,
  SUM(DECODE(HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_EARNING_CATEGORY,'REG',HXT_DET_HOURS_WORKED_X.HOURS,0)) REG,
  SUM(DECODE(HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_EARNING_CATEGORY,'ABS',HXT_DET_HOURS_WORKED_X.HOURS,0)) ABS,
  SUM(DECODE(HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_EARNING_CATEGORY,'OVT',HXT_DET_HOURS_WORKED_X.HOURS,0)) OVT,
  SUM(DECODE(HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_EARNING_CATEGORY,'OTH',HXT_DET_HOURS_WORKED_X.HOURS,0)) OTH,
  PER_ASSIGNMENTS_F.ASSIGNMENT_ID,
  HR_ORGANIZATION_UNITS.NAME ORG_NAME
FROM 
  PER_PEOPLE_F, 
  HXT_TIMECARDS_X,      
  HXT_DET_HOURS_WORKED_X,
  HXT_SUM_HOURS_WORKED_X,
  HR_ORGANIZATION_UNITS, 
  PER_ASSIGNMENTS_F,  
  PAY_ELEMENT_TYPES_F,
  HXT_PAY_ELEMENT_TYPES_F_DDF_V
WHERE
    HXT_DET_HOURS_WORKED_X.parent_id=HXT_SUM_HOURS_WORKED_X.id 
    AND PER_PEOPLE_F.PERSON_ID=HXT_TIMECARDS_X.FOR_PERSON_ID
    AND HXT_DET_HOURS_WORKED_X.TIM_ID=HXT_TIMECARDS_X.ID
    AND PER_ASSIGNMENTS_F.ORGANIZATION_ID=HR_ORGANIZATION_UNITS.ORGANIZATION_ID
    AND HXT_DET_HOURS_WORKED_X.ASSIGNMENT_ID=PER_ASSIGNMENTS_F.ASSIGNMENT_ID
    AND HXT_DET_HOURS_WORKED_X.DATE_WORKED between :P_START_DATE and :P_END_DATE 
    AND HXT_DET_HOURS_WORKED_X.DATE_WORKED between PER_ASSIGNMENTS_F.EFFECTIVE_START_DATE and PER_ASSIGNMENTS_F.EFFECTIVE_END_DATE
    AND HXT_DET_HOURS_WORKED_X.DATE_WORKED between PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE and PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE
    AND HXT_DET_HOURS_WORKED_X.DATE_WORKED between PER_PEOPLE_F.EFFECTIVE_START_DATE and PER_PEOPLE_F.EFFECTIVE_END_DATE
    AND PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID=HXT_DET_HOURS_WORKED_X.ELEMENT_TYPE_ID
    AND (PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID = 			            	
             HXT_PAY_ELEMENT_TYPES_F_DDF_V.ELEMENT_TYPE_ID		    
             AND HXT_DET_HOURS_WORKED_X.DATE_WORKED BETWEEN		    
             HXT_PAY_ELEMENT_TYPES_F_DDF_V.EFFECTIVE_START_DATE 	
           AND HXT_PAY_ELEMENT_TYPES_F_DDF_V.EFFECTIVE_END_DATE)	
GROUP BY PER_PEOPLE_F.EMPLOYEE_NUMBER, 
         PER_PEOPLE_F.FULL_NAME,
         HXT_TIMECARDS_X.FOR_PERSON_ID,
         HXT_SUM_HOURS_WORKED_X.DATE_WORKED, 
         HXT_SUM_HOURS_WORKED_X.TIME_IN, 
         HXT_SUM_HOURS_WORKED_X.TIME_OUT, 
         HXT_SUM_HOURS_WORKED_X.ACTUAL_TIME_IN, 
         HXT_SUM_HOURS_WORKED_X.ACTUAL_TIME_OUT,
         PER_ASSIGNMENTS_F.ASSIGNMENT_ID,
         HR_ORGANIZATION_UNITS.NAME,
         HXT_SUM_HOURS_WORKED_X.id,
        HXT_SUM_HOURS_WORKED_X.HOURS
ORDER BY
        HR_ORGANIZATION_UNITS.NAME ASC,
        PER_PEOPLE_F.EMPLOYEE_NUMBER ASC ,
HXT_SUM_HOURS_WORKED_X.DATE_WORKED

SELECT PER_PEOPLE_F.EMPLOYEE_NUMBER, PER_PEOPLE_F.FULL_NAME, HXT_TIMECARDS_X.FOR_PERSON_ID, HXT_SUM_HOURS_WORKED_X.DATE_WORKED, HXT_SUM_HOURS_WORKED_X.TIME_IN, HXT_SUM_HOURS_WORKED_X.TIME_OUT, HXT_SUM_HOURS_WORKED_X.ACTUAL_TIME_IN, HXT_SUM_HOURS_WORKED_X.ACTUAL_TIME_OUT, HXT_SUM_HOURS_WORKED_X.HOURS, HXT_SUM_HOURS_WORKED_X.ID, SUM(DECODE(HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_EARNING_CATEGORY,'REG',HXT_DET_HOURS_WORKED_X.HOURS,0)) REG, SUM(DECODE(HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_EARNING_CATEGORY,'ABS',HXT_DET_HOURS_WORKED_X.HOURS,0)) ABS, SUM(DECODE(HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_EARNING_CATEGORY,'OVT',HXT_DET_HOURS_WORKED_X.HOURS,0)) OVT, SUM(DECODE(HXT_PAY_ELEMENT_TYPES_F_DDF_V.HXT_EARNING_CATEGORY,'OTH',HXT_DET_HOURS_WORKED_X.HOURS,0)) OTH, PER_ASSIGNMENTS_F.ASSIGNMENT_ID, HR_ORGANIZATION_UNITS.NAME ORG_NAME FROM PER_PEOPLE_F, HXT_TIMECARDS_X, HXT_DET_HOURS_WORKED_X, HXT_SUM_HOURS_WORKED_X, HR_ORGANIZATION_UNITS, PER_ASSIGNMENTS_F, PAY_ELEMENT_TYPES_F, HXT_PAY_ELEMENT_TYPES_F_DDF_V WHERE HXT_DET_HOURS_WORKED_X.parent_id=HXT_SUM_HOURS_WORKED_X.id AND PER_PEOPLE_F.PERSON_ID=HXT_TIMECARDS_X.FOR_PERSON_ID AND HXT_DET_HOURS_WORKED_X.TIM_ID=HXT_TIMECARDS_X.ID AND PER_ASSIGNMENTS_F.ORGANIZATION_ID=HR_ORGANIZATION_UNITS.ORGANIZATION_ID AND HXT_DET_HOURS_WORKED_X.ASSIGNMENT_ID=PER_ASSIGNMENTS_F.ASSIGNMENT_ID AND HXT_DET_HOURS_WORKED_X.DATE_WORKED between :P_START_DATE and :P_END_DATE AND HXT_DET_HOURS_WORKED_X.DATE_WORKED between PER_ASSIGNMENTS_F.EFFECTIVE_START_DATE and PER_ASSIGNMENTS_F.EFFECTIVE_END_DATE AND HXT_DET_HOURS_WORKED_X.DATE_WORKED between PAY_ELEMENT_TYPES_F.EFFECTIVE_START_DATE and PAY_ELEMENT_TYPES_F.EFFECTIVE_END_DATE AND HXT_DET_HOURS_WORKED_X.DATE_WORKED between PER_PEOPLE_F.EFFECTIVE_START_DATE and PER_PEOPLE_F.EFFECTIVE_END_DATE AND PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID=HXT_DET_HOURS_WORKED_X.ELEMENT_TYPE_ID AND (PAY_ELEMENT_TYPES_F.ELEMENT_TYPE_ID = HXT_PAY_ELEMENT_TYPES_F_DDF_V.ELEMENT_TYPE_ID AND HXT_DET_HOURS_WORKED_X.DATE_WORKED BETWEEN HXT_PAY_ELEMENT_TYPES_F_DDF_V.EFFECTIVE_START_DATE AND HXT_PAY_ELEMENT_TYPES_F_DDF_V.EFFECTIVE_END_DATE) GROUP BY PER_PEOPLE_F.EMPLOYEE_NUMBER, PER_PEOPLE_F.FULL_NAME, HXT_TIMECARDS_X.FOR_PERSON_ID, HXT_SUM_HOURS_WORKED_X.DATE_WORKED, HXT_SUM_HOURS_WORKED_X.TIME_IN, HXT_SUM_HOURS_WORKED_X.TIME_OUT, HXT_SUM_HOURS_WORKED_X.ACTUAL_TIME_IN, HXT_SUM_HOURS_WORKED_X.ACTUAL_TIME_OUT, PER_ASSIGNMENTS_F.ASSIGNMENT_ID, HR_ORGANIZATION_UNITS.NAME, HXT_SUM_HOURS_WORKED_X.id, HXT_SUM_HOURS_WORKED_X.HOURS ORDER BY HR_ORGANIZATION_UNITS.NAME ASC, PER_PEOPLE_F.EMPLOYEE_NUMBER ASC , HXT_SUM_HOURS_WORKED_X.DATE_WORKED

Parameter Name

SQL text

Validation

End Date

Date

Start Date

Date

Download

Related Blitz Reports
HXT Organization Variance Calc HXT Location Variance Calculation HXT Earning Variance Calculation HXT Periodic Late Clock-In HXT OTLR Missing Timecard PAY Business Payrolls Survey HXT Person/Assignment Table List HXT Total Hours To BEE PAY Payroll Reconciliation (New Zealand)