|A Couple of HRMS/HCM Utilities
I'm currently working on an HRMS/HCM 8.9 upgrade
that is more like a combination of an upgrade and a reimplementation.
A lot of data is being moved around, and some of it
is, well, old and a little sketchy. That's why I was
glad to see a utility called the "HR Data Integrity
Audits" report (Set Up HRMS > System Administration
> Database Processes > Core HR Data Integrity Audit).
It checks relationships between PERSON, JOB, JOB_JR, PERS_DATA_EFFDT,
and a few other tables. Unfortunately, it seems to
limit these checks to finding orphans (a row exists
in a child table but there is no corresponding row
in the parent table) and a couple of other broken
relationships (JOB vs. JOB_JR, and missing primary
NAMES rows). It does not check, for example, that
a DEPTID exists on DEPT_TBL, considering the SETID
and EFFDT. As I've discussed in previous posts, it is
entirely possible for these types of mismatches to
occur, even if you are not moving data around
behind the scenes. I've written a number of quick
SQL SELECTs to audit these fields, but there is
probably a better solution using the Tools tables
to check the "prompt" tables and generate dynamic
There are other more specialized audit utilities scattered
around. For example, there is a primary job audit
report, a PER_ORG audit (although that one seemed to
be missing), and others. Of course, we still have
SYSAUDIT and DDDAUDIT, two very good utilities—but
they are more database-related than application-related.
I found another utility that seems a little odd. This
one is at Set Up HRMS > System Administration > Utilities > Tables Accessed and Updated.
It will analyze App Engine, COBOL or SQR programs or queries
to create a list of the tables that are used and the
access modes (select, insert, update, delete). It
expands the programs to find these—for example,
it will list the names of the SQCs contained within a program
that access a particular table. But it seems a little
backwards to me. Is it for documentation purposes?
We usually want to find the programs that are using
a table, not the tables that a program uses. Find that
by looking at the code. Yes, you will need to open
each SQC, for example—but just use
and that work is done for you.