/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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_PERF_REVIEW_TEMPL_STEPS_V
AS
 SELECT perf_review_templ_step_id
       ,perf_review_template_id
       ,stage_code
       ,xxas_util_pkg.decode_lookup('XXAS_PERF_REVIEW_TS_STAGES',stage_code) stage_meaning
       ,sequence
       ,xxas_com_msg_pkg.get_string(title_message) title
       ,title_message
       ,step_duration_days
       ,xxas_com_msg_pkg.get_string(description_message) description
       ,description_message
       ,interaction_code
       ,xxas_util_pkg.decode_lookup('XXAS_PERF_REVIEW_TS_INTERACT',interaction_code) interaction_meaning
       ,goal_editing
       ,xxas_util_pkg.decode_lookup('XXAS_PERF_GOAL_EDITING',goal_editing) goal_edit_meaning
       ,goal_commenting
       ,xxas_util_pkg.decode_lookup('XXAS_PERF_GOAL_COMMENTING',goal_commenting) goal_comment_meaning
       ,competency_editing
       ,xxas_util_pkg.decode_lookup('XXAS_PERF_COMP_EDITING',competency_editing) comp_edit_meaning
       ,competency_commenting
       ,xxas_util_pkg.decode_lookup('XXAS_PERF_COMP_COMMENTING',competency_commenting) comp_comment_meaning
       ,questionnaire_editing
       ,xxas_util_pkg.decode_lookup('XXAS_PERF_QUEST_EDITING',questionnaire_editing) quest_edit_meaning
       ,start_perf_participant_id
       ,(SELECT COUNT(1) FROM xxas_perf_review_forms WHERE perf_review_template_id = xprts.perf_review_template_id) "TEMPLATE_USAGE"
       ,created_by             
       ,creation_date          
       ,last_update_date        
       ,last_updated_by         
       ,last_update_login
   FROM  XXAS_PERF_REVIEW_TEMPL_STEPS xprts;
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_REVIEW_TEMPL_STEPS_V'
   ,p_description            => 'Applaud Performance Review Steps');
END;
/
COMMIT;
EXIT;

