SM_ASOF_ISIR_VW

(SQL View)
 Index Back

ISIR SUMM VIEW

This view selects ISIR data as of a user defined date.

SELECT A.EMPLID , A.INSTITUTION , A.AID_YEAR , A.EFFDT , A.EFFSEQ , A.DT_APP_RECEIVED , A.ORIG_SSN , A.NAME_CD , A.ISIR_TXN_NBR , A.PELL_ELIGIBILITY , A.SS_MATCH , A.SS_REGISTRATION , A.SSN_MATCH , A.INS_MATCH , A.INS_VERF_NUM , A.VERF_SELECTION_IND , A.SAR_C_FLAG , A.SSA_CITIZENSHP_IND , B.DEPNDNCY_STAT , C.NATIONAL_ID , A.VA_MATCH FROM PS_ISIR_CONTROL A , PS_ISIR_STUDENT B , PS_SEC_A_PD_VW C WHERE A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.AID_YEAR = B.AID_YEAR AND A.EFFDT = B.EFFDT AND A.EFFSEQ = B.EFFSEQ AND A.EMPLID = C.EMPLID

# 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
3 AID_YEAR Character(4) VARCHAR2(4) NOT NULL Aid Year
4 EFFDT Date(10) DATE Effective Date

Default Value: %date

5 EFFSEQ Number(3,0) SMALLINT NOT NULL Effective Sequence
6 DT_APP_RECEIVED Date(10) DATE Date Application Received
7 ORIG_SSN Character(9) VARCHAR2(9) NOT NULL Original SSN
8 NAME_CD Character(2) VARCHAR2(2) NOT NULL Name Code
9 ISIR_TXN_NBR Number(2,0) SMALLINT NOT NULL Transaction Num
10 PELL_ELIGIBILITY Character(1) VARCHAR2(1) NOT NULL Federal PELL Eligibility
Y=Yes
11 SS_MATCH Character(1) VARCHAR2(1) NOT NULL Selective Service Match
I=Institutional Override
L=Record Returned Late
N=Regist Status Not Confirmed
T=Temporarily Exempt
Y=Registration Status Confirmed
Z=Record Mismatch
12 SS_REGISTRATION Character(1) VARCHAR2(1) NOT NULL Selective Service Registration
I=Institutional Override
L=Record Returned Late
N=Registration not completed
T=Temporarily Exempt
Y=Registration Confirmed
Z=Record Mismatch
13 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
14 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
15 INS_VERF_NUM Character(15) VARCHAR2(15) NOT NULL INS Verification Nbr
16 VERF_SELECTION_IND Character(2) VARCHAR2(2) NOT NULL Verification Selection Ind
*=Subsequent Trans Select
N=Not Selected
Y=Selected
17 SAR_C_FLAG Character(1) VARCHAR2(1) NOT NULL Indicates When 'C' Printed SAR
Y=SAR C Flag Set
18 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
19 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
20 NATIONAL_ID Character(20) VARCHAR2(20) NOT NULL National ID
21 VA_MATCH Character(1) VARCHAR2(1) NOT NULL Veteran Status Match Flag
1=Veteran status confirmed
2=Record found on VA db not Qual
3=Record not found on VA db
4=Active Duty
8=Record not sent to VA