|
SELECT DISTINCT A.INSTITUTION , A.AID_YEAR , D.OWNING_SCHOOL_CD , A.EMPLID , H.NAME FROM PS_HCR_PERSON_NM_I H , PS_STDNT_AWARDS A , PS_ISIR_CONTROL D , PS_ITEM_TYPE_FA I WHERE H.EMPLID = A.EMPLID AND A.EMPLID = D.EMPLID AND D.EMPLID = A.EMPLID AND D.INSTITUTION = A.INSTITUTION AND D.AID_YEAR = A.AID_YEAR AND D.EFFDT = ( SELECT MAX(D1.EFFDT) FROM PS_ISIR_CONTROL D1 WHERE D1.EMPLID = D.EMPLID AND D1.INSTITUTION = D.INSTITUTION AND D1.AID_YEAR = D.AID_YEAR AND D1.EFFDT <= %CurrentDateIn) AND D.EFFSEQ = ( SELECT MAX(D2.EFFSEQ) FROM PS_ISIR_CONTROL D2 WHERE D2.EMPLID = D.EMPLID AND D2.INSTITUTION = D.INSTITUTION AND D2.AID_YEAR = D.AID_YEAR AND D2.EFFDT = D.EFFDT) AND A.SETID = I.SETID AND A.ITEM_TYPE = I.ITEM_TYPE AND A.AID_YEAR = I.AID_YEAR AND ( ( (A.AWARD_STATUS = 'O' AND A.OFFER_ACTIVITY_IND IN ('A','U')) OR (A.AWARD_STATUS = 'A' AND A.OFFER_ACTIVITY_IND IN ('A','U')) OR (A.AWARD_STATUS IN ('C','D') AND A.OFFER_ACTIVITY_IND IN ('A','O')) ) OR EXISTS ( SELECT 'X' FROM PS_SFA_ASG_ORG G WHERE G.EMPLID = A.EMPLID AND G.INSTITUTION = A.INSTITUTION AND G.AID_YEAR = A.AID_YEAR AND G.ACAD_CAREER = A.ACAD_CAREER AND G.ITEM_TYPE = A.ITEM_TYPE AND ( G.SFA_ASG_UPDATE_ORG = 'Y' OR G.SFA_ASG_UPDATE_DSB = 'Y' ) ) ) AND I.FEDERAL_ID IN ('ACG', 'SMRT') AND I.EFF_STATUS = 'A' AND I.EFFDT = ( SELECT MAX(I2.EFFDT) FROM PS_ITEM_TYPE_FA I2 WHERE I2.SETID = I.SETID AND I2.ITEM_TYPE = I.ITEM_TYPE AND I2.AID_YEAR = I.AID_YEAR AND I2.EFF_STATUS = I.EFF_STATUS AND I2.EFFDT <= %CurrentDateIn)
|
INSTITUTION