/* $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_SWI_MGR_LIST_V
AS
SELECT  'flexible' "type"
       ,xpp.perf_plan_id "id"
       ,papf.person_id
       ,xpp.perf_plan_id
       ,xpp.plan_start_date
       ,CASE WHEN xpp.plan_start_date > trunc(sysdate) THEN
                  'calendar'
             WHEN xpp.plan_end_date < trunc(sysdate) THEN
                  'tick'
             ELSE 'spinner'
        END "icon"
       ,CASE WHEN xpp.plan_start_date > trunc(sysdate) THEN
                  xxas_rslt_cache_pkg.resolve_color_name('grey')
             WHEN xpp.plan_end_date < trunc(sysdate) THEN
                  xxas_rslt_cache_pkg.resolve_color_name('green')
             ELSE xxas_rslt_cache_pkg.resolve_color_name('orange')
        END "statusColor"
       ,xpp.title "line1"
       ,'<font color="'
     || xxas_rslt_cache_pkg.resolve_color_name('grey')
     || '">'
     || to_char(xpp.plan_start_date,'Fmdd Mon YYYY')
     || ' to '
     || to_char(xpp.plan_end_date,'Fmdd Mon YYYY')
     || '</font>' "line3"
       ,'performance-dashboard' "pageId"
       ,papf.person_id || '_' || xpp.perf_plan_id "objectId"
   FROM xxas_perf_plans_v xpp
       ,per_all_people_f papf
  WHERE EXISTS 
         (SELECT column_value 
            FROM xxas_perf_plan_enrollments_v xppe,
                TABLE(xxas_com_hier_pkg.drs_for_per_tbl(papf.person_id,trunc(sysdate))) drs
          WHERE xppe.person_id = drs.column_value
            AND xppe.perf_plan_id = xpp.perf_plan_id)
    AND trunc(sysdate) between papf.effective_start_date and papf.effective_end_date;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_PLAN_SWI_MGR_LIST_V'
   ,p_description            => 'Applaud Performance Plan Switch List Manager');
END;
/

COMMIT;
EXIT;