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_STATUS, B.PROG_ACTION, B.PROG_REASON, D.PHONE, D.EMAIL_ADDR, E.IMMUNIZATION, TO_CHAR(E.DATE_TAKEN,'YYYY-MM-DD'), TO_CHAR(E.EXPIRATION_DT,'YYYY-MM-DD'), E.STATUS_IMMUN, ' '
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, PS_IMMUNIZATIONS E
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 A.EMPLID = D.EMPLID
AND ( A.ADMIT_TYPE = :5
OR :5 IS NULL)
AND A.EMPLID = E.EMPLID
AND ( B.PROG_STATUS = :6
OR :6 IS NULL) )
UNION
SELECT I.LAST_NAME, I.FIRST_NAME, I.MIDDLE_NAME, F.EMPLID, F.ADMIT_TYPE, G.ADMIT_TERM, G.ACAD_PROG, H.ACAD_PLAN, G.PROG_STATUS, G.PROG_ACTION, G.PROG_REASON, I.PHONE, I.EMAIL_ADDR, ' ', ' ', ' ', ' ', 'No Immunization records'
FROM PS_ADM_APPL_DATA F, PS_ADM_APPL_SCTY F1, PS_ADM_APPL_PROG G, PS_ADM_MAINT_SCTY G1, PS_ADM_APPL_PLAN H, PS_CU_BIO_VW I
WHERE F.EMPLID = F1.EMPLID
AND F.ACAD_CAREER = F1.ACAD_CAREER
AND F.ADM_APPL_NBR = F1.ADM_APPL_NBR
AND F1.OPRCLASS = 'CUHCPPBAR01'
AND F1.OPRID = '11002650'
AND G.EMPLID = G1.EMPLID
AND G.ACAD_CAREER = G1.ACAD_CAREER
AND G.STDNT_CAR_NBR = G1.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = G1.ADM_APPL_NBR
AND G.APPL_PROG_NBR = G1.APPL_PROG_NBR
AND G1.OPRCLASS = 'CUHCPPBAR01'
AND G1.OPRID = '11002650'
AND ( F.EMPLID = G.EMPLID
AND F.ACAD_CAREER = G.ACAD_CAREER
AND F.STDNT_CAR_NBR = G.STDNT_CAR_NBR
AND F.ADM_APPL_NBR = G.ADM_APPL_NBR
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_ADM_APPL_PROG G_ED
WHERE G.EMPLID = G_ED.EMPLID
AND G.ACAD_CAREER = G_ED.ACAD_CAREER
AND G.STDNT_CAR_NBR = G_ED.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = G_ED.ADM_APPL_NBR
AND G.APPL_PROG_NBR = G_ED.APPL_PROG_NBR
AND G_ED.EFFDT <= SYSDATE)
AND G.EFFSEQ =
(SELECT MAX(G_ES.EFFSEQ) FROM PS_ADM_APPL_PROG G_ES
WHERE G.EMPLID = G_ES.EMPLID
AND G.ACAD_CAREER = G_ES.ACAD_CAREER
AND G.STDNT_CAR_NBR = G_ES.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = G_ES.ADM_APPL_NBR
AND G.APPL_PROG_NBR = G_ES.APPL_PROG_NBR
AND G.EFFDT = G_ES.EFFDT)
AND G.EMPLID = H.EMPLID
AND G.ACAD_CAREER = H.ACAD_CAREER
AND G.STDNT_CAR_NBR = H.STDNT_CAR_NBR
AND G.ADM_APPL_NBR = H.ADM_APPL_NBR
AND G.APPL_PROG_NBR = H.APPL_PROG_NBR
AND G.EFFSEQ = H.EFFSEQ
AND G.EFFDT = H.EFFDT
AND F.INSTITUTION = :1
AND F.ACAD_CAREER = :2
AND G.ADMIT_TERM BETWEEN :3 AND :4
AND ( F.ADMIT_TYPE = :5
OR :5 IS NULL)
AND ( G.PROG_STATUS = :6
OR :6 IS NULL)
AND F.EMPLID = I.EMPLID
AND NOT EXISTS (SELECT J.EMPLID
FROM PS_IMMUNIZATIONS J
WHERE ( J.EMPLID = F.EMPLID )) )
ORDER BY 1, 2, 3
  • No labels