/* $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_oracle_favorites_v 
AS
SELECT *
FROM   (SELECT ice.display_sequence
              ,ice.prompt
              ,fnd_run_function.get_run_function_url
                (fff.function_id, ice.responsibility_application_id
                ,ice.responsibility_id, ice.security_group_id) url
        FROM  fnd_form_functions_vl fff
             ,icx_custom_menu_entries ice
        WHERE ice.reference_path = 'HOMEPAGE:' || to_char(fnd_global.user_id)
        AND   ice.user_id        = fnd_global.user_id
        AND   fff.function_id    = ice.function_id
        AND   ice.function_type <> 'XXX'
        AND   fff.web_html_call NOT LIKE 
                'OA.jsp?page=/xxas/oracle/apps/xxas/emp/webui/EmployeeCenterPG%'
        AND   fff.web_html_call NOT LIKE 'xxas%'
        UNION
        SELECT ice.display_sequence
              ,ice.prompt
              ,CASE WHEN lower(substr(trim(ice.url), 1, 4)) <> 'http'
                    THEN 'http://' || trim(ice.url)
                    ELSE trim(ice.url)
               END url
        FROM  icx_custom_menu_entries ice
        WHERE ice.reference_path = 'HOMEPAGE:' || to_char(fnd_global.user_id)
        AND   ice.user_id        = fnd_global.user_id
        AND   ice.function_id IS NULL)
ORDER BY display_sequence;
  
COMMIT;
EXIT;
