SSR_CRSE_OFR_VW

(SQL View)
 Index Back

Course Catalog Offerings

The Course Catalog Offerings indicates what academic units, subject and course catalog numbers a course can be offered under.

SELECT a.CRSE_ID , a.EFFDT , a.CRSE_OFFER_NBR , b.EFF_STATUS , a.INSTITUTION , a.ACAD_GROUP , a.SUBJECT , a.CATALOG_NBR , a.CAMPUS , a.ACAD_ORG , a.COURSE_APPROVED FROM PS_CRSE_OFFER a , PS_CRSE_CATALOG b WHERE b.CRSE_ID = a.CRSE_ID AND b.EFFDT = a.EFFDT UNION SELECT DISTINCT a.CRSE_ID , %DateAdd(b.EFFDT, -1) , a.CRSE_OFFER_NBR , 'I' , a.INSTITUTION , a.ACAD_GROUP , a.SUBJECT , a.CATALOG_NBR , a.CAMPUS , a.ACAD_ORG , a.COURSE_APPROVED FROM PS_CRSE_OFFER a , PS_CRSE_OFFER b , PS_CRSE_CATALOG c WHERE c.CRSE_ID = a.CRSE_ID AND c.EFFDT = a.EFFDT AND c.EFF_STATUS <> 'I' AND b.CRSE_ID = a.CRSE_ID AND b.EFFDT = ( SELECT MIN(b2.EFFDT) FROM PS_CRSE_OFFER b2 WHERE b2.CRSE_ID = a.CRSE_ID AND b2.EFFDT > a.EFFDT)

  • Parent record: CRSE_CATALOG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
    2 EFFDT Date(10) DATE NOT NULL Effective Date

    Default Value: %date

    3 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
    4 EFF_STATUS Character(1) VARCHAR2(1) NOT NULL Effective Status
    A=Active
    I=Inactive

    Default Value: A

    5 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Default Value: OPR_DEF_TBL_CS.INSTITUTION

    Prompt Table: INSTITUTION_TBL

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

    Default Value: OPR_DEF_TBL_CS.ACAD_GROUP

    Prompt Table: ACAD_GROUP_TBL

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

    Prompt Table: SUBJECT_TBL

    8 CATALOG_NBR Character(10) VARCHAR2(10) NOT NULL Catalog Nbr
    9 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus

    Prompt Table: CAMPUS_TBL

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

    Prompt Table: ACAD_ORG_SRCH

    11 COURSE_APPROVED Character(1) VARCHAR2(1) NOT NULL Course Approved
    A=Approved
    D=Denied
    P=Pending

    Default Value: P