EZRouting PowerQuery v2

<queries>
    <query name="org.sdst.ezrouting_export" flattened="true">
        <summary>SDST data export for EZ Routing system</summary>
        <args></args>
        <columns>
            <column column="students.student_number">01_student_number</column>
            <column column="students.last_name">02_last_name</column>
            <column column="students.first_name">03_first_name</column>
            <column column="students.middle_name">04_middle_name</column>
            <column column="students.last_name">05_Race</column>
            <column column="students.gender">06_gender</column>
            <column column="students.last_name">07_dob</column>
            <column column="students.last_name">08_schoolid</column>
            <column column="students.grade_level">09_grade_level</column>
            <column column="students.street">10_street</column>
            <column column="students.city">11_city</column>
            <column column="students.state">12_state</column>
            <column column="students.zip">13_zip</column>
            <column column="students.mailing_street">14_mailing_street</column>
            <column column="students.mailing_city">15_mailing_city</column>
            <column column="students.mailing_state">16_mailing_state</column>
            <column column="students.mailing_zip">17_mailing_zip</column>
            <column column="students.last_name">18_contact1_name</column>
            <column column="students.last_name">19_contact1_relation</column>
            <column column="students.last_name">20_contact1_phone1</column>
            <column column="students.last_name">21_contact1_phonetype1</column>
            <column column="students.last_name">22_contact1_phone2</column>
            <column column="students.last_name">23_contact1_phonetype2</column>
            <column column="students.last_name">24_contact1_phone3</column>
            <column column="students.last_name">25_contact1_phonetype3</column>
            <column column="students.last_name">26_contact1_email</column>
            <column column="students.last_name">27_contact2_name</column>
            <column column="students.last_name">28_contact2_relation</column>
            <column column="students.last_name">29_contact2_phone1</column>
            <column column="students.last_name">30_contact2_phonetype1</column>
            <column column="students.last_name">31_contact2_phone2</column>
            <column column="students.last_name">32_contact2_phonetype2</column>
            <column column="students.last_name">33_contact2_phone3</column>
            <column column="students.last_name">34_contact2_phonetype3</column>
            <column column="students.last_name">35_contact2_email</column>
            <column column="students.last_name">36_contact3_name</column>
            <column column="students.last_name">37_contact3_relation</column>
            <column column="students.last_name">38_contact3_phone1</column>
            <column column="students.last_name">39_contact3_phonetype1</column>
            <column column="students.last_name">40_contact3_phone2</column>
            <column column="students.last_name">41_contact3_phonetype2</column>
            <column column="students.last_name">42_contact3_phone3</column>
            <column column="students.last_name">43_contact3_phonetype3</column>
            <column column="students.last_name">44_contact3_email</column>
            <column column="students.last_name">45_contact4_name</column>
            <column column="students.last_name">46_contact4_relation</column>
            <column column="students.last_name">47_contact4_phone1</column>
            <column column="students.last_name">48_contact4_phonetype1</column>
            <column column="students.last_name">49_contact4_phone2</column>
            <column column="students.last_name">50_contact4_phonetype2</column>
            <column column="students.last_name">51_contact4_phone3</column>
            <column column="students.last_name">52_contact4_phonetype3</column>
            <column column="students.last_name">53_contact4_email</column>
            <column column="students.last_name">54_EntryDate</column>
            <column column="students.last_name">55_ExitDate</column>
            <column column="students.last_name">56_sped</column>
        </columns>
        <sql>
            <![CDATA[
                SELECT
                    stu.student_number,
                    stu.last_name,
                    CASE
                        WHEN scf.PSCORE_LEGAL_FIRST_NAME IS NULL THEN stu.first_name
                        ELSE scf.PSCORE_LEGAL_FIRST_NAME
                    END first_name,
                    stu.middle_name,
                    CASE
                        WHEN stu.FedEthnicity = 1 THEN 'H'
                        WHEN (SELECT COUNT(0) FROM studentrace WHERE studentrace.studentid = stu.id) > 1 THEN 'M'
                        ELSE (SELECT RaceCD FROM studentrace WHERE studentrace.studentid = stu.id)
                    END AS Race,
                    stu.gender,
                    to_char(stu.dob, 'MM/DD/YYYY') AS dob,
                    CASE
                        WHEN to_char(stu.schoolid) = '3' THEN nvl(ps.School_Code, 'PRIVATE_UNKNOWN')
                        ELSE to_char(stu.schoolid)
                    END schoolid,
                    stu.grade_level,
                    stu.street,
                    stu.city,
                    stu.state,
                    stu.zip,
                    stu.mailing_street,
                    stu.mailing_city,
                    stu.mailing_state,
                    stu.mailing_zip,
                    contacts.contact1_name,
                    contacts.contact1_relation,
                    contacts.contact1_phone1,
                    contacts.contact1_phonetype1,
                    contacts.contact1_phone2,
                    contacts.contact1_phonetype2,
                    contacts.contact1_phone3,
                    contacts.contact1_phonetype3,
                    contacts.contact1_email,
                    contacts.contact2_name,
                    contacts.contact2_relation,
                    contacts.contact2_phone1,
                    contacts.contact2_phonetype1,
                    contacts.contact2_phone2,
                    contacts.contact2_phonetype2,
                    contacts.contact2_phone3,
                    contacts.contact2_phonetype3,
                    contacts.contact2_email,
                    contacts.contact3_name,
                    contacts.contact3_relation,
                    contacts.contact3_phone1,
                    contacts.contact3_phonetype1,
                    contacts.contact3_phone2,
                    contacts.contact3_phonetype2,
                    contacts.contact3_phone3,
                    contacts.contact3_phonetype3,
                    contacts.contact3_email,
                    contacts.contact4_name,
                    contacts.contact4_relation,
                    contacts.contact4_phone1,
                    contacts.contact4_phonetype1,
                    contacts.contact4_phone2,
                    contacts.contact4_phonetype2,
                    contacts.contact4_phone3,
                    contacts.contact4_phonetype3,
                    contacts.contact4_email,
                    to_char(stu.EntryDate, 'MM/DD/YYYY') AS EntryDate,
                    CASE
                        WHEN stu.ExitDate < t_DistrictYr.LastDay THEN to_char(stu.ExitDate, 'MM/DD/YYYY')
                        ELSE ''
                    END ExitDate,
                    CASE
                        WHEN sped.IS_SPED_Student_YN = '1' THEN 'Y'
                        ELSE 'N'
                    END sped
                FROM
                    students stu
                LEFT JOIN
                    StudentCoreFields scf ON scf.studentsdcid = stu.dcid
                LEFT JOIN
                    S_PA_STU_X sped ON sped.studentsdcid = stu.dcid
                LEFT JOIN
                    U_Private_School ps ON ps.studentsdcid = stu.dcid
                LEFT JOIN
                    Terms t_DistrictYr ON t_DistrictYr.SchoolId = 0 AND t_DistrictYr.FirstDay <= SYSDATE AND t_DistrictYr.LastDay >= SYSDATE AND t_DistrictYr.IsyearRec = 1
                LEFT JOIN
                    Terms t_SchoolYr ON t_SchoolYr.SchoolId = stu.SchoolId AND t_SchoolYr.YearId = t_DistrictYr.YearId AND t_SchoolYr.IsyearRec = 1
                LEFT OUTER JOIN (
                    SELECT
                        *
                    FROM
                        (
                            SELECT
                                sca.studentdcid,
                                row_number() over (
                                    PARTITION BY sca.studentdcid
                                    ORDER BY sca.contactpriorityorder
                                ) R,
                                nvl(
                                    trim(
                                        pers.firstname || ' ' || pers.lastname
                                    ),
                                    chr(91) || 'NO NAME' || chr(93)
                                ) name,
                                rel.code relation,
                                personphonenumber.phone1,
                                personphonenumber.phone2,
                                personphonenumber.phone3,
                                personphonetype.phonetype1,
                                personphonetype.phonetype2,
                                personphonetype.phonetype3,
                                personemail.email
                            FROM
                                studentcontactassoc sca
                                INNER JOIN studentcontactdetail scd ON sca.studentcontactassocid = scd.studentcontactassocid
                                    AND nvl(scd.startdate, SYSDATE) <= SYSDATE
                                    AND nvl(scd.enddate, SYSDATE) >= SYSDATE
                                    AND (
                                        scd.iscustodial = 1
                                        OR scd.liveswithflg = 1
                                    )
                                LEFT OUTER JOIN codeset rel ON sca.currreltypecodesetid = rel.codesetid
                                LEFT OUTER JOIN person pers ON sca.personid = pers.id
                                LEFT OUTER JOIN (
                                    SELECT
                                        *
                                    FROM
                                        (
                                            SELECT
                                                personphonenumberassoc1.personid,
                                                personphonenumberassoc1.phonenumberasentered phonenumber,
                                                row_number() over (
                                                    PARTITION BY personphonenumberassoc1.personid
                                                    ORDER BY personphonenumberassoc1.phonenumberpriorityorder
                                                ) R
                                            FROM
                                                personphonenumberassoc personphonenumberassoc1
                                        ) pivot (
                                            MAX(phonenumber) FOR R IN (1 phone1, 2 phone2, 3 phone3)
                                        )
                                ) personphonenumber ON pers.id = personphonenumber.personid
                                LEFT OUTER JOIN (
                                    SELECT
                                        *
                                    FROM
                                        (
                                            SELECT
                                                personphonenumberassoc2.personid,
                                                phonetype.code phonetype,
                                                row_number() over (
                                                    PARTITION BY personphonenumberassoc2.personid
                                                    ORDER BY personphonenumberassoc2.phonenumberpriorityorder
                                                ) R
                                            FROM
                                                personphonenumberassoc personphonenumberassoc2
                                                LEFT OUTER JOIN codeset phonetype ON personphonenumberassoc2.phonetypecodesetid = phonetype.codesetid
                                        ) pivot (
                                            MAX(phonetype) FOR R IN (1 phonetype1, 2 phonetype2, 3 phonetype3)
                                        )
                                ) personphonetype ON pers.id = personphonetype.personid
                                LEFT OUTER JOIN (
                                    SELECT
                                        *
                                    FROM
                                        (
                                            SELECT
                                                pea.personid,
                                                ea.EmailAddress email,
                                                row_number() over (
                                                    partition BY pea.personid
                                                    order by pea.EmailAddressPriorityOrder
                                                ) R
                                            FROM
                                                PersonEmailAddressAssoc pea
                                            RIGHT JOIN
                                                EMAILADDRESS ea ON ea.EMAILADDRESSID = pea.EMAILADDRESSID
                                        ) pivot (
                                            MAX(email) FOR R IN (1 email)
                                        )
                                ) personemail ON pers.id = personemail.personid
                        ) pivot (
                            MAX(name) name,
                            MAX(relation) relation,
                            MAX(phone1) phone1,
                            MAX(phone2) phone2,
                            MAX(phone3) phone3,
                            MAX(phonetype1) phonetype1,
                            MAX(phonetype2) phonetype2,
                            MAX(phonetype3) phonetype3,
                            MAX(email) email FOR R IN (1 contact1, 2 contact2, 3 contact3, 4 contact4)
                        )
                ) contacts ON stu.dcid = contacts.studentdcid
                WHERE
                    (stu.enroll_status = 0 OR stu.enroll_status = 2) AND stu.ExitDate > t_DistrictYr.FirstDay
                ORDER BY
                    stu.lastfirst
            ]]>
        </sql>
    </query>
</queries>