SFA_ASG_ISIR_VW

(SQL View)
 Index Back

ACG/SMART Student ISIR view

ACG/SMART Student ISIR view for Equation Engine.

SELECT A.EMPLID ,A.INSTITUTION ,A.AID_YEAR ,A.ISIR_TXN_NBR ,A.PELL_ELIGIBILITY ,A.SSA_CITIZENSHP_IND ,B.CITIZENSHIP_STATUS 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.EFC_STATUS = A2.EFC_STATUS ) 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.EFC_STATUS = A3.EFC_STATUS 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.EFC_STATUS = A4.EFC_STATUS 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

  • Parent record: STUDENT_AID

    • # PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
    1 EMPLID Character(11) VARCHAR2(11) NOT NULL Employee ID

    Prompt Table: PERSONAL_DATA

    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: STUDENT_AID

    4 ISIR_TXN_NBR Number(2,0) SMALLINT NOT NULL Transaction Num
    5 PELL_ELIGIBILITY Character(1) VARCHAR2(1) NOT NULL Federal PELL Eligibility
    Y=Yes
    6 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
    7 CITIZENSHIP_STATUS Character(1) VARCHAR2(1) NOT NULL Citizenship Status