Service Operation CHG_SWS_PSOFTQL

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

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

Using the Service

Unlike the standard GET Service where the request syntax is hidden in the configuration table, this web service requires the client to know the PeopleSoft record structure and how to craft a request. The service takes a PsoftQL Synxtax 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.## 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 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.