# Campus Solutions PsoftQL - Examples

> PsoftQL  Examples for PeopleSoft Campus Solutions (CS)

---

LLMS index: [llms.txt](/llms.txt)

---

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

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

### Academic Organization Table

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

### Academic Organization Table

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

### Academic Group Table

```json
{
  "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.

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

```

### Instruction Mode

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

```

### Component Xlat

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

```

### PeopleSoft Terms

```json
{
    "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.

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

### Acad Career Table

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

### Meeting Pattern Setup

```json
{
  "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`)

```json
{
    "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.

```json
{
  "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

```json

{
  "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.

```json
{
  "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}}`

```json
{

 "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

```json
{
    "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.

```json
{
    "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.

```json
{
    "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

```json
{
    "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.

```JSON

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

]
}

```