PeopleTools PsoftQL - Examples

PsoftQL Examples for PeopleSoft PeopleTools Tables

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