/* $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_menu_items_list_v
AS
SELECT 'flexible' "type"
      ,mi.menu_id
      ,mi.title
      ,mi.entry_sequence current_entry_sequence
      ,mi.entry_sequence
      ,mi.prompt "line1"
      ,mi.prompt
      ,mi.root_menu_flag
      ,mi.root_menu_id
      ,mi.url
      ,mi.item_type
      ,mi.menu_id || '_' || mi.entry_sequence "id"
      ,CASE mi.item_type
            WHEN 'PAGE'    THEN 'document'
            WHEN 'FOLDER'  THEN 'folder_open'
            WHEN 'URL'     THEN nvl(mi.icon, 'other')
       END "icon"
      ,CASE mi.item_type
            WHEN 'PAGE'    THEN 'positive'
            WHEN 'FOLDER'  THEN 'energized'
            WHEN 'URL'     THEN 'balanced'
       END "statusColor"
      ,CASE mi.item_type
            /*WHEN 'PAGE'    THEN 'menu-item'*/
            WHEN 'FOLDER'  THEN 'menu-items'
            /*WHEN 'URL'     THEN 'menu-item'*/
       END "pageId"
      ,CASE mi.item_type
            WHEN 'FOLDER' THEN to_char(mi.sub_menu_id)
            /*ELSE mi.menu_id || '_' || mi.entry_sequence*/
       END "objectId"
      ,CASE mi.item_type
            WHEN 'FOLDER' THEN 'modal'
       END  "target"
      ,mi.page_slug
      ,mi.sub_menu_id
      ,mi.function_id
      ,nvl(mi.icon, 'other') function_icon
      ,CASE WHEN mi.item_type = 'PAGE' OR mi.item_type = 'FOLDER'
            THEN xxas_emp_menu_pkg.menu_item_actions(mi.menu_id,mi.sub_menu_id,mi.entry_sequence).get_xml()
            /*WHEN mi.item_type = 'URL'
            THEN xxas_emp_menu_pkg.link_menu_item_actions(mi.menu_id,mi.entry_sequence).get_xml()*/
       END "linked"
FROM   (SELECT me.menu_id
              ,me.sub_menu_id
              ,decode(m.menu_id, null, 'N', 'Y') root_menu_flag
              ,m.title
              ,nvl(m.menu_id
                  ,(SELECT pm.menu_id
                    FROM   fnd_menu_entries pm
                          ,xxas_menus_v xm
                    WHERE  pm.sub_menu_id = me.menu_id
                    AND    xm.menu_id = pm.menu_id)) root_menu_id
              ,pg.menu_id page_menu_id
              ,pg.slug page_slug
              ,me.function_id
              ,ff.web_icon icon
              ,me.entry_sequence
              ,me.prompt
              ,CASE WHEN pg.menu_id IS NOT NULL
                    AND EXISTS
                      (SELECT 'exists'
                       FROM   xxas_pages_v sp
                             ,fnd_menu_entries sme
                       WHERE  sp.menu_id = sme.sub_menu_id
                       AND    sme.menu_id = pg.menu_id)
                    THEN 'FOLDER'
                    WHEN pg.menu_id IS NOT NULL
                    AND (SELECT COUNT(*)
                         FROM   fnd_menu_entries fme
                         WHERE  fme.menu_id = pg.menu_id) = 0
                    THEN 'FOLDER'
                    WHEN pg.menu_id IS NOT NULL
                    THEN 'PAGE'
                    WHEN lower
                           (substr
                             (nvl(xxas_com_sec_pkg.get_parameter_value(ff.parameters, 'pUrl')
                                 ,xxas_com_sec_pkg.get_parameter_value(ff.parameters, 'pHref'))
                             ,1, 4)) = 'http'
                    THEN 'URL'
                    WHEN nvl(xxas_com_sec_pkg.get_parameter_value(ff.parameters, 'pUrl')
                            ,xxas_com_sec_pkg.get_parameter_value(ff.parameters, 'pHref')) IS NOT NULL
                    THEN 'PAGE'
                    ELSE 'URL'
               END item_type
              ,nvl(xxas_com_sec_pkg.get_parameter_value(ff.parameters, 'pUrl')
                  ,nvl(xxas_com_sec_pkg.get_parameter_value(ff.parameters, 'pHref')
                      ,ff.web_html_call)) url
        FROM  fnd_menu_entries_vl me
             ,fnd_form_functions ff
             ,xxas_pages_v pg
             ,xxas_menus_v m
        WHERE me.function_id = ff.function_id(+)
        AND   me.sub_menu_id = pg.menu_id(+)
        AND   me.menu_id = m.menu_id(+)
        AND  (me.function_id IS NULL OR
              (me.function_id IS NOT NULL AND
               me.function_id NOT IN
                (SELECT bk.function_id
                 FROM   xxas_blocks_v bk)
               ))
        AND   xxas_com_sec_pkg.get_parameter_value(ff.parameters, 'pLayoutBase') IS NULL) mi;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_MENU_ITEMS_LIST_V'
   ,p_description            => 'Applaud Menu Items');
END;
/

COMMIT;
EXIT;

