bu_sql

gistfile1.txt

SELECT DISTINCT
    EMAIL.EMAILID                         AS USERID
    , P.FIRST_NAME                          AS FIRST_NAME
    , P.LAST_NAME                           AS LAST_NAME
    , P.LAST_NAME
      || '-'
      || P.FIRST_NAME
      || '-'
      || EMAIL.EMAILID AS UNIQUE_NAME
    , P.ID                                  AS LMS_USER_ID
    , EMAIL.EMAILID || '@acu.edu' AS EMAIL_ADDRESS
    , CASE
        WHEN F.SCHEDULED_THIS_PERIOD_IND = 'Y' THEN
            '23'
        ELSE
            '3'
    END AS BLUE_ROLE
    , '' AS DATASOURCE_ACCESS_KEY
    , '' AS PREFFEREDLANGUAGE_1
    , P.GENDER                              AS GENDER
    ,
--Faculty Specific
     F.HOME_DEPARTMENT_DESC                AS FACULTY_DEPARTMENT
    , F.HOME_COLLEGE_DESC                   AS COLLEGE
    , NULL AS SUPERVISOR_GROUPS
    , CASE
        WHEN F.SCHEDULED_THIS_PERIOD_IND = 'Y' THEN
            'Instructor'
        ELSE
            NULL
    END AS TEACHING_ROLE
    ,
--Student Specific
    (
        SELECT
            SCSQ.COHORT_DESC
        FROM
            STUDENT_COHORT SCSQ
        WHERE
            SCSQ.PERSON_UID = S.PERSON_UID
            AND SCSQ.ACADEMIC_PERIOD = S.ACADEMIC_PERIOD
            AND SCSQ.COHORT_ACTIVE_IND = 'Y'
            AND ROWNUM = 1
    ) AS COHORT
    , ACAD1.GRAD_ACADEMIC_PERIOD_INTENDED   AS EXPECTED_GRAD_TERM_CODE
    , GPATERM.GPA                           AS TERM_GPA
    , GPATERM.GPA_CREDITS                   AS TERM_GPA_HOURS
    , S.TOTAL_CREDITS                       AS TERM_CREDIT_HOURS
    , GPAT.CREDITS_EARNED                   AS TRANSFER_CUMULATIVE_HOURS
    , GPAI.CREDITS_EARNED                   AS INSTITUTIONAL_CUMULATIVE_HOURS
    , GPAI.GPA                              AS INSTITUTIONAL_CUMULATIVE_GPA
    , GPAO.GPA                              AS OVERALL_CUMULATIVE_GPA
    , CASE
        WHEN GPAI.GPA > 0 THEN
            (
                SELECT
                    MIN(GPAMIN.GPA)
                FROM
                    GPA GPAMIN
                WHERE
                    GPAMIN.PERSON_UID = S.PERSON_UID
                    AND GPA > 0
            )
            || '-'
            ||(
                SELECT
                    MAX(GPAMAX.GPA)
                FROM
                    GPA GPAMAX
                WHERE
                    GPAMAX.PERSON_UID = S.PERSON_UID
            )
        ELSE
            NULL
    END AS GPA_RANGE
    , ACAD1.CAMPUS_DESC                     AS CAMPUS
    , CASE
        WHEN S.STUDENT_STATUS = 'AS'                    THEN
            CASE
        WHEN S.STUDENT_CLASSIFICATION_DESC = 'Graduate' THEN
            'Graduate'
        ELSE
            'Undergraduate'
    END ELSE NULL END AS Student_Level,
acad1.STUDENT_POPULATION_DESC AS Student_Type,
acad1.MAJOR_DESC AS Major_1,
acad2.MAJOR_DESC AS Major_2,
acad1.FIRST_MINOR_DESC AS Minor_1,
CASE WHEN acad1.SECOND_MINOR_DESC IS NOT NULL THEN acad1.SECOND_MINOR_DESC ELSE acad2.FIRST_MINOR_DESC END AS Minor_2,
acad1.FIRST_CONCENTRATION_DESC AS Concentration_1,
acad1.DEGREE_DESC AS Degree_1,
acad2.DEGREE_DESC AS Degree_2,
acad1.PROGRAM AS Program_Code,
acad1.CATALOG_ACADEMIC_PERIOD AS Catalog_Term,
acad1.DEPARTMENT_DESC AS Student_Department,
acad1.COLLEGE_DESC AS College_1,
acad2.COLLEGE_DESC AS College_2,
s.STUDENT_CLASSIFICATION_DESC AS Classification,
acad1.ADMISSIONS_POPULATION_DESC AS Admit_Type,
CASE WHEN s.STUDENT_STATUS = 'AS' THEN vc.VETERAN_TYPE_DESC ELSE NULL END AS Veteran_Description,
CASE WHEN s.STUDENT_STATUS = 'AS' THEN p.PRIMARY_ETHNICITY_DESC ELSE NULL END AS Ethnicity,
CASE WHEN s.STUDENT_STATUS = 'AS' THEN p.PRIM_ETHNICITY_CATEGORY_DESC ELSE NULL END AS Ethnicity_Code_Description,
race.RACE_DESC1 AS GORPRAC_1,
race.RACE_DESC2 AS GORPRAC_2,
race.RACE_DESC3 AS GORPRAC_3,
CASE WHEN s.STUDENT_STATUS = 'AS' THEN p.NATION_OF_CITIZENSHIP_DESC ELSE NULL END AS Nation,
(SELECT sa.STUDENT_ATTRIBUTE_DESC FROM student_attribute sa 
        WHERE sa.ACADEMIC_PERIOD = s.ACADEMIC_PERIOD AND sa.PERSON_UID = s.PERSON_UID
        AND rownum = 1) AS Student_Attribute_1, 
(SELECT sa.STUDENT_ATTRIBUTE_DESC FROM student_attribute sa 
        WHERE sa.ACADEMIC_PERIOD = s.ACADEMIC_PERIOD AND sa.PERSON_UID = s.PERSON_UID
        AND rownum = 2) AS Student_Attribute_2,
(SELECT sa.STUDENT_ATTRIBUTE_DESC FROM student_attribute sa 
        WHERE sa.ACADEMIC_PERIOD = s.ACADEMIC_PERIOD AND sa.PERSON_UID = s.PERSON_UID
        AND rownum = 3) AS Student_Attribute_3,
(SELECT sa.STUDENT_ATTRIBUTE_DESC FROM student_attribute sa 
        WHERE sa.ACADEMIC_PERIOD = s.ACADEMIC_PERIOD AND sa.PERSON_UID = s.PERSON_UID
        AND rownum = 4) AS Student_Attribute_4,
(SELECT sa.STUDENT_ATTRIBUTE_DESC FROM student_attribute sa 
        WHERE sa.ACADEMIC_PERIOD = s.ACADEMIC_PERIOD AND sa.PERSON_UID = s.PERSON_UID
        AND rownum = 5) AS Student_Attribute_5,
ss.ACTIVITY_DESC1 AS Sport_1,
ss.ACTIVITY_DESC2 AS Sport_2,
CASE WHEN s.STUDENT_STATUS = 'AS' THEN trunc((to_number(to_char(sysdate,'YYYYMMDD'))- to_number(to_char(p.BIRTH_DATE,'YYYYMMDD')))/10000) ELSE NULL END AS Age,
CASE WHEN s.STUDENT_STATUS = 'AS' THEN p.CITIZENSHIP_IND ELSE NULL END AS Citizen,
CASE WHEN s.STUDENT_STATUS = 'AS' THEN p.RELIGION_DESC ELSE NULL END AS Religious_Description
FROM person p
LEFT JOIN ACU.ACU_EMAIL EMAIL 
    ON EMAIL.PERSON_UID = P.PERSON_UID
LEFT JOIN FACULTY F 
    ON F.PERSON_UID = P.PERSON_UID 
    AND F.ACADEMIC_PERIOD = '201930'
LEFT JOIN EMPLOYEE E 
    ON E.PERSON_UID = P.PERSON_UID
LEFT JOIN STUDENT S 
    ON S.PERSON_UID = P.PERSON_UID 
    AND S.ACADEMIC_PERIOD = '201930' 
    AND S.STUDENT_STATUS = 'AS'
LEFT JOIN ACADEMIC_STUDY ACAD1 
    ON ACAD1.PERSON_UID = S.PERSON_UID 
    AND ACAD1.ACADEMIC_PERIOD = S.ACADEMIC_PERIOD 
    AND ACAD1.PROGRAM_NUMBER = '1'
LEFT JOIN ACADEMIC_STUDY ACAD2 
    ON ACAD2.PERSON_UID = S.PERSON_UID 
    AND ACAD2.ACADEMIC_PERIOD = S.ACADEMIC_PERIOD 
    AND ACAD2.PROGRAM_NUMBER = '2'
LEFT JOIN GPA GPAO 
    ON GPAO.PERSON_UID = S.PERSON_UID 
    AND GPAO.GPA_TIME_IND = 'N' 
    AND GPAO.GPA_TYPE = 'O' 
    AND GPAO.ACADEMIC_STUDY_VALUE = ACAD1.STUDENT_LEVEL
LEFT JOIN GPA GPAI 
    ON GPAI.PERSON_UID = S.PERSON_UID 
    AND GPAI.GPA_TIME_IND = 'N' 
    AND GPAI.GPA_TYPE = 'I' 
    AND GPAI.ACADEMIC_STUDY_VALUE = ACAD1.STUDENT_LEVEL
LEFT JOIN GPA GPAT 
    ON GPAT.PERSON_UID = S.PERSON_UID 
    AND GPAT.GPA_TIME_IND = 'N' 
    AND GPAT.GPA_TYPE = 'T' 
    AND GPAT.ACADEMIC_STUDY_VALUE = ACAD1.STUDENT_LEVEL
LEFT JOIN GPA GPATERM 
    ON GPATERM.PERSON_UID = S.PERSON_UID 
    AND GPATERM.ACADEMIC_PERIOD = S.ACADEMIC_PERIOD 
    AND GPATERM.GPA_TYPE = 'I' 
    AND GPATERM.ACADEMIC_STUDY_VALUE = ACAD1.STUDENT_LEVEL
LEFT JOIN VETERAN_CERTIFICATION VC 
    ON VC.PERSON_UID = S.PERSON_UID 
    AND VC.ACADEMIC_PERIOD = S.ACADEMIC_PERIOD
LEFT JOIN SPORT_SLOT SS 
    ON SS.PERSON_UID = S.PERSON_UID 
    AND SS.ACADEMIC_PERIOD = S.ACADEMIC_PERIOD
LEFT JOIN PERSON_SENSITIVE_RACE_SLOT RACE 
    ON RACE.PERSON_UID = S.PERSON_UID
WHERE 1=1
    (
        (e.EMPLOYEE_STATUS = 'A' OR f.FACULTY_MEMBER_STATUS = 'AC')
        OR (s.STUDENT_STATUS = 'AS' AND s.TOTAL_CREDITS > 0)
    ) 
    AND email.EMAILID IS NOT NULL
--and p.PERSON_UID = 18220
;

select * from internet_address where entity_uid = 18220;