CU_BAR_SR_00045_2

Background:

Stephanie Govan already uses CU_BAR_SR_00045_1. She is requesting some additional data in the report. Based on the additional data, some of which is undergraduate or graduate specific, it might make sense to split out the query based on career.

Final Report Delivery with requirements:

CU_BAR_SR_00045_3 Prompts are below:

Institution: Bar01
Career: GRAD
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
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)
SERV_IND_DATA	SRVC_IND_CD for 'APS' have used it with reason code
SERV_IND_DATA	SRVC_IND_CD for 'ACP' have used it with reason code
ACAD_SUBPLAN	DESCR, DEGREE, SSR_NSC_CRD_LVL(DEG LEVEL)
ACAD_PLAN	ACAD_PLAN
ACAD_SUBPLAN	ACAD_SUB_PLAN
STNDT_GRPS	Student Group (SG)

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

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')

The decode function helps to determine the academic level for students based on their ACAD_LEVEL_BOT number.

CASE 
WHEN  F.TOT_TRNSFR > 0 THEN 'Y' ELSE 'N' 
END

If the Total Transfer is grater than 0 then 'Y' else 'N'

LISTAGG(CASE WHEN  M.STDNT_GROUP IN ('IEXP') THEN 'IEXP' 
WHEN  M.STDNT_GROUP IN ('IESN') THEN 'IESN' 
WHEN  M.STDNT_GROUP IN ('ISS') THEN 'ISS' 
WHEN  M.STDNT_GROUP is null then 'Not Provided' 
ELSE 'OTHER' END, ', ') 

WITHIN GROUP (ORDER BY 
CASE WHEN  M.STDNT_GROUP IN ('IEXP') THEN 'IEXP' 
WHEN  M.STDNT_GROUP IN ('IESN') THEN 'IESN' 
WHEN  M.STDNT_GROUP IN ('ISS') THEN 'ISS' 
WHEN  M.STDNT_GROUP is null then 'Not Provided' 
ELSE 'OTHER' END)

Listtagg function helps to fetch multiple values in a single row. it's an aggregate function. In this example, the report determine student based on their student group with multiple value such as 'IEXP', 'IESN', 'ISS'. Student may contain more than one value. Therefore, Listtagg resolve the occurrence of duplications in the report.

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 through SERV_IND_DATA record. Create join with ACAD_PLAN with ACAD_SUB_PLAN to define the major concentration. Added STNDT_GRPS to get student group information.

Query Output Image:

SQL Summery:

SELECT DISTINCT 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 ('IEXP') THEN 'IEXP' 
     WHEN  M.STDNT_GROUP IN ('IESN') THEN 'IESN' 
     WHEN  M.STDNT_GROUP IN ('ISS') THEN 'ISS' 
 when  M.STDNT_GROUP is null then 'Not Provided' 
     ELSE 'OTHER' END, ', ') WITHIN GROUP (ORDER BY 
   CASE WHEN  M.STDNT_GROUP IN ('IEXP') THEN 'IEXP' 
 WHEN  M.STDNT_GROUP IN ('IESN') THEN 'IESN' 
     WHEN  M.STDNT_GROUP IN ('ISS') THEN 'ISS' 
 when  M.STDNT_GROUP is null then 'Not Provided' 
 ELSE 'OTHER' 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