SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[PS_CER_ROS_STD_VW] (STRM, CLASS_NBR, EMPLID, ACAD_CAREER,
 STDNT_ENRL_STATUS, ENRL_STATUS_REASON, GRADING_BASIS_ENRL,
 CRSE_GRADE_OFF, LAPSE_TO_GRADE, LAPSE_DEADLINE, ENRL_DROP_DT,
 ENRL_ADD_DT, STATUS_DT, UNT_TAKEN, STDNT_POSITIN, ENRL_ACTN_RSN_LAST,
 CONTACT_MINUTES, LAST_NAME, FIRST_NAME, MIDDLE_NAME, CER_ROS_MINOR,
 PHONE, EMAIL_ADDR) AS  SELECT S.STRM , S.CLASS_NBR , S.EMPLID ,
 S.ACAD_CAREER , S.STDNT_ENRL_STATUS , S.ENRL_STATUS_REASON ,
 S.GRADING_BASIS_ENRL , S.CRSE_GRADE_OFF , I.LAPSE_TO_GRADE ,
 I.LAPSE_DEADLINE , S.ENRL_DROP_DT , S.ENRL_ADD_DT , S.STATUS_DT ,
 S.UNT_TAKEN , S.STDNT_POSITIN , S.ENRL_ACTN_RSN_LAST , ( SELECT
 SUM(CA.CONTACT_MINUTES) FROM PS_CLASS_ATTENDNCE CA WHERE CA.EMPLID =
 S.EMPLID AND CA.INSTITUTION = S.INSTITUTION AND CA.STRM = S.STRM AND
 CA.CLASS_NBR = S.CLASS_NBR) , N.LAST_NAME , N.FIRST_NAME ,
 N.MIDDLE_NAME , CASE WHEN (DATEADD(YEAR, -18, GETDATE()) <
 P.BIRTHDATE) THEN 'Y' ELSE 'N' END , PH.PHONE , ( SELECT TOP 1
 E.EMAIL_ADDR FROM PS_EMAIL_ADDRESSES AS E WHERE (E.EMPLID = S.EMPLID)
 ORDER BY E.PREF_EMAIL_FLAG DESC) AS EXPR1 FROM PS_STDNT_ENRL AS S
 INNER JOIN PS_PERSON AS P ON P.EMPLID = S.EMPLID LEFT JOIN
 PS_PERSON_NAME AS N ON N.EMPLID = S.EMPLID LEFT JOIN PS_PERSON_PHONE
 PH ON PH.EMPLID = S.EMPLID LEFT JOIN PS_STDNT_INCOMPLTE I ON I.EMPLID
 = S.EMPLID AND I.ACAD_CAREER = S.ACAD_CAREER AND I.INSTITUTION =
 S.INSTITUTION AND I.STRM = S.STRM AND I.CLASS_NBR = S.CLASS_NBR AND
 I.LAPSE_TO_GRADE <> ' ' AND I.LAPSE_GRADE_STATUS = 'I'