SAD_QCK_PLN_VW

(SQL View)
 Index Back

Academic Plan Security View

This view is a clone of ACAD_PLAN_SVW. By using this view as a prompt table you will be able to selects all plans that are associated with an Academic Program or a career within an Institution and plan that are valid for the Admit Term and the Admit Term is either less than or equal to the Last Admit Term or the Last Admit Term is blank.

SELECT DISTINCT a.institution , c.strm , b.acad_prog , a.acad_plan , d.oprid , a.descr , a.acad_career , a.acad_plan_type , a.degree , d.access_cd FROM PS_ACAD_PLAN_TBL a , ps_acad_prog_tbl b , ps_term_tbl c , ps_scrty_tbl_plan d WHERE a.institution = b.institution AND (a.acad_prog = b.acad_prog OR (a.acad_prog = ' ' AND a.acad_career = b.acad_career)) AND (c.strm <= a.SSR_LAST_ADM_TERM OR a.SSR_LAST_ADM_TERM = ' ') AND a.effdt = ( SELECT MAX(effdt) FROM PS_ACAD_PLAN_TBL a1 WHERE a.institution = a1.institution AND a.acad_plan = a1.acad_plan AND a1.effdt <= c.term_begin_dt) AND a.eff_status <> 'I' AND a.institution = c.institution AND a.institution = d.institution AND (a.acad_plan = d.acad_plan OR d.acad_plan = 'ALL') AND NOT EXISTS ( SELECT 'X' FROM ps_scrty_tbl_plan e WHERE e.access_cd = 'N' AND e.oprid = d.oprid AND e.institution = d.institution AND e.acad_plan = d.acad_plan)

  • Related Language Record: SAD_QCK_PLN_VWL

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

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

    Prompt Table: TERM_TBL

    3 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program

    Prompt Table: ACAD_PROG_TBL

    4 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan

    Prompt Table: ACAD_PLAN_TBL

    5 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    6 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    7 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
    8 ACAD_PLAN_TYPE Character(3) VARCHAR2(3) NOT NULL Academic Plan Type
    CON=Concentration
    COS=Course of Study
    HON=Honors
    MAJ=Major
    MIN=Minor
    PRP=Preparation
    RTC=ROTC
    SP=Specialization
    9 DEGREE Character(8) VARCHAR2(8) NOT NULL Degree
    10 ACCESS_CD Character(1) VARCHAR2(1) NOT NULL Access Code
    N=No Access
    Y=Read/Write Access