SAA_ACRSE_AVLV2

(SQL View)
 Index Back

Analysis DB Courses Used

This table contains the results for courses used in the Academic Advisement Analysis Database, used to identify courses to be displayed.

SELECT a.EMPLID , a.ANALYSIS_DB_SEQ , a.SAA_CAREER_RPT , a.RQRMNT_GROUP , a.REQUIREMENT , a.RQ_LINE_NBR , a.SAA_ENTRY_SEQ , a.SAA_COURSE_SEQ , ' ' , 'S' , ' ' , ' ' , a.SUBJECT , a.CATALOG_NBR , a.CRSE_ID , c.CRSE_OFFER_NBR , a.CRS_TOPIC_ID , b.UNITS_MINIMUM , b.UNITS_MAXIMUM , 0 , 0 , c.INSTITUTION , c.ACAD_CAREER , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , ' ' , %DateNull , a.COURSE_LIST , a.EFFDT , a.R_COURSE_SEQUENCE , d.SAA_WHERE_CLAUSE , d.SAA_WILDCARD_XLIST , d.SAA_DISPLAY_FLAG FROM PS_SAA_ADB_CRSEAVL a , PS_CRSE_CATALOG b , PS_CRSE_OFFER c , PS_CLST_DETL_TBL d WHERE a.CRSE_ID = b.CRSE_ID AND b.EFFDT = ( SELECT MAX(b1.EFFDT) FROM PS_CRSE_CATALOG b1 WHERE b1.CRSE_ID = b.CRSE_ID AND b1.EFFDT <= a.RPT_DATE) AND b.CRSE_ID = c.CRSE_ID AND b.EFFDT = c.EFFDT AND a.SUBJECT = c.SUBJECT AND a.CATALOG_NBR = c.CATALOG_NBR AND c.COURSE_APPROVED = 'A' AND NOT EXISTS ( SELECT 'X' FROM PS_SAA_ACRSE_USEVW u WHERE u.EMPLID=a.EMPLID AND u.ANALYSIS_DB_SEQ=a.ANALYSIS_DB_SEQ AND u.SAA_CAREER_RPT=a.SAA_CAREER_RPT AND u.RQRMNT_GROUP=a.RQRMNT_GROUP AND u.REQUIREMENT=a.REQUIREMENT AND u.RQ_LINE_NBR=a.RQ_LINE_NBR AND u.CRSE_ID=a.CRSE_ID AND u.SEL_MODE='S') AND a.COURSE_LIST = d.COURSE_LIST AND a.EFFDT = d.EFFDT AND a.R_COURSE_SEQUENCE = d.R_COURSE_SEQUENCE

  • Parent record: SAA_ADB_RESULTS

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PEOPLE_SRCH

    2 ANALYSIS_DB_SEQ Number(4,0) SMALLINT NOT NULL Analysis DB Sequence
    3 SAA_CAREER_RPT Character(4) VARCHAR2(4) NOT NULL This field is used in academic advisement as a key to the results tables.

    Prompt Table: ACAD_CAR_TBL

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

    Prompt Table: RQ_GRP_TBL

    5 REQUIREMENT Character(9) VARCHAR2(9) NOT NULL Academic Requirement

    Prompt Table: RQ_MAIN_TBL

    6 RQ_LINE_NBR Character(4) VARCHAR2(4) NOT NULL Line Nbr
    7 SAA_ENTRY_SEQ Number(4,0) SMALLINT NOT NULL This field is used in Academic Advisement to track the position of the output in the report.
    8 SAA_COURSE_SEQ Number(3,0) SMALLINT NOT NULL This field is used in the table SAA_ADB_CRSEUSE to track multiple occurances of a course at the same entry sequence position in the table.
    9 CRSE_TAG Character(4) VARCHAR2(4) NOT NULL Course Tag
    10 SEL_MODE Character(1) VARCHAR2(1) NOT NULL Selection Mode
    11 SAA_CRSE_TYPE Character(1) VARCHAR2(1) NOT NULL This field is used to identify what-if and planned courses in the advisement results tables.
    N=Normal Course
    P=Planned Course
    S=Shopping Cart Course
    W=What-If Course
    12 CLASS_ENRL_TYPE Character(1) VARCHAR2(1) NOT NULL Class enroll type
    13 SUBJECT Character(8) VARCHAR2(8) NOT NULL Subject Area
    14 CATALOG_NBR Character(10) VARCHAR2(10) NOT NULL Catalog Nbr
    15 CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course ID
    16 CRSE_OFFER_NBR Number(2,0) SMALLINT NOT NULL Course Offering Nbr
    17 CRS_TOPIC_ID Number(3,0) SMALLINT NOT NULL Course Topic ID
    18 UNITS_MINIMUM Number(6,2) DECIMAL(5,2) NOT NULL Minimum Units
    19 UNITS_MAXIMUM Number(6,2) DECIMAL(5,2) NOT NULL Maximum Units
    20 UNT_TAKEN Number(6,2) DECIMAL(5,2) NOT NULL Units Taken
    21 UNT_EARNED Number(6,2) DECIMAL(5,2) NOT NULL Units Earned
    22 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

    23 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: ACAD_CAR_TBL

    24 STRM Character(4) VARCHAR2(4) NOT NULL Term

    Prompt Table: TERM_VAL_TBL

    25 SRC_INSTITUTION Character(5) VARCHAR2(5) NOT NULL Source Institution

    Prompt Table: INSTITUTION_TBL

    26 SRC_CAREER Character(4) VARCHAR2(4) NOT NULL Source Career

    Prompt Table: ACAD_CAR_TBL

    27 TERM_TAKEN Character(4) VARCHAR2(4) NOT NULL Term Taken
    28 APPLIED_TERM Character(4) VARCHAR2(4) NOT NULL Applied term

    Prompt Table: TERM_VAL_TBL

    29 CRSE_GRADE_OFF Character(3) VARCHAR2(3) NOT NULL Official Grade
    30 UNITS_ATTEMPTED Character(1) VARCHAR2(1) NOT NULL Units Attempted
    I=In Progress
    N=No
    Y=Yes
    31 SUB_CRSE_ID Character(6) VARCHAR2(6) NOT NULL Course Id
    32 SUB_SUBJECT Character(8) VARCHAR2(8) NOT NULL Subject Area
    33 SUB_CATLG_NBR Character(10) VARCHAR2(10) NOT NULL Catalog Nbr
    34 STDNT_SUB_NBR Character(4) VARCHAR2(4) NOT NULL Substitution Seq
    35 RQ_AA_OVERRIDE Character(9) VARCHAR2(9) NOT NULL Advisement Override
    36 ASOF_DATE Date(10) DATE This field is used all over campus solutions to hold the value of an as of date to be used in different processing.
    37 COURSE_LIST Character(9) VARCHAR2(9) NOT NULL Course List
    38 SAA_EFFDT Date(10) DATE Used to rename EFFDT in views, to prevent the component processor from using this value in its effective date logic.
    39 R_COURSE_SEQUENCE Number(7,0) INTEGER NOT NULL Course Sequence
    40 SAA_WHERE_DETAIL Long Character CLOB This field will contain the individual where clause for course list details to be passed to the academic advisement reporting routines to aid in displaying only the courses a student needs to see when trying to find courses from the advisement reports.
    41 SAA_WILDCARD_XLIST Character(1) VARCHAR2(1) NOT NULL This field is a Y/N field used to denote when a wildcard course list detail can include any cross-listed courses in the evaluation.
    42 SAA_DISPLAY_FLAG Character(1) VARCHAR2(1) NOT NULL Display In Acad Requirements