PeopleSoft System Restore

There is one important aspect of Microsoft XP which helped me get out of the worst situations with ease – System Restore (I buy all the latest games and install it, download all internet games and without System Restore I would have been doomed). I’ve wondered many a times how will it be if we could perform System Restore in an ERP. This makes me feel good about choosing the appropriate blog title. System Restore is not impossible to achieve with an ERP system, but the complexity lies with the Table structures being modified result in loss of data in certain cases and this cannot be effectively handled with a System Restore that we try and build. Let me provide a scenario, a patch delivered by PeopleSoft changes the Field type from Number to Char and we alter the table to make this change. After a few weeks the Char data which flows into the system might make it impossible for us to revert back to our Numeric field type.

Although it could not be so useful incase of the Field properties which require “Alter” on all tables containing the Field, it would be suitable for all the other Field property changes (like Labels, Descriptions, etc.). The online objects like Pages, Components, PeopleCode, etc. could prove a big advantage for System Restore. This post will mention how we could achieve system restore with PeopleSoft and provide an automated application to achieve the same.

Before applying a project to our existing environment we are bound to perform a PeopleSoft Project compare. We initially apply the Patch project to our Demo environment and then compare with our Development/Test environment and copy the object to our Dev/Test, we reapply our customizations with the help of compare reports, perform tests on the Dev/Test environment and finally copy the project from Dev/Test to Production. To back up and restore our system we can manually list all the objects in our Test/Dev environment and back-up the corresponding rows from PeopleTools tables for the objects in the project. The back-up can be through data mover scripts and the exported .dat file from production can be used to revert back to any specific point.

The manual task of identifying the objects in Test/Dev and then writing export programs to get the Meta Data of these objects in Production can be automated. The link provided below contains an application which will help you achieve system restore for Fields – The application can only back-up and restore the on-line object (the current application can only do it for Fields in a selected project.), it cannot build these changes at the database level – Remember Automate those Migrations by Brent! The export-import through data mover used by the application cannot be suitable for PeopleCode; we might have to use database level import-export options.

Use the link below to download a Zip file which will contain the Setup.exe and the help document. (Approx. 2MB) – Valid for 30 days from today (17th May 2006). Do not be alarmed by “FileUL.com is being moved to a different server, we are sorry for the inconvenience this may cause”. Instead search for this “You can download the file here.” – “here” is the download link.
http://www.fileul.com/view.php?file=T0gzdY

The application is still at its primitive stages and I do not expect it to be perfect. Test it and let me know your suggestions. If you want to work on the Source Code and extend the applications features, feel free to send me a mail at nextrevolutioninerp@yahoo.com. I can provide you with the source code and certain design documents to extend the benefits of this application. The application was built with Visual Basic and tested on Windows XP and PeopleTools 8.46. Do not complain about Translate changes as the application will only handle Fields currently and Translates are distinct on-line objects in PeopleSoft projects.

I still feel like a developer at heart and would like to leave consulting for development – Just like the little boy in Alchemist! I’m not sure when that day will dawn, but I’ll keep this blog to motivate myself in the right direction.

PeopleSoft - "Applications Unlimited"

After the Big News “Applications Unlimited” – by Charles Phillip, I thought I would do some research on these promises. The almost obvious aspect of this announcement was the fear of Oracle in SAP stealing their customer base. PeopleSoft was not procured for its strength in applications; Oracle went in for the huge Customer base that the acquisition would give them – But this is of least importance, as most of know where all this was heading to - Monopoly. The deeper meaning in this announcement when understood will only shatter our hopes of where the IT industry is heading to.

The first PeopleSoft 9.0 product release that I found on Customer Connection was Enterprise Learning Management. Now, that PeopleSoft 9.0 was released for this product I wanted to know what the ELM Development team was up to; for my disappointment I heard that this development team was scrapped and moved to product fusion (This might not really be true, as this one came from a rather unreliable source – But cannot rule this out as impossible!). Nevertheless, let us take a closer look at what, if this were true, are Oracle’s intentions with “Applications Unlimited”. Oracle I guess is looking to convert most of the Development teams of PeopleSoft, Siebel, JDE and Oracle under one umbrella as per their previous plan of Fusion development. Then, what about “Apps Unlimited”, the support development teams will be responsible for developing future releases of PeopleSoft (post 9.0), Siebel and JDE. “Apps Unlimited” will only provide certain enhancements to Customers wanting to stick with PeopleSoft and if any real improvement which the Customer would require from the applications - He might have to move to Fusion. The support development teams might be discouraged from making major enhancements to future releases of existing Application products, while these will be part of Fusion –Customers who would like to make use of these benefits must move ahead to Fusion.

Oracle has pulled this one to perfection; it can now retain the Customers in existing applications and at the same time will be working hard on building and selling Fusion. The real story is what is demeaning; I am yet to see another business where a Company dictates terms to its Customers. Why should this happen in the IT industry? Probably the next decade will save Customers the trouble of depending too much on the product vendor. Probably, we will have a lot more competition in this sphere to pull the client out of the current situation. So much for all these probable statements!

Kindly be informed - these are my personal views and Oracle Corporation has no role to play with this post.

PeopleSoft SQR Upgrades

It has been really sometime since I have written something. I am dedicating most of my time to the Re-Implementation project planning phase, I seem to have taken it personally, and the main reason is – One of my previous colleagues from the PeopleSoft Solution Center said “You will need all the experience inside our solution center to pull this off”, by which he means at least 600 projects that was completed by the Solution center. He has a point and I respect his views, but a challenge is always exiting. So as per my expectations these long breaks between my posts is going to continue. In this post I would like to give some suggestions to achieve the SQR Upgrade during a PeopleSoft upgrade process and the kind of Tools that you could develop to achieve the process with ease. This post talks about how I would handle the project, most of it is based on what I learnt out of my previous experience. If I’m missing something kindly let me know, missing pieces are perfectly possible because I work from India with absolutely no client interaction and no knowledge of the Real Time issues with this process. The offshore process described will involve the Technical Upgrade of the SQRs and I believe the process defined for this specific portion is precise.

The PeopleSoft SQR Upgrade must be divided into three phases,
SQR Technical Upgrade
SQR Functional Upgrade
Testing and Issue Resolution

SQR Technical Upgrade
The SQR Technical Upgrade is further classified into,
SQR Upgrade for Custom Added SQRs
SQR Upgrade for Custom Modified SQRs

Each Custom Added SQR is made compatible with the new PeopleSoft instance, this being a Technical Upgrade we will only consider the effects of Table structure modifications which would prevent an SQR from execution, the results of the SQR can only be perfected after Functional Upgrade and Testing. The Table structure changes must be provided in a document to help the Technical Consultant achieve his goal. The usual Format of this document will contain the changes that must be incorporated into each of the SQRs (This document is the Technical Design document). The Technical Design document can be created by parsing each SQR for table names within the SQRs and printing all the differences in the table structure for these tables. The table structure differences are pulled out of the Meta Tables from our old and new release PeopleSoft databases, another important aspect which must not be overlooked is the impact of “Renamed” objects. The utility which creates the SQR TDD must also look into the path specific rename scripts. The path specific rename scripts are the scripts in your new release pshomes which will be executed for your upgrade path. So the inputs to our TDD utility are folder containing Custom SQRs, Old Release database, New Release Database and the rename scripts. Understanding the precise use of the TDD is very important, at no point in time should we forget the real objective of this stage – Make the old release SQRs executable in the new release. When the report points to a field which was deleted from a specific table, we must look into the data conversion programs to identify the replacement RecordField. When a new Field is added to a table we must look out for the INSERT statements in the SQR containing these tables, and add these newly added fields in the Insert statement, if the values being inserted can be identified easily for these new RecordFields the Technical Consultant can include the precise values else we either include a Default value for the field or data type specific defaults (for CHAR – ‘ ‘, for number – 0, for date – NULL). The values entered in these insert statements will be evaluated during the subsequent phases. Renames must be done depending on the TDD pointers. Finally, the PERSONAL_DATA changes must be made to the SQRs depending on the upgrade path – that is if the upgrade path has transferred all the data from PERSONAL_DATA to PERSON, ADDRESSES, PER_DATA_EFFDT etc. An example for this is – The Custom SQR which looks like this,
BEGIN-SELECT
EMPLID
NAME
ADDRESS
ADDRESS_OTHER
FROM PS_PERSONAL_DATA
WHERE EMPLID = ‘xxxx’

We will have to convert the program to this format in the new release,
BEGIN-SELECT
! Modified by UpgraderName for SQR Upgrade Project Name - Begins
A.EMPLID
B.NAME
C.ADDRESS1
D.ADDRESS1
FROM PS_PERSON A, PS_NAMES B, PS_ADDRESSES C, PS_ADDRESSES D
WHERE A.EMPLID = ‘xxxx’
AND B.EMPLID = A.EMPLID
AND B.NAME_TYPE = ‘PRI’
AND C.EMPLID = A.EMPLID
AND C.ADDRESS_TYPE = ‘HOME’
AND D.EMPLID = A.EMPLID
AND D.ADDRESS_TYPE = ‘MAIL’
! Modified by UpgraderName for SQR Upgrade Project Name – Ends

I guess I have covered the PERSONAL_DATA changes in detail. Look out for the comments these serve as perfect documentation trails within the SQRs. After we do this sort of Documentation whenever a change was made, we make sure the summary goes to the Top of the SQR like this,
!***********************************************************
! SQR Technical Upgrade by Co. Name on May 4, 2006
!
! + Renamed XLATTABLE to PSXLATITEM – 7 Occurrences
! + Implemented PERSONAL_DATA changes in the procedure
! Get-Personal-Info
!***********************************************************
The steps listed above must be performed to complete the SQR Upgrade for Custom Added tables. These steps can be automated; the tool must make all the desired changes, and make proper documentation in the SQR. There are number of instances to be considered, Renames must only be made to the Table or Column name and not to the variables that appear with the same names.

Technical Upgrade of Custom Modified SQRs are done with the help of Beyond Compare (or any such file comparison program) the Custom Modified SQR is compared with the Delivered SQR in the old release and the Customizations are identified. The Delivered SQRs between the Old and New releases are compared to identify the changes made by PeopleSoft. If the Customization can be carried forward it should be carried forward to the New Delivered SQR. This would have been my next objective had I stayed with my old team – Create a tool to automate this analysis of SQRs and then customize them with the help of a code.

All the Technically upgraded SQRs are compiled with the help of another tool – it does nothing more than pass the command line parameters using the common flags set.

SQR Functional Upgrade
The Technically upgraded SQRs are executed with Test data and the results are analyzed. The issue is identified as a business process issues which have come into effect as a result of an upgrade in this stage will be evaluated by the Functional Consultants to provide suitable solutions.

Testing and Issue Resolution
As always we finish our project with this phase.

I’ll probably upload all the Tools mentioned in this post in sometime and provide the links. Currently working on a Tool to get a complete Application Engine program from the back-end, this will include the exact sequence in which the Application Engines are being executed (Dynamic Call section must be handled, Application Designer doesn’t do it! Just trying my best will let you know how it goes – Required for Re-Implementation project.) and the contents (PeopleCode too (Yes!)) and properties of the various steps.