Context
Callable SQL used in the EE_PLUS run controls to insert a new "Plus" email address for recently disbursed parent plus loan borrowers.
SQL
INSERT INTO PS_EMAIL_ADDRESSES
SELECT DISTINCT A.EMPLID, 'PLUS', I.EMAIL_ADDR,'N'
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 NOT EXISTS (SELECT DISTINCT 'X'
FROM PS_SCC_EMAIL_QVW J
WHERE J.EMPLID = A.EMPLID
AND J.E_ADDR_TYPE = 'PLUS')
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)