/* $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_block_advert_v
AS
SELECT b.menu_id
      ,b.function_id
      ,b.function_name
      ,b.entry_sequence
      ,b.prompt
      ,b.object_id
      ,b.obj_name
	  ,decode(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pHideIf'),null,'N','Y') RESTRICT_DEVICE
      ,(xxas_block_pkg.get_restrictions(b.function_parameters)) RESTRICTIONS
      ,upper(b.block_type) block_type
      ,b.description
      ,xxas_util_pkg.boolean_to_flag(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pStamp')) stamp
      ,xxas_util_pkg.boolean_to_flag(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pFullWidth')) full_width
      ,b.function_icon
      ,b.security_type type_of_security
      ,CASE WHEN xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NOT NULL
            THEN 'S'
            WHEN (xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pAdFunction') IS NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pFileName') IS NULL)
            THEN 'R'
            WHEN (xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pAdFunction') IS NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pFileName') IS NOT NULL)
            THEN 'S'
            WHEN (xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pAdFunction') IS NOT NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pFileName') IS NULL)
            THEN 'S'
            WHEN (xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pAdFunction') IS NOT NULL)
            THEN 'S'
       END single_or_random
      ,CASE WHEN xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pAdFunction') IS NOT NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NOT NULL 
            THEN 'D'
            WHEN xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pAdFunction') IS NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NULL 
            THEN 'S'
            WHEN xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pAdFunction') IS NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NOT NULL 
            THEN 'S'
            WHEN xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters,'pAdFunction') IS NOT NULL AND 
                 xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NULL 
            THEN 'D'
       END static_or_dynamic
      ,CASE WHEN NVL(INSTR(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pLink'),'#/',1,1),0) <> 0
            THEN 'P'
            WHEN xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pLink') IS NOT NULL
            THEN 'U'
            ELSE 'N'
       END type_of_link
      ,CASE WHEN xxas_com_sec_pkg.get_parameter_value
                   (b.function_parameters, 'pButtonText') IS NOT NULL
            THEN 'Y'
            ELSE 'N'
       END show_button
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pAdFunction') dynamic_function_name
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pFileName') filename
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTextColor') text_color
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTextBgColor') text_bg_color
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTextWidth') textbox_width_percent
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTextPosX') textbox_x_width_percent
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTextYAlign') textbox_y_width_percent
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTextAlign') text_alignment
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTextMargBottom') textbox_bottom_margin
      ,xxas_util_pkg.boolean_to_flag(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTextPad')) textbox_padding
      ,xxas_util_pkg.boolean_to_flag(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pMargNo')) advert_padding
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pPageId') page
      ,decode(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pTarget'),null,'N','Y') open_in_modal
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pLink') url
      ,xxas_com_msg_pkg.get_string(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pLine1')) line1
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pLine1') line1_message
      ,xxas_com_msg_pkg.get_string(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pLine2')) line2
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pLine2') line2_message
      ,xxas_com_msg_pkg.get_string(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pButtonText')) button_label
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pButtonText') button_label_message
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pAdGroup') advert_group_menu
      ,p.context_handler page_object_handler
      ,p.object_id_format page_object_id_format
      ,CASE WHEN p.layout = 'columns2'
            THEN 'Y'
            WHEN p.layout = 'columns3'
            THEN 'Y'
            ELSE 'N'
       END  is_page_column_layout
      ,p.layout page_layout
	  ,CASE WHEN p.layout = 'columns3' 
			THEN decode(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pBlockSize'),'two-third','Two-third','full-width','Full width','One-third') 
			ELSE NULL
		END BLOCK_SIZE
      ,b.seeded_block_flag
FROM   xxas_blocks_v b
      ,xxas_pages_v p
WHERE  b.block_type = 'advert'
AND    b.menu_id    = p.menu_id;

BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'XXAS_BLOCK_ADVERT_V'
   ,p_description            => 'Applaud Block Advert');
END;
/

COMMIT;
EXIT;

