PeopleTools PsoftQL - Examples
Categories:
In this section we will show detailed PsoftQL examples of how to extract PeopleTools information using SWS PsoftQL Syntax. Each example includes a sample response so you can see the field shape before deciding which to wire up.
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"]
}
]
}
Sample response — one user with their nested role list. LANGUAGE_CD_description appears because LANGUAGE_CD was listed in includeDescriptionsFor; CURRENCY_CD_description would appear too if the field had a non-blank value. The child PSROLEUSER.fields array carries each role assignment for the parent OPRID, joined via OPRID = ROLEUSER:
{
"data": {
"PSOPRDEFN": {
"objectType": "record",
"objectName": "PSOPRDEFN",
"fields": [
{
"rowNumber": 1,
"OPRID": "AADAMS",
"OPRDEFNDESC": "Andrew Adams",
"LANGUAGE_CD": "ENG",
"LANGUAGE_CD_description": "English",
"CURRENCY_CD": "",
"LASTUPDDTTM": "2021-09-08-05.01.17.440969",
"LASTUPDOPRID": "PPLSOFT",
"PSROLEUSER": {
"objectType": "record",
"objectName": "PSROLEUSER",
"fields": [
{ "ROLENAME": "CS - Student", "DYNAMIC_SW": "N" },
{ "ROLENAME": "CS - Student Applicant", "DYNAMIC_SW": "N" },
{ "ROLENAME": "EOPP_USER", "DYNAMIC_SW": "N" },
{ "ROLENAME": "PeopleSoft User", "DYNAMIC_SW": "N" },
{ "ROLENAME": "Standard Query Permissions", "DYNAMIC_SW": "N" }
]
}
}
]
}
},
"responseCode": 200,
"nextPageNumber": 2
}
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"
}
]
}
Sample response (heavily trimmed — one parent record, one child field, one grand-child field). The full response for two records is ~60 KB because every PSRECDEFN explodes into dozens of PSRECFIELDDB rows, each with its own PSDBFIELD child. Filter aggressively with criteriaFields on the parent (RECNAME = 'YOUR_RECORD') when you only need one record’s structure:
{
"data": {
"PSRECDEFN": {
"objectType": "record",
"objectName": "PSRECDEFN",
"fields": [
{
"rowNumber": 1,
"RECNAME": "PSROLEDEFN",
"RECTYPE": 0,
"PSRECFIELDDB": {
"objectType": "record",
"objectName": "PSRECFIELDDB",
"fields": [
{
"RECNAME": "PSROLEDEFN",
"FIELDNAME": "ALLOWLOOKUP",
"FIELDNUM": 17,
"DEFFIELDNAME": "Y",
"USEEDIT": 8396800,
"PSDBFIELD": {
"objectType": "record",
"objectName": "PSDBFIELD",
"fields": [
{
"FIELDNAME": "ALLOWLOOKUP",
"FIELDTYPE": 0,
"LENGTH": 1,
"OBJECTOWNERID": "PPT",
"DESCRLONG": "Allow Recipient Lookup"
}
]
}
}
]
}
}
]
}
},
"responseCode": 200
}
This three-level nest is a useful pattern for “schema discovery” integrations — building a client-side model of every field on a record, including its underlying type from PSDBFIELD.
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"
}
]
}
]
}
Sample response (top 3) — note the timestamps step backward as you walk the array, confirming the DESC order is applied. This pattern is the standard “audit/recent-changes” query for any record that carries LASTUPDDTTM:
{
"data": {
"PSPROJECTDEFN": {
"objectType": "record",
"objectName": "PSPROJECTDEFN",
"fields": [
{ "rowNumber": 1, "PROJECTNAME": "CHG_CI_TEST", "LASTUPDDTTM": "2026-05-08-23.22.42.036849" },
{ "rowNumber": 2, "PROJECTNAME": "CMALEK_NODE", "LASTUPDDTTM": "2026-04-23-02.15.14.806879" },
{ "rowNumber": 3, "PROJECTNAME": "CHG_TOKEN_GEN_POC", "LASTUPDDTTM": "2026-04-22-16.53.29.852062" }
]
}
},
"responseCode": 200,
"nextPageNumber": 2
}
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"
}
]
}
]
}