/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

CREATE OR REPLACE VIEW xxas_cand_olm_assessment_v
AS
SELECT per.employee_number
      ,per.person_id
      ,per.full_name
      ,ot.test_id
      ,olo.name test
      ,ots.test_section_id
      ,ots.name section
      ,oq.question_id
      ,oq.text question
      ,otq.sequence question_sequence
      --,ouq.user_test_question_id
      --,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.user_test_response_id
      --,our.answered_flag emp_answered_flag
      ,DECODE(ort.type_flag, 'F', our.text, 'B', our.text, 'X', our.text, 'T', DECODE(ouq.skipped_flag, 'Y', NULL, DECODE(our.answered_flag, 'Y', orv.text, 'N', 
                                                         (SELECT orv1.text 
                                                          FROM   ota_response_values orv1
                                                                ,ota_utest_responses our1 
                                                          WHERE our.response_type_id       = orv1.response_type_id 
                                                          AND   our1.response_type_id      = orv1.response_type_id 
                                                          AND   our1.answered_flag         = 'Y'
                                                          AND   orv1.response_value_id     = our1.response_value_id
                                                          AND   our1.user_test_question_id = our.user_test_question_id))), 'S', 
              DECODE(ouq.skipped_flag, 'Y', NULL, DECODE(our.answered_flag, 'Y', orv.text, 'N', 
                                                         (SELECT orv1.text 
                                                          FROM   ota_response_values orv1
                                                                ,ota_utest_responses our1 
                                                          WHERE our.response_type_id       = orv1.response_type_id 
                                                          AND   our1.response_type_id      = orv1.response_type_id 
                                                          AND   our1.answered_flag         = 'Y'
                                                          AND   orv1.response_value_id     = our1.response_value_id
                                                          AND   our1.user_test_question_id = our.user_test_question_id))), NULL) answer
      ,oa.attempt_id
      ,to_char(oa.timestamp, 'YYYY-MM-DD HH:MI:SS') olm_assess_start_ts
FROM   per_all_people_f per
      ,ota_tests ot
      ,ota_learning_objects olo
      ,ota_test_sections ots
      ,ota_test_questions otq
      ,ota_questions oq
      ,ota_response_types ort
      ,ota_attempts oa
      ,ota_response_values orv
      ,ota_utest_questions ouq
      ,ota_utest_responses our
 WHERE trunc(sysdate) BETWEEN per.effective_start_date
                      AND     per.effective_end_date
       AND olo.test_id = ot.test_id
       AND ots.test_id = ot.test_id
       AND otq.section_id = ots.test_section_id
       AND oq.question_id = otq.question_id
       AND ort.question_id = oq.question_id
       AND oa.user_id = per.person_id
       AND oa.attempt_id = ouq.attempt_id
       AND orv.response_type_id = ort.response_type_id
       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 oa.attempt_id = (SELECT MAX(ota.attempt_id) 
                            FROM   ota_attempts ota
                            WHERE  ota.user_id    = per.person_id
                            AND    ota.test_id    = ot.test_id
                            AND    ota.attempt_id > (SELECT NVL(max(irc.attempt_id),0)
                                                     FROM   irc_assignment_details_f irc
                                                           ,per_all_assignments_f    paaf
                                                     WHERE  /*ota.attempt_id     = irc.attempt_id
                                                     AND    */per.person_id      = paaf.person_id
                                                     AND    paaf.assignment_id = irc.assignment_id
                                                     AND    trunc(sysdate) BETWEEN irc.effective_start_date
                                                                           AND     irc.effective_end_date
                                                     AND    trunc(sysdate) BETWEEN paaf.effective_start_date
                                                                     AND     paaf.effective_end_date
                                                     AND    EXISTS (SELECT pav.vacancy_id
                                                                    FROM   per_all_vacancies pav
                                                                    WHERE  paaf.vacancy_id   = pav.vacancy_id 
                                                                    AND    pav.assessment_id = ot.test_id)
                                                    )
                           )
      AND decode(ort.type_flag, 'S', decode(our.correct_flag, 'Y', 'Y', 'N'), 'T', decode(our.correct_flag, 'Y', 'Y', 'N'), 'Y') = 'Y'
UNION
SELECT null
      ,papf.person_id
      ,null
      ,ots.test_id
      ,null
      ,null
      ,null
      ,oq.question_id
      ,oq.text question
      ,otq.sequence question_sequence
      --,null
      --,null
      ,null
      ,null
      --,null
      --,null
      --,null
      --,null
      ,null answer
      ,null attempt_id
      ,null
  FROM  ota_test_sections ots
       ,ota_test_questions otq
       ,ota_questions oq
       ,ota_response_types ort
       ,per_all_people_f papf
  WHERE trunc(sysdate) BETWEEN nvl(ots.start_date_active, trunc(sysdate)) 
                       AND     nvl(ots.end_date_active, trunc(sysdate))
  AND   ots.test_id = otq.test_id
  AND   ots.test_section_id = otq.section_id
  AND   otq.question_id = oq.question_id
  AND   trunc(sysdate) BETWEEN nvl(oq.start_date_active, trunc(sysdate)) 
                       AND     nvl(oq.end_date_active, trunc(sysdate))
  AND   oq.question_id = ort.question_id
  AND   trunc(sysdate) BETWEEN papf.effective_start_date 
                       AND     papf.effective_end_date
  AND   (((SELECT NVL(max(oa.attempt_id),0)
          FROM   ota_attempts oa
          WHERE  oa.user_id     = papf.person_id
          AND    ots.test_id    = oa.test_id) = (SELECT NVL(max(oa.attempt_id),0)
                                                 FROM   per_all_assignments_f    paaf
                                                       ,irc_assignment_details_f irc
                                                       /*,ota_utest_questions      ouq*/
                                                       ,ota_attempts             oa
                                                 WHERE  papf.person_id     = paaf.person_id
                                                 AND    trunc(sysdate) BETWEEN paaf.effective_start_date
                                                                       AND     paaf.effective_end_date
                                                 AND    paaf.assignment_id = irc.assignment_id
                                                 AND    trunc(sysdate) BETWEEN irc.effective_start_date
                                                                       AND     irc.effective_end_date
                                                 AND    oa.user_id     = papf.person_id
                                                 AND    ots.test_id    = oa.test_id
                                                 AND    oa.attempt_id  = irc.attempt_id
                                                 AND    EXISTS (SELECT pav.vacancy_id
                                                                FROM   per_all_vacancies pav
                                                                WHERE  paaf.vacancy_id   = pav.vacancy_id 
                                                                AND    pav.assessment_id = ots.test_id)
                                                 /*AND    oa.attempt_id  = ouq.attempt_id
                                                 AND    oq.question_id = ouq.object_id*/))
          OR (EXISTS (SELECT 1
                      FROM   ota_attempts oa
                      WHERE  oa.user_id    = papf.person_id
                      AND    ots.test_id   = oa.test_id
                      AND    oa.attempt_id = (SELECT MAX(ota.attempt_id) 
                                              FROM   ota_attempts ota
                                              WHERE  ota.user_id    = papf.person_id
                                              AND    ota.test_id    = ots.test_id
                                              AND    ota.attempt_id > (SELECT NVL(max(irc.attempt_id),0)
                                                                       FROM   irc_assignment_details_f irc
                                                                             ,per_all_assignments_f    paaf
                                                                       WHERE  /*ota.attempt_id     = irc.attempt_id
                                                                        AND    */papf.person_id      = paaf.person_id
                                                                       AND    paaf.assignment_id = irc.assignment_id
                                                                       AND    trunc(sysdate) BETWEEN irc.effective_start_date
                                                                                             AND     irc.effective_end_date
                                                                       AND    trunc(sysdate) BETWEEN paaf.effective_start_date
                                                                                             AND     paaf.effective_end_date
                                                                       AND    EXISTS (SELECT pav.vacancy_id
                                                                                      FROM   per_all_vacancies pav
                                                                                      WHERE  paaf.vacancy_id   = pav.vacancy_id 
                                                                                      AND    pav.assessment_id = ots.test_id)
                                                                      )
                                             )
                      AND    NOT EXISTS (SELECT 1 
                                         FROM   ota_utest_questions ouq
                                         WHERE  oa.attempt_id        = ouq.attempt_id
                                         AND    otq.question_id      = ouq.object_id
                                         AND    ouq.object_type_flag = 'Q'
                                        )
                     )
             )
         )
ORDER BY attempt_id DESC, question_sequence, question_id;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_CAND_OLM_ASSESSMENT_V'
   ,p_description            => 'Applaud Candidate OLM Assessment Details');
END;
/

COMMIT;
EXIT;