|
SELECT A.EMPLID ,C.INSTITUTION_SET ,B.BUSINESS_UNIT ,A.STRM ,SUM(A.NET_AWARD_AMT) ,D.DESCR ,D.DESCRSHORT ,A.DISB_EXPIRE_DT FROM PS_ANTICIPATED_AID A , PS_STDNT_AWRD_DISB B , PS_ISET_BU_DTL C , PS_ITEM_TYPE_TBL D , PS_BUS_UNIT_TBL_SF E WHERE A.NET_AWARD_AMT > 0 AND (A.DISB_EXPIRE_DT > %CurrentDateIn OR A.DISB_EXPIRE_DT IS NULL) AND A.EMPLID = B.EMPLID AND A.INSTITUTION = B.INSTITUTION AND A.AID_YEAR = B.AID_YEAR AND A.ITEM_TYPE = B.ITEM_TYPE AND A.ACAD_CAREER = B.ACAD_CAREER AND A.DISBURSEMENT_ID = B.DISBURSEMENT_ID AND A.AS_OF_DTTM=( SELECT MAX(AS_OF_DTTM) FROM PS_ANTICIPATED_AID A1 WHERE A.EMPLID = A1.EMPLID AND A.INSTITUTION = A1.INSTITUTION AND A.AID_YEAR = A1.AID_YEAR AND A.ITEM_TYPE = A1.ITEM_TYPE AND A.ACAD_CAREER=A1.ACAD_CAREER AND A.DISBURSEMENT_ID=A1.DISBURSEMENT_ID) AND B.BUSINESS_UNIT = C.BUSINESS_UNIT AND B.BUSINESS_UNIT = E.BUSINESS_UNIT AND A.ITEM_TYPE = D.ITEM_TYPE AND D.SETID = ( SELECT D1.SETID FROM PS_SET_CNTRL_REC D1 WHERE D1.SETCNTRLVALUE = E.INSTITUTION AND D1.RECNAME = 'ITEM_TYPE_TBL') AND D.EFFDT = ( SELECT MAX(F.EFFDT) FROM PS_ITEM_TYPE_TBL F WHERE F.SETID = D.SETID AND F.ITEM_TYPE = D.ITEM_TYPE AND F.EFFDT <= %CurrentDateIn) GROUP BY A.EMPLID, C.INSTITUTION_SET ,B.BUSINESS_UNIT, A.STRM, D.DESCR,D.DESCRSHORT ,A.DISB_EXPIRE_DT
|