1. SELECT
  2.     DISTINCT
  3.     B.SUBJECTCODE,
  4.     B.SUBJECTNAME,
  5.     B.STUDYPROGRAMID,
  6.     A .CLOPLOID,
  7.     A .PLONAME,
  8.     A .COUNTSTATUS,
  9.     A .CLOPLOMAPPINGID,
  10.     CASE
  11.         WHEN A .CLOPLOMAPPINGID IS NOT NULL THEN
  12.                     A .ACTIVESTATUS
  13.         WHEN A .CLOPLOMAPPINGID IS NULL THEN
  14.                     'N'
  15.     END AS STATUSPLOMAPPING,
  16.     NVL (V.TOTALPLO,
  17.     0) AS TOTALPLO
  18. FROM
  19.     (
  20.     SELECT
  21.         B.CLOPLOMAPPINGID,
  22.         A .CLOPLOID,
  23.         A .STUDYPROGRAMID,
  24.         A .PLONAME,
  25.         B.SUBJECTCODE,
  26.         B.SCHOOLYEAR,
  27.         B.SEMESTER,
  28.         B.ACTIVESTATUS,
  29.         B.COUNTSTATUS
  30.     FROM
  31.         ACADEMIC.CLOPLO A
  32.     JOIN ACADEMIC.CLOPLOMAPPING B ON
  33.         (A .CLOPLOID = B.CLOPLOID)
  34.     WHERE
  35.         A .CLOPLOID = '1641'
  36.                     ) A
  37. RIGHT JOIN (
  38.     SELECT
  39.         A .SUBJECTCODE,
  40.         A .SUBJECTNAME,
  41.         A .STUDYPROGRAMID,
  42.         A .CURICULUMYEAR
  43.     FROM
  44.         ACADEMIC.SUBJECTS A
  45.     WHERE
  46.         A .ACTIVESTATUS = 'YA'
  47.                 ) B ON
  48.     (
  49.                     A .SUBJECTCODE = B.SUBJECTCODE
  50.                 )
  51. LEFT JOIN (
  52.     SELECT
  53.         X.SUBJECTCODE,
  54.         Z.STUDYPROGRAMID,
  55.         COUNT (CLOPLOMAPPINGID) AS TOTALPLO
  56.     FROM
  57.         CLOPLOMAPPING X
  58.     JOIN CLOPLO Z ON
  59.         (X.CLOPLOID = Z.CLOPLOID)
  60.     WHERE
  61.         SUBJECTCODE = X.SUBJECTCODE
  62.         AND STUDYPROGRAMID = Z.STUDYPROGRAMID
  63.         AND X.ACTIVESTATUS = 'Y'
  64.     GROUP BY
  65.         X.SUBJECTCODE,
  66.         Z.STUDYPROGRAMID
  67.                 ) V ON
  68.     (
  69.                     B.SUBJECTCODE = V.SUBJECTCODE
  70.         AND B.STUDYPROGRAMID = V.STUDYPROGRAMID
  71.                 )
  72. WHERE
  73.     B.STUDYPROGRAMID IN(42)
  74.     AND B.CURICULUMYEAR = '2024'
  75.     AND B.SUBJECTCODE = 'DBK1HAB3'
  76. ORDER BY
  77.     1 ASC