|Effective and Data Entry Dates||KEVIN RESCHENBERG|
I did some work on an HR headcount report recently. Listing the number
of active and terminated employees—"counting heads"—seems as
if it would be just about the easiest thing to do. If only.
The HRMS/HCM system is centered around the effective-dated JOB table.
In general, an employee's status as of a particular date is determined by
the latest JOB row as of that date (the row dated on that date or most
recently), and in particular, the row with the highest sequence number on
So suppose that you need to know the number of active employees as of today,
April 11. That's easy. You could also answer the same question next week by
looking back in the history to April 11. But what if a new hire's information
had been entered late? Suppose that the hire was effective on Monday, April 9
but it was not entered until Thursday, April 12. You might have counted 100
actives today, but when you run the report next week (again, for April 11) you will
find 101 actives. That's understandable, but you may find that you are answering
frequent questions from managers who are looking at the headcount reports.
Now the requirement changes and the report must show not only the number of
actives as of a specified date, but also the number of changes (hires/rehires and
terminations) since the previous report. The people reading the report would
expect that the number of actives at the end of the period would be equal
to the number from the previous report plus new actives minus new terminations.
Here is where the flexibility provided by PeopleSoft can turn this little
project into a minor nightmare if we don't think through the situation fully.
As I mentioned earlier, there can be "retro"-type changes and late entries.
Users with correction-mode access can cause havoc, even while entering the
data correctly. There can be future-dated entries (a new hire starting next
Monday, or a termination for someone who has just submitted two weeks' notice).
If we simply look at the EFFDT field, we will miss many changes. If the report
requires detail on the types of changes, we will need to consider the ACTION
and/or ACTION_REASON fields. But those might not be found on the highest
effective-sequence row. For example, sequence 0 on a particular date could
contain the HIR (hire), and sequence 1 could contain a DTA (data change) or
other type of activity. There could even be two HIR rows, or a HIR corrected
later by a REH, or...any combination is possible. Blindly counting action codes
will lead to trouble.
And the more detail you need to report, the more trouble
it becomes. For example, suppose that the users require a detailed breakdown
of termination reasons, and a retroactive change is made that looks back several
time periods. How will you account for that? The employee had already been
counted, but now the reason has changed.
In practice, many organizations would prefer that their headcount numbers remain
stable after they are produced. In other words, if you were to rerun a report
later for a date range, the numbers would stay the same as they originally were.
This means that a combination of EFFDT and ACTION_DT must be considered. The team
must consider how to use rows entered early or late to modify the effective
status counts while avoiding the double-counting problem or the problem of
missing changes. How will you be handling odd situations such as no-shows
(those who appear to be hired and terminated on the same day)?
Whether one report must "tie" to the next must be decided.
In addition to handling both EFFDT and ACTION_DT situations, you might consider
storing headcount results in a custom history table. This "freezes" them.
When beginning a headcount report project, be sure to discuss all of the possible
situations with the users who are requesting the report. Look at the data
for examples of what actually happens. Ask
about processes for entering retroactive changes. Determine what's important to
the users—stability of the numbers, "accuracy" (however that may be
defined!), simplicity/explainability, or something else?
Many organizations seem to have completely solved the problem for their own
needs. Others have crude, naive implementations that constantly raise questions
or require manual adjustments. What works for one company won't necessarily work
for another, because the requirements vary so widely. Sometimes it seems as if
payroll processing is simpler than just counting employees.