This is the multi-page printable view of this section. Click here to print.

Return to the regular view of this page.

PeopleSoft Query Language (PsoftQL)

This documents the detailed PsoftQL - PeopleSoft Query Language

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.

1 - Service Operation CHG_SWS_PSOFTQL

Documentation on the CHG_SWS_PSOFTQL web service. This is a more advanced web service that will will give to very limited and trusted integration partners or when prototyping new web services.

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.

2 - PsoftQL Syntax 🌟

Detailed PsoftQL syntax documentation. PsoftQL serves as the “language” of SWS

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"
        }
      ]
    }
  ]
}

Note: When using orderByFields, pagination will use your custom ordering instead of the default key-based ordering.

Next, we will look at a simple pagination example. First, we will ask for pageNumber 1 and ask for only 2 rows to be returned per page using the rowLimit parameter.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 2,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

The response coming back will look like this:

{
  "data": {
    "ACAD_ORG_TBL": {
      "objectType": "record",
      "objectName": "ACAD_ORG_TBL",
      "fields": [
        {
          "rowNumber": 1,
          "ACAD_ORG": "A&S",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Faculty of Arts and Social Sci",
          "DESCRSHORT": "Arts&SoSci",
          "DESCRFORMAL": "Faculty of Arts and Social Sciences",
          "INSTITUTION": "PSGBR",
          "CAMPUS": "MAIN",
          "MANAGER_ID": "",
          "INSTR_EDIT": "P",
          "CAMPUS_EDIT": "N",
          "SUBJECT_EDIT": "N",
          "COURSE_EDIT": "N"
        },
        {
          "rowNumber": 2,
          "ACAD_ORG": "ACAD DIV",
          "EFFDT": "1900-01-01",
          "EFF_STATUS": "A",
          "DESCR": "Academic Division",
          "DESCRSHORT": "Acad Div",
          "DESCRFORMAL": "Academic Division",
          "INSTITUTION": "PSCCS",
          "CAMPUS": "",
          "MANAGER_ID": "",
          "INSTR_EDIT": "P",
          "CAMPUS_EDIT": "N",
          "SUBJECT_EDIT": "N",
          "COURSE_EDIT": "N"
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-06.29.55.000000",
    "psftTransactionId": "8d83aaed-0433-11ee-96c1-2302b6923a7a",
    "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
}

At the bottom you will see a nextPageNumber with a value of “2”. That tells you that there are more pages to pull. To pull the next page you would request the following. The only thing that is changing is the pageNumber parameter.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 2,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

How do you know when you have hit the last page? The response will NOT have a nextPageNumber property. You can also ask for pages beyond the result set like this:

Request:

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 2,
  "pageNumber": 999,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

The response will look similar to this:

{
  "data": {
    "ACAD_ORG_TBL": {
      "objectType": "record",
      "objectName": "ACAD_ORG_TBL",
      "fields": []
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": {
    "toolsVer": "8.58.07",
    "currentUser": "CHG_SWS_UNIT_TESTER",
    "responseDTTM": "2023-06-06-06.30.58.000000",
    "psftTransactionId": "b28e2298-0433-11ee-96c1-2302b6923a7a",
    "dbname": "CS92U020",
    "dbType": "ORACLE",
    "serverTimeZone": "PST",
    "serverDirectory": "C:\\Users\\psoft\\psft\\pt\\8.58\\appserv\\APPDOM",
    "debugMessages": ""
  },
  "pageNumber": 9999,
  "apiVersion": "2023-03-14"
}

Aggregate Queries

PsoftQL supports aggregate queries that allow you to group data and apply aggregate functions like COUNT, SUM, AVG, MIN, and MAX. This is useful for reporting and analytics scenarios where you need summarized data rather than individual rows.

Aggregate Mode

To enable aggregate mode, set isAggregate: true at the top level of your request. When aggregate mode is enabled:

  • Pagination is NOT supported - The pageNumber parameter must be omitted or set to 1
  • Child records are NOT supported - Only a single root record is allowed (no parentRecordName)
  • aggregateConfig is required - You must specify which fields to group by and which aggregate functions to apply

Aggregate Syntax

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "SOME_TABLE",
      "aggregateConfig": {
        "groupByFields": ["FIELD1", "FIELD2"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "total_count"
          },
          {
            "function": "SUM(AMOUNT)",
            "outputLabel": "total_amount"
          }
        ]
      }
    }
  ]
}

Aggregate Configuration

  • isAggregate - (boolean, required for aggregate queries) Set to true at the top level to enable aggregate mode.
  • aggregateConfig - (object, required on each record when isAggregate is true) Configuration for the aggregate query. This is specified at the record level to support future join capabilities.
    • groupByFields - (array of strings, optional) Fields to group results by. If empty or omitted, aggregates are computed across all matching rows.
    • aggregateFields - (array of objects, required) At least one aggregate function definition is required.
      • function - (string, required) The SQL aggregate function with the field or expression embedded (e.g., COUNT(*), COUNT(1), SUM(AMOUNT)).
      • outputLabel - (string, optional) Custom label for the output column. If not provided, a default label will be generated.

Supported Aggregate Functions

Function ExampleDescription
COUNT(*)Count all rows
COUNT(1)Count all rows (alternative syntax)
COUNT(FIELDNAME)Count non-null values in a field
COUNT(DISTINCT FIELDNAME)Count unique values in a field
SUM(FIELDNAME)Sum numeric values (must be a numeric field)
AVG(FIELDNAME)Average numeric values (must be a numeric field)
MIN(FIELDNAME)Minimum value (works with numeric, date, and string fields)
MAX(FIELDNAME)Maximum value (works with numeric, date, and string fields)

Aggregate Example 1: Count by Role Type

Count the number of roles grouped by role type:

{
  "isAggregate": true,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          }
        ]
      }
    }
  ]
}

Response:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": ["ROLETYPE"],
      "aggregateFields": ["role_count"],
      "results": [
        {
          "ROLETYPE": "U",
          "ROLETYPE_description": "User List",
          "role_count": 245
        },
        {
          "ROLETYPE": "Q",
          "ROLETYPE_description": "Query",
          "role_count": 12
        },
        {
          "ROLETYPE": "P",
          "ROLETYPE_description": "PeopleCode",
          "role_count": 8
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "isAggregate": true,
  "totalGroups": 3,
  "apiVersion": "2024-01-15"
}

Aggregate Example 2: Multiple Aggregates with Criteria

Calculate salary statistics by department for active employees:

{
  "isAggregate": true,
  "rowLimit": 50,
  "records": [
    {
      "recordName": "JOB",
      "criteriaFields": [
        {
          "fieldName": "EMPL_STATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["DEPTID"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "employee_count"
          },
          {
            "function": "SUM(ANNUAL_RT)",
            "outputLabel": "total_salary"
          },
          {
            "function": "AVG(ANNUAL_RT)",
            "outputLabel": "avg_salary"
          },
          {
            "function": "MIN(ANNUAL_RT)",
            "outputLabel": "min_salary"
          },
          {
            "function": "MAX(ANNUAL_RT)",
            "outputLabel": "max_salary"
          }
        ]
      }
    }
  ]
}

Aggregate Example 3: Total Count Without Grouping

Get a simple count of all active roles (no grouping):

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "criteriaFields": [
        {
          "fieldName": "ROLESTATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": [],
        "aggregateFields": [
          {
            "function": "COUNT(*)",
            "outputLabel": "total_active_roles"
          }
        ]
      }
    }
  ]
}

Response:

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "aggregateResult",
      "objectName": "PSROLEDEFN",
      "groupByFields": [],
      "aggregateFields": ["total_active_roles"],
      "results": [
        {
          "total_active_roles": 265
        }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "isAggregate": true,
  "totalGroups": 1,
  "apiVersion": "2024-01-15"
}

Aggregate Response Structure

Aggregate responses differ from standard responses:

AspectStandard QueryAggregate Query
objectType"record""aggregateResult"
Data array"fields""results"
Row identifierrowNumber includedNot included
PaginationpageNumber, nextPageNumberNot present
Top-level indicatorNone"isAggregate": true
CountNone"totalGroups" count

Features That Work in Aggregate Mode

  • rowLimit - Limits the number of grouped result rows returned (useful for “top N” queries)
  • criteriaFields / sqlWhereClause - Filters rows before aggregation (equivalent to SQL WHERE clause)
  • noEffectiveDateLogic, effectiveDateOverride - Affects which rows are included in aggregation
  • includeAllDescriptions - Adds descriptions for GROUP BY field values (XLAT/prompt table translations)

Features Not Supported in Aggregate Mode

  • pageNumber > 1 - Pagination is not supported; returns an error
  • Multiple records with parentRecordName - Child records are not supported; returns an error
  • includeFieldTypes, includeAllFieldLabels, includeKeyFieldIndicators - These are ignored in aggregate mode

3 - Campus Solutions PsoftQL - Examples

PsoftQL Examples for PeopleSoft Campus Solutions (CS)

Here are some PsoftQL examples for PeopleSoft Campus Solutions. If you have some others you’ve created, please feel free to contact us and let me know, and we can update this document.

You can use these as a starting point.

Academic Setup

Institution Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "INSTITUTION_TBL"
    }
  ]
}

Academic Organization Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

Academic Organization Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

Academic Group Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_GROUP_TBL"
    }
  ]
}

Subject table

This is a setup table in PeopleSoft that defines the list of subjects.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 200,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "SUBJECT_TBL"
    }
  ]
}

Instruction Mode

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "INSTRUCT_MODE"
    }
  ]
}

Component Xlat

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "PSXLATITEM",
      "sqlWhereClause": " FIELDNAME = 'SSR_COMPONENT'"
    }
  ]
}

PeopleSoft Terms

{
    "isDebugMode": false,
    "rowLimit": 9999,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "TERM_TBL",
            "sqlWhereClause": "INSTITUTION = '{{institution}}'",
            "criteriaFields": [
                {
                    "fieldName": "TERM_BEGIN_DT",
                    "fieldValue": "2020-01-01",
                    "operator": ">="
                }
            ]
        },
        {
            "recordName": "SESSION_TBL",
            "parentRecordName": "TERM_TBL"
        }
    ]
}

Campus Table

This is a setup table in PeopleSoft that defines the list of campuses.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "CAMPUS_TBL"
    }
  ]
}

Acad Career Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_CAR_TBL"
    }
  ]
}

Meeting Pattern Setup

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "STND_MTGPAT_TBL"
    }
  ]
}

Course Catalog

This will return all active courses. It will be paginated as we are limiting it to 10 rows per page (rowLimit)

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "CRSE_CATALOG",
            "sqlWhereClause": ""
        },
        {
            "recordName": "CRSE_OFFER",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_ATTRIBUTES",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_COMPONENT",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_ATTENDANCE",
            "parentRecordName": "CRSE_COMPONENT",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CMPNT_CHRSTC",
            "parentRecordName": "CRSE_COMPONENT",
            "useParentEffectiveDate": true
        }
    ]
}

Course Catalog Change Detection (Incremental Sync)

Integration partners that mirror the PeopleSoft course catalog rarely want the whole catalog on every run. The SCC_ROW_UPD_DTTM column records the per-row last-update timestamp; compare it against the timestamp of the previous successful run to pull only records that changed since then.

{
  "isDebugMode": false,
  "rowLimit": 5,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "CRSE_CATALOG",
      "criteriaFields": [
        { "fieldName": "SCC_ROW_UPD_DTTM", "fieldValue": "1900-01-01", "operator": ">=" }
      ]
    }
  ]
}

Substitute 1900-01-01 for the timestamp of the previous successful run (in YYYY-MM-DD or YYYY-MM-DD-HH.MM.SS.NNNNNN format). On the first run, a low sentinel date returns everything; on subsequent runs, only rows whose SCC_ROW_UPD_DTTM has advanced past the previous watermark.

Grading Basis Setup


{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "GRADESCHEME_TBL"
    },
     {
      "recordName": "GRADE_BASIS_TBL",
      "parentRecordName":"GRADESCHEME_TBL"
    }
    ,
     {
      "recordName": "GRD_BASE_CHOICE",
      "parentRecordName":"GRADE_BASIS_TBL"
    }
    ,
     {
      "recordName": "GRADE_TBL",
      "parentRecordName":"GRADE_BASIS_TBL"
    }
  ]
}

Facilities

This is really from the “bottom” up and we are bringing in related tables that are defined independently.

{
  "isDebugMode": true,
  "includeFieldTypes": false,
  "rowLimit": 1,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "FACILITY_TBL"
    },
     {
      "recordName": "BLDG_TBL",
      "parentRecordName":"FACILITY_TBL",
      "sqlWhereClause": ""
    }
    ,
     {
      "recordName": "LOCATION_TBL",
      "parentRecordName":"FACILITY_TBL"
    }
    ,
     {
      "recordName": "FACILITY_CHRSTC",
      "parentRecordName":"FACILITY_TBL",
      "useParentEffectiveDate": true
    }
    ,
     {
      "recordName": "FACIL_BLACK_OUT",
      "parentRecordName":"FACILITY_TBL",
      "useParentEffectiveDate": true
    }
    ,
     {
      "recordName": "CAMPUS_LOC_TBL",
      "parentRecordName":"FACILITY_TBL",
            "useParentEffectiveDate": true
    }
  ]
}

PeopleSoft Classes

This will pull in a list of classes and we assume that you have two “path” or query string parameters to substitute in the {{institution}} and {{strm}}

{

 "isDebugMode": false,
    "rowLimit": 3,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "CLASS_TBL",
            "sqlWhereClause": " INSTITUTION = {{institution}} and STRM = {{strm}}"
        },
        {
            "recordName": "CLASS_ATTRIBUTE",
            "parentRecordName": "CLASS_TBL"
        },     
        {
            "recordName": "CLASS_MTG_PAT",
            "parentRecordName": "CLASS_TBL"
        },
        {
            "recordName": "CLASS_INSTR",
            "parentRecordName": "CLASS_MTG_PAT"
        },
        {
            "recordName": "CLASS_NOTES",
            "parentRecordName": "CLASS_TBL"
        },
        {
            "recordName": "CLASS_NOTES_TBL",
            "parentRecordName": "CLASS_NOTES"
        },
         {
            "recordName": "FACILITY_TBL",
            "parentRecordName": "CLASS_MTG_PAT"
        }
    ]
}

Student Data

Get Person Information

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PERSON",
            "sqlWhereClause": "  EMPLID = {{emplid}}"
        },
        {
            "recordName": "NAMES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "EMAIL_ADDRESSES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERSONAL_PHONE",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERS_DATA_EFFDT",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "ADDRESSES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERS_NID",
            "parentRecordName": "PERSON"
        }
    ]
}

Student Program and Plan Setup

When joining effective-dated child records to effective-dated parents, set useParentEffectiveDate: true on the child. This keeps the child’s EFFDT logic aligned with the parent row SWS selected, so you don’t end up with a child EFFDT newer or older than its parent. Without this flag the child record would run its own effective-date computation against SYSDATE, which can diverge from the parent.

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "ACAD_PROG",
            "sqlWhereClause": "  EMPLID = {{emplid}}  "
        },
        {
            "recordName": "ACAD_PLAN",
            "parentRecordName": "ACAD_PROG",
            "useParentEffectiveDate": true
        }
    ]
}

More Complete Student Program and Plan Setup

Ported from a real Modern Campus integration, this walks the full student-career hierarchy: STDNT_CAREER (careers the student is pursuing) → ACAD_PROG (each program under that career) → ACAD_PLAN (majors/minors under each program), plus STDNT_CAR_TERM for per-term standing. Note useParentEffectiveDate: true on ACAD_PLAN so its EFFDT aligns with the ACAD_PROG row above it.

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [

        {
            "recordName": "STDNT_CAREER",
            "sqlWhereClause": "  EMPLID = {{emplid}}"
        }
        ,
        {
            "recordName": "ACAD_PROG",
            "parentRecordName": "STDNT_CAREER"
        },
        {
            "recordName": "ACAD_PLAN",
            "parentRecordName": "ACAD_PROG",
            "useParentEffectiveDate": true
        }
        ,
        {
            "recordName": "STDNT_CAR_TERM",
            "parentRecordName": "STDNT_CAREER"
        }
    ]
}

Get A Students Service Indicators

{
    "isDebugMode": "{{debugMode}}",
    "includeFieldTypes": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "SRVC_IND_DATA",
        "sqlWhereClause": "EMPLID={{emplid}} and (SCC_SI_END_DT is null or SCC_SI_END_DT > %currentdatein) and (SCC_SI_END_TERM = '' OR NOT EXISTS ( SELECT 1 FROM PS_TERM_TBL B WHERE A.SCC_SI_END_TERM = B.STRM AND B.TERM_BEGIN_DT < %CURRENTDATEIN))",
        "includeDescriptionsFor": [
        "SRVC_IND_CD"
        ]
    },
    {
        "recordName": "SRVC_IND_CD_TBL",
        "parentRecordName": "SRVC_IND_DATA"
    },
    {
        "recordName": "SERVICE_IMPACT",
        "parentRecordName": "SRVC_IND_CD_TBL"
    },
    {
        "recordName": "SRVC_IN_RSN_TBL",
        "parentRecordName": "SRVC_IND_DATA"
    }
    ]
}

Get a Student’s Schedule for a Term

The student-schedule join is a textbook case for explicit joinFields. STDNT_ENRL and CLASS_TBL share three key fields (CLASS_NBR, STRM, INSTITUTION), and the default auto-join would attempt to match on every common key, which produces the wrong cardinality. Setting doNotAutoJoinToParent: true disables the default behavior so SWS uses only the joinFields you supply.


{
    "isDebugMode": "{{debugMode}}",
    "includeFieldTypes": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "STDNT_ENRL",
        "sqlWhereClause": "EMPLID= {{emplid}} AND STRM = {{strm}} AND STDNT_ENRL_STATUS = 'E'"
    },
    {
        "recordName": "CLASS_TBL",
        "parentRecordName": "STDNT_ENRL",
        "doNotAutoJoinToParent": true,
        "joinFields": [
        {
            "parentField": "CLASS_NBR",
            "childField": "CLASS_NBR"
        },
        {
            "parentField": "STRM",
            "childField": "STRM"
        },
        {
            "parentField": "INSTITUTION",
            "childField": "INSTITUTION"
        }
        ]
    },
    {
        "recordName": "CLASS_MTG_PAT",
        "parentRecordName": "CLASS_TBL"
    }
    ,
    {
        "recordName": "CAMPUS_MTG",
        "parentRecordName": "CLASS_TBL"
    }

]
}

4 - PsoftQL Operators & Filtering Examples

Examples showing every criteriaFields operator PsoftQL supports, LIKE wildcard patterns, and combined filtering with sqlWhereClause

PsoftQL’s criteriaFields clause lets you filter rows with any of the operators SQL supports for scalar comparisons. The examples below demonstrate each operator, the two wildcards supported by LIKE, how multiple criteria combine with implicit AND, and how criteriaFields can coexist with a free-form sqlWhereClause.

All examples here are drawn verbatim from the integration test suite in doc-internal/test/test-pql-criteria-operators.http. Each test is numbered to match the source file.

Supported Operators

OperatorMeaningNotes
=EqualDefault operator if omitted
<Less thanString or numeric comparison, depends on field type
>Greater than
<=Less than or equal
>=Greater than or equal
<>Not equalEquivalent to !=
!=Not equal (alternate syntax)Equivalent to <>
LIKEPattern match% matches any substring, _ matches exactly one character
INMembershipSupplied via fieldValues array (see syntax reference for details)

Multiple entries in the criteriaFields array are joined with AND. For OR logic, move the condition into sqlWhereClause.

Test 1: Greater Than (>) — PSROLEDEFN by last-update timestamp

Use > when you want rows strictly after a threshold. A common use case is fetching roles that have been edited since your last sync.

Request

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "rowLimit": 5,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "LASTUPDDTTM",
                    "fieldValue": "2020-01-01",
                    "operator": ">"
                }
            ]
        }
    ]
}

Response

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Every returned row has LASTUPDDTTM > '2020-01-01'.

Test 2: Less Than (<) — PSXLATITEM by field name

Use < for rows strictly before a threshold. This example also shows how noEffectiveDateLogic and noEffectiveStatusLogic flags disable the default SWS filters — useful when you want raw rows from tables that would otherwise be filtered to the current effective row or active status.

Request

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "noEffectiveDateLogic": true,
    "noEffectiveStatusLogic": true,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSXLATITEM",
            "criteriaFields": [
                {
                    "fieldName": "FIELDNAME",
                    "fieldValue": "D",
                    "operator": "<"
                }
            ]
        }
    ]
}

Response

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSXLATITEM": {
      "objectType": "record",
      "objectName": "PSXLATITEM",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Tests 3 & 4: Greater-or-Equal (>=) and Less-or-Equal (<=) — CRSE_CATALOG range

Range filters use the inclusive comparison operators. The pair below walks the Course Catalog: first everything from CRSE_ID = '000100' onward, then everything up to and including CRSE_ID = '000010'.

Request — Test 3: >=

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "rowLimit": 5,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "CRSE_CATALOG",
            "criteriaFields": [
                {
                    "fieldName": "CRSE_ID",
                    "fieldValue": "000100",
                    "operator": ">="
                }
            ]
        }
    ]
}

Request — Test 4: <=

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "CRSE_CATALOG",
            "criteriaFields": [
                {
                    "fieldName": "CRSE_ID",
                    "fieldValue": "000010",
                    "operator": "<="
                }
            ]
        }
    ]
}

Response

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "CRSE_CATALOG": {
      "objectType": "record",
      "objectName": "CRSE_CATALOG",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Tests 5 & 6: Not-Equal (<> and !=) — PSROLEDEFN by role status

PsoftQL accepts both <> and != for inequality. They generate identical SQL — pick whichever reads more naturally for your team.

Request — Test 5: <>

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "ROLESTATUS",
                    "fieldValue": "A",
                    "operator": "<>"
                }
            ]
        }
    ]
}

Request — Test 6: !=

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "ROLESTATUS",
                    "fieldValue": "I",
                    "operator": "!="
                }
            ]
        }
    ]
}

Response

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Test 8: Multiple Criteria (AND logic) — PSOPRDEFN

When you supply more than one entry in criteriaFields, PsoftQL joins them with AND. The example below finds users whose OPRID begins with A and whose account is locked, while using excludeFields to strip sensitive password columns from the response.

Request

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": true,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSOPRDEFN",
            "excludeFields": ["OPERPSWD", "PTOPERPSWDV2", "OPERPSWDSALT", "ENCRYPTED"],
            "criteriaFields": [
                {
                    "fieldName": "OPRID",
                    "fieldValue": "A%",
                    "operator": "LIKE"
                },
                {
                    "fieldName": "ACCTLOCK",
                    "fieldValue": "1",
                    "operator": "="
                }
            ]
        }
    ]
}

Response

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSOPRDEFN": {
      "objectType": "record",
      "objectName": "PSOPRDEFN",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Tests 9, 10, 11: LIKE Wildcard Patterns

PsoftQL passes LIKE patterns through to SQL, so the standard wildcards apply:

  • % matches any sequence of zero or more characters
  • _ matches exactly one character

The three examples below demonstrate each placement pattern: trailing, surrounding, and single-character.

Test 9: Leading wildcard (%ADMIN) — roles ending in ADMIN

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "ROLENAME",
                    "fieldValue": "%ADMIN",
                    "operator": "LIKE"
                }
            ]
        }
    ]
}

Test 10: Wildcards on both sides (%USER%) — roles containing USER

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSROLEDEFN",
            "criteriaFields": [
                {
                    "fieldName": "ROLENAME",
                    "fieldValue": "%USER%",
                    "operator": "LIKE"
                }
            ]
        }
    ]
}

Test 11: Single-character wildcard (_lient) — 6-letter XLATs ending in “lient”

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "noEffectiveDateLogic": true,
    "noEffectiveStatusLogic": true,
    "rowLimit": 20,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PSXLATITEM",
            "criteriaFields": [
                {
                    "fieldName": "XLATLONGNAME",
                    "fieldValue": "_lient",
                    "operator": "LIKE"
                }
            ]
        }
    ]
}

Response (common shape)

HTTP/1.1 200 OK
content-type: application/json; encoding=UTF-8

{
  "data": {
    "PSROLEDEFN": {
      "objectType": "record",
      "objectName": "PSROLEDEFN",
      "fields": [
        { "...": "response-body-to-be-captured-from-live-endpoint" }
      ]
    }
  },
  "responseCode": 200,
  "errorMessages": "",
  "meta": { "...": "..." },
  "pageNumber": 1,
  "apiVersion": "2023-03-14"
}

Test 12: Combining criteriaFields with sqlWhereClause — TERM_TBL

You can mix structured criteria with a free-form SQL clause. They are joined with AND. Use sqlWhereClause for expressions that don’t fit the scalar comparison model — subqueries, functions, OR logic — and keep criteriaFields for straightforward column-value checks so the intent is obvious.

Request

POST /PSIGW/RESTListeningConnector/PSFT_CS/CHG_SWS_PSOFTQL HTTP/1.1
Authorization: Basic ...redacted...
Content-Type: application/json

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "TERM_TBL",
            "sqlWhereClause": "ACAD_CAREER = 'CNED'",
            "criteriaFields": [
                {
                    "fieldName": "INSTITUTION",
                    "fieldValue": "PSUNV",
                    "operator": "="
                }
            ]
        }
    ]
}

Where to go next

5 - PeopleTools PsoftQL - Examples

PsoftQL Examples for PeopleSoft PeopleTools Tables

In this section we will show detailed PsoftQL examples of how to extract PeopleTools information using SWS PsoftQL Syntax.

Users and Their Roles

This example shows how to pull PSOPRDEFN and PSROLEUSER information and join them together to get a list of users and their roles. We also exclude some fields that are not needed. Additionally, we include the descriptions for the LANGUAGE_CD and CURRENCY_CD fields.

{
    "isDebugMode": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": false,
    "includeKeyFieldIndicators": false,
    "includeAllFieldLabels": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "PSOPRDEFN",
        "includeDescriptionsFor": ["LANGUAGE_CD", "CURRENCY_CD"],
        "excludeFields": ["VERSION", "EXPENT", "OPERPSWD", "PTOPERPSWDV2", "OPERPSWDSALT", "ENCRYPTED"],
        "criteriaFields": [

        ]
    },
    {
        "recordName": "PSROLEUSER",
        "parentRecordName": "PSOPRDEFN",
        "doNotAutoJoinToParent": true,
        "joinFields": [
            {"parentField": "OPRID", "childField": "ROLEUSER"}
        ],
        "excludeFields": ["ROLEUSER"]
    }

    ]
}

PeopleSoft Record Definitions

Export PeopleSoft Record Definitions and their fields. This example shows how to pull PSRECDEFN, PSRECFIELDDB, and PSDBFIELD information and join them together to get a list of record definitions and their fields.


{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "includeAllDescriptions": true,
  "includeKeyFieldIndicators": false,
  "includeAllFieldLabels": false,
  "rowLimit": 2,
  "records": [
    {
      "recordName": "PSRECDEFN",
      "includeDescriptionsFor": []
    },
    {
      "recordName": "PSRECFIELDDB",
      "parentRecordName": "PSRECDEFN"
    },
    {
      "recordName": "PSDBFIELD",
      "parentRecordName": "PSRECFIELDDB"
    }
  ]
}

Recently Modified Projects

This example shows how to use orderByFields to get the most recently modified App Designer projects. By ordering by LASTUPDDTTM in descending order, you get the most recently updated projects first.

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

Recently Modified Roles

Get the most recently modified PeopleSoft roles, sorted by last update date descending. This is useful for auditing recent security changes.

{
  "isDebugMode": false,
  "rowLimit": 20,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": ["RECNAME", "FIELDNAME", "PC_EVENT_TYPE", "QRYNAME_SEC", "VERSION", "QRYNAME"],
      "orderByFields": [
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}

Roles Sorted by Status and Last Update

This example demonstrates compound sorting with multiple orderByFields. Roles are sorted first by status (Active roles first alphabetically), then by last update date with most recent first.

{
  "isDebugMode": false,
  "rowLimit": 50,
  "includeAllDescriptions": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "excludeFields": ["RECNAME", "FIELDNAME", "PC_EVENT_TYPE", "QRYNAME_SEC", "VERSION", "QRYNAME"],
      "orderByFields": [
        {
          "fieldName": "ROLESTATUS",
          "sortOrder": "ASC"
        },
        {
          "fieldName": "LASTUPDDTTM",
          "sortOrder": "DESC"
        }
      ]
    }
  ]
}

6 - Aggregate SQL Translation Examples

Internal reference showing how PsoftQL aggregate requests translate to SQL, with real response bodies for the most-used patterns

This document shows how PsoftQL aggregate requests translate to SQL queries. It is both an internal reference for implementation and a guide for callers assembling aggregate requests. Examples 4, 5, 6, and 9 include sample response bodies drawn from the integration test suite in doc-internal/test/test-pql-aggregate.http.

Example 1: Simple COUNT with GROUP BY

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS role_count
FROM PS_PSROLEDEFN A
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

Example 2: COUNT with Criteria (WHERE clause)

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "criteriaFields": [
        {
          "fieldName": "ROLESTATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "active_role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS active_role_count
FROM PS_PSROLEDEFN A
WHERE A.ROLESTATUS = 'A'
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

Example 3: Multiple GROUP BY Fields

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE", "ROLESTATUS"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    A.ROLESTATUS,
    COUNT(1) AS role_count
FROM PS_PSROLEDEFN A
GROUP BY A.ROLETYPE, A.ROLESTATUS
ORDER BY A.ROLETYPE, A.ROLESTATUS

Example 4: Multiple Aggregate Functions

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "JOB",
      "criteriaFields": [
        {
          "fieldName": "EMPL_STATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["DEPTID"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "employee_count"
          },
          {
            "function": "SUM(ANNUAL_RT)",
            "outputLabel": "total_salary"
          },
          {
            "function": "AVG(ANNUAL_RT)",
            "outputLabel": "avg_salary"
          },
          {
            "function": "MIN(ANNUAL_RT)",
            "outputLabel": "min_salary"
          },
          {
            "function": "MAX(ANNUAL_RT)",
            "outputLabel": "max_salary"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.DEPTID,
    COUNT(1) AS employee_count,
    SUM(A.ANNUAL_RT) AS total_salary,
    AVG(A.ANNUAL_RT) AS avg_salary,
    MIN(A.ANNUAL_RT) AS min_salary,
    MAX(A.ANNUAL_RT) AS max_salary
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID

Example 5: No GROUP BY (Total Aggregation)

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "aggregateConfig": {
        "groupByFields": [],
        "aggregateFields": [
          {
            "function": "COUNT(*)",
            "outputLabel": "total_roles"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    COUNT(*) AS total_roles
FROM PS_PSROLEDEFN A

Note: No GROUP BY clause, no ORDER BY clause when there are no groupByFields.

Example 6: COUNT DISTINCT

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "STDNT_ENRL",
      "criteriaFields": [
        {
          "fieldName": "STDNT_ENRL_STATUS",
          "fieldValue": "E",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["INSTITUTION", "STRM"],
        "aggregateFields": [
          {
            "function": "COUNT(DISTINCT EMPLID)",
            "outputLabel": "unique_students"
          },
          {
            "function": "COUNT(1)",
            "outputLabel": "total_enrollments"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.INSTITUTION,
    A.STRM,
    COUNT(DISTINCT A.EMPLID) AS unique_students,
    COUNT(1) AS total_enrollments
FROM PS_STDNT_ENRL A
WHERE A.STDNT_ENRL_STATUS = 'E'
GROUP BY A.INSTITUTION, A.STRM
ORDER BY A.INSTITUTION, A.STRM

Example 7: With Effective Date Logic

When noEffectiveDateLogic is false (default) and the record has EFFDT:

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL",
      "aggregateConfig": {
        "groupByFields": ["INSTITUTION"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "org_count"
          }
        ]
      }
    }
  ]
}

Generated SQL (Oracle)

SELECT
    A.INSTITUTION,
    COUNT(1) AS org_count
FROM PS_ACAD_ORG_TBL A
WHERE A.EFFDT = (
    SELECT MAX(A2.EFFDT)
    FROM PS_ACAD_ORG_TBL A2
    WHERE A2.ACAD_ORG = A.ACAD_ORG
    AND A2.EFFDT <= SYSDATE
)
GROUP BY A.INSTITUTION
ORDER BY A.INSTITUTION

Example 8: With rowLimit (Top N)

PsoftQL Request

{
  "isAggregate": true,
  "rowLimit": 10,
  "records": [
    {
      "recordName": "JOB",
      "criteriaFields": [
        {
          "fieldName": "EMPL_STATUS",
          "fieldValue": "A",
          "operator": "="
        }
      ],
      "aggregateConfig": {
        "groupByFields": ["DEPTID"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "employee_count"
          }
        ]
      }
    }
  ]
}

Generated SQL (Oracle 12c+)

SELECT
    A.DEPTID,
    COUNT(1) AS employee_count
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID
FETCH FIRST 10 ROWS ONLY

Generated SQL (SQL Server)

SELECT TOP 10
    A.DEPTID,
    COUNT(1) AS employee_count
FROM PS_JOB A
WHERE A.EMPL_STATUS = 'A'
GROUP BY A.DEPTID
ORDER BY A.DEPTID

Example 9: With sqlWhereClause

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEDEFN",
      "sqlWhereClause": "A.ROLENAME LIKE 'SA%' AND A.ROLESTATUS = 'A'",
      "aggregateConfig": {
        "groupByFields": ["ROLETYPE"],
        "aggregateFields": [
          {
            "function": "COUNT(1)",
            "outputLabel": "role_count"
          },
          {
            "function": "MAX(LASTUPDDTTM)",
            "outputLabel": "last_updated"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLETYPE,
    COUNT(1) AS role_count,
    MAX(A.LASTUPDDTTM) AS last_updated
FROM PS_PSROLEDEFN A
WHERE A.ROLENAME LIKE 'SA%' AND A.ROLESTATUS = 'A'
GROUP BY A.ROLETYPE
ORDER BY A.ROLETYPE

PsoftQL Request

{
  "isAggregate": true,
  "records": [
    {
      "recordName": "PSROLEUSER",
      "aggregateConfig": {
        "groupByFields": ["ROLENAME"],
        "aggregateFields": [
          {
            "function": "COUNT(DISTINCT ROLEUSER)",
            "outputLabel": "user_count"
          }
        ]
      }
    }
  ]
}

Generated SQL

SELECT
    A.ROLENAME,
    COUNT(DISTINCT A.ROLEUSER) AS user_count
FROM PS_PSROLEUSER A
GROUP BY A.ROLENAME
ORDER BY A.ROLENAME

Implementation Notes

SQL Generation Pattern

SELECT
    {groupByFields with A. prefix},
    {aggregateFields with A. prefix on field references}
FROM PS_{recordName} A
WHERE {criteriaFields converted to SQL}
    {AND sqlWhereClause if provided}
    {AND effective date logic if applicable}
GROUP BY {groupByFields with A. prefix}
ORDER BY {groupByFields with A. prefix}
{FETCH FIRST rowLimit ROWS ONLY if rowLimit provided}

Field Prefix Handling

The aggregate function string needs to be parsed to add the A. alias prefix to field names:

InputOutput
COUNT(*)COUNT(*) (no change)
COUNT(1)COUNT(1) (no change)
COUNT(EMPLID)COUNT(A.EMPLID)
COUNT(DISTINCT EMPLID)COUNT(DISTINCT A.EMPLID)
SUM(ANNUAL_RT)SUM(A.ANNUAL_RT)
AVG(SALARY)AVG(A.SALARY)
MIN(HIRE_DT)MIN(A.HIRE_DT)
MAX(LASTUPDDTTM)MAX(A.LASTUPDDTTM)

Validation Checks

  1. If isAggregate: true and pageNumber > 1 → Error
  2. If isAggregate: true and multiple records with parentRecordName → Error
  3. If aggregateConfig missing on the record when isAggregate: true → Error
  4. If aggregateFields empty when isAggregate: true → Error
  5. Validate field names in groupByFields exist on the record
  6. Parse and validate field names in function expressions exist on the record (except * and literals like 1)

7 - PsoftQL Validator

Validate your PsoftQL JSON or XML syntax against the official schema

Paste your PsoftQL JSON or XML below to validate it against the official schema. The format is auto-detected and validation runs automatically as you type.

1
Paste PsoftQL JSON or XML above to validate.