Backgroud

Kevin from Zicklin Graduate Admission has requested report based on the following criteria:

  • New Applicants
  • Status is 'DEIN' (program Action = 'DEIN')

 

Fields are:

  • Empid
  • Names (FIRST, LAST)
  • Program Plan
  • Action
  • Admit term

The ticket reference for this request, please visit Kace Ticket: https://helpdesk.baruch.cuny.edu/admin ServiceRequest Detail TICK:52668

 

ADM_ACTION_TBL – Joined from ADM_MC_VW for Action type – see CU_BAR_AD_00033

 

 

 

 

The image of CU_BAR_AD_00019_2

Query SQL:

SELECT DISTINCT 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 = '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 ( C.ACAD_PLAN LIKE '%-MBA'

     OR C.ACAD_PLAN IN ('ENTPR-MS','FIN-MS','IS-MS','MKT-MS','OPRR-MS','RESR-MS','STAT-MS','TAX-MS','ACC-MS'))

     AND B.PROG_ACTION = 'DEIN' ))

  ORDER BY 1, 2, 3

  • No labels