Employee attempted Questions and analysis of the same in Detail

SELECT per.employee_number,

       per.person_id,

       per.full_name,

       paaf.business_group_id,

       oe.event_id,

       oe.title class,

       oav.version_name course,

       ocu.category_usage_id,

       ocu.category,

       ot.test_id,

       olo.name test,

       ots.test_section_id,

       ots.name section,

       oqb.name question_bank,

       oq.question_id,

       oq.text question,

       ort.response_type_id,

       ort.type_flag response_type,

       DECODE (ort.type_flag,

               'F', 'Fill in the Blanks Text',

               'B', 'Fill in the Blanks Numeric',

               'T', 'True or False',

               'S', 'Single Choice',

               'M', 'Multiple Choice',

               'X', 'Free Text')

          response_type_desc,

       orv.response_value_id,

       orv.text response_suggested_values,

       our.answered_flag emp_answered_flag,

       our.text response_by_employee

  FROM per_all_people_f per,

       per_all_assignments_f paaf,

       ota_delegate_bookings odb,

       ota_events oe,

       ota_activity_versions oav,

       ota_act_cat_inclusions oaci,

       ota_category_usages ocu,

       ota_evaluations oev,

       ota_tests ot,

       ota_learning_objects olo,

       ota_test_sections ots,

       ota_test_questions otq,

       ota_question_banks oqb,

       ota_questions oq,

       ota_response_types ort,

       ota_attempts oa,

       ota_response_values orv,

       ota_utest_questions ouq,

       ota_utest_responses our

 WHERE     per.person_id = paaf.person_id

       AND paaf.assignment_type = 'E'

       AND paaf.primary_flag = 'Y'

       AND paaf.assignment_status_type_id IN (1, 2)

       AND SYSDATE BETWEEN per.effective_start_date

                       AND per.effective_end_date

       AND SYSDATE BETWEEN paaf.effective_start_date

                       AND paaf.effective_end_date

       AND odb.delegate_person_id = per.person_id --> Delegate & People Connection

       AND oe.event_id = odb.event_id --> Delegate & Event (Class) Connection

       AND oe.activity_version_id = oav.activity_version_id --> Course & Event (Class)Connection

       AND oaci.activity_version_id = oav.activity_version_id --> Intermediate Table to Category Usage and Course (ota_act_cat_inclusions)

       AND ocu.category_usage_id = oaci.category_usage_id

       AND oev.object_id = oe.event_id --> Evaluation & Event(Class) Connection

       AND ot.test_id = oev.evaluation_id --> Evaluation & Test Connection

       AND olo.test_id = ot.test_id --> Learning Object & Test Connection

       AND ots.test_id = ot.test_id  --> Test to Section Connection

       AND otq.section_id = ots.test_section_id --> Test Questions Added Under Section

       AND otq.question_bank_id = oqb.question_bank_id --> Test Questions Under Question Bank

       AND oq.question_id = otq.question_id --> Link Between Questions and Questions Added Under Section

       AND ort.question_id = oq.question_id  --> Question Type & Question Connection

       AND oa.user_id = per.person_id --> Attempts & Person Link

       AND oa.attempt_id = ouq.attempt_id --> Attempts and User Questions Link

       AND oa.event_id = oe.event_id --> Attempts & Event (Class) Connection

       AND orv.response_type_id = ort.response_type_id --> Response Values  (Suggested)

       AND oq.question_id = ouq.object_id

       AND our.user_test_question_id = ouq.user_test_question_id

       AND orv.response_value_id = our.response_value_id

       AND paaf.business_group_id =

              NVL (:l_business_group_id, paaf.business_group_id)

       AND ocu.category_usage_id =

              NVL (:l_category_usage_id, ocu.category_usage_id)

       AND oe.event_id = NVL (:l_event_id, oe.event_id)

       AND oq.question_id = NVL (:l_question_id, oq.question_id)

       AND per.person_id = NVL (:l_person_id, per.person_id)