Documentation

• 1: Introduction to SWS
• 1.1: Introduction to SWS
• 1.2: Your First SWS Service
• 1.3: Which SWS Method Should I Use?
• 1.4: What is HTTP and REST
• 1.5: Recommended Tools
• 1.6: SWS Alternatives
• 2: Installing SWS
• 2.1: Installing SWS
• 2.2: Patching SWS
• 2.3: Release Notes
• 3: SWS Configuration
• 3.1: Configuration 🌟
• 3.2: SWS Security Setup
• 3.3: SWS Caching
• 3.4:
• 4: PeopleSoft Query Language (PsoftQL)
• 4.1: Service Operation CHG_SWS_PSOFTQL
• 4.2: PsoftQL Syntax 🌟
• 4.3: Campus Solutions PsoftQL - Examples
• 4.4: PsoftQL Operators & Filtering Examples
• 4.5: PeopleTools PsoftQL - Examples
• 4.6: Aggregate SQL Translation Examples
• 4.7: PsoftQL Validator
• 5: Road Map
• 6: Project Meta
• 6.1: Why was SWS Created?
• 6.2: Terminology
• 6.3: SWS Assumptions and Limitations
• 7: Legal & Licensing
• 7.1: SWS License Agreement
• 7.2: SWS Self-Audit Procedure
• 7.3: SWS Copyright Notice
• 8: About the Project
• 9: Purchase SWS

1 - Introduction to SWS

1.1 - Introduction to SWS

SWS is a PeopleSoft module that understands your metadata and turns PeopleSoft data into clean REST APIs, ready for AI, modern applications, and partner integrations. Deploy production web services in under 5 minutes through configuration alone.

Welcome to the SWS product created by Cedar Hills Group, Inc.

• SWS is a PeopleSoft extension delivered as a standard Application Designer project that allows you to create web services with configuration only. We assume you read about it on the SWS Product Page.
• Check out the Your First SWS Service to see how fast you can create web services with configuration only.
• You should first read about SWS Concepts to get an understanding of how to think about SWS configuration and requests.
• Then you need to get SWS installed using the installation documentation
• Then read about the detailed SWS Configuration Options
• Not sure why you should use web services? I would check out the Why Use Web Services? chapter of our book.

When you install the SWS from Cedar Hills Group, it has two PeopleSoft web services that have very different use cases and audiences.

• SWS Get Services - You will use this method 95% of the time.
• PsoftQL Web Services
  • This is a more advanced method to extract PeopleSoft data for a system integrator or outside vendor.
  • You generally will NOT open up this web service to many users. This is geared toward internal integration tools like Mulesoft, Snaplogic, and Boomi.

Not sure which one fits your situation? See Which SWS Method Should I Use? for a visual decision flowchart covering both services and the SQL-vs-PsoftQL sub-choice inside a config.

SWS Get Services

The easiest and most secure way to expose your PeopleSoft data is by using SWS-configured web services. This method gives the PeopleSoft admin control over what data is exposed to third parties and is a more secure model for most use cases. You can read about how to set up a new web service in the SWS Configuration Section.

• A PeopleSoft power user
  • Configures a new SWS configuration with either SQL or PsoftQL Syntax.
  • Defines parameters and filters
  • Grants security
  • Give URL and Authentication information to the integration partner.

The integration partner ends up using a REST GET Service Operation called CHG_SWS. This web service checks security, resolves parameters, executes the SQL, encodes the data and returns it to the user.

• SWS Get Services - Standard HTTP Clients - Service Operation: CHG_SWS
  • Most of the web services you configure with SWS will be set up to be used by non-trusted users.
  • This is the default way to configure SWS services.
  • You can start reading about how to set up a new web service in the SWS Configuration Section

(Advanced) PsoftQL Web Services

This is a more advanced method for a system integrator. You generally will NOT open up this web service to many users.

• PsoftQL Web Services - Service Operation: CHG_SWS_PSOFTQL
  • This web service allows a highly trusted integration platform to request data in PsoftQL Syntax
  • This is for internal integration tools like Mulesoft, Snaplogic and Boomi.
  • When developing and prototyping new SWS configurations, your PeopleSoft admins may use this method to execute PsoftQL against PeopleSoft before configuring it in the most secure and locked-down SWS Get Service.

1.2 - Your First SWS Service

SWS combines two products. One product is advanced and caters to a specific group of users. The other has a broader application. In our first example, we’ll concentrate on the simplest method to create a web service. This involves crafting a SQL statement that can be exposed as a web service. This documentation will focus on the other areas of SWS. In this first example, we will focus on a quick win to expose a list of users and their emails.

Define Our Use Case

• In this use case, the objective is to provide a list of users with their names, employee IDs, and primary email addresses to an external system.
• Let’s further assume that the external system is going to have several other data requests that will be custom to this system/vendor. Therefore, we will “scope” our APIs to the vendor system in the URL path which will make more sense as we progress through this example. We will use “acmecorp” as the vendor name in our URL path.
• We also know from the vendor that they need specific field names in the output as they cannot handle data translation. Therefore, we will need to rename the fields in our output.

Create a SQL Statement

The first step is to create a SQL statement that will return the data we need. In this case, we will use the following SQL statement. This was generated by PS Query and copied out but you can do what magic SQL you want.

SELECT B.EMPLID, B.NAME, A.EMAIL_ADDR
  FROM PS_EMAIL_ADDRESSES A, PS_PERSON_NAME B
  WHERE  A.PREF_EMAIL_FLAG = 'Y'
     AND A.EMPLID = B.EMPLID 

Create the SWS Service

• Navigate to: CHG Custom -> Simple Web Service Setup
• Use “Add Mode” to add a new value.
• Unique Identifier: This is auto-generated by SAVE.
• Description & Notes: Any Metadata you want to add to the service.
• URL Path: This is the URL path that will be used to access the service. In this case, we will use acmecorp as the vendor name in our URL path. We further added /people/email to have a full path of /acmecorp/people/email. This will be in the URL that the vendor will use to access the service.
• Request Type: SQL
• SQL Statement: The SQL statement we created above.
• Output Fields: This is where we will rename the fields to match the vendor’s requirements. We will rename the fields to UserID, LegalName, and PrimaryEmail. These fields were communicated by the vendor.

• The second half of the page contains some miscellaneous options that we will not cover in this example. We will cover these in other examples. Leave the defaults.

• Parameters: The parameters section does not need any values as the vendor will NOT pass in any parameters to filter or limit the data for this simple example.

• Allowed Permission Lists: This is important and defines how users can access the system. We cover this in detail in the Security section. We granted the web service to our admin permission list and a new permission list we created just for ACME. We created a user to represent the ACME Corp server and the security structure looks like this:

  • OPRID: ACMECORP_SWS_USER
    • Password: phoebe-PROBABLE-dallas
    • Role: ACMECORP_SWS_USER
      • Permission List: ACMECORP_SWS_USER
        • Web Service: CHG_SWS.CHG_SWG_GET

We can go to the last table and get the URL to test the service. We will use the URL in the next section. We give you HTTP and curl syntax.

I like HTTP Syntax as I think it is very clear. Let’s create our base64 encoded user name and password (Postman can do this for you) and ask for JSON accept: application/json. We will see if a response comes back. I am removing some of the data because we did not have a row limit specified and we have not implemented pagination which is possible but those are more advanced topics to cover later.

GET http://psft-cs-858.c.peoplesoftdemo-1470578694381.internal:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/acmecorp/people/email
accept: application/json
authorization: Basic QUNNRUNPUlBfU1dTX1VTRVI6cGhvZWJlLVBST0JBQkxFLWRhbGxhcw==


HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-type: application/json; encoding=UTF-8
date: Thu, 21 Sep 2023 01:03:27 GMT
transfer-encoding: chunked
x-oracle-dms-ecid: 8458013e-3913-40bb-9698-190771ec7d9a-0000001f
x-oracle-dms-rid: 0
x-peoplesoftrequestid: ad31d07e-581a-11ee-a26c-7f8dcd811d6d
x-success: True

{
  "data": [
    {
      "UserID": "FAI036",
      "LegalName": "Shard Yahq",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FAEQT0001",
      "LegalName": "Sandra Falix",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FAEQT0003",
      "LegalName": "Ortega Pariara",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FAEQT0008",
      "LegalName": "Rinda Marnar",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FAEQT0012",
      "LegalName": "Judith Alkins",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FASS0150",
      "LegalName": "Timothy Rond",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FASS0153",
      "LegalName": "Tim Rihela",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FASS0154",
      "LegalName": "Tona Ricado",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    },
    {
      "UserID": "FASSFAN001",
      "LegalName": "Apple Fan",
      "PrimaryEmail": "HCMGENUser1@ap6023fems.us.oracle.com"
    }
  ],
  "errors": "",
  "meta": {
    "rowCount": "4011",
    "sqlIDExecuted": "d7fb7404-b1dc-4acd-b062-6d9f529f9b00",
    "success": "True",
    "debugMessages": "",
    "QueryString": "NULL",
    "URLPath": "acmecorp/people/email",
    "finalSQL": "SELECT B.EMPLID, B.NAME, A.EMAIL_ADDR\n  FROM PS_EMAIL_ADDRESSES A, PS_PERSON_NAME B\n  WHERE  A.PREF_EMAIL_FLAG = 'Y'\n     AND A.EMPLID = B.EMPLID",
    "productVersion": "2023-06-07",
    "toolsVer": "8.58.07",
    "currentUser": "ACMECORP_SWS_USER",
    "responseDTTM": "2023-09-21 01:03:29.000000",
    "psftTransactionId": "ad31d07e-581a-11ee-a26c-7f8dcd811d6d",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "ServerDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessage": ""
  }
}

If you go back and look at the setup in the miscellaneous section we asked for metadata to be generated and we get back this JSON object which provides very helpful information for debugging.

"meta": {
    "rowCount": "4011",
    "sqlIDExecuted": "d7fb7404-b1dc-4acd-b062-6d9f529f9b00",
    "success": "True",
    "debugMessages": "",
    "QueryString": "NULL",
    "URLPath": "acmecorp/people/email",
    "finalSQL": "SELECT B.EMPLID, B.NAME, A.EMAIL_ADDR\n  FROM PS_EMAIL_ADDRESSES A, PS_PERSON_NAME B\n  WHERE  A.PREF_EMAIL_FLAG = 'Y'\n     AND A.EMPLID = B.EMPLID",
    "productVersion": "2023-06-07",
    "toolsVer": "8.58.07",
    "currentUser": "ACMECORP_SWS_USER",
    "responseDTTM": "2023-09-21 01:03:29.000000",
    "psftTransactionId": "ad31d07e-581a-11ee-a26c-7f8dcd811d6d",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "ServerDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessage": ""
  }

Do you want XML instead? Simply change the accept header.

GET http://psft-cs-858.c.peoplesoftdemo-1470578694381.internal:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/acmecorp/people/email
accept-encoding: gzip, deflate, br
accept: application/xml
authorization: Basic QUNNRUNPUlBfU1dTX1VTRVI6cGhvZWJlLVBST0JBQkxFLWRhbGxhcw==
user-agent: httpyac

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-type: application/xml; encoding=UTF-8
date: Thu, 21 Sep 2023 01:16:11 GMT
transfer-encoding: chunked
x-oracle-dms-ecid: 8458013e-3913-40bb-9698-190771ec7d9a-00000020
x-oracle-dms-rid: 0
x-peoplesoftrequestid: 7343166f-581c-11ee-a26c-7f8dcd811d6d
x-success: True
<?xml version="1.0"?>
<response>
  <data>
    <row>
      <UserID><![CDATA[FAI036]]></UserID>
      <LegalName><![CDATA[Shard Yahq]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FAEQT0001]]></UserID>
      <LegalName><![CDATA[Sandra Falix]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FAEQT0003]]></UserID>
      <LegalName><![CDATA[Ortega Pariara]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FAEQT0008]]></UserID>
      <LegalName><![CDATA[Rinda Marnar]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FAEQT0012]]></UserID>
      <LegalName><![CDATA[Judith Alkins]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>
    <row>
      <UserID><![CDATA[FASS0150]]></UserID>
      <LegalName><![CDATA[Timothy Rond]]></LegalName>
      <PrimaryEmail><![CDATA[HCMGENUser1@ap6023fems.us.oracle.com]]></PrimaryEmail>
    </row>

  </data>
  <errors></errors>
  <meta>
    <rowCount>4011</rowCount>
    <sqlIDExecuted>d7fb7404-b1dc-4acd-b062-6d9f529f9b00</sqlIDExecuted>
    <peopleSoftRequestID>7343166f-581c-11ee-a26c-7f8dcd811d6d</peopleSoftRequestID>
    <success>True</success>
    <dbName>CS92U020</dbName>
    <QueryString><![CDATA[NULL]]></QueryString>
    <URLPath><![CDATA[acmecorp/people/email]]></URLPath>
    <finalSQL><![CDATA[SELECT B.EMPLID, B.NAME, A.EMAIL_ADDR
  FROM PS_EMAIL_ADDRESSES A, PS_PERSON_NAME B
  WHERE  A.PREF_EMAIL_FLAG = 'Y'
     AND A.EMPLID = B.EMPLID]]></finalSQL>
    <productVersion>2023-06-07</productVersion>
    <peopleSoftUser>ACMECORP_SWS_USER</peopleSoftUser>
    <peopleToolsVersion>8.58.07</peopleToolsVersion>
    <requestTime>2023-09-21-01.16.11.000000</requestTime>
    <debugMessage><![CDATA[]]></debugMessage>
  </meta>
</response>

Or how about CSV? Just change the accept header. You will notice with CSV any metadata is in the header and the data is in the body. This is a common pattern with CSV.

GET http://psft-cs-858.c.peoplesoftdemo-1470578694381.internal:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/acmecorp/people/email
accept-encoding: gzip, deflate, br
accept: text/csv
authorization: Basic QUNNRUNPUlBfU1dTX1VTRVI6cGhvZWJlLVBST0JBQkxFLWRhbGxhcw==
user-agent: httpyac

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-type: text/csv; encoding=UTF-8
date: Thu, 21 Sep 2023 01:18:36 GMT
transfer-encoding: chunked
x-dbname: CS92U020
x-errors: 
x-peoplesoftrequestid: ca315226-581c-11ee-a26c-7f8dcd811d6d
x-peoplesoftuser: ACMECORP_SWS_USER
x-peopletoolsversion: 8.58.07
x-productversion: 2023-06-07
x-requesttime: 2023-09-21-01.18.37.000000
x-rowcount: 4011
x-sqlidexecuted: d7fb7404-b1dc-4acd-b062-6d9f529f9b00
x-success: True
x-urlpath: acmecorp/people/email

"UserID","LegalName","PrimaryEmail"
"FAI036","Shard Yahq","HCMGENUser1@ap6023fems.us.oracle.com"
"FAEQT0001","Sandra Falix","HCMGENUser1@ap6023fems.us.oracle.com"
"FAEQT0003","Ortega Pariara","HCMGENUser1@ap6023fems.us.oracle.com"
"FAEQT0008","Rinda Marnar","HCMGENUser1@ap6023fems.us.oracle.com"
"FAEQT0012","Judith Alkins","HCMGENUser1@ap6023fems.us.oracle.com"
"FASS0150","Timothy Rond","HCMGENUser1@ap6023fems.us.oracle.com"
"FASS0153","Tim Rihela","HCMGENUser1@ap6023fems.us.oracle.com"
"FASS0154","Tona Ricado","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN001","Apple Fan","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN002","Honey Fan","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN003","Cookie Fan","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN004","Gavin Kravitz","HCMGENUser1@ap6023fems.us.oracle.com"
"FASSFAN005","KATHERINE HURADO","HCMGENUser1@ap6023fems.us.oracle.com

Summary

In this example, we created a simple web service that returned a list of users and their email addresses. We used a SQL statement to return the data and we renamed the fields to match the vendor’s requirements. We also showed how to test the service using HTTP and curl. We also showed how to get metadata back from the service to help with debugging. We also showed how to get the service to return XML and CSV.

This is a production-ready web service that was created in under 5 minutes. (Not counting the security setup.)

1.3 - Which SWS Method Should I Use?

SWS ships with two PeopleSoft service operations and one important sub-choice inside the config-based service. This page is a visual, one-page answer to “which one do I use?” — pick the path at the bottom of the flowchart, then follow the link to the detail page for that path.

Most admins will land on CHG_SWS (config-based) with PsoftQL syntax — that is the 95% answer. The diagram below confirms when to reach for the other two options instead.

Decision Flowchart

flowchart TD
    start([Need to expose PeopleSoft data via REST]) --> q1{Is the caller a highly<br/>trusted internal<br/>integration platform?<br/><br/>e.g. MuleSoft,<br/>Snaplogic, Boomi}
    q1 -->|Yes, and they can craft PsoftQL themselves| psoftqlws[CHG_SWS_PSOFTQL<br/><br/>client sends PsoftQL<br/>in the POST body<br/><br/>advanced · high-trust only]:::advanced
    q1 -->|No — third-party,<br/>standard REST client,<br/>or locked-down use case| config[CHG_SWS<br/><br/>admin-configured GET service<br/>the 95% path]:::standard
    config --> q2{What syntax inside<br/>the config?}
    q2 -->|Need CSV output, or<br/>SQL-function data translation,<br/>or flat rowset data| sql[SQL statement]:::sql
    q2 -->|Need nested parent/child data,<br/>pagination, auto EFFDT /<br/>EFF_STATUS / EFFSEQ,<br/>or all fields auto-exported| psoftql[PsoftQL statement<br/><br/>the default inside most configs]:::psoftql

    classDef advanced fill:#fde2e2,stroke:#c0392b,stroke-width:2px,color:#222
    classDef standard fill:#dff0d8,stroke:#3c763d,stroke-width:2px,color:#222
    classDef sql fill:#e7f3fb,stroke:#289dd0,stroke-width:2px,color:#222
    classDef psoftql fill:#d9f3f3,stroke:#5ac5c5,stroke-width:2px,color:#222

Path 1 — CHG_SWS_PSOFTQL (Advanced)

This is the advanced service. The client POSTs a PsoftQL request body directly to PeopleSoft, and SWS returns structured JSON or XML that mirrors the PeopleSoft record layout. It is powerful — the caller can request any whitelisted table at will — but that power lives with the client, not with your admin configuration.

Only use CHG_SWS_PSOFTQL when the caller is a highly trusted internal integration platform such as MuleSoft, Snaplogic, or Boomi, and your team is comfortable that the caller can craft PsoftQL correctly. It is also useful while prototyping new SWS configurations. See Service Operation CHG_SWS_PSOFTQL for request/response details and the security model.

Path 2 — CHG_SWS (Config-Based GET)

This is the default path and what 95% of your web services will use. A PeopleSoft power-user configures the URL path, parameters, security grid, and the statement that runs — the client just calls a normal REST GET. All the PeopleSoft knowledge lives in the configuration row, not on the client.

Use CHG_SWS any time the caller is a third party, a standard REST client, or any integration where you want the admin (not the client) to define what data is exposed and how. See the SWS Configuration page for the full configuration walkthrough.

Sub-Decision — SQL or PsoftQL Inside the Config?

Inside a CHG_SWS configuration you choose one syntax type. Here is the short version of the trade-off:

Choose SQL when you need:

• CSV output (PsoftQL does not produce CSV)
• Data translation via SQL functions, joins across records, or aliases
• Flat, row-shaped results rather than nested parent/child trees
• Full hand-control over the WHERE clause, joins, and effective-date logic

Choose PsoftQL when you want:

• Nested parent/child data (one person, many phones, many addresses) in a single response
• Pagination out of the box
• Automatic EFFDT, EFF_STATUS, and EFFSEQ handling — no SQL required
• All record fields auto-exported without hard-coding the select list

For the full, authoritative feature comparison (JSON/XML/CSV support, pagination, effective-date handling, etc.) see the Syntax Types table in the SWS Configuration page.

At a Glance

1.4 - What is HTTP and REST

HTTP Quick Start

This is a quick and succinct background on HTTP and REST concepts. We provide this here to give readers who are new to the Web Service concept a short introduction to HTTP syntax and REST concepts, as this documentation assumes that you have a basic understanding of the terminology and concept. If you want to understand HTTP, see the recommended tools section for some recommendations.

In the world of web development, the Hypertext Transfer Protocol (HTTP) serves as the foundation for communication between web browsers and servers. HTTP enables the exchange of information, such as web pages, images, and data. In this article, we will explore the basic concepts of HTTP, including requests, responses, and the syntax used to send and receive information between clients and servers.

HTTP Requests

When you type a website URL into your web browser’s address bar and hit enter, your browser sends an HTTP request to the server hosting that website. HTTP requests are made up of several components:

1. Request Method: The request method specifies the type of action the client wants to perform on the server. The two most common methods are:

   • GET: This method is used to retrieve data from the server. It is typically used when you visit a website or fetch information.
   • POST: This method is used to send data to the server. It is commonly used when you submit a form or upload a file.

2. URL/URI: The Uniform Resource Locator (URL) or Uniform Resource Identifier (URI) identifies the specific resource the client wants to access. It typically includes the domain name and the path to the resource on the server.

3. Headers: Headers contain additional information about the request, such as the type of content being sent, accepted content types, authentication credentials, and more. Headers provide crucial context to the server and allow the client and server to understand each other’s capabilities and requirements.

4. Parameters: Parameters are additional pieces of data that can be included with an HTTP request. They provide specific instructions to the server or help filter and narrow down the requested data. Parameters are often used in GET requests as query parameters, allowing the client to pass data in the URL.

HTTP Responses

When the server receives an HTTP request, it processes the request and sends back an HTTP response. An HTTP response consists of the following components:

1. Status Code: The status code indicates the outcome of the server’s attempt to fulfill the request. Some common status codes include:

   • 200 OK: The request was successful, and the server is returning the requested data.
   • 404 Not Found: The requested resource could not be found on the server.
   • 500 Internal Server Error: An unexpected error occurred on the server.

2. Headers: Similar to request headers, response headers provide additional information about the response, such as the content type, cache-control directives, cookies, and more.

3. Response Body: The response body contains the actual data or content requested by the client. For example, if the client requested a web page, the response body would contain the HTML content of that page.

Syntax and Examples

HTTP requests and responses follow a specific syntax. Here are examples of GET and POST requests:

• GET Request:

GET /api/products?id=123 HTTP/1.1
Host: api.example.com
Accept: application/json

In the above example, the client is making a GET request to the server’s /api/products endpoint, passing the id parameter with a value of 123. The request also includes the Host header, specifying the server’s domain, and the Accept header, indicating that the client prefers a response in JSON format.

• POST Request:

POST /api/users HTTP/1.1
Host: api.example.com
Content-Type: application/json

{
  "name": "John Doe",
  "email": "john@example.com"
}

In this example, the client is making a POST request to the server’s `/api

Understanding REST APIs and Parameter Passing with HTTP

Within the realm of HTTP, a powerful architectural style called REST (Representational State Transfer) has emerged as a standard for designing web APIs. SWS uses REST concepts to provide GET web services. Let’s quickly introduce a few concepts.

What is REST?

REST is an architectural style that provides a set of guidelines for designing networked applications. It emphasizes simplicity, scalability, and interoperability between systems. RESTful APIs (Application Programming Interfaces) enable different software applications to communicate with each other by leveraging the existing HTTP protocol.

In a RESTful architecture, resources (such as data or services) are identified by unique URLs called URIs (Uniform Resource Identifiers). These resources can be accessed and manipulated using standard HTTP methods like GET, POST, PUT, DELETE, etc. By following the principles of REST, developers can create APIs that are easy to understand, consume, and extend.

Understanding Parameters

In the context of REST APIs, parameters play a crucial role in specifying additional information for requests and responses. They allow clients (the applications making the requests) and servers (the applications serving the requests) to exchange data and perform specific actions.

Parameters can be classified into two main types: query parameters and path parameters.

1. Query Parameters

Query parameters are used to filter, sort, or paginate data. They are appended to the end of a URL after a question mark ? and separated by ampersands &. For example:

GET https://api.example.com/products?category=electronics&sort=price&limit=10

In the above example, the query parameters are category=electronics, sort=price, and limit=10. These parameters provide additional instructions to the server, such as retrieving only electronic products, sorting them by price, and limiting the response to 10 items.

2. Path Parameters

Path parameters are used to identify a specific resource within a URL path. They are denoted by a placeholder surrounded by curly braces ‘{}’. For example:

GET https://api.example.com/products/{id}

In the above example, the {id} placeholder represents a path parameter that would be replaced with an actual value when making a request. For instance, to retrieve the details of a product with an ID of 123, the URL would be:

GET https://api.example.com/products/123

Path parameters are useful when you want to interact with a specific resource or perform operations on it, such as updating or deleting it.

Conclusion

REST APIs have become the de facto standard for building web services due to their simplicity, scalability, and compatibility with the HTTP protocol. Understanding how parameters are passed using HTTP is essential for effectively working with RESTful APIs. By utilizing query parameters and path parameters, clients can provide additional instructions to the server and interact with specific resources. This empowers developers to build flexible and powerful applications that can seamlessly communicate with other services over the web.

1.5 - Recommended Tools

When working with SWS to configure new web services you will want some simple tools on your machine that you can quickly test new services. Having your own local tools will speed up your development and troubleshooting time significantly.

The tools we recommend to use are:

• Visual Studio Code - The only text editor you should be using!
  • A great text editor with a huge list of plugins for editing and working with XML, JSON, encodings, etc.
  • I really like the HTTPYac extension. It can replace Postman in many situations. It has some pretty good documentation.
    • The key benefit here is that your HTTP request syntax is in local text files and you can easily check them into Git or do other types of backups.
    • Chris uses this combination of tools to do 98% of development, troubleshooting and testing and is likely all you need.
• Use Hurl.dev for Automated API testing.
  • You need to understand HTTP to use this tool and the learning curve can be steep. However, if you want to set up a simple automated HTTP test, I highly recommend learning it.
  • Hurl is used in the development of SWS!
• Postman
  • This is the default choice for most people. However, I have found that it has gotten too bloated with features for what I use it for. However, it is a great product!
• hoppscotch.io is a good alternative to Postman.
• curl is also a tried and true tool for the techies.

Understanding HTTP Syntax

If you don’t have a firm understanding of HTTP concepts, I highly recommend that you read this concise book.

• What Every Web Developer Should Know About HTTP

We will be documenting most HTTP calls in HTTP syntax in this documentation so you must have a general understanding of HTTP. If you prefer a more visual instruction I suggest you Search YouTube for HTTP introductions

1.6 - SWS Alternatives

You might be reading this page because you are trying to decide if SWS is right for your organization. Let’s look at what you would do if you did not have SWS. There are a few alternatives to our SWS solution. Let’s see how they compare.

Bespoke Web Services

The main alternative to SWS is to create a bespoke web service for each client or vendor and each piece of data. This is the traditional way of doing things. It is expensive and time-consuming. It is also very inflexible.

If we take the PeopleSoft Campus module as an example, a software vendor may need to integrate with Student Enrollment, Configuration data like Terms and other Academic Setup data, Student Bio data, Student Grades, and Invoices. This is a very common integration pattern.

With each new vendor, a custom bespoke web service may need to be created. You often may have very similar web services returning mostly the same data but with some slight variations.

SWS can run in any PeopleSoft database, but our example here is focused on Campus Solutions.

left to right direction
skinparam sequenceArrowThickness 3
Title: The SWS Alternative! Web Service Sprawl!

Package "PeopleSoft" {


  rectangle "Terms" as rec.terms #FFD3B0
  rectangle "Student Bio data" as rec.students #FFD3B0
  rectangle "Student Grades" as rec.grades #FFD3B0
  rectangle "Student Enrollment" as rec.se #FFD3B0
  rectangle "Invoices" as rec.invoices #FFD3B0

  ' package "Bespoke Web Services" {
  rectangle "Terms\nWeb Service" as ib.rec.terms #FF6969
  rectangle "Student Bio data\nWeb Service" as ib.rec.students #FF6969 
  rectangle "Student Bio data\nWeb Service\nVersion 2" as ib.rec.students2 #FF6969 
  rectangle "Student Grades\nWeb Service" as ib.rec.grades #FF6969
  rectangle "Student Enrollment\nWeb Service" as ib.rec.se #FF6969
  rectangle "Student Enrollment\nWeb Service \nVersion 2" as ib.rec.se2 #FF6965
  rectangle "Invoices\nWeb Service" as ib.rec.invoices #FF6969
' }

  ib.rec.terms <-- rec.terms
  ib.rec.students <-- rec.students
  ib.rec.students2 <-- rec.students
  ib.rec.grades <-- rec.grades
  ib.rec.se <-- rec.se
  ib.rec.se2 <-- rec.se

  ib.rec.invoices <-- rec.invoices

  ' rectangle "SWS - Web Service\n\n(Single Web Service)\nReplaces all " as ib.sws #limeGreen
  ' ib.sws -[#limeGreen,thickness=3,dashed]-> rec.terms
  ' ib.sws -[#limeGreen,thickness=3,dashed]-> rec.students
  ' ib.sws -[#limeGreen,thickness=3,dashed]-> rec.grades
  ' ib.sws -[#limeGreen,thickness=3,dashed]-> rec.se
  ' ib.sws -[#limeGreen,thickness=3,dashed]-> rec.invoices


}


rectangle "LMS Integration Partner" as client #A6D0DD
rectangle "Student Success\nIntegration Partner" as client2 #A6D0DD
rectangle "Payment\nIntegration Partner" as client3 #A6D0DD

client <-- ib.rec.terms: GET
client <-- ib.rec.students: GET
client <-- ib.rec.grades: GET
client <-- ib.rec.se: GET


client2 <-- ib.rec.students2: GET
client2 <-- ib.rec.grades: GET
client2 <-- ib.rec.se2: GET

client3 <-- ib.rec.invoices: GET

' client -[#limeGreen,thickness=3,dashed]-> ib.sws
' client2 -[#limeGreen,thickness=3,dashed]-> ib.sws
' client3 -[#limeGreen,thickness=3,dashed]-> ib.sws

If you are a Software vendor trying to integrate with a PeopleSoft customer then you might develop 5 unique web services for the five unique areas you are trying to integrate with if you are NOT using SWS.

• You have a PeopleSoft application with 5 different web services.
• Each web service is designed for a specific vendor or client.
• Each client may have customizations that are hard to support in the traditional approach without access to the client’s PeopleSoft development instances or support from the client’s PeopleSoft development team.
• Each client may have different data filtering requirements.
• Code patches can take months to deploy to all clients and some clients may not be able to take the patch at all or they don’t have the resources to deploy the patch.

We have developed vendor solutions similarly and this is the reason we developed SWS. We wanted to make it easier for vendors to integrate with PeopleSoft, and we wanted to make it easier for PeopleSoft customers to integrate with third-party vendors. You can read more in the SWS origin story

What is the cost of this approach?

You can ask a developer to create a new web service for each user integration requirement. If a vendor commissioned the PeopleSoft application team to develop a new web service, the following process would likely be followed. This assumes you are a medium to large organization with a formal development process.

After years of developing web services for PeopleSoft customers, we have a good idea of the cost of developing a new web service and even wrote a book about the topic.

• If you value your developer’s time at roughly $100 an hour. Then the cost to develop a single new web service is somewhere between $3,600 and $13,000.
• With SWS, that amount can be drastically reduced. In some cases, it can take just a few minutes to create a new web service and have it deployed in production.

Query Access Web Services

We have a KB article called Reporting Web Services: Using the REST Web Services to run a Query which demonstrates how you can make a “web service” out of a PeopleSoft query manager query. This can be an effective tool to generate web services.

However, the SWS offers many advantages over this delivered functionality.

• You have better control over the field names in the output.
• SWS offers more output encoding types
• The PeopleSoft query tool imposes unneeded complexity with data security and query tree security that often just gets in the way.
• SWS supports more advanced SQL and you are not forced into the SQL generated by query manager.

How does SWS Make it better?

• SWS is a single web service that can be used for most integrations.
• SWS is a single web service that can be used for all clients.
• SWS is a single web service that can be used for all vendors.
• SWS is a single web service that can be used for all data.
• SWS provides a consistent configuration framework for all clients and vendors.

left to right direction
skinparam sequenceArrowThickness 3
Title: The SWS Alternative! Web Service Sprawl!

Package "PeopleSoft" {


  rectangle "Terms" as rec.terms #FFD3B0
  rectangle "Student Bio data" as rec.students #FFD3B0
  rectangle "Student Grades" as rec.grades #FFD3B0
  rectangle "Student Enrollment" as rec.se #FFD3B0
  rectangle "Invoices" as rec.invoices #FFD3B0

  ' package "Bespoke Web Services" {
  ' rectangle "Terms\nWeb Service" as ib.rec.terms #FF6969
  ' rectangle "Student Bio data\nWeb Service" as ib.rec.students #FF6969 
  ' rectangle "Student Bio data\nWeb Service\nVersion 2" as ib.rec.students2 #FF6969 
  ' rectangle "Student Grades\nWeb Service" as ib.rec.grades #FF6969
  ' rectangle "Student Enrollment\nWeb Service" as ib.rec.se #FF6969
  ' rectangle "Student Enrollment\nWeb Service \nVersion 2" as ib.rec.se2 #FF6965
  ' rectangle "Invoices\nWeb Service" as ib.rec.invoices #FF6969
' }

  ' ib.rec.terms <-- rec.terms
  ' ib.rec.students <-- rec.students
  ' ib.rec.students2 <-- rec.students
  ' ib.rec.grades <-- rec.grades
  ' ib.rec.se <-- rec.se
  ' ib.rec.se2 <-- rec.se
  ' ib.rec.invoices <-- rec.invoices

  rectangle "SWS - Web Service\n\n*Single Web Service\n*Configuration Based\n* No Hard Coding" as ib.sws #limeGreen
  ib.sws <-[#limeGreen,thickness=3,dashed]- rec.terms
  ib.sws <-[#limeGreen,thickness=3,dashed]- rec.students
  ib.sws <-[#limeGreen,thickness=3,dashed]- rec.grades
  ib.sws <-[#limeGreen,thickness=3,dashed]- rec.se
  ib.sws <-[#limeGreen,thickness=3,dashed]- rec.invoices


}


rectangle "LMS Integration Partner" as client #A6D0DD
rectangle "Student Success\nIntegration Partner" as client2 #A6D0DD
rectangle "Payment\nIntegration Partner" as client3 #A6D0DD

' client <-- ib.rec.terms
' client <-- ib.rec.students
' client <-- ib.rec.grades
' client <-- ib.rec.se


' client2 <-- ib.rec.students2
' client2 <-- ib.rec.grades
' client2 <-- ib.rec.se2

' client3 <-- ib.rec.invoices

client <-[#limeGreen,thickness=3,dashed]- ib.sws: GET
client2 <-[#limeGreen,thickness=3,dashed]- ib.sws: GET
client3 <-[#limeGreen,thickness=3,dashed]- ib.sws: GET

2 - Installing SWS

This section will describe how to install SWS in your PeopleSoft instance.

2.1 - Installing SWS

General Notes

Most PeopleSoft implementations have a migration path starting in a dev environment that flows to production. Every organization does this slightly differently. For this document, we assume you have a development flow that looks like the following.

1. Development
2. Test
3. Production

We recommend starting in a development environment first. Then, follow your normal production migration procedure to push this to your production systems.

Import Project from File

When importing into your development environment, you will import the delivered project from a file. For subsequent databases like test and production, you should use the standard PeopleSoft migration using “Project Copy to Database.”

• Login to your development instance using application designer.
• Import the project from the file.
  • Inspect the upgrade windows to ensure there are no errors or warnings.

• Application Designer:
  • Tools -> Copy Project -> From File
  • Select the project file. Always use the latest dated project file.

• Note that “Compile PeopleCode after Import” is checked. You can also compile the project PeopleCode after the import. This is a good check to ensure that all objects imported correctly. There are times when the application can crash or skip things. Make sure the compile does NOT show any errors.

• Next build the new tables
  • Make sure the Application Designer “Project Output window” does NOT show any errors.

Import Setup Data

The product comes with some configuration data to give some examples of working web services. These are mostly queries on basic PeopleTools tables that will exist in any PeopleSoft Application (Finance, Campus, HCM, etc). You are not required to import these configurations into your system. However, it is recommended that you install the CHG roles for development to gain a working understanding of the product.

Note: You will need to change the path in this script to match your environment

SET LOG C:\temp\SAMPLE_DATA_IMPORT.LOG;
SET INPUT C:\temp\SWS_EXPORT_config.DAT;
IMPORT C_SWS_CONF_PAR;
IMPORT C_SWS_CONF_PL;
IMPORT C_SWS_CONF_TBL;
IMPORT C_SWS_OUT_FLDS;

Grant Users Access

Now, you need to grant Admin and API users access to the setup pages and API. Please see the Security Setup Section in the User Documentation

Object Naming

The PeopleSoft project that delivers SWS is a 100% PeopleSoft bolt-on. No delivered PeopleSoft objects are modified. All

All PeopleSoft objects delivered with SWS

• Object Prefix: CHG_
  • Exceptions:
    • Record Prefix - C_

List of SWS Installed Objects

Below is the list of objects that will be imported.

• Record: C_PQL_PL_EDIT
• Record: C_SWS_CONF_PAR
• Record: C_SWS_CONF_PL
• Record: C_SWS_CONF_TBL
• Record: C_SWS_OUT_FLDS
• Record: C_SWS_PL_EDIT
• Record: C_SWS_REC_WL
• Record: C_SWS_RUN_LOG
• Record: C_SWS_WORK
• Field: CHG_CACHE_MINUTES
• Field: CHG_COPY_TO
• Field: CHG_DEBUG_MODE
• Field: CHG_DELETE_CONFIRM
• Field: CHG_DE_INCL_META
• Field: CHG_DE_OUT_FLDNAME
• Field: CHG_DE_PATH
• Field: CHG_ENCODING
• Field: CHG_FIELD_NAMES
• Field: CHG_MAX_LENGTH
• Field: CHG_PARAM_DFLT_VAL
• Field: CHG_PARM_DATA_TYPE
• Field: CHG_PARM_NAME
• Field: CHG_PARM_SEQNO
• Field: CHG_PARM_TYPE
• Field: CHG_PSOFT_QL_TEXT
• Field: CHG_REQ_TYPE
• Field: CHG_ROW_COUNT
• Field: CHG_ROW_LIMIT
• Field: CHG_SWS_MISC_GRP
• Field: CHG_SWS_PQL_SETUP
• Field: CHG_SWS_SQL_SETUP
• Field: CHG_WAS_SUCCESS
• Translate: CHG_ENCODING-CSV-1901-01-01
• Translate: CHG_ENCODING-JSON-1901-01-01
• Translate: CHG_ENCODING-XML-1901-01-01
• Translate: CHG_PARM_DATA_TYPE-BOOL-1901-01-01
• Translate: CHG_PARM_DATA_TYPE-INT-1901-01-01
• Translate: CHG_PARM_DATA_TYPE-NUMB-1901-01-01
• Translate: CHG_PARM_DATA_TYPE-STR-1901-01-01
• Translate: CHG_PARM_TYPE-H-1901-01-01
• Translate: CHG_PARM_TYPE-P-1901-01-01
• Translate: CHG_PARM_TYPE-Q-1901-01-01
• Translate: CHG_REQ_TYPE-P-1901-01-01
• Translate: CHG_REQ_TYPE-S-1901-01-01
• Page: CHG_SWS_ADMIN
• Page: CHG_SWS_CONF_TBL
• Page: CHG_SWS_REC_WL
• Page: CHG_SWS_TEST
• Menu: CHG_TOOLS
• Component: CHG_SWS_CONF_TBL-GBL
• Component: CHG_SWS_REC_WL-GBL
• Record PeopleCode: C_SWS_REC_WL-LASTUPDDTTM-SavePreChange
• Record PeopleCode: C_SWS_WORK-SETUP-FieldFormula
• Role: CHG_SWS_ADMIN
• Role: CHG_SWS_USER
• SQL Object: C_PQL_PL_EDIT-2
• SQL Object: C_SWS_PL_EDIT-2
• Message Definition: CHG_EXPORTER_PARAMS
• Message Definition: CHG_GENERIC
• Message Definition: CHG_SWS_PARAMS
• Message Definition: IB_REST_STUB
• Page PeopleCode: CHG_SWS_TEST-Activate
• Component PeopleCode: CHG_SWS_CONF_TBL-GBL-PostBuild
• Component PeopleCode: CHG_SWS_CONF_TBL-GBL-PreBuild
• Component PeopleCode: CHG_SWS_CONF_TBL-GBL-SavePostChange
• Component PeopleCode: CHG_SWS_CONF_TBL-GBL-SavePreChange
• Component Record PeopleCode: CHG_SWS_CONF_TBL-GBL-C_SWS_CONF_PAR-RowInit
• Component Record PeopleCode: CHG_SWS_CONF_TBL-GBL-C_SWS_CONF_TBL-SearchInit
• Component Record Field PeopleCode: CHG_SWS_CONF_TBL-GBL-C_SWS_CONF_PAR-CHG_PARM_TYPE FieldChange
• Component Record Field PeopleCode: CHG_SWS_CONF_TBL-GBL-C_SWS_CONF_TBL-CHG_DE_PATH FieldChange
• Component Record Field PeopleCode: CHG_SWS_CONF_TBL-GBL-C_SWS_CONF_TBL-CHG_REQ_TYPE FieldChange
• Component Record Field PeopleCode: CHG_SWS_CONF_TBL-GBL-C_SWS_WORK-CHG_COPY_TO FieldChange
• Component Record Field PeopleCode: CHG_SWS_CONF_TBL-GBL-C_SWS_WORK-COPY_PB FieldChange
• Component Record Field PeopleCode: CHG_SWS_CONF_TBL-GBL-C_SWS_WORK-DELETE_PB FieldChange
• HTML Catalog: CHG_SWS_HTTP-4
• Permission List: CHG_SWS_ADMIN
• Permission List: CHG_SWS_USER
• Portal Registry Structure: EMPLOYEE-C-CHG_SWS_CONF_TBL
• Portal Registry Structure: EMPLOYEE-C-CHG_SWS_REC_WL
• Portal Registry Structure: EMPLOYEE-F-CHG_CUSTOM
• URL Definitions: C_SWS_DOC
• Application Package: CHG_CONSTANTS-CHG_CONSTANTS-.
• Application Package: CHG_ENCODING_TOOLS-CHG_ENCODING_TOOLS-.
• Application Package: CHG_HTTP-CHG_HTTP-.
• Application Package: CHG_IB-CHG_IB-.
• Application Package: CHG_SWS-CHG_SWS-.
• Application Package: CHG_UTILS-CHG_UTILS-.
• Application Package: PsoftQL-CHG_SWS-:
• Application Package PeopleCode: CHG_CONSTANTS-http
• Application Package PeopleCode: CHG_ENCODING_TOOLS-JSONHelper
• Application Package PeopleCode: CHG_ENCODING_TOOLS-XMLToPsoft
• Application Package PeopleCode: CHG_ENCODING_TOOLS-constants
• Application Package PeopleCode: CHG_ENCODING_TOOLS-psoftToJSON
• Application Package PeopleCode: CHG_ENCODING_TOOLS-psoftToXML
• Application Package PeopleCode: CHG_HTTP-URLParser
• Application Package PeopleCode: CHG_HTTP-constants
• Application Package PeopleCode: CHG_IB-IBRequest
• Application Package PeopleCode: CHG_IB-IBResponse
• Application Package PeopleCode: CHG_IB-constants
• Application Package PeopleCode: CHG_SWS-PsoftQL-constants
• Application Package PeopleCode: CHG_SWS-PsoftQL-fieldCriteria
• Application Package PeopleCode: CHG_SWS-PsoftQL-request
• Application Package PeopleCode: CHG_SWS-PsoftQL-requestRecord
• Application Package PeopleCode: CHG_SWS-SWSConfig
• Application Package PeopleCode: CHG_SWS-SWSHandler
• Application Package PeopleCode: CHG_SWS-constants
• Application Package PeopleCode: CHG_UTILS-Field
• Application Package PeopleCode: CHG_UTILS-RecordHelper
• Application Package PeopleCode: CHG_UTILS-debugLogger
• Service: CHG_SWS
• Service Operation: CHG_SWS_GET
• Service Operation: CHG_SWS_PSOFTQL_POST
• Service Operation Handler: CHG_SWS_GET-REQUESTHDLR
• Service Operation Handler: CHG_SWS_PSOFTQL_POST-REQUESTHDLR
• Service Operation Version : CHG_SWS_GET-v1
• Service Operation Version : CHG_SWS_PSOFTQL_POST-v1
• Service Operation Routing: GENERATED12273628-1900-01-01
• Service Operation Routing: GENERATED60932339-1900-01-01
• Logical Schema: CHG_SWS-PARAMS-V1
• XML Schema: CHG_SWS-PARAMS-V1-XML
• Document Schema: CHG_SWS-PARAMS-V1
• Json Schema : CHG_SWS-PARAMS-V1-JSON
• Html Schema : CHG_SWS-PARAMS-V1-HTML

2.2 - Patching SWS

Patches for SWS include all previously delivered objects, even if they have not changed. This ensures that you are brought completely up to date, even if your organization missed a patch. We do NOT deliver “delta projects” by design.

Cedar Hills Group completes development in our own sandboxes. We rely heavily on shared libraries that are used across different projects and clients that have their own code releases. The only way to be 100% confident that we deliver high-quality software patches is to deliver a full file with all the objects. This is similar in spirit to how Oracle delivers PeopleSoft patches by delivering a “demo” database that is the source of truth for a particular software PUM. If you need a “delta”/change-only application designer project, we have a procedure below on how to generate that.

The most straightforward way to mark patches is by the date in YYYY-MM-DD format. This represents the date the code was packaged from our development environment. The patch is in the Project comments of the Application Designer project CHG_SWS_V2.

You will be able to download the releases from the private GitHub repository. There will be a zip file resembling the release date like YYYY-MM-DD. You should always get the most recent version available from that list unless you have been directed otherwise by Cedar Hills Group, Inc.

Generate Delta Project

If your change control team needs a “change only” file for production migration of patches, that can easily be generated during the patching installation process. The procedure is below and must be done prior to installing the patch.

• Place the application designer project on a drive where the PeopleTools Application Designer can access it.
• In Application Designer:
  • Tools → Compare and Report → From File
    • If asked about replacing the existing project, choose to replace
  • Let the compare run
  • This will create a project in the source system where the upgrade flags and compare flags on the project will be set to the delta values between what we deliver and what you have in your target database.
  • File → Save Project As: New Delta Project Name per your institutional standards.
  • The project “upgrade” flags will be set with items that have changed. You can delete the objects from the project that have not changed.

If you realize that you need a delta project after already installing the patch in your source database, you can perform a comparison from DEV to TEST or TEST to PROD to determine what objects have changed and delete any unchanged objects from the project.

Patch Installation Instructions

• Download the latest YYYY-MM-DD code archive ZIP file from the New Installation section above.
• Please unzip the file to a location that the Application Designer will be able to access.
• There are two projects included in the attached zip. For patching, you will only import one of these projects.
• In your DEV instances, import Project: CHG_SWS_V2
• If there are special instructions for a patch, they will be listed in the subsection below.

2.3 - Release Notes

2020-01-02

• Version 1.0 release

2023-06-10

• Version 2 Release
  • Major changes in functionality. Merging PsoftQL functionality that was never publicly marketed before with the version 1 SWS into one platform.

2023-09-26

• Small fix for automatic prompt table lookups. If no DESCR field is found, we now look for the first non-key character field on the prompt table that has a non-null value and return that. This supports prompt tables that have no DESCR field but have other fields that can be used as a description.

2024-05-01

• Added some missing code to the project during last build.

2026-01-21

• Add support for basic Aggregate functions in PsoftQL queries.
  • Only One record is supported and no support for Having clause currently.

2026-01-25

• Added support for sorting results in PsoftQL queries.
  • Sort order can be ascending or descending.
  • Multiple sort fields are supported.
• Fixed an issue with joining parent-child fields where the key field is a blank character and adding a “space” since PeopleSoft does NOT allow nulls in character fields.

2026-04-20

• Added pagination support for SQL-type SWS configurations. Clients can now page through large result sets by passing the pageNumber and rowLimit URL query-string parameters. The response meta now includes pageNumber, rowLimit, and (when more rows remain) nextPageNumber. See Pagination for SQL endpoints for details.
  • This brings the SQL handler to parity with the PsoftQL handler on pagination.
  • Existing callers that send neither parameter continue to receive the same response they did before — the change is fully backward compatible.

3 - SWS Configuration

The easiest and most secure way to expose your PeopleSoft data is by using SWS configured web services. This method gives the PeopleSoft admin control over what data is exposed to third parties and is a more secure model for most use cases.

• A PeopleSoft power user:
  • Configures a new SWS configuration with either SQL or PsoftQL Syntax.
  • Defines parameters and filters
  • Grants security
  • Gives URL and Basic Auth Code to the integration partner

The integration partner ends up using a REST GET Service Operation called CHG_SWS. This web service checks security, resolves parameters, executes the SQL, encodes the data and returns it to the user.

Read more in the sections below.

3.1 - Configuration 🌟

At the heart of SWS is a single PeopleSoft Service Operation called CHG_SWS_GET. This single service operation can serve infinite use cases. A PeopleSoft super-user who has read this documentation configures SWS to create new web services using a configuration page delivered as part of SWS. This configuration page lives inside your PeopleSoft database. It is the job of the CHG_SWS_GET service operation to interpret the configuration, enforce security, run SQL and export the results to the client.

This section discusses configuring this service in detail in this section as well as the security model and how to configure API client users.

Planning a new web service

Before configuring a new web service there are a few things to think through. We will cover them here at a high level and then go into detail in the subsequent sections. New web services are trivial to set up with SWS. However, without a little upfront thought, you can end up with some confusion for the users of your APIs.

URLs and Paths

In REST web services, the URL paths have an implied meaning and provide a hierarchy that should map to some logical structure of the underlying data it exposes. This paradigm is carried forward into SWS. The URL path is used to find the logic to run to export data over the web services. So you need to think through the URL path structure and how it will map to the SQL or PsoftQL statements you want to expose.

Thinking about the path hierarchy gives the API clients a good structure to reason about. When reading the paths from left to right, they start out broad and narrow down the request. A URL Path structure could be something like this:

• {prefix}/person/{emplid}
  • This output information about a person and could possibly include some child data like phones, addresses, etc.
• {prefix}/person/{emplid}/phone
  • This would output phones for a person. You might give this out to a team that needs to know phone numbers for a person and are not interested in other data or have security to see that other data.
• {prefix}/person/{emplid}/address/
  • This would output addresses for a person.
• {prefix}/person/{emplid}/address/{address_type}}
  • This would output a specific address type for a person.
• {prefix}/security/users/{oprid}/
  • This would output general information on a specific OPRID.
• {prefix}/security/users/{oprid}/roles/
  • This would output roles a user was a member of.

In those examples above, they are fairly generic paths. You can also have more specific paths that are more targeted to a specific use case. Let’s imagine that you have an integration with your SSO system. You need to expose some data that they need. Their requests do NOT seem to fit into any other categories that you have configured before. Perhaps those other SWS configurations expose too much data or sensitive data the SSO team should not see. They have very limited information and that team cannot use the generic paths above because the data is too broad. So you can create a more specific path for them. Let’s imagine that they need to know information about a person as well as to know if a person is active or not. You could create a path like this:

• {prefix}/sso/person/{emplid}/
  • This may return general information about a person like name, email, etc.
• {prefix}/sso/person/{emplid}/status
  • This may return a simple yes/no if the person is active or not.

In the case above, we “scoped” the SSO end points to the /sso/* path. If you had some other integration partners with very specific requirements then you could create a scoped path as well like /acmecorp/*. You can create as many paths as you need. You can also create paths that are not scoped to a specific integration partner. You can create paths that are scoped to a specific business process. For example, you could create a path for all the data needed for a specific business process like expense reports or invoices.

SWS is flexible. It provides no guidance on how to structure your paths. You can create as many paths as you need.

In SWS, the Path string is used to look up a configuration that ends up running some SQL and exporting the results to the client. So you need to think through the path structure and how it will map to the SQL or PsoftQL statements you want to run. You also configure security to these paths. As we will see shortly, a path maps to a specific SWS configuration row in the database. That configuration row has a security grid that allows you to specify what permission lists are allowed to run that configuration.

Syntax Types

There are two syntax types that SWS supports. When you configure a new web service you must choose one.

• SQL - You can configure a SQL statement to to run.
• PsoftQL - You can configure a PsoftQL statement to run.

When an HTTP client is calling a specific SWS path, the code in the web service handler looks up the configuration for that path. The configuration has a syntax type. The handler will use that syntax type to determine how to export data to the client. There are pros and cons to each syntax type. Here is a quick summary:

Security

Security - Security is very important with PeopleSoft data as the database holds sensitive information. SWS is based on the PeopleSoft REST services. The only viable authentication mechanism for REST based services is “Basic Authentication” which is tied to a PeopleSoft OPRID and password stored in PSOPRDEFN. There is a very thorough document on REST Security in our Integration broker book. That should be your reference on how REST authentication works and the best practices. We assume you have read it and are following the best practices.

There is more explanation of security in the Security Page

Configuring a New Web Service

Configuring a new web service in SWS is easy and fast. You can deploy a new SQL or PsoftQL statement as a new web service in a few minutes. We will walk through all the configuration sections in this section. In later sections, we will show how to create different web services in order to give you some ideas of how this can be used.

Here is a screenshot of the SWS configuration page for a simple person example. Let’s go through each configuration at a high level. Then in later sections, we will drill into the detailed functionality offered.

In the example above we have configured the following PsoftQL statement to run at this path: /doc-example/person/{{emplid}}

{
  "isDebugMode": true,
    "records": [
        {
            "recordName": "PERSON",
            "sqlWhereClause": "  EMPLID = {{emplid}}",
            "excludeFields": [
                "BIRTHDATE",
                "BIRTHPLACE",
                "BIRTHCOUNTRY",
                "BIRTHSTATE",
                "DT_OF_DEATH"
            ]
        },
        {
            "recordName": "NAMES",
            "parentRecordName": "PERSON",
            "includeDescriptionsFor": [
                "NAME_TYPE"
            ],
            "criteriaFields": [
                {
                    "fieldName": "NAME_TYPE",
                    "fieldValue": "PRI"
                }
            ] 
        },
        {
            "recordName": "EMAIL_ADDRESSES",
            "parentRecordName": "PERSON",
            "includeDescriptionsFor": [
                "E_ADDR_TYPE"
            ]
        },
        {
            "recordName": "PERSONAL_PHONE",
            "parentRecordName": "PERSON",
            "includeDescriptionsFor": [
                "PHONE_TYPE"
            ]
        },
        {
            "recordName": "ADDRESSES",
            "parentRecordName": "PERSON",
            "includeDescriptionsFor": [
                "ADDRESS_TYPE"
            ]
        }
    ]
}

Let’s decode this configuration. We are configuring a PsoftQL statement to run. It is asking for this structure.

• PERSON
  • NAMES
  • EMAIL_ADDRESSES
    • SWS will return all email address types since there are no filters
  • PERSONAL_PHONE
    • SWS will return all phone types since there are no filters
  • ADDRESSES
    • SWS will return all effective dated and active addresses since there are no filters. This is SWS auto-magic handling of effective dates and EFF_STATUS
• Additionally, we are asking that we exclude some fields from the PERSON record. We do not want to expose these fields to the client.
  • BIRTHDATE
  • BIRTHPLACE
  • BIRTHCOUNTRY
  • BIRTHSTATE
  • DT_OF_DEATH
• We are also making this a single response for a single EMPLID. We have the {{emplid}} variable in the path. That will be substituted into the PsoftQL statement at run time.
• For NAMES, we are limiting it to only return the PRI name type.
• We are also asking for several description fields to be included in the output. This is a feature of PsoftQL.
• This SWS configuration is limited to a single permission list SWS_DOC_ACCOUNT, which is a service account we setup for this example.

We can then call this web service using an HTTP Request like this:

GET https://127.0.0.1:8000/PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/doc-example/person/FA0003
accept: application/json
authorization: Basic U1dTX0RPQ19BQ0NPVU5UOkRJUkdFLXNwZWxsaW5nLWphZGUtdml0aWF0ZQ==

The response will look like this:

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 1271
content-type: application/json; encoding=UTF-8
date: Mon, 25 Sep 2023 19:00:34 GMT
x-oracle-dms-ecid: 786dc482-39b0-4314-8f25-9a91bc734fdb-0000002a
x-oracle-dms-rid: 0
x-peoplesoftrequestid: ce514ec3-5bd5-11ee-adcb-d5409ab5d8fe
x-success: True

{
  "data": {
    "PERSON": {
      "objectType": "record",
      "objectName": "PERSON",
      "fields": [
        {
          "rowNumber": 1,
          "EMPLID": "FA0003",
          "LAST_CHILD_UPDDTM": "",
          "NAMES": {
            "objectType": "record",
            "objectName": "NAMES",
            "fields": [
              {
                "EMPLID": "FA0003",
                "NAME_TYPE": "PRF",
                "EFFDT": "1998-07-09",
                "EFF_STATUS": "A",
                "COUNTRY_NM_FORMAT": "001",
                "NAME": "Abban,Ali",
                "NAME_INITIALS": "",
                "NAME_PREFIX": "",
                "NAME_SUFFIX": "",
                "NAME_ROYAL_PREFIX": "",
                "NAME_ROYAL_SUFFIX": "",
                "NAME_TITLE": "",
                "LAST_NAME_SRCH": "ABBAN",
                "FIRST_NAME_SRCH": "ALI",
                "LAST_NAME": "Abban",
                "FIRST_NAME": "Ali",
                "MIDDLE_NAME": "",
                "SECOND_LAST_NAME": "",
                "SECOND_LAST_SRCH": "",
                "NAME_AC": "",
                "PREF_FIRST_NAME": "",
                "PARTNER_LAST_NAME": "",
                "PARTNER_ROY_PREFIX": "",
                "LAST_NAME_PREF_NLD": "1",
                "NAME_DISPLAY": "Ali Abban",
                "NAME_FORMAL": "Ali Abban",
                "NAME_DISPLAY_SRCH": "ALIABBAN",
                "LASTUPDDTTM": "",
                "LASTUPDOPRID": ""
              },
              {
                "EMPLID": "FA0003",
                "NAME_TYPE": "PRI",
                "EFFDT": "1998-07-09",
                "EFF_STATUS": "A",
                "COUNTRY_NM_FORMAT": "001",
                "NAME": "Abban,Ali",
                "NAME_INITIALS": "",
                "NAME_PREFIX": "Ms",
                "NAME_SUFFIX": "",
                "NAME_ROYAL_PREFIX": "",
                "NAME_ROYAL_SUFFIX": "",
                "NAME_TITLE": "",
                "LAST_NAME_SRCH": "ABBAN",
                "FIRST_NAME_SRCH": "ALI",
                "LAST_NAME": "Abban",
                "FIRST_NAME": "Ali",
                "MIDDLE_NAME": "",
                "SECOND_LAST_NAME": "",
                "SECOND_LAST_SRCH": "",
                "NAME_AC": "",
                "PREF_FIRST_NAME": "",
                "PARTNER_LAST_NAME": "",
                "PARTNER_ROY_PREFIX": "",
                "LAST_NAME_PREF_NLD": "1",
                "NAME_DISPLAY": "Ali Abban",
                "NAME_FORMAL": "Ms Ali Abban",
                "NAME_DISPLAY_SRCH": "ALIABBAN",
                "LASTUPDDTTM": "",
                "LASTUPDOPRID": ""
              }
            ]
          },
          "EMAIL_ADDRESSES": {
            "objectType": "record",
            "objectName": "EMAIL_ADDRESSES",
            "fields": []
          },
          "PERSONAL_PHONE": {
            "objectType": "record",
            "objectName": "PERSONAL_PHONE",
            "fields": [
              {
                "EMPLID": "FA0003",
                "PHONE_TYPE": "MAIN",
                "COUNTRY_CODE": "",
                "PHONE": "",
                "EXTENSION": "",
                "PREF_PHONE_FLAG": "Y"
              }
            ]
          },
          "ADDRESSES": {
            "objectType": "record",
            "objectName": "ADDRESSES",
            "fields": [
              {
                "EMPLID": "FA0003",
                "ADDRESS_TYPE": "PERM",
                "EFFDT": "1998-01-14",
                "EFF_STATUS": "A",
                "COUNTRY": "USA",
                "ADDRESS1": "10299 Placid Place",
                "ADDRESS2": "",
                "ADDRESS3": "",
                "ADDRESS4": "",
                "CITY": "New Market",
                "NUM1": "",
                "NUM2": "",
                "HOUSE_TYPE": "",
                "ADDR_FIELD1": "",
                "ADDR_FIELD2": "",
                "ADDR_FIELD3": "",
                "COUNTY": "",
                "STATE": "MD",
                "POSTAL": "21774",
                "GEO_CODE": "",
                "IN_CITY_LIMIT": "",
                "ADDRESS1_AC": "",
                "ADDRESS2_AC": "",
                "ADDRESS3_AC": "",
                "CITY_AC": "",
                "REG_REGION": "",
                "LASTUPDDTTM": "",
                "LASTUPDOPRID": ""
              }
            ]
          }
        }
      ]
    }
  },
  "errorMessages": "",
  "errors": "",
  "meta": {
    "rowCount": "1",
    "sqlIDExecuted": "71906a11-57b0-4831-b227-f5238ed1be58",
    "success": "True",
    "debugMessages": "",
    "QueryString": "NULL",
    "URLPath": "doc-example/person/FA0003",
    "finalSQL": "{\n    \"records\": [\n        {\n            \"recordName\": \"PERSON\",\n            \"sqlWhereClause\": \"  EMPLID = 'FA0003'\",\n            \"excludeFields\": [\"BIRTHDATE\",\"BIRTHPLACE\", \"BIRTHCOUNTRY\", \"BIRTHSTATE\", \"DT_OF_DEATH\"]\n        },\n        {\n            \"recordName\": \"NAMES\",\n            \"parentRecordName\": \"PERSON\"\n        },\n        {\n            \"recordName\": \"EMAIL_ADDRESSES\",\n            \"parentRecordName\": \"PERSON\"\n        },\n        {\n            \"recordName\": \"PERSONAL_PHONE\",\n            \"parentRecordName\": \"PERSON\"\n        },\n        {\n            \"recordName\": \"ADDRESSES\",\n            \"parentRecordName\": \"PERSON\"\n        }\n    ]\n}",
    "productVersion": "2023-06-07",
    "toolsVer": "8.58.07",
    "currentUser": "SWS_DOC_ACCOUNT",
    "responseDTTM": "2023-09-25 19:00:34.000000",
    "psftTransactionId": "ce514ec3-5bd5-11ee-adcb-d5409ab5d8fe",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "ServerDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessage": ""
  }
}

Configuration Options

Let’s cover each field on the SWS configuration page.

• Unique Identifier: This is a system generated GUID that will be generated at save time. It is used to uniquely identify this configuration. You can not change this value and it should be safe to export to another database unlike a integer counter.
• Description: This is the description of the configuration. This is for admin use and is used in PeopleSoft search records.
• URL Path: This field is very important and will map to the full URL that a client will use to target your web service. This has to be unique across the database.
• Active: Allows you to easily turn on or off the configuration.
• Notes: This is a section for you to add notes about this web service. This might be internal notes or links to documentation or development tickets.
• Request Format Type: PSOFT-QL or SQL - This defines what syntax SWS uses to query and return data.
  • If “Psoft-QL request format type was chosen then a long text box will show up called. Psoft-QL Text (JSON)
    • You enter the Psoft-QL that you want to run in response to a client request. This has a very particular format.
  • If “SQL” request format type was chosen then a long text box will show up called. SQL Statement Text
    • You enter the SQL that you want to run in response to a client request.
  • SQL Statement Text: This is the section where you enter the SQL statement that will run.
  • There is some special syntax for binding variables from the client request to the SQL statement. We will cover that detail in the next section.
• Row Limit: Optional row limit. You can specify a non-zero number here and the handler will stop the output after that number of output rows has been encoded. Clients can override this at runtime by passing ?rowLimit=N on the URL, which also serves as the page size when the client paginates with ?pageNumber=N. See Pagination for SQL endpoints below.
• Default Encoding: Used to set a default encoding scheme if the client does not include one in the request. All SWS web services automatically handle JSON, CSV and XML encoding.
• Include metadata in Response: If checked then some additional information about the SWS request and response will be encoded. We will show some examples of this shortly.
• (danger) Minutes to Cache Response: You can optionally configure to have the integration broker cache the response. This can be done for performance reasons if the SQL is very “expensive”. Please read the Caching article before enabling this. There are some hidden dangers.
• Parameters Grid: This grid allows you to configure how the client passes parameters that are substituted into the SQL. If your SQL does not have any variables provided by the client, there will be no rows here. We will cover this in depth shortly.
• Output Fields Grid: This will only show for a “SQL” Request Format Type. This grid is where you name the SQL columns that show up in the encoding section. For JSON and XML encoding, the values here will be the names of the properties and nodes. For CSV, these will be the header values.
• Allowed Permission Lists Grid: This grid is where you configure SWS to tell what permission lists are authorized to run this SQL statement. There is a detailed security section below that documents how to use this and setup API client users.
• Copy & Delete - Inside this group box are buttons to clone/copy the current configuration or to delete it.
• DMS Export - Inside this group box is a generated DMS script template that can be used to export your configuration between databases.
• HTTP Info Page - This page gives you both HTTP and curl examples on how to invoke the service operation. This removes the guesswork and speeds up the testing cycle.

Pagination for SQL endpoints

Any SQL-type SWS configuration can be paginated by the client with two optional URL query-string parameters. No configuration change is required on the SWS setup page — the parameters are applied automatically by the SWS handler when present.

Pagination is offset-based. The handler fetches the full result set from the database and skips the rows that belong to earlier pages before it encodes the response. This means every paginated call still pays the cost of the underlying query, so pagination is not a substitute for an efficient WHERE clause when working with very large tables.

Example

Fetch the third page of 20 users from a SQL-backed /sql/allusers configuration:

GET /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS/sql/allusers?pageNumber=3&rowLimit=20
Accept: application/json
Authorization: Basic <token>

The response payload includes three additional meta fields so the client knows where it is in the result set:

{
  "data": [ /* ... 20 rows ... */ ],
  "errors": "",
  "meta": {
    "rowCount": "20",
    "pageNumber": "3",
    "rowLimit": "20",
    "nextPageNumber": "4",
    "success": "True"
  }
}

• pageNumber and rowLimit echo the effective values used for this request.
• nextPageNumber is present only when at least one more row remains after the current page. Its absence signals the last page.
• For XML responses the same three fields appear under /response/meta/. For CSV responses they are emitted as the HTTP response headers x-pageNumber, x-rowLimit, and x-nextPageNumber (matching the existing x-rowCount, x-success, and other x-* headers used by CSV clients).

Backward compatibility

If a client sends neither pageNumber nor rowLimit, the SQL endpoint behaves exactly as it did before pagination was added: results are capped only by the setup-configured Row Limit field on the configuration row. Existing integrations do not need to change.

Ordering

Pagination relies on the natural ordering of the configured SQL statement. If the SQL does not include an ORDER BY clause, the database is free to return rows in any order, and that order can shift between calls — paging may then skip or duplicate rows. When you design a SQL configuration that clients will page through, add an explicit ORDER BY on a stable key.

3.2 - SWS Security Setup

SWS has several layers of security for the web services. Additionally, security is required for a few custom components that are delivered with SWS that power-user use to configure SWS. We will discuss those here.

For SWS, there are two main areas of security.

• API users who need to use call an SWS web service.
• Security for Administrators to create and update SWS configurations.
  • This security is just needed by trusted users who will be configuring SWS web services.

SWS API User Security

SWS is based on PeopleSoft REST framework. The security mechanism that exists for REST is based on standard PeopleSoft PSOPRDEFN OPRIDs and passwords. We will walk through how this works and the best practices in this section. SWS is backed by a custom PeopleSoft REST Service.

For API users that need to invoke SWS web services, they need to be setup as a valid PeopleSoft user.

The technical design of SWS is actually structured around one PeopleSoft service operation called CHG_SWS_GET. All SQL statements that you deploy as a web service are handled by this one service operation and PeopleCode Handler. Therefore, all API users will need this base service operation as part of their security.

There are a few layers to security with SWS:

• PeopleSoft OPRID and Password
  • The basic auth token provided must be a valid PeopleSoft OPRID and password. The PeopleSoft IB will authenticate the OPRID and password. If the OPRID is not valid or the password is incorrect, the IB will return an error.
• The REST Service Account OPRID’s access to the Service Operation CHG_SWS_GET
  • The authenticated OPRID must have access to the CHG_SWS_GET service operation. If the OPRID does not have access, the IB will return an error.
• The SWS Configuration Security setup for the Path.
  • The authenticated OPRID must have access to the SWS configuration for the path. If the OPRID does not have access, the SWS will return an error.
• The configured SQL or PsoftQL could also have data security configured to limit what data is returned to the client.

@startuml

participant "HTTP Client" as client
participant "PeopleSoft IB" as psib
participant "SWS Handler" as pssws


client -> psib: HTTP Request\nBasic Auth Token
psib -> psib: Authenticate\nOPRID and Password

psib -> psib: Check Access\nTo Service Operation
psib -> pssws: Call Service Operation\nCHG_SWS_GET
pssws -> pssws: Lookup SWS\nConfiguration
pssws -> pssws: Is User Authorized?
pssws -> pssws: Convert Configuration into SQL
pssws -> pssws: Get Data
pssws -> client: Return Data

@enduml

The handler PeopleCode in the CHG_SWS_GET operation actually performs some additional checks on the current user to determine if they have access to execute the SQL statement at the path. Those permission lists are configured on the SWS setup table (COMPONENT: CHG_SWS_CONF_TBL). If an API user tries to invoke a SWS SQL statement and they do NOT have security, the SQL will NOT be run.

What is required to create an API User for SWS?

• Create a new OPRID that represents the client using the application.
• Give that OPRID a complex password. This password stored in PSOPRDEFN will be used in the authentication.
• Give that OPRID access to the Service Operation CHG_SWS_GET
  • You can use the role CHG_SWS_USER for this purpose.
• Give that user access to some other unique permission list that identifies it and that you can use to secure the SWS setup.
  • When the clients tries to trigger an SWS web service, the API User OPRID must have a permission list configured on the SQL statement. Each SWS SQL statement is tagged with permission lists that are allowed to execute it.

You can use the security objects we delivered as part of the project or use your own permission lists based on your own standard.

SWS API Delivered Security:

• Permission List: CHG_SWS_USER
  • Service: CHG_SWS, Service Operation: CHG_SWS_GET
• Role: CHG_SWS_USER
  • Permission List: CHG_SWS_USER

Example User ID and Basic Authentication Code

Let’s imagine we want to set up a test API user account to manually test web services.

• Create a new OPRID called Z_TEST_API_USER
  • Create a complex randomly generated password. We will use proctor-consular-esther-hull-flood for this example.
  • The EMPLID can be set to nothing.
  • Set the other required fields to your system’s normal default values for a low-level non-privileged account.
• Give OPRID Z_TEST_API_USER the role CHG_SWS_USER
• Create a permission list called Z_TEST_API_USER. This will not have any real permission in the security tab. We will use it in the SWS configuration.
• Create a new role called Z_TEST_API_USER and assign it the Z_TEST_API_USER permission list.
• Grant user Z_TEST_API_USER the new Z_TEST_API_USER role.

Now that we have our OPRID and password we can generate an HTTP Basic Authentication token using the following scheme.

• Concatenate the OPRID and password together with a colon. That gives us:
  • Z_TEST_API_USER:proctor-consular-esther-hull-flood
• Base64 encode that concatenated string.
  • Z_TEST_API_USER:proctor-consular-esther-hull-flood –> Wl9URVNUX0FQSV9VU0VSOnByb2N0b3ItY29uc3VsYXItZXN0aGVyLWh1bGwtZmxvb2Q=
  • Many text editors and HTTP test clients like Postman have base64 encoding functions built in. There are also tools online that will do this but I would not trust those with my production passwords.
• The base64 encoded output servers as the token and is used in an HTTP Basic authorization header in the following form:
  • Authorization: Basic Wl9URVNUX0FQSV9VU0VSOnByb2N0b3ItY29uc3VsYXItZXN0aGVyLWh1bGwtZmxvb2Q=

Security Best Practices

For these best practices, we are going to imagine three fictional internal systems that will be calling SWS web services that we can use in our example.

• Stellar Wind - Internal Payment System
• PRISM - Internal CRM system
• MYSTIC - Internal Marketing System

Based on those three systems, we will show you the recommended security setup for these API users. Your security team may vary this depending on your own standards.

• For each client or external system calling your web service, create a new and unique OPRID. Do not reuse a super user or real user account for this. Create a new account that only has the minimum API security and no PIA login ability.
  • For our three internal systems that may look like:
    • OPRID: Z_STELLAR_WIND_API_USER
    • OPRID: Z_PRISM_API_USER
    • OPRID: Z_MYSTIC_API_USER
  • Do not share API user accounts across systems.
    • Shared accounts make password rotation nearly impossible since you have to coordinate with more than one group.
    • Shared accounts also make auditing more difficult.
  • Having separate accounts makes it easy for you to shut off one system and not impact the others.
• Each API User should have a complex password that is generated by a password manager.
  • Store your passwords in some sort of password database.
  • Do NOT email passwords to users. Emails persist forever.
• The API Users should store the password in a secure location that easily facilitates password rotation.
  • The password should NOT be hard coded in the source code.
  • This could be an environment variable or something more advanced like Hashicorp Vault
• Each API user may have a distinct permission list that identifies it and that you can use in SWS. The API user may have access to other PeopleSoft APIs. We often recommend creating a permission list and a role that is equal to the OPRID. Your security standard may be different. We find this simple model for API client OPRIDs is effective and allows visibility into what permissions an API user has. (OPRID = ROLENAME = CLASSID). API Clients tend to have very specific permissions that are not shared across other users.
  • For our three internal systems that may end up looking like this:
    • OPRID: Z_STELLAR_WIND_API_USER
      • ROLE: CHG_SWS_USER
        • Permission List: CHG_SWS_USER
      • ROLE: Z_STELLAR_WIND_API_USER
        • Permission List: Z_STELLAR_WIND_API_USER
    • OPRID: Z_PRISM_API_USER
      • ROLE: CHG_SWS_USER
        • Permission List: CHG_SWS_USER
      • ROLE: Z_PRISM_API_USER
        • Permission List: Z_PRISM_API_USER
    • OPRID: Z_MYSTIC_API_USER
      • ROLE: CHG_SWS_USER
        • Permission List: CHG_SWS_USER
      • ROLE: Z_MYSTIC_API_USER
        • Permission List: Z_MYSTIC_API_USER

Our Integration Broker book has a detailed section on how REST Security in PeopleSoft works. That is great reference.

SWS Administrator Security

For users who will be maintaining SWS configuration, they need access to the setup component for SWS. You can use the permission list and role that we delivered with the project or use one that works with your security standard.

SWS Administrator Delivered Security:

• Permission List: CHG_SWS_ADMIN
  • MENU: CHG_TOOLS, COMPONENT: CHG_SWS_CONF_TBL
  • Service: CHG_SWS, Service Operation: CHG_SWS_GET
• Role: CHG_SWS_ADMIN
  • Permission List: CHG_SWS_ADMIN

Research Queries

This SQL will find the users who have access to the SWS Web Services.

• Users who have access to Service Operation: CHG_SWS_PSOFTQL_POST should be extremely limited.

-- Find Users who have access to the 
-- SWS Web Services

SELECT
  A.OPRID ,
  B.ROLENAME ,
  C.CLASSID ,
  O.IB_OPERATIONNAME,
  AU.CLASSID
FROM
  PSOPRDEFN A ,
  PSROLEUSER B ,
  PSROLECLASS C ,
  PSOPERATION O,
  PSAUTHWS AU
WHERE
  A.OPRID = B.ROLEUSER
  AND B.ROLENAME = C.ROLENAME
  AND O.IB_OPERATIONNAME LIKE 'CHG_SWS%'
  AND O.IB_OPERATIONNAME = AU.IB_OPERATIONNAME
  AND AU.CLASSID = C.CLASSID
ORDER BY
  A.OPRID,
  B.ROLENAME;

Find users who can access CHG_SWS_GET and the paths they can execute

--- Find users who can access CHG_SWS_GET and the paths they can execute
SELECT
  A.OPRID ,
  B.ROLENAME ,
  C.CLASSID ,
  O.IB_OPERATIONNAME,
  SWSC.CHG_DE_PATH
FROM
  PSOPRDEFN A ,
  PSROLEUSER B ,
  PSROLECLASS C ,
  PSOPERATION O,
  PSAUTHWS AU, PS_C_SWS_CONF_TBL SWSC, PS_C_SWS_CONF_PL SWSPL
WHERE
  A.OPRID = B.ROLEUSER
  AND B.ROLENAME = C.ROLENAME
  AND O.IB_OPERATIONNAME = 'CHG_SWS_GET'
  AND O.IB_OPERATIONNAME = AU.IB_OPERATIONNAME
  AND AU.CLASSID = C.CLASSID
  AND SWSPL.CLASSID = AU.CLASSID
  AND SWSC.GUID = SWSPL.GUID;

3.3 - SWS Caching

• Minutes to Cache Response: You can optionally configure to have the integration broker cache the response. This can be done for performance reasons if the SQL is very “expensive”.

SWS can tell the RESTListeningConnector servlet to cache the response. This caching is provided by the built-in caching mechanism in PeopleTools REST functionality. It can speed up requests for expensive SQL where the result is not expected to change often. There are some known caveats — see the PeopleSoft REST Caching integration broker book section before using this.

3.4 -

@startuml

participant "HTTP Client" as client
participant "PeopleSoft IB" as psib
participant "SWS Handler" as pssws


client -> psib: HTTP Request\nBasic Auth Token
psib -> psib: Authenticate\nOPRID and Password

psib -> psib: Check Access\nTo Service Operation
psib -> pssws: Call Service Operation\nCHG_SWS_GET
pssws -> pssws: Lookup SWS\nConfiguration
pssws -> pssws: Is User Authorized?
pssws -> pssws: Convert Configuration into SQL
pssws -> pssws: Get Data
pssws -> client: Return Data

@enduml

4 - PeopleSoft Query Language (PsoftQL)

PsoftQL (PeopleSoft Query Language) is a request structure that is used by SWS to “ask” for data. This is similar in spirit to GraphQL but it has a syntax that is targeted specifically for asking for PeopleSoft data in a minimal format.

4.1 - Service Operation CHG_SWS_PSOFTQL

The CHG_SWS_PSOFTQL service operation is a web service that allows a privileged and trusted client to “ask” for any number of tables in PeopleSoft using PsoftQL Syntax. It can return structured JSON or XML data that mimics the PeopleSoft data structure. It is similar in spirit to GraphQL but only works inside a PeopleSoft database. The response body structure will vary based on the input request parameters.

This web service puts the responsibility on the integration client to know what they are asking for. The SWS GET Handler uses similar concepts but hides the complexity of the PeopleSoft data and is more secure.

Using the Service

Unlike the standard GET Service where the request syntax is hidden in the configuration table, this web service requires the client to know the PeopleSoft record structure and how to craft a request. The service takes a PsoftQL Syntax request as the body of the HTTP Post.

Let’s look at some examples and it will make more sense. We are using PsoftQL syntax to ask for tables. The request body is JSON or XML. The response body is JSON or XML. The response body structure will vary based on the input request parameters. All tables that are requested must be “whitelisted” to the API user. See the security section below for more details.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic basic ....token....
Content-Type: application/json
Host: your-ib-host.com

{
    "isDebugMode": true,
    "includeFieldTypes": true,
    "includeAllDescriptions": true,
    "includeKeyFieldIndicators": true,
    "records": [
    {
        "recordName": "PSOPRDEFN",
        "includeDescriptionsFor": [],
        "excludeFields": []
    }
    ]
}

Here is the same request but in XML form.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic basic ....token....
Content-Type: application/xml
Host: your-ib-host.com


<?xml version="1.0" encoding="UTF-8" ?>
<request>
    <isDebugMode>false</isDebugMode>
    <rowLimit>20</rowLimit>
    <noEffectiveStatusLogic>false</noEffectiveStatusLogic>
    <noEffectiveDateLogic>false</noEffectiveDateLogic>
    <includeFieldTypes>false</includeFieldTypes>
    <includeAllDescriptions>false</includeAllDescriptions>
    <includeKeyFieldIndicators>false</includeKeyFieldIndicators>
    <includeAllFieldLabels>false</includeAllFieldLabels>
    <effectiveDateOverride></effectiveDateOverride>
    <records>
        <record>
            <recordName>PSOPRDEFN</recordName>
        </record>
    </records>
</request>

Intended Use Cases

• This web service can replace many other “GET” operations and can provide more flexibility for different clients. This can accommodate custom client tables to be included in standard GET operations by just changing the request body. You should try to use this operation as a GET first. If it cannot accommodate the request then you can develop a custom operation.
• Pulling out “pick lists” or “prompt tables” values. For example, a list of subjects, locations, etc.
• Pulling out “XLAT” values
• Generating “Changes since x hours ago” queries. Many institutions may have large numbers of tables that you only want to pull out changes since the last sync. In this situation, you need some way to query “what has changed in the last hours” and then only pull those objects that have changed. You can use this web service and the “where clause” functionality to craft a query to find these and then potentially use other web services to only pull those objects.

Security Considerations

Data security is an important component of an enterprise. This web service technically can extract any PeopleSoft table or view out of the client system. 99.99% of institutions will not like this and want some sort of restriction on what tables this web service can pull. We have developed a client-owned “Whitelist” table. This is a simple listing of tables that this web service can be allowed to pull from the client system.

There is a PeopleSoft page where this is configured.

• Menu Name: CHG_TOOLS
• Component Name: CHG_SWS_REC_WL
• Record Name: C_SWS_REC_WL

The table also allows you to define a different whitelist for different API users. For example, we might have different API clients using this API that need different sets of data. The API authentication token is tied to a PeopleSoft user (Permission List). Therefore, you define what records (tables) each permission list should be allowed to see.

Here is an example where there are a few tables that are whitelisted. In this case, these are the only tables that can be pulled by this web service.

For these advanced use cases, it is often helpful for the client to be able to see what is whitelisted. In that case, you should “whitelist” the “whitelist” table and they can query it.

The whitelist table can be queried using this payload.

{
    "request": {
        "isDebugMode": false,
        "includeFieldTypes": false,
        "records": [
            {
                "recordName": "C_SWS_REC_WL"
            }
        ]
    }
}

That query above will return all tables that have been whitelisted for all permission lists NOT just limited to what the user will see. This version will limit what the user has access to but you need to change the "fieldValue": "CHG_SWS_USER" to something that matches your permission list.

{
    "isDebugMode": true,
    "records": [
    {
        "recordName": "C_SWS_REC_WL",
        "criteriaFields": [
        {
            "fieldName": "CLASSID",
            "fieldValue": "CHG_SWS_USER"
        }
        ]
    }
    ]
}

Features

• This web service is JSON or XML-based.
• The client can request a PeopleSoft record be returned and can include a where clause in various forms.
  • Child and Grandchildren table nesting is supported using specific request syntax.
  • A table in PeopleSoft is called a “record”. That is an object in the PeopleTools code. The field structure of the record will be synced to a database table with the same name as the record with “PS_” prefix for application tables and “PS” for PeopleTools table. All parameters for this web service refer to the record name and not the database table name.
• All record fields are exported unless you specifically ask for certain table fields to NOT be included. This can be useful on tables that have sensitive data (SNN, Salary) that you may not be using, and you don’t want logged across the infrastructure.
• The service supports adding human-readable code descriptions to the output. For example, if a CAMPUS code is “SDIEG” a description attribute may be returned that says “San Diego”.
• The service supports pagination to extract large amounts of data.
• Effective data logic is automatically handled. If the parent has the EFFDT field then all children requested will use that EFFDT.
• Records that have the EFF_STATUS field pull only the active value. This happens automatically but the request parameter is highly configurable.

Assumptions & Notes

• The client is a very trusted service account (PeopleSoft OPRID). This web service is not meant to be called from a web browser by some end user or javascript. This is something that should be called from immutable server code.
• The base response JSON structure is the same. All data is returned inside a data element. The JSON structure of what is returned nested inside the data element is entirely based on the request as the response data mimics that database/record structure. This will make more sense when you look at the examples.
• When requesting child and grandchild records, the logic looks at all fields on the parent. If the child table has a key field matching the field name then that value is used to pull data for this child. This is dynamically generated based on the PeopleSoft metadata. This allows you to ask for “children” that are NOT true children in PeopleSoft. For example, you may have a FACILITY_TBL that has a LOCATION code validated by the LOCATION_TBL. You can ask for the LOCATION_TBL as a child, and it will include the row from the LOCATION_TBL that matches the LOCATION entered on the FACILITY_TBL.
  • This service does not validate the parent-child relationships in the input parameters. You can submit bad requests. It is assumed you are working with someone who can tell you what the correct parent-child relationship is of tables. It gives you plenty of room to make a mistake because there is really no place in PeopleSoft to map parent-child relationships.
  • There can be any number of nesting of parent/child/grandchild.
• Effective dated logic is automatically applied to each record. You can pass in an optional ISO 8601 date (YYYY-MM-DD) like "effectiveDateOverride": "2021-12-25" for a record. This is optional. The current date will be used for all effective dating logic if data is not specified.
  • If you need all historical records, there is an optional parameter to NOT apply effective dated logic.
• You can only have one root record.
• Row Limits - If a row limit is not specified, then only the 1st 50 rows are returned and the remaining rows can be retrieved using pagination.
• Pagination is supported and enforced for large tables.
• Effective Status
  • This web service only pulls back records where EFF_STATUS = 'A'. Not all records have the EFF_STATUS field. The web service will apply this automatically. It can be turned off if you need values that are inactive.

4.2 - PsoftQL Syntax 🌟

The PeopleSoft Query Language (PsoftQL) is a powerful tool that allows users to extract information from PeopleSoft applications with SWS. PsoftQL is a request syntax used by the SWS to extract data from PeopleSoft. PsoftQL syntax is used when configuring SWS services.

PsoftQL is a succinct language that provides fine-grain control over what data is returned from a PeopleSoft application. It allows users to specify key parameters that determine the exact data that should be returned, making it an indispensable tool for those who seek specific information from their PeopleSoft applications.

By default, PsoftQL has reasonable defaults that accommodate most requests. However, advanced users can manipulate the various knobs and dials available to override the default behavior and access more detailed information. For instance, one can hide fields or join non-standard fields across tables to the query, ensuring that the data returned meets their specific needs.

Understanding base PsoftQL syntax is essential for working with SWS. The syntax is relatively easy to learn and can be used to access any data stored in PeopleSoft applications.

This document will cover every piece of the syntax.

First, we’ll demonstrate a few basic examples using the default behavior of SWS and PsoftQL syntax. This will prove that you don’t need to know every parameter to extract key information with SWS. After covering the details in the documentation, we’ll explore advanced usage later on.

Simple Examples

We will use JSON syntax here but XML syntax is also supported.

One Table

First, ask for the first 3 rows from the PSROLEDEFN table which defines roles in PeopleSoft security.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ..redacted..
Content-Type: application/json
Host: dev.ib.cedarhillsgroup.com
Content-Length: 84

{
  "rowLimit": 3,
  "records": [
    {
      "recordName": "PSROLEDEFN"
    }
  ]
}

The response we get back from the web service is detailed and exports all fields.

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 795
content-type: application/json; encoding=UTF-8
date: Tue, 06 Jun 2023 05:21:32 GMT

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "ACM Administrator",
          "VERSION": 1,
          "ROLETYPE": "U",
          "DESCR": "ACM Administrator",
          "QRYNAME": "",
          "ROLESTATUS": "A",
          "RECNAME": "",
          "FIELDNAME": "",
          "PC_EVENT_TYPE": "",
          "QRYNAME_SEC": "",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "",
          "ALLOWNOTIFY": "Y",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2020-02-19-13.49.14.223597",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "ADS Designer",
          "VERSION": 1,
          "ROLETYPE": "U",
          "DESCR": "ADS Designer",
          "QRYNAME": "",
          "ROLESTATUS": "A",
          "RECNAME": "",
          "FIELDNAME": "",
          "PC_EVENT_TYPE": "",
          "QRYNAME_SEC": "",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "Role for Application Dataset Designer",
          "ALLOWNOTIFY": "Y",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2020-02-19-13.49.14.223642",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 3,
          "ROLENAME": "AG Composer Administrator",
          "VERSION": 1,
          "ROLETYPE": "U",
          "DESCR": "AG Composer Administrator",
          "QRYNAME": "",
          "ROLESTATUS": "A",
          "RECNAME": "",
          "FIELDNAME": "",
          "PC_EVENT_TYPE": "",
          "QRYNAME_SEC": "",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "This role provides access to the setup pages to define, manage, and maintain Activity Guide Composer templates.",
          "ALLOWNOTIFY": "Y",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2019-05-22-09.34.01.102262",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-05.21.33.000000",
    "psftTransactionId": "ffb423b7-0429-11ee-9a28-e332b6feab8c",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14",
  "nextPageNumber": 2
}

Please note that SWS is NOT hard coded in any way with the fields that exist. SWS reads the PeopleTools meta-data to determine what fields exist on the record. So if Oracle changes the fields or you add custom fields those are automatically picked up.

Adding Some Metadata

Let’s go a bit deeper. Let’s say that your external integration partner needs to know the field label and also decode any sort of XLATs or prompt tables that could exist.

Here we just add to new optional parameter that tells the web service to dynamically pull back labels and any descriptions.

• "includeAllDescriptions": true
• "includeAllFieldLabels": true

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted..
Content-Type: application/json
Host: dev.ib.cedarhillsgroup.com
Content-Length: 151

{
  "rowLimit": 3,
  "includeAllDescriptions": true,
  "includeAllFieldLabels": true,
  "records": [
    {
      "recordName": "PSROLEDEFN"
    }
  ]
}

Here is the response.

You will see that there is the following pattern.

• Field labels are output as {{fieldName}}_defaultLabel
• Fields with XLATs or prompt tables are exported as {{fieldName}}_description

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 1075
content-type: application/json; encoding=UTF-8
date: Tue, 06 Jun 2023 05:29:49 GMT

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "ACM Administrator",
          "ROLENAME_defaultLabel": "Role Name",
          "VERSION": 1,
          "VERSION_defaultLabel": "Version",
          "ROLETYPE": "U",
          "ROLETYPE_defaultLabel": "Role Type",
          "ROLETYPE_description": "User List",
          "DESCR": "ACM Administrator",
          "DESCR_defaultLabel": "Description",
          "QRYNAME": "",
          "QRYNAME_defaultLabel": "Role-Query Name",
          "ROLESTATUS": "A",
          "ROLESTATUS_defaultLabel": "Role Status",
          "ROLESTATUS_description": "Active",
          "RECNAME": "",
          "RECNAME_defaultLabel": "Record (Table) Name",
          "FIELDNAME": "",
          "FIELDNAME_defaultLabel": "Field Name",
          "PC_EVENT_TYPE": "",
          "PC_EVENT_TYPE_defaultLabel": "PeopleCode Event Name",
          "QRYNAME_SEC": "",
          "QRYNAME_SEC_defaultLabel": "Query Name",
          "PC_FUNCTION_NAME": "",
          "PC_FUNCTION_NAME_defaultLabel": "PeopleCode Function Name",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_PCODE_RULE_ON_defaultLabel": "PeopleCode Rule Enabled",
          "ROLE_QUERY_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON_defaultLabel": "Query Rule Enabled",
          "LDAP_RULE_ON": "N",
          "LDAP_RULE_ON_defaultLabel": "Directory Rule Enabled",
          "DESCRLONG": "",
          "DESCRLONG_defaultLabel": "Description",
          "ALLOWNOTIFY": "Y",
          "ALLOWNOTIFY_defaultLabel": "Allow notification",
          "ALLOWLOOKUP": "Y",
          "ALLOWLOOKUP_defaultLabel": "Allow Recipient Lookup",
          "LASTUPDDTTM": "2020-02-19-13.49.14.223597",
          "LASTUPDDTTM_defaultLabel": "Last Update Date/Time",
          "LASTUPDOPRID": "PPLSOFT",
          "LASTUPDOPRID_defaultLabel": "Last Update User ID"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "ADS Designer",
          "ROLENAME_defaultLabel": "Role Name",
          "VERSION": 1,
          "VERSION_defaultLabel": "Version",
          "ROLETYPE": "U",
          "ROLETYPE_defaultLabel": "Role Type",
          "ROLETYPE_description": "User List",
          "DESCR": "ADS Designer",
          "DESCR_defaultLabel": "Description",
          "QRYNAME": "",
          "QRYNAME_defaultLabel": "Role-Query Name",
          "ROLESTATUS": "A",
          "ROLESTATUS_defaultLabel": "Role Status",
          "ROLESTATUS_description": "Active",
          "RECNAME": "",
          "RECNAME_defaultLabel": "Record (Table) Name",
          "FIELDNAME": "",
          "FIELDNAME_defaultLabel": "Field Name",
          "PC_EVENT_TYPE": "",
          "PC_EVENT_TYPE_defaultLabel": "PeopleCode Event Name",
          "QRYNAME_SEC": "",
          "QRYNAME_SEC_defaultLabel": "Query Name",
          "PC_FUNCTION_NAME": "",
          "PC_FUNCTION_NAME_defaultLabel": "PeopleCode Function Name",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_PCODE_RULE_ON_defaultLabel": "PeopleCode Rule Enabled",
          "ROLE_QUERY_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON_defaultLabel": "Query Rule Enabled",
          "LDAP_RULE_ON": "N",
          "LDAP_RULE_ON_defaultLabel": "Directory Rule Enabled",
          "DESCRLONG": "Role for Application Dataset Designer",
          "DESCRLONG_defaultLabel": "Description",
          "ALLOWNOTIFY": "Y",
          "ALLOWNOTIFY_defaultLabel": "Allow notification",
          "ALLOWLOOKUP": "Y",
          "ALLOWLOOKUP_defaultLabel": "Allow Recipient Lookup",
          "LASTUPDDTTM": "2020-02-19-13.49.14.223642",
          "LASTUPDDTTM_defaultLabel": "Last Update Date/Time",
          "LASTUPDOPRID": "PPLSOFT",
          "LASTUPDOPRID_defaultLabel": "Last Update User ID"
        },
        {
          "rowNumber": 3,
          "ROLENAME": "AG Composer Administrator",
          "ROLENAME_defaultLabel": "Role Name",
          "VERSION": 1,
          "VERSION_defaultLabel": "Version",
          "ROLETYPE": "U",
          "ROLETYPE_defaultLabel": "Role Type",
          "ROLETYPE_description": "User List",
          "DESCR": "AG Composer Administrator",
          "DESCR_defaultLabel": "Description",
          "QRYNAME": "",
          "QRYNAME_defaultLabel": "Role-Query Name",
          "ROLESTATUS": "A",
          "ROLESTATUS_defaultLabel": "Role Status",
          "ROLESTATUS_description": "Active",
          "RECNAME": "",
          "RECNAME_defaultLabel": "Record (Table) Name",
          "FIELDNAME": "",
          "FIELDNAME_defaultLabel": "Field Name",
          "PC_EVENT_TYPE": "",
          "PC_EVENT_TYPE_defaultLabel": "PeopleCode Event Name",
          "QRYNAME_SEC": "",
          "QRYNAME_SEC_defaultLabel": "Query Name",
          "PC_FUNCTION_NAME": "",
          "PC_FUNCTION_NAME_defaultLabel": "PeopleCode Function Name",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_PCODE_RULE_ON_defaultLabel": "PeopleCode Rule Enabled",
          "ROLE_QUERY_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON_defaultLabel": "Query Rule Enabled",
          "LDAP_RULE_ON": "N",
          "LDAP_RULE_ON_defaultLabel": "Directory Rule Enabled",
          "DESCRLONG": "This role provides access to the setup pages to define, manage, and maintain Activity Guide Composer templates.",
          "DESCRLONG_defaultLabel": "Description",
          "ALLOWNOTIFY": "Y",
          "ALLOWNOTIFY_defaultLabel": "Allow notification",
          "ALLOWLOOKUP": "Y",
          "ALLOWLOOKUP_defaultLabel": "Allow Recipient Lookup",
          "LASTUPDDTTM": "2019-05-22-09.34.01.102262",
          "LASTUPDDTTM_defaultLabel": "Last Update Date/Time",
          "LASTUPDOPRID": "PPLSOFT",
          "LASTUPDOPRID_defaultLabel": "Last Update User ID"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-05.29.49.000000",
    "psftTransactionId": "28121750-042b-11ee-9a28-e332b6feab8c",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14",
  "nextPageNumber": 2
}

Adding Some Criteria

In those examples above, we were just pulling back all the rows from the PSROLEDEFN table in a paginated way and only returning the top 3. Let’s limit our request to roles starting with “SA”% using the criteriaFields and remove some fields from that output that are not important to our integration partner using excludeFields. PsoftQL also has syntax for advanced SQL clauses but the criteriaFields is good for simple use cases. When you configure PsoftQL you can substitute user parameters from the HTTP request instead of hard coding like we are doing here for demo purposes.

We also removed the labels by removing includeAllFieldLabels from the request just to make the output more succinct.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted..
Content-Type: application/json
Host: dev.ib.cedarhillsgroup.com
Content-Length: 394

{
  "rowLimit": 3,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": ["RECNAME", "FIELDNAME", "PC_EVENT_TYPE", "QRYNAME_SEC", "VERSION", "QRYNAME", "ALLOWNOTIFY"],
      "criteriaFields": [
        {
            "fieldName": "ROLENAME",
            "fieldValue": "SA%",
            "operator": "LIKE"
        }
        ]
    }
  ]
}

Here is the response.

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 686
content-type: application/json; encoding=UTF-8
date: Tue, 06 Jun 2023 05:42:59 GMT

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "SA Administrator",
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "DESCR": "SA and CR Administrator",
          "ROLESTATUS": "A",
          "ROLESTATUS_description": "Active",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "Student Administration and Contributor Relations administrator.  Do not modify.",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2004-05-11-21.49.57.000000",
          "LASTUPDOPRID": "PPLSOFT"
        },
        {
          "rowNumber": 2,
          "ROLENAME": "SAIP Administrator",
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "DESCR": "SAIP Administrator",
          "ROLESTATUS": "A",
          "ROLESTATUS_description": "Active",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "Adminster Student Administration Integration Platform",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2007-07-11-16.45.39.000000",
          "LASTUPDOPRID": "PPLSOFT"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-05.42.59.000000",
    "psftTransactionId": "feb52cc9-042c-11ee-9a28-e332b6feab8c",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Joining Tables - Automatically

Ok, we just hit 2nd gear. Let’s shift into 3rd gear and join in some child tables!

We want to join in a few more tables. They have the following hierarchy

• PSROLEDEFN - Defines a role
  • PSROLECLASS - Defines Permission lists (CLASSID) assigned to a role.
    • PSCLASSDEFN - The permission list definition.
      • PSAUTHBUSCOMP - The component interface authorizations

We use the PsoftQL syntax to pass in more records but this time we tell it using the parentRecordName what the parent should be and SWS will automatically join the record based on the key fields. There are some more advanced options you can override the join logic if joining “strange” tables that are documented in detail in the syntax documentation. The default behavior works in 90% of the cases.

We will also just ask for one row because you are going to have to scroll.

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted..
Content-Type: application/json
Host: dev.ib.cedarhillsgroup.com
Content-Length: 717

{
  "rowLimit": 1,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": [
        "RECNAME",
        "FIELDNAME",
        "PC_EVENT_TYPE",
        "QRYNAME_SEC",
        "VERSION",
        "QRYNAME",
        "ALLOWNOTIFY"
      ],
      "criteriaFields": [
        {
          "fieldName": "ROLENAME",
          "fieldValue": "SA%",
          "operator": "LIKE"
        }
      ]
    },
      {
      "recordName": "PSROLECLASS",
      "parentRecordName": "PSROLEDEFN"
    },
    {
      "recordName": "PSCLASSDEFN",
      "parentRecordName": "PSROLECLASS"
    },
    {
      "recordName": "PSAUTHBUSCOMP",
      "parentRecordName": "PSCLASSDEFN"
    }
  ]
}

Here is the response. In this case, this role only had one permission list and a bunch of Component Interface permissions (PSAUTHBUSCOMP). I deleted some of the rows so you did not have to scroll so much.

HTTP/1.1 200 OK
connection: close
content-encoding: gzip
content-length: 2731
content-type: application/json; encoding=UTF-8
date: Tue, 06 Jun 2023 05:56:54 GMT

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        {
          "rowNumber": 1,
          "ROLENAME": "SA Administrator",
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "DESCR": "SA and CR Administrator",
          "ROLESTATUS": "A",
          "ROLESTATUS_description": "Active",
          "PC_FUNCTION_NAME": "",
          "ROLE_PCODE_RULE_ON": "N",
          "ROLE_QUERY_RULE_ON": "N",
          "LDAP_RULE_ON": "N",
          "DESCRLONG": "Student Administration and Contributor Relations administrator.  Do not modify.",
          "ALLOWLOOKUP": "Y",
          "LASTUPDDTTM": "2004-05-11-21.49.57.000000",
          "LASTUPDOPRID": "PPLSOFT",
          "PSROLECLASS": {
            "objectType": "record",
            "objectName": "PSROLECLASS",
            "fields": [
              {
                "ROLENAME": "SA Administrator",
                "CLASSID": "HCCPCSSA1000",
                "PSCLASSDEFN": {
                  "objectType": "record",
                  "objectName": "PSCLASSDEFN",
                  "fields": [
                    {
                      "CLASSID": "HCCPCSSA1000",
                      "VERSION": 131,
                      "CLASSDEFNDESC": "CS Administration - All Pages",
                      "TIMEOUTMINUTES": 0,
                      "DEFAULTBPM": "",
                      "STARTAPPSERVER": 0,
                      "ALLOWPSWDEMAIL": 0,
                      "LASTUPDDTTM": "2020-12-18-09.13.22.000000",
                      "LASTUPDOPRID": "PPLSOFT",
                      "PSAUTHBUSCOMP": {
                        "objectType": "record",
                        "objectName": "PSAUTHBUSCOMP",
                        "fields": [
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_LIST",
                            "BCNAME_description": "Enrollment List for the Term",
                            "BCMETHOD": "GetEnrlFldXlat",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_LIST",
                            "BCNAME_description": "Enrollment List for the Term",
                            "BCMETHOD": "GetSection",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_REQUEST",
                            "BCNAME_description": "Submit Enrollment Request",
                            "BCMETHOD": "Cancel",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_REQUEST",
                            "BCNAME_description": "Submit Enrollment Request",
                            "BCMETHOD": "Create",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_REQUEST",
                            "BCNAME_description": "Submit Enrollment Request",
                            "BCMETHOD": "Find",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "ENRL_REQUEST",
                            "BCNAME_description": "Submit Enrollment Request",
                            "BCMETHOD": "Get",
                            "AUTHORIZEDACTIONS": 4
                          },
                          {
                            "CLASSID": "HCCPCSSA1000",
                            "BCNAME": "SCC_CHESSN_AUS_CI",
                            "BCNAME_description": "Chessn Page CI",
                            "BCMETHOD": "Save",
                            "AUTHORIZEDACTIONS": 4
                          }
                        ]
                      }
                    }
                  ]
                }
              }
            ]
          }
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-05.56.54.000000",
    "psftTransactionId": "f075d5ba-042e-11ee-9a28-e332b6feab8c",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 1,
  "apiVersion": "2023-03-14",
  "nextPageNumber": 2
}

So what did we learn from the examples above? That SWS PsoftQL can do a lot of stuff for you automatically with a short syntax. We did not show you how effective dating and effective status is also handled automatically. However, those are better covered in some of the detailed examples for each PeopleSoft pillar in the later sections of this chapter.

PsoftQL Syntax

Here is the base JSON syntax example for one field. Most of these parameters are completely optional.

{
  "isDebugMode": false,
  "rowLimit": 1,
  "pageNumber": 3,
  "includeFieldTypes": false,
  "includeAllDescriptions": false,
  "includeKeyFieldIndicators": false,
  "includeAllFieldLabels": false,
  "noEffectiveDateLogic": false,
  "effectiveDateOverride": "1888-01-01",
  "noEffectiveStatusLogic": false,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "includeDescriptionsFor": [
        "ROLESTATUS",
        "ROLETYPE"
      ],
      "excludeFields": [
        "ALLOWNOTIFY",
        "VERSION"
      ],
      "parentRecordName": "",
      "useParentEffectiveDate": true,
      "doNotAutoJoinToParent": false,
      "criteriaFields": [
        {
          "fieldName": "ROLENAME",
          "fieldValue": "A%",
          "operator": "LIKE"
        }
      ]
    }
  ]
}

There is also an XML syntax that has the same parameter names but there are some subtle differences due to how XML and JSON are slightly different, especially around arrays. This is an XML example of a parent-child request.

<?xml version="1.0" encoding="UTF-8" ?>
<request>
    <records>
        <noEffectiveStatusLogic>true</noEffectiveStatusLogic>
        <noEffectiveDateLogic>true</noEffectiveDateLogic>
        <includeFieldTypes>true</includeFieldTypes>
        <includeAllDescriptions>false</includeAllDescriptions>
        <includeKeyFieldIndicators>false</includeKeyFieldIndicators>
        <includeAllFieldLabels>true</includeAllFieldLabels>
        <record>
            <recordName>PSOPRDEFN</recordName>
            <excludeFields fieldName="OPERPSWD" />
            <excludeFields fieldName="PTOPERPSWDV2" />
            <excludeFields fieldName="OPERPSWDSALT" />
            <excludeFields fieldName="ENCRYPTED" />
            <parentRecordName></parentRecordName>
            <includeDescriptionsFor fieldName="LANGUAGE_C" />
            <sqlWhereClause><![CDATA[]]></sqlWhereClause>
            <criteriaFields fieldName="OPRID" fieldValue="AJORDAN" operator="=" />
        </record>
        <record>
            <recordName>PSROLEUSER</recordName>
            <parentRecordName>PSOPRDEFN</parentRecordName>
            <includeDescriptionsFor fieldName="" />
            <excludeFields fieldName="" />
            <sqlWhereClause><![CDATA[]]></sqlWhereClause>
            <joinFields parentField="OPRID" childField="ROLEUSER" />
        </record>
    </records>
    <isDebugMode>true</isDebugMode>
    <rowLimit>30</rowLimit>
</request>

Important Notes

• PsoftQL is case-sensitive.
• JSON can be very picky. If you have not worked with JSON before, I would suggest you use online JSON Linters or if you are using VSCODE I would suggest finding a JSON linter plugin.
• JSON requires some escaping of certain characters and this is especially true if you are passing complex where clauses in sqlWhereClause

PsoftQL Syntax Detail

• rowLimit - (integer, optional, Override the default 50 row limit.)
• includeFieldTypes - (boolean, optional, default: false) - Set to true if you want the PeopleSoft field types to come back. This will also output field length data. This should generally only be true during development as it causes extra processing at run time.
• includeAllDescriptions - (boolean, optional, default: false) Set to true if you want to include all field descriptions to come back. This should generally only be true during development as it causes extra processing at run time.
• pageNumber - (integer, optional) Request a different page number other than the first page
• noEffectiveDateLogic - (boolean, default: false) This parameter is optional and only applies to record with EFFDT field. Set this to true if you want to include all historical rows. The default behavior when false is that only the most effective dated row is included.
• noEffectiveStatusLogic - (boolean, default: false) This parameter is optional and only applies to records with EFF_STATUS. Set this to true if you want to include rows where EFF_STATUS = 'I'.
• effectiveDateOverride - (string, optional) This parameter is optional and only applies to record with EFFDT field. Optional date in YYYY-MM-DD format to override the current date logic used for effective dated records.
• includeAllFieldLabels (boolean, default: false) Optional boolean to ask that the default field label be included in the output in the form of {fieldName}_label. This will be the default field label at the field level. There are many times in PeopleSoft where the page field label is actually different. So this label will NOT always be the correct label that shows up on user-facing pages. This should generally only be true during development as it causes extra processing at run time.
• isDebugMode - (boolean, optional, default: false)
• records (array of objects, at least one object required) This is a required list of records you want from the database. There has to be at least one value with the parentRecordName blank to designate the ‘root parent record’. There can only be one record listed with parentRecordName blank. “,
  • recordName (string, required) The record name that you want to return data from. It must be whitelisted to you.
  • parentRecordName (string, optional) There must be one array with this blank. There can only be one record with this blank. Then any child table you are requesting must have this specified with the recordName you want to be the parent.”
  • useParentEffectiveDate (boolean, optional for child records only, default: false) If set to true this forces the parent effective date to be used instead of letting the logic build an effective dated clause. This should be set to true for any true children records where a PeopleSoft component enforces the child effective date to match the parent. Only a PeopleSoft expert will know this by looking at the data entry component.
  • doNotAutoJoinToParent (boolean, default false) - Only in rare situations do you want to set this to true. If set to true then the code will not look at common key fields between parent and child and automatically join them. The request MUST include an additional key-value pair array to tell the API how to join the parent and child. These are cases where the parent and child do not share a common field or key values.
  • joinFields (optional array of objects) - This allows you to tell the web service to join on fields between parent and child that are not obvious or where parent and child do NOT share a common field name but are truly related. OPRID = ROLEUSER, SETID = DEPTID_SETID, EMPLID = MANAGER_ID, etc. This is generally NOT required for this web service. If you are joining standard PeopleSoft tables they generally have the same field and the keys match. However, there are times when you need to force a join between fields. In that case, you can use theses. You might also need to set doNotAutoJoinToParent = true if you want complete control over how the table are joined.
    • parentField - The name of the parent field that you want to join to.
    • childField - The name of the child field you want to join to.
  • sqlWhereClause (string, optional) Optional where clause to limit the data. Do NOT include ‘where’ at the front. The record is loaded with an alias of ‘A’.
  • criteriaFields (array, optional) This is another way to pass in structured data that is used to build a where clause to limit the data. See Operator Examples for worked examples of every operator and LIKE wildcard pattern.
    • fieldName (string) Field name to limit. It must exist on the record.
    • fieldValue (string) The value to limit
    • operator (string) SQL Operator to use like equal to, greater than, etc
  • excludeFields (array of string, optional) Optional array of fields that you do not want to include in the output.
  • includeDescriptionsFor (array of string, optional) Optional array of fields that want to include the description for any codes.
  • includeKeyFieldIndicators (boolean, default: false) Optional boolean to ask for a return of fields that are defined as keys on the record. The response will be {fieldName}_isKey: true only if the field is defined as a key. If the field is not defined as a key then this attribute will NOT be present. This should generally only be true during development as it causes extra processing at run time.
  • orderByFields (array of objects, optional) Optional array of objects to specify custom ordering of results. When specified, this overrides the default key-based ordering used for pagination. Each object has the following properties:
    • fieldName (string, required) The field name to order by. Must exist on the record.
    • sortOrder (string, optional) The sort direction - either ASC (ascending) or DESC (descending). Defaults to ASC if not specified.

Date fields

PeopleSoft was developed to be database-independent. There are some built-in “META-SQL” functions that can be leveraged in the request to handle data parsing.

• %datein - can be used to get the current system date. At run time this will expand to either the Oracle or SQL Server (or other).
• %currentdatein can be used to get the current system date time.

There are several other “meta-sql” variables around data math that can be helpful.

Pagination

This web service handles pagination of large amounts of data. This web service does pagination using a dynamic “Order by” clause that is generated at run-time. The “root” record is used and drives the sort order. By default, the web service looks at the root record and uses the defined database keys to automatically generate an “order by” clause based on the keys and the field ordering of the keys on the record definition. These attributes are all accessible as meta-data inside PeopleSoft.

Custom Ordering With orderByFields

You can override the default key-based ordering by specifying the orderByFields parameter on a record. This allows you to control the sort order of your results, which is especially useful when you want to see the most recently updated records first or sort by a specific business field.

{
  "rowLimit": 10,
  "records": [
    {
      "recordName": "PSPROJECTDEFN",
      "sqlWhereClause": "LASTUPDDTTM is not null",
      "orderByFields": [
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}

You can specify multiple fields to create a compound sort order:

{
  "rowLimit": 10,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "orderByFields": [
        {
          "fieldName": "ROLESTATUS",
          "sortOrder": "ASC"
        },
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}