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 |