FA_INST_CRIT_VW

(SQL View)
 Index Back

Inst Batch Pckg Select View

Institutional Batch Packaging Selection View - This record is used by the front end of batch packaging to select student with institutional apps for processing.

SELECT A.EMPLID ,B.INSTITUTION ,B.AID_YEAR ,B.AWARD_PERIOD ,G.ACAD_CAREER ,E.AGI ,E.MARITAL_STAT ,E.TAX_FORM_FILED ,F.AGI ,F.DEPENDENTS ,F.MARITAL_STAT ,F.NUM_FAMILY_MEMBERS ,F.STATE_RESIDENCE ,F.STD_EARNED_INCOME ,F.VETERAN ,A.BIRTHCOUNTRY ,A.BIRTHDATE ,A.BIRTHPLACE ,A.BIRTHSTATE ,A.CAMPUS_ID ,K.MAR_STATUS ,K.SEX ,B.EFC_STATUS ,B.FED_EFC ,B.FED_NEED ,B.FED_PARENT_CONTRB ,B.FED_STDNT_CONTRB ,B.INST_EFC ,B.INST_NEED ,B.INST_PARENT_CONTRB ,B.INST_STDNT_CONTRB ,G.STRM ,H.PROCESSING_STATUS ,H.AID_APP_STATUS ,G.FA_LOAD_CHANGE_IND FROM PS_SCC_PERS_SA_VW A , PS_STDNT_AWD_PER B , PS_INST_CONTROL D , PS_INST_PARENT E , PS_INST_STUDENT F , PS_STDNT_FA_TERM G , PS_STDNT_AID_ATRBT H , PS_INSTALLATION_FA J , PS_HCR_PER_PDE_I K WHERE A.EMPLID = B.EMPLID AND A.EMPLID = D.EMPLID AND A.EMPLID = E.EMPLID AND A.EMPLID = F.EMPLID AND A.EMPLID = G.EMPLID AND A.EMPLID = H.EMPLID AND A.EMPLID = K.EMPLID AND B.INSTITUTION = D.INSTITUTION AND B.INSTITUTION = E.INSTITUTION AND B.INSTITUTION = F.INSTITUTION AND B.INSTITUTION = G.INSTITUTION AND B.INSTITUTION = H.INSTITUTION AND B.AID_YEAR = D.AID_YEAR AND B.AID_YEAR = E.AID_YEAR AND B.AID_YEAR = F.AID_YEAR AND B.AID_YEAR = G.AID_YEAR AND B.AID_YEAR = H.AID_YEAR AND J.INST_APP_SOURCE = D.APP_SOURCE_CODE AND J.INST_APP_SOURCE = E.APP_SOURCE_CODE AND J.INST_APP_SOURCE = F.APP_SOURCE_CODE AND K.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PS_HCR_PER_PDE_I X1 WHERE X1.EMPLID = K.EMPLID) AND G.EFFDT = ( SELECT MAX(G1.EFFDT) FROM PS_STDNT_FA_TERM G1 WHERE G1.EMPLID = G.EMPLID AND G1.INSTITUTION = G.INSTITUTION AND G1.STRM = G.STRM AND G1.EFFDT <= %CurrentDateIn) AND G.EFFSEQ = ( SELECT MAX(G2.EFFSEQ) FROM PS_STDNT_FA_TERM G2 WHERE G2.EMPLID = G.EMPLID AND G2.INSTITUTION = G.INSTITUTION AND G2.STRM = G.STRM AND G2.EFFDT = G.EFFDT) AND G.EFF_STATUS = 'A'

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

Prompt Table: INSTITUTION_TBL

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

Prompt Table: FED_AID_YR_TBL

4 AWARD_PERIOD Character(1) VARCHAR2(1) NOT NULL Award Period
A=Academic
N=Non Standard
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
6 AGI_PAR Signed Number(11,0) DECIMAL(10) NOT NULL AGI-Parent
7 MARITAL_STAT_PAR Character(1) VARCHAR2(1) NOT NULL Marital Status-Parents
1=Single
2=Married
3=Separated
4=Divorced
5=Widowed
6=Never Married
8 TAX_FORM_FILED Character(1) VARCHAR2(1) NOT NULL Tax Form Filed
1=Completed 1040A/EZ
2=Completed 1040
3=Estimated 1040A/EZ
4=Estimated 1040
5=Will Not File
9 AGI Signed Number(11,0) DECIMAL(10) NOT NULL Adjusted Gross Income
10 DEPENDENTS Character(1) VARCHAR2(1) NOT NULL Dependents Other Than Spouse
1=Yes
2=No
N=No
Y=Yes
11 MARITAL_STAT Character(1) VARCHAR2(1) NOT NULL Marital Status
1=Single
2=Married
3=Separated
4=Divorced
5=Widowed
12 NUM_FAMILY_MEMBERS Character(2) VARCHAR2(2) NOT NULL Nbr in Family
13 STATE_RESIDENCE Character(6) VARCHAR2(6) NOT NULL State of Residence

Prompt Table: HCR_STATE_I

14 STD_EARNED_INCOME Signed Number(11,0) DECIMAL(10) NOT NULL Student's Earned Income
15 VETERAN Character(1) VARCHAR2(1) NOT NULL Veteran
1=Yes
2=No
N=No
Y=Yes
16 BIRTHCOUNTRY Character(3) VARCHAR2(3) NOT NULL Birth Country

Prompt Table: COUNTRY_TBL

17 BIRTHDATE Date(10) DATE Date of Birth
18 BIRTHPLACE Character(30) VARCHAR2(30) NOT NULL Birth Location
19 BIRTHSTATE Character(6) VARCHAR2(6) NOT NULL Birth State

Prompt Table: HCR_STATE_I

20 CAMPUS_ID Character(16) VARCHAR2(16) NOT NULL Campus ID
21 MAR_STATUS Character(1) VARCHAR2(1) NOT NULL Marital Status
C=Common-Law
D=Divorced
E=Separated
H=Head of Household
L=DissDeclLost Civil Partner
M=Married
P=Civil Partnership
S=Single
T=Surviving Civil Partner
U=Unknown
V=Dissolved Civil Partnership
W=Widowed
22 SEX Character(1) VARCHAR2(1) NOT NULL Gender
F=Female
M=Male
U=Unknown
X=Indeterminate/Intersex/Unspec
23 EFC_STATUS Character(1) VARCHAR2(1) NOT NULL EFC Status Indicator
O=Official
R=Rejected
U=Unofficial
Z=Provisional
24 FED_EFC Signed Number(7,0) DECIMAL(6) NOT NULL Federal EFC
25 FED_NEED Number(9,2) DECIMAL(8,2) NOT NULL Fed Need
26 FED_PARENT_CONTRB Signed Number(7,0) DECIMAL(6) NOT NULL Parents Contribution - Federal
27 FED_STDNT_CONTRB Signed Number(7,0) DECIMAL(6) NOT NULL Student Contribution - Federal
28 INST_EFC Number(6,0) INTEGER NOT NULL Institutional EFC
29 INST_NEED Number(9,2) DECIMAL(8,2) NOT NULL Inst Need
30 INST_PARENT_CONTRB Signed Number(7,0) DECIMAL(6) NOT NULL Parents Contribution - Inst
31 INST_STDNT_CONTRB Signed Number(7,0) DECIMAL(6) NOT NULL Student Contribution - Inst
32 STRM Character(4) VARCHAR2(4) NOT NULL Term

Prompt Table: TERM_VAL_TBL

33 PROCESSING_STATUS Character(1) VARCHAR2(1) NOT NULL Aid Package Processing Status
0=No Electronic Application
1=Applied
2=Ready For Counselor Review
3=Ready for Packaging
4=Repackage
9=Packaging Completed
34 AID_APP_STATUS Character(1) VARCHAR2(1) NOT NULL Aid Application Status
A=Application Active for All Aid
C=Application Canceled
R=Apply for Restricted Aid Only
35 FA_LOAD_CHANGE_IND Character(1) VARCHAR2(1) NOT NULL FATerm Load Changed
B=Both Loads Changed
C=Current Load Changed
F=FA Load Changed
N=Neither Load Changed