Campus Solutions PsoftQL - Examples

PsoftQL Examples for PeopleSoft Campus Solutions (CS)
Tags:
Categories:

Here are some PsoftQL examples for PeopleSoft Campus Solutions. If you have some others you’ve created, please feel free to contact us and let me know, and we can update this document.

You can use these as a starting point.

Academic Setup

Institution Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "INSTITUTION_TBL"
    }
  ]
}

Academic Organization Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

Academic Organization Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_ORG_TBL"
    }
  ]
}

Academic Group Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_GROUP_TBL"
    }
  ]
}

Subject table

This is a setup table in PeopleSoft that defines the list of subjects.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 200,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "SUBJECT_TBL"
    }
  ]
}

Instruction Mode

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "INSTRUCT_MODE"
    }
  ]
}

Component Xlat

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "PSXLATITEM",
      "sqlWhereClause": " FIELDNAME = 'SSR_COMPONENT'"
    }
  ]
}

PeopleSoft Terms

{
    "isDebugMode": false,
    "rowLimit": 9999,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "TERM_TBL",
            "sqlWhereClause": "INSTITUTION = '{{institution}}'",
            "criteriaFields": [
                {
                    "fieldName": "TERM_BEGIN_DT",
                    "fieldValue": "2020-01-01",
                    "operator": ">="
                }
            ]
        },
        {
            "recordName": "SESSION_TBL",
            "parentRecordName": "TERM_TBL"
        }
    ]
}

Campus Table

This is a setup table in PeopleSoft that defines the list of campuses.

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "CAMPUS_TBL"
    }
  ]
}

Acad Career Table

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "ACAD_CAR_TBL"
    }
  ]
}

Meeting Pattern Setup

{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 30,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "STND_MTGPAT_TBL"
    }
  ]
}

Course Catalog

This will return all active courses. It will be paginated as we are limiting it to 10 rows per page (rowLimit)

{
    "isDebugMode": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "CRSE_CATALOG",
            "sqlWhereClause": ""
        },
        {
            "recordName": "CRSE_OFFER",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_ATTRIBUTES",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_COMPONENT",
            "parentRecordName": "CRSE_CATALOG",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CRSE_ATTENDANCE",
            "parentRecordName": "CRSE_COMPONENT",
            "useParentEffectiveDate": true
        },
        {
            "recordName": "CMPNT_CHRSTC",
            "parentRecordName": "CRSE_COMPONENT",
            "useParentEffectiveDate": true
        }
    ]
}

Course Catalog Change Detection (Incremental Sync)

Integration partners that mirror the PeopleSoft course catalog rarely want the whole catalog on every run. The SCC_ROW_UPD_DTTM column records the per-row last-update timestamp; compare it against the timestamp of the previous successful run to pull only records that changed since then.

{
  "isDebugMode": false,
  "rowLimit": 5,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "CRSE_CATALOG",
      "criteriaFields": [
        { "fieldName": "SCC_ROW_UPD_DTTM", "fieldValue": "1900-01-01", "operator": ">=" }
      ]
    }
  ]
}

Substitute 1900-01-01 for the timestamp of the previous successful run (in YYYY-MM-DD or YYYY-MM-DD-HH.MM.SS.NNNNNN format). On the first run, a low sentinel date returns everything; on subsequent runs, only rows whose SCC_ROW_UPD_DTTM has advanced past the previous watermark.

Grading Basis Setup


{
  "isDebugMode": false,
  "includeFieldTypes": false,
  "rowLimit": 20,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "GRADESCHEME_TBL"
    },
     {
      "recordName": "GRADE_BASIS_TBL",
      "parentRecordName":"GRADESCHEME_TBL"
    }
    ,
     {
      "recordName": "GRD_BASE_CHOICE",
      "parentRecordName":"GRADE_BASIS_TBL"
    }
    ,
     {
      "recordName": "GRADE_TBL",
      "parentRecordName":"GRADE_BASIS_TBL"
    }
  ]
}

Facilities

This is really from the “bottom” up and we are bringing in related tables that are defined independently.

{
  "isDebugMode": true,
  "includeFieldTypes": false,
  "rowLimit": 1,
  "pageNumber": 1,
  "records": [
    {
      "recordName": "FACILITY_TBL"
    },
     {
      "recordName": "BLDG_TBL",
      "parentRecordName":"FACILITY_TBL",
      "sqlWhereClause": ""
    }
    ,
     {
      "recordName": "LOCATION_TBL",
      "parentRecordName":"FACILITY_TBL"
    }
    ,
     {
      "recordName": "FACILITY_CHRSTC",
      "parentRecordName":"FACILITY_TBL",
      "useParentEffectiveDate": true
    }
    ,
     {
      "recordName": "FACIL_BLACK_OUT",
      "parentRecordName":"FACILITY_TBL",
      "useParentEffectiveDate": true
    }
    ,
     {
      "recordName": "CAMPUS_LOC_TBL",
      "parentRecordName":"FACILITY_TBL",
            "useParentEffectiveDate": true
    }
  ]
}

PeopleSoft Classes

This will pull in a list of classes and we assume that you have two “path” or query string parameters to substitute in the {{institution}} and {{strm}}

{

 "isDebugMode": false,
    "rowLimit": 3,
    "pageNumber": 1,
    "noEffectiveDateLogic": false,
    "noEffectiveStatusLogic": false,
    "includeFieldTypes": false,
    "includeAllDescriptions": true,
    "records": [
        {
            "recordName": "CLASS_TBL",
            "sqlWhereClause": " INSTITUTION = {{institution}} and STRM = {{strm}}"
        },
        {
            "recordName": "CLASS_ATTRIBUTE",
            "parentRecordName": "CLASS_TBL"
        },     
        {
            "recordName": "CLASS_MTG_PAT",
            "parentRecordName": "CLASS_TBL"
        },
        {
            "recordName": "CLASS_INSTR",
            "parentRecordName": "CLASS_MTG_PAT"
        },
        {
            "recordName": "CLASS_NOTES",
            "parentRecordName": "CLASS_TBL"
        },
        {
            "recordName": "CLASS_NOTES_TBL",
            "parentRecordName": "CLASS_NOTES"
        },
         {
            "recordName": "FACILITY_TBL",
            "parentRecordName": "CLASS_MTG_PAT"
        }
    ]
}

Student Data

Get Person Information

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "PERSON",
            "sqlWhereClause": "  EMPLID = {{emplid}}"
        },
        {
            "recordName": "NAMES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "EMAIL_ADDRESSES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERSONAL_PHONE",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERS_DATA_EFFDT",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "ADDRESSES",
            "parentRecordName": "PERSON"
        },
        {
            "recordName": "PERS_NID",
            "parentRecordName": "PERSON"
        }
    ]
}

Student Program and Plan Setup

When joining effective-dated child records to effective-dated parents, set useParentEffectiveDate: true on the child. This keeps the child’s EFFDT logic aligned with the parent row SWS selected, so you don’t end up with a child EFFDT newer or older than its parent. Without this flag the child record would run its own effective-date computation against SYSDATE, which can diverge from the parent.

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [
        {
            "recordName": "ACAD_PROG",
            "sqlWhereClause": "  EMPLID = {{emplid}}  "
        },
        {
            "recordName": "ACAD_PLAN",
            "parentRecordName": "ACAD_PROG",
            "useParentEffectiveDate": true
        }
    ]
}

More Complete Student Program and Plan Setup

Ported from a real Modern Campus integration, this walks the full student-career hierarchy: STDNT_CAREER (careers the student is pursuing) → ACAD_PROG (each program under that career) → ACAD_PLAN (majors/minors under each program), plus STDNT_CAR_TERM for per-term standing. Note useParentEffectiveDate: true on ACAD_PLAN so its EFFDT aligns with the ACAD_PROG row above it.

{
    "isDebugMode": "{{debugMode}}",
    "rowLimit": 100,
    "pageNumber": 1,
    "records": [

        {
            "recordName": "STDNT_CAREER",
            "sqlWhereClause": "  EMPLID = {{emplid}}"
        }
        ,
        {
            "recordName": "ACAD_PROG",
            "parentRecordName": "STDNT_CAREER"
        },
        {
            "recordName": "ACAD_PLAN",
            "parentRecordName": "ACAD_PROG",
            "useParentEffectiveDate": true
        }
        ,
        {
            "recordName": "STDNT_CAR_TERM",
            "parentRecordName": "STDNT_CAREER"
        }
    ]
}

Get A Students Service Indicators

{
    "isDebugMode": "{{debugMode}}",
    "includeFieldTypes": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "SRVC_IND_DATA",
        "sqlWhereClause": "EMPLID={{emplid}} and (SCC_SI_END_DT is null or SCC_SI_END_DT > %currentdatein) and (SCC_SI_END_TERM = '' OR NOT EXISTS ( SELECT 1 FROM PS_TERM_TBL B WHERE A.SCC_SI_END_TERM = B.STRM AND B.TERM_BEGIN_DT < %CURRENTDATEIN))",
        "includeDescriptionsFor": [
        "SRVC_IND_CD"
        ]
    },
    {
        "recordName": "SRVC_IND_CD_TBL",
        "parentRecordName": "SRVC_IND_DATA"
    },
    {
        "recordName": "SERVICE_IMPACT",
        "parentRecordName": "SRVC_IND_CD_TBL"
    },
    {
        "recordName": "SRVC_IN_RSN_TBL",
        "parentRecordName": "SRVC_IND_DATA"
    }
    ]
}

Get a Student’s Schedule for a Term

The student-schedule join is a textbook case for explicit joinFields. STDNT_ENRL and CLASS_TBL share three key fields (CLASS_NBR, STRM, INSTITUTION), and the default auto-join would attempt to match on every common key, which produces the wrong cardinality. Setting doNotAutoJoinToParent: true disables the default behavior so SWS uses only the joinFields you supply.


{
    "isDebugMode": "{{debugMode}}",
    "includeFieldTypes": false,
    "rowLimit": 10,
    "pageNumber": 1,
    "records": [
    {
        "recordName": "STDNT_ENRL",
        "sqlWhereClause": "EMPLID= {{emplid}} AND STRM = {{strm}} AND STDNT_ENRL_STATUS = 'E'"
    },
    {
        "recordName": "CLASS_TBL",
        "parentRecordName": "STDNT_ENRL",
        "doNotAutoJoinToParent": true,
        "joinFields": [
        {
            "parentField": "CLASS_NBR",
            "childField": "CLASS_NBR"
        },
        {
            "parentField": "STRM",
            "childField": "STRM"
        },
        {
            "parentField": "INSTITUTION",
            "childField": "INSTITUTION"
        }
        ]
    },
    {
        "recordName": "CLASS_MTG_PAT",
        "parentRecordName": "CLASS_TBL"
    }
    ,
    {
        "recordName": "CAMPUS_MTG",
        "parentRecordName": "CLASS_TBL"
    }

]
}