SSR_CRSOFR_ESLN

(SQL View)
 Index Back

Course Offer ES Rel Lang View

Course Offer Elastic Search Related Language View

SELECT DISTINCT O.CRSE_ID , O.CRSE_OFFER_NBR , O.EFFDT , O.SUBJECT , O.SSR_CRSE_TYPOFF_CD , GBL.XLATLONGNAME , C.RQMNT_DESIGNTN , CTL.XLATLONGNAME , ACL.DESCR , COL.XLATLONGNAME , O.CAMPUS , ORL.DESCR , ATL.CRSE_ATTR , ATL.CRSE_ATTR_VALUE , UL.DESCR , CL.LANGUAGE_CD , CASE WHEN ATT.SSR_CLSCFG_ATTRVAL = 'Y' THEN ATL.DESCR1 %Concat '/' %Concat ATL.DESCR2 ELSE ATL.DESCR1 END , O.SUBJECT %Concat '/' %Concat SL.DESCR , CL.DESCR , O.SUBJECT %Concat ' ' %Concat O.CATALOG_NBR , CL.COURSE_TITLE_LONG , TL.DESCR , TL.DESCR , TOL.DESCR , TOL.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 */ , UL.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 , PS_CRSE_CATLG_LNG CL , PSXLATITEM GBX , PSXLATITEMLANG GBL , PS_CRSE_COMPONENT CO , PS_ACAD_CAR_TBL AC , PS_ACAD_CAR_LANG ACL , PSXLATITEM COX , PSXLATITEMLANG COL , PSXLATITEM CTX , PSXLATITEMLANG CTL , PS_ACAD_ORG_TBL ORG , PS_ACAD_ORG_LANG ORL , PS_SUBJECT_TBL S , PS_SUBJECT_TBL_LNG SL , PS_SSR_CRS_TYPF_VW T , PS_SSR_CRS_TYPF_VL TL , PS_SSR_BC_UNITS_VL UL , PS_CRSE_TOPICS_LNG TOL , PS_SSR_CRS_ATTR_VL ATL , PSMSGCATLANG UNS , PSMSGCATLANG 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 CL.CRSE_ID = C.CRSE_ID AND CL.EFFDT = C.EFFDT 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 GBL.FIELDNAME = GBX.FIELDNAME AND GBL.FIELDVALUE = GBX.FIELDVALUE AND GBL.EFFDT = GBX.EFFDT AND GBL.LANGUAGE_CD = CL.LANGUAGE_CD 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 ACL.INSTITUTION = AC.INSTITUTION AND ACL.ACAD_CAREER = AC.ACAD_CAREER AND ACL.EFFDT = AC.EFFDT AND ACL.LANGUAGE_CD = CL.LANGUAGE_CD 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 COL.FIELDNAME = COX.FIELDNAME AND COL.FIELDVALUE = COX.FIELDVALUE AND COL.EFFDT = COX.EFFDT AND COL.LANGUAGE_CD = CL.LANGUAGE_CD 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 CTL.FIELDNAME = CTX.FIELDNAME AND CTL.FIELDVALUE = CTX.FIELDVALUE AND CTL.EFFDT = CTX.EFFDT AND CTL.LANGUAGE_CD = CL.LANGUAGE_CD 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 ORL.ACAD_ORG = ORG.ACAD_ORG AND ORL.EFFDT = ORG.EFFDT AND ORL.LANGUAGE_CD = CL.LANGUAGE_CD 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 SL.INSTITUTION = S.INSTITUTION AND SL.SUBJECT = S.SUBJECT AND SL.EFFDT = S.EFFDT AND SL.LANGUAGE_CD = CL.LANGUAGE_CD AND T.CRSE_ID = O.CRSE_ID AND T.CRSE_OFFER_NBR = O.CRSE_OFFER_NBR AND T.EFFDT = O.EFFDT AND TL.CRSE_ID = T.CRSE_ID AND TL.CRSE_OFFER_NBR = T.CRSE_OFFER_NBR AND TL.EFFDT = O.EFFDT AND UL.CRSE_ID = U.CRSE_ID AND UL.CRSE_OFFER_NBR = U.CRSE_OFFER_NBR AND UL.LANGUAGE_CD = CL.LANGUAGE_CD AND TOL.CRSE_ID = TPC.CRSE_ID AND TOL.EFFDT = TPC.EFFDT AND TOL.LANGUAGE_CD = CL.LANGUAGE_CD AND ATL.CRSE_ID = ATT.CRSE_ID AND ATL.CRSE_OFFER_NBR = ATT.CRSE_OFFER_NBR AND ATL.LANGUAGE_CD = CL.LANGUAGE_CD AND (UNS.MESSAGE_SET_NBR = 14770 AND UNS.MESSAGE_NBR = 9025) AND UNS.LANGUAGE_CD = CL.LANGUAGE_CD AND (UNT.MESSAGE_SET_NBR = 14770 AND UNT.MESSAGE_NBR = 9024) AND UNT.LANGUAGE_CD = CL.LANGUAGE_CD

  • Related Language Record for SSR_CRSOFR_ESVW

    • # 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 LANGUAGE_CD Character(3) VARCHAR2(3) NOT NULL Language Code
    17 SSR_CRS_ATTR_FL Character(70) VARCHAR2(70) NOT NULL Course Attribute
    18 SSR_SUBJ_DESCR_ES Character(30) VARCHAR2(30) NOT NULL Subject
    19 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    20 DESCR1 Character(30) VARCHAR2(30) NOT NULL Descr
    21 COURSE_TITLE_LONG Character(100) VARCHAR2(100) NOT NULL Long Course Title
    22 DESCR2 Character(30) VARCHAR2(30) NOT NULL Descr2
    23 SSR_CRSE_OFF_DESCR Character(30) VARCHAR2(30) NOT NULL Course Typically Offered Description
    24 SSR_CRSE_TOPIC_ES Character(30) VARCHAR2(30) NOT NULL Course Topic
    25 DESCR3 Character(30) VARCHAR2(30) NOT NULL Descr 3
    26 DESCR4 Character(30) VARCHAR2(30) NOT NULL Descr 4
    27 DESCR5 Character(30) VARCHAR2(30) NOT NULL This field is used in a complex query that shows many descr fields.