SAD_APP_PRG_VW2

(SQL View)
 Index Back

Academic Program Security

This is a clone of SAD_ACAD_PRG_VW. This view does not include ACAD_CAREER as a key. Prompt view which enforces Academic Program operator security and also checks to see if the programs are valid for the Admit Term and the the Admit Term is greater than the Last Admit Term for a Program.

SELECT a.institution ,d.strm ,a.acad_prog ,b.oprid ,a.descr ,b.access_cd FROM PS_ACAD_PROG_TBL a ,ps_scrty_tbl_prog b ,ps_term_tbl d WHERE b.access_cd = 'Y' AND a.institution = b.institution AND a.acad_career = b.acad_career AND (d.strm <= a.SSR_LAST_ADM_TERM OR a.SSR_LAST_ADM_TERM = ' ') AND (a.acad_prog = b.acad_prog OR b.acad_prog = 'ALL') AND NOT EXISTS ( SELECT 'X' FROM ps_scrty_tbl_prog c WHERE c.access_cd = 'N' AND c.oprid = b.oprid AND c.institution = b.institution AND c.acad_career = b.acad_career AND c.acad_prog = a.acad_prog) AND a.institution = d.institution AND a.acad_career = d.acad_career AND a.effdt = ( SELECT MAX(effdt) FROM PS_ACAD_PROG_TBL a1 WHERE a.institution = a1.institution AND a.acad_prog = a1.acad_prog AND a1.effdt <= d.term_begin_dt) AND a.eff_status <> 'I' AND a.institution = d.institution

  • Related Language Record: SAD_APP_PG_VW2L

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

    Default Value: OPR_DEF_TBL_CS.INSTITUTION

    Prompt Table: INSTITUTION_TBL

    2 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term

    Prompt Table: ACAD_PROG_TBL

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

    Prompt Table: ACAD_PROG_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 ACCESS_CD Character(1) VARCHAR2(1) NOT NULL Access Code
    N=No Access
    Y=Read/Write Access