/* $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_DET_V
AS
  SELECT  xpp.*
       ,(SELECT XMLAgg
                 (XMLElement
                   ("XXAS_PREVIOUS_EMPLOYER"
                   ,XMLForest
                     (xppr.perf_plan_review_id
                     ,xppr.perf_plan_id
                     ,xppr.perf_review_template_id
                     ,xppr.start_date
                     ,xppr.due_date
                     )
                   ) ORDER BY xppr.start_date)
       FROM   xxas_perf_plan_reviews xppr
             ,xxas_perf_review_templates xprt
       WHERE  xppr.perf_review_template_id = xprt.perf_review_template_id
       AND    xppr.perf_plan_id = xpp.perf_plan_id
       ) reviews
  FROM xxas_perf_plans_v xpp
  ORDER BY xpp.plan_start_date;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_PERF_PLAN_DET_V'
   ,p_description            => 'Applaud Performance Plan Detail');
END;
/
COMMIT;
EXIT;