SAD_PRS_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 , 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_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 (%CurrentDateIn <= a.SSR_LAST_PRS_DT OR a.SSR_LAST_PRS_DT IS NULL) 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 <= %CurrentDateIn) AND a.eff_status <> 'I' 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_PRS_PLN_VWL

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Institution field to store institution information in campus solution system.

    Prompt Table: INSTITUTION_TBL

    2 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic program field coming from campus solution system.

    Prompt Table: ACAD_PROG_TBL

    3 ACAD_PLAN Character(10) VARCHAR2(10) NOT NULL Academic Plan field coming from campus solution system

    Prompt Table: ACAD_PLAN_TBL

    4 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    5 DESCR Character(30) VARCHAR2(30) NOT NULL Description
    6 ACAD_CAREER Character(4) VARCHAR2(4) NOT NULL Academic career field coming from campus solution system.
    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
    7 ACAD_PLAN_TYPE Character(3) VARCHAR2(3) NOT NULL Academic Plan Type
    8 DEGREE Character(8) VARCHAR2(8) NOT NULL DEGREE. This field is used in the TS189 Outbound Message.
    9 ACCESS_CD Character(1) VARCHAR2(1) NOT NULL Access Code
    N=No Access
    R=Read Only Access
    Y=Read/Write Access