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
  • No labels