Monday, August 20, 2007

Effective Dated Records in PeopleSoft

PeopleSoft application supports a the effective dated records throughout the system. PeopleTool provides the built-in functions to enable this feature for the selected records. They make the effective dated records very easy for developers to implement.

Effective dated records have the EFFDT field. The EFFDT field allows the user to enter a data ahead of time. For example, the GL accounts in PeopleSoft is effective dated. The user can design the GL accounts and create them in the system and make them not effective until next year. This is very useful since you do not want the users to rush to get the data into the system right before the next year.

The EFFDT column is part of the composite key for those tables.
There are multiple alternate ways to address the requirements. PeopleSoft chose to use a single column for this purpose. The EFFDT column is actually the effective start date. The downside of the approach I can think of are the following:

1. The design does not allow the user to deactivate a record. You have to create a new row in the database to terminate an old record. The database need to use another column to allow the user to deactivate an object.

2. To write a SQL to retrieve the effective record for a given point in time, a sub-query is needed:

SELECT T.DESCR
FROM PS_XX_TAB T
WHERE T.XX = '123'
AND T.SETID = 'US001'
AND T.EFFDT =
( SELECT MAX(EFFDT)
FROM PS_XX_TAB TM
WHERE TM.SETID = T.SETID
AND TM.XX = T.XX
AND EFFDT <= sysdate )
The sub-query tries to get the highest value of the effective date where that date is equal to or less then than the current date.

PeopleSoft development solves the 1st issue by adding the EFFSTATUS column to the tables.

PeopleTool solves the 2nd problem bu hiding the details from the developers. For example, in PeopleCode, you can use the %EffDtCheck function. The above SQL will be like this:

SELECT T.DESCR
FROM
PS_XX_TAB T
WHERE T.XX = '123'
AND T.SETID = 'US001'
AND %EffDtCheck(:1, T, :2)

It is quite interesting to see how different OLTP applications handles the same design problem. We can build a collection of design patterns for these common requirements.

PeopleSoft development actually puts the effective date column on the pages and ask the users to specify a date for data entry. The page design can also be considered a design pattern.

No comments: