STUBGT_PLN_SCTY

(SQL View)
 Index Back

Stu Budget Plan Security Vw

This view is used to impose plan-level security on specific Financial Aid components. (See Security View setup under Secure Student Administration.) It selects student from both the admissions and program stacks. It is a clone of STDNT_BDGT_SRCH.

SELECT a.emplid , a.institution , a.aid_year , f.oprid , b.name , f.access_cd FROM PS_STUDENT_AID a , PS_PEOPLE_SRCH b , PS_ADM_PROG_PLN_VW e , PS_SCRTY_TBL_PLAN f WHERE a.emplid = b.emplid AND a.emplid IN ( SELECT DISTINCT D.EMPLID FROM PS_STDNT_FA_TERM D WHERE D.EMPLID = A.EMPLID AND D.INSTITUTION = A.INSTITUTION AND D.STRM=( SELECT MAX(D1.STRM) FROM PS_STDNT_FA_TERM D1 WHERE D1.EMPLID=A.EMPLID AND D1.INSTITUTION=A.INSTITUTION AND D1.AID_YEAR=A.AID_YEAR AND D1.EFF_STATUS = 'A') AND D.EFFDT = ( SELECT MAX(D2.EFFDT) FROM PS_STDNT_FA_TERM D2 WHERE D2.EMPLID = D.EMPLID AND D2.INSTITUTION = D.INSTITUTION AND D2.STRM = D.STRM AND D2.EFFDT <= %CurrentDateIn) AND D.EFFSEQ = ( SELECT MAX(D3.EFFSEQ) FROM PS_STDNT_FA_TERM D3 WHERE D3.EMPLID = D.EMPLID AND D3.INSTITUTION = D.INSTITUTION AND D3.STRM = D.STRM AND D3.EFFDT = D.EFFDT)) AND a.emplid NOT IN ( SELECT c.emplid FROM PS_STDNT_BDGT_ASGN c WHERE c.processing_mode = 'B' AND c.aid_year = a.aid_year) AND a.emplid = e.emplid AND a.institution = f.institution AND (f.acad_plan = e.acad_plan OR f.acad_plan = 'ALL') AND f.access_cd = 'Y' AND NOT EXISTS ( SELECT 'X' FROM PS_SCRTY_TBL_PLAN f1 WHERE f1.access_cd = 'N' AND f1.oprid = f.oprid AND f1.institution = a.institution AND f1.acad_plan = e.acad_plan)

  • Parent record: STUDENT_AID

    • # 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 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Default Value: OPR_DEF_TBL_CS.INSTITUTION

    Prompt Table: INSTITUTION_TBL

    3 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year

    Default Value: OPR_DEF_TBL_CS.AID_YEAR

    Prompt Table: AID_YEAR_TBL

    4 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    5 NAME Character(50) VARCHAR2(50) NOT NULL Name
    6 ACCESS_CD Character(1) VARCHAR2(1) NOT NULL Access Code
    N=No Access
    Y=Read/Write Access