Most of my report and interface programs output snapshots of the database. Reports and interfaces about how the data has changed are more challenging. The effective date model of many Peoplesoft tables suggest that we can easily extract the history of data, but the difference between effective dates and action dates, and the availability of correction mode complicate the issue.
Defining Change
We identify changes with a start date, an end date, and a specification. We have to compare data as it was on the start date with data as it was on the end date. The specification tells us what data to review and what constitutes a reportable change.
A change might be a new value in a field or it could be a sufficiently different value in a field.
- Employee status (PS_JOB.EMPL_STATUS) can be active (A), on leave (L), on paid leave (P), terminated (T), terminated with pay (U), retired (R), retired with pay (Q), or several other values. We could report any change of value or only certain changes. We might wish to report only a transition from being an employee (A, L, P, S, or W) to being an ex-employee (D, Q, R, T, U, V, or X).
- Most organizations have a hierarchy of departments (PS_JOB.DEPTID). We could report any change of value or only certain changes. We might not report a move from “Product Marketing” to “Brand Marketing,” but we might report a move from any “Marketing” department to any “Finance” department.
- We might report any employee name change (PS_NAMES.NAME) or omit changes that consist of adding a middle initial to an employee who didn’t have one.
Effective Action
The PS_JOB table has a field for the effective date of a change (EFFDT) and the date on which the data entry took place (ACTION_DT). A change report has to make four decisions.
- Did this employee have a row in the given date range?
- If there are multiple rows in the given date range, which one should we inspect?
- To which earlier row should we compare the latest row?
- Do the differences between the two rows make this employee eligible for the change report?
Suppose we run a change report on the first business day of each calendar month for the month just ended. There are three cases to consider.
- Changes that were entered in the same month they were effective should be included in the report for that month.
- Changes that were entered in the month before they were effective should be included in the report for the month they were effective.
- Changes that were entered in the month after they were effective should be included in the report for the month they were entered.
We could write the begin-select command with a where clause that includes this comparison.
and greatest(J.EFFDT, J.ACTION_DT) between $start_dt and $end_dt
There might be performance issues because using a function (greatest) and combining two columns forces the database manager to bypass the index. It might be faster to execute these clauses.
and J.EFFDT between $start_dt and $end_dt
and J.ACTION_DT <= $end_dt
If these clauses return multiple rows of PS_JOB, but we want the latest effective date and effective sequence number.
and J.EFFDT = (select max(EFFDT)
from PS_JOB
where EMPLID = J.EMPLID
and EMPL_RCD = J.EMPL_RCD
and EFFDT between $start_dt and $end_dt
and ACTION_DT <= $end_dt)
and J.EFFSEQ = (select max(EFFSEQ)
from PS_JOB
where EMPLID = J.EMPLID
and EMPL_RCD = J.EMPL_RCD
and EFFDT = J.EFFDT)
We need to compare this row with the PS_JOB row that has the maximum EFFDT that is less than $start_dt. The comparison operator is not the typical “less than or equal to” because if the EFFDT were equal to the $start_dt, the row would be within the change date range. Assuming we call another procedure to perform another begin-select, the where clause would be like this.
where J1.EMPLID = &J.EMPLID
and J1.EMPL_RCD = &J.EMPL_RCD
and J1.EFFDT = (select max(EFFDT)
from PS_JOB
where EMPLID = J1.EMPLID
and EMPL_RCD = J1.EMPL_RCD
and EFFDT < $start_dt)
and J1.EFFSEQ = (select max(EFFSEQ)
from PS_JOB
where EMPLID = J1.EMPLID
and EMPL_RCD = J1.EMPL_RCD
and EFFDT = J1.EFFDT)
Lookup Tables
The PS_JOB table may identify the employees who changed and the nature of their changes, but the report or interface may call for data from additional tables; the employees’ names, their departments’ names, their job titles, and more. The tables with those data are effective dated themselves.
Consider this history of changes.
- January 1, 2000: The organization creates department 1234, called “Product Marketing.”
- December 1, 2008: Employee joins department 1234.
- January 1, 2009: The organization renames department 1234 to be “Brand Marketing.”
- August 15, 2009: Employee transfers to department 4321, called “Product Planning.”
- August 20, 2009: The organization renames department 1234 to be “Brand Promotions.”
Assume the only PS_JOB rows for this employee are on December 1, 2008 and August 15, 2009. If we run the report on September 1, 2009, how should we describe this employee’s former department? Should we use the name that was effective when the employee joined the department, or the name that was effective when the employee left the department, or the name that was effective at the end of the change date range, or the name that is effective when we run the report?
Since the report is about employee reassignments, not organizational changes, we should use the latest name for the department, saying “on August 15, 2009, Jon Smith transferred from Brand Promotions to Product Planning.” A report about organizational changes would say “on August 20, 2009, Brand Marketing became Brand Promotions.”
Looking Ahead
Next week we’ll consider tables that don’t have action dates – that is, every table in Peoplesoft HCM except PS_JOB.
What was I thinking?
I advised a colleague to use the code on this page for a program he was writing. We reviewed his work and it just didn’t seem right to me. I thought I had worked this out very carefully, but I have second thoughts now.
The first selection should take all J.EFFDT between $start_dt and $end_dt but it should also take all J.EFFDT in the past if the J.ACTION_DT is between $start_dt and $end_dt; e.g. a report of changes for August should include a change effective in July if it was entered in August:
and (J.EFFDT between $start_dt and $end_dt or J.ACTION_DT between $start_dt and $end_dt)
The second selection, of the previous row to compare with the selected row should be before $start_dt, but it also should be before the selected row. Otherwise we might compare a retroactive change to itself.
and J1.EFFDT = (select max(EFFDT) from PS_JOB where … and EFFDT < J.EFFDT and EFFDT < $start_dt)
I apologize for leaving a bad algorithm in place for the past year.
Can you explain the cases when transfers happened such as:
1. if the employee is at home country and transferred to another department.
2. If the employee is having home and host (both are active) and transferred to another department at host
3. If the employee moved from Home to Host with a change of department id
4. if the employee moved from Host to Home with a change of department id due to assignment completion.
Nick, I don’t understand what you’re asking. These are scenarios that involve changes to DEPTID. My post discusses how to detect changes to DEPTID. Are you asking how to differentiate among these scenarios? That’s a legitimate question, but not the topic I was discussing. You need to look at before and after data, find the pattern that each scenario produces, and test for that pattern.