STDMLST_PLN_SEC

(SQL View)
 Index Back

Prg Sec Stdnt Milestone Search

This view is used to search the student who has terms in the Student Term Table. This view is the plan-level security version of STDNT_MLST_SRCH.

SELECT B.OPRCLASS ,A.EMPLID ,A.INSTITUTION ,A.ACAD_CAREER ,A.ACAD_PROG ,S.OPRID ,B.NAME ,B.SEX ,B.BIRTHDATE ,B.BIRTHDATE_MSK ,B.CAMPUS_ID ,B.NATIONAL_ID ,B.NATIONAL_ID_MSK ,B.NID_COUNTRY ,B.NATIONAL_ID_TYPE ,B.NID_DESCRSHORT ,B.LAST_NAME_SRCH ,B.FIRST_NAME_SRCH FROM PS_STDNT_MLSTN A , PS_PEOPLE_SRCH B , PS_ACAD_PROG PR , PS_ACAD_PLAN PL , PS_SCRTY_TBL_PLAN S WHERE A.EMPLID = B.EMPLID AND A.EMPLID = PR.EMPLID AND A.ACAD_CAREER = PR.ACAD_CAREER AND A.INSTITUTION = PR.INSTITUTION AND PR.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_ACAD_PROG F WHERE F.INSTITUTION = PR.INSTITUTION AND F.EMPLID = PR.EMPLID AND F.ACAD_CAREER = PR.ACAD_CAREER AND F.STDNT_CAR_NBR = PR.STDNT_CAR_NBR AND F.EFFDT <= %CurrentDateIn) AND PR.EFFSEQ = ( SELECT MAX(H.EFFSEQ) FROM PS_ACAD_PROG H WHERE H.INSTITUTION = PR.INSTITUTION AND H.EMPLID = PR.EMPLID AND H.ACAD_CAREER = PR.ACAD_CAREER AND H.STDNT_CAR_NBR = PR.STDNT_CAR_NBR AND H.EFFDT = PR.EFFDT) AND PR.EMPLID = PL.EMPLID AND PR.ACAD_CAREER = PL.ACAD_CAREER AND PR.STDNT_CAR_NBR = PL.STDNT_CAR_NBR AND PR.EFFDT = PL.EFFDT AND PR.EFFSEQ = PL.EFFSEQ AND PR.INSTITUTION = S.INSTITUTION AND (S.ACAD_PLAN = PL.ACAD_PLAN OR S.ACAD_PLAN = 'ALL') AND S.ACCESS_CD = 'Y' AND NOT EXISTS ( SELECT 'X' FROM PS_SCRTY_TBL_PLAN C4 WHERE C4.ACCESS_CD = 'N' AND C4.OPRID = S.OPRID AND C4.INSTITUTION = PR.INSTITUTION AND C4.ACAD_PLAN = PL.ACAD_PLAN)

  • Parent record: ACAD_PROG

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 OPRCLASS Character(30) VARCHAR2(30) NOT NULL Operator Class
    2 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    3 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Default Value: OPR_DEF_TBL_CS.INSTITUTION

    Prompt Table: INSTITUTION_TBL

    4 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
    5 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program

    Prompt Table: ACAD_PROG_TBL

    6 OPRID Character(30) VARCHAR2(30) NOT NULL A user's ID (see PSOPRDEFN).
    7 NAME Character(50) VARCHAR2(50) NOT NULL Name
    8 SEX Character(1) VARCHAR2(1) NOT NULL Gender
    F=Female
    M=Male
    U=Unknown
    X=Indeterminate/Intersex/Unspec
    9 BIRTHDATE Date(10) DATE Date of Birth
    10 BIRTHDATE_MSK Character(10) VARCHAR2(10) NOT NULL Date of Birth
    11 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
    12 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
    13 NATIONAL_ID_MSK Character(20) VARCHAR2(20) NOT NULL National ID
    14 NID_COUNTRY Character(3) VARCHAR2(3) NOT NULL National ID Country
    15 NATIONAL_ID_TYPE Character(6) VARCHAR2(6) NOT NULL National ID Type

    Prompt Table: HCR_NID_TYP_I
    Set Control Field: NID_COUNTRY

    16 NID_DESCRSHORT Character(10) VARCHAR2(10) NOT NULL NID Short Description
    17 LAST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL Last Name
    18 FIRST_NAME_SRCH Character(30) VARCHAR2(30) NOT NULL First Name