SAE_CRSOFFR_VW

(SQL View)
 Index Back

SAIP CourseOffering View

SAIP Course Offering snapshot view for third party integration.

SELECT /*+ LEADING( CLASS_EXT CLS) */ CLS.CRSE_ID , CLS.CRSE_OFFER_NBR , CLS.STRM , CLS.SESSION_CODE , CLS.CLASS_SECTION , CRSE.COURSE_TITLE_LONG , CRSE.EFF_STATUS , CRSE.DESCR , CLS.START_DT , CLS.END_DT , OFFER.INSTITUTION , OFFER.ACAD_GROUP , OFFER.SUBJECT , OFFER.CATALOG_NBR , OFFER.COURSE_APPROVED , OFFER.CAMPUS , CLS.ACAD_ORG , OFFER.ACAD_CAREER , OFFER.RQRMNT_GROUP , OFFER.AP_BUS_UNIT , OFFER.AP_LEDGER , OFFER.AP_ACCOUNT , OFFER.AP_DEPTID , OFFER.AP_PROJ_ID , OFFER.AP_PRODUCT , OFFER.AP_FUND_CODE , OFFER.AP_PROG_CODE , OFFER.AP_CLASS_FLD , OFFER.AP_AFFILIATE , OFFER.AP_BUD_REF , OFFER.WRITEOFF_BUS_UNIT , OFFER.EXT_WRITEOFF , OFFER.GL_INTERFACE_REQ , OFFER.SSR_CRSE_TYPOFF_CD , OFFER_EXT.SAE_MODE , OFFER_EXT.SAE_EVENT_DT FROM %Table(SAE_CLASS_EXT) CLASS_EXT , %Table(CLASS_TBL) CLS , %Table(CRSE_OFFER) OFFER , %Table(SAE_CRSOFFR_EXT) OFFER_EXT , %Table(CRSE_CATALOG) CRSE , %Table(SAE_ACADORG_EXT) ORG_EXT , %Table(TERM_TBL) TERM , %Table(SAE_TERM_EXT) TERM_EXT , %Table(SAE_INST_SCOPE) INST_EXT ,%Table(SAE_SESSION_EXT) SES_EXT WHERE INST_EXT.INSTITUTION = CLS.INSTITUTION AND INST_EXT.SAE_MODE <> 'D' AND ORG_EXT.INSTITUTION = CLS.INSTITUTION AND ORG_EXT.ACAD_ORG = CLS.ACAD_ORG AND ORG_EXT.SAE_MODE <> 'D' AND TERM.STRM = CLS.STRM AND TERM.INSTITUTION = CLS.INSTITUTION AND TERM.ACAD_CAREER = CLS.ACAD_CAREER AND TERM_EXT.STRM = CLASS_EXT.STRM AND TERM_EXT.INSTITUTION = CLASS_EXT.INSTITUTION AND TERM_EXT.ACAD_CAREER = CLASS_EXT.ACAD_CAREER AND TERM_EXT.SAE_INCLUDE = 'Y' AND SES_EXT.INSTITUTION = CLASS_EXT.INSTITUTION AND SES_EXT.STRM = CLASS_EXT.STRM AND SES_EXT.ACAD_CAREER = CLASS_EXT.ACAD_CAREER AND SES_EXT.SESSION_CODE = CLASS_EXT.SESSION_CODE AND %Coalesce(CLASS_EXT.SAE_INTEG_START_DT, %DateAdd(CLS.START_DT, SES_EXT.SAE_START_OFFSET)) <= %CurrentDateIn AND %Coalesce(CLASS_EXT.SAE_INTEG_END_DT, %DateAdd(CLS.END_DT, SES_EXT.SAE_END_OFFSET)) >= %CurrentDateIn AND CRSE.CRSE_ID = CLS.CRSE_ID AND CRSE.EFFDT = %Coalesce((select max(effdt) FROM PS_CRSE_CATALOG CRSE_CATALOG1 WHERE CRSE_CATALOG1.CRSE_ID =CRSE.CRSE_ID AND CRSE_CATALOG1.EFFDT <=%CurrentDateIn ) ,(select min(effdt) FROM PS_CRSE_CATALOG CRSE_CATALOG1 WHERE CRSE_CATALOG1.CRSE_ID =CRSE.CRSE_ID AND CRSE_CATALOG1.EFFDT >%CurrentDateIn )) AND OFFER.CRSE_ID = CLS.CRSE_ID AND OFFER.CRSE_OFFER_NBR = CLS.CRSE_OFFER_NBR AND OFFER.EFFDT = %Coalesce((select max(effdt) FROM PS_CRSE_OFFER CRSE_OFFER1 WHERE CRSE_OFFER1.CRSE_ID =OFFER.CRSE_ID AND CRSE_OFFER1.CRSE_OFFER_NBR=OFFER.CRSE_OFFER_NBR AND CRSE_OFFER1.EFFDT <=%CurrentDateIn ) ,( select min(effdt) FROM PS_CRSE_OFFER CRSE_OFFER1 WHERE CRSE_OFFER1.CRSE_ID =OFFER.CRSE_ID AND CRSE_OFFER1.CRSE_OFFER_NBR=OFFER.CRSE_OFFER_NBR AND CRSE_OFFER1.EFFDT >%CurrentDateIn )) AND OFFER.COURSE_APPROVED = 'A' AND OFFER_EXT.CRSE_ID = OFFER.CRSE_ID AND OFFER_EXT.CRSE_OFFER_NBR = OFFER.CRSE_OFFER_NBR AND OFFER_EXT.STRM = CLS.STRM AND OFFER_EXT.SESSION_CODE = CLS.SESSION_CODE AND OFFER_EXT.CLASS_SECTION = CLS.CLASS_SECTION AND CLASS_EXT.CRSE_ID = CLS.CRSE_ID AND CLASS_EXT.CRSE_OFFER_NBR = CLS.CRSE_OFFER_NBR AND CLASS_EXT.STRM = CLS.STRM AND CLASS_EXT.SESSION_CODE = CLS.SESSION_CODE AND CLASS_EXT.CLASS_SECTION = CLS.CLASS_SECTION AND CLASS_EXT.SAE_INCLUDE = 'Y' AND OFFER_EXT.SAE_MODe <> 'D' UNION ALL SELECT /*+ LEADING(OFFER_EXT1 CLASS_EXT) */ OFFER_EXT1.CRSE_ID , OFFER_EXT1.CRSE_OFFER_NBR , OFFER_EXT1.STRM , OFFER_EXT1.SESSION_CODE , OFFER_EXT1.CLASS_SECTION , '' , '' , '' , %DateNull , %DateNull , CLASS_EXT.INSTITUTION , '' , '' , '' , '' , '' , CLASS_EXT.ACAD_ORG , CLASS_EXT.ACAD_CAREER , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , '' , OFFER_EXT1.SAE_MODE , OFFER_EXT1.SAE_EVENT_DT FROM %Table(SAE_CRSOFFR_EXT) OFFER_EXT1 , %Table(SAE_CLASS_EXT) CLASS_EXT , %Table(SAE_ACADORG_EXT) ORG_EXT , %Table(TERM_TBL) TERM , %Table(SAE_TERM_EXT) TERM_EXT , %Table(SAE_INST_SCOPE) INST_EXT , %Table(SAE_SESSION_EXT) SES_EXT1 WHERE INST_EXT.INSTITUTION = CLASS_EXT.INSTITUTION AND INST_EXT.SAE_MODE <> 'D' AND ORG_EXT.INSTITUTION = CLASS_EXT.INSTITUTION AND ORG_EXT.ACAD_ORG = CLASS_EXT.ACAD_ORG AND ORG_EXT.SAE_MODE <> 'D' AND TERM.STRM = CLASS_EXT.STRM AND TERM.INSTITUTION = CLASS_EXT.INSTITUTION AND TERM.ACAD_CAREER = CLASS_EXT.ACAD_CAREER AND TERM_EXT.STRM = TERM.STRM AND TERM_EXT.INSTITUTION = TERM.INSTITUTION AND TERM_EXT.ACAD_CAREER = TERM.ACAD_CAREER AND TERM_EXT.SAE_INCLUDE = 'Y' AND SES_EXT1.INSTITUTION = CLASS_EXT.INSTITUTION AND SES_EXT1.STRM = CLASS_EXT.STRM AND SES_EXT1.ACAD_CAREER = CLASS_EXT.ACAD_CAREER AND SES_EXT1.SESSION_CODE = CLASS_EXT.SESSION_CODE AND %Coalesce(CLASS_EXT.SAE_INTEG_START_DT, %DateAdd(CLASS_EXT.START_DT, SES_EXT1.SAE_START_OFFSET)) <= %CurrentDateIn AND %Coalesce(CLASS_EXT.SAE_INTEG_END_DT, %DateAdd(CLASS_EXT.END_DT, SES_EXT1.SAE_END_OFFSET)) >= %CurrentDateIn AND OFFER_EXT1.SAE_MODE = 'D' AND EXISTS ( SELECT 'X' FROM %Table(CRSE_CATALOG) CRSE WHERE CRSE.CRSE_ID = CLASS_EXT.CRSE_ID AND CRSE.EFFDT = %Coalesce((select max(effdt) FROM PS_CRSE_CATALOG CRSE_CATALOG1 WHERE CRSE_CATALOG1.CRSE_ID =CRSE.CRSE_ID AND CRSE_CATALOG1.EFFDT <=%CurrentDateIn ) ,(select min(effdt) FROM PS_CRSE_CATALOG CRSE_CATALOG1 WHERE CRSE_CATALOG1.CRSE_ID =CRSE.CRSE_ID AND CRSE_CATALOG1.EFFDT >%CurrentDateIn )) ) AND OFFER_EXT1.CRSE_ID = CLASS_EXT.CRSE_ID AND OFFER_EXT1.CRSE_OFFER_NBR = CLASS_EXT.CRSE_OFFER_NBR AND OFFER_EXT1.STRM = CLASS_EXT.STRM AND OFFER_EXT1.SESSION_CODE = CLASS_EXT.SESSION_CODE AND OFFER_EXT1.CLASS_SECTION = CLASS_EXT.CLASS_SECTION

  • Related Language Record: SAE_CRSOFFR_LNG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID

    Prompt Table: CRSE_CATALOG

    2 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
    3 STRM Character(4) VARCHAR2(4) NOT NULL Term
    4 SESSION_CODE Character(3) VARCHAR2(3) NOT NULL Session
    1=Regular Academic Session
    10W=10 Week
    12W=Twelve Week
    4W1=Four Week - First
    4W2=Four Week - Second
    4W3=Four Week - Third
    4W4=Four Week - Fourth
    6W1=Six Week - First
    6W2=Six Week - Second
    8W1=Eight Week - First
    8W2=Eight Week - Second
    A11=Medical Period 1, 4 Weeks
    A12=Medical Period 1, 8 Weeks
    A13=Medical Pd 1, 12 Weeks
    A21=Medical Period 2, 4 Weeks
    A22=Medical Period 2, 8 Weeks
    A23=Medical Period 2, 12 Week
    FYR=Full Year
    MIN=Mini Session
    OEE=Open Entry/Open Exit
    SM1=Semester 1
    SM2=Semester 2
    5 CLASS_SECTION Character(4) VARCHAR2(4) NOT NULL Class Section
    6 COURSE_TITLE_LONG Character(100) VARCHAR2(100) NOT NULL Long Course Title
    7 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive
    8 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    9 START_DT Date(10) DATE Start Date
    10 END_DT Date(10) DATE end date
    11 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Default Value: OPR_DEF_TBL_CS.INSTITUTION

    Prompt Table: INSTITUTION_TBL

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

    Default Value: OPR_DEF_TBL_CS.ACAD_GROUP

    Prompt Table: ACAD_GROUP_TBL

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

    Prompt Table: SUBJECT_TBL

    14 CATALOG_NBR Character(10) VARCHAR2(10) NOT NULL Catalog Nbr
    15 COURSE_APPROVED Character(1) VARCHAR2(1) NOT NULL Course Approved
    A=Approved
    D=Denied
    P=Pending

    Default Value: P

    16 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus

    Prompt Table: CAMPUS_TBL

    17 ACAD_ORG Character(10) VARCHAR2(10) NOT NULL Academic Organization

    Prompt Table: ACAD_ORG_SRCH

    18 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

    Prompt Table: CATLG_CAR_TBL

    19 RQRMNT_GROUP Character(6) VARCHAR2(6) NOT NULL Requirement Group

    Prompt Table: RQ_GRP_ENR_VW

    20 AP_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: BUS_UNIT_TBL_FS

    21 AP_LEDGER Character(10) VARCHAR2(10) NOT NULL Ledger

    Prompt Table: LED_DEFN_TBL
    Set Control Field: AP_BUS_UNIT

    22 AP_ACCOUNT Character(10) VARCHAR2(10) NOT NULL Account
    23 AP_DEPTID Character(10) VARCHAR2(10) NOT NULL Department
    24 AP_PROJ_ID Character(15) VARCHAR2(15) NOT NULL Project ID
    25 AP_PRODUCT Character(6) VARCHAR2(6) NOT NULL Product
    26 AP_FUND_CODE Character(5) VARCHAR2(5) NOT NULL Fund
    27 AP_PROG_CODE Character(5) VARCHAR2(5) NOT NULL Program
    28 AP_CLASS_FLD Character(5) VARCHAR2(5) NOT NULL Public Sector Financials Chart Field.
    29 AP_AFFILIATE Character(5) VARCHAR2(5) NOT NULL Public Sector Financials Chart Field
    30 AP_BUD_REF Character(8) VARCHAR2(8) NOT NULL Budget Reference Chartfield
    31 WRITEOFF_BUS_UNIT Character(5) VARCHAR2(5) NOT NULL Business Unit

    Prompt Table: BUS_UNIT_TBL_FS

    32 EXT_WRITEOFF Character(50) VARCHAR2(50) NOT NULL External Writeoff
    33 GL_INTERFACE_REQ Character(1) VARCHAR2(1) NOT NULL GL Interface Required

    Y/N Table Edit

    Default Value: N

    34 SSR_CRSE_TYPOFF_CD Character(10) VARCHAR2(10) NOT NULL Course Typically Offered

    Prompt Table: SSR_CRSE_TYPOFF

    35 SAE_MODE Character(1) VARCHAR2(1) NOT NULL Operation Mode
    A=Add
    C=Change
    D=Delete
    36 SAE_EVENT_DT DateTime(26) TIMESTAMP Event Driven Timestamp