SEL_FATERMNR_VW

(SQL View)
 Index Back

FA Term No Residency

This view is used to select by term the career, program, plan that a student is enrolled in. It is the non residency version of the sel_faterm_vw. It is intended to be used for schools that do not track residency. This view is only used by two COBOL programs, SFPFAFEE.CBL and SFPSTDNT.CBL. OwnerID changed from FA-FA Term to Student Financials.

SELECT A.EMPLID ,A.STRM ,A.INSTITUTION ,A.BILLING_CAREER ,A.ACAD_CAREER ,B.ACAD_GROUP ,A.ACAD_PROG_PRIMARY ,A.ACAD_PLAN ,C.TERM_BEGIN_DT ,D.ACAD_PLAN_TYPE ,A.WITHDRAW_CODE ,A.WITHDRAW_REASON ,A.WITHDRAW_DATE ,A.LAST_DATE_ATTENDED ,A.ACAD_PROG_PRIMARY ,A.ACADEMIC_LOAD ,A.PROJ_BILL_UNT ,A.FORM_OF_STUDY ,A.ACAD_LOAD_APPR ,A.ACAD_LEVEL_BOT ,A.ACAD_LEVEL_EOT ,A.ACAD_LEVEL_PROJ ,A.ELIG_TO_ENROLL ,D.DEGREE ,A.UNT_TAKEN_FA ,A.TOT_TAKEN_FA ,A.TOT_PASSD_FA ,A.ACAD_YEAR ,A.ACAD_GROUP_ADVIS ,H.STDNT_CAR_NBR ,H.EFFDT ,H.EFFSEQ ,A.CUR_RESIDENT_TERMS ,A.TRF_RESIDENT_TERMS ,A.CUM_RESIDENT_TERMS ,A.ADMIT_TERM ,H.PROG_STATUS ,A.CAMPUS ,A.TUIT_CALC_REQ ,A.TOT_TERM_UNT_FA ,A.TERM_TYPE ,A.COUNTRY ,A.STUDY_AGREEMENT ,A.START_DATE ,A.END_DATE ,A.CUM_GPA ,G.BIRTHDATE FROM PS_STDNT_FA_TERM A ,PS_ACAD_PROG_TBL B ,PS_TERM_TBL C ,PS_ACAD_PLAN_TBL D ,PS_SCC_PERS_SA_VW G ,PS_ACAD_PROG H WHERE A.EFFDT = ( SELECT MAX(A1.EFFDT) FROM PS_STDNT_FA_TERM A1 WHERE A1.EMPLID = A.EMPLID AND A1.INSTITUTION = A.INSTITUTION AND A1.STRM = A.STRM AND A1.EFFDT <= %CurrentDateIn) AND A.EFFSEQ = ( SELECT MAX(A2.EFFSEQ) FROM PS_STDNT_FA_TERM A2 WHERE A2.EMPLID = A.EMPLID AND A2.INSTITUTION = A.INSTITUTION AND A2.STRM = A.STRM AND A2.EFFDT = A.EFFDT) AND A.EFF_STATUS = 'A' AND A.INSTITUTION = C.INSTITUTION AND A.ACAD_CAREER = C.ACAD_CAREER AND A.STRM=C.STRM AND A.INSTITUTION=D.INSTITUTION AND A.ACAD_PLAN=D.ACAD_PLAN AND D.EFFDT= ( SELECT MAX(E.EFFDT) FROM PS_ACAD_PLAN_TBL E WHERE D.INSTITUTION=E.INSTITUTION AND D.ACAD_PLAN=E.ACAD_PLAN AND D.EFFDT <= C.TERM_BEGIN_DT AND D.EFF_STATUS <> 'I' ) AND A.INSTITUTION=B.INSTITUTION AND A.ACAD_PROG_PRIMARY=B.ACAD_PROG AND B.EFFDT= ( SELECT MAX(EFFDT) FROM PS_ACAD_PROG_TBL Z WHERE Z.INSTITUTION=B.INSTITUTION AND Z.ACAD_PROG=B.ACAD_PROG AND Z.EFFDT <= C.TERM_BEGIN_DT AND Z.EFF_STATUS <> 'I' ) AND A.EMPLID = G.EMPLID AND H.EMPLID = A.EMPLID AND H.ACAD_CAREER = A.ACAD_CAREER AND H.STDNT_CAR_NBR = A.STDNT_CAR_NBR AND H.EFFDT = ( SELECT MAX (X.EFFDT) FROM PS_ACAD_PROG X WHERE X.EMPLID = H.EMPLID AND X.ACAD_CAREER = H.ACAD_CAREER AND X.STDNT_CAR_NBR = H.STDNT_CAR_NBR AND X.EFFDT <= C.SSR_TRMAC_LAST_DT) AND H.EFFSEQ = ( SELECT MAX (XX.EFFSEQ) FROM PS_ACAD_PROG XX WHERE XX.EMPLID = H.EMPLID AND XX.ACAD_CAREER = H.ACAD_CAREER AND XX.STDNT_CAR_NBR = H.STDNT_CAR_NBR AND XX.EFFDT = H.EFFDT)

  • Parent record: HCR_PERSON_I

    • # 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

    Prompt Table: ACAD_LEVEL_TBL

    21 ACAD_LEVEL_EOT Character(3) VARCHAR2(3) NOT NULL Academic Level - Term End

    Prompt Table: ACAD_LEVEL_TBL

    22 ACAD_LEVEL_PROJ Character(3) VARCHAR2(3) NOT NULL Academic Level - Projected

    Prompt Table: ACAD_LEVEL_TBL

    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 UNT_TAKEN_PRGRSS Number(9,3) DECIMAL(8,3) NOT NULL Units Taken for Progress
    26 TOT_TAKEN_PRGRSS Number(9,3) DECIMAL(8,3) NOT NULL Total Taken for Progress
    27 TOT_PASSD_PRGRSS Number(9,3) DECIMAL(8,3) NOT NULL Total Passed for Progress
    28 ACAD_YEAR Character(4) VARCHAR2(4) NOT NULL Academic Year
    29 ACAD_GROUP_ADVIS Character(5) VARCHAR2(5) NOT NULL Academic Group of Advisor
    30 STDNT_CAR_NBR Number(3,0) SMALLINT NOT NULL Student Career Nbr
    31 EFFDT Date(10) DATE Effective Date
    32 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
    33 CUR_RESIDENT_TERMS Number(7,3) DECIMAL(6,3) NOT NULL Current In Residence Terms
    34 TRF_RESIDENT_TERMS Number(7,3) DECIMAL(6,3) NOT NULL Transfer In Residence Terms
    35 CUM_RESIDENT_TERMS Number(7,3) DECIMAL(6,3) NOT NULL Cumulative In Residence Terms
    36 ADMIT_TERM Character(4) VARCHAR2(4) NOT NULL Admit Term
    37 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
    38 CAMPUS Character(5) VARCHAR2(5) NOT NULL Campus
    39 TUIT_CALC_REQ Character(1) VARCHAR2(1) NOT NULL Tuition Calc Required
    40 UNT_TERM_TOT Number(9,3) DECIMAL(8,3) NOT NULL Total Term Units
    41 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
    42 COUNTRY_OTHER Character(3) VARCHAR2(3) NOT NULL Country field for ADDR_OTR_SBR and DERIVED_ADDR_OT
    43 STUDY_AGREEMENT Character(10) VARCHAR2(10) NOT NULL Study Agreement
    44 START_DATE Date(10) DATE Start Date for Gen Standing PO
    45 END_DATE Date(10) DATE End Date
    46 CUM_GPA Number(9,3) DECIMAL(8,3) NOT NULL Cumulative GPA
    47 BIRTHDATE Date(10) DATE Date of Birth