|
August 16, 2006
Effective Status
I'm currently involved in a conversion project.
We are reconciling some existing data against
the various setup tables. In some cases, setup table
rows have been marked "inactive" but there is employee
data that uses these codes. This can happen if users
modify the setup data (in correction mode) to inactivate
rows that were already in use. Mismatches can also
occur if employee effective dates are changed, or
there is a SETID mixup, or if data is loaded outside
of the application. And then there is the common
situation in which setup rows are inactivated while
the associated data (employee JOB rows, for example)
are still referring to them.
If you go to the employee data page when this happens,
you will see the code—department ID, for example—but
no description will be displayed. As long as you don't
try to change the department ID, you can modify other
fields and save the row without seeing any error messages.
The reason is that the component processor does not do
another lookup on every code to validate it; for
performance reasons, this is done
only if the field is changed.
(This assumes, of course, that no PeopleCode is relying
on data contained within the setup table.)
You can even insert a new row and the code won't be
validated unless it's changed.
As an aside, I think
it's a little odd that the edit is not done during RowInit,
since the component processor presumably already knows
the code is inactive when it tries to retrieve the
description to be displayed. Maybe this would lead
to too many annoying data entry errors.
So why do we even have the EFF_STATUS field? This
value restricts data entry when the field is being
changed, so that the user can select only "active"
values. But it does very little to restrict existing
data, as discussed above.
I've previously written about the fact that referential
integrity is not enforced at the database level (and
the reasons why this is true). There is a more "logical"
type of referential integrity involved here, and as
we can see, that isn't strictly enforced by the
application either.
For the reasons described above, it is important to remember
that we should almost never include EFF_STATUS in
SQL joins. An inactive setup table row still exists
and it still contains useful information, such as the
description field. Use it. If we include "AND EFF_STATUS = 'A'" in our
joins, the joins will drop rows that should not be
dropped. I've seen a large
number of bugs that ended up being caused by SQL that
referred inappropriately to EFF_STATUS.
EFF_STATUS is for data entry purposes and for
the component processor's use, not
for batch processing or reporting purposes.
Until next time...
|
