SELECT
    A.SUBJECTID,
    A.SUBJECTCODE,
    A.STUDYPROGRAMID,
    A.CREDIT,
    A.SUBJECTNAME,
    A.CURICULUMYEAR,
    A.MANDATORYSTATUS,
    A.ADMITSEMESTER,
    A.ADMITSCHOOLYEAR,
    A.SUBJECTTYPE,
    NVL (B.STUDENTID,
    A.STUDENTID) STUDENTID,
    NVL (B.SCHOOLYEAR,
    A.SCHOOLYEAR) SCHOOLYEAR,
    NVL (B.SEMESTER,
    A.SEMESTER) SEMESTER,
    NVL (B.INDEXPOINT,
    A.INDEXPOINT) INDEXPOINT,
    NVL (B.INDEX_CONVERSION,
    A.INDEX_CONVERSION) INDEX_CONVERSION,
    NVL (B.PASS_STATUS,
    A.PASS_STATUS) PASS_STATUS,
    NVL (B.SUBJECTIDOLD,
    A.SUBJECTIDOLD) SUBJECTIDOLD,
    NVL (B.SUBJECTCODEOLD,
    A.SUBJECTCODEOLD) SUBJECTCODEOLD,
    NVL (B.SUBJECTNAMEOLD,
    A.SUBJECTNAMEOLD) SUBJECTNAMEOLD,
    NVL2 ( B.SUBJECTNAMEOLD,
    DECODE (B.SUBJECTID,
    B.SUBJECTIDOLD,
    '',
    'RESULT OF EQUIVALENCE'),
    STATUS) AS STATUS
FROM
    (
    SELECT
        A.SUBJECTID,
        A.SUBJECTCODE,
        A.STUDYPROGRAMID,
        A.CREDIT,
        A.SUBJECTNAME,
        A.CURICULUMYEAR,
        A.MANDATORYSTATUS,
        A.ADMITSEMESTER,
        A.ADMITSCHOOLYEAR,
        A.SUBJECTTYPE,
        B.STUDENTID,
        B.SCHOOLYEAR,
        B.SEMESTER,
        B.INDEXPOINT,
        B.INDEX_CONVERSION,
        B.PASS_STATUS,
        SUBJECTCODEOLD,
        SUBJECTIDOLD,
        STUDYPROGRAMIDOLD,
        SUBJECTNAMEOLD,
        DECODE (B.SUBJECTID,
        SUBJECTIDOLD,
        '',
        'RESULT OF EQUIVALENCE') STATUS
    FROM
        (
        SELECT
            SUBJECTID,
            SUBJECTCODE,
            STUDYPROGRAMID,
            CREDIT,
            SUBJECTNAME,
            CURICULUMYEAR,
            MANDATORYSTATUS,
            ADMITSEMESTER,
            ADMITSCHOOLYEAR,
            SUBJECTTYPE
        FROM
            SUBJECTS
        WHERE
            STUDYPROGRAMID = (
            SELECT
                STUDYPROGRAMID
            FROM
                MASTERDATA.STUDENT
            WHERE
                STUDENTID = '1304192002')
            AND ACTIVESTATUS = 'YA'
            AND MANDATORYSTATUS IS NOT NULL
            AND SUBJECTTYPE = 'TINGKAT 1'
            AND CURICULUMYEAR = (
            SELECT
                MAX(CURICULUMYEAR)
            FROM
                CURICULUMYEAR)
            AND SUBJECTNAME NOT LIKE '%AGAMA%'
    UNION
        SELECT
            SUBJECTID,
            SUBJECTCODE,
            STUDYPROGRAMID,
            CREDIT,
            SUBJECTNAME,
            CURICULUMYEAR,
            MANDATORYSTATUS,
            ADMITSEMESTER,
            ADMITSCHOOLYEAR,
            SUBJECTTYPE
        FROM
            SUBJECTS
        WHERE
            STUDYPROGRAMID = (
            SELECT
                STUDYPROGRAMID
            FROM
                MASTERDATA.STUDENT
            WHERE
                STUDENTID = '1304192002')
            AND ACTIVESTATUS = 'YA'
            AND MANDATORYSTATUS IS NOT NULL
            AND SUBJECTTYPE = 'TINGKAT 1'
            AND CURICULUMYEAR = (
            SELECT
                MAX(CURICULUMYEAR)
            FROM
                CURICULUMYEAR)
            AND SUBJECTNAME LIKE '%AGAMA%'
            AND SUBJECTNAME LIKE '%' || (
            SELECT
                RELIGION
            FROM
                MASTERDATA.ADDITIONALSTUDENTDATA
            WHERE
                STUDENTID = '1304192002') || '%' ) A
    LEFT JOIN (
        SELECT
            STUDENTID,
            NVL2 (D.SUBJECTID,
            D.SUBJECTID,
            A.SUBJECTID) SUBJECTID,
            NVL2 (D.SUBJECTCODE,
            D.SUBJECTCODE,
            A.SUBJECTCODE) SUBJECTCODE,
            NVL2 (D.STUDYPROGRAMID,
            D.STUDYPROGRAMID,
            A.STUDYPROGRAMID) STUDYPROGRAMID,
            NVL2 (D.SUBJECTNAME,
            D.SUBJECTNAME,
            B.SUBJECTNAME) SUBJECTNAME,
            SCHOOLYEAR,
            SEMESTER,
            INDEXPOINT,
            INDEX_CONVERSION,
            PASS_STATUS,
            NVL2 (D.CURICULUMYEAR,
            D.CURICULUMYEAR,
            B.CURICULUMYEAR) CURICULUMYEAR,
            A.SUBJECTCODE AS SUBJECTCODEOLD,
            A.SUBJECTID AS SUBJECTIDOLD,
            A.STUDYPROGRAMID AS STUDYPROGRAMIDOLD,
            B.SUBJECTNAME AS SUBJECTNAMEOLD
        FROM
            COMPLETECOURSEPOINT A
        JOIN SUBJECTS B ON
            ( A.SUBJECTID = B.SUBJECTID
                AND A.SUBJECTCODE = B.SUBJECTCODE
                AND A.STUDYPROGRAMID = B.STUDYPROGRAMID)
        LEFT JOIN (
            SELECT
                SUBJECTID,
                SUBJECTCODE,
                STUDYPROGRAMID,
                MAX (GROUPID) GROUPID
            FROM
                SUBJECTGROUPEQUIVALENCY
            GROUP BY
                SUBJECTID,
                SUBJECTCODE,
                STUDYPROGRAMID) C ON
            ( B.SUBJECTID = C.SUBJECTID
                AND B.SUBJECTCODE = C.SUBJECTCODE
                AND B.STUDYPROGRAMID = C.STUDYPROGRAMID)
        LEFT JOIN (
            SELECT
                SUBJECTIDEQUIVALENCE AS SUBJECTID,
                SUBJECTCODEEQUIVALENCE AS SUBJECTCODE,
                STUDYPROGRAMIDEQUIVALENCE AS STUDYPROGRAMID,
                SUBJECTNAME AS SUBJECTNAME,
                CURICULUMYEAR AS CURICULUMYEAR,
                GROUPID
            FROM
                SUBJECTEQUIVALENCES A
            JOIN SUBJECTS B ON
                (A.SUBJECTIDEQUIVALENCE = B.SUBJECTID) ) D ON
            (C.GROUPID = D.GROUPID)
        WHERE
            STUDENTID = '1304192002'
            AND A.ACTIVESTATUS = 'Y'
            AND A.LATESTSTATUS = 'Y'
    UNION
        SELECT
            STUDENTID,
            A.SUBJECTID,
            A.SUBJECTCODE,
            A.STUDYPROGRAMID,
            B.SUBJECTNAME,
            SCHOOLYEAR,
            SEMESTER,
            INDEXPOINT,
            INDEX_CONVERSION,
            PASS_STATUS,
            B.CURICULUMYEAR,
            A.SUBJECTCODE AS SUBJECTCODEOLD,
            A.SUBJECTID AS SUBJECTIDOLD,
            A.STUDYPROGRAMID AS STUDYPROGRAMIDOLD,
            B.SUBJECTNAME AS SUBJECTNAMEOLD
        FROM
            COMPLETECOURSEPOINT A
        JOIN SUBJECTS B ON
            ( A.SUBJECTID = B.SUBJECTID
                AND A.SUBJECTCODE = B.SUBJECTCODE
                AND A.STUDYPROGRAMID = B.STUDYPROGRAMID)
        WHERE
            STUDENTID = '1304192002'
            AND A.ACTIVESTATUS = 'Y'
            AND A.LATESTSTATUS = 'Y' ) B ON
        ( A.SUBJECTID = B.SUBJECTID
            AND A.SUBJECTCODE = B.SUBJECTCODE
            AND A.STUDYPROGRAMID = B.STUDYPROGRAMID)) A
LEFT JOIN (
    SELECT
        A.STUDENTID,
        NVL2 (D.SUBJECTID,
        D.SUBJECTID,
        A.SUBJECTID) SUBJECTID,
        NVL2 (D.SUBJECTCODE,
        D.SUBJECTCODE,
        A.SUBJECTCODE) SUBJECTCODE,
        NVL2 (D.STUDYPROGRAMID,
        D.STUDYPROGRAMID,
        A.STUDYPROGRAMID) STUDYPROGRAMID,
        NVL2 (D.SUBJECTNAME,
        D.SUBJECTNAME,
        B.SUBJECTNAME) SUBJECTNAME,
        SCHOOLYEAR,
        SEMESTER,
        INDEXPOINT,
        INDEX_CONVERSION,
        PASS_STATUS,
        NVL2 (D.CURICULUMYEAR,
        D.CURICULUMYEAR,
        B.CURICULUMYEAR) CURICULUMYEAR,
        A.SUBJECTCODE AS SUBJECTCODEOLD,
        A.SUBJECTID AS SUBJECTIDOLD,
        A.STUDYPROGRAMID AS STUDYPROGRAMIDOLD,
        B.SUBJECTNAME AS SUBJECTNAMEOLD
    FROM
        COMPLETECOURSEPOINT A
    JOIN (
        SELECT
            STUDENTID,
            STUDYPROGRAMID
        FROM
            MASTERDATA.STUDENT
        WHERE
            STUDENTID = '1304192002') E ON
        (A.STUDENTID = E.STUDENTID)
    JOIN SUBJECTS B ON
        ( A.SUBJECTID = B.SUBJECTID
            AND A.SUBJECTCODE = B.SUBJECTCODE
            AND A.STUDYPROGRAMID = B.STUDYPROGRAMID)
    LEFT JOIN (
        SELECT
            SUBJECTID,
            SUBJECTCODE,
            STUDYPROGRAMID,
            MAX (GROUPID) GROUPID
        FROM
            SUBJECTGROUPEQUIVALENCY
        GROUP BY
            SUBJECTID,
            SUBJECTCODE,
            STUDYPROGRAMID) C ON
        (B.SUBJECTCODE = C.SUBJECTCODE
            AND E.STUDYPROGRAMID = C.STUDYPROGRAMID)
    LEFT JOIN (
        SELECT
            SUBJECTIDEQUIVALENCE AS SUBJECTID,
            SUBJECTCODEEQUIVALENCE AS SUBJECTCODE,
            STUDYPROGRAMIDEQUIVALENCE AS STUDYPROGRAMID,
            SUBJECTNAME AS SUBJECTNAME,
            CURICULUMYEAR AS CURICULUMYEAR,
            GROUPID
        FROM
            SUBJECTEQUIVALENCES A
        JOIN SUBJECTS B ON
            (A.SUBJECTIDEQUIVALENCE = B.SUBJECTID) ) D ON
        (C.GROUPID = D.GROUPID)
    WHERE
        A.STUDENTID = '1304192002'
        AND A.ACTIVESTATUS = 'Y'
        AND A.LATESTSTATUS = 'Y'
        AND A.STUDYPROGRAMID != E.STUDYPROGRAMID) B ON
    (A.SUBJECTCODE = B.SUBJECTCODE)
ORDER BY
    STUDENTID,
    SCHOOLYEAR,
    SEMESTER,
    INDEXPOINT