November 8, 2006

Expanding Subrecords

I've written previously about using the PeopleTools tables for various searches or documentation purposes. One of the most useful is the PSRECDEFN/PSRECFIELD combination. These tables contain a wealth of information about records (tables and views) and their fields (columns). But it seems that subrecords always get in the way.

We can also look into the tables and views provided directly by the database. There are a number of these and they vary depending on the RDBMS you might be using. One useful view in Oracle is DBA_TAB_COLUMNS. This lists all of the fields without the subrecord complication (since the database doesn't even know about subrecords), but it doesn't contain the PeopleSoft-specific attributes such as whether a field is required.

Here's a little trick to get around these difficulties. (This code is specific to Oracle but there should be similar solutions in other databases.) We use DBA_TAB_COLUMNS to drive the process. With the list of table and field names it provides, we can then dig into the hierarchy of records and subrecords to retrieve specific attributes. This example lists the "required" or "optional" attribute for each field.

SELECT R.RECNAME  "Record"
     , D.COLUMN_NAME "Field"
     , D.COLUMN_ID  "Fld Seq"
     , COALESCE((SELECT DECODE(BITAND(USEEDIT,256), 
                               256, 'Required', 
                               'Optional') 
                 FROM   PSRECFIELD
                 WHERE  FIELDNAME = D.COLUMN_NAME
                 AND    RECNAME = R.RECNAME
                 AND    SUBRECORD = 'N')
              , (SELECT DECODE(BITAND(USEEDIT,256), 
                               256, 'Required', 
                               'Optional')
                 FROM   PSRECFIELD
                 WHERE  FIELDNAME = D.COLUMN_NAME
                 AND    RECNAME IN 
                        (SELECT FIELDNAME 
                         FROM   PSRECFIELD 
                         WHERE  RECNAME = R.RECNAME 
                         AND    SUBRECORD = 'Y')
                        )
              , (SELECT DECODE(BITAND(USEEDIT,256), 
                               256, 'Required', 
                               'Optional')
                 FROM   PSRECFIELD
                 WHERE  FIELDNAME = D.COLUMN_NAME
                 AND    RECNAME IN 
                        (SELECT FIELDNAME 
                         FROM   PSRECFIELD 
                         WHERE  RECNAME IN 
                                (SELECT FIELDNAME 
                                 FROM   PSRECFIELD 
                                 WHERE  RECNAME = R.RECNAME 
                                 AND    SUBRECORD = 'Y') 
                         AND SUBRECORD = 'Y')
                        )   
               )  "Req/Opt" 
FROM   DBA_TAB_COLUMNS  D
     , PSRECDEFN  R
WHERE  D.TABLE_NAME = DECODE(R.SQLTABLENAME, 
                             ' ', 'PS_' || R.RECNAME, 
                             R.SQLTABLENAME)
AND    R.RECTYPE = 0
ORDER  BY R.RECNAME, D.COLUMN_ID

This SQL expands subrecords to two levels of nesting. I think that this should be sufficient for HRMS/HCM, but maybe not for Financials. The pattern you see here could be extended if needed to go another level or two.

Until next time...