/* $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_flexible_v
AS
SELECT b.menu_id
      ,b.function_id
      ,b.function_name
	  ,b.seeded_block_flag
      ,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
      ,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
      ,nvl(xxas_util_pkg.boolean_to_flag(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pViewMode')),'N') view_only_mode
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pLayoutType') layout
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pFormulaName') view_formula_name
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pEditFormulaName') edit_formula_name
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pModalWidth') modal_width
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pNoDataMessage') no_data_message
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pBackgroundColor') background_color
      ,xxas_util_pkg.boolean_to_flag(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pLimitItems')) limit_list_items
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pDefaultItemsPerPage') items_per_page
      ,nvl(xxas_com_msg_pkg.get_string(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pMoreItemsAvailableLinkMessage')),xxas_com_msg_pkg.get_string('XXAS_COM_MORE_ITEMS_TEXT')) more_items_available_text
      ,xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pMoreItemsAvailableLinkMessage') more_items_available_message     
      ,f.function_id data_function_id
      ,f.object_id data_object_id
      ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pOrderBy') order_by
      ,CASE WHEN xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pPersonIdColumnName') IS NOT NULL 
              OR xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pAssignmentIdColumnName') IS NOT NULL 
              OR xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pTransactionIdColumnName') IS NOT NULL 
              OR xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pPartyIdColumnName') IS NOT NULL 
            THEN 'Y'
            ELSE 'N'
       END  show_joining_columns
              ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pPersonIdColumnName') person_id_column_name
      ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pAssignmentIdColumnName') Assignment_Id_Column_Name
      ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pTransactionIdColumnName') transaction_id_column_name
      ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pPartyIdColumnName') party_Id_Column_Name
      ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pViewXml') form
      ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pImplementation') form_handler
      ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pCalledFrom') called_from_function_id
      ,decode(xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pViewXml'),
          '/public/xxas/emp/view/asgeit.xml',
          xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pInformationType'),
		  '/public/xxas/custom/emp/view/asgeit.xml',
          xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pInformationType'),null) asg_eit_structures
      ,decode(xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pViewXml'),
          '/public/xxas/emp/view/personeit.xml',
          xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pInformationType'),
		  '/public/xxas/custom/emp/view/personeit.xml',
          xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pInformationType'),null) eit_structures
      ,xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pFlexStructureCode') sit_structures
      ,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
	  ,decode(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pSearchAttributes'),null,'N','Y') SEARCH_FLAG
      ,(select xxas_block_pkg.get_search_details(xxas_com_sec_pkg.get_parameter_value(parameters,'pSearchAttributes'))
        from fnd_form_functions
        where function_id = b.function_id) search_details
      ,decode(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pFacets'),null,'N','Y') FILTER_FLAG 
      ,cast(multiset(
               select ab.field,
                      ab.typ,
                      ab.title
               from XMLTable('//value/*'
                        PASSING (select xxas_block_pkg.get_filter_details(xxas_com_sec_pkg.get_parameter_value(parameters,'pFacets'))
                                 from fnd_form_functions
                                 where function_id = b.function_id)
                        COLUMNS FIELD VARCHAR2(100)   PATH '/XXAS_FILTER_DETAILS/FILTER_FIELD',
                                TITLE VARCHAR2(100)   PATH '/XXAS_FILTER_DETAILS/FILTER_TITLE',
                                TYP   VARCHAR2(10)    PATH '/XXAS_FILTER_DETAILS/FILTER_TYPE'
                            ) ab
                    ) as XXAS_FILTER_FIELDS_TBL) FILTER_DETAILS
      ,decode(xxas_com_sec_pkg.get_parameter_value(b.function_parameters,'pSorts'),null,'N','Y') SORT_FLAG    
      ,cast(multiset(
               select ab.field,
                      ab.title,
                      ab.direction,
                      ab.sel_def
               from XMLTable('//value/*'
                        PASSING (select xxas_block_pkg.get_sort_details(xxas_com_sec_pkg.get_parameter_value(parameters,'pSorts'))
                                 from fnd_form_functions
                                 where function_id = b.function_id)
                        COLUMNS FIELD      VARCHAR2(30)   PATH '/XXAS_SORT_DETAILS/SORT_FIELD',
                                TITLE      VARCHAR2(100)  PATH '/XXAS_SORT_DETAILS/SORT_TITLE',
                                DIRECTION  VARCHAR2(100)  PATH '/XXAS_SORT_DETAILS/SORT_DIRECTION',
                                SEL_DEF    VARCHAR2(10)   PATH '/XXAS_SORT_DETAILS/SORT_TYPE'
                            ) ab
                    ) as XXAS_SORT_FIELDS_TBL) SORT_DETAILS 
FROM   xxas_blocks_v b
      ,xxas_pages_v p
      ,fnd_form_functions f
      ,fnd_form_functions f1
WHERE  b.block_type = 'flexible'
AND  f.function_name(+) = xxas_com_sec_pkg.get_parameter_value(function_parameters, 'pDataFunction')
AND  f1.function_name(+) = xxas_com_sec_pkg.get_parameter_value(f.parameters, 'pCalledFrom')
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_FLEXIBLE_V'
   ,p_description            => 'Applaud Block Flexible');
END;
/

COMMIT;
EXIT;

