/* $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_form_field_attributes_v
AS 
 SELECT xfv.any_path xdb_uri
      ,xfv.form_id
      ,xxas_forms_pkg.container_exists(xfv.form_id) container_exists
      --,DECODE(ff.form_field_type, 'COMBOLIST',xxas_forms_pkg.get_form_field_type(ff.show_as_radio, ff.show_as_boolean, ff.multiple_selection),xxas_util_pkg.encode_lookup('XXAS_FIELDS',ff.form_field_type)) field_type
      ,CASE xxas_util_pkg.encode_lookup('XXAS_FIELDS',ff.form_field_type)
        WHEN 'COMBOLIST' THEN
          xxas_forms_pkg.get_form_field_type(ff.show_as_radio, ff.show_as_boolean, ff.multiple_selection)
        WHEN 'POPLIST' THEN
          xxas_forms_pkg.get_form_field_type(ff.show_as_radio, ff.show_as_boolean, ff.multiple_selection)          
        ELSE
          xxas_util_pkg.encode_lookup('XXAS_FIELDS',ff.form_field_type)
       END field_type
      ,ff.form_field_type
      --,xxas_util_pkg.encode_lookup('XXAS_FIELDS',ff.field_type)  field_type
      /*,REPLACE(CASE xxas_util_pkg.encode_lookup('XXAS_FIELDS',ff.field_type)
          WHEN 'FLEXFIELD' THEN
            NVL(ff.descriptive_flexfield_name,xxas_forms_pkg.get_kff_name(ff.flex_structure_code))
          ELSE
            ff.field_id
          END
          ,'_','-') field_id*/
      ,REPLACE(ff.field_id,'_','-') field_id   
      ,xxas_forms_pkg.get_display_sequence(xfv.any_path,REPLACE(ff.field_id,'-','_')) display_sequence
      ,ff.description
      ,ff.value
      ,ff.label
      ,CASE ff.visible
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.visible 
        END visible
      ,CASE ff.enabled
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.enabled 
        END enabled
      ,CASE ff.required
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.required 
        END required
      ,ff.empty_value
      ,ff.hideif
      ,ff.post_to_href
      ,CASE ff.refresh_on_change
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.refresh_on_change
        END refresh_on_change
      ,ff.max_length
      ,ff.max_chars
      ,ff.service_name
      ,ff.google_place_type
      ,ff.place_holder
      ,CASE ff.rich_text
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.rich_text 
        END rich_text
      ,ff.list_function
      ,ff.style
      ,ff.weight
      ,ff.color
      ,ff.value_generator
      ,ff.max_attachments
      ,ff.entity_name
      ,CASE ff.category_code
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.category_code 
        END category_code
      ,ff.attachment_title
      ,ff.attachment_description
      ,ff.PK1FIELDNAME
      ,ff.PK2FIELDNAME
      ,ff.PK3FIELDNAME
      ,ff.PK4FIELDNAME
      ,ff.PK5FIELDNAME
      ,ff.file_types
      ,ff.file_name
      ,ff.file_href
      ,ff.button_text
      ,ff.post_new_to_href
      ,ff.decimal_precision
      ,ff.date_format
      ,SUBSTR(ff.list_provider,0,INSTR(ff.list_provider,':')-1) list_provider_type
      ,ff.list_provider
      ,CASE INSTR(list_provider,'LOOKUP')
       WHEN 1 THEN  SUBSTR(list_provider,INSTR(list_provider,':')+1)
       ELSE NULL
       END lookup_type, 
      --ff.lookup_type
      CASE INSTR(list_provider,'VALUE')
      WHEN 1 THEN  SUBSTR(list_provider,INSTR(list_provider,':')+1)
      ELSE NULL
      END value_set,      
     -- ,ff.value_set
      CASE INSTR(list_provider,'REST')
      WHEN 1 THEN  SUBSTR(list_provider,INSTR(list_provider,':')+1)
      ELSE NULL
      END rest_api
--      ,ff.rest_api
      ,CASE ff.positive_boolean_value
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
		ELSE ff.positive_boolean_value
       END positive_boolean_value
      ,ff.show_as_boolean      
      ,CASE ff.longlist
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.longlist
        END longlist
      ,ff.no_data_message
      ,CASE ff.create_allowed
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.create_allowed 
        END create_allowed
      ,ff.create_label
      ,ff.min_rows
      ,ff.max_rows
      ,ff.MIN
      ,ff.MAX
      ,ff.icon
      ,ff.icon_color
      ,ff.multi_color
      ,ff.min_label
      ,ff.max_label
      ,ff.multirow_xdb_uri
      ,CASE ff.include_empty_row
        WHEN 'true' THEN 'Y'
        WHEN 'false' THEN 'N'
        ELSE ff.include_empty_row 
        END include_empty_row
      ,ff.show_as_radio
      ,ff.multiple_selection        
      ,NVL(ff.descriptive_flexfield_name,xxas_forms_pkg.get_kff_name(ff.flex_structure_code)) flexfield
      ,ff.column_name_prefix
      ,ff.question_ref_id
      ,ff.application_id
      ,ff.flex_code
      ,ff.flex_structure_code
      ,ff.descriptive_flexfield_name
      ,ff.descriptive_flex_context_code
                 FROM
                 --xxas_forms_v xfv,
                 xxas_forms_resource xfv,
                  XMLTable
           ('/*'
            PASSING (xxas_forms_pkg.form_element_xml(xfv.form_id,'EditFields')--xfv.view_xml
                     )
            COLUMNS  form_field_type          VARCHAR2(4000)  path 'name()'
                    ,field_id                 VARCHAR2(4000)  path '@id'
                    ,description              VARCHAR2(4000)  PATH  '@description'
                    ,label                    VARCHAR2(4000)  PATH  '@label'
                    ,value                    VARCHAR2(4000)  PATH  '@value'
                    ,visible                  VARCHAR2(4000)  PATH  '@visible'
                    ,enabled                  VARCHAR2(4000)  PATH  '@enabled'
                    ,required                 VARCHAR2(4000)  PATH  '@required'
                    ,empty_value              VARCHAR2(4000)  PATH  '@emptyValue'
                    ,hideif                  VARCHAR2(4000)  PATH  '@hideIf'
                    ,post_to_href             VARCHAR2(4000)  PATH  '@postToHref'  
                    ,refresh_on_change        VARCHAR2(4000)  PATH  '@refreshOnChange'
                    ,max_length               VARCHAR2(4000)  PATH  '@maxLength'
                    ,max_chars                VARCHAR2(4000)  PATH  '@maxChars'
                    ,service_name             VARCHAR2(4000)  PATH  '@serviceName'
                    ,google_place_type        VARCHAR2(4000)  PATH  '@googlePlaceType'
                    ,place_holder             VARCHAR2(4000)  PATH  '@placeHolder'    
                    ,rich_text                VARCHAR2(4000)  PATH  '@richText'
                    ,list_function            VARCHAR2(4000)  PATH  '@listFunction'
                    ,style                    VARCHAR2(4000)  PATH  '@style'
                    ,weight                   VARCHAR2(4000)  PATH  '@weight'
                    ,color                    VARCHAR2(4000)  PATH  '@color'
                    ,value_generator          VARCHAR2(4000)  PATH  '@valueGenerator'
                    ,max_attachments          VARCHAR2(4000)  PATH  '@maxAttachments'
                    ,entity_name              VARCHAR2(4000)  PATH  '@entityName' 
                    ,category_code            VARCHAR2(4000)  PATH  '@categoryCode'
                    ,attachment_title         VARCHAR2(4000)  PATH  '@attachmentTitle'
                    ,attachment_description   VARCHAR2(4000)  PATH  '@attachmentDescription'
                    ,PK1FIELDNAME             VARCHAR2(4000)  PATH  '@pk1FieldName'
                    ,PK2FIELDNAME             VARCHAR2(4000)  PATH  '@pk2FieldName'
                    ,PK3FIELDNAME             VARCHAR2(4000)  PATH  '@pk3FieldName'
                    ,PK4FIELDNAME             VARCHAR2(4000)  PATH  '@pk4FieldName'
                    ,PK5FIELDNAME             VARCHAR2(4000)  PATH  '@pk5FieldName'
                    ,file_types               VARCHAR2(4000)  PATH  '@fileTypes'
                    ,file_name                VARCHAR2(4000)  PATH  '@fileName'
                    ,file_href                VARCHAR2(4000)  PATH  '@fileHref'
                    ,button_text              VARCHAR2(4000)  PATH  '@buttonText'
                    ,post_new_to_href         VARCHAR2(4000)  PATH  '@postNewToHref' 
                    ,decimal_precision        VARCHAR2(4000)  PATH  '@decimalPrecision'
                    ,date_format              VARCHAR2(4000)  PATH  '@dateFormat' 
                    ,list_provider_type       VARCHAR2(4000)  PATH  '@listProviderType'
                    ,list_provider            VARCHAR2(4000)  path  '@listProvider'
                    ,lookup_type              VARCHAR2(4000)  PATH  '@lookupType'
                    ,value_set                VARCHAR2(4000)  PATH  '@valueSet'
                    ,rest_api                 VARCHAR2(4000)  PATH  '@restAPI'
                    ,positive_boolean_value   VARCHAR2(4000)  PATH  '@positiveBooleanValue'
                    ,show_as_boolean          VARCHAR2(4000)  PATH  '@showAsBoolean'
                    ,longlist                 VARCHAR2(4000)  PATH  '@longlist'
                    ,no_data_message          VARCHAR2(4000)  PATH  '@noDataMessage'
                    ,create_allowed           VARCHAR2(4000)  PATH  '@createAllowed'
                    ,create_label             VARCHAR2(4000)  PATH  '@createLabel'
                    ,min_rows                 VARCHAR2(4000)  PATH  '@minRows'
                    ,max_rows                 VARCHAR2(4000)  PATH  '@maxRows'
                    ,min                      VARCHAR2(4000)  PATH  '@min'
                    ,max                      VARCHAR2(4000)  PATH  '@max'
                    ,min_label                VARCHAR2(4000)  PATH  '@minLabel'
                    ,max_label                VARCHAR2(4000)  PATH  '@maxLabel'
                    ,multi_color              VARCHAR2(4000)  PATH  '@multiColor'
                    ,icon                     VARCHAR2(4000)  PATH  '@icon'
                    ,icon_color               VARCHAR2(4000)  PATH  '@iconColor'
                    ,multirow_xdb_uri         VARCHAR2(4000)  PATH  '@xdbUri'
                    ,include_empty_row        VARCHAR2(4000)  PATH  '@includeEmptyRow'
                    ,show_as_radio            VARCHAR2(4000)  PATH  '@showAsRadio'
                    ,multiple_selection       VARCHAR2(4000)  PATH  '@multipleSelection'                    
                    ,flexfield                VARCHAR2(4000)  PATH  '@flexfield'
                    ,column_name_prefix       VARCHAR2(4000)  PATH  '@columnNamePrefix'
                    ,question_ref_id          VARCHAR2(4000)  PATH  '@questionRefId'
                    ,application_id           VARCHAR2(4000)  PATH  '@applicationId'
                    ,flex_code                VARCHAR2(4000)  PATH  '@flexCode'
                    ,flex_structure_code      VARCHAR2(4000)  PATH  '@flexStructureCode'
                    ,descriptive_flexfield_name VARCHAR2(4000)  PATH  '@descriptiveFlexfieldName'
                    ,descriptive_flex_context_code VARCHAR2(4000)  PATH  '@descriptiveFlexContextCode')
                    ff;
  
BEGIN
  xxas_fnd_dictionary_pkg.add_view
   (p_application_short_name => xxas_util_pkg.app
   ,p_view_name              => 'xxas_form_field_attributes_v'
   ,p_description            => 'Applaud Form Fields List');
END;
/
COMMIT;
EXIT;
