PsoftQL Syntax 🌟

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

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.
      • 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.

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. 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. Since the web service is always creating an order by clause there is no way to pass in a custom where clause.

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