SELECT DISTINCT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, B.PHONE, B.EMAIL_ADDR, A.ACCOUNT_BALANCE, 0, A.ACCOUNT_BALANCE FROM PS_ACCOUNT_SF A, PS_CU_BIO_VW B WHERE A.BUSINESS_UNIT = :1 AND A.ACCOUNT_TERM = :2 AND A.EMPLID = B.EMPLID AND A.ACCOUNT_BALANCE > 0 AND NOT EXISTS (SELECT C.EMPLID FROM PS_ANTICIPATED_AID C WHERE C.EMPLID = A.EMPLID AND C.INSTITUTION = A.BUSINESS_UNIT AND C.STRM = A.ACCOUNT_TERM AND C.NET_AWARD_AMT > 0 AND C.AS_OF_DTTM = (SELECT max ( D.AS_OF_DTTM) FROM PS_ANTICIPATED_AID D WHERE D.EMPLID = C.EMPLID AND D.INSTITUTION = C.INSTITUTION AND D.AID_YEAR = C.AID_YEAR AND D.ITEM_TYPE = C.ITEM_TYPE AND D.ACAD_CAREER = C.ACAD_CAREER AND D.DISBURSEMENT_PLAN = C.DISBURSEMENT_PLAN AND D.DISBURSEMENT_ID = C.DISBURSEMENT_ID AND D.STRM = C.STRM)) GROUP BY B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, B.PHONE, B.EMAIL_ADDR, A.ACCOUNT_BALANCE, 0 HAVING A.ACCOUNT_BALANCE > 0 UNION SELECT DISTINCT F.LAST_NAME, F.FIRST_NAME, F.MIDDLE_NAME, E.EMPLID, F.PHONE, F.EMAIL_ADDR, E.ACCOUNT_BALANCE, G.NET_AWARD_AMT, E.ACCOUNT_BALANCE - G.NET_AWARD_AMT FROM PS_ACCOUNT_SF E, PS_CU_BIO_VW F, PS_ANTICIPATED_AID G WHERE E.EMPLID = F.EMPLID AND E.BUSINESS_UNIT = :1 AND E.ACCOUNT_TERM = :2 AND E.EMPLID = G.EMPLID AND E.BUSINESS_UNIT = G.INSTITUTION AND E.ACCOUNT_TERM = G.STRM AND G.AS_OF_DTTM = (SELECT DISTINCT max ( H.AS_OF_DTTM) FROM PS_ANTICIPATED_AID H WHERE H.EMPLID = G.EMPLID AND H.INSTITUTION = G.INSTITUTION AND H.AID_YEAR = G.AID_YEAR AND H.ITEM_TYPE = G.ITEM_TYPE AND H.ACAD_CAREER = G.ACAD_CAREER AND H.DISBURSEMENT_PLAN = G.DISBURSEMENT_PLAN AND H.DISBURSEMENT_ID = G.DISBURSEMENT_ID AND H.STRM = G.STRM) AND G.NET_AWARD_AMT > 0 GROUP BY F.LAST_NAME, F.FIRST_NAME, F.MIDDLE_NAME, E.EMPLID, F.PHONE, F.EMAIL_ADDR, E.ACCOUNT_BALANCE, G.NET_AWARD_AMT HAVING E.ACCOUNT_BALANCE - G.NET_AWARD_AMT > 0 ORDER BY 1, 2, 3 |