/* $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_menus_v
AS
SELECT m.menu_id
      ,m.menu_name
      ,m.user_menu_name
      ,nvl(extractvalue(ov.object_values, '//title')
          ,m.user_menu_name) title
      ,extractvalue(ov.object_values, '//menuType') menu_type
      ,extractvalue(ov.object_values, '//objectIdFormat') object_id_format
      ,extractvalue(ov.object_values, '//objectIdHandler') object_id_handler
      ,extractvalue(ov.object_values, '//subnavHeaderProvider') subnav_header_provider
      ,ov.creation_date
      ,ov.created_by
      ,ov.last_update_date
      ,ov.last_updated_by
      ,ov.last_update_login
FROM   fnd_menus_vl m
      ,xxas_object_values ov
WHERE  m.menu_id = ov.object_id
AND    ov.object_type = 'FND_MENU_NAV'
AND    m.menu_name NOT IN ('XXAS_GLOBAL_MENU', 'XXAS_GLOBAL_USER_MENU');

COMMIT;
EXIT;

