/* $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_defaults_v 
AS
SELECT 1 autosetup_id
      ,s.tenant_id
      ,s.cloud_api_key
      ,s.object_prefix
      ,s.twitter_username
      ,s.news_feed_url
      ,(SELECT XMLElement
                 ("value"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("value"
                     ,XMLForest
                        (r.responsibility_id "id"
                        ,r.responsibility_name "label"
                        ,'true' "deletable"
                        ,null "description"
                        )
                      )
                    )
                  ).getClobVal()
         FROM  TABLE(nvl(s.responsibility_ids, xxas_setup_pkg.eligible_responsibility_ids)) er
              ,fnd_responsibility_vl r
         WHERE er.column_value = r.responsibility_id) responsibilities
FROM   xxas_autosetup_base_v s;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_AUTOSETUP_DEFAULTS_V'
   ,p_description            => 'Applaud Autosetup Defaults');
END;
/
  
COMMIT;
EXIT;
