Pre AME rollout - HR sanity check scripts

Prior to rolling out AME for iProcurement and/or Internet Expenses, it is important to ensure the HR hierarchy is clean with no breaks or holes in it.

Here's an example of a script to help identify corrupt data in the HR hierarchy (e.g. direct reports with no supervisors, missing default expense accounts, jobs (positions), office location, email address)


select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.supervisor_id is null --with missing supervisor record
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.default_code_comb_id is null --with missing default expense account
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.job_id is null  --with missing job (or position, for fetching the DOA)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and paa.location_id is null  --with missing location (used to create supplier records of Employees with OIE)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
union
select distinct ppf.full_name empl_name
from per_all_assignments_f paa,
     per_people_f ppf,
     fnd_user f
where paa.person_id = ppf.person_id
  and f.end_date is null
  and ppf.person_id = f.employee_id
and ppf.email_address is null  --with missing email address (to send email notifications)
and sysdate BETWEEN paa.effective_start_date AND paa.effective_end_date
and paa.primary_flag = 'Y'
order by 1;