| Effective Dates and Component Processing | KEVIN RESCHENBERG
12-28-2005 |
A user reported a problem recently. She was trying to
delete a future-dated row on the Additional Pay page. This page is
basically a large scroll area, because additional pay
rows are effective-dated. She deleted the entry and
tried to save, but the system responded with an error
message saying that a row dated way back in 2002 was invalid.
"But I was just trying to delete the last record." This
can be a source of confusion. Users may think that
they are "saving a page," when they are actually triggering
all of the processing related to the entire component.
A component (sometimes referred to as a "transaction")
is probably the fundamental object in a PeopleSoft
system. Even though only one row might be changed,
the system will (usually) scan through all of the rows
in the buffer and validate them.
The old row really was invalid. It was an earnings type
attached to an earnings program that was attached to
the paygroup that was indicated on the current JOB
row for the employee. What this means is that there was
a stack of three or four different effective-dated rows
that all needed to make sense relative to each other.
I've previously written about some of the considerations
involved in choosing how to relate effective dates
(here
and here,
for example). After tracing through the different tables,
I could see that the row was invalid. It was an earnings
type being paid before it was valid for the earnings
program.
How could this happen? There are two major causes of this:
direct SQL updates to the database and Correction-mode
changes. As I wrote in last
week's post, the database itself does not enforce
referential integrity (and this particular example is
even more complex than the situations covered by the
referential integrity rules). It is normally the responsibility
of the system to validate the data. When we make direct
updates to the database, that responsibility falls on
us. Even for customers who prohibit nearly all direct
updates, though, there is still the loophole provided
by Correction mode. Be very careful about how Correction
mode access is assigned through security.
But there is another quirk. The system does not always
run all of its edits. An edit is normally done only when
it appears that the related data has changed. If there's
no change, there should be no need to run the edit, right?
Well, that's probably a good policy (to reduce run times),
but it's not necessarily true. Related tables could have
changed. Even when there are changes, the system does not
always run all of the edits. For example, it appears that
inserting a new row does not qualify as a "change" to the
effective date field, and some associated edits might not
be run. This means that in some very unusual situations, an
error might not be detected until much later.
These are not easy things to explain (or diagnose), and
I can understand how users might be confused at times.
But if we are careful about SQL updates and Correction
mode, these situations should be rare or nonexistent.
|
