/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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 FORCE VIEW xxas_perf_plans_v
AS
SELECT xpp.perf_plan_id
      ,xpp.title
      ,xpp.start_date plan_start_date
      ,xpp.end_date   plan_end_date
      ,xpp.goal_rating_scale_id
      ,xpp.icon_name
      ,xpp.icon_color
      ,CASE WHEN nvl(xpp.icon_name,xpp.icon_color) IS NULL
              THEN 'N'
            ELSE 'Y'
       END use_graphical_rating
      ,xpp.status_code
      ,xpp.goal_rating_lookup_type
      ,xpp.competency_rating_lookup_type
      ,fl.meaning status_code_meaning
      ,xpp.enrollment_formula_name
      ,xpp.created_by
      ,xpp.creation_date
      ,xpp.last_update_date
      ,xpp.last_updated_by
      ,xpp.last_update_login
FROM  xxas_perf_plans xpp
     ,hr_lookups fl
WHERE  xpp.status_code = fl.lookup_code(+)
AND    fl.lookup_type = 'XXAS_PERF_PLAN_STATUS';
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_PLANS_V'
   ,p_description            => 'Applaud Performance Plan View');
END;
/
COMMIT;
EXIT;
