/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2015 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_pages_v
AS
SELECT m.menu_id
      ,m.menu_name
      ,nvl(extractvalue(ov.object_values, '//slug')
          ,m.menu_id) slug
      ,extractvalue(ov.object_values, '//publishedFlag') published_flag
      ,lower(extractvalue(ov.object_values, '//icon')) icon
      ,lower(extractvalue(ov.object_values, '//iconColor')) icon_color
      ,xxas_rslt_cache_pkg.resolve_color_name
        (lower(extractvalue(ov.object_values, '//iconColor'))) icon_color_hex
      ,null obj_name --obsolete
      ,nvl
         (extractvalue(ov.object_values, '//objectIdFormat')
         ,CASE extractvalue(ov.object_values, '//ctxHandler')
           WHEN 'xxas_per_pkg.person_ctx'                      THEN '{PERSON_ID}'
           WHEN 'xxas_per_pkg.per_and_primary_asg_ctx'         THEN '{PERSON_ID}'
           WHEN 'xxas_block_pkg.block_ctx'                     THEN '{MENU_ID}_{FUNCTION_ID}'
           WHEN 'xxas_mss_util_pkg.emp_ctx'                    THEN '{PERSON_ID}_{TRANSACTION_ID}_{TRANSACTION_STEP_ID}'
           WHEN 'xxas_com_worklist_pkg.notification_ctx'       THEN '{NOTIFICATION_ID}'
           WHEN 'xxas_com_asg_pkg.assignment_ctx'              THEN '{ASSIGNMENT_ID}'
           WHEN 'xxas_page_pkg.page_ctx'                       THEN '{MENU_ID}'
           WHEN 'xxas_per_contact_pkg.contact_ctx'             THEN '{PERSON_ID}_{CONTACT_PERSON_ID}_{TRANSACTION_ID}_{TRANSACTION_STEP_ID}'
           WHEN 'xxas_note_pkg.note_ctx'                       THEN '{NOTE_ID}'
           WHEN 'xxas_mss_util_pkg.asg_ctx'                    THEN '{PERSON_ID}_{TRANSACTION_ID}_{TRANSACTION_STEP_ID}'
           WHEN 'xxas_perf_plans_pkg.enrollment_ctx'           THEN '{PERSON_ID}_{PERF_PLAN_ENROLLMENT_ID}'
           WHEN 'xxas_goals_pkg.goal_ctx'                      THEN '{GOAL_ID}'
           WHEN 'xxas_grantees_pkg.grantee_ctx'                THEN '{GRANT_ID}'
           WHEN 'xxas_grant_pkg.grant_ctx'                     THEN '{FUNCTION_ID}'
           WHEN 'xxas_com_worklist_pkg.vac_rule_ctx'           THEN '{RULE_ID}'
           WHEN 'xxas_vacancy_pkg.vacancy_and_logon_per_ctx'   THEN '{VACANCY_ID}'
           WHEN 'xxas_vacancy_pkg.vacancy_ctx'                 THEN '{VACANCY_ID}'
           WHEN 'xxas_goals_pkg.enrollment_ctx'                THEN '{PERF_PLAN_ENROLLMENT_ID}'
           WHEN 'xxas_page_util_pkg.general_settings_ctx'      THEN '{PERSON_ID}'
           WHEN 'xxas_change_pay_pkg.pay_proposal_ctx'         THEN '{PERSON_ID}_{ASSIGNMENT_ID}'
           WHEN 'xxas_new_hire_pkg.person_ctx'                 THEN '{PERSON_ID}'
           WHEN 'xxas_page_util_pkg.activate_product_ctx'      THEN '{PRODUCT_NAME}'
          END
         )object_id_format
      ,CASE WHEN nvl(extractvalue(ov.object_values, '//slug')
                    ,m.menu_id) = 'search' THEN
               -1 -- null
            ELSE
              xxas_emp_menu_pkg.menu_name_to_id
                (nvl
                  (extractvalue(ov.object_values, '//subnavMenu')
                  ,fnd_profile.value('XXAS_EMP_REG_VIEWS_MENU')))
       END sub_menu_id
      ,CASE WHEN nvl(extractvalue(ov.object_values, '//slug')
                    ,m.menu_id) = 'search' THEN
               null
            ELSE
            nvl
              (extractvalue(ov.object_values, '//subnavMenu')
              ,fnd_profile.value('XXAS_EMP_REG_VIEWS_MENU'))
       END sub_menu_name
      ,extractvalue(ov.object_values, '//ctxHandler') context_handler
      ,extractvalue(ov.object_values, '//actions') actions      
      ,xxas_com_msg_pkg.get_string
        (nvl(extractvalue(ov.object_values, '//titleMessage')
            ,'XXAS_UNTITLED')) title
      ,extractvalue(ov.object_values, '//titleMessage') title_message
      ,extractvalue(ov.object_values, '//titleFunction') title_function
      ,xxas_com_msg_pkg.get_string
        (extractvalue(ov.object_values, '//descriptionMessage')) description
      ,extractvalue(ov.object_values, '//descriptionMessage') description_message
      ,nvl(extractvalue(ov.object_values, '//layout'), 'stacked') layout
      ,extractvalue(ov.object_values, '//fixTitleFlag') fix_title_flag
      ,extractvalue(ov.object_values, '//hideTitleFlag') hide_title_flag
      ,extractvalue(ov.object_values, '//backgroundImage') background_image
      ,extractvalue(ov.object_values, '//backgroundImageRepeat') background_image_repeat
      ,nvl(extractvalue(ov.object_values, '//unclosableModal'),'N') unclosable_modal
      ,decode(substr(m.menu_name, 1, 4), 'XXAS', 'Y', 'N') seeded_page_flag
      ,CASE extractvalue(ov.object_values, '//subnavMenu')
         WHEN null THEN 'N'
         WHEN 'XXAS_ADMIN_SUBNAV' THEN 'Y'
         WHEN 'XXAS_PERFORMANCE_SETUP_SUBNAV' THEN 'Y'
         ELSE 'N'
       END internal_page_flag
      ,replace(lower(extractvalue(ov.object_values, '//hideIf')),',',' ') hideif
      ,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';

COMMIT;
EXIT;

