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.

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

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 are 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 of 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 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 a 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 in-active.