/* $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_PLAN_LIST_V
AS
SELECT  'flexible' "type"
       ,xpp.perf_plan_id "id"
       ,xpp.perf_plan_id
       ,xpp.status_code
       ,xpp.plan_start_date
       ,xpp.creation_date
       ,xpp.last_update_date
       ,decode
         (xpp.status_code
         ,'P', 'tick'
         ,'U', 'edit'
         ,'W', 'timer') "icon"
       ,decode
         (xpp.status_code
         ,'P', xxas_rslt_cache_pkg.resolve_color_name('green')
         ,'U', xxas_rslt_cache_pkg.resolve_color_name('grey')
         ,'W', xxas_rslt_cache_pkg.resolve_color_name('orange')) "statusColor"
       ,xpp.status_code_meaning "statusText"
       ,'<font color="'
     || decode
         (xpp.status_code
         ,'P', xxas_rslt_cache_pkg.resolve_color_name('green')
         ,'U', xxas_rslt_cache_pkg.resolve_color_name('grey')
         ,'W', xxas_rslt_cache_pkg.resolve_color_name('orange'))
     || '">'
     || xpp.status_code_meaning
     || '</font>'
     || decode(xpp.status_code
              ,'P'
              ,'<font color="'
             || xxas_rslt_cache_pkg.resolve_color_name('grey')
             || '"> - '
             || (SELECT count(1) || ' enrolled, '
                 FROM   xxas_perf_plan_enrollments a
                 WHERE  a.perf_plan_id = xpp.perf_plan_id)
             || (SELECT count(1) || ' reviews'
                 FROM   xxas_perf_plan_reviews b
                 WHERE  b.perf_plan_id = xpp.perf_plan_id)
             || '</font>'
              ,null) "line2"
       ,xpp.title "line1"
       ,to_char(xpp.plan_start_date,'Fmdd Mon YYYY')
     || ' to '
     || to_char(xpp.plan_end_date,'Fmdd Mon YYYY') "line3"
       ,'view-perf-plan' "pageId"
       ,xpp.perf_plan_id "objectId"
  FROM xxas_perf_plans_v xpp
  ORDER BY xpp.plan_start_date DESC;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_PLAN_LIST_V'
   ,p_description            => 'Applaud Performance Plans List');
END;
/
COMMIT;
EXIT;