Campus Solutions PsoftQL - Examples
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. Setup-level examples (Institution, Subject, XLAT, etc.) below include a response sample so you can see the field shape before you wire one up. The parameterized examples (Get Person, Class Schedule, Service Indicators) are intentionally request-only — they depend on tenant-specific {{emplid}}, {{institution}}, and {{strm}} values you’ll substitute at integration time.
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"
}
]
}
Sample response — ACAD_ORG_TBL is effective-dated, so by default each row is the currently-effective version per ACAD_ORG. First two rows shown:
{
"data": {
"ACAD_ORG_TBL": {
"objectType": "record",
"objectName": "ACAD_ORG_TBL",
"fields": [
{
"rowNumber": 1,
"ACAD_ORG": "A&S",
"EFFDT": "1900-01-01",
"EFF_STATUS": "A",
"DESCR": "Faculty of Arts and Social Sci",
"DESCRSHORT": "Arts&SoSci",
"DESCRFORMAL": "Faculty of Arts and Social Sciences",
"INSTITUTION": "PSGBR",
"CAMPUS": "MAIN"
},
{
"rowNumber": 2,
"ACAD_ORG": "ACAD DIV",
"EFFDT": "1900-01-01",
"EFF_STATUS": "A",
"DESCR": "Academic Division",
"DESCRSHORT": "Acad Div",
"INSTITUTION": "PSCCS"
}
]
}
},
"responseCode": 200,
"nextPageNumber": 2
}
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"
}
]
}
Sample response — SUBJECT is the key, ACAD_ORG links each subject to the owning academic org. First two rows:
{
"data": {
"SUBJECT_TBL": {
"objectType": "record",
"objectName": "SUBJECT_TBL",
"fields": [
{
"rowNumber": 1,
"INSTITUTION": "GLAKE",
"SUBJECT": "ACCOUNT",
"EFFDT": "1900-01-01",
"EFF_STATUS": "A",
"DESCR": "Accounting",
"ACAD_ORG": "ACCOUNTING",
"CIP_CODE": "52.03"
},
{
"rowNumber": 2,
"INSTITUTION": "GLAKE",
"SUBJECT": "ARTHIST",
"EFFDT": "1900-01-01",
"EFF_STATUS": "A",
"DESCR": "Art & Art History",
"ACAD_ORG": "ARTHISTORY",
"CIP_CODE": "50.07"
}
]
}
},
"responseCode": 200,
"nextPageNumber": 2
}
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'"
}
]
}
Sample response — PSXLATITEM is the translate-values table; XLATLONGNAME is the human-readable label that decodes a code like CLN → Clinical or DIS → Discussion:
{
"data": {
"PSXLATITEM": {
"objectType": "record",
"objectName": "PSXLATITEM",
"fields": [
{
"rowNumber": 1,
"FIELDNAME": "SSR_COMPONENT",
"FIELDVALUE": "CLN",
"EFFDT": "1900-01-01",
"EFF_STATUS": "A",
"XLATLONGNAME": "Clinical",
"XLATSHORTNAME": "Clinical"
},
{
"rowNumber": 2,
"FIELDNAME": "SSR_COMPONENT",
"FIELDVALUE": "CON",
"EFFDT": "1900-01-01",
"EFF_STATUS": "A",
"XLATLONGNAME": "Continuance",
"XLATSHORTNAME": "Continuanc"
},
{
"rowNumber": 3,
"FIELDNAME": "SSR_COMPONENT",
"FIELDVALUE": "DIS",
"EFFDT": "1900-01-01",
"EFF_STATUS": "A",
"XLATLONGNAME": "Discussion",
"XLATSHORTNAME": "Discussion"
}
]
}
},
"responseCode": 200,
"nextPageNumber": 2
}
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"
}
]
}