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