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,
    ADDRESS,
                        POSTALCODE,
                        PROVINCENAME,
    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,
    DESCRIPTIONOFSELECTIONPATH,
    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,
    YUDISIUMSTATUS,
    ETHNIC
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 MASTERDATA.STUDENTSFAMILY SF ON
    (S.STUDENTID = SF.STUDENTID
        AND
                        RELATIONSHIP IN('FATHER', 'ORANG TUA', 'AYAH')
            AND ADDRESS IS NOT NULL)
LEFT JOIN MASTERDATA.PROVINCES P ON
    (SF.PROVINCE = TO_CHAR(P.PROVINCEID))
LEFT JOIN (
    SELECT
        ROUND(SUM(MULTIPLYCREDITCONVERSION)/ SUM(CREDIT), 2) AS GPA,
        STUDENTID
    FROM
        FINALGRADE
    WHERE
        (INDEXPOINT IS NOT NULL
            AND INDEXPOINT <> 'T')
        AND STATUSFASTRACK IS NULL
    GROUP BY
        STUDENTID) 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
        ACADEMIC.ACADEMICSTATUS
    WHERE
        ACADEMICCOUNCILNAME IN ('DIPLOMA', 'SARJANA', 'MAGISTER')
            AND ACADEMICCOUNCILDATE IS NOT NULL) AC ON
    (S.STUDENTID = AC.STUDENTID)
LEFT JOIN (
    SELECT
                                STUDENTID,
                                TRIM (ETHNIC) ETHNIC
    FROM
                                ADMISSION.PARTICIPANT
    JOIN ADMISSION.ADDITIONALPARTICIPANTDATA
            USING (PARTICIPANTID)
    WHERE
                                STUDENTID IS NOT NULL
                        ) AE ON
    (S.STUDENTID = AE.STUDENTID)
WHERE
    SP.FACULTYID IN (
    SELECT
        FACULTYID
    FROM
        ACADEMIC.USEROFFACULTY
    WHERE
        USERID = '166607')
ORDER BY
    S.STUDENTID ASC