Results
SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, A.ADMIT_TYPE, B.ADMIT_TERM, B.ACAD_PROG, C.ACAD_PLAN, B.PROG_ACTION, D.PHONE, D.EMAIL_ADDR FROM PS_ADM_APPL_DATA A, PS_ADM_APPL_SCTY A1, PS_ADM_APPL_PROG B, PS_ADM_MAINT_SCTY B1, PS_ADM_APPL_PLAN C, PS_CU_BIO_VW D WHERE A.EMPLID = A1.EMPLID AND A.ACAD_CAREER = A1.ACAD_CAREER AND A.ADM_APPL_NBR = A1.ADM_APPL_NBR AND A1.OPRCLASS = 'CUHCPPBAR01' AND A1.OPRID = '11002650' AND B.EMPLID = B1.EMPLID AND B.ACAD_CAREER = B1.ACAD_CAREER AND B.STDNT_CAR_NBR = B1.STDNT_CAR_NBR AND B.ADM_APPL_NBR = B1.ADM_APPL_NBR AND B.APPL_PROG_NBR = B1.APPL_PROG_NBR AND B1.OPRCLASS = 'CUHCPPBAR01' AND B1.OPRID = '11002650' AND A.EMPLID = B.EMPLID AND A.ACAD_CAREER = B.ACAD_CAREER AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR AND A.ADM_APPL_NBR = B.ADM_APPL_NBR AND B.EFFDT = (SELECT MAX(B_ED.EFFDT) FROM PS_ADM_APPL_PROG B_ED WHERE B.EMPLID = B_ED.EMPLID AND B.ACAD_CAREER = B_ED.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ED.STDNT_CAR_NBR AND B.ADM_APPL_NBR = B_ED.ADM_APPL_NBR AND B.APPL_PROG_NBR = B_ED.APPL_PROG_NBR AND B_ED.EFFDT <= SYSDATE) AND B.EFFSEQ = (SELECT MAX(B_ES.EFFSEQ) FROM PS_ADM_APPL_PROG B_ES WHERE B.EMPLID = B_ES.EMPLID AND B.ACAD_CAREER = B_ES.ACAD_CAREER AND B.STDNT_CAR_NBR = B_ES.STDNT_CAR_NBR AND B.ADM_APPL_NBR = B_ES.ADM_APPL_NBR AND B.APPL_PROG_NBR = B_ES.APPL_PROG_NBR AND B.EFFDT = B_ES.EFFDT) AND B.EMPLID = C.EMPLID AND B.ACAD_CAREER = C.ACAD_CAREER AND B.STDNT_CAR_NBR = C.STDNT_CAR_NBR AND B.ADM_APPL_NBR = C.ADM_APPL_NBR AND B.APPL_PROG_NBR = C.APPL_PROG_NBR AND B.EFFSEQ = C.EFFSEQ AND B.EFFDT = C.EFFDT AND A.INSTITUTION = :1 AND A.ACAD_CAREER = :2 AND B.ADMIT_TERM BETWEEN :3 AND :4 AND B.PROG_ACTION NOT IN ('WADM','WAPP') AND A.EMPLID = D.EMPLID AND ( A.ADMIT_TYPE = :5 OR :5 IS NULL) AND NOT EXISTS (SELECT E.EMPLID FROM PS_STDNT_GRPS_HIST E WHERE 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.EMPLID = A.EMPLID AND E.INSTITUTION = A.INSTITUTION AND E.EFF_STATUS = 'A' AND ( substr ( E.STDNT_GROUP, 1, 2) IN ('M1','M2') OR E.STDNT_GROUP = 'S100')) AND NOT EXISTS (SELECT F.EMPLID FROM PS_STDNT_TEST_COMP F WHERE F.EMPLID = A.EMPLID AND F.TEST_COMPONENT IN ('MPL2','MPL3','MPL5') AND (SYSDATE - TEST_DT) / 365 < 2) AND NOT EXISTS (SELECT G.EMPLID FROM PS_SRVC_IND_DATA G WHERE G.EMPLID = A.EMPLID AND G.INSTITUTION = A.INSTITUTION AND G.SRVC_IND_CD = 'TST' AND G.SRVC_IND_REASON = 'MTHPL') ORDER BY 1, 2, 3 |