HXT Periodic Late Clock-In

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