Using PUM to apply PRP (PeopleSoft Release Patchset)

PeopleSoft (Oracle) with the advent of 9.2 release introduced the PUM tool to apply and migrate patches/bundles/fixes. Going forward an individual patch or fix is now called as PRP (PeopleSoft Release Patchset).

So in a nutshell the process is to download the PRP, apply it to the current Image (which means you might have to first download and get the latest version of the PUM Image up and running) and then apply it to your Demo (this might be optional) continued with applying the PRP to your DEV, TST/QA and finally Production.
I have covered some basic steps on how PUM works in the post here.

Pre-reqs
I had to install the latest PeopleTools client which came with the Image 10. Also had to install Oracle 64 bit client. Mapped the pi_home share to a drive on my workstation. Make sure pi_home is writtable. For making it writtable do the following

Edit the /etc/samba/smb.conf file

Before >> guest account = nobody

After >> guest account = root

Under the [pi_home] section

Before >> writable = no

After >> writable = yes

Restart samba service as below

service smb restart

Map a drive and when prompted for credentials provide

workgroup\root as the username and root’s password as the password


1. Navigate through wizard (Tools > Change Action)
2. Selection Action would be Update Manager
3. On the General tab, select PS_HOME. For this example I am working on FSCM Image 10, running on tools 8.54.05. So I installed the client from the PUM image share to my local drive under C:\PT85405. Designated a download, staging and output directory. Assigned path to Oracle client. Had to install the 64 bit version which was delivered part of the image share.
4.  PUM Source tab - stepped through the wizard and made sure connection successful. Mapped the share to a drive on my workstation. \\image_ip_address\pi_home
5. EM Hub tab - pointed it to my demo db. My Demo db is running FS 9.2, PT 8.53.14 on SQL Server 2012
6. On Select Tab - selected "Apply downloaded PeopleSoft Release Patchset to PeopleSoft Image".

So now the current wizard is complete and the next one pops-up.

1. Select download directory where you downloaded the zip file from Oracle's website
2. PSCA will only show applicable patches if your download directory contains other zip files. Select and move on.
3. Compatibility check should run to success.
4. Apply summary - eye ball and make sure everything looks right.

PSCA will create a job and start applying the project to the Image db. Verify logs once done.

Login to the Image 10 PIA and navigate to PeopleTools > Lifecycle Tools > Update Manager > Define Change Package. I have already uploaded my demo db to this Image 10 db. (definitely another post on that to follow). Provide a package name, select "Specific Updates" radio button, provide bug number and finish creating the package.

So prior to 9.2 this was a simple step, search for patch on Oracle website, download and apply to demo, but now its very very elaborate. Come to terms, no other way out !!

Using PeopleSoft Update Manager (PUM)

This is a test to understand how PUM works and what are the different pieces involved.

Workstation/Laptop config
Windows 7 Enterprise, 64bit OS, 12GB RAM, 237GB HDD

VM Details

FSC92DMO - Patch 16877487: PEOPLESOFT FSCM IMAGE 9.2.001
EP92P005 - Patch 17965081: PEOPLESOFT FSCM IMAGE 9.2.005

Disk Space Details

zip files – 24.5GB
zip files expanded – 25GB
.ova file – 25GB
So total disk space used before even creating appliance = 75GB

Once .ova file is created the expanded files can be deleted.

The .ova file can be deleted once the VM is up and running

Both VMs once up and running take around 90 GB

Environments

EP92P005 (PI) - FSCM 9.2 Image #5, PT 8.53.09
FSC92DMO (target) - FSCM 9.2, Demo, PT 8.53.04

If target and PI are at different minor tools release, then run client install from the higher tools release (8.53.09) and then install PSCA running at tools rel 8.53.09. Basically take whatever comes with the PI environment, as that would generally be latest and greatest.

Update the environment long and short name in the following page before starting any agents.
PeopleTools > Utilities > Administration > PeopleTools Options

I am starting FSC92DMO VM first and then the EP92P005. As Dynamic IP addressing option has been chosen, the first one has 192.168.56.102 and the next one 103.

Verify App Designer, SQL Developer and web connectivity to both envs.

http://192.168.56.102:8000/ps/signon.html

http://192.168.56.103:8000/ps/signon.html

EMHUB/Agents

Logged into FSC92DMO VM, su in as psadm1, updated PSEMAgent config URL to http://192.168.56.102:8000/PSEMHUB/hub and Unix drives to crawl to /opt/oracle|/home/psadm2

Rename the delivered search-results.xml file to search-results-old.xml and then start the agent in the background as follows.

./StartAgent.sh &

 

Did a local client install under c:\apps\PT853. Updated the PSEMAgent config file here also to point to http://192.168.56.102:8000/PSEMHUB/hub and windows drives to crawl set to C:/apps/PT853|C:/apps/oracle

Started the agent as an administrator.

 

Start PSCA

 
 

 

 

 

 

C:\apps\PT853\ is where the workstation install of PT 8.53 and 09 patch was done
Y:\ is mapped to the pi_home share of the PI environment.

EMF Agent is running on target which is FSC92DMO. I have one agent running on the Linux VM. So it found App/Prcs and Web. For File Server Linux is not a certified option, which is why I have a local install of PeopleTools which acts as the file server.

 

Additional Options - no changes took defaults

 

 

 

Target is FSC92DMO db and source will be the PI image db EP92P005.

 

 

Click Next and then Finish on the confirmation screen.

 

Now file server section is automatically filled out.

 

Key in the db name and once you tab out the following screen appears

 

Click Next to view the confirmation screen and then Click on Finish which will take you back to the following screen

Key in the URL to the PI environment.

 

 

Examine Patch Target and Source db info as displayed above.

Now click on the “Open Browser and Connect to the PI Database” button.
Signon page pointing to the PI Image URL opens up. Rest of the steps are now done within PIA and we will come back to this page to apply the change package which will be created in your PIA session.

Step 1 is automatically grayed out, as we only have one target db in this case.

Provide a Package name and click next

So there are 30 patches that need to be applied

Now back in PSCA.

Now begin applying this patch as normal.

 

XSL Template in BI Publisher (PeopleSoft)

Here is a quick tip on how to create and use XSL template via BI Publisher in PeopleSoft.
For this test I am using PeopleTools 8.52.x but I am sure this is even possible in older releases like 8.49.x and above.

When you create a new Report defn, pick XSL as the template type.

1. Using XRFWIN as the data source create a new report defn

2. Pick XSL as the template type

3. For the XSL template, either you can manually create a file manually or some other web based app or use the following steps to generate one for you.

Take the delivered xrfwin.rtf RTF template.

Create a dir, lets say c:\temp\xmlp and copy it here. Copy the jlif dir from your local install of the BI Publisher Desktop. (C:\Program Files (x86)\Oracle\BI Publisher\BI Publisher Desktop\Template Builder for Word\jlib)

So you will have c:\temp\xmlp\jlib

Now via cmd line from under c:\temp\xmlp execute the following.

javaw -Duser.language=en -Duser.region=US -Xmx256M -jar ".\jlib\RTF2PDF.jar" "xrfwin.rtf" "dummy" "." "XSL" "TRUE" "en-US" "dummy" "dummy" "." ""

This will create two sub-dirs log and tmp. The xml file under tmp will be you xsl template. Upload this to report defn and verify.

eText Gotcha's

I have been using eText BI Publisher templates for generating extract files for PeopleSoft data for a while now. Generally I would do all the filtering/transformation of data within PeopleSoft and generate a well-formatted XML file to be read directly without much transformations done in the template itself.

Recently, I had to use the same XML file for an eText template as well as a RTF report template with slight differences in the output. After doing a lot of hunting around found a very good trick to perform data filtering within the eText template.

So in this case, I had to filter out data when the amount is less than equal to zero on the extract (but include that data in the report)

My XML file was pretty straight forward, had a header tag, detail tags and trailer tag, something like below.

<Start>
<N_HEADER>
<RUN_ID>PR13-07-05</RUN_ID><CREATION_TIME>12:36:36</CREATION_TIME>
</N_HEADER>
<N_DETAIL>
<EMPLID>12345</EMPLID><CHECK_DT>2017-11-01</CHECK_DT><AMOUNT>100.12</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>67890</EMPLID><CHECK_DT>2017-11-01</CHECK_DT><AMOUNT>200.50</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>22112</EMPLID><CHECK_DT>2017-11-01</CHECK_DT><AMOUNT>0</AMOUNT>
</N_DETAIL>
<N_DETAIL>
<EMPLID>33121</EMPLID><CHECK_DT>2017-11-01</CHECK_DT><AMOUNT>6100.45</AMOUNT>
</N_DETAIL>
<N_TRAILER>
<TOTAL_AMT>6401.07</TOTAL_AMT>
</N_TRAILER>
</Start>

Following is my eText template (Detail record). Highlighted changes in red.

<LEVEL>		N_DETAIL [AMOUNT > 0]
<POSITION>	<LENGTH>	<FORMAT>	<PAD>	<DATA>	<COMMENTS>
<NEW RECORD>		Detail

.....
.....
.....

<END LEVEL>

N_DETAIL [AMOUNT > 0]

That did the trick, so EMPLID 22112 is automatically filtered out.

Another requirement was to report row counts in the trailer record. So instead of count it within the peoplecode and store it in the XML file, I could use the COUNT function as below.

8

5

Number

COUNT(Detail)

Total # of detail records in file

The argument "Detail" to the COUNT function is the New Record value defined for the detail section. If I have to count the header and detail rows also then I can do something like COUNT(Detail)+2, as I know I will have only one header and detail row in my file.

There are other functions like SYSDATE to print the current system datetime stamp.

3

10

Date, YYYY/MM/DD

SYSDATE

Current Date

So I could something like above and format it as YYYY/MM/DD to print the current date. I could also print current time as follows.

3

10

Date, HH24:MI:SS

SYSDATE

Current Time

This prints the time in 24hr clock. This is always GMT/UTC time.

Another function that I have used often is the FORMAT_NUMBER function. So I had a requirement to print number without the decimals, as a continuous string persay, with leading zeros.

FORMAT_NUMBER(AMOUNT*100,'0')

So with reference to the above example, if I had 9 positions to print the numerical amount value then

<EMPLID>12345</EMPLID><AMOUNT>100.12</AMOUNT>

will be printed as 000010012

Another requirement was to sum up the amounts from the detail and write the total to the trailer. 
One method is to compute the amount upfront and write to the XML file trailer tag. Another way (shown below) is to compute the value in the eText template.
Add a "Format Data Levels" table as shown below, after the "Format Setup".

Format Data Levels:

Hint: Define data levels that are needed in the format which do not exist in data extract…

<DEFINE LEVEL>	N_TRAILER
<BASE LEVEL>	N_DETAIL
<GROUPING CRITERIA>	CHECK_DT
<END DEFINE LEVEL>	N_TRAILER

....
....
....

<LEVEL>		N_TRAILER
<POSITION>	<LENGTH>	<FORMAT>	<PAD>	<DATA>	<COMMENTS>
<NEW RECORD>		Trailer
1	2	Alpha		‘T’	Record Type
3	9	Number		COUNT(Detail)	Record Count
12	17	Number		SUM(AMOUNT)	Total Amount from Detail

....
....
....

<END LEVEL>

N_TRAILER

Hope this helps.

PeopleSoft Pivot Grid

PT 8.52 introduces the analytical tool called as Pivot Grid. Following is my first shot at using this functionality. The goal was to explore the functionality with minium setup configuration. As of now Pivot Grid supports only a PSQuery data source.

1. Created a query in Query Manager. My Query returns JPM Profile data for Employees in my HCM system.
2. Navigate to Reporting Tools > Pivot Grid > Pivot Grid Wizard and follow the steps in the wizard.

 















Only valid models are available from the Pivot Grid Viewer

Provide query name and select the columns from the query which will be part of this pivot grid.

At least one Axis and one Value member is required. Total column is available only for the data source column with column type Axis with data type not equal to date. For Char fields aggregate function defaults to Count and cannot be changed. If query has runtime prompts then you should see another section to provide defaults for runtime prompts. In my case there were not prompts defined on the query.

Selecting Grid only option. Specifying the Grid and Chart Axis. Kept the default Grid and Chart Options.



Preview the result. So as per my setup in Step 4 of the wizard, Name is a filter, ID and EFFDT are columns and rows are category and item description.

I can export just the specify data the grid is displaying (by clicking on grid icon in the grid title bar, next to the help icon) or all data to excel (which is similar to downloading results to excel from query).

Via Reporting Tools > Pivot Grid > Pivot Grid Viewer, an end-user can lookup the models. Though I had selected Grid only as the option, when creating my model, I do see a check-box to display a chart in addition to the grid.

 

Have the option to view it as a Bar, Line or Pie graph.

I did get some hard peoplecode errors while designing my model as I was flipping through various options, which tells me that this functionality is not robust as yet. The error messages are not at all user friendly for a functional person to understand.

So thats it for now.

Thanks

Deepak.