Enemies of Upgrade

Upgrade – An upgrade is nothing but a shift towards a higher version of software application. However,in the world of PeopleSoft, that counts as the scariest word so far. As an ERP, PeopleSoft has thousands of installations worldwide. Yet, there would hardly be a handful of customers, who would welcome an opportunity to Upgrade. Such a customer, is either too very well disciplined in the way they use PeopleSoft that they belong to a pantheon of revered PeopleSoft users or they use too little of PeopleSoft, to be scratching the surface of a “calorie-bomb”ish box of doughnuts.

Oh wait. I take my word back. PeopleSoft is not a calorie-bomb. PeopleSoft is a box of goodies, that packs great functionality, a robust technical framework and the power to customize. Don’t like a label? No problem. Need to capture specific information on a PO? No problem. Does your performance appraisals have the most complex approval process known in the entire Milky way? No problem. Custom reports? No problem. Custom modules? No problem. Launch a rocket? mmmmm…not just yet. You get the idea 🙂 You’ve got the power in your hands to weave exactly what your organization needs. Remember, with great power, comes great responsibilities.

Forking Off

Right after implementation, customers get carried away by the new found power to customize PeopleSoft to suit their genuine needs and well…their whims and fancies (sometimes?). Thats a sublime starting point towards what would turn out to be a potentially expensive journey called “Forking-off”. When you fork-off, you start creating differences between your PeopleSoft system (as-is) and the Vanilla PeopleSoft (supposed-to-be) delivered by Oracle. In an ERP like PeopleSoft, every object/software artifact has two owners – Oracle and You. When you fork off, you step on Oracle’s foot, jostle your way in and start co-owning the object. Does that sound right? Can you own 20% of a PeopleSoft record or 40% of a Page Peoplecode? Ain’t no big deal? Read on.

Staying Current

Just as you’ve got your own sweet little plan for customizing “your” PeopleSoft, Big Brother Oracle has bigger plans to evolve the product. The Brother keeps supplying you with patches that fix product issues and tax updates that you need to keep the tax-man happy. Over time, the Brother starts throwing bigger baggages at you in the form of bundles, maintenance packs and service packs. Whats more? The Brother expects you to get going with all those baggages and be in a state-of-mind which is popularly called “Being current”. Mind you, Big Brother likes you, when you “stay current”. And why wouldn’t you want to be current? After all, you are paying for all those patches, tax updates, bundles, MPs and SPs as license fees. You might as well apply them and enjoy the benefits.

However, staying current, like most good deeds, is easier said than done, due to one major and two minor reasons:

Major reason – Time

Time, though, is a multi-dimensional problem. PeopleSoft Administrators need the time to download and apply the patches in a demo environment. Like all code changes, patches & bundles also travel through your organization’s change control route. Hence, developers need time to apply those patches & bundles in a development environment. Most often, a piece of code delivered in a patch will conflict with a piece of code that you modified to suit your organization’s needs. So, developers need the time to retrofit code, also known as “finding middle ground”. Users need time to test the changes. The Application Manager has to find the time to manage the time required for this endeavor.

Minor Reasons – Budget and Expertise

Executive sponsors need to allocate an annual budget for staying current. Above all, your Subject Matter Experts must have the know-how to analyze the patches/bundles and suit them to your organizations business processes.

As it is, staying current has been a tough proposition. The longer you stay out-of-touch, the greater the effort required to re-route and become current. Some customers stay so out-of-touch that they reach a point of no return. I’ve personally known a PeopleSoft customer who had let their instance live through two leap-years without even applying a single patch. Such a waste of license money!

Again, the farther you fork-off, the harder it gets to stay current.

To sum up, forking off and not staying current are the biggest enemies to a smooth upgrade. Ask me how? Sometimes, proving the converse is easier. Had you not forked off, had you only so obediently stayed current….wouldn’t you have stayed inline with the product’s roadmap? Wouldn’t it be easier to catch up with changes in the product’s landscape? You would just be gently “reaching out”, rather than “giant-leaping”, to a higher product plane. So, by staying current you make future upgrades a breeze.

But then, is forking off such a sin? Should you avoid that at any cost? What if the cost amounts to not serving your business needs? Should you not customize at all? The next post will have my two-cents on it.

The power of RANK

Talk PeopleSoft and you can’t avoid talking about the omni-present, omni-potent “Effective Date”. Ah! What a convenient way to store active and historic records, all in one place? Often you end up developing SQLs that self-join an effective dated table to reach out to the most active record. But, when an effective dated table grows, grows and grows, you soon start having a “King Kong-rampaging-the-streets-of-New-York” scene in your database. Pages take forever to load, sometimes even time-out. Processes never seem to end, so you can’t generate those interface files you need to send across to up/down stream systems. Heck…you can’t even generate that simple Employee listing report and keep your manager happy.

A typical effective-dated query on JOB table looks like:

SELECT J.EMPLID, J.EMPL_RCD, J.EFFDT, J.EFFSEQ……….
FROM PS_JOB J
WHERE J.EFFDT = (SELECT MAX(EFFDT) FROM PS_JOB
WHERE EMPLID = J.EMPLID
AND EMPL_RCD = J.EMPL_RCD
AND EFFDT <= SYSDATE)
AND J.EFFSEQ = (SELECT MAX(EFFSEQ) FROM PS_JOB
WHERE EMPLID = J.EMPLID
AND EMPL_RCD = J.EMPL_RCD
AND EFFDT = J.EFFDT)

Each row for an employee is matched up against all of their other rows to determine the latest effective-dated row. In other words, if the employee has three rows in all, you access the three rows once in the main query and three times again for each row in the subquery. Thats 3+(3×3) = 12 reads against the database for one employee.

If an employee has about 100 rows of data (a common scenario in organizations that have been running on PeopleSoft for over a decade), thats a whopping 10,100 – 100 + (100 x 100) – reads against the database. That’s just for one employee! Many other mammoth-sized tables, for example the Global Payroll Accumulator table GP_RSLT_ACUM, use effective dates and suffer similar performance issues.
Oracle’s LAST analytic function offers a better way to handle this problem. The LAST function with a few other friends like KEEP, DENSE_RANK and OVER, forms a great team that delivers results! Without further ado, here’s the LAST way of doing the effective dated sub-query.

SELECT J.EMPLID, J.EMPL_RCD, J.EFFDT, J.EFFSEQ
FROM (
SELECT EMPLID, EMPL_RCD, EFFDT, EFFSEQ
MAX(EFFDT) KEEP (DENSE_RANK LAST ORDER BY EFFDT, EFFSEQ)
OVER (PARTITION BY EMPLID, EMPL_RCD) MAX_EFFDT
MAX(EFFSEQ) KEEP (DENSE_RANK LAST ORDER BY EFFDT, EFFSEQ)
OVER (PARTITION BY EMPLID, EMPL_RCD) MAX_EFFSEQ
FROM PS_JOB
) J
WHERE J.EFFDT = J.MAX_EFFDT
AND J.EFFSEQ = J.MAX_EFFSEQ

All the fire power is packed in a single inline query, in which you select all the columns you need plus two other pseudo columns – MAX_EFFDT and MAX_EFFSEQ.

The pseudo-column is composed of:
1. An aggregate function, which could be any of MAX, MIN, SUM, etc., which aggregates on rows ranked by LAST.
2. DENSE_RANK LAST – Handpicks only those rows that rank LAST after ordering the dataset by EFFDT and EFFSEQ.
3. OVER – Indicates how to group the dataset, in this case, EMPLID and EMPL_RCD.

The inline query returns as many rows as an employee has on the PS_JOB table. The main query doesn’t have to do much here. It does a simple join on the pseudo-columns to throw the result back in a jiffy. There have been cases when the LAST function performed hundreds of times faster compared to the old-fashioned sub-query.

You can use the LAST function, virtually anywhere you would use a standard sub-query. Some of the other tables that I have had tremendous luck with LAST function are PSTREENODE and PS_GP_RSLT_ACUM.

Being Metadata aware!!

I firmly believe that PeopleSoft developers should understand the PeopleTools tables. This understanding opens up entirely new ways to see and appreciate the power of meta-data driven development. Most PeopleSoft enthusiasts know that PeopleTools tables start with PS without an “_” (underscore).

In my early days as a PeopleSoft developer, I was asked to modify the behavior of a prompt field on Job data page. This prompt field sourced its data from a custom table (lets name it ZZ_CUST_TBL). Obviously, ZZ_CUST_TBL had its own setup page.

Using Application Designer’s “Find Definition References”, I figured out the page > component > menu hierarchy that the table was bundled into. But, that’s how far I could go. How do I find out the navigation to the component?

Here’s a short list of PeopleTools tables and what they contain:

PSRECDEFN – Record definitions
PSRECFIELD – Record Field definitions
PSDBFIELD – Field definitions
PSXLATITEM – Translate values
PSPNLDEFN – Page definitions
PSPNLFIELD – Page field definitions
PSPNLGRPDEFN – Component definition
PSMENUDEFN – Menu definitions
….and so on and so forth.

For a complete list of PeopleTools tables, for a given release, MVPRDEXP.DMS (found in ‘scripts’ directory of the File Server) is a dependable and definitive source.

I’ll go back to my challenge of figuring out the navigation to the page that holds ZZ_CUST_TBL. Personally, I prefer querying PeopleTools tables against performing a “Find Definition References”. I hate to drum my fingers while that nagging grey dialog box keeps me waiting rather unmercifully. Finally, I get dumped with a truckload of references, while I simply wanted to know the pages a record field is used in.

Over time, I developed my own ready-reckoner of sorts. Here we go.

This query throws back all the pages ZZ_CUST_TBL is used in.

SQL > SELECT PNLNAME, RECNAME, FIELDNAME from PSPNLFIELD WHERE RECNAME = 'ZZ_CUST_TBL';

ZZ_CUST was the page I was interested in. So, I go on to find out the component that holds this page.

SELECT PNLGRPNAME FROM PSPNLGROUP WHERE PNLNAME = 'ZZ_CUST';

And the grand finale! The entire portal navigation tree is stored in PSPRSMDEFN table. Specifically, PORTAL_URI_SEG2 is the column that stores the component that corresponds to a navigation link.

SELECT PORTAL_URI_TEXT FROM PSPRSMDEFN WHERE PORTAL_URI_SEG2 = 'ZZ_CUST_TBL'

The PORTAL_URLTEXT returned by the query reads “/c/ADMINISTER_WORKFORCE_(GBL).ZZ_CUST_TBL.GBL”, which is nothing but the menu and the component stringed together.

On a PIA screen, the url format is
http:/server:port/psp/domain/EMPLOYEE/HRMS/c/………………………..

Now, I would snap the PORTAL_URLTEXT on the URL,

http:/server:port/psp/domain/EMPLOYEE/HRMS/
c/ADMINISTER_WORKFORCE_(GBL).ZZ_CUST_TBL.GBL

…and go.
Voila! I land up on the page that holds ZZ_CUST_TBL.