/* $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_autosetup_perf_base_v 
AS
SELECT ov.object_id autosetup_id
      ,extractvalue(ov.object_values, '/v/status') status
      ,nvl(extractvalue(ov.object_values, '/v/essRole'), 'UMX|XXAS_ESS') ess_role
      ,nvl(extractvalue(ov.object_values, '/v/mssRole'), 'UMX|XXAS_MSS') mss_role
FROM   xxas_object_values ov
WHERE  ov.object_type = 'AUTOSETUP'
AND    ov.object_id = 2
UNION ALL
SELECT 2              autosetup_id
      ,'D'            status
      ,'UMX|XXAS_ESS' ess_role
      ,'UMX|XXAS_MSS' mss_role
FROM   dual
WHERE NOT EXISTS
  (SELECT 'exists'
   FROM   xxas_object_values ov2
   WHERE  ov2.object_type = 'AUTOSETUP'
   AND    ov2.object_id = 2);
  
COMMIT;
EXIT;
