/* $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_REV_TEMPL_ST_LIST_V
AS
 SELECT 'flexible' "type",
        xprtv.sequence,
        xprtv.perf_review_template_id,
        xprtv.perf_review_templ_step_id,
        xprtv.perf_review_templ_step_id "id",
        xprtv.stage_code,
        xprtv.stage_meaning,
        xprtv.interaction_code,
        xprtv.interaction_meaning,        
        goal_editing,
        goal_edit_meaning,
        goal_commenting,
        goal_comment_meaning,
        competency_editing,
        comp_edit_meaning,
        competency_commenting,
        comp_comment_meaning,
        questionnaire_editing,
        quest_edit_meaning,        
        xprtv.start_perf_participant_id,
        decode(xprtv.interaction_code,'S',xprtv.start_perf_participant_id,NULL) "SINGLE_PARTICIPANT_ID",
        xprtv.step_duration_days,
        xprtv.title "STEP_TITLE",
        xprtv.template_usage "TEMPLATE_USAGE",
        xprtv.title "line1",
        '<font color="'
      || xxas_rslt_cache_pkg.resolve_color_name('grey')
      || '">'
      || xprtv.description
      || '</font>' "line3",
        xprtv.description "STEP_DESCRIPTION",
        cast
        (multiset
          ( SELECT XXAS_TEMPL_STEP_PARTIC(
                   xppv.perf_participant_id,
                   xprtsv.end_step_allowed_flag,
                   xxas_perf_review_templates_pkg.is_start_participant -- return type: varchar2
                           ( p_template_step_id      =>   xprtv.perf_review_templ_step_id
                            ,p_perf_participant_id   =>   xppv.perf_participant_id
                             ), --"START_FLAG"
                   (SELECT COUNT(1) "TEMPLATE_USAGE" FROM xxas_perf_review_forms WHERE perf_review_template_id = xprtv.perf_review_template_id)) 
              FROM xxas_perf_rev_ts_parti_v xprtsv,
                   xxas_perf_participants_v xppv
             WHERE xprtsv.perf_participant_id = xppv.perf_participant_id
             AND   xprtsv.perf_review_templ_step_id = xprtv.perf_review_templ_step_id
          )
          AS XXAS_TEMPL_STEP_PARTIC_TBL) participants,
        XMLElement
            ("actions"
             ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
             ,CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_PERF_TEMP_ST_EDIT',xprtv.perf_review_template_id) = 'Y' THEN
                        XMLElement("action"
                         ,XMLFOREST(xxas_com_msg_pkg.get_string('XXAS_RB_COM_EDIT') "title")
                         ,XMLFOREST('false' "bulkActionEnabled")
                         ,XMLFOREST('LINK' "operation")
                         ,XMLFOREST('edit-template-step' "pageId")
                         ,XMLFOREST(xprtv.perf_review_template_id||'_'||xprtv.perf_review_templ_step_id "objectId")
                         --,XMLFOREST('modal' "target")
                         ,XMLElement ("meta"
                                      ,XMLElement("options"
                                                  ,XMLFOREST('positive' "color")
                                                  ,XMLFOREST('med' "weight")
                                                 )
                                     )
                        ) END
             ,CASE WHEN xxas_com_sec_pkg.test_instance('XXAS_DT_TEMPL_STEP_DEL',xprtv.perf_review_template_id) = 'Y' THEN
                        XMLElement("action"
                         ,XMLFOREST(xxas_com_msg_pkg.get_string('XXAS_RB_COM_DEL') "title")
                         ,XMLFOREST('false' "bulkActionEnabled")
                         ,XMLFOREST('DELETE' "operation")
                         ,XMLFOREST(xxas_com_rest_util_pkg.uri_from_path('performance-templates')||'/'||xprtv.perf_review_template_id||'/steps/'||xprtv.perf_review_templ_step_id "href")
                         ,XMLElement ("meta"
                                      ,XMLElement("options"
                                                  ,XMLFOREST('assertive' "color")
                                                  ,XMLFOREST('med' "weight")
                                                 )
                                     )
                        ) END
            ) "linked"
   FROM xxas_perf_review_templ_steps_v xprtv
 ORDER BY xprtv.sequence asc;



BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_REV_TEMPL_ST_LIST_V'
   ,p_description            => 'Applaud Performance Review Template Steps List');
END;
/

COMMIT;
EXIT;

