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 specififed term. Query included DOB, National ID (NID), if exists, or external system ID type "LID" (assigned SSN).
Results Sample
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(D.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 D WHERE ( A.INSTITUTION = :1 AND A.STRM = :2 AND A.EMPLID = B.EMPLID AND B.NAME_TYPE = 'PRI' AND A.EMPLID = C.EMPLID (+) AND A.EMPLID = D.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(G.BIRTHDATE,'YYYY-MM-DD') FROM PS_STDNT_CAR_TERM E, PS_NAME_CURR_VW F, PS_PERSNL_FERPA_VW G, PS_EXTERNAL_SYSTEM H WHERE ( E.EMPLID = F.EMPLID AND E.EMPLID = G.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 = :2 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' )) ) |
...