PA Missing Timecards

select distinct
 '1' dummy_fld,
 :ending_date week_ending_date,
 per.full_name employee_name,
 decode(nvl(per.current_employee_flag,'N'),'Y',per.employee_number,per.npw_number) employee_number, 
 pl.meaning status
from
 per_assignments_f assign,
 per_assignment_status_types af,
 pa_lookups pl,
 per_people_f per
where
      assign.organization_id = :organization_id
  and nvl(assign.supervisor_id,-999) = :supervisor_id
  and assign.person_id = nvl(:employee_id,assign.person_id)
  and assign.person_id = per.person_id
  and assign.ASSIGNMENT_STATUS_TYPE_ID = af.ASSIGNMENT_STATUS_TYPE_ID
  and (af.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN' OR af.PER_SYSTEM_STATUS = 'ACTIVE_CWK')
  and per.effective_start_date <= :ending_date
  and nvl(per.effective_end_date,:ending_date) >= (:ending_date - 6)
  and (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL)
  and (nvl(per.current_employee_flag,'N') = 'Y' OR nvl(per.current_npw_flag,'N') = 'Y')
  and (assign.assignment_type = 'E' OR assign.assignment_type = 'C')
  and assign.primary_flag = 'Y'
  and assign.effective_start_date <=:ending_date
  and nvl(assign.effective_end_date,:ending_date) >= (:ending_date - 6)
  and pl.lookup_type = 'NOT ENTERED'
  and pl.lookup_code = 'NOT ENTERED'
  and
 not exists
  (
  select
   1
  from
   pa_expenditures exp
  where
         exp.expenditure_class_code in ('PT','OT')
  and exp.incurred_by_person_id = assign.person_id
  and exp.expenditure_ending_date = :ending_date)
 and '1'=:dummy_column
union
select distinct
 '1' dummy_fld,
 :ending_date week_ending_date,
 per.full_name employee_name,
 decode( nvl(per.current_employee_flag,'N'),'Y',per.employee_number,per.npw_number) employee_number, 
 lk.meaning status
from
 per_assignments_f assign,
 per_people_f per,
 per_assignment_status_types af,
 pa_lookups lk,
 pa_expenditures exp
where
      assign.organization_id = :organization_id
  and nvl(assign.supervisor_id,-999) = :supervisor_id
  and assign.person_id = nvl(:employee_id,assign.person_id)
  and assign.person_id = per.person_id
  and (nvl(per.current_employee_flag,'N') = 'Y' OR nvl(per.current_npw_flag,'N') = 'Y')
  and per.effective_start_date <= :ending_date
  and nvl(per.effective_end_date,:ending_date) >= (:ending_date - 6)
  and (per.employee_number IS NOT NULL OR per.npw_number IS NOT NULL) 
  and (assign.assignment_type = 'E'  OR assign.assignment_type = 'E')
  and assign.primary_flag = 'Y'
  and assign.effective_start_date <= :ending_date
  and nvl(assign.effective_end_date,:ending_date) >= (:ending_date - 6)
  and assign.ASSIGNMENT_STATUS_TYPE_ID = af.ASSIGNMENT_STATUS_TYPE_ID
  and (af.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN' OR af.PER_SYSTEM_STATUS = 'ACTIVE_CWK')
  and ( (exp.expenditure_status_code in ('SUBMITTED','WORKING','REJECTED') )
      OR (exp.expenditure_status_code = 'APPROVED' 
             and not exists (select 1
		  from pa_expenditure_items ei
		  where ei.expenditure_id = exp.expenditure_id) ) )
  and lk.lookup_type = 'EXPENDITURE STATUS'
  and lk.lookup_code = exp.expenditure_status_code
  and exp.expenditure_class_code in ('PT','OT')
  and exp.incurred_by_person_id = per.person_id
  and exp.expenditure_ending_date = :ending_date 
 and '1'=:dummy_column
order by 4,1,2,3,5