SELECT DISTINCT B.ACAD_PLAN,
LISTAGG(D.CAMPUS, ', ') within group (order by b.acad_plan) AS VALID_CAMPUSES,
LISTAGG(E.DESCR, ', ') within group (order by b.acad_plan) AS VALID_TERMS
FROM PS_ACAD_PROG_TBL A
LEFT OUTER JOIN PS_ACAD_PLAN_TBL B
    ON  B.EFF_STATUS = 'A'
    AND A.INSTITUTION = B.INSTITUTION
    AND A.ACAD_PROG = B.ACAD_PROG
    AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ACAD_PLAN_TBL B_ED
        WHERE B.INSTITUTION = B_ED.INSTITUTION
        AND B.ACAD_PLAN = B_ED.ACAD_PLAN
        AND B_ED.EFFDT <= SYSDATE)
LEFT OUTER JOIN  PS_ASU_ACADP_EXTNS C
    ON  B.INSTITUTION = C.INSTITUTION
    AND B.ACAD_PLAN = C.ACAD_PLAN
    AND C.EFFDT = (SELECT MAX(C_ED.EFFDT) FROM PS_ASU_ACADP_EXTNS C_ED
        WHERE C.INSTITUTION = C_ED.INSTITUTION
        AND C.ACAD_PLAN = C_ED.ACAD_PLAN
        AND C_ED.EFFDT <= SYSDATE)
    AND C.EFFSEQ = (SELECT MAX(C_ES.EFFSEQ) FROM PS_ASU_ACADP_EXTNS C_ES
        WHERE C.INSTITUTION = C_ES.INSTITUTION
        AND C.ACAD_PLAN = C_ES.ACAD_PLAN
        AND C.EFFDT = C_ES.EFFDT)
LEFT OUTER JOIN  PS_ASU_ACADP_EXTN5 I
    ON C.INSTITUTION = I.INSTITUTION
    AND C.ACAD_PLAN = I.ACAD_PLAN
    AND C.EFFDT = I.EFFDT
    AND C.EFFSEQ = I.EFFSEQ
LEFT OUTER JOIN PS_ASU_ACADP_TERM D
    ON  B.INSTITUTION = D.INSTITUTION
    AND B.ACAD_PLAN = D.ACAD_PLAN
JOIN PS_TERM_TBL E
    ON D.INSTITUTION = E.INSTITUTION
    AND E.ACAD_CAREER = 'UGRD'
    AND D.STRM = E.STRM
LEFT OUTER JOIN  PS_ASU_ACADP_EXTND F
    ON  D.INSTITUTION = F.INSTITUTION
    AND D.ACAD_PLAN = F.ACAD_PLAN
    AND F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_ASU_ACADP_EXTND F_ED
        WHERE F.INSTITUTION = F_ED.INSTITUTION
        AND F.ACAD_PLAN = F_ED.ACAD_PLAN
        AND F_ED.EFFDT <= SYSDATE)
    AND F.EFFSEQ = (SELECT MAX(F_ES.EFFSEQ) FROM PS_ASU_ACADP_EXTND F_ES
        WHERE F.INSTITUTION = F_ES.INSTITUTION
        AND F.ACAD_PLAN = F_ES.ACAD_PLAN
        AND F.EFFDT = F_ES.EFFDT)
        AND (( UPPER(SUBSTR( E.DESCR,6,1)) = F.ASU_PLNXTDL_TRM)
        OR ( UPPER(SUBSTR( E.DESCR,7,1)) = F.ASU_PLNXTDL_TRM))
/*LEFT OUTER JOIN  PS_ASU_ACADP_S_TRM G
    ON  D.INSTITUTION = G.INSTITUTION
    AND D.ACAD_PLAN = G.ACAD_PLAN
    AND D.STRM = G.STRM
    AND D.CAMPUS = G.CAMPUS
    AND D.NOTIFICATION_PLAN = G.NOTIFICATION_PLAN
LEFT OUTER JOIN  PS_ACAD_SUBPLN_TBL H
    ON  G.ACAD_PLAN = H.ACAD_PLAN
    AND G.ACAD_SUB_PLAN = H.ACAD_SUB_PLAN, */
JOIN PS_ASU_ACADP_DDLN J ON
     D.INSTITUTION = J.INSTITUTION
     AND D.STRM = J.STRM
     AND D.CAMPUS = J.CAMPUS
     AND J.ACAD_CAREER = 'UGRD'
WHERE (A.ACAD_CAREER = 'UGRD'
    AND A.EFF_STATUS = 'A'
    AND A.EFFDT = (SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG_TBL A_ED
        WHERE A.INSTITUTION = A_ED.INSTITUTION
        AND A.ACAD_PROG = A_ED.ACAD_PROG
        AND A_ED.EFFDT <= SYSDATE)
    AND A.ACAD_PROG <> 'UCNV'
    AND D.ASU_ACTIVE_IN_APP = 'Y'
/*    AND ( H.EFFDT = (SELECT MAX(H_ED.EFFDT) FROM PS_ACAD_SUBPLN_TBL H_ED
        WHERE H.INSTITUTION = H_ED.INSTITUTION
        AND H.ACAD_PLAN = H_ED.ACAD_PLAN
        AND H.ACAD_SUB_PLAN = H_ED.ACAD_SUB_PLAN
        AND H_ED.EFFDT <= SYSDATE)
    OR H.EFFDT IS NULL)
    AND ( H.TRNSCR_PRINT_FL > '0'
    OR H.TRNSCR_PRINT_FL IS NULL) */
    AND ( I.EFFDT = (SELECT MAX(I_ED.EFFDT) FROM PS_ASU_ACADP_EXTN5 I_ED
        WHERE I.INSTITUTION = I_ED.INSTITUTION
        AND I.ACAD_PLAN = I_ED.ACAD_PLAN
        AND I_ED.EFFDT <= C.EFFDT)
     OR I.EFFDT IS NULL)
     AND ( J.ASU_START_DATE < sysdate
     OR J.ASU_START_DATE = sysdate)
     AND ( J.ASU_SESSN_END_DT > sysdate
     OR J.ASU_SESSN_A_END_DT > sysdate
     OR J.ASU_SESSN_B_END_DT = sysdate)
/*     AND ( G.ASU_SPLAN_STATUS IN ('A','N')
     OR G.ASU_SPLAN_STATUS IS NULL) */
     AND A.ACAD_PROG <> 'UGNFA' and b.acad_plan = 'ARPUPBSP')
GROUP BY B.ACAD_PLAN
ORDER BY B.ACAD_PLAN