DARTS : UM_D_APPL_PG_VW - Admission Applicant Program

SELECT X.EMPLID,
       X.ACAD_CAREER,
       X.ADM_APPL_NBR,
       X.APPL_PROG_NBR,
       X.EFFDT,
       X.EFFSEQ,
       X.INSTITUTION,
       X.ACAD_PROG,
       XPROG.DESCR,
       X.PROG_STATUS,
       X.PROG_ACTION,
       X.ACTION_DT,
       X.PROG_REASON,
       X.ADMIT_TERM,
       X.EXP_GRAD_TERM,
       X.REQ_TERM,
       X.ACAD_LOAD_APPR,
       X.CAMPUS,
       X.ACAD_PROG_DUAL,
       X.JOINT_PROG_APPR,
       X.ACAD_PLAN,
       XPLAN.ACAD_PLAN_TYPE,
       XPLAN.DESCR,
       X.DECLARE_DT,
       X.PLAN_SEQUENCE,
       X.ACAD_SUB_PLAN,
       XSUB.ACAD_SUBPLAN_TYPE,
       XSUB.DESCR,
       CASE
         WHEN (ROW_NUMBER() OVER(PARTITION BY X.EMPLID,
                                 X.ACAD_CAREER,
                                 X.ADM_APPL_NBR ORDER BY X.EFFDT DESC,
                                 X.EFFSEQ DESC NULLS LAST)) = 1 THEN
          'Y'
         ELSE
          'N'
       END,
       ROW_NUMBER() OVER(PARTITION BY X.EMPLID, X.ACAD_CAREER, X.ADM_APPL_NBR, X.APPL_PROG_NBR ORDER BY X.EFFDT DESC, X.EFFSEQ DESC NULLS LAST) FOON,
       ROW_NUMBER() OVER(PARTITION BY X.EMPLID, X.ACAD_CAREER, X.ADM_APPL_NBR, X.APPL_PROG_NBR, X.ACAD_PLAN ORDER BY X.EFFDT DESC, X.EFFSEQ DESC NULLS LAST) FOBAR,
       ROW_NUMBER() OVER(PARTITION BY X.EMPLID, X.ACAD_CAREER, X.ADM_APPL_NBR, X.APPL_PROG_NBR, X.ACAD_PLAN, X.ACAD_SUB_PLAN ORDER BY X.EFFDT DESC, X.EFFSEQ DESC NULLS LAST) FUBAR,
       X.DATASOURCE,
       X.SSR_SHIFT
  FROM (SELECT A.EMPLID,
               A.ACAD_CAREER,
               A.STDNT_CAR_NBR,
               A.ADM_APPL_NBR,
               A.APPL_PROG_NBR,
               A.EFFDT,
               A.EFFSEQ,
               A.INSTITUTION,
               A.ACAD_PROG,
               A.PROG_STATUS,
               A.PROG_ACTION,
               A.ACTION_DT,
               A.PROG_REASON,
               A.ADMIT_TERM,
               A.EXP_GRAD_TERM,
               A.REQ_TERM,
               A.ACAD_LOAD_APPR,
               A.CAMPUS,
               A.ACAD_PROG_DUAL,
               A.JOINT_PROG_APPR,
               B.ACAD_PLAN,
               B.DECLARE_DT,
               B.PLAN_SEQUENCE,
               C.ACAD_SUB_PLAN,
               'AD' AS DATASOURCE,
               A.SSR_SHIFT
          FROM PS_ADM_APPL_PROG A, PS_ADM_APPL_PLAN B, PS_ADM_APPL_SBPLAN C
         WHERE B.EMPLID(+) = A.EMPLID
           AND B.ACAD_CAREER(+) = A.ACAD_CAREER
           AND B.ADM_APPL_NBR(+) = A.ADM_APPL_NBR
           AND B.APPL_PROG_NBR(+) = A.APPL_PROG_NBR
           AND B.EFFDT(+) = A.EFFDT
           AND B.EFFSEQ(+) = A.EFFSEQ
           AND C.EMPLID(+) = B.EMPLID
           AND C.ACAD_CAREER(+) = B.ACAD_CAREER
           AND C.ADM_APPL_NBR(+) = B.ADM_APPL_NBR
           AND C.APPL_PROG_NBR(+) = B.APPL_PROG_NBR
           AND C.EFFDT(+) = B.EFFDT
           AND C.EFFSEQ(+) = B.EFFSEQ
           AND C.ACAD_PLAN(+) = B.ACAD_PLAN
        UNION
        SELECT A2.EMPLID,
               A2.ACAD_CAREER,
               A2.STDNT_CAR_NBR,
               A2.ADM_APPL_NBR,
               A2.APPL_PROG_NBR,
               A2.EFFDT,
               A2.EFFSEQ,
               A2.INSTITUTION,
               A2.ACAD_PROG,
               A2.PROG_STATUS,
               A2.PROG_ACTION,
               A2.ACTION_DT,
               A2.PROG_REASON,
               A2.ADMIT_TERM,
               A2.EXP_GRAD_TERM,
               A2.REQ_TERM,
               A2.ACAD_LOAD_APPR,
               A2.CAMPUS,
               A2.ACAD_PROG_DUAL,
               A2.JOINT_PROG_APPR,
               B2.ACAD_PLAN,
               B2.DECLARE_DT,
               B2.PLAN_SEQUENCE,
               C2.ACAD_SUB_PLAN,
               'ST',
               A2.SSR_SHIFT
          FROM PS_ACAD_PROG A2, PS_ACAD_PLAN B2, PS_ACAD_SUBPLAN C2
         WHERE B2.EMPLID(+) = A2.EMPLID
           AND B2.ACAD_CAREER(+) = A2.ACAD_CAREER
           AND B2.STDNT_CAR_NBR(+) = A2.STDNT_CAR_NBR
           AND B2.EFFDT(+) = A2.EFFDT
           AND B2.EFFSEQ(+) = A2.EFFSEQ
           AND C2.EMPLID(+) = B2.EMPLID
           AND C2.ACAD_CAREER(+) = B2.ACAD_CAREER
           AND C2.STDNT_CAR_NBR(+) = B2.STDNT_CAR_NBR
           AND C2.ACAD_PLAN(+) = B2.ACAD_PLAN
           AND C2.EFFDT(+) = B2.EFFDT
           AND C2.EFFSEQ(+) = B2.EFFSEQ
           AND C2.ACAD_PLAN(+) = B2.ACAD_PLAN
           AND NOT EXISTS
         (SELECT 'Q'
                  FROM PS_ADM_APPL_PROG QTC
                 WHERE QTC.EMPLID = A2.EMPLID
                   AND QTC.ACAD_CAREER = A2.ACAD_CAREER
                   AND QTC.EFFDT = A2.EFFDT
                   AND QTC.EFFSEQ = A2.EFFSEQ
                   AND QTC.ADM_APPL_NBR = A2.ADM_APPL_NBR
                   AND QTC.APPL_PROG_NBR = A2.APPL_PROG_NBR
                   AND QTC.PROG_STATUS = A2.PROG_STATUS
                   AND QTC.PROG_ACTION = A2.PROG_ACTION)) X,
       PS_ACAD_PLAN_TBL XPLAN,
       PS_ACAD_PROG_TBL XPROG,
       PS_ACAD_SUBPLN_TBL XSUB
 WHERE X.INSTITUTION = XPLAN.INSTITUTION(+)
   AND X.ACAD_PLAN = XPLAN.ACAD_PLAN(+)
   AND (XPLAN.EFFDT = (SELECT MAX(XPLAN_ED.EFFDT)
                         FROM PS_ACAD_PLAN_TBL XPLAN_ED
                        WHERE XPLAN_ED.INSTITUTION = XPLAN.INSTITUTION
                          AND XPLAN_ED.ACAD_PLAN = XPLAN.ACAD_PLAN
                          AND XPLAN_ED.EFF_STATUS = 'A') OR
       XPLAN.EFFDT IS NULL)
   AND X.INSTITUTION = XPROG.INSTITUTION(+)
   AND X.ACAD_PROG = XPROG.ACAD_PROG(+)
   AND (XPROG.EFFDT = (SELECT MAX(XPROG_ED.EFFDT)
                         FROM PS_ACAD_PROG_TBL XPROG_ED
                        WHERE XPROG_ED.INSTITUTION = XPROG.INSTITUTION
                          AND XPROG_ED.ACAD_PROG = XPROG.ACAD_PROG
                          AND XPROG_ED.EFF_STATUS = 'A') OR
       XPROG.EFFDT IS NULL)
   AND X.INSTITUTION = XSUB.INSTITUTION(+)
   AND X.ACAD_PLAN = XSUB.ACAD_PLAN(+)
   AND X.ACAD_SUB_PLAN = XSUB.ACAD_SUB_PLAN(+)
   AND (XSUB.EFFDT = (SELECT MAX(XSUB_ED.EFFDT)
                        FROM PS_ACAD_SUBPLN_TBL XSUB_ED
                       WHERE XSUB_ED.INSTITUTION = XSUB.INSTITUTION
                         AND XSUB_ED.ACAD_PLAN = XSUB.ACAD_PLAN
                         AND XSUB_ED.ACAD_SUB_PLAN = XSUB.ACAD_SUB_PLAN
                         AND XSUB_ED.EFF_STATUS = 'A') OR
       XSUB.EFFDT IS NULL);