/* $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_rest_methods_v
as
  SELECT    r.service_name
           ,m.method_name
           ,m.description
           ,m.method
           ,xxas_com_rest_util_pkg.remove_fwd_trailing_slash
             (extractvalue
               (r.descriptor, '/ServiceDescriptor/@path'))
             ||
             decode
               (m.path, null, null
               ,'/' || xxas_com_rest_util_pkg.remove_fwd_trailing_slash(m.path)) path_template
           ,decode
               (m.path, null, null
               ,xxas_com_rest_util_pkg.remove_fwd_trailing_slash(m.path)) mpath
           ,nvl(m.consumes, extractvalue(r.descriptor, '/ServiceDescriptor/@consumes')) consumes
           ,nvl(m.produces, extractvalue(r.descriptor, '/ServiceDescriptor/@produces')) produces
           ,m.provider_type
           ,decode(m.remove_root_ele,'true','Y','N') root_ele
           ,m.bipub_dd_app
           ,m.xmldef
      --     ,m.method_xml
           ,decode(m.provider_type,'SQL',m.sql,null) sql
           ,decode(m.provider_type,'PLSQL',m.sql,null) plsql
  FROM      (SELECT service_name
                   ,nvl(custom_descriptor, seeded_descriptor) descriptor
             FROM   xxas_com_service_repository
             where existsnode(seeded_descriptor, '//Provider') > 0) r
           ,XMLTable
             ('//Method' PASSING r.descriptor
                         COLUMNS method_name        varchar2(400)  PATH '@name'
                                ,description        varchar2(4000) PATH '@description'
                                ,method             varchar2(400)  PATH '@methods'
                                ,path               varchar2(400)  PATH '@path'
                                ,consumes           varchar2(4000) PATH '@consumes'
                                ,produces           varchar2(4000) PATH '@produces'
                                ,provider_type      varchar2(400)  PATH 'Provider/@type'
                                ,remove_root_ele    varchar2(400)  PATH 'Provider/@removeRootElement'
                                ,sql                varchar2(4000) PATH 'Provider/substring(text(),1,3998)'
                                ,bipub_dd_app       varchar2(400)  PATH 'BIPublisher/@app'
                                ,xmldef      varchar2(400)  PATH 'BIPublisher/@code') m
    order by m.method,m.method_name;
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_rest_methods_v'
   ,p_description            => 'Applaud Rest Methods');
END;
/
COMMIT;
EXIT;
