/* $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_base_v 
AS
SELECT ov.object_id autosetup_id
      ,extractvalue(ov.object_values, '/v/status') status
      ,extractvalue(ov.object_values, '/v/manualOrAutomatic') manual_or_automatic
      ,extractvalue(ov.object_values, '/v/tenantId') tenant_id
      ,extractvalue(ov.object_values, '/v/cloudApiKey') cloud_api_key
      ,extractvalue(ov.object_values, '/v/objectPrefix') object_prefix
      ,extractvalue(ov.object_values, '/v/twitterUsername') twitter_username
      ,extractvalue(ov.object_values, '/v/newsFeedUrl') news_feed_url
      ,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 = 1
UNION ALL
SELECT 1    autosetup_id
      ,'D'  status
      ,'A'  manual_or_automatic
      ,fnd_profile.value('XXAS_COM_CLOUD_TENANT_ID') tenant_id
      ,fnd_profile.value('XXAS_COM_CLOUD_API_KEY') cloud_api_key
      ,fnd_profile.value('XXAS_CUSTOM_META_PREFIX') object_prefix
      ,null twitter_username
      ,null newsfeed_url
      ,null responsibility_ids
FROM   dual
WHERE NOT EXISTS
  (SELECT 'exists'
   FROM   xxas_object_values ov2
   WHERE  ov2.object_type = 'AUTOSETUP'
   AND    ov2.object_id = 1);
  
COMMIT;
EXIT;
