SELECT
    DISTINCT
        A .SUBJECTCODE,
    B.SUBJECTNAME,
    D .FACULTYNAME,
    D .FACULTYID,
    C.STUDYPROGRAMNAME,
    C.STUDYPROGRAMID,
    NVL (H .TOTALCLO,
    0) AS TOTALCLO,
    NVL (A.CLOSTATUS,
    'N') AS CLOSTATUS,
    A.SCHOOLYEAR,
    A.SEMESTER
FROM
    ACADEMIC.COURSE A
JOIN SUBJECTS B ON
    (A .SUBJECTID = B.SUBJECTID)
JOIN MASTERDATA.STUDYPROGRAM C ON
    (
        B.STUDYPROGRAMID = C.STUDYPROGRAMID
        )
JOIN MASTERDATA.FACULTIES D ON
    (C.FACULTYID = D .FACULTYID)
JOIN MASTERDATA.EMPLOYEEMASTER F ON
    (
        F.LECTURERCODE = A .COURSECOORDINATOR
        OR A.UNIVERSITYCOORDINATOR = F.LECTURERCODE
        )
JOIN FRAMEWORK1. USERS G ON
    (G .USERNAME = F.EMPLOYEEID)
LEFT JOIN (
    SELECT
        SUBJECTCODE,
        SCHOOLYEAR,
        SEMESTER,
        COUNT (CLOMAPPINGID) AS TOTALCLO,
        ACADEMIC.CLOPLO.STUDYPROGRAMID
    FROM
        ACADEMIC.CLOMAPPING
    JOIN ACADEMIC.CLOCLO ON
        (ACADEMIC.CLOMAPPING.CLOCLOID = ACADEMIC.CLOCLO.CLOCLOID)
    JOIN ACADEMIC.CLOPLO ON
        (ACADEMIC.CLOCLO.CLOPLOID = ACADEMIC.CLOPLO.CLOPLOID)
    WHERE
        SCHOOLYEAR = '2324'
        AND SEMESTER = '1'
    GROUP BY
        SUBJECTCODE,
        SCHOOLYEAR,
        SEMESTER,
        ACADEMIC.CLOPLO.STUDYPROGRAMID
        ) H ON
    (
        A .SUBJECTCODE = H .SUBJECTCODE
        AND H.STUDYPROGRAMID = C.STUDYPROGRAMID
        )
WHERE
    1 = 1
    AND C.STUDYPROGRAMID IN (32)
    AND G .ACTIVATEDSTATUS = 'yes'
    AND B.ACTIVESTATUS = 'YA'
    AND A.SCHOOLYEAR = '2324'
    AND A.SEMESTER = '1'
    AND A.ACTIVATEDSTATUS = 'YA'