OTA OLM Course Evaluation

 select
response_stat.activity_name COURSE_NAME,
response_stat.class_name CLASS_NAME,
sec.name SECTION_NAME,
sec.test_section_id SECTION_ID,
quest.text QUESTION_TEXT,
quest.question_id,
resptype.type_flag TypeFlag,
respvalue.correct_flag CorrectFlag,
respvalue.text RESPONSE_ANSWER,
response_stat.per_response NUMBER_ANSWERED,
respvalue.response_value_id,
resptype.response_type_id
from
ota_tests test,
ota_test_sections sec,
ota_test_questions tq,
ota_questions quest,
ota_response_types resptype,
ota_response_values respvalue,
 (select sum(decode(utr.answered_flag,'Y',1,0)) per_response,
utr.response_value_id responsevalueid,
utr.question_id questionid,
evt_tl.title class_name,
avt.version_name activity_name
from ota_utest_questions utq,
ota_utest_responses utr,
ota_attempts att,
ota_events evt,
ota_activity_versions avt,
ota_events_tl evt_tl
where utq.object_type_flag= 'Q' and
utq.attempt_id = att.attempt_id and
att.time >0 and
(att.score >=0 or att.score = -1000) and
att.attempt_status = 'C' and
utq.user_test_question_id = utr.user_test_question_id
and (
     (:P_ACTIVITY_ID is not null and :P_EVENT_ID is not null
       and avt.activity_Version_id = :P_ACTIVITY_ID
       and evt.event_id = :P_EVENT_ID
       and att.test_id = :TEST_ID or att.act_eval_id = :TEST_ID
       and att.event_id = :ATT_EVENT_ID
     )
     OR
     (:P_ACTIVITY_ID is not null and :P_EVENT_ID is null
      and avt.activity_version_id = :P_ACTIVITY_ID
      and att.act_eval_id = :TEST_ID
      and att.event_id = :ATT_EVENT_ID
     )
     OR
     (:P_ACTIVITY_ID is null and :P_EVENT_ID is not null
      and evt.event_id = :P_EVENT_ID
      and att.test_id = :TEST_ID or att.act_eval_id = :TEST_ID
      and att.event_id = :ATT_EVENT_ID
     )
     OR
     (:P_ACTIVITY_ID is null and :P_EVENT_ID is null
     --and att.test_id = :TEST_ID or att.ACT_EVAL_ID = :TEST_ID
     and (  (att.test_id = :TEST_ID and :TYPE= 'CLASS')
         or (att.ACT_EVAL_ID = :TEST_ID and :TYPE = 'COURSE'))
     and att.event_id = :ATT_EVENT_ID
     )
    )
and att.event_id = evt.event_id
and ota_admin_access_util.admin_can_access_object('CL',evt.event_id)='Y'
and evt.activity_version_id = avt.activity_version_id
and evt_tl.event_id = evt.event_id
and evt_tl.language = Userenv('LANG')
and ( (:P_FROM_DATE is null)
              or
      (trunc(att.creation_date) >= trunc(:P_FROM_DATE))
     )
and ( (:P_TO_DATE is null)
              or
      (trunc(att.creation_date) <= trunc(:P_TO_DATE))
     )
group by utr.response_value_id,
utr.question_id,
utr.correct_flag,
evt_tl.title,
avt.version_name)response_stat
where
response_stat.questionid = quest.question_id
and response_stat.responsevalueid = respvalue.response_value_id
and test.test_id = sec.test_id
and test.test_id = tq.test_id
and sec.TEST_SECTION_ID = tq.section_id
and tq.question_id = quest.question_id
and tq.question_id = resptype.question_id
and (resptype.type_flag = 'M' or
     resptype.type_flag = 'S' or
     resptype.type_flag = 'T')
and resptype.response_type_id = respvalue.response_type_id
and (
     (:P_ACTIVITY_ID is not null and :P_EVENT_ID is not null
      and test.test_id = :TEST_ID
     )
     OR
     (:P_ACTIVITY_ID is not null and :P_EVENT_ID is null
       and test.test_id = :TEST_ID
    )
     OR
     (:P_ACTIVITY_ID is null and :P_EVENT_ID is not null
      and test.test_id = :TEST_ID
     )
     OR
     (:P_ACTIVITY_ID is null and :P_EVENT_ID is null
      and test.test_id = :TEST_ID
     )
    )
group by
response_stat.activity_name,
response_stat.class_name,
sec.name,
sec.test_section_id,
quest.text,
quest.question_id,
resptype.type_flag,
respvalue.correct_flag,
respvalue.text,
response_stat.per_response,
respvalue.response_value_id,
resptype.response_type_id
order by
response_stat.activity_name,
response_stat.class_name,
sec.name,
quest.text,
respvalue.text