Context
Callable SQL used in the EE_PLUS run controls to update "Plus" email address for recently disbursed parent plus loan borrowers.
SQL
UPDATE PS_EMAIL_ADDRESSES EMA
SET EMA.EMAIL_ADDR = (SELECT DISTINCT W.EMAIL_ADDR
FROM PS_STDNT_AWRD_ACTV S, PS_ITEM_TYPE_FA T, PS_LOAN_ORIGNATN U, PS_LOAN_ORIG_DTL V, PS_SCC_EMAIL_QVW W
WHERE S.INSTITUTION = 'UOD01'
AND S.AID_YEAR = :1
AND S.ACTION_DTTM > TRUNC(SYSDATE-1)
AND S.ACTION_DTTM <= TRUNC(SYSDATE)
AND S.AWARD_DISB_ACTION = 'P'
AND S.DISB_AMOUNT > 0
AND S.AID_YEAR = T.AID_YEAR
AND S.ITEM_TYPE = T.ITEM_TYPE
AND T.EFFDT =
(SELECT MAX(T_ED.EFFDT) FROM PS_ITEM_TYPE_FA T_ED
WHERE T.SETID = T_ED.SETID
AND T.ITEM_TYPE = T_ED.ITEM_TYPE
AND T.AID_YEAR = T_ED.AID_YEAR
AND T_ED.EFFDT <= SYSDATE)
AND T.FEDERAL_ID = 'PLUS'
AND S.EMPLID = U.EMPLID
AND S.INSTITUTION = U.INSTITUTION
AND S.AID_YEAR = U.AID_YEAR
AND S.ACAD_CAREER = U.ACAD_CAREER
AND U.EMPLID = V.EMPLID
AND U.INSTITUTION = V.INSTITUTION
AND U.AID_YEAR = V.AID_YEAR
AND U.ACAD_CAREER = V.ACAD_CAREER
AND U.LOAN_TYPE = V.LOAN_TYPE
AND U.LN_APPL_SEQ = V.LN_APPL_SEQ
AND V.ITEM_TYPE = S.ITEM_TYPE
AND U.BORR_EMPLID = W.EMPLID
AND W.E_ADDR_TYPE = :2
AND S.EMPLID = EMA.EMPLID
)
WHERE EMA.E_ADDR_TYPE = 'PLUS'
AND EMA.EMPLID IN (SELECT DISTINCT A.EMPLID
FROM PS_STDNT_AWRD_ACTV A, PS_ITEM_TYPE_FA B, PS_LOAN_ORIGNATN C, PS_LOAN_ORIG_DTL D, PS_SCC_EMAIL_QVW I
WHERE A.INSTITUTION = 'UOD01'
AND A.AID_YEAR = :1
AND A.ACTION_DTTM > TRUNC(SYSDATE-1)
AND A.ACTION_DTTM <= TRUNC(SYSDATE)
AND A.AWARD_DISB_ACTION = 'P'
AND A.DISB_AMOUNT > 0
AND A.AID_YEAR = B.AID_YEAR
AND A.ITEM_TYPE = B.ITEM_TYPE
AND B.EFFDT =
(SELECT MAX(B_ED.EFFDT) FROM PS_ITEM_TYPE_FA B_ED
WHERE B.SETID = B_ED.SETID
AND B.ITEM_TYPE = B_ED.ITEM_TYPE
AND B.AID_YEAR = B_ED.AID_YEAR
AND B_ED.EFFDT <= SYSDATE)
AND B.FEDERAL_ID = 'PLUS'
AND A.EMPLID = C.EMPLID
AND A.INSTITUTION = C.INSTITUTION
AND A.AID_YEAR = C.AID_YEAR
AND A.ACAD_CAREER = C.ACAD_CAREER
AND C.EMPLID = D.EMPLID
AND C.INSTITUTION = D.INSTITUTION
AND C.AID_YEAR = D.AID_YEAR
AND C.ACAD_CAREER = D.ACAD_CAREER
AND C.LOAN_TYPE = D.LOAN_TYPE
AND C.LN_APPL_SEQ = D.LN_APPL_SEQ
AND D.ITEM_TYPE = A.ITEM_TYPE
AND C.BORR_EMPLID = I.EMPLID
AND I.E_ADDR_TYPE = :2
AND EXISTS (SELECT DISTINCT 'X'
FROM PS_SCC_EMAIL_QVW J
WHERE J.EMPLID = A.EMPLID
AND J.E_ADDR_TYPE = 'PLUS'
AND J.EMAIL_ADDR <> I.EMAIL_ADDR)
AND C.BORR_EMPLID = (SELECT MAX( H.BORR_EMPLID)
FROM PS_STDNT_AWRD_ACTV E, PS_ITEM_TYPE_FA F, PS_LOAN_ORIG_DTL G, PS_LOAN_ORIGNATN H
WHERE E.EMPLID = A.EMPLID
AND E.INSTITUTION = A.INSTITUTION
AND E.AID_YEAR = A.AID_YEAR
AND E.ACAD_CAREER = A.ACAD_CAREER
AND E.AWARD_DISB_ACTION = 'P'
AND E.DISB_AMOUNT > 0
AND E.AID_YEAR = F.AID_YEAR
AND E.ITEM_TYPE = F.ITEM_TYPE
AND F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_ITEM_TYPE_FA F_ED
WHERE F.SETID = F_ED.SETID
AND F.ITEM_TYPE = F_ED.ITEM_TYPE
AND F.AID_YEAR = F_ED.AID_YEAR
AND F_ED.EFFDT <= SYSDATE)
AND F.FEDERAL_ID = 'PLUS'
AND E.EMPLID = G.EMPLID
AND E.INSTITUTION = G.INSTITUTION
AND E.AID_YEAR = G.AID_YEAR
AND E.ITEM_TYPE = G.ITEM_TYPE
AND E.ACAD_CAREER = G.ACAD_CAREER
AND G.EMPLID = H.EMPLID
AND G.INSTITUTION = H.INSTITUTION
AND G.AID_YEAR = H.AID_YEAR
AND G.ACAD_CAREER = H.ACAD_CAREER
AND G.LOAN_TYPE = H.LOAN_TYPE
AND G.LN_APPL_SEQ = H.LN_APPL_SEQ
AND E.ACTION_DTTM > TRUNC(SYSDATE-1)
AND E.ACTION_DTTM <= TRUNC(SYSDATE))
AND C.BORR_EMPLID = (SELECT DISTINCT MIN( N.BORR_EMPLID)
FROM PS_STDNT_AWRD_ACTV K, PS_ITEM_TYPE_FA L, PS_LOAN_ORIG_DTL M, PS_LOAN_ORIGNATN N
WHERE K.EMPLID = A.EMPLID
AND K.INSTITUTION = A.INSTITUTION
AND K.AID_YEAR = A.AID_YEAR
AND K.ACAD_CAREER = A.ACAD_CAREER
AND K.AWARD_DISB_ACTION = 'P'
AND K.DISB_AMOUNT > 0
AND K.ACTION_DTTM > TRUNC(SYSDATE-1)
AND K.ACTION_DTTM <= TRUNC(SYSDATE)
AND K.AID_YEAR = L.AID_YEAR
AND K.ITEM_TYPE = L.ITEM_TYPE
AND L.EFFDT =
(SELECT MAX(L_ED.EFFDT) FROM PS_ITEM_TYPE_FA L_ED
WHERE L.SETID = L_ED.SETID
AND L.ITEM_TYPE = L_ED.ITEM_TYPE
AND L.AID_YEAR = L_ED.AID_YEAR
AND L_ED.EFFDT <= SYSDATE)
AND L.FEDERAL_ID = 'PLUS'
AND K.EMPLID = M.EMPLID
AND K.INSTITUTION = M.INSTITUTION
AND K.AID_YEAR = M.AID_YEAR
AND K.ITEM_TYPE = M.ITEM_TYPE
AND K.ACAD_CAREER = M.ACAD_CAREER
AND M.EMPLID = N.EMPLID
AND M.INSTITUTION = N.INSTITUTION
AND M.AID_YEAR = N.AID_YEAR
AND M.ACAD_CAREER = N.ACAD_CAREER
AND M.LOAN_TYPE = N.LOAN_TYPE
AND M.LN_APPL_SEQ = N.LN_APPL_SEQ)
)