SELECT                                                   /*+ NO_CPU_COSTING */
  s.emplid "EMPLID",
   S.STRM "STRM",
   SYSDATE "DTTM_CREATED",
   -- REPLACED 2/14/2012 NG
   --       CASE WHEN substr(t.acad_group, 1, LENGTH(t.acad_group) - 1) = 'AC' THEN 'COAS'
   --            ELSE substr(t.acad_group, 1, LENGTH(t.acad_group) - 1) END "SMO_COLLEGE",
   -- END REPLACE 2/14/2012 NG
   t.SMO_COLLEGE "SMO_COLLEGE",
   AP.acad_career "ACAD_CAREER",
   AP.ACAD_PROG "ACAD_PROG",
   APL.ACAD_PLAN "ACAD_PLAN",
   c.acad_level_bot "ACADEMIC_LVL",
   COALESCE (
      (SELECT 'RES'
         FROM ps_residency_off R
        WHERE     emplid = AP.emplid
              AND acad_career = AP.acad_career
              AND institution = AP.institution
              AND effective_term =
                     (SELECT MAX (effective_term)
                        FROM ps_residency_off
                       WHERE     emplid = r.emplid
                             AND acad_career = r.acad_career
                             AND institution = t.institution
                             AND effective_term <= t.strm)
              AND (residency = 'R'
                   OR (residency IN ('F', 'N')
                       AND admission_excpt IN ('A', 'M')))),
      'NON')
      "RESIDENCY",
   COALESCE (
      (SELECT residency || ':' || admission_excpt
         FROM ps_residency_off R
        WHERE     emplid = AP.emplid
              AND acad_career = AP.acad_career
              AND institution = AP.institution
              AND effective_term =
                     (SELECT MAX (effective_term)
                        FROM ps_residency_off
                       WHERE     emplid = r.emplid
                             AND acad_career = r.acad_career
                             AND institution = t.institution
                             AND effective_term <= t.strm)
              AND (residency = 'R'
                   OR (residency IN ('F', 'N')
                       AND admission_excpt IN ('A', 'M')))),
      'NON')
      "SMO_RES_ADM_EXCPT",
   CASE WHEN AP.ACAD_PROG = 'PMSTR' THEN AP.ACAD_PROG ELSE 'OTHER' END
      "SMO_PROGRAM",
   CASE WHEN AP.ACAD_PROG = 'PMSTR' THEN 12 ELSE 15 END "SMO_FTE_DIV",
   s.class_nbr "CLASS_NBR",
   T.SUBJECT "SUBJECT",
   -- NG 10/27/09
   --  CASE WHEN s.units_attempted = 'N' THEN 0
   --      ELSE s.unt_taken END "UNT_TAKEN",
   s.unt_taken "UNT_TAKEN",
   -- NG END
   PDE.sex "SEX",
   NVL (
      (SELECT DE.ETHNIC_GRP_CD
         FROM PS_DIVERS_ETHNIC DE
        WHERE     DE.EMPLID = C.EMPLID
              AND DE.REG_REGION = 'USA'
              AND DE.PRIMARY_INDICATOR = 'Y'),
      'DECLINE')
      "ETHNIC_GRP_CD",
   P.BIRTHDATE "BIRTHDATE",
   COALESCE (
      (SELECT CITY
         FROM PS_ADDRESSES A
        WHERE EMPLID = C.EMPLID AND ADDRESS_TYPE = 'HOME'
              AND EFFDT =
                     (SELECT MAX (EFFDT)
                        FROM PS_ADDRESSES
                       WHERE EMPLID = A.EMPLID
                             AND ADDRESS_TYPE = A.ADDRESS_TYPE)),
      (SELECT CITY
         FROM PS_ADDRESSES A
        WHERE EMPLID = C.EMPLID AND ADDRESS_TYPE = 'PERM'
              AND EFFDT =
                     (SELECT MAX (EFFDT)
                        FROM PS_ADDRESSES
                       WHERE EMPLID = A.EMPLID
                             AND ADDRESS_TYPE = A.ADDRESS_TYPE)),
      ' ')
      "CITY",
   CASE
      WHEN AP.admit_term < S.strm
      THEN
         'C'
      ELSE
         NVL (
            (SELECT ADMIT_TYPE
               FROM PS_ADM_APPL_DATA
              WHERE EMPLID = AP.emplid AND ADM_APPL_NBR = AP.ADM_APPL_NBR),
            ' ')
   END
      "ADMITTYPE",
   COALESCE (
      CASE WHEN PS.CAMPUS_ID <> ' ' THEN 'C' ELSE NULL END,
      (SELECT 'S'
         FROM PS_PERS_NID
        WHERE     EMPLID = PS.EMPLID
              AND NATIONAL_ID <> '999999999'
              AND ROWNUM = 1
              AND COUNTRY = 'USA'),
      'D')
      "ALT_TYPE",
   COALESCE (
      CASE WHEN PS.CAMPUS_ID <> ' ' THEN PS.CAMPUS_ID ELSE NULL END,
      (SELECT NATIONAL_ID
         FROM PS_PERS_NID
        WHERE     EMPLID = PS.EMPLID
              AND NATIONAL_ID <> '999999999'
              AND ROWNUM = 1
              AND COUNTRY = 'USA'),
      '999999999')
      "ALT_ID"

FROM ps_stdnt_enrl S, -- REPLACED 2/14/2012 NG ps_class_tbl T, HRSA_EXTR.PS_SMO_CLS_COL_VW t, -- END REPLACE 2/14/2012 NG ps_acad_prog AP, ps_stdnt_car_term C, ps_pers_data_effdt PDE, ps_person P, ps_person_sa PS, ps_acad_plan APL WHERE t.institution = s.institution AND t.strm = s.strm AND t.class_nbr = s.class_nbr AND s.stdnt_enrl_status = 'E' -- NG 10/26/2009 -- AND s.units_attempted <> 'N' AND s.GRADING_BASIS_ENRL NOT IN ('AUD', 'ADM', 'NON') -- NG END AND AP.emplid = c.emplid AND AP.acad_career = c.acad_career AND AP.stdnt_car_nbr = c.stdnt_car_nbr AND AP.effdt = (SELECT MAX (effdt) FROM ps_acad_prog WHERE emplid = AP.emplid AND acad_career = AP.acad_career AND stdnt_car_nbr = ap.stdnt_car_nbr AND effdt <= SYSDATE) AND AP.effseq = (SELECT MAX (effseq) FROM ps_acad_prog WHERE emplid = AP.emplid AND acad_career = ap.acad_career AND stdnt_car_nbr = ap.stdnt_car_nbr AND effdt = AP.effdt) AND AP.admit_term = (SELECT MAX (admit_term) FROM ps_acad_prog WHERE emplid = AP.emplid AND stdnt_car_nbr = ap.stdnt_car_nbr AND effdt = AP.effdt AND admit_term <= s.strm) AND AP.acad_prog NOT IN ('PEXTN', 'PEXDG', 'UEXTN', 'UEXDG') AND NOT SUBSTR (t.acad_group, 1, LENGTH (t.acad_group) - 1) IN ('EXED') AND s.session_code IN ('1', '5W1', '5W2', '8W1', '8W2') AND s.emplid = c.emplid AND s.acad_career = c.acad_career AND s.strm = c.strm AND PDE.emplid = c.emplid AND PDE.effdt = (SELECT MAX (effdt) FROM ps_pers_data_effdt WHERE emplid = PDE.emplid) AND P.emplid = c.emplid AND PS.emplid = P.emplid AND APL.emplid = AP.emplid AND APL.acad_career = AP.acad_career AND APL.stdnt_car_nbr = AP.stdnt_car_nbr AND APL.effdt = AP.effdt AND APL.effseq = AP.effseq AND APL.plan_sequence = (SELECT MIN (plan_sequence) FROM ps_acad_plan WHERE emplid = APL.emplid AND acad_career = APL.acad_career AND stdnt_car_nbr = APL.stdnt_car_nbr AND effdt = APL.effdt AND effseq = APL.effseq) AND APL.DECLARE_DT = (SELECT MAX (DECLARE_DT) FROM ps_acad_plan WHERE emplid = APL.emplid AND acad_career = APL.acad_career AND stdnt_car_nbr = APL.stdnt_car_nbr AND effdt = APL.effdt AND effseq = APL.effseq AND plan_sequence = APL.plan_sequence) AND APL.ACAD_PLAN = (SELECT MAX (ACAD_PLAN) FROM ps_acad_plan WHERE emplid = APL.emplid AND acad_career = APL.acad_career AND stdnt_car_nbr = APL.stdnt_car_nbr AND effdt = APL.effdt AND effseq = APL.effseq AND plan_sequence = APL.plan_sequence AND declare_dt = APL.declare_dt) AND EXISTS (SELECT 1 FROM PS_CLASS_ATTRIBUTE WHERE CRSE_ID = T.CRSE_ID AND CRSE_OFFER_NBR = T.CRSE_OFFER_NBR AND STRM = T.STRM AND SESSION_CODE = T.SESSION_CODE AND CLASS_SECTION = T.CLASS_SECTION AND CRSE_ATTR = 'CLEV' AND CRSE_ATTR_VALUE IN ('1', '2', '3'))