Versions Compared

Old Version 1

changes.mady.by.user Tabassum Lodhi

Saved on

compared with

New Version Current

changes.mady.by.user Tabassum Lodhi

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

 
 
SELECT D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, A.EMPLID, A.ACAD_PROG, B.ACAD_PLAN, C.ADMIT_TYPE, D.EMAIL_ADDR, E.EMAIL_ADDR, ' '
FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, (SELECT TC.ADMIT_TYPE, TC.EMPLID, TC.ACAD_CAREER, TC.ADM_APPL_NBR, TC.INSTITUTION
FROM PS_ADM_APPL_DATA TC,PS_ADM_APPL_SCTY C1 WHERE TC.EMPLID = C1.EMPLID AND TC.ACAD_CAREER = C1.ACAD_CAREER AND TC.ADM_APPL_NBR = C1.ADM_APPL_NBR AND C1.OPRCLASS = 'CUHCPPBAR01' AND C1.OPRID = '11002650' ) C, PS_CU_BIO_VW D, PS_EMAIL_FERPA_VW E
WHERE ( A.EFFDT =
(SELECT MAX(A_ED.EFFDT) FROM PS_ACAD_PROG A_ED
WHERE A.EMPLID = A_ED.EMPLID
AND A.ACAD_CAREER = A_ED.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ED.STDNT_CAR_NBR
AND A_ED.EFFDT <= SYSDATE)
AND A.EFFSEQ =
(SELECT MAX(A_ES.EFFSEQ) FROM PS_ACAD_PROG A_ES
WHERE A.EMPLID = A_ES.EMPLID
AND A.ACAD_CAREER = A_ES.ACAD_CAREER
AND A.STDNT_CAR_NBR = A_ES.STDNT_CAR_NBR
AND A.EFFDT = A_ES.EFFDT)
AND A.EMPLID = B.EMPLID
AND A.ACAD_CAREER = B.ACAD_CAREER
AND A.STDNT_CAR_NBR = B.STDNT_CAR_NBR
AND A.EFFSEQ = B.EFFSEQ
AND A.EFFDT = B.EFFDT
AND A.INSTITUTION = :1
AND A.ACAD_CAREER = :2
AND A.PROG_STATUS = 'AC'
AND A.ADMIT_TERM BETWEEN :3 AND :4
AND A.EMPLID = D.EMPLID
AND ( C.ADMIT_TYPE = :5
OR :5 IS NULL)
AND A.EMPLID = C.EMPLID (+)
AND A.ACAD_CAREER = C.ACAD_CAREER (+)
AND A.ADM_APPL_NBR = C.ADM_APPL_NBR (+)
AND A.INSTITUTION = C.INSTITUTION (+)
AND A.ACAD_PROG <> 'NDEGU'
AND A.EMPLID = E.EMPLID (+)
AND 'CAMP' = E.E_ADDR_TYPE (+) )
UNION
SELECT G.LAST_NAME, G.FIRST_NAME, G.MIDDLE_NAME, F.EMPLID, ' ', ' ', ' ', ' ', ' ', H.STDNT_GROUP
FROM PS_ACAD_PROG F, PS_CU_BIO_VW G, PS_STDNT_GRPS_HIST H, (SELECT TI.ADMIT_TYPE, TI.EMPLID, TI.ACAD_CAREER, TI.ADM_APPL_NBR, TI.INSTITUTION
FROM PS_ADM_APPL_DATA TI,PS_ADM_APPL_SCTY I1 WHERE TI.EMPLID = I1.EMPLID AND TI.ACAD_CAREER = I1.ACAD_CAREER AND TI.ADM_APPL_NBR = I1.ADM_APPL_NBR AND I1.OPRCLASS = 'CUHCPPBAR01' AND I1.OPRID = '11002650' ) I
WHERE ( F.EFFDT =
(SELECT MAX(F_ED.EFFDT) FROM PS_ACAD_PROG F_ED
WHERE F.EMPLID = F_ED.EMPLID
AND F.ACAD_CAREER = F_ED.ACAD_CAREER
AND F.STDNT_CAR_NBR = F_ED.STDNT_CAR_NBR
AND F_ED.EFFDT <= SYSDATE)
AND F.EFFSEQ =
(SELECT MAX(F_ES.EFFSEQ) FROM PS_ACAD_PROG F_ES
WHERE F.EMPLID = F_ES.EMPLID
AND F.ACAD_CAREER = F_ES.ACAD_CAREER
AND F.STDNT_CAR_NBR = F_ES.STDNT_CAR_NBR
AND F.EFFDT = F_ES.EFFDT)
AND F.EMPLID = G.EMPLID
AND F.EMPLID = H.EMPLID
AND H.INSTITUTION = F.INSTITUTION
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_STDNT_GRPS_HIST H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.INSTITUTION = H_ED.INSTITUTION
AND H.STDNT_GROUP = H_ED.STDNT_GROUP
AND H_ED.EFFDT <= SYSDATE)
AND H.EFF_STATUS = 'A'
AND F.INSTITUTION = :1
AND F.ACAD_CAREER = :2
AND F.ADMIT_TERM BETWEEN :3 AND :4
AND F.EMPLID = I.EMPLID (+)
AND F.ACAD_CAREER = I.ACAD_CAREER (+)
AND F.ADM_APPL_NBR = I.ADM_APPL_NBR (+)
AND F.INSTITUTION = I.INSTITUTION (+)
AND ( I.ADMIT_TYPE = :5
OR :5 IS NULL)
AND F.PROG_STATUS = 'AC'
AND F.ACAD_PROG <> 'NDEGU' )
ORDER BY 1, 2, 3, 10