August 2, 2006

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 SQL.

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 a debugger and that work is done for you.

Until next time...