SFA_FWS_STU_INF

(SQL View)
 Index Back

FWS Student Information

Used for Federal Work Study report to get student information - ssn, name

SELECT B.EMPLID , B.INSTITUTION , B.AID_YEAR , B.SSN , B.BIRTHDATE , B.IWD_STD_LAST_NAME , B.IWD_STD_FIRST_NM02 , B.IWD_STU_MI , B.CITIZENSHIP_STATUS , A.ISIR_TXN_NBR FROM PS_ISIR_CONTROL A , PS_ISIR_STUDENT B WHERE A.EFC_STATUS = 'O' AND A.EFFDT = ( SELECT MAX(A2.EFFDT) FROM PS_ISIR_CONTROL A2 WHERE A.EMPLID = A2.EMPLID AND A.INSTITUTION = A2.INSTITUTION AND A.AID_YEAR = A2.AID_YEAR) AND A.EFFSEQ = ( SELECT MAX(A3.EFFSEQ) FROM PS_ISIR_CONTROL A3 WHERE A.EMPLID = A3.EMPLID AND A.INSTITUTION = A3.INSTITUTION AND A.AID_YEAR = A3.AID_YEAR AND A.EFFDT =A3.EFFDT ) AND A.ISIR_TXN_NBR = ( SELECT MAX(A4.ISIR_TXN_NBR) FROM PS_ISIR_CONTROL A4 WHERE A.EMPLID = A4.EMPLID AND A.INSTITUTION = A4.INSTITUTION AND A.AID_YEAR = A4.AID_YEAR AND A.EFFDT =A4.EFFDT ) AND 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

# 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 SSN Character(9) VARCHAR2(9) NOT NULL Social Security #
5 BIRTHDATE Date(10) DATE Date of Birth
6 IWD_STD_LAST_NAME Character(35) VARCHAR2(35) NOT NULL Last Name
7 IWD_STD_FIRST_NM02 Character(35) VARCHAR2(35) NOT NULL Student's First Name
8 IWD_STU_MI Character(15) VARCHAR2(15) NOT NULL Student Middle Initial
9 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status
10 ISIR_TXN_NBR Number(2,0) SMALLINT NOT NULL Transaction Num