Advertisement
ma39isy

Untitled

Sep 18th, 2023
1,322
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. SELECT
  2.     A.ID AS IDTEMP,
  3.     A.ID,
  4.     A.WEEK,
  5.     A.MEET,
  6.     C.CLONUMBER,
  7.     CTA.CLOASSESSMENTTOOLSNAME,
  8.     CTM.METHOD_NAME,
  9.     CTF.EXPERIENCE_F AS FACETOFACE,
  10.     CTO.EXPERIENCE_O AS ON_LINE,
  11.     A.APPROVAL_STATUS
  12. FROM
  13.     ACADEMIC.CLO_TEACHING_PLANS A
  14.     LEFT JOIN (
  15.     SELECT
  16.         TA.CLO_TEACHING_PLAN_ID AS CLO_RPS_ID,
  17.         RTRIM(
  18.             XMLAGG( XMLELEMENT ( E, 'CLO ' || TB.CLONUMBER || ',' ) ORDER BY TB.CLONUMBER ).EXTRACT( '//text()' ),
  19.             ','
  20.         ) CLONUMBER
  21.     FROM
  22.         ACADEMIC.CLO_TEACHING_PLANS_MAPPING TA
  23.         JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
  24.         JOIN ACADEMIC.CLOCLO TB ON ( TA.CLOCLOID = TB.CLOCLOID )
  25.     WHERE
  26.         TA.ACTIVE_STATUS = '1'
  27.         AND TPS.SCHOOLYEAR = '2324'
  28.         AND TPS.SEMESTER = '1'
  29.         AND TPS.SUBJECTID = '30364'
  30.     GROUP BY
  31.         TA.CLO_TEACHING_PLAN_ID
  32.     ) C ON ( A.ID = CLO_RPS_ID )
  33.     LEFT JOIN (
  34.     SELECT
  35.         TA.CLO_TEACHING_PLAN_ID AS ASSMENTTOOLS_RPS_ID,
  36.         RTRIM( XMLAGG( XMLELEMENT ( E, TB.CLOASSESSMENTTOOLSNAME || ',' ) ORDER BY TB.CLOASSESSMENTTOOLSNAME ).EXTRACT( '//text()' ), ',' ) CLOASSESSMENTTOOLSNAME
  37.     FROM
  38.         ACADEMIC.CLO_TEACHING_ASSESSMENTOOLS TA
  39.         JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
  40.         JOIN ACADEMIC.CLOASSESSMENTTOOLS TB ON ( TB.CLOASSESSMENTTOOLSID = TA.CLOASSESSMENTTOOLSID )
  41.     WHERE
  42.         TA.ACTIVE_STATUS = '1'
  43.         AND TPS.SCHOOLYEAR = '2324'
  44.         AND TPS.SEMESTER = '1'
  45.         AND TPS.SUBJECTID = '30364'
  46.     GROUP BY
  47.         TA.CLO_TEACHING_PLAN_ID
  48.     ) CTA ON ( A.ID = ASSMENTTOOLS_RPS_ID )
  49.     LEFT JOIN (
  50.     SELECT
  51.         TA.CLO_TEACHING_PLAN_ID AS METHODE_RPS_ID,
  52.         RTRIM( XMLAGG( XMLELEMENT ( E, TA.METHOD_NAME || ',' ) ORDER BY TA.METHOD_NAME ).EXTRACT( '//text()' ), ',' ) METHOD_NAME
  53.     FROM
  54.         ACADEMIC.CLO_TEACHING_METHOD TA
  55.         JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
  56.     WHERE
  57.         TA.ACTIVE_STATUS = '1'
  58.         AND TPS.SCHOOLYEAR = '2324'
  59.         AND TPS.SEMESTER = '1'
  60.         AND TPS.SUBJECTID = '30364'
  61.     GROUP BY
  62.         TA.CLO_TEACHING_PLAN_ID
  63.     ) CTM ON ( A.ID = METHODE_RPS_ID )
  64.     LEFT JOIN (
  65.     SELECT
  66.         TA.CLO_TEACHING_PLAN_ID AS EXPERIENCE_F_RPS_ID,
  67.         RTRIM(
  68.             XMLAGG( XMLELEMENT ( E, TA.LEARNING_DESCRIPTION || '[' || ESTIMATION_TIME || ' Menit]' || '~' ) ORDER BY TA.LEARNING_DESCRIPTION ).EXTRACT( '//text()' ),
  69.             '~'
  70.         ) EXPERIENCE_F
  71.     FROM
  72.         ACADEMIC.CLO_LEARNING_EXPERIENCE TA
  73.         JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
  74.     WHERE
  75.         TA.LEARNING_TYPE = 'Face to face'
  76.         AND TA.ACTIVE_STATUS = '1'
  77.         AND TPS.SCHOOLYEAR = '2324'
  78.         AND TPS.SEMESTER = '1'
  79.         AND TPS.SUBJECTID = '30364'
  80.     GROUP BY
  81.         TA.CLO_TEACHING_PLAN_ID
  82.     ) CTF ON ( A.ID = CTF.EXPERIENCE_F_RPS_ID )
  83.     LEFT JOIN (
  84.     SELECT
  85.         TA.CLO_TEACHING_PLAN_ID AS EXPERIENCE_O_RPS_ID,
  86.         RTRIM(
  87.             XMLAGG(
  88.                 XMLELEMENT ( E, REGEXP_REPLACE( TA.LEARNING_DESCRIPTION || '[' || ESTIMATION_TIME || ' Menit]' || '~', '[[:cntrl:]]', '' ) )
  89.             ORDER BY
  90.                 TA.LEARNING_DESCRIPTION
  91.             ).EXTRACT( '//text()' ),
  92.             '~'
  93.         ) EXPERIENCE_O
  94.     FROM
  95.         ACADEMIC.CLO_LEARNING_EXPERIENCE TA
  96.         JOIN ACADEMIC.CLO_TEACHING_PLANS TPS ON ( TA.CLO_TEACHING_PLAN_ID = TPS.ID )
  97.     WHERE
  98.         TA.LEARNING_TYPE = 'Online'
  99.         AND TA.ACTIVE_STATUS = '1'
  100.         AND TPS.SCHOOLYEAR = '2324'
  101.         AND TPS.SEMESTER = '1'
  102.         AND TPS.SUBJECTID = '30364'
  103.     GROUP BY
  104.         TA.CLO_TEACHING_PLAN_ID
  105.     ) CTO ON ( A.ID = CTO.EXPERIENCE_O_RPS_ID )
  106. WHERE
  107.     A.SUBJECTID = '30364'
  108.     AND A.SCHOOLYEAR = '2324'
  109.     AND A.SEMESTER = '1'
  110.     AND A.ACTIVE_STATUS = '1'
  111. ORDER BY
  112.     WEEK
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement