Formerly CU_BAR_SR_00035_1
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.
...
- 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 | ||
|---|---|---|---|
| ACADSTDNT_CAR_PROGTERM | EMPLID, ADMIT TERMSTERM, ACAD_CAREER, ACAD_PROG, | ||
| ACAD_PLAN | ACAD_PLAN,PLAN_SEQ, | ||
| ADM_APPL_DATA | ADMIT_TYPE | ||
| CU_BOI_NAME_CURR_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|
| SCC_PERS_NI_QVW | NATIONAL_ID_TYPE, NATIONAL_ID | ||
| PERSNL_FERPA_VW | BIRTHDATE | ||
Subquery for A.STRM - Term | |||
| 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 | ||
| max (G.STRM) | |||
| UNION | |||
| STDNT_CAR_TERM | EMPLID, ACAD_CAREER, STRM | ||
| NAME_CURR_VW | LAST_NAME, FIRST_NAME | ||
| EXTERNAL_SYSTEM | EXTERNAL_SYSTEM, EXTERNAL_SYSTEM_ID | ||
| PERSNL_FERPA_VW | BIRTHDATE | ||
Subquery for exists | |||
| SCC_PERS_NI_QVW | EMPLID | ||
Subquery for E.STRM - Term | |||
| STDNT_CAR_TERM | max (D.STRM) |
Legend of Query:
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 IndicatorsMaximum term' which pick up the latest update about students such as 'max (G.STRM)','max (D.STRM)' . 'SUBSTR(C.NATIONAL_ID,6,4)', 'SUBSTR(I.NATIONAL_ID,1,1)' have been used to mask national id.
Prompt:
Institution, Career, From Enroll Term and To Enroll Term have been mandatory for an user to input and Degree, ACAD_PROG, ACAD_PLAN are optional to extract more precise data.
Criteria:
...
Query Output Image:
Query SQL:
| Panel |
|---|
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' ) |