Monday, November 23, 2015

PeopleSoft RESTful Webservice

Here is my initial exposure to creating a RESTful webservice in PeopleSoft. I am running HR 9.1 on PT 8.53.x. 
The requirement is to provide a list of locations based on a region input and then provide a list of employees in that region/location combination.
Here is the flow.

This is accomplished by creating a provider service using the GET method.
I am assuming the basic IB setup is in place, which is essentially making sure the local gateway is up and running and under Service configuration, the REST target location is provided. 

Navigate to PeopleTools > Documents > Document Builder.
First create a document with 2 primitives called as "REGION" and  "LOCATION".
We could have just created a non-rowset based message, but document object provides the flexibility to exchange data in XML or JSON format whereas non-rowset based message would be just XML.
Both primitives will be of the type string, sub-type = None. Adjust the length appropriately, in my case region is just a 1 char code and location is 10 chars.

Now create a response document. This document will have 2 collections one for the location data and the other for the employee data. Under each collection I added a compound child which is essentially a view (record) that I created which will return the appropriate data elements. 
LOC_RESPONSE  -- root element
      LOC_DATA -- collection
            LOC_DTL_VW -- compound
      EMP_DATA -- collection 
            EE_LIST_VW -- compound 

When the compound child is created behind the scenes PeopleSoft creates another document. So make sure the naming of each type is appropriate otherwise it can get pretty confusing. 

Now I created another document which I am going to use for handling fault, this has only one primitive in it of the type text.

After each document is created and saved, verify the document using the "Validate" and "Document Tester" options available on the page.

From the document now create messages, so navigate to PeopleTools > Integration Broker > Integration Setup > Messages. Add new messages of type document. You can keep the document name and message name same. Did this for all the three documents that I created in the earlier step. Once in just click save. The page looks similar to the document builder page, with the addition of the metadata reference section visible at the top of the page. No additional work is required here.

Now navigate to PeopleTools > Integration Broker > Integration Setup > Services and create a REST service. Make sure the "REST Service Type" check-box and the "Is Provider" check-box is checked. Save the service. 
Now select the REST method, in my case "Get", provide a service operation name and click on Add. The REST method is appended to the service operation name.

the REST Base URL will be grayed out and would be auto-populated with the setup info and service name. Under the URI section I have the following indexes.

1. Locations/?region={REGION}
2. Locations/{REGION}/?location={LOCATION}

Document Template - lookup the request document that was created in the first step above. This is the document which has the 2 primitives defined. The primitive name should match what is provided in the curly brackets under the URI section. 

Make the service operation active. Generate any-to-local routing.
Associate the response and fault messages appropriately. In my case the data exchange would be done in JSON format, so under content type I selected "application/json".

Provide service operation security. Verify routing info. Enable logging if required. Doesn't help much in case of JSON messages though.

For the handler section we need to write application package peoplecode for the OnRequest method.  
Following is the basic structure.

import PS_PT:Integration:IRequestHandler;

class Locations implements PS_PT:Integration:IRequestHandler
   method Locations();
   method OnRequest(&MSG As Message) Returns Message;
   method OnError(&request As Message) Returns string;

/* constructor */
method Locations

method OnRequest
   /+ &MSG as Message +/
   /+ Returns Message +/
   /+ Extends/implements PS_PT:Integration:IRequestHandler.OnRequest +/
   /* Variable Declaration */

method OnError
   /+ &request as Message +/
   /+ Returns String +/
   /+ Extends/implements PS_PT:Integration:IRequestHandler.OnError +/


Once the service operation is saved, a WADL has to be generated. So navigate back to services and click on the "Provide Web Service" link and step through the wizard.

Once this is done you can test it from an utility like SOAPUI (yes can be used to test RESTful web services) or plain browser or via PeopleTools > Integration Broker > Services Utilities > Provider REST Template.

So the call to this service would be like so.
which will return all locations in region A and,

which will return employees in region A and Location XYZ01. Location XYZ01 is returned by the first call.

Output 1:

<?xml version="1.0"?> 
<data psnonxml="Yes"> 
"ADDRESS1": "1 Palace Dr",
"CITY": "",
"DESCR": "My Location",
"DESCRSHORT": "My Location",
"EFFDT": "2010-08-02",
"FAX": "",
"PHONE": "",
"POSTAL": "",
"STATE": ""
"ADDRESS1": "100 Main Street",
"CITY": "",
"DESCR": "New Location",
"DESCRSHORT": "New Location",
"EFFDT": "2011-08-02",
"FAX": "",
"PHONE": "",
"POSTAL": "",
"STATE": ""
],"EMP_DATA": {"DEPTNAME": "","EMAIL_ADDR": "","NAME": "","PHONE": "","TITLE": ""}}
} ]]> 

Output 2:

<?xml version="1.0"?> 
<data psnonxml="Yes"> 
    "DEPTNAME": "My Location Dept",
"NAME": "Doe,Jon",
"PHONE": "123456789",
"TITLE": "Managing Director"
    "DEPTNAME": "My Location Dept",
"NAME": "Doe,Jane",
"PHONE": "123456789",
"TITLE": "Managing Director"
} ]]> 


Thursday, September 17, 2015

PeopleSoft BI Publisher Pivot Wizard

The following explains how the crosstab wizard builds the crosstab and what the fields inside the resulting template structure are there for.
To create the crosstab a new XDO command "<?crosstab:...?>" has been created.
XDO Command: <?crosstab: ctvarnamedata-elementrowscolumnsmeasuresaggregation?>
Crosstab variable name. This is automatically generated by the Add-in.
This is the XML data element that contains the data.
This contains a list of XML elements for row headers. The ordering information is specified within "{" and "}". The first attribute is the sort element. Leaving it blank means the sort element is the same as the row header element. The attribute "o" means order. Its value can be "a" for ascending, or "d" for descending. The attribute "t" means type. Its value can be "t" for text, and "n" for numeric.
There can be more than one sort elements, example: "emp-full-name {emp-lastname,o=a,t=n}{emp-firstname,o=a,t=n}. This will sort employee by last name and first name.
"EMPLID{,o=a,t=t}, NAME{,o=a,t=t}"
In the example, the first row header is "EMPLID". It is sort by "EMPLID", order is ascending, and type is text. The second row header is "NAME". It is sort by "NAME", order is ascending, and type is text.
This contains a list of XML elements for columns headers. The ordering information is specified within "{" and "}". The first attribute is the sort element. Leaving it blank means the sort element is the same as the column header element. The attribute "o" means order. Its value can be "a" for ascending, or "d" for descending. The attribute "t" means type. Its value can be "t" for text, and "n" for numeric.
There can be more than one sort elements, example: "emp-full-name {emp-lastname,o=a,t=n}{emp-firstname,o=a,t=n}. This will sort employee by last name and first name.
In the example, the first column header is "CHECK_DT". It is sort by "CHECK_DT", order is ascending, and type is text.
This contains a list of XML elements for measures.
The aggregation function name. Currently, we only support "sum" and “count”.

Using the Oracle BI Publisher Template Builder for Word add-in, we are able to construct the following Pivot Table:

Row layout can be Inline or Outline.

Row/Column totals

Table defined in RTF template

G 999E

G 999E

Code generated by the Pivot Wizard

XML file



Jon Doe
Jane Bond


Wednesday, August 19, 2015

PeopleSoft Process Security (setup/configuration)

In order to provide a user access to run processes in PeopleSoft provide a value for primary permission list as well as process profile permission list on the general tab on the user profile page.

Following is from Peoplebooks.


PeopleSoft determines which data permissions to grant a user by examining the primary permission list and row security permission list. Which one is used varies by application and data entity (employee, customer, vendor, business unit, and so on). Consult your PeopleSoft application documentation for more details. PeopleSoft also determines mass change and definition security permissions from the primary permission list.


The process profile contains the permissions that a user requires for running batch processes through PeopleSoft Process Scheduler. For example, the process profile authorizes users to view output, update run locations, restart processes, and so on. Only the process profile comes from this permission list, not the list of process groups.

Configuring Microsoft IIS 7.0 as a RPS

This post describes how to proxy content to a single server configuration of PIA. In a production environment, a multi server configuration would be used to perform these steps to proxy content to your managed server instance of PIA, PIA1, PIA2, and so on.

Microsoft Internet Information Server (IIS) can be configured as a reverse proxy server (RPS) for one or more WebLogic Server instances. Multiple instances can either be independent instances, or grouped into a cluster. When using a reverse proxy, all URLs that are used to access your PeopleSoft application (even URLs that are stored in the database), need to point to the reverse proxy, and not to the WebLogic server.

Oracle only supports IIS 7.0 as an RPS on Windows Server 2008 x64 Standard Edition, or Enterprise Edition environments. These instructions are based on a physical separation of WebLogic Server and Microsoft IIS 7.0, where both web servers are installed on different VMs.

First verify/enable CGI or ISAPI Extensions on IIS.


1. On the taskbar, click Start, point to Administrative Tools, and then click Server Manager.

2. In the Server Manager hierarchy pane, expand Roles, and then click Web Server (IIS).

3. In the Web Server (IIS) pane, scroll to the Role Services section, and then click Add Role Services.

4. On the Select Role Services page of the Add Role Services Wizard, select CGI or ISAPI Extensions.

5. If the Add role services dialog appears, click Add Required Role Services. (This page appears only if you have not already installed any prerequisite role services on your server.)

6. On the Select Role Services page, click Next.

7. On the Confirm Installation Selections page, click Install.

8. On the Results page, click Close.

Create RPS site in IIS

1. Create a web application in IIS Manager:
    a. Right click Web Sites and select Add Web Site.
    b. Enter the web site name of your web application.
    c. Select the path of your web application port that can be any valid port number not currently in use.
   d. Click OK button to create the web application.
If you can see the name of your application under Web Sites it means that your application has been created and started running.

From the WebLogic server, copy WL_HOME\server\plugin\win\x64\iisproxy.dll to c:\inetpub\psrps
From the WebLogic server, copy WL_HOME\server\plugin\win\x64\iisforward.dll to c:\inetpub\psrps

2. Click the web application node under Web Sites to see all the settings related to the application.

3. Click Handler Mappings link to set the mappings to the handler for a particular MIME type.

4. Click StaticFile link and change the Request path field from * to *.*. Click OK.

5. In Features View, on the server, site, or applicationHome page, double-click Handler Mappings. On the Handler Mappings page, in the Actions pane, click Add Script Map… under the Actions panel on right-hand side.

6. The Edit Script Map dialog box appears. Enter * for the Request path.

7. Browse to the iisproxy.dll file in Executable field. Name it proxy.

8. Click Request Restrictions… button and deselect the Invoke handler only if the request is mapped to check box.

9. Click OK to add this Handler mapping. Click Yes on the Add Script Map dialog box.

10. Close IIS Manger and restart it. Click Root node of the IIS Manager tree and click ISAPI and CGI Restrictions. Select Allow unspecified ISAPI modules check box.

11. Create a file called iisproxy.ini with the following contents and place it in the directory with the plug-in:


Add user IUSR to the psrps folder and also to the psrps\logs folder too.

PeopleSoft Setup

cookie-name tag in the weblogic.xml file of PIA1, PIA2 etc should have the same value. e.g. SERVER-80-PORTAL-PSJSESSIONID

<?xml version="1.0" encoding="ISO-8859-1" standalone="no"?>
<weblogic-web-app xmlns="">

<description>PeopleSoft Internet Architecture</description>

<!-- Coherence*Web Library - Resolution 863761


In web profile - update the General and Virtual Addressing tab as shown below.