SELECT
    DISTINCT
                        SUBJECTID,
    CLOMAPPINGID,
    SCOREMINIMUM,
    CLOCLOID,
    CLONAME,
    CONCAT( 'CLO-',
    LPAD( CLONUMBER,
    2,
    '0' ) ) AS CLONUMBER,
    CLOPLOID,
    CONCAT( 'PLO-',
    LPAD( PLONUMBER,
    2,
    '0' ) ) AS PLONUMBER,
    CLOASSESSMENTTOOLSNAME AS ASSESSMENTTOOL,
    ROUND( AVG(NILAI_CLO),
    2 ) AS RATARATA_CLO,
    SUM( CASE WHEN NILAI_CLO > 50.01 THEN 1 ELSE 0 END ) AS DIATAS,
    SUM( CASE WHEN NILAI_CLO = 50.01 THEN 1 ELSE 0 END ) AS SESUAI,
    SUM( CASE WHEN NILAI_CLO < 50.01 THEN 1 ELSE 0 END ) AS DIBAWAH,
    ROUND( SUM( CASE WHEN NILAI_CLO >= 50.01 THEN 1 ELSE 0 END ) / COUNT(NILAI_CLO) * 100,
    2 ) AS REALISASI,
    ROUND( ( SUM( CASE WHEN NILAI_CLO >= 50.01 THEN 1 ELSE 0 END ) / COUNT(NILAI_CLO) * 100 ) - 85,
    2 ) AS GAP_PENINGKATAN,
    EVALUATION,
    IMPROVMENT AS IMPROVEMENT,
    CASE
        WHEN ROUND( AVG(NILAI_CLO),
        2 ) >= 50.01 THEN
                            'TERCAPAI'
        ELSE 'TIDAK TERCAPAI'
    END AS STATUS_KETERCAPAIAN
FROM
    (
    SELECT
        A.STUDYPROGRAMID,
        A.STUDYPROGRAMNAME,
        A.SUBJECTID,
        A.SUBJECTCODE,
        A.SUBJECTNAME,
        A.CLOPLOID,
        A.SCOREMINIMUM,
        A.PLONUMBER,
        A.CLOCLOID,
        A.CLONAME,
        A.CLONUMBER,
        A.COURSEID,
        A.CLASS,
        A.STUDENTID,
        A.SCHOOLYEAR,
        A.SEMESTER,
        A.CLOMAPPINGID,
        A.PERCENTAGE_CLO,
        A.EVALUATION,
        A.IMPROVMENT,
        (
        SELECT
            REGEXP_REPLACE( ( listagg ( E.CLOASSESSMENTTOOLSNAME,
            ',' ) WITHIN GROUP (
        ORDER BY
            E.CLOASSESSMENTTOOLSNAME ) ),
            '([^,]*)(, )+($|,)',
            ' ' )
        FROM
            (
            SELECT
                DISTINCT
                                    SUBJECTCODE,
                STUDYPROGRAMID,
                SCHOOLYEAR,
                SEMESTER,
                CLOASSESSMENTTOOLSNAME,
                CLOASSESSMENTTOOLSID,
                CLOMAPPINGID
            FROM
                CLOASSESSMENTTOOLS
            JOIN CLOQUESTION Q ON
                ( CLOASSESSMENTTOOLSID = Q.CLOASSESMENTID )
                                ) E
        WHERE
            SUBJECTCODE = A.SUBJECTCODE
            AND STUDYPROGRAMID = A.STUDYPROGRAMID
            AND SCHOOLYEAR = A.SCHOOLYEAR
            AND SEMESTER = A.SEMESTER
            AND CLOMAPPINGID = A.CLOMAPPINGID
                            ) AS CLOASSESSMENTTOOLSNAME,
        ROUND( SUM(NILAIPERQUEST) / PERCENTAGE_CLO,
        2 ) * 100 AS NILAI_CLO
    FROM
        (
        SELECT
            B.STUDYPROGRAMID,
            B.STUDYPROGRAMNAME,
            D.SUBJECTCODE,
            H.SUBJECTID,
            H.SUBJECTNAME,
            A.CLOPLOID,
            A.SCOREMINIMUM,
            A.PLONUMBER,
            A.PLONAME,
            C.CLOCLOID,
            C.CLONUMBER,
            C.CLONAME,
            D.SCHOOLYEAR,
            D.SEMESTER,
            CSP.COURSEID,
            COURSE.CLASS,
            CQ.QUESTIONDESCRIPTION,
            CQ.PERCENTAGE,
            D.CLOMAPPINGID,
            CQ.CLOASSESMENTID,
            CSP.STUDENTID,
            CE.EVALUATION,
            CE.IMPROVMENT,
            SUM(CQ.PERCENTAGE) OVER ( PARTITION BY C.CLOPLOID,
            H.SUBJECTID,
            CSP.STUDENTID ) AS PERCENTAGE_PLO,
            SUM(CQ.PERCENTAGE) OVER ( PARTITION BY C.CLOCLOID,
            H.SUBJECTID,
            CSP.STUDENTID ) AS PERCENTAGE_CLO,
            NVL( CSP.CLOPOINT,
            0 ) * ( CQ.PERCENTAGE / 100 ) AS NILAIPERQUEST
        FROM
            ACADEMIC.CLOPLO A
        LEFT JOIN MASTERDATA.STUDYPROGRAM B ON
            ( A.STUDYPROGRAMID = B.STUDYPROGRAMID )
        LEFT JOIN ACADEMIC.CLOCLO C ON
            ( A.CLOPLOID = C.CLOPLOID )
        LEFT JOIN ACADEMIC.CLOMAPPING D ON
            ( C.CLOCLOID = D.CLOCLOID
                AND D.ACTIVESTATUS = 'Y' )
        LEFT JOIN ACADEMIC.SUBJECTS H ON
            ( D.SUBJECTCODE = H.SUBJECTCODE
                AND A.STUDYPROGRAMID = H.STUDYPROGRAMID
                AND H.ACTIVESTATUS = 'YA' )
        LEFT JOIN ACADEMIC.CLOQUESTION CQ ON
            ( D.CLOMAPPINGID = CQ.CLOMAPPINGID )
        LEFT JOIN ACADEMIC.CLOSTUDENTPOINT CSP ON
            ( CQ.CLOQUESTIONID = CSP.CLOQUESTIONID )
        LEFT JOIN ACADEMIC.COURSE ON
            ( CSP.COURSEID = COURSE.COURSEID )
        LEFT JOIN ACADEMIC.CLO_SUBJECTS_EVALUATION CE ON
            ( COURSE.SUBJECTID = CE.SUBJECTID
                AND D.CLOMAPPINGID = CE.CLOMAPPINGID
                AND CE.SCHOOLYEAR = COURSE.SCHOOLYEAR
                AND CE.SEMESTER = COURSE.SEMESTER)
        JOIN (
            SELECT
                AAA.COURSEID,
                AAA.STUDENTID
            FROM
                STUDENTSTUDYCARD AAA
            JOIN COURSE BBB ON
                ( AAA.COURSEID = BBB.COURSEID )
            WHERE
                BBB.SCHOOLYEAR = 2223
                AND BBB.SEMESTER = 2
                AND BBB.SUBJECTID = 23797
                AND AAA.STATUS = 'BERLAKU'
                                    ) STC ON
            ( CSP.COURSEID = STC.COURSEID
                AND STC.STUDENTID = CSP.STUDENTID )
        WHERE
            ACADEMIC.COURSE.SUBJECTID = 23797
            AND ACADEMIC.COURSE.SCHOOLYEAR = 2223
            AND ACADEMIC.COURSE.SEMESTER = 2
                            ) A
    WHERE
        STUDYPROGRAMID = '22'
    GROUP BY
        A.STUDYPROGRAMID,
        A.STUDYPROGRAMNAME,
        A.SUBJECTID,
        A.SUBJECTCODE,
        A.SUBJECTNAME,
        A.CLOPLOID,
        A.SCOREMINIMUM,
        A.PLONUMBER,
        A.CLOCLOID,
        A.CLONAME,
        A.CLONUMBER,
        A.COURSEID,
        A.CLASS,
        A.STUDENTID,
        A.SCHOOLYEAR,
        A.SEMESTER,
        A.CLOMAPPINGID,
        A.PERCENTAGE_CLO,
        A.EVALUATION,
        A.IMPROVMENT
                        )
GROUP BY
    SUBJECTID,
    CLOMAPPINGID,
    SCOREMINIMUM,
    CLOCLOID,
    CLONAME,
    CLONUMBER,
    CLOPLOID,
    PLONUMBER,
    CLOASSESSMENTTOOLSNAME,
    EVALUATION,
    IMPROVMENT
ORDER BY
    CLONUMBER,
    PLONUMBER ASC