CU_BAR_SR_00080

Greetings,

The Zicklin Dean's Office is currently working on the AACSB accreditation survey and require information via queries in CUNYfirst. We will require the following queries by Monday, April 20th, 2015 to complete the survey:

§ Query 3: Admissions

o Institution: BAR01

o Academic Level Term Start

o Service Indicator: "Accepted In Official Major"

§ Reason: "Admitted to the Zicklin School"

§ Start Term (Prompt to select term)

o Service Indicator: "Academic Plan Status" (include IF they also have a Service Indicator of "Accepted In Official Major")

§ Reason: "Must Satisfy additional req"

§ Start Term (Prompt to select term)

o Academic Plan: BBA plans

If you have any questions or if there is anything else needed, please let me know as soon as possible.

Thank you in advance.

Best regards,
Sarah

SELECT A.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_CAREER, A.ACAD_PROG, B.ACAD_PLAN, I.ACAD_SUB_PLAN, B.PLAN_SEQUENCE, G.DESCR, G.DEGREE, G.SSR_NSC_CRD_LVL, decode ( F.ACAD_LEVEL_BOT, '00', 'Not Set') ||
decode ( F.ACAD_LEVEL_BOT, '11', 'Lower Freshman') ||
decode ( F.ACAD_LEVEL_BOT, '15', 'Upper Freshman')||
decode ( F.ACAD_LEVEL_BOT, '21', 'Lower Sophmore') ||
decode ( F.ACAD_LEVEL_BOT, '25', 'Upper Sophmore')|| decode ( F.ACAD_LEVEL_BOT, '31', 'Lower Junior') ||
decode ( F.ACAD_LEVEL_BOT, '35', 'Upper Junior') || decode ( F.ACAD_LEVEL_BOT, '41', 'Lower Senior') ||
decode ( F.ACAD_LEVEL_BOT, '45', 'Upper Senior') ||
decode ( F.ACAD_LEVEL_BOT, '52', 'Second Degree') ||
decode ( F.ACAD_LEVEL_BOT, 'GR', 'Graduate'), F.UNT_TAKEN_PRGRSS, F.TOT_CUMULATIVE, F.TOT_PASSD_GPA, F.CUM_GPA, D.PHONE, D.EMAIL_ADDR, E.SRVC_IND_CD || ' ' || E.SRVC_IND_REASON, H.SRVC_IND_CD || ' ' || H.SRVC_IND_REASON, E.SRVC_IND_ACT_TERM, H.SRVC_IND_ACT_TERM,G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD')
FROM PS_ACAD_PROG A, PS_ACAD_PLAN B, (SELECT TC.ADMIT_TYPE, TC.EMPLID, TC.ACAD_CAREER, TC.STDNT_CAR_NBR, 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_STDNT_CAR_TERM F, PS_ACAD_PLAN_TBL G, PS_SRVC_IND_DATA E, PS_SRVC_IND_DATA H, PS_ACAD_SUBPLAN I
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.PROG_STATUS = 'AC'
AND A.EMPLID = C.EMPLID (+)
AND A.ACAD_CAREER = C.ACAD_CAREER (+)
AND A.STDNT_CAR_NBR = C.STDNT_CAR_NBR (+)
AND A.ADM_APPL_NBR = C.ADM_APPL_NBR (+)
AND A.INSTITUTION = C.INSTITUTION (+)
AND A.EMPLID = D.EMPLID
AND A.EMPLID = F.EMPLID
AND A.ACAD_CAREER = F.ACAD_CAREER
AND A.STDNT_CAR_NBR = F.STDNT_CAR_NBR
AND A.INSTITUTION = F.INSTITUTION
AND B.ACAD_PLAN = G.ACAD_PLAN
AND G.EFFDT =
(SELECT MAX(G_ED.EFFDT) FROM PS_ACAD_PLAN_TBL G_ED
WHERE G.INSTITUTION = G_ED.INSTITUTION
AND G.ACAD_PLAN = G_ED.ACAD_PLAN
AND G_ED.EFFDT <= B.EFFDT)
AND A.INSTITUTION = G.INSTITUTION
AND A.ACAD_CAREER = :2
AND F.STRM = :3
AND F.UNT_TAKEN_PRGRSS > 0
AND ( G.DEGREE = :4
OR :4 IS NULL)
AND ( A.ACAD_PROG = :5
OR :5 IS NULL)
AND ( B.ACAD_PLAN = :6
OR :6 IS NULL)
AND A.EMPLID = E.EMPLID (+)
AND 'APS' = E.SRVC_IND_CD (+)
AND A.INSTITUTION = E.INSTITUTION (+)
AND A.EMPLID = H.EMPLID (+)
AND A.INSTITUTION = H.INSTITUTION (+)
AND 'ACP' = H.SRVC_IND_CD (+)
AND B.EMPLID = I.EMPLID (+)
AND B.ACAD_CAREER = I.ACAD_CAREER (+)
AND B.EFFSEQ = I.EFFSEQ (+)
AND B.ACAD_PLAN = I.ACAD_PLAN (+)
AND B.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
AND B.EFFDT = I.EFFDT (+) )
ORDER BY 2, 3, 4, 11