SELECT DISTINCT B.LAST_NAME, B.FIRST_NAME, B.MIDDLE_NAME, A.EMPLID, C.SEX, A.ACAD_CAREER, D.ACAD_PROG, A.DEGREE, E.ACAD_PLAN, F.DESCR, A.COMPLETION_TERM, TO_CHAR(A.DEGR_CONFER_DT,'YYYY-MM-DD'), A.GPA_DEGREE, L.EMAIL_ADDR, G.ETHNIC_GROUP, H.ETHNIC_GROUP, I.ETHNIC_GROUP, J.ETHNIC_GROUP, K.ETHNIC_GROUP, TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'), TO_CHAR(SYSDATE,'YYYY-MM-DD'),F.INSTITUTION,F.ACAD_PLAN,TO_CHAR(F.EFFDT,'YYYY-MM-DD') FROM PS_ACAD_DEGR A, PS_CU_BIO_VW B, PS_SF_STDNT_PRS_VW C, PS_ACAD_PROG D, PS_ACAD_PLAN E, PS_ACAD_PLAN_TBL F, PS_SCC_ETH_GRP_VW G, PS_SCC_ETH_GRP_VW H, PS_SCC_ETH_GRP_VW I, PS_SCC_ETH_GRP_VW J, PS_SCC_ETH_GRP_VW K, PS_SCC_EMAIL_QVW L WHERE A.INSTITUTION = :1 AND A.COMPLETION_TERM BETWEEN :2 AND :3 AND A.ACAD_DEGR_STATUS = 'A' AND A.EMPLID = B.EMPLID AND A.EMPLID = C.EMPLID AND A.EMPLID = D.EMPLID AND D.ACAD_CAREER = A.ACAD_CAREER AND D.EFFDT = (SELECT MAX(D_ED.EFFDT) FROM PS_ACAD_PROG D_ED WHERE D.EMPLID = D_ED.EMPLID AND D.ACAD_CAREER = D_ED.ACAD_CAREER AND D.STDNT_CAR_NBR = D_ED.STDNT_CAR_NBR AND D_ED.EFFDT <= SYSDATE) AND D.EFFSEQ = (SELECT MAX(D_ES.EFFSEQ) FROM PS_ACAD_PROG D_ES WHERE D.EMPLID = D_ES.EMPLID AND D.ACAD_CAREER = D_ES.ACAD_CAREER AND D.STDNT_CAR_NBR = D_ES.STDNT_CAR_NBR AND D.EFFDT = D_ES.EFFDT) AND D.INSTITUTION = A.INSTITUTION AND D.PROG_STATUS = 'CM' AND D.EMPLID = E.EMPLID AND D.ACAD_CAREER = E.ACAD_CAREER AND D.STDNT_CAR_NBR = E.STDNT_CAR_NBR AND D.EFFSEQ = E.EFFSEQ AND D.EFFDT = E.EFFDT AND A.STDNT_DEGR = E.STDNT_DEGR AND E.ACAD_PLAN = F.ACAD_PLAN AND F.EFFDT = (SELECT MAX(F_ED.EFFDT) FROM PS_ACAD_PLAN_TBL F_ED WHERE F.INSTITUTION = F_ED.INSTITUTION AND F.ACAD_PLAN = F_ED.ACAD_PLAN AND F_ED.EFFDT <= E.EFFDT) AND F.INSTITUTION = D.INSTITUTION AND A.EMPLID = G.EMPLID (+) AND '1' = G.ETHNIC_GROUP (+) AND A.EMPLID = H.EMPLID (+) AND '2' = H.ETHNIC_GROUP (+) AND A.EMPLID = I.EMPLID (+) AND '3' = I.ETHNIC_GROUP (+) AND A.EMPLID = J.EMPLID (+) AND '4' = J.ETHNIC_GROUP (+) AND A.EMPLID = K.EMPLID (+) AND '5' = K.ETHNIC_GROUP (+) AND F.ACAD_PLAN_TYPE = 'MAJ' AND A.EMPLID = L.EMPLID (+) AND 'CAMP' = L.E_ADDR_TYPE (+) ORDER BY 1, 2, 3 |