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 campuses.

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

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

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

More Complete Student Program and Plan Setup

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


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

]
}