HXT Actual and Rounded Time Punch

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

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

Ask a question

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 HXT Person/Assignment Table List HXT Total Hours To BEE PAY Business Payrolls Survey HXT Time Entry Detail