Reports2017-11-18T12:27:27+00:00

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
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
By continuing to use the site, you agree to the use of cookies. Accept