SELECT
    A.ID AS IDTEMP,
    A.ID,
    A.WEEK,
    A.MEET,
    C.CLONUMBER,
    CTA.CLOASSESSMENTTOOLSNAME,
    CTM.METHOD_NAME,
    CTF.EXPERIENCE_F AS FACETOFACE,
    CTO.EXPERIENCE_O AS ON_LINE,
    A.APPROVAL_STATUS
FROM
    ACADEMIC.CLO_TEACHING_PLANS A
    LEFT JOIN (
    SELECT
        TA.CLO_TEACHING_PLAN_ID AS CLO_RPS_ID,
        RTRIM(
            XMLAGG( XMLELEMENT ( E, 'CLO ' || TB.CLONUMBER || ',' ) ORDER BY TB.CLONUMBER ).EXTRACT( '//text()' ),
            ','
        ) CLONUMBER
    FROM
        ACADEMIC.CLO_TEACHING_PLANS_MAPPING TA
        JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
        JOIN ACADEMIC.CLOCLO TB ON ( TA.CLOCLOID = TB.CLOCLOID )
    WHERE
        TA.ACTIVE_STATUS = '1'
        AND TPS.SCHOOLYEAR = '2324'
        AND TPS.SEMESTER = '1'
        AND TPS.SUBJECTID = '30364'
    GROUP BY
        TA.CLO_TEACHING_PLAN_ID
    ) C ON ( A.ID = CLO_RPS_ID )
    LEFT JOIN (
    SELECT
        TA.CLO_TEACHING_PLAN_ID AS ASSMENTTOOLS_RPS_ID,
        RTRIM( XMLAGG( XMLELEMENT ( E, TB.CLOASSESSMENTTOOLSNAME || ',' ) ORDER BY TB.CLOASSESSMENTTOOLSNAME ).EXTRACT( '//text()' ), ',' ) CLOASSESSMENTTOOLSNAME
    FROM
        ACADEMIC.CLO_TEACHING_ASSESSMENTOOLS TA
        JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
        JOIN ACADEMIC.CLOASSESSMENTTOOLS TB ON ( TB.CLOASSESSMENTTOOLSID = TA.CLOASSESSMENTTOOLSID )
    WHERE
        TA.ACTIVE_STATUS = '1'
        AND TPS.SCHOOLYEAR = '2324'
        AND TPS.SEMESTER = '1'
        AND TPS.SUBJECTID = '30364'
    GROUP BY
        TA.CLO_TEACHING_PLAN_ID
    ) CTA ON ( A.ID = ASSMENTTOOLS_RPS_ID )
    LEFT JOIN (
    SELECT
        TA.CLO_TEACHING_PLAN_ID AS METHODE_RPS_ID,
        RTRIM( XMLAGG( XMLELEMENT ( E, TA.METHOD_NAME || ',' ) ORDER BY TA.METHOD_NAME ).EXTRACT( '//text()' ), ',' ) METHOD_NAME
    FROM
        ACADEMIC.CLO_TEACHING_METHOD TA
        JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
    WHERE
        TA.ACTIVE_STATUS = '1'
        AND TPS.SCHOOLYEAR = '2324'
        AND TPS.SEMESTER = '1'
        AND TPS.SUBJECTID = '30364'
    GROUP BY
        TA.CLO_TEACHING_PLAN_ID
    ) CTM ON ( A.ID = METHODE_RPS_ID )
    LEFT JOIN (
    SELECT
        TA.CLO_TEACHING_PLAN_ID AS EXPERIENCE_F_RPS_ID,
        RTRIM(
            XMLAGG( XMLELEMENT ( E, TA.LEARNING_DESCRIPTION || '[' || ESTIMATION_TIME || ' Menit]' || '~' ) ORDER BY TA.LEARNING_DESCRIPTION ).EXTRACT( '//text()' ),
            '~'
        ) EXPERIENCE_F
    FROM
        ACADEMIC.CLO_LEARNING_EXPERIENCE TA
        JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
    WHERE
        TA.LEARNING_TYPE = 'Face to face'
        AND TA.ACTIVE_STATUS = '1'
        AND TPS.SCHOOLYEAR = '2324'
        AND TPS.SEMESTER = '1'
        AND TPS.SUBJECTID = '30364'
    GROUP BY
        TA.CLO_TEACHING_PLAN_ID
    ) CTF ON ( A.ID = CTF.EXPERIENCE_F_RPS_ID )
    LEFT JOIN (
    SELECT
        TA.CLO_TEACHING_PLAN_ID AS EXPERIENCE_O_RPS_ID,
        RTRIM(
            XMLAGG(
                XMLELEMENT ( E, REGEXP_REPLACE( TA.LEARNING_DESCRIPTION || '[' || ESTIMATION_TIME || ' Menit]' || '~', '[[:cntrl:]]', '' ) )
            ORDER BY
                TA.LEARNING_DESCRIPTION
            ).EXTRACT( '//text()' ),
            '~'
        ) EXPERIENCE_O
    FROM
        ACADEMIC.CLO_LEARNING_EXPERIENCE TA
        JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
    WHERE
        TA.LEARNING_TYPE = 'Online'
        AND TA.ACTIVE_STATUS = '1'
        AND TPS.SCHOOLYEAR = '2324'
        AND TPS.SEMESTER = '1'
        AND TPS.SUBJECTID = '30364'
    GROUP BY
        TA.CLO_TEACHING_PLAN_ID
    ) CTO ON ( A.ID = CTO.EXPERIENCE_O_RPS_ID )
WHERE
    A.SUBJECTID = '30364'
    AND A.SCHOOLYEAR = '2324'
    AND A.SEMESTER = '1'
    AND A.ACTIVE_STATUS = '1'
ORDER BY
    WEEK