PI#27

SFA_SIM_OTHVW

(SQL View)

SELECT DISTINCT A.EMPLID ,A.INSTITUTION ,A.AID_YEAR ,A.EFFDT ,A.EFFSEQ ,B.DEPNDNCY_STAT ,A.DEPNDNCY_OVERRIDE ,A.DT_APP_RECEIVED ,B.DT_APP_COMPLETED ,B.APP_SIGNED_BY ,A.FATHER_SSN_MATCH ,A.MOTHER_SSN_MATCH , E.MOTHER_DOB , E.FATHER_DOB , A.ADJ_EFC_CALC_REQ FROM %Table(ISIR_CONTROL) A , %Table(INAS_SIM_STU) B , %Table(INAS_SIM_PAR) E WHERE A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.AID_YEAR = B.AID_YEAR AND A.EMPLID = E.EMPLID AND A.INSTITUTION = E.INSTITUTION AND A.AID_YEAR = E.AID_YEAR AND A.EFFDT = ( SELECT MAX(C.EFFDT) FROM %Table(ISIR_CONTROL) C WHERE C.EMPLID = A.EMPLID AND C.INSTITUTION = A.INSTITUTION AND C.AID_YEAR = A.AID_YEAR) AND A.EFFSEQ = ( SELECT MAX(D.EFFSEQ) FROM %Table(ISIR_CONTROL) D WHERE D.EMPLID = A.EMPLID AND D.INSTITUTION = A.INSTITUTION AND D.AID_YEAR = A.AID_YEAR AND D.EFFDT = A.EFFDT)

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

7 DEPNDNCY_OVERRIDE Character(1) VARCHAR2(1) NOT NULL Dependency Override Indicator
1=Dependent To Independent
2=Cancel Dep Override-Corr Only
3=Override Failed
4=Homeless Youth Determination

8 DT_APP_RECEIVED Date(10) DATE Date Application Received

9 DT_APP_COMPLETED Date(10) DATE Date Application Completed

10 APP_SIGNED_BY Character(1) VARCHAR2(1) NOT NULL Application Signed By
A=Applicant
B=Applicant and Parent

11 FATHER_SSN_MATCH Character(1) VARCHAR2(1) NOT NULL Father's SSN Match
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
I=Institutional Override

12 MOTHER_SSN_MATCH Character(1) VARCHAR2(1) NOT NULL Mother's SSN Match
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
I=Institutional Override

13 MOTHER_DOB Date(10) DATE Mother's Date of Birth

14 FATHER_DOB Date(10) DATE Father's Date of Birth

15 ADJ_EFC_CALC_REQ Character(1) VARCHAR2(1) NOT NULL Adjusted EFC Calc Requested
1=Calc Adjustment Processed
2=Calc Adjustment Failed

#	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	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
7	DEPNDNCY_OVERRIDE	Character(1)	VARCHAR2(1) NOT NULL	Dependency Override Indicator 1=Dependent To Independent 2=Cancel Dep Override-Corr Only 3=Override Failed 4=Homeless Youth Determination
8	DT_APP_RECEIVED	Date(10)	DATE	Date Application Received
9	DT_APP_COMPLETED	Date(10)	DATE	Date Application Completed
10	APP_SIGNED_BY	Character(1)	VARCHAR2(1) NOT NULL	Application Signed By A=Applicant B=Applicant and Parent
11	FATHER_SSN_MATCH	Character(1)	VARCHAR2(1) NOT NULL	Father's SSN Match 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 I=Institutional Override
12	MOTHER_SSN_MATCH	Character(1)	VARCHAR2(1) NOT NULL	Mother's SSN Match 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 I=Institutional Override
13	MOTHER_DOB	Date(10)	DATE	Mother's Date of Birth
14	FATHER_DOB	Date(10)	DATE	Father's Date of Birth
15	ADJ_EFC_CALC_REQ	Character(1)	VARCHAR2(1) NOT NULL	Adjusted EFC Calc Requested 1=Calc Adjustment Processed 2=Calc Adjustment Failed