FA_BPKG_CRIT_VW(SQL View) |
Index Back |
|---|---|
Batch Packaging Selection ViewBatch Packaging Selection View - This record is used by the front end of batch packaging to select student for processing. |
| SELECT A.EMPLID ,B.INSTITUTION ,B.AID_YEAR ,B.AWARD_PERIOD ,G.ACAD_CAREER ,D.INS_MATCH ,D.PELL_ELIGIBILITY ,D.SSA_CITIZENSHP_IND ,D.SSN_MATCH ,D.TITLEIV_ELIG ,E.AGI ,E.MARITAL_STAT ,E.TAX_FORM_FILED ,F.AGI ,F.DEPENDENTS ,F.DEPNDNCY_STAT ,F.DOB_PRIOR ,F.MARITAL_STAT ,F.MARRIED ,F.NUM_FAMILY_MEMBERS ,F.PAY_NUM_DEPENDENTS ,F.STATE_RESIDENCE ,F.STD_EARNED_INCOME ,F.VETERAN ,A.BIRTHCOUNTRY ,A.BIRTHDATE ,A.BIRTHPLACE ,A.BIRTHSTATE ,A.CAMPUS_ID ,J.MAR_STATUS ,J.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_ISIR_CONTROL D , PS_ISIR_PARENT E , PS_ISIR_STUDENT F , PS_STDNT_FA_TERM G , PS_STDNT_AID_ATRBT H , PS_HCR_PER_PDE_I J 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 = J.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 D.EFFDT = ( SELECT MAX(X.EFFDT) FROM PS_ISIR_CONTROL X WHERE X.EMPLID = D.EMPLID AND X.INSTITUTION = D.INSTITUTION AND X.AID_YEAR = D.AID_YEAR) AND D.EFFDT = E.EFFDT AND D.EFFDT = F.EFFDT AND D.EFFSEQ = ( SELECT MAX (Y.EFFSEQ) FROM PS_ISIR_CONTROL Y WHERE Y.EMPLID = D.EMPLID AND Y.INSTITUTION = D.INSTITUTION AND Y.AID_YEAR = D.AID_YEAR AND Y.EFFDT = D.EFFDT) AND D.EFFSEQ = E.EFFSEQ AND D.EFFSEQ = F.EFFSEQ AND E.TABLE_ID = F.TABLE_ID AND E.TABLE_ID = H.TABLE_ID AND G.EFFDT = ( SELECT MAX(X1.EFFDT) FROM PS_STDNT_FA_TERM X1 WHERE X1.EMPLID = G.EMPLID AND X1.INSTITUTION = G.INSTITUTION AND X1.STRM = G.STRM) AND G.EFFSEQ = ( SELECT MAX(Y1.EFFSEQ) FROM PS_STDNT_FA_TERM Y1 WHERE Y1.EMPLID = G.EMPLID AND Y1.INSTITUTION = G.INSTITUTION AND Y1.STRM = G.STRM AND Y1.EFFDT = G.EFFDT) AND J.EFFDT = ( SELECT MAX(Z1.EFFDT) FROM PS_HCR_PER_PDE_I Z1 WHERE Z1.EMPLID = J.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 | INS_MATCH | Character(1) | VARCHAR2(1) NOT NULL |
INS Match
D=Duplicate case I=Invalid ARN L=Record Returned Late N=Citizenship Not Confirmed Y=Citizenship Confirmed Z=Record Mismatch |
| 7 | PELL_ELIGIBILITY | Character(1) | VARCHAR2(1) NOT NULL |
Federal PELL Eligibility
Y=Yes |
| 8 | SSA_CITIZENSHP_IND | Character(1) | VARCHAR2(1) NOT NULL |
SSA Citizenship Indicator
*=Foreign born A=U.S. Citizen B=Legal Alien, work eligible C=Legal Alien, work ineligible D=Other E=Alien/student restrc/wk elig F=Alien, conditionally legal I=Institutional Override N=No Match on Name or DOB |
| 9 | SSN_MATCH | Character(1) | VARCHAR2(1) NOT NULL |
SSN Match
0=Record Returned Late 1=No Match 2=SSN and Name, no DOB 3=SSN Only, No Name 4=SSN, Name, and DOB Match 5=All match Date of Death 6=SSN not verified 8=Record Not Sent to SSA 9=Record Mismatch I=Institutional Override |
| 10 | TITLEIV_ELIG | Character(1) | VARCHAR2(1) NOT NULL |
Title IV Fund Eligibility
I=Institutional Override Y/N Table Edit |
| 11 | AGI_PAR | Signed Number(11,0) | DECIMAL(10) NOT NULL | AGI-Parent |
| 12 | 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 |
| 13 | 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 |
| 14 | AGI | Signed Number(11,0) | DECIMAL(10) NOT NULL | Adjusted Gross Income |
| 15 | DEPENDENTS | Character(1) | VARCHAR2(1) NOT NULL |
Dependents Other Than Spouse
1=Yes 2=No N=No Y=Yes |
| 16 | DEPNDNCY_STAT | Character(1) | VARCHAR2(1) NOT NULL |
Dependency Status
1=Independent 2=Dependent 3=Indepenedent/Pro Judgmnt D=Dependent I=Independent X=Dependent Reject Y=Independent Reject Z=Provisional Independent |
| 17 | DOB_PRIOR | Character(1) | VARCHAR2(1) NOT NULL |
DOB Before 1/1/74
1=Yes 2=No |
| 18 | MARITAL_STAT | Character(1) | VARCHAR2(1) NOT NULL |
Marital Status
1=Single 2=Married 3=Separated 4=Divorced 5=Widowed |
| 19 | MARRIED | Character(1) | VARCHAR2(1) NOT NULL |
Student Married
1=Yes 2=No |
| 20 | NUM_FAMILY_MEMBERS | Character(2) | VARCHAR2(2) NOT NULL | Nbr in Family |
| 21 | PAY_NUM_DEPENDENTS | Character(2) | VARCHAR2(2) NOT NULL | Care for how many Dependents |
| 22 | STATE_RESIDENCE | Character(6) | VARCHAR2(6) NOT NULL |
State of Residence
Prompt Table: HCR_STATE_I |
| 23 | STD_EARNED_INCOME | Signed Number(11,0) | DECIMAL(10) NOT NULL | Student's Earned Income |
| 24 | VETERAN | Character(1) | VARCHAR2(1) NOT NULL |
Veteran
1=Yes 2=No N=No Y=Yes |
| 25 | BIRTHCOUNTRY | Character(3) | VARCHAR2(3) NOT NULL |
Birth Country
Prompt Table: COUNTRY_TBL |
| 26 | BIRTHDATE | Date(10) | DATE | Date of Birth |
| 27 | BIRTHPLACE | Character(30) | VARCHAR2(30) NOT NULL | Birth Location |
| 28 | BIRTHSTATE | Character(6) | VARCHAR2(6) NOT NULL |
Birth State
Prompt Table: HCR_STATE_I |
| 29 | CAMPUS_ID | Character(16) | VARCHAR2(16) NOT NULL | Campus ID |
| 30 | 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 |
| 31 | SEX | Character(1) | VARCHAR2(1) NOT NULL |
Gender
F=Female M=Male U=Unknown X=Indeterminate/Intersex/Unspec |
| 32 | EFC_STATUS | Character(1) | VARCHAR2(1) NOT NULL |
EFC Status Indicator
O=Official R=Rejected U=Unofficial Z=Provisional |
| 33 | FED_EFC | Signed Number(7,0) | DECIMAL(6) NOT NULL | Federal EFC |
| 34 | FED_NEED | Number(9,2) | DECIMAL(8,2) NOT NULL | Fed Need |
| 35 | FED_PARENT_CONTRB | Signed Number(7,0) | DECIMAL(6) NOT NULL | Parents Contribution - Federal |
| 36 | FED_STDNT_CONTRB | Signed Number(7,0) | DECIMAL(6) NOT NULL | Student Contribution - Federal |
| 37 | INST_EFC | Number(6,0) | INTEGER NOT NULL | Institutional EFC |
| 38 | INST_NEED | Number(9,2) | DECIMAL(8,2) NOT NULL | Inst Need |
| 39 | INST_PARENT_CONTRB | Signed Number(7,0) | DECIMAL(6) NOT NULL | Parents Contribution - Inst |
| 40 | INST_STDNT_CONTRB | Signed Number(7,0) | DECIMAL(6) NOT NULL | Student Contribution - Inst |
| 41 | STRM | Character(4) | VARCHAR2(4) NOT NULL |
Term
Prompt Table: TERM_VAL_TBL |
| 42 | 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 |
| 43 | 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 |
| 44 | 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 |