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'))