HXT Periodic Late Clock-In
Description
Categories: BI Publisher
Application: Time and Labor
Source: Periodic Late Clock-In Report (XML)
Short Name: HXT959A_XML
DB package: HXT_HXT959A_XMLP_PKG
Source: Periodic Late Clock-In Report (XML)
Short Name: HXT959A_XML
DB package: HXT_HXT959A_XMLP_PKG
Run
HXT Periodic Late Clock-In and other Oracle EBS reports with Blitz Report™ on our demo environment
SELECT ALL ASG.ORGANIZATION_ID, ORG.NAME ORG_NAME, PER.EMPLOYEE_NUMBER, SHT.STANDARD_START, PER.FULL_NAME, DET.DATE_WORKED, DET.TIME_IN, TIM.PAYROLL_ID, AEIV.HXT_ROTATION_PLAN, SHT.NAME SHIFT_NAME, HXT_HXT959A_XMLP_PKG.cf_tot_varformula(SHT.STANDARD_START, DET.TIME_IN) CF_TOT_VAR, HXT_HXT959A_XMLP_PKG.cf_l_vlformula(SHT.STANDARD_START, DET.TIME_IN) CF_L_VL FROM PER_ASSIGNMENTS_F ASG, PER_PEOPLE_F PER, HXT_TIMECARDS_X TIM, HXT_DET_HOURS_WORKED_X DET, HR_ORGANIZATION_UNITS ORG, HXT_PER_AEI_DDF_V AEIV, HXT_ROTATION_PLANS ROT, HXT_ROTATION_SCHEDULES RSC, HXT_WEEKLY_WORK_SCHEDULES WWS, HXT_WORK_SHIFTS WSH, HXT_SHIFTS SHT WHERE SHT.NAME <> 'Off' and DET.TIME_IN is NOT NULL and TIM.PAYROLL_ID = :P_PAYROLL_ID and DET.DATE_WORKED >=:START_DATE and DET.DATE_WORKED <= :END_DATE and (ROT.ID=AEIV.HXT_ROTATION_PLAN) AND (RSC.RTP_ID=ROT.ID) AND RSC.START_DATE= (SELECT MAX(RSC.START_DATE) FROM HXT_ROTATION_SCHEDULES WHERE ((RSC.RTP_ID=AEIV.HXT_ROTATION_PLAN) AND RSC.START_DATE<=DET.DATE_WORKED)) AND (RSC.TWS_ID=WWS.ID) and WWS.DATE_FROM<=DET.DATE_WORKED AND ((WWS.DATE_TO IS NULL) OR (WWS.DATE_TO>DET.DATE_WORKED)) AND WSH.WEEK_DAY=to_char(DET.DATE_WORKED,'DY') AND (WSH.TWS_ID=WWS.ID) AND ((SHT.ID=WSH.SHT_ID)) AND (DET.DATE_WORKED >= ORG.DATE_FROM) AND ((ORG.DATE_TO is NULL) or (ORG.DATE_TO >=DET.DATE_WORKED)) and (DET.DATE_WORKED >= ASG.EFFECTIVE_START_DATE) and ((ASG.EFFECTIVE_END_DATE is NULL) or (ASG.EFFECTIVE_END_DATE >=DET.DATE_WORKED)) and (DET.DATE_WORKED >= PER.EFFECTIVE_START_DATE) and ((PER.EFFECTIVE_END_DATE is NULL) or (PER.EFFECTIVE_END_DATE >=DET.DATE_WORKED)) AND ((DET.TIM_ID=TIM.ID) AND (TIM.FOR_PERSON_ID=PER.PERSON_ID) AND (ASG.ASSIGNMENT_ID=AEIV.ASSIGNMENT_ID) AND DET.DATE_WORKED BETWEEN AEIV.EFFECTIVE_START_DATE AND AEIV.EFFECTIVE_END_DATE AND (DET.ASSIGNMENT_ID=ASG.ASSIGNMENT_ID) AND (ORG.ORGANIZATION_ID=ASG.ORGANIZATION_ID)) order by SHIFT_NAME |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Payroll Name |
|
LOV Oracle | |
Start Date |
|
Date | |
End Date |
|
Date |