Results

Returns: Last Name, First Name, Middle Name, EMPLID, Admit_Type, Admit_Term, Acad_Prog, Acad_Plan, Prog_Action, Phone, Email_ADDR

 

 
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 = '10858089'
    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 = '10858089'
    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 = 'NTMT'
     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
  • No labels