Finding Changes in PeopleSoft
Sunday, August 23, 2009 at 12:15PM Over my career I have been working a variety of different technologies for different reasons. One of them being the PeopleSoft products.
One of the most interesting aspects of the toolset happens to be how much it relies on the database. Pretty much everything important is stored inside it usually in strange tables with obscure meanings. However the database can also provide quite a treasure trove of insight if you need it. A common example happens to involve code. Most source code control will work pretty well in terms of managing what gets put into a PeopleSoft platform. However I have found that if I wanted to find out what has definitively happened that can be very long and labor intensive.
Thankfully since PeopleSoft stores so much into their tables there are a few tables that can help. The first table happens to be the Project Item List table named PSPROJECTITEM. It contains all the items for a particular project, including actions to be taken, etc. The second table happens to be the Change Control History table named PSCHGCTLHIST. This particular table happens to have all the change control that PeopleSoft maintains for certain projects. These two tables can provide a complete picture of what has taken place within your PeopleSoft platform in terms of projects. These tables do not track any DBA actions that occur outside of PeopleSoft.
Common queries that I have used involve looking up all changes for the instance, restricting that look-up based on certain dates such as releases, looking for particular objects or particular projects, or for all projects for certain users, etc. This can be useful to determine how often things change in the environment via approved PeopleSoft changes, can help focus customizations for particular objects, determine if a particular project went through and if so did it change what it intended to, and helps insure that changes are proceeding according to existing standards.
For example here is a sample query I have used to get changes made to an instance within a particular date range:
select distinct a.oprid User_ID, decode (a.chgctrl_action, 'A', 'Added', 'C', 'Upgrade Copy', 'D', 'Deleted', 'I', 'Informational', 'R', 'Rename', 'U', 'Unlock', 'L', 'Lock', 'NA') Action_Taken, a.projectname Project_Name, a.dttm_stamp Action_Date, decode(a.objectid1, 0, b.objectvalue1, a.objectvalue1) Main_Object from sysadm.PSCHGCTLHIST A, sysadm.psprojectitem B where a.dttm_stamp >= to_date('01/01/2009', 'MM/DD/YYYY') and a.dttm_stamp <= to_date('08/15/2009', 'MM/DD/YYYY') AND a.projectname = b.projectname and a.objecttype = b.objectid1 order by a.dttm_stamp desc
Overall I find having good access to the PeopleSoft database to run queries is necessary to see if things are working properly. But be mindful, in that you have to be careful to craft the queries in accordance to their structures, know the data that you are looking for, and to insure that any query you are running does not impact the database adversely.
Reader Comments (3)
I am wondering how is this possible? Can you explain more details in your blog post. It will be more useful. Thanks
htc unlock
I can provide more explanation but is there a particular item in particular you are looking for? Thanks!
My name is unlockserver from USA. I am interested in your writing. Some of your posting are good, I can say, best. Can you please tell me how to subscribe to your blog post online?