SELECT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, A.STDNT_DEGR, A.DEGREE, A.ACAD_CAREER, A.COMPLETION_TERM, TO_CHAR(A.DEGR_CONFER_DT,'YYYY-MM-DD'), I.HONORS_CODE, to_char ( A.GPA_DEGREE, '9999.999'), A.ACAD_DEGR_STATUS, B.PHONE, B.EMAIL_ADDR, ' ' FROM PS_ACAD_DEGR A, PS_CU_BIO_VW B, PS_ACAD_DEGR_HONS I WHERE A.INSTITUTION = :1 AND A.COMPLETION_TERM BETWEEN :2 AND :3 AND A.EMPLID = B.EMPLID AND A.EMPLID = I.EMPLID (+) AND A.STDNT_DEGR = I.STDNT_DEGR (+) UNION SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.EMPLID, 'SG', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' Student Group - ' || E.STDNT_GROUP FROM PS_ACAD_DEGR C, PS_CU_BIO_VW D, PS_STDNT_GRPS_HIST E WHERE C.EMPLID = D.EMPLID AND C.INSTITUTION = :1 AND C.COMPLETION_TERM BETWEEN :2 AND :3 AND C.EMPLID = E.EMPLID AND E.INSTITUTION = C.INSTITUTION AND E.EFFDT = (SELECT MAX(E_ED.EFFDT) FROM PS_STDNT_GRPS_HIST E_ED WHERE E.EMPLID = E_ED.EMPLID AND E.INSTITUTION = E_ED.INSTITUTION AND E.STDNT_GROUP = E_ED.STDNT_GROUP AND E_ED.EFFDT <= SYSDATE) AND E.EFF_STATUS = 'A' AND ( E.STDNT_GROUP = :4 OR :4 IS NULL) UNION SELECT G.LAST_NAME, G.FIRST_NAME, G.MIDDLE_NAME, G.EMPLID, 'SI', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', ' ', 'Service Indicator - ' || H.SRVC_IND_CD || ' / ' || H.SRVC_IND_REASON FROM PS_ACAD_DEGR F, PS_CU_BIO_VW G, PS_SRVC_IND_DATA H WHERE F.EMPLID = G.EMPLID AND F.INSTITUTION = :1 AND F.COMPLETION_TERM BETWEEN :2 AND :3 AND F.EMPLID = H.EMPLID AND F.INSTITUTION = H.INSTITUTION AND ( H.SRVC_IND_CD = :5 OR :5 IS NULL) ORDER BY 1, 2, 3 |