SELECT
    DISTINCT
                S.STUDENTID,
                S.FULLNAME,
                S.CLASS,
                S.CLASS2,
                S.CLASS3,
                F.GPA,
                S.LECTURERCODE,
                S.ACTIVESTATUS,
                S.STUDYPROGRAMID,
                S.STUDENTSCHOOLYEAR,
                S.STUDYFIELD,
                S.OUTDATE,
                S.PARTICIPANTNUMBER,
                S.NOTES,
                S.STUDENTTYPEID,
                S.STARTDATE,
                S.CLASSIFICATIONID,
                ST.STUDENTTYPENAME,
                CASE
        WHEN UPPER(C.CLASSIFICATIONNAME) LIKE '%INTER%CLASS%' THEN C.CLASSIFICATIONNAME
        ELSE SP.STUDYPROGRAMNAME
    END AS STUDYPROGRAMNAME,
                T.PHONE,
                (
    SELECT
                    VALUE
    FROM
                    MASTERDATA.ACCOUNT
    WHERE
                    STUDENTID = S.STUDENTID
        AND INFOID IN (7, 8, 26)
        AND VALUE IS NOT NULL
        AND ROWNUM <= 1
                ) EMAIL,
                'email_unitel' EMAILINSTITUSI,
                TAK,
                GENDER,
                CASE
        WHEN UPPER(C.CLASSIFICATIONNAME) LIKE '%INTER%CLASS%'
        OR S.STUDYPROGRAMID = 40
        OR UPPER(SP.STUDYPROGRAMNAME) LIKE '%INTER%CLASS%' THEN 'KELAS INT'
        WHEN UPPER(C.CLASSIFICATIONNAME) LIKE '%PINDAHAN%'
        OR UPPER(SP.STUDYPROGRAMNAME) LIKE '%PINDAHAN%' THEN 'PINDAHAN'
        ELSE 'REGULER'
    END AS PROGRAMTYPE,
                AD.NATIONALITY,
                TO_CHAR (AD.BIRTHDATE,
    'DD-MM-YYYY') BIRTHDATE,
                DESCRIPTIONOFSELECTIONPATH,
                TO_CHAR(S.OUTDATE, 'DD-MM-YYYY') GRADUATEDATE,
                PASS_CREDIT,
                S.NOTES AS NOTE,
                UPPER(AC.YUDISIUMSTATUS) YUDISIUMSTATUS
FROM
            MASTERDATA.STUDENT S
LEFT JOIN MASTERDATA.ADDITIONALSTUDENTDATA AD ON
    (S.STUDENTID = AD.STUDENTID)
LEFT JOIN MASTERDATA.STUDENTTYPE ST ON
    (S.STUDENTTYPEID = ST.STUDENTTYPEID)
LEFT JOIN MASTERDATA.STUDYPROGRAM SP ON
    (S.STUDYPROGRAMID = SP.STUDYPROGRAMID)
LEFT JOIN ACADEMIC.GPASTUDENT F ON
    (F.STUDENTID = S.STUDENTID)
LEFT JOIN (
    SELECT
                    MAX(VALUE) PHONE,
                    STUDENTID
    FROM
                    MASTERDATA."ACCOUNT"
    WHERE
                    INFOID = 22
    GROUP BY
                    STUDENTID
            ) T ON
    (T.STUDENTID = S.STUDENTID)
LEFT JOIN MASTERDATA.STUDENT_TOKEN ON
    (S.STUDENTID = "nim")
LEFT JOIN (
    SELECT
                    STUDENTID,
                    SUM(MHS_POINT) TAK
    FROM
                    TAK.VIEW_STUDENT_TAK
    GROUP BY
                    STUDENTID
            ) TA ON
    (S.STUDENTID = TA.STUDENTID)
LEFT JOIN ADMISSION.SELECTIONPATHS ON
    (SELECTIONPATH = SELECTIONPATHID)
LEFT JOIN MASTERDATA.STUDYPROGRAMCLASSIFICATION C ON
    (S.CLASSIFICATIONID = C.CLASSIFICATIONID)
LEFT JOIN (
    SELECT
                STUDENTID,
                    ACADEMICCOUNCILNAME,
                    YUDISIUMSTATUS
    FROM
        (
        SELECT
                    STUDENTID,
                    ACADEMICCOUNCILNAME,
                    YUDISIUMSTATUS,
                    ROW_NUMBER() OVER (PARTITION BY STUDENTID
        ORDER BY
            ACADEMICCOUNCILDATE DESC NULLS LAST,
            inputdate DESC NULLS LAST) AS rno
        FROM
                    ACADEMIC.ACADEMICSTATUS
        WHERE
                    ACADEMICCOUNCILNAME IN ('DIPLOMA', 'SARJANA', 'MAGISTER')
                AND ACADEMICCOUNCILDATE IS NOT NULL
                    )
    WHERE
        rno = 1
            ) AC ON
    (S.STUDENTID = AC.STUDENTID)
WHERE
                1 = 1
                SP.FACULTYID IN (
    SELECT
        FACULTYID
    FROM
        ACADEMIC.USEROFFACULTY
    WHERE
        USERID = '166607')
    AND st.STUDYPROGRAMID = '31'
    AND REGPROGRAM IS NULL
    AND S.STUDENTSCHOOLYEAR = '2324'
ORDER BY
            S.STUDENTID ASC