SEL_STDNORES_VW

(SQL View)
 Index Back

Student Data View No Residency

This view is used to select by term the career, program, plan and residency status that a student is enrolled in.

SELECT k.emplid ,k.strm ,k.institution ,k.billing_career ,k.acad_career ,b.acad_group ,b.acad_prog ,a.acad_plan ,c.term_begin_dt ,z.acad_plan_type ,k.withdraw_code ,K.WITHDRAW_REASON ,k.withdraw_date ,k.last_date_attended ,k.acad_prog_primary ,k.academic_load ,k.proj_bill_unt ,k.form_of_study ,k.acad_load_appr ,k.acad_level_bot ,k.acad_level_eot ,k.acad_level_proj ,k.elig_to_enroll ,z.degree ,' ' ,' ' ,' ' ,' ' ,' ' ,' ' ,k.unt_taken_prgrss ,k.tot_taken_prgrss ,k.tot_passd_prgrss ,k.tot_cumulative ,k.acad_year ,k.acad_group_advis ,l.stdnt_car_nbr ,l.effdt ,l.effseq ,k.cur_resident_terms ,k.trf_resident_terms ,k.cum_resident_terms ,l.admit_term ,l.prog_status ,l.campus ,k.tuit_calc_req ,k.unt_term_tot ,K.TERM_TYPE ,K.COUNTRY ,K.STUDY_AGREEMENT ,K.START_DATE ,K.END_DATE ,K.CUR_GPA ,K.CUM_GPA ,K.SEL_GROUP ,K.EXT_ORG_ID ,K.REGISTERED ,K.TUITION_RES_TERMS ,K.PRO_RATA_ELIGIBLE ,D.BIRTHDATE FROM PS_ACAD_PLAN a , ps_acad_prog_tbl b , ps_acad_prog l , ps_stdnt_car_term k , ps_term_tbl c , PS_ACAD_PLAN_tbl z , PS_SCC_PERS_SA_VW D WHERE b.institution = l.institution AND b.acad_prog = l.acad_prog AND l.institution = c.institution AND k.acad_career = b.acad_career AND z.institution = l.institution AND z.acad_plan = a.acad_plan AND k.strm = c.strm AND k.billing_career = c.acad_career AND k.emplid = a.emplid AND l.emplid = a.emplid AND A.EMPLID = D.EMPLID AND l.acad_career = a.acad_career AND l.Stdnt_Car_Nbr = a.Stdnt_Car_Nbr AND l.Effdt = a.Effdt AND l.Effseq = a.Effseq AND z.effdt = ( SELECT MAX(x.effdt) FROM PS_ACAD_PLAN_tbl x WHERE x.institution = z.institution AND x.acad_plan = z.acad_plan AND x.effdt <= c.term_begin_dt) AND b.effdt = ( SELECT MAX(f.effdt) FROM ps_acad_prog_tbl f WHERE f.institution = b.institution AND f.acad_prog = b.acad_prog AND f.effdt <= c.term_begin_dt) AND l.effdt = ( SELECT MAX(g.effdt) FROM ps_acad_prog g WHERE g.emplid = l.emplid AND g.acad_career=l.acad_career AND g.stdnt_car_nbr=l.stdnt_car_nbr AND g.effdt <= c.SSR_TRMAC_LAST_DT) AND l.effseq = ( SELECT MAX(h.effseq) FROM ps_acad_prog h WHERE h.emplid = l.emplid AND h.acad_career = l.acad_career AND h.stdnt_car_nbr=l.stdnt_car_nbr AND h.effdt = l.effdt) AND l.prog_status IN ('AC','AD') AND k.institution = l.institution

  • Parent record: PERSON

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID
    2 STRM Character(4) VARCHAR2(4) NOT NULL Term
    3 INSTITUTION Character(5) VARCHAR2(5) NOT NULL Academic Institution

    Prompt Table: INSTITUTION_TBL

    4 BILLING_CAREER Character(4) VARCHAR2(4) NOT NULL Billing Career
    5 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

    Prompt Table: ACAD_CAR_TBL

    6 ACAD_GROUP Character(5) VARCHAR2(5) NOT NULL Academic Group
    7 ACAD_PROG Character(5) VARCHAR2(5) NOT NULL Academic Program

    Prompt Table: ACAD_PROG_TBL

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

    Prompt Table: ACAD_PLAN_TBL

    9 TERM_BEGIN_DT Date(10) DATE Term Begin Date
    10 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
    11 WITHDRAW_CODE Character(3) VARCHAR2(3) NOT NULL Withdrawal \ Cancel
    CAN=Cancelled
    NWD=
    WDR=Withdrew
    12 WITHDRAW_REASON Character(4) VARCHAR2(4) NOT NULL Withdrawal \ Cancel Reason
    MEDI=Medical
    NPAY=Non-Payment of Fees
    RETR=Retroactive
    13 WITHDRAW_DATE Date(10) DATE Withdrawal \ Cancel Date
    14 LAST_DATE_ATTENDED Date(10) DATE Last Date of Attendance
    15 ACAD_PROG_PRIMARY Character(5) VARCHAR2(5) NOT NULL Primary Academic Program

    Prompt Table: ACAD_PROG_TBL

    16 ACADEMIC_LOAD Character(1) VARCHAR2(1) NOT NULL Academic Load
    F=Enrolled Full-Time
    H=Enrolled Half-Time
    L=Less than Half-Time
    N=No Unit Load
    P=Enrolled Part-Time
    T=Three Quarter Time
    17 PROJ_BILL_UNT Number(3,0) SMALLINT NOT NULL Projected Bill Units
    18 FORM_OF_STUDY Character(4) VARCHAR2(4) NOT NULL Form of Study
    ABRD=Study Abroad
    CAND=Advanced to Candidacy
    DTCH=Detached Study
    ENRL=Class Enrollment
    19 ACAD_LOAD_APPR Character(1) VARCHAR2(1) NOT NULL Approved Academic Load
    B=Vocational Coaching (NLD)
    D=Dual (NLD)
    E=Part-time Vocational Tr (NLD)
    F=Full-Time
    O=Vocational Training (NLD)
    P=Part-Time
    20 ACAD_LEVEL_BOT Character(3) VARCHAR2(3) NOT NULL Academic Level - Term Start
    21 ACAD_LEVEL_EOT Character(3) VARCHAR2(3) NOT NULL Academic Level - Term End
    22 ACAD_LEVEL_PROJ Character(3) VARCHAR2(3) NOT NULL Academic Level - Projected
    23 ELIG_TO_ENROLL Character(1) VARCHAR2(1) NOT NULL Eligible to Enroll

    Y/N Table Edit

    24 DEGREE Character(8) VARCHAR2(8) NOT NULL Degree

    Prompt Table: DEGREE_TBL

    25 STATE Character(6) VARCHAR2(6) NOT NULL State
    26 COUNTY Character(30) VARCHAR2(30) NOT NULL County
    27 SCC_DISTRICT Character(30) VARCHAR2(30) NOT NULL District
    28 COUNTRY Character(3) VARCHAR2(3) NOT NULL Country
    29 POSTAL Character(12) VARCHAR2(12) NOT NULL Postal
    30 TUITION_RES Character(5) VARCHAR2(5) NOT NULL Tuition Residency
    31 UNT_TAKEN_PRGRSS Number(9,3) DECIMAL(8,3) NOT NULL Units Taken for Progress
    32 TOT_TAKEN_PRGRSS Number(9,3) DECIMAL(8,3) NOT NULL Total Taken for Progress
    33 TOT_PASSD_PRGRSS Number(9,3) DECIMAL(8,3) NOT NULL Total Passed for Progress
    34 TOT_CUMULATIVE Number(9,3) DECIMAL(8,3) NOT NULL Total Cumulative Units
    35 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
    36 ACAD_GROUP_ADVIS Character(5) VARCHAR2(5) NOT NULL Academic Group of Advisor
    37 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr
    38 EFFDT Date(10) DATE Effective Date

    Default Value: %date

    39 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    40 CUR_RESIDENT_TERMS Number(7,3) DECIMAL(6,3) NOT NULL Current In Residence Terms
    41 TRF_RESIDENT_TERMS Number(7,3) DECIMAL(6,3) NOT NULL Transfer In Residence Terms
    42 CUM_RESIDENT_TERMS Number(7,3) DECIMAL(6,3) NOT NULL Cumulative In Residence Terms
    43 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term
    44 PROG_STATUS Character(4) VARCHAR2(4) NOT NULL Academic Program Status
    AC=Active in Program
    AD=Admitted
    AP=Applicant
    CM=Completed Program
    CN=Cancelled
    DC=Discontinued
    DE=Deceased
    DM=Dismissed
    LA=Leave of Absence
    PM=Prematriculant
    SP=Suspended
    WT=Waitlisted
    45 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus
    46 TUIT_CALC_REQ Character(1) VARCHAR2(1) NOT NULL Tuition Calc Required
    47 UNT_TERM_TOT Number(9,3) DECIMAL(8,3) NOT NULL Total Term Units
    48 TERM_TYPE Character(1) VARCHAR2(1) NOT NULL Term Unit Type
    C=Clock Hours
    E=Continuing Education Units
    Q=Quarter Hours
    S=Semester Hours
    T=Trimester Hours
    U=Units
    49 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT
    50 STUDY_AGREEMENT Character(10) VARCHAR2(10) NOT NULL Study Agreement
    51 START_DATE Date(10) DATE Start Date for Gen Standing PO
    52 END_DATE Date(10) DATE End Date
    53 CUR_GPA Number(9,3) DECIMAL(8,3) NOT NULL Current GPA
    54 CUM_GPA Number(9,3) DECIMAL(8,3) NOT NULL Cumulative GPA
    55 SEL_GROUP Character(10) VARCHAR2(10) NOT NULL Tuition Group
    56 EXT_ORG_ID Character(11) VARCHAR2(11) NOT NULL External Org ID
    57 REGISTERED Character(1) VARCHAR2(1) NOT NULL Registered
    R=Registered
    58 TUITION_RES_TERMS Number(7,3) DECIMAL(6,3) NOT NULL Tuition Residency
    59 PRO_RATA_ELIGIBLE Character(1) VARCHAR2(1) NOT NULL Pro-Rata Eligible
    60 BIRTHDATE Date(10) DATE Date of Birth