PS_EMPLOYMENT used to be a SQL table. Now it is a SQL view that joins PS_PER_ORG_ASGN, PS_PER_ORG_INST, and PS_JOB. Of course, PS_JOB has two subselects to pick a single value of EFFDT and EFFSEQ for each value of EMPL_RCD. The subselect for EFFDT is unusual and it may not behave the way the designers intended.
The Intention
The record properties for EMPLOYMENT contains a description.
EMPLOYMENT is a view of the main Job/assignment information. It joins PER_ORG_ASGN, PER_ORG_INST, current JOB, and JOB_DATES. All persons with an EMPL_RCD are returned regardless of their HR_STATUS or EMPL_STATUS. If an EMPLID/EMPL_RCD only has a future dated JOB row, then that row is used instead of the current row.
I think they mean that employees that were hired before we perform “select * from PS_EMPLOYMENT” should be represented by their current rows in PS_JOB. Employees who have been hired and a scheduled to start work in the future should be represented by their earliest rows in PS_JOB.
The View SQL
Oracle delivered SQL that includes PS_JOB with an alias of “D.” PS_JOB contributes fields like HIRE_DT, LAST_HIRE_DT, LAST_DATE_WORKED, TERMINATION_DT, REPORTS_TO, and SUPERVISOR_ID. Its effective date is determined by this portion of the where clause.
and D.EFFDT = (select max(JOB2.EFFDT)
from PS_JOB JOB2
where D.EMPLID = JOB2.EMPLID
and D.EMPL_RCD = JOB2.EMPL_RCD
and ((JOB2.EFFDT <= %CurrentDateIn)
or
( D.EFFDT > %CurrentDateIn
and %CurrentDateIn < (select min(J2.EFFDT)
from PS_JOB J2
where J2.EMPLID = D.EMPLID
and J2.EMPL_RCD=D.EMPL_RCD)
)
)
)
The first issue with this SQL is that we are trying to determine the value of D.EFFDT in a subselect that relies on the value of D.EFFDT. That’s not a bug; the database handles it without complaint, but it is circular logic. The Oracle programmers may have meant to write “JOB2.EFFDT > %CurrentDateIn.”
Suppose we select PS_EMPLOYEES on March 15, 2010. Which PS_JOB row will it use in each case?
Case 1. The employee was hired on January 1, 2009. She has PS_JOB rows on 01/01/09, 06/30/09, and 01/01/10. The SQL will find a maximum effective date less than 03/15/10 and will not find a minimum effective date greater than 03/15/10, so it will return 01/01/10.
Case 2. The employee was hired and will start on April 1, 2010. He has a PS_JOB row on 04/01/10. The SQL will not find a maximum effective date less than 03/15/10. It will find a minimum effective date greater than 03/15/10, April 1st. I’m not sure how this works, but perhaps it will assign it to D.EFFDT tentatively and then confirm that it is greater than 03/15/10.
The Problems
Problem 1. The employee was hired on January 1, 2000 and quit on December 31, 2005. The employee was rehired for a different department and scheduled to start on April 1, 2010. The SQL statement will prefer the five year old PS_JOB row which implies the employee is terminated and identifies his previous supervisor.
Problem 2. Human Resources is far ahead of schedule and has entered a row effective May 1, 2010. The main subselect looks for “max(JOB2.EFFDT)” which will return 05/01/10, not 04/01/10.
These may not be bugs. They may be features. If every understands and accepts what PS_EMPLOYMENT delivers, which usually matches its description, then it’s OK. If it’s not OK, then we customers have to decide what we want, and what we want isn’t obvious. If an employee is terminated and rehired, which status is more relevant in the time between data entry and the rehire date? If there is more than one future dated row for a never before employed employee, which one should we see?
A Partial Improvement
The first step is to create a view of PS_JOB which contains the most recent and/or the most imminent EFFDT for each employee. PS_CURR_NEXT1_VW would have field EMPLID and EFFDT, populated with this SQL.
select EMPLID, max(EFFDT) from PS_JOB where EFFDT <= sysdate group by EMPLID
union all
select EMPLID, min(EFFDT) from PS_JOB where EFFDT >= sysdate group by EMPLID
The second step is to create a view of PS_CURR_NEXT1_VW which prefers the current EFFDT, if it exists. PS_CURR_NEXT2_VW would have field EMPLID and EFFDT, populated with this SQL.
select EMPID, min(EFFDT) from PS_CURR_NEXT1_VW group by EMPLID
The third step is to use PS_CURR_NEXT2_VW to set the value of D.EFFDT in the SQL for PS_EMPLOYMENT.
and D.EFFDT = (select EFFDT from PS_CURR_NEXT2_VW where EMPLID = D.EMPLID)
Reviewing the post, I perhaps could have explained what I was trying to do a little better. In 99% of cases if you have an Effdt clause and the data only has a future dated row then you would want the data to be excluded. This situation was a little different however.
Hi Tara,
Thank you for your comment, but I’m a little puzzled. Were you involved with this code?