/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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_per_assessments_v 
AS
select ota.attempt_id
,ota.question_id
,ota.question_text question
,case when ota.answered_flag = 'Y' and ota.response_type <> 'T'
  THEN ota.response_value_text
  when ota.answered_flag = 'Y' and ota.response_type = 'T'
  THEN decode(ota.response_value_text, 'T', 'True'
                                     , 'F', 'False'
                                     , null)
  when ota.answered_flag = 'N' and ota.response_type = 'F'
  then ota.user_response_text
  end user_response
from OTA_UTEST_QUESTIONS_V ota
where (ota.answered_flag = 'Y' or (ota.answered_flag='N' and ota.response_type = 'F'));

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_per_assessments_v'
   ,p_description            => 'Applaud Person Applicant Assessment');
END;
/
  
COMMIT;
EXIT;