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;
end-class;

/* constructor */
method Locations
end-method;

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

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


End-method;


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.
http://servername/PSIGW/RESTListeningConnector/Locations.v1/Locations/?region=A
which will return all locations in region A and,


http://servername/PSIGW/RESTListeningConnector/Locations.v1/Locations/A/?location=XYZ01
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"> 
  <![CDATA[
{"LOC_RESPONSE": {
"LOC_DATA": [
{
"ADDRESS1": "1 Palace Dr",
"CITY": "",
"DESCR": "My Location",
"DESCRSHORT": "My Location",
"EFFDT": "2010-08-02",
"EFF_STATUS": "A",
"EXTENSION": "",
"FAX": "",
"LOCATION": "XYZ01",
"MESSAGE_TEXT2": "",
"PHONE": "",
"POSTAL": "",
"STATE": ""
},
{
"ADDRESS1": "100 Main Street",
"CITY": "",
"DESCR": "New Location",
"DESCRSHORT": "New Location",
"EFFDT": "2011-08-02",
"EFF_STATUS": "A",
"EXTENSION": "",
"FAX": "",
"LOCATION": "ABC01",
"MESSAGE_TEXT2": "",
"PHONE": "",
"POSTAL": "",
"STATE": ""
}
],"EMP_DATA": {"DEPTNAME": "","EMAIL_ADDR": "","NAME": "","PHONE": "","TITLE": ""}}
} ]]> 
</data>

Output 2:

<?xml version="1.0"?> 
<data psnonxml="Yes"> 
  <![CDATA[{"LOC_RESPONSE": {
"EMP_DATA": [
{
    "DEPTNAME": "My Location Dept",
"EMAIL_ADDR": "jon.doe@company.com",
"NAME": "Doe,Jon",
"PHONE": "123456789",
"TITLE": "Managing Director"
},
{
    "DEPTNAME": "My Location Dept",
"EMAIL_ADDR": "jane.doe@company.com",
"NAME": "Doe,Jane",
"PHONE": "123456789",
"TITLE": "Managing Director"
}
]}
} ]]> 

</data>



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?>
Parameter
Description
Example
Ctvarname
Crosstab variable name. This is automatically generated by the Add-in.
c72515
data-element
This is the XML data element that contains the data.
"//Payroll"
Rows
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.
Columns
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.
"CHECK_DT{,o=a,t=t}"
In the example, the first column header is "CHECK_DT". It is sort by "CHECK_DT", order is ascending, and type is text.
Measures
This contains a list of XML elements for measures.
"ANNUAL_RT"
Aggregation
The aggregation function name. Currently, we only support "sum" and “count”.
"sum"

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

C H ACCEMPLID
NAME
G CHECK_DTE
Total
G RS EMPLID
G NAME
G 999E
999E


G 999E
999

Code generated by the Pivot Wizard


XML file






















……
……
……






















Output

EMPLID
NAME
5/31/2009
7/31/2011
8/31/2011
9/30/2011
3/31/2019
4/30/2019
Total
 01234
Jon Doe
4167
4167
4167
4167
4167
4167
500000
 99999
Jane Bond
0
3472
3472
3472
0
0
250000


4167
7639
7639
7639
4167
4167
750000


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.

PrimaryPermissionList

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.












ProcessProfilePermissionList 

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.

WINDOWS SERVER 2008 OR WINDOWS SERVER 2008 R2

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:

WebLogicCluster=server1:80,server2:80
DynamicServerList=ON
ConnectRetrySecs=5
ConnectTimeoutSecs=25
Debug=ALL
DebugConfigInfo=ON
KeepAliveEnabled=true
WlForwardPath=/psp,/psc
WLCookieName=SERVER-80-PORTAL-PSJSESSIONID
WLLogFile=C:/inetpub/psrps/logs/psprs.log
SecureProxy=OFF




































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="http://www.bea.com/ns/weblogic/weblogic-web-app">

<description>PeopleSoft Internet Architecture</description>

<!-- Coherence*Web Library - Resolution 863761
  <library-ref>
      <library-name>coherence-web-spi</library-name>
  </library-ref>
-->

  <session-descriptor>
  <id-length>32</id-length>
  <cookie-name>SERVER-80-PORTAL-PSJSESSIONID</cookie-name>
  <cookie-domain>.company.com</cookie-domain>
  <monitoring-attribute-name>USERID</monitoring-attribute-name>
  <persistent-store-table>wl_servlet_sessions</persistent-store-table>
  <http-proxy-caching-of-cookies>true</http-proxy-caching-of-cookies>
  </session-descriptor>
    <container-descriptor>
      <servlet-reload-check-secs>-1</servlet-reload-check-secs>
      <session-monitoring-enabled>true</session-monitoring-enabled>
  </container-descriptor>
  <context-root>/</context-root>
</weblogic-web-app>

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