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
  • No labels