Enhanced Access for Managers - Reports To Updates

Develop a AE process to:

1. Review the current max job rows for specific job actions (HIR, POS, PRO, DEM, XFR) for Employees not in Terminated or Retired status and determine if the position number has changes on this row compared to the previous job row. Effective date of max current job row should equal the processing date with the assumption that the process will run daily.
2. If the Action creates a vacant position (position exists in PS_POSITION_DATA but does not exist in PS_JOB), generate an email to the Reports To of the position being vacated. Email is to instruct the Reports To to move the Employees that Reports to the vacating position to another position if the Position is not going to be immediately filled. If the Reports To of the vacating position is vacant (error), send to error table.
3. Determine if the position number on the current max job row has employees (in Job.empl_status A,L,P,S,W) reporting to it. Reports To on Job is equal to the position number on the job row examined.
4. Insert a job rows on all the employees. Copy the previous job row for all values. The effective date of the job row should be equal to the effective date of the position change. Sequence row as necessary. Action Position Change (POS) and Reason Reports to Change (RSC).
5. Do not update if there are future dated job rows (error).
6. Create a table to store exceptions that includes Emplid, empl_rcd, effdt, position_nbr, reports_to, reason not updated