SSR_CRSOFR_ESVW

(SQL View)
Index Back

Course Offer ES Record View

Course Offer Elastic Search Record View

SELECT DISTINCT O.CRSE_ID , O.CRSE_OFFER_NBR , O.EFFDT , O.SUBJECT , O.SSR_CRSE_TYPOFF_CD , GBX.XLATLONGNAME , C.RQMNT_DESIGNTN , CTX.XLATLONGNAME , AC.DESCR , COX.XLATLONGNAME , O.CAMPUS , ORG.DESCR , ATT.CRSE_ATTR , ATT.CRSE_ATTR_VALUE , U.DESCR , C.UNITS_MINIMUM , C.UNITS_MAXIMUM , CASE WHEN ATT.SSR_CLSCFG_ATTRVAL = 'Y' THEN ATT.DESCR1 %Concat '/' %Concat ATT.DESCR2 ELSE ATT.DESCR1 END , O.INSTITUTION , O.ACAD_GROUP , O.SUBJECT %Concat '/' %Concat S.DESCR , O.CATALOG_NBR , C.DESCR , O.SUBJECT %Concat ' ' %Concat O.CATALOG_NBR , C.COURSE_TITLE_LONG , O.COURSE_APPROVED , O.EFFDT , T.DESCR , T.DESCR , CASE WHEN O.SSR_CRSE_TYPOFF_CD <> ' ' THEN 'Y' ELSE 'N' END , C.GRADING_BASIS , O.OEE_IND , O.ACAD_CAREER , CO.SSR_COMPONENT , O.ACAD_ORG , TPC.DESCR , TPC.DESCR , CASE WHEN C.UNITS_MINIMUM = C.UNITS_MAXIMUM THEN CASE WHEN C.UNITS_MINIMUM = 1 THEN %NumToChar(C.UNITS_MINIMUM) %Concat ' ' %Concat UNT.MESSAGE_TEXT ELSE %NumToChar(C.UNITS_MINIMUM) %Concat ' ' %Concat UNS.MESSAGE_TEXT END ELSE CASE WHEN C.UNITS_MAXIMUM = 1 THEN %NumToChar(C.UNITS_MINIMUM) %Concat ' - ' %Concat %NumToChar(C.UNITS_MAXIMUM) %Concat ' ' %Concat UNT.MESSAGE_TEXT ELSE %NumToChar(C.UNITS_MINIMUM) %Concat ' - ' %Concat %NumToChar(C.UNITS_MAXIMUM) %Concat ' ' %Concat UNS.MESSAGE_TEXT END END /* , CASE WHEN C.UNITS_MINIMUM = C.UNITS_MAXIMUM THEN CASE WHEN C.UNITS_MINIMUM > 1 OR C.UNITS_MINIMUM = 0 THEN %NumToChar(C.UNITS_MINIMUM) %Concat ' ' %Concat UNS.MESSAGE_TEXT ELSE %NumToChar(C.UNITS_MINIMUM) %Concat ' ' %Concat UNT.MESSAGE_TEXT END ELSE %NumToChar(C.UNITS_MINIMUM) %Concat ' - ' %Concat %NumToChar(C.UNITS_MAXIMUM) %Concat ' ' %Concat UNS.MESSAGE_TEXT END AS DESCR4 */ , U.DESCR FROM PS_CRSE_OFFER O LEFT OUTER JOIN PS_CRSE_TOPICS TPC ON O.CRSE_ID = TPC.CRSE_ID AND TPC.EFFDT = O.EFFDT LEFT OUTER JOIN PS_SSR_BC_UNITS_VW U ON O.CRSE_ID = U.CRSE_ID AND O.EFFDT = U.EFFDT LEFT OUTER JOIN PS_SSR_CRS_ATTR_VW ATT ON O.CRSE_ID = ATT.CRSE_ID AND O.CRSE_OFFER_NBR = ATT.CRSE_OFFER_NBR AND O.EFFDT = ATT.EFFDT , PS_CRSE_CATALOG C , PSXLATITEM GBX , PS_CRSE_COMPONENT CO , PS_ACAD_CAR_TBL AC , PSXLATITEM COX , PSXLATITEM CTX , PS_ACAD_ORG_TBL ORG , PS_SUBJECT_TBL S , PS_SSR_CRS_TYPF_VW T , PSMSGCATDEFN UNS , PSMSGCATDEFN UNT WHERE O.CRSE_ID = C.CRSE_ID AND O.EFFDT = C.EFFDT AND O.COURSE_APPROVED = 'A' AND O.CATALOG_PRINT = 'Y' AND C.EFF_STATUS = 'A' AND GBX.FIELDNAME = 'GRADING_BASIS' AND GBX.FIELDVALUE = C.GRADING_BASIS AND GBX.EFFDT = ( SELECT MAX(GBX2.EFFDT) FROM PSXLATITEM GBX2 WHERE GBX.FIELDNAME = GBX2.FIELDNAME AND GBX.FIELDVALUE = GBX2.FIELDVALUE AND GBX2.EFFDT <= %CurrentDateIn) AND C.CRSE_ID = O.CRSE_ID AND CO.CRSE_ID = O.CRSE_ID AND AC.INSTITUTION = O.INSTITUTION AND AC.ACAD_CAREER = O.ACAD_CAREER AND AC.EFFDT = ( SELECT MAX(AC2.EFFDT) FROM PS_ACAD_CAR_TBL AC2 WHERE AC.INSTITUTION = AC2.INSTITUTION AND AC.ACAD_CAREER = AC2.ACAD_CAREER AND AC2.EFFDT <= O.EFFDT) AND COX.FIELDNAME = 'SSR_COMPONENT' AND COX.FIELDVALUE = CO.SSR_COMPONENT AND COX.EFFDT = ( SELECT MAX(COX2.EFFDT) FROM PSXLATITEM COX2 WHERE COX.FIELDNAME = COX2.FIELDNAME AND COX.FIELDVALUE = COX2.FIELDVALUE AND COX2.EFFDT <= %CurrentDateIn) AND CTX.FIELDNAME = 'ALLOWABLE_IND' AND CTX.FIELDVALUE = O.OEE_IND AND CTX.EFFDT = ( SELECT MAX(CTX2.EFFDT) FROM PSXLATITEM CTX2 WHERE CTX.FIELDNAME = CTX2.FIELDNAME AND CTX.FIELDVALUE = CTX2.FIELDVALUE AND CTX2.EFFDT <= %CurrentDateIn) AND ORG.ACAD_ORG = O.ACAD_ORG AND ORG.EFFDT = ( SELECT MAX(ORG2.EFFDT) FROM PS_ACAD_ORG_TBL ORG2 WHERE ORG.ACAD_ORG = ORG2.ACAD_ORG AND ORG2.EFFDT <= O.EFFDT) AND S.INSTITUTION = O.INSTITUTION AND S.SUBJECT = O.SUBJECT AND S.EFFDT = ( SELECT MAX(S2.EFFDT) FROM PS_SUBJECT_TBL S2 WHERE S.INSTITUTION = S2.INSTITUTION AND S.SUBJECT = S2.SUBJECT AND S2.EFFDT <= O.EFFDT) AND T.CRSE_ID = O.CRSE_ID AND T.CRSE_OFFER_NBR = O.CRSE_OFFER_NBR AND T.EFFDT = O.EFFDT AND (UNS.MESSAGE_SET_NBR = 14770 AND UNS.MESSAGE_NBR = 9025) AND (UNT.MESSAGE_SET_NBR = 14770 AND UNT.MESSAGE_NBR = 9024)

  • Related Language Record: SSR_CRSOFR_ESLN
  • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
    2 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
    3 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    4 SUBJECT Character(8) VARCHAR2(8) NOT NULL Subject Area

    Prompt Table: SUBJECT_TBL

    5 SSR_CRSE_TYPOFF_CD Character(10) VARCHAR2(10) NOT NULL Course Typically Offered
    6 SSR_GRADING_BASIS Character(30) VARCHAR2(30) NOT NULL Grading Basis
    7 RQMNT_DESIGNTN Character(4) VARCHAR2(4) NOT NULL This field is used to track the Requirement Designation attached to a course in Student Records. This field is also used for tracking information through Academic Advisement.
    8 SSR_OEE_IND_DESCR Character(3) VARCHAR2(3) NOT NULL Open Entry/Exit Indicator Description
    9 SSR_CAREER_DESC_ES Character(30) VARCHAR2(30) NOT NULL Career Description
    10 SSR_COMPO_DESCR_ES Character(30) VARCHAR2(30) NOT NULL Component Description
    11 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus

    Prompt Table: CAMPUS_TBL

    12 SSR_ACAD_ORG_DESCR Character(30) VARCHAR2(30) NOT NULL Academic Organization Description
    13 CRSE_ATTR Character(4) VARCHAR2(4) NOT NULL Course Attribute
    14 CRSE_ATTR_VALUE Character(10) VARCHAR2(10) NOT NULL Course Attribute Value
    15 SSR_NUM_UNITS_ES Character(30) VARCHAR2(30) NOT NULL Number of Units
    16 UNITS_MINIMUM Number(6,2) DECIMAL(5,2) NOT NULL Minimum Units
    17 UNITS_MAXIMUM Number(6,2) DECIMAL(5,2) NOT NULL Maximum Units
    18 SSR_CRS_ATTR_FL Character(70) VARCHAR2(70) NOT NULL Course Attribute
    19 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Default Value: OPR_DEF_TBL_CS.INSTITUTION

    Prompt Table: INSTITUTION_TBL

    20 ACAD_GROUP Character(5) VARCHAR2(5) NOT NULL Academic Group

    Default Value: OPR_DEF_TBL_CS.ACAD_GROUP

    Prompt Table: ACAD_GROUP_TBL

    21 SSR_SUBJ_DESCR_ES Character(30) VARCHAR2(30) NOT NULL Subject
    22 CATALOG_NBR Character(10) VARCHAR2(10) NOT NULL Catalog Nbr
    23 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    24 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    25 COURSE_TITLE_LONG Character(100) VARCHAR2(100) NOT NULL Long Course Title
    26 COURSE_APPROVED Character(1) VARCHAR2(1) NOT NULL Course Approved
    A=Approved
    D=Denied
    P=Pending

    Default Value: P

    27 SSR_CRSE_AS_OF_DT Character(35) VARCHAR2(35) NOT NULL Course As Of Date
    28 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    29 SSR_CRSE_OFF_DESCR Character(30) VARCHAR2(30) NOT NULL Course Typically Offered Description
    30 SSR_CRSE_TYPOFF_YN Character(1) VARCHAR2(1) NOT NULL Student Records Course Typically Offered Yes or No
    N=No
    Y=Yes
    31 GRADING_BASIS Character(3) VARCHAR2(3) NOT NULL Grading Basis
    ANC=ABC/NC Grading
    AUD=Audit
    BMT=Multi-Term Course: Not Graded
    CNC=Credit / No Credit
    EQV=Equivalent
    GRD=Graded
    HSC=Hundred Point Scale (NLD)
    LAW=Law
    MED=Medical School Grades
    NOG=No Grade Associated
    NON=Non-Graded Component
    NQF=NQF Scale
    OPT=Student Option
    PNP=Pass/Not Pass
    SUS=Satisfactory/Unsatisfactory
    TRN=Transfer Grading Basis
    TSC=Ten Point Scale (NLD)
    32 OEE_IND Character(1) VARCHAR2(1) NOT NULL Allow OEE Enrollment
    33 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic Career
    BAC=Bachelor (NLD)
    BBL=Vocational Coaching (NLD)
    BOL=Vocational Training (NLD)
    BUSN=Graduate Business
    CNED=Continuing Education
    CRED=Semester Credit
    EDU=Education (NLD)
    EXED=Extended Education
    GRAD=Graduate
    LAW=Law
    MEDS=Medical School
    NONA=Non Award
    PGRD=Postgraduate
    RSCH=Research
    TECH=Technical
    UENG=Undergraduate Engineering
    UGRD=Undergraduate
    VAVO=Advanced General Educ. (NLD)
    VETM=Veterinary Medicine
    34 SSR_COMPONENT Character(3) VARCHAR2(3) NOT NULL Course Component.
    CLN=Clinical
    CON=Continuance
    DIS=Discussion
    FLD=Field Studies
    IND=Independent Study
    LAB=Laboratory
    LEC=Lecture
    PRA=Practicum
    RSC=Research
    SEM=Seminar
    SUP=Supervision
    THE=Thesis Research
    TUT=Tutorial
    35 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization

    Prompt Table: ACAD_ORG_SRCH

    36 SSR_CRSE_TOPIC_ES Character(30) VARCHAR2(30) NOT NULL Course Topic
    37 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    38 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    39 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.