PA Missing Timecards

Description
Categories: BI Publisher, Financials
Application: Projects
Source: AUD: Missing Timecards (XML)
Short Name: PAXAUMTC_XML
DB package: PA_PAXAUMTC_XMLP_PKG
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
Parameter Name SQL text Validation
Employee Name
 
LOV Oracle
Supervisor Name
 
LOV Oracle
Organization Name
 
LOV Oracle
To Week Ending Date
 
Date
From Week Ending Date
 
Date