The views expressed on this blog are my own and do not necessarily reflect the views of Oracle.

Saturday 17 August 2013

Special fields in PeopleSoft

PeopleSoft reserves some of the fields with some special characteristics and those fields behave differently from other fields. These fields are meant to be used only for some specific purposes. I will be discussing about two such fields, OPRID and EFFDT, in this post.

OPRID is the field which stores the operator id of a user in the PeopleSoft system. The users use operator id to log in to the PeopleSoft system. PeopleSoft, hence, has a special processing routine for this field. One of the interesting things you can note is by trying to make the field OPRID as a KEY field to any of the table and try to load the data using component processor. Even though you have more than one row of data for that specific table, PeopleSoft always loads only one row of data for the table. And if you observe, you will note that the one row is the row specific to the logged in user. What it means is, PeopleSoft will automatically append a condition OPRID = %OperatorId in the where clause when the data is loaded from the database. So the big question is, how can I overcome this situation? There is one workaround to overcome this situation. The workaround is to check the List Box Item property of the field in the record field definition. Now if you load the page, you will be able to see the full data list. I am still not sure whether this is a bug or it is designed so.

Another interesting and most widely used field is EFFDT. EFFDT is not a normal date field. PeopleSoft has many in-built logic wrapping this field. So if your purpose is to store a date, it is better you use a different date field. The main specialty of the field is, if you use this field then whenever you load the data on the page it will always display the rows of data which is effective as of today and future. To view the past data, you need to log in with Include History mode. But still you won’t be able to edit the data. To edit the data you need to be visiting the component with Correct History mode. All these are captured automatically by PeopleSoft. Also this field has special treatment in some queries, where the effective dated row is automatically returned by the query. Another advantage of the field is, if you have this field on any of your table, then whenever you add a second row, all the details from the previous row will be automatically populated in the new row as well. If you have noticed, you can see another specialty like, if you have this field on a table, then the same table can be used as level 0 and level 1 record. You can include all the keys upto EFFDT into level 0 and the rest of the fields in level 1. And the search and component behaves like a normal parent child relation.

No comments:

Post a Comment

Note: only a member of this blog may post a comment.