DARTS : UM_CNS_RPT_VW

WITH SPOCK 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_census_age,
         a.um_census_age_prec,
         a.um_census_age_rng,
         a.um_census_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.um_unt_tak_prg_rc = b.unt_prgrss) AND
                (a.institution <> b.um_inst_host) THEN
            'Y'
           WHEN (a.um_unt_tak_prg_rc = bm.unt_prgrss_bm) AND
                (a.institution <> b.um_inst_host) THEN
            'Y'
           ELSE
            'N'
         END falserowgenerator
    FROM sysadm.ps_um_stud_cens_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_std_enr_c_vw c
                     WHERE c.strm >= '0910'
                     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 cm.emplid,
                           cm.strm,
                           cm.institution,
                           SUM(cm.unt_prgrss) unt_prgrss_bm
                      FROM sysadm.ps_um_std_enr_c_vw cm
                     WHERE cm.strm >= '0910'
                       AND cm.institution <> cm.um_inst_host
                     GROUP BY cm.emplid, cm.strm, cm.institution) bm)
      ON b.emplid = bm.emplid
     AND b.institution = bm.institution
     AND b.strm = bm.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'),
KIRK 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_census_age
       END um_census_age,
       CASE
         WHEN v.institution <> v.um_inst_host THEN
          NULL
         ELSE
          v.um_census_age_prec
       END um_census_age_prec,
       v.um_census_age_rng,
       v.um_census_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 SPOCK v
  LEFT OUTER JOIN KIRK k
    ON v.emplid = k.emplid
UNION
SELECT DISTINCT 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_census_age,
                v1.um_census_age_prec,
                v1.um_census_age_rng,
                v1.um_census_age_rsrt,
                v1.citizenship_status,
                1,
                v1.Um_Unt_Tak_Prg_Rc / (CASE
                  WHEN v1.Institution = 'UMS02' AND v1.Acad_plan = 'PSY-MA' THEN
                   15
                  WHEN v1.Acad_Level_Bot IN ('GR', 'MAS', 'PHD') THEN
                   9
                  WHEN v1.Acad_Level_Bot IN ('L1', 'L2', '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,
                0,
                v1.term_descr,
                v1.cip_code,
                k1.state,
                k1.county,
                k1.postal,
                k1.country,
                k1.country_descr,
                k1.state_description,
                k1.county_description
  FROM SPOCK v1
  LEFT OUTER JOIN KIRK k1
    ON v1.emplid = k1.emplid
 WHERE v1.falserowgenerator = 'Y';