SELECT
    DISTINCT
    B.SUBJECTCODE,
    B.SUBJECTNAME,
    B.STUDYPROGRAMID,
    A .CLOPLOID,
    A .PLONAME,
    A .COUNTSTATUS,
    A .CLOPLOMAPPINGID,
    CASE
        WHEN A .CLOPLOMAPPINGID IS NOT NULL THEN
                    A .ACTIVESTATUS
        WHEN A .CLOPLOMAPPINGID IS NULL THEN
                    'N'
    END AS STATUSPLOMAPPING,
    NVL (V.TOTALPLO,
    0) AS TOTALPLO
FROM
    (
    SELECT
        B.CLOPLOMAPPINGID,
        A .CLOPLOID,
        A .STUDYPROGRAMID,
        A .PLONAME,
        B.SUBJECTCODE,
        B.SCHOOLYEAR,
        B.SEMESTER,
        B.ACTIVESTATUS,
        B.COUNTSTATUS
    FROM
        ACADEMIC.CLOPLO A
    JOIN ACADEMIC.CLOPLOMAPPING B ON
        (A .CLOPLOID = B.CLOPLOID)
    WHERE
        A .CLOPLOID = '1641'
                    ) A
RIGHT JOIN (
    SELECT
        A .SUBJECTCODE,
        A .SUBJECTNAME,
        A .STUDYPROGRAMID,
        A .CURICULUMYEAR
    FROM
        ACADEMIC.SUBJECTS A
    WHERE
        A .ACTIVESTATUS = 'YA'
                ) B ON
    (
                    A .SUBJECTCODE = B.SUBJECTCODE
                )
LEFT JOIN (
    SELECT
        X.SUBJECTCODE,
        Z.STUDYPROGRAMID,
        COUNT (CLOPLOMAPPINGID) AS TOTALPLO
    FROM
        CLOPLOMAPPING X
    JOIN CLOPLO Z ON
        (X.CLOPLOID = Z.CLOPLOID)
    WHERE
        SUBJECTCODE = X.SUBJECTCODE
        AND STUDYPROGRAMID = Z.STUDYPROGRAMID
        AND X.ACTIVESTATUS = 'Y'
    GROUP BY
        X.SUBJECTCODE,
        Z.STUDYPROGRAMID
                ) V ON
    (
                    B.SUBJECTCODE = V.SUBJECTCODE
        AND B.STUDYPROGRAMID = V.STUDYPROGRAMID
                )
WHERE
    B.STUDYPROGRAMID IN(42)
    AND B.CURICULUMYEAR = '2024'
    AND B.SUBJECTCODE = 'DBK1HAB3'
ORDER BY
    1 ASC