SEL_STDNORE2_VW

(SQL View)
 Index Back

Stdnt Data View No Residency 2

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.STDNT_CAR_NBR = K.STDNT_CAR_NBR AND G.EFFDT <= K.SSR_ACTIVATION_DT AND G.PROG_STATUS IN ('AC', 'AD')) OR (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.STDNT_CAR_NBR <> K.STDNT_CAR_NBR AND G.EFFDT <= C.SSR_TRMAC_LAST_DT) AND L.PROG_STATUS IN ('AC','AD'))) 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 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