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 = '48379'))) 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 = '41'
    AND S.STUDENTTYPEID IN ('1', '8', '9', '10')
ORDER BY
    S.STUDENTID ASC