SELECT
    DISTINCT
                        S.*,
    SP.STUDYPROGRAMNAME,
                        CELLPHONE,
                        YAHOOMAIL,
                        GMAIL,
                        EMAIL,
                        GPA,
                        NILAID,
                        NILAIE,
                        CREDIT,
    CREDITNOTPASS,
    CREDITPASS,
    CASE
        WHEN CREDITPASS>0 THEN ROUND(MCREDITPASS / CREDITPASS, 2)
        ELSE 0
    END GPAPASS,
                        CREDIT2
FROM
                        (
    SELECT
                                *
    FROM
                                MASTERDATA.STUDENT S
    WHERE
                                S.STUDYPROGRAMID IN (
        SELECT
                                        STUDYPROGRAMID
        FROM
                                        MASTERDATA.STUDYPROGRAM
        WHERE
            FACULTYID IN
                                    (
            SELECT
                FACULTYID
            FROM
                USEROFFACULTY
            WHERE
                USERID = '577205'
                                    )
                                )
                        ) S
LEFT JOIN MASTERDATA.STUDYPROGRAM SP ON
    (
                        S.STUDYPROGRAMID = SP.STUDYPROGRAMID
                    )
LEFT JOIN (
    SELECT
                            STUDENTID,
                            MAX (DECODE(INFOID, 22, VALUE)) CELLPHONE,
                            MAX (DECODE(INFOID, 7, VALUE)) GMAIL,
                            MAX (DECODE(INFOID, 8, VALUE)) YAHOOMAIL
    FROM
                            MASTERDATA. ACCOUNT
    WHERE
                            INFOID IN (7, 8, 22)
    GROUP BY
                            STUDENTID
                    ) t1 ON
    (S.STUDENTID = t1.STUDENTID)
LEFT JOIN (
    SELECT
                            USERNAME,
                            EMAIL
    FROM
                            FRAMEWORK1. USERS
    JOIN FRAMEWORK1.USERMAPPING
            USING (USERID)
    WHERE
                            USERGROUPID = 12
                    ) t2 ON
    (S.STUDENTID = t2.USERNAME)
LEFT JOIN (
    SELECT
                            studentid,
                            PASS_CREDIT CREDIT,
        GPA,
                            COUNT (DECODE(INDEXPOINT, 'D', 1)) NILAID,
                            COUNT (DECODE(INDEXPOINT, 'E', 1)) NILAIE,
                            SUM (DECODE(PASS_STATUS, 'Y', 0, CREDIT)) CREDITNOTPASS,
                            SUM (DECODE(PASS_STATUS, 'Y', CREDIT, 0)) CREDITPASS,
                            SUM (DECODE(PASS_STATUS, 'Y', CREDIT * INDEX_CONVERSION, 0)) MCREDITPASS
    FROM
                            COMPLETECOURSEPOINT
    JOIN GPASTUDENT
            USING (STUDENTID)
    WHERE
                            ACTIVESTATUS = 'Y'
        AND LATESTSTATUS = 'Y'
    GROUP BY
                            (STUDENTID,
        PASS_CREDIT,
        GPA)
                    ) t3 ON
    (S.STUDENTID = t3.STUDENTID)
LEFT JOIN (
    SELECT
        STUDENTID,
        SUM(CREDIT) CREDIT2
    FROM
        VIEW_KSM
    WHERE
        1 = 1
        AND SCHOOLYEAR = '2324'
        AND SEMESTER = '2'
    GROUP BY
        STUDENTID) t4 ON
    (S.STUDENTID = t4.STUDENTID)
WHERE
    1 = 1
    AND S.STUDYPROGRAMID = '11'
    AND S.STUDENTTYPEID IN ('1', '8', '9', '10')
ORDER BY
                        S.STUDENTID ASC