Background and Use
The data is used assignment of student Active Directory ID/SAM and Baruchmail email address. Query is for students with term activation for specified term. Query included DOB, National ID (NID), if exists, or external system ID type "LID" (assigned SSN).
Since it retrieves only one term data and Fall term contain both summer and fall term data. It was inconvenient to generate data 2 different time within the same semester. Therefore, Patrick proposed to include 'term range' in the prompt in this query.
Final Report Delivery with requirements:
The query for ADID Updates Active Term Range CU_BAR_SR_00035_1 has been shared with Patrick and Dixon and the query is working fine.
CU_BAR_SR_00030_1 Prompts are below:
- Institution: Bar01
- From Enroll Term: pick any term
- To Enroll Term: pick any term
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:
Query SQL:
SELECT A.EMPLID, A.ACAD_CAREER, A.STRM, B.LAST_NAME, B.FIRST_NAME, C.NATIONAL_ID_TYPE, C.NATIONAL_ID, TO_CHAR(J.BIRTHDATE,'YYYY-MM-DD')
FROM PS_STDNT_CAR_TERM A, PS_NAME_CURR_VW B, PS_SCC_PERS_NI_QVW C, PS_PERSNL_FERPA_VW J
WHERE ( A.INSTITUTION = :1
AND A.STRM BETWEEN :2 AND :3
AND A.EMPLID = B.EMPLID
AND B.NAME_TYPE = 'PRI'
AND A.EMPLID = C.EMPLID (+)
AND A.STRM = (SELECT max ( G.STRM)
FROM PS_STDNT_CAR_TERM G
WHERE ( G.EMPLID = A.EMPLID
AND G.ACAD_CAREER = A.ACAD_CAREER
AND G.INSTITUTION = A.INSTITUTION
AND G.STDNT_CAR_NBR = A.STDNT_CAR_NBR ))
AND A.EMPLID = J.EMPLID(+) )
UNION
SELECT E.EMPLID, E.ACAD_CAREER, E.STRM, F.LAST_NAME, F.FIRST_NAME, H.EXTERNAL_SYSTEM, H.EXTERNAL_SYSTEM_ID, TO_CHAR(K.BIRTHDATE,'YYYY-MM-DD')
FROM PS_STDNT_CAR_TERM E, PS_NAME_CURR_VW F, PS_EXTERNAL_SYSTEM H, PS_PERSNL_FERPA_VW K
WHERE ( E.EMPLID = F.EMPLID
AND E.EMPLID = H.EMPLID
AND H.EFFDT =
(SELECT MAX(H_ED.EFFDT) FROM PS_EXTERNAL_SYSTEM H_ED
WHERE H.EMPLID = H_ED.EMPLID
AND H.EXTERNAL_SYSTEM = H_ED.EXTERNAL_SYSTEM
AND H_ED.EFFDT <= SYSDATE)
AND H.EXTERNAL_SYSTEM = 'LID'
AND E.INSTITUTION = :1
AND E.STRM BETWEEN :2 AND :3
AND EXISTS (SELECT I.EMPLID
FROM PS_SCC_PERS_NI_QVW I
WHERE ( I.EMPLID = E.EMPLID
AND SUBSTR( I.NATIONAL_ID,1,1) = 'X' ))
AND E.STRM = (SELECT max ( D.STRM)
FROM PS_STDNT_CAR_TERM D
WHERE ( D.EMPLID = E.EMPLID
AND D.ACAD_CAREER = E.ACAD_CAREER
AND D.INSTITUTION = E.INSTITUTION
AND D.STDNT_CAR_NBR = E.STDNT_CAR_NBR ))
AND E.EMPLID = K.EMPLID(+)
AND F.NAME_TYPE = 'PRI' )