/* $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_time_abs_autosetup_base_v 
AS
SELECT 1 autosetup_id
      ,ov.object_id ta_autosetup_id
      ,extractvalue(ov.object_values, '/v/status') status
      ,extractvalue(ov.object_values, '/v/manualOrAutomatic') manual_or_automatic
      ,CAST
         (MULTISET
           (SELECT responsibility_id
            FROM   XMLTable
                     ('/v/responsibilities/*'
                   PASSING ov.object_values
                   COLUMNS responsibility_id NUMBER path '.')
           ) AS xxas_com_num_table) responsibility_ids
FROM   xxas_object_values ov
WHERE  ov.object_type = 'AUTOSETUP'
AND    ov.object_id = 3
UNION ALL
SELECT 1 autosetup_id
      ,3 ta_autosetup_id
      ,'D'  status
      ,'A'  manual_or_automatic
      ,null responsibility_ids
FROM   dual
WHERE NOT EXISTS
  (SELECT 'exists'
   FROM   xxas_object_values ov2
   WHERE  ov2.object_type = 'AUTOSETUP'
   AND    ov2.object_id = 3);
  
COMMIT;
EXIT;
