DARTS : UM_DLY_RPT_VW

WITH DATA AS
 (SELECT A.EMPLID,
         A.STRM,
         A.INSTITUTION,
         A.INSTITUTION_DESCR,
         A.UM_INST_SHORT,
         A.ACAD_CAREER,
         A.ACAD_PLAN,
         A.UM_ACAD_PLAN_DESCR,
         A.ACAD_PROG_PRIMARY,
         A.UM_ACAD_PROGP_DSC,
         A.ACAD_GROUP,
         A.UM_ACAD_GRP_DESCR,
         A.UM_CURRENT_AGE,
         A.UM_CURRENT_AGE_RNG,
         A.UM_CURRNT_AGE_RSRT,
         A.CITIZENSHIP_STATUS,
         CASE
           WHEN A.INSTITUTION = B.UM_INST_HOST THEN
            A.HEADCOUNT
           ELSE
            NULL
         END HEADCOUNT,
         CASE
           WHEN A.INSTITUTION = B.UM_INST_HOST THEN
            A.FTE
           ELSE
            NULL
         END FTE,
         A.UM_DEGREE_NONDEG,
         A.UM_STUD_LEVEL_SUM,
         A.UM_STD_LVL_SUM_SRT,
         A.UM_STUDENT_LEVEL,
         A.UM_STUD_LEVEL_SORT,
         A.UM_ACADEMIC_YR,
         A.UM_ACADEMIC_YR_SRT,
         A.UM_RESIDENCY_DESCR,
         A.UM_RESIDENCY_SORT,
         A.UM_ETHNIC_GRP_DESC,
         A.UM_ETHNIC_GRP_SORT,
         A.UM_FULL_PART,
         A.UM_GENDER,
         A.UM_IN_OUT,
         A.UM_STD_LVL_SUM_UG,
         A.ROW_CHECK,
         A.ACAD_LEVEL_BOT,
         A.LEVEL_LOAD_RULE,
         A.UM_ACAD_LOAD_RC,
         A.BIRTHDATE,
         A.CUM_GPA,
         A.UM_TUITION_PAID,
         A.SEX,
         SUBSTR(A.ACAD_PROG_PRIMARY, LENGTH(A.ACAD_PROG_PRIMARY) - 1, 2) UM_ACAD_PROG_P_END,
         A.ADMIT_TYPE,
         A.PROG_REASON,
         A.EXT_ORG_ID,
         A.UM_LAST_SCH_TYPE,
         A.ADMIT_TERM,
         A.UM_UNT_TAK_PRG_RC,
         A.DEGREE,
         A.ACAD_SUB_PLAN,
         A.UM_ACAD_SUBPLN_DSC,
         A.ACAD_YEAR,
         A.CAMPUS,
         A.UM_FIRST_TIME,
         A.UM_ADMIT_TYPE_DESC,
         A.WITHDRAW_CODE,
         B.UM_INST_HOST,
         B.UNT_PRGRSS,
         H.HOST_DESCR UM_HOST_DESCR,
         H.HOST_DESCRSHORT UM_HOST_DESCRSHORT,
         D.DESCR TERM_DESCR,
         E.CIP_CODE,
         CASE
           WHEN (A.UNT_TAKEN_PRGRSS = B.UNT_PRGRSS) AND
                (A.INSTITUTION <> B.UM_INST_HOST) THEN
            'Y'
           ELSE
            'N'
         END FALSEROWGENERATOR
    FROM SYSADM.PS_UM_STUD_EXT_VW A
    JOIN SYSADM.PS_TERM_TBL D
      ON A.STRM = D.STRM
     AND A.INSTITUTION = D.INSTITUTION
     AND A.ACAD_CAREER = D.ACAD_CAREER
    LEFT OUTER JOIN((SELECT F.INSTITUTION, F.ACAD_PLAN, F.CIP_CODE
                      FROM SYSADM.PS_ACAD_PLAN_TBL F
                     WHERE F.EFF_STATUS = 'A'
                       AND F.EFFDT =
                           (SELECT MAX(F_ED.EFFDT)
                              FROM SYSADM.PS_ACAD_PLAN_TBL F_ED
                             WHERE F.INSTITUTION = F_ED.INSTITUTION
                               AND F.ACAD_PLAN = F_ED.ACAD_PLAN
                               AND F_ED.EFFDT <= SYSDATE)) E)
      ON A.INSTITUTION = E.INSTITUTION
     AND A.ACAD_PLAN = E.ACAD_PLAN
    LEFT OUTER JOIN((SELECT C.EMPLID,
                           C.STRM,
                           C.INSTITUTION,
                           C.UM_INST_HOST,
                           SUM(C.UNT_PRGRSS) UNT_PRGRSS
                      FROM SYSADM.PS_UM_D_STD_ENR_VW C
                     WHERE C.STRM >= '0910'
                       AND C.STDNT_ENRL_STATUS = 'E'
                       AND (C.EARN_CREDIT = 'Y' OR
                           (C.UNITS_ATTEMPTED <> 'N' AND
                           C.AUDIT_GRADE_BASIS = 'N'))
                       AND C.ENRL_DROP_DT IS NULL
                     GROUP BY C.EMPLID,
                              C.STRM,
                              C.INSTITUTION,
                              C.UM_INST_HOST) B)
      ON A.EMPLID = B.EMPLID
     AND A.INSTITUTION = B.INSTITUTION
     AND A.STRM = B.STRM
    LEFT OUTER JOIN((SELECT G.INSTITUTION,
                           G.DESCR       HOST_DESCR,
                           G.DESCRSHORT  HOST_DESCRSHORT
                      FROM SYSADM.PS_INSTITUTION_TBL G
                     WHERE G.EFFDT =
                           (SELECT MAX(G_ED.EFFDT)
                              FROM SYSADM.PS_INSTITUTION_TBL G_ED
                             WHERE G_ED.INSTITUTION = G.INSTITUTION)
                       AND G.EFF_STATUS = 'A') H)
      ON H.INSTITUTION = B.UM_INST_HOST
   WHERE A.STRM >= '0910'),
PICARD AS
 (SELECT D.EMPLID,
         D.STATE,
         D.COUNTY,
         D.POSTAL,
         D.COUNTRY,
         E.DESCR AS COUNTRY_DESCR,
         CASE
           WHEN D.COUNTRY IN ('USA', 'CAN') THEN
            F.DESCR
           ELSE
            ' '
         END AS STATE_DESCRIPTION,
         CASE
           WHEN D.COUNTRY = 'USA' AND D.STATE = 'ME' THEN
            DECODE(G.UM_COUNTY_DESCR,
                   'Androscoggin',
                   'Androscoggin',
                   'Aroostook',
                   'Aroostook',
                   'Cumberland',
                   'Cumberland',
                   'Franklin',
                   'Franklin',
                   'Hancock',
                   'Hancock',
                   'Kennebec',
                   'Kennebec',
                   'Knox',
                   'Knox',
                   'Lincoln',
                   'Lincoln',
                   'Oxford',
                   'Oxford',
                   'Penobscot',
                   'Penobscot',
                   'Piscataquis',
                   'Piscataquis',
                   'Sagadahoc',
                   'Sagadahoc',
                   'Somerset',
                   'Somerset',
                   'Waldo',
                   'Waldo',
                   'Washington',
                   'Washington',
                   'York',
                   'York',
                   ' ',
                   ' ',
                   'Invalid County')
           WHEN D.COUNTRY = 'USA' THEN
            G.UM_COUNTY_DESCR
           ELSE
            ' '
         END AS COUNTY_DESCRIPTION
    FROM SYSADM.PS_UM_ORIG_ADDR_VW D
    LEFT OUTER JOIN SYSADM.PS_COUNTRY_TBL E
      ON D.COUNTRY = E.COUNTRY
    LEFT OUTER JOIN SYSADM.PS_STATE_TBL F
      ON D.COUNTRY = F.COUNTRY
     AND D.STATE = F.STATE
    LEFT OUTER JOIN SYSADM.PS_UM_ZIP_COUNTYVW G
      ON SUBSTR(D.POSTAL, 1, 5) = G.ZIP)
SELECT V.EMPLID,
       V.STRM,
       V.INSTITUTION,
       V.ACAD_CAREER,
       V.ACAD_PLAN,
       V.UM_ACAD_PLAN_DESCR,
       V.ACAD_PROG_PRIMARY,
       V.UM_ACAD_PROGP_DSC,
       V.ACAD_GROUP,
       V.UM_ACAD_GRP_DESCR,
       CASE
         WHEN V.INSTITUTION <> V.UM_INST_HOST THEN
          NULL
         ELSE
          V.UM_CURRENT_AGE
       END UM_CURRENT_AGE,
       V.UM_CURRENT_AGE_RNG,
       V.UM_CURRNT_AGE_RSRT,
       V.CITIZENSHIP_STATUS,
       V.HEADCOUNT,
       V.FTE,
       V.UM_DEGREE_NONDEG,
       V.UM_STUD_LEVEL_SUM,
       V.UM_STD_LVL_SUM_SRT,
       V.UM_STUDENT_LEVEL,
       V.UM_STUD_LEVEL_SORT,
       V.UM_ACADEMIC_YR,
       V.UM_ACADEMIC_YR_SRT,
       V.UM_RESIDENCY_DESCR,
       V.UM_RESIDENCY_SORT,
       V.UM_ETHNIC_GRP_DESC,
       V.UM_ETHNIC_GRP_SORT,
       V.UM_FULL_PART,
       V.UM_GENDER,
       V.UM_IN_OUT,
       V.UM_STD_LVL_SUM_UG,
       V.ROW_CHECK,
       V.ACAD_LEVEL_BOT,
       V.LEVEL_LOAD_RULE,
       V.UM_ACAD_LOAD_RC,
       V.BIRTHDATE,
       CASE
         WHEN V.INSTITUTION <> V.UM_INST_HOST THEN
          NULL
         ELSE
          V.CUM_GPA
       END CUM_GPA,
       CASE
         WHEN V.INSTITUTION <> V.UM_INST_HOST THEN
          NULL
         ELSE
          V.UM_TUITION_PAID
       END UM_TUITION_PAID,
       V.SEX,
       V.UM_ACAD_PROG_P_END,
       V.ADMIT_TYPE,
       V.PROG_REASON,
       V.EXT_ORG_ID,
       V.UM_LAST_SCH_TYPE,
       V.ADMIT_TERM,
       V.DEGREE,
       V.ACAD_SUB_PLAN,
       V.UM_ACAD_SUBPLN_DSC,
       V.ACAD_YEAR,
       V.CAMPUS,
       V.UM_FIRST_TIME,
       V.UM_ADMIT_TYPE_DESC,
       V.WITHDRAW_CODE,
       V.UM_INST_HOST,
       V.UM_HOST_DESCR,
       V.UM_HOST_DESCRSHORT,
       V.UNT_PRGRSS,
       V.TERM_DESCR,
       V.CIP_CODE,
       K.STATE,
       K.COUNTY,
       K.POSTAL,
       K.COUNTRY,
       K.COUNTRY_DESCR,
       K.STATE_DESCRIPTION,
       K.COUNTY_DESCRIPTION
  FROM DATA V
  LEFT OUTER JOIN PICARD K
    ON V.EMPLID = K.EMPLID
UNION
SELECT V1.EMPLID,
       V1.STRM,
       V1.INSTITUTION,
       V1.ACAD_CAREER,
       V1.ACAD_PLAN,
       V1.UM_ACAD_PLAN_DESCR,
       V1.ACAD_PROG_PRIMARY,
       V1.UM_ACAD_PROGP_DSC,
       V1.ACAD_GROUP,
       V1.UM_ACAD_GRP_DESCR,
       V1.UM_CURRENT_AGE,
       V1.UM_CURRENT_AGE_RNG,
       V1.UM_CURRNT_AGE_RSRT,
       V1.CITIZENSHIP_STATUS,
       1,
       V1.UM_UNT_TAK_PRG_RC / (CASE
         WHEN V1.ACAD_LEVEL_BOT = 'GR' THEN
          9
         WHEN V1.ACAD_LEVEL_BOT = 'MAS' THEN
          9
         WHEN V1.ACAD_LEVEL_BOT = 'PHD' THEN
          9
         WHEN V1.ACAD_LEVEL_BOT = 'L1' THEN
          15
         WHEN V1.ACAD_LEVEL_BOT = 'L2' THEN
          15
         WHEN V1.ACAD_LEVEL_BOT = 'L3' THEN
          15
         WHEN V1.INSTITUTION = 'UMS02' THEN
          16
         ELSE
          15
       END),
       V1.UM_DEGREE_NONDEG,
       V1.UM_STUD_LEVEL_SUM,
       V1.UM_STD_LVL_SUM_SRT,
       V1.UM_STUDENT_LEVEL,
       V1.UM_STUD_LEVEL_SORT,
       V1.UM_ACADEMIC_YR,
       V1.UM_ACADEMIC_YR_SRT,
       V1.UM_RESIDENCY_DESCR,
       V1.UM_RESIDENCY_SORT,
       V1.UM_ETHNIC_GRP_DESC,
       V1.UM_ETHNIC_GRP_SORT,
       V1.UM_FULL_PART,
       V1.UM_GENDER,
       V1.UM_IN_OUT,
       V1.UM_STD_LVL_SUM_UG,
       V1.ROW_CHECK,
       V1.ACAD_LEVEL_BOT,
       V1.LEVEL_LOAD_RULE,
       V1.UM_ACAD_LOAD_RC,
       V1.BIRTHDATE,
       V1.CUM_GPA,
       V1.UM_TUITION_PAID,
       V1.SEX,
       V1.UM_ACAD_PROG_P_END,
       V1.ADMIT_TYPE,
       V1.PROG_REASON,
       V1.EXT_ORG_ID,
       V1.UM_LAST_SCH_TYPE,
       V1.ADMIT_TERM,
       V1.DEGREE,
       V1.ACAD_SUB_PLAN,
       V1.UM_ACAD_SUBPLN_DSC,
       V1.ACAD_YEAR,
       V1.CAMPUS,
       V1.UM_FIRST_TIME,
       V1.UM_ADMIT_TYPE_DESC,
       V1.WITHDRAW_CODE,
       V1.INSTITUTION,
       V1.INSTITUTION_DESCR,
       V1.UM_INST_SHORT,
       NULL,
       V1.TERM_DESCR,
       V1.CIP_CODE,
       K1.STATE,
       K1.COUNTY,
       K1.POSTAL,
       K1.COUNTRY,
       K1.COUNTRY_DESCR,
       K1.STATE_DESCRIPTION,
       K1.COUNTY_DESCRIPTION
  FROM DATA V1
  LEFT OUTER JOIN PICARD K1
    ON V1.EMPLID = K1.EMPLID
 WHERE V1.FALSEROWGENERATOR = 'Y';