/* $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_blocks_v
AS
SELECT   e.menu_id
        ,lower(xxas_com_sec_pkg.get_parameter_value(cf.parameters, 'pModule')) block_type
        ,e.entry_sequence
        ,e.prompt
        ,e.description
        ,e.function_id
        ,cf.function_name
        ,cf.object_id
        ,cf.obj_name
        ,cf.parameters function_parameters
        ,cf.web_icon function_icon
        ,decode(cf.object_id, null, 'RO', 'RD') security_type
        ,xxas_block_pkg.page_to_block_join
          (e.menu_id, fnd_global.employee_id, cf.obj_name) join_condition
        ,decode(upper(substr(cf.function_name, 1, 4)), 'XXAS', 'Y', 'N') seeded_block_flag
        ,cf.object_values block_values
FROM     fnd_menu_entries_vl e
        ,(SELECT f.function_id
                ,f.function_name
                ,f.object_id
                ,o.obj_name
                ,f.parameters
                ,f.web_icon
                ,xov.object_values
          FROM   fnd_form_functions_vl f
                ,fnd_objects o
                ,xxas_object_values xov
          WHERE  f.object_id = o.object_id(+)
          AND    f.function_id = xov.object_id(+)
          AND    xov.object_type(+) = 'BLOCK') cf
WHERE    e.function_id = cf.function_id(+)
AND      e.function_id IS NOT NULL
AND      xxas_com_sec_pkg.get_parameter_value(cf.parameters, 'pModule') IS NOT NULL
/*AND      xxas_emp_menu_pkg.is_func_html_or_flash(cf.parameters) = 'html'*/
AND      e.menu_id IN
           (SELECT p.menu_id
            FROM   xxas_pages_v p);

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_BLOCKS_V'
   ,p_description            => 'Applaud Blocks');
END;
/

COMMIT;
EXIT;

