HXT OTLR Missing Timecard

Description
Categories: BI Publisher, Human Resources
Application: Time and Labor
Source: OTLR Missing Timecard Report (XML)
Short Name: HXT964A_XML
DB package: HXT_HXT964A_XMLP_PKG
SELECT paf.person_id
     , emp.full_name
     , emp.employee_number
FROM per_time_periods ptp
     , pay_payrolls_f ppf
     , per_people_f emp
     , per_assignments_f paf
     , hxt_per_aei_ddf_v pafv
  WHERE ptp.payroll_id = ppf.payroll_id
  AND ppf.payroll_id = paf.payroll_id
  AND emp.person_id = paf.person_id
  AND paf.assignment_id = pafv.assignment_id
  AND (ptp.start_date between pafv.effective_start_date          
                                      and pafv.effective_end_date           
          OR pafv.effective_start_date between ptp.start_date     
                                                           and ptp.end_date)     
  AND (ptp.start_date between paf.effective_start_date
         AND paf.effective_end_date
       OR paf.effective_start_date between ptp.start_date
         AND ptp.end_date)
  AND paf.assignment_status_type_id = 1
  AND (ptp.start_date between ppf.effective_start_date
         AND ppf.effective_end_date
       OR ppf.effective_start_date between ptp.start_date
         AND ptp.end_date)
  AND (ptp.start_date between emp.effective_start_date
         AND emp.effective_end_date
       OR emp.effective_start_date between ptp.start_date
         AND ptp.end_date)
  AND ptp.time_period_id = :P_TIME_PERIOD_ID
AND ptp.time_period_id NOT IN
(SELECT time_period_id 
 from HXT_DET_HOURS_WORKED_X THW,
       HXT_TIMECARDS_X  TTC
  where
     TTC.FOR_PERSON_ID = emp.PERSON_ID and
     THW.ASSIGNMENT_ID in
     (
      SELECT  paf.ASSIGNMENT_ID 
         from  per_assignments_f paf
         where ppf.payroll_id = paf.payroll_id
         AND emp.person_id = paf.person_id
         AND (ptp.start_date between paf.effective_start_date
          AND paf.effective_end_date
          OR paf.effective_start_date between ptp.start_date
          AND ptp.end_date)
         AND paf.assignment_status_type_id = 1
     )
     AND
     TTC.TIME_PERIOD_ID = :P_TIME_PERIOD_ID and
     TTC.ID = THW.TIM_ID and
     THW.PARENT_ID > 0)
Parameter Name SQL text Validation
Time Period
 
LOV Oracle
Payroll Name
 
LOV Oracle