| Expanding Subrecords | KEVIN RESCHENBERG
11-08-2006 |
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.
|
