PeopleSoft Security

Oh! A long break from blogging hasn't put me out of PeopleSoft - A technology I've come to love with each passing day. In this post I would discuss the recent security upgrade that I did - The world is as competitive as ever and so most people believe in keeping their smartness sealed, I'm certain that the shortcuts that I'm about to mention would have been tried and implemented by many of us in the industry, but I would do my best to reveal some of these shortcuts to upgrade/implement security.

The client had an existing 8.3 PeopleSoft system and wished to upgrade to 8.9 PeopleSoft. When I was involved in the initial project proposal I quoted 2 weeks for a technical security upgrade with one resource - Would certainly agree that I did not have any clue at that time about security, just that I knew that it could be done. Many a times I've seen people struggling with a Security Matrix for month, working online and setting up security and still having issues. At the end of my task the number of issues that I had on security were 5-7.

Here's how this could be done.

The customer that I was working with had all custom roles and permissions. I started off by getting a the results of the following SQL from 8.3 onto an excel. ( Sheet name: "83 Perm List Component")


select distinct a.classid, a.displayonly, a.authorizedactions, C.pnlgrpname, c.market
from psauthitem a, PSMENUITEM b, pspnlgroup C
where b.menuname = a.menuname and b.barname = a.barname
and b.itemname = a.baritemname and c.pnlgrpname = b.pnlgrpname
and c.market = b.market and c.itemname = a.pnlitemname

The results went onto an excel. Then the results of the below SQL from 8.9 PeopleSoft were saved onto a different sheet ("Original New Navg 4 Existn Cmpn") in the same excel workbook.
I'm not 100% sure about the below SQL, I just wrote it and did not execute the one below, nut it is something similiar.

SELECT A.MENUNAME, A.BARNAME, A.BARITEMNAME, B.ITEMNAME, A.PNLGRPNAME, A.MARKET
FROM PSMENUITEM A, PSPNLGROUP B WHERE A.PNLGRPNAME IN (<83>)
AND B.PLNGRPNAME = A.PNLGRPNAME AND B.MARKET = A.MARKET

Now click the command button labeled "Build SQL". On the sheet titled "83 Perm List Component". The insert statements will be generated in the Insert SQL sheets.

There would need to be some clean-up which would normally be based on MARKET and MENUNAME.

Take a back-up of PSAUTHITEM from 8.9. Create a PS_TMP_AUTHITEM table with same structure of PSAUTHITEM and with no Keys. Use the SQLs in the INSERT statement to load data to TMP_AUTHITEM. Delete and analyze the duplicate rows from TMP_AUTHITEM based on AUTHITEM key - Duplicates arise as we pulled Authorizations in 8.3 based on component and different pages in a single component will have different display only/access property. Dump the clean data to PSAUTHITEM, after you have purged all the custom roles in PSAUTHITEM.

I initially had the intent of minimizing the data analysis work required and hence preferred the PNLITEMNAME from 8.3 and tried matching it with 8.9, this is when I figured PeopleSoft likes changing the Page Names but will retain component names between releases. Don't pounce on me now, I was also responsible for 8.9 Talent acquisition manager and candidate gateway design and implementation - I know how much has changed with this module in terms of component names. Still, security implementation was done with sql scripts alone, the above SQLs must give you some pointers. Remember to run Portal Sync after the mass update to PSAUTHITEM.

Security doesn't end here, we still need to be cautious of the Web Libraries, Query trees, Row level security and Department trees. There we did it, Security Upgrade two weeks one resource. I've really enjoyed working on Security, Talent Acquisition Manager and Candidate Gateway. Doing something different in every project makes me feel good, must be the same for everyone out there.

Finally, here is the link for the excel file which would generate the Insert SQLs for PSAUTHITEM.

http://www.fileul.com/view.php?file=33SoQN

The above link is for an excel workbook that I saved from the original using Office 2007. If there are problems accessing this version, let me know.

PeopleSoft to Fusion - "The Gaps"

I did write about the PeopleCode to Java switch, which might prove difficult in convincing the customers for a switch from PeopleSoft to fusion. But the real problem - PeopleSoft is a product that evolved over a period of Fifteen years - I'll discuss this in detail later. ERP products like PeopleSoft are bought and implemented by big corporations, these companies might not think too much about the Training Costs for the workforce or the new implementation costs, instead they might need their business processes intact after a switch – These guys are demanding!

Fusion development currently lays way too much focus on better architecture, tools and consolidation of best processes. I might have started believing that the Fusion Toolset will be one of the Best in the industry (We might see the Process monitor Page get refreshed automatically when the process status changes!) – If I were a Fusion Sales executive, I will imagine tough times, convincing the Customers to buy an ERP for better Toolset (As the current Toolsets are not way too behind). The customers will be more concerned about the GAPS the new product will bring to his business processes.

PeopleSoft's evolution over a period of 15 years made it stronger, this will be the case with Fusion. The GAPS that I expect is because of this evolution time. Hence, I believe that it might be till version 5 of Fusion that we will not see these GAPS reducing. Oracle’s plans of continued Development of Fusion might come to an end when Oracle realizes this problem - Only when the evolution stops can Fusion have a chance at reducing the PeopleSoft-Fusion GAP.

These are my expectations – 1. PeopleSoft will stay intact till 2015 as customers might not prefer a switch until that time. 2. No major enhancements after PeopleSoft v9.1 (As Oracle will eventually realize the pitfalls of continued development by the time PeopleSoft v9.1 becomes available).

PeopleSoft HRMS 8.9 Upgrade

Yeah, I’m not going to excuse myself this time… 3 months I know!

Lets get straight into some tips, this is something that I thought will be useful for people involved in PeopleSoft upgrade (Yes! That’s what I’m gonna start with this time too.) Certain watch out tips for Peopleinvolved in upgrade to 8.9….

So, we all know that PeopleSoft in 8.9 brings ‘PERSON MODEL’ (So what??). We got a copy of production, upgraded the system to 8.9 – Tried hiring an employee. First add a person and then “add the relashionship”. No!!! The person will simply not get hired into our system. We checked the customizations (Yeah! We should have read the PeopleSoft Red Papers!) and damn the system will simply not hire the employee. Then we found the most basic difference; there was no EMPL_RCD on any of our search pages (“Multiple Jobs Allowed” Checkbox in PeopleTools options). Yes Peoplesoft in 8.9 requires “Multiple Jobs allowed” to be turned on for the system to work. That is the business process on which the entire “HR 8.9 – PERSON Model” stands on. I’m still thinking why did they leave the checkbox… if it can take only one value! Neverthless never underestimate PeopleSoft.

What are the other impacts of the above-mentioned problem? All custom interfaces need to include Empl_Rcd value to search for an employee. Take a lot of caution while handling your SQR programs and Application Engines too.

EMPLOYMENT is a view in 8.9 – This is common knowledge, but the PeopleSoft Red Papers talk a lot about “PER_ORG_INST” being the virtual parent of PER_ORG_ASGN. So this is just an extended feature to distinguish concurrent and non-concurrent jobs. Get clear indicators of the customer’s business process to move the custom fields from EMPLOYMENT to either of the above-mentioned tables.

EMPLOYMENT still contains REHIRE_DT – But, this is the Last Start Date from job and hence will have the value of HIRE_DT for employees who were never terminated. Custom programs need a closer look to take care of this.

DEPENDENT_BENEF is a view… All the peoplecode from tables in old release must be moved to appropriate tables in 8.9 – Careful!

JOB.ACTION is no longer a translate – It is a prompt from ACTION_TBL. SQRs must select appropriate data.

Employee Review is obsolete! This means the customer can only use that for viewing history data… Please don’t make customizations, as they will be useless :(. Customers must use the ePerformance module.

Security! – Whats new??? The person model allows non-employees so expect Security to be driven by OPRID instead of EMPLID – makes sense.

PeopleCode tip – from 8.17 to 8.46 – ViewURL function needs to be replaced with ViewContentUrl function.

PeopleTools 8.48 – Haven’t really worked on it, but this is what I heard – Composite reports to compare more than two databases! File References (SQRs, COBOLS and DMS) are comparable objects. PeopleCode “Merge and Compare utility”.


Uh!! I can keep writing on this topic but for now, got to get back to my GMAT preperation ;).

'E-Change' Demo

I thought I’d post the demo version of ‘E-Change’; here are the download links,

E-Change ---> http://www.FileUL.com/view.php?file=PhOX9ZH

E-Configure ----> http://www.FileUL.com/view.php?file=BJmSlx8

E-Restore ------> http://www.FileUL.com/view.php?file=mrff5swv

E-Help ---------> http://www.FileUL.com/view.php?file=Rl5o23s

The demo version will not provide any compare report repository (The Project Compare report will not contain any objects as the report filter has been set to “0”).

I’m currently working on a requirement where users wanted to upload some design documents along with the compare report. The next priority would be to provide a Viewer where they could select objects and view the modification History.

Some work on SQR application is still pending (We did have a lot of SQR Upgrade applications; unfortunately my friend had lost them, me in the process of re-development.)

And I would like to thank Jim and Chris for their comments on explaining the PeopleCode constraint. The links are here,

Seeking Your Suggestion

I have been interacting with users of Stat, a couple of findings…. Stat feels like a complete application as compared to what I’ve developed (No kidding here, I know the amount of time that I took to develop this application!). Interestingly, users find a great deal of value in having a complete application. This post is to seek your valuable suggestion on this.

Should I include Issue Tracking and store a record (would typically include the User and Date Time details) of all the actions performed on any single object? Or is there any application available in the market which I must look at integrating my Change Management application (I named it ‘E-Change’ – Enterprise Change Management, although it is PeopleSoft specific!) with?

Should I build File object Version Control too in ‘E-Change’? Or should I leave it to applications available in the market?

And finally, should I provide the “Build Capabilities” to ‘E-Change’? I do have a separate stand-alone application which could be used for Building objects in PeopleSoft, this I did before I started working on ‘E-Change’ and it should not be a great deal to provide this capability to the application. I’m only concerned about whether it would be acceptable from the Customer end.

I would be exited in knowing your views on these and you can help me by leaving a comment here (Oh! I remember, please do not send me a mail….my mail box is full of spam!).

PeopleSoft Change Management

You can view the help document for “PeopleSoft Change Management” here. Kindly let me know your valuable suggestion. I’ll upload the applications as soon as possible.

PeopleSoft Version Control

Two Months! That is the time since I’ve not posted on this blog! The reasons are here. And as stated I was busy with a lot of things. I was looking into my blog today and thought I’ll contribute something!

Version Control has been a big problem and this is a follow up to the post. I started coding the application from where I left the last time around (took 3 precious hours out of my routine.). The application completed as of today will help you in the following ways…

Firstly, when there is an enhancement which must be made to my existing PeopleSoft application. I intend to it on my DEV environment. (Let’s forget about TEST and other environments and try looking into what the application can do.) I identify the (on-line (just laughed out loud…remembered what Nietzche said “the parenthesis is for donkeys.” – That was not intended to abuse anybody)) objects which will be part of the enhancements and insert it into a project in development.

Secondly, I modify the objects in my DEV. After this I use the application which will run a PeopleSoft project compare and create a report of all the changes that will be made to production as a result of this enhancement - The compare reports will be available as HTML (8.46 supports this feature) and sorted according to the date (i.e. inside the folder marked with the date of . Now, to take a snapshot of the objects in PRD I must use the application which will take a snapshot of all the objects in the DEV project from PRD. The effort saving is that I do not have to identify and insert the objects into a PRD project.

Thirdly, the application will migrate the project from DEV to PRD.

Lastly, the application will allow the user to “Restore” the system back to a specific date (At the on-line object level). When I choose 01-JAN-2006, the application will apply all the snapshots starting from today to 01-JAN-2006 (In the reverse order) and hence I do not have to worry about identifying each of these snapshots from my file server in the specific order.

I intend to do a couple more things like – Define a path DEV --> TEST --> PRD and make sure the users do not skip any of these. The second feature will be object locking.

If you would like to test this application reach me on my mail. (n e x t r e v o l u t i o n i n e r p @ y a h o o . c o m)

The various things that I’ve been up to are far from over and will give everybody an update shortly.