CU_BAR_SR_00045_3

Background:

Final Report Delivery with requirements:

CU_BAR_SR_00080 Prompts are below:

Institution: Bar01
Career: UGRD
Term: pick any term
Degree: (optional) ( pick any degree)
AcadProg (optional) If you want to be more specific
AcadPlan (optional) If you want to be more specific

Query Development:

Following table shows all necessary records that have been used to generate fields as requested by an user to create this report.

Records	Description of fields
ACAD_PROG	EMPLID, ADMIT TERM,ACAD_CAREER, ACAD_PROG,
ACAD_PLAN	ACAD_PLAN,PLAN_SEQ,
ADM_APPL_DATA	ADMIT_TYPE
CU_BOI_VW	LAST_NAME, FIRST_NAME, MIDDLE_NAME, PHONE, EMAIL_ADDR
SERV_IND_DATA	SRVC_IND_CD for 'APS' and 'ACP' and have used for their reason codes
STDNT_CAR_TERM	ACADEMIC LEVEL,UNT_TAKEN_PRGRSS,TOT_CUM,TOT_PASSED_GPA, CUM_GPA
ACAD_PLAN_TBL	DESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL)
ACAD_SUBPLAN	ACAD_SUB_PLAN

Expressions:

Few 'Expressions' have been added to find 'Academic Level' have described the prompt as null and define the value of 'ACP' and 'APS'. Reference for Service Indicator: Service Indicators

Prompt:

Institution, Career, Term have been mandatory for an user to input and Degree, ACAD_PROG, ACAD_PLAN are optional to extract more precise data.

Criteria:

ACAD_PROG_STATUS is 'AC' because the report is based on active students. Added 2 service indicator records to define 'APS' and 'ACP' with their reason code. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration.

Query Output Image:

SQL Summery:

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.PLAN_SEQUENCE, B.ACAD_PLAN, I.ACAD_SUB_PLAN, J.ACAD_PLAN, K.ACAD_SUB_PLAN, G.DESCR, G.DEGREE, A.DEGR_CHKOUT_STAT, 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, F.TOT_TRNSFR, CASE
WHEN F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N'
END, LISTAGG(CASE WHEN M.STDNT_GROUP IN ('SEEK') THEN 'SEEK'
WHEN M.STDNT_GROUP IN ('PATH') THEN 'PATH'
WHEN M.STDNT_GROUP IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS'
ELSE NULL END, ', ')
WITHIN GROUP (ORDER BY
CASE WHEN M.STDNT_GROUP IN ('SEEK') THEN 'SEEK'
WHEN M.STDNT_GROUP IN ('PATH') THEN 'PATH'
WHEN M.STDNT_GROUP IN ('HPNS', 'HREG', 'PROV', 'BSCH', 'DSCH') THEN 'HONORS'
ELSE NULL END),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, PS_ACAD_PLAN J, PS_ACAD_SUBPLAN K, PS_STDNT_GRPS M
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.STDNT_CAR_NBR = I.STDNT_CAR_NBR (+)
     AND B.EFFSEQ = I.EFFSEQ (+)
     AND B.ACAD_PLAN = I.ACAD_PLAN (+)
     AND B.EFFDT = I.EFFDT (+)
     AND B.EMPLID = J.EMPLID (+)
     AND B.ACAD_CAREER = J.ACAD_CAREER (+)
     AND B.STDNT_CAR_NBR = J.STDNT_CAR_NBR (+)
     AND B.EFFSEQ = J.EFFSEQ (+)
     AND B.ACAD_PLAN <> J.ACAD_PLAN (+)
     AND B.EFFDT = J.EFFDT (+)
     AND J.EMPLID = K.EMPLID (+)
     AND J.ACAD_CAREER = K.ACAD_CAREER (+)
     AND J.STDNT_CAR_NBR = K.STDNT_CAR_NBR (+)
     AND J.EFFSEQ = K.EFFSEQ (+)
     AND J.ACAD_PLAN = K.ACAD_PLAN (+)
     AND J.EFFDT = K.EFFDT (+)
     AND B.PLAN_SEQUENCE = (SELECT MIN ( L.PLAN_SEQUENCE)
FROM PS_ACAD_PLAN L
WHERE ( L.EMPLID = B.EMPLID
     AND L.ACAD_CAREER = B.ACAD_CAREER
     AND L.STDNT_CAR_NBR = B.STDNT_CAR_NBR
     AND L.EFFDT = B.EFFDT
     AND L.EFFSEQ = B.EFFSEQ ))
     AND A.EMPLID = M.EMPLID (+)
     AND A.INSTITUTION = M.INSTITUTION (+) )
GROUP BY A.EMPLID, D.LAST_NAME, D.FIRST_NAME, D.MIDDLE_NAME, C.ADMIT_TYPE, A.ADMIT_TERM, A.ACAD_CAREER, A.ACAD_PROG, B.PLAN_SEQUENCE, B.ACAD_PLAN, I.ACAD_SUB_PLAN, J.ACAD_PLAN, K.ACAD_SUB_PLAN, G.DESCR, G.DEGREE, A.DEGR_CHKOUT_STAT, 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, F.TOT_TRNSFR, CASE
WHEN F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N'
END,G.INSTITUTION,G.ACAD_PLAN,TO_CHAR(G.EFFDT,'YYYY-MM-DD')
ORDER BY 2, 3, 4, 9

Page tree