/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_sql varchar2(32000);

BEGIN

  --
  -- Register QueryCostAllocationKffSegments service
  --
  l_sql :=
'        SELECT  fs.segment_num
               ,fs.application_column_name
               ,fs.segment_name
               ,fs.required_flag
               ,fs.display_flag
               ,fs.display_size
               ,fs.maximum_description_len
               ,fs.default_type
               ,fs.default_value
               ,fs.flex_value_set_id
               ,vset.flex_value_set_name
               ,vset.validation_type
               ,decode(vset.longlist_flag
                      ,''X'', ''N''
                      ,''Y'', ''Y''
                      ,''N'') longlist_flag
               ,vset.format_type
               ,vset.maximum_size
               ,vset.alphanumeric_allowed_flag
               ,vset.uppercase_only_flag
               ,decode(vset.numeric_mode_enabled_flag
                      ,''R'', ''Y''
                      ,''Y'', ''Y''
                      ,''N'') numeric_mode_enabled_flag
               ,vset.description
               ,vset.dependant_default_value
               ,vset.dependant_default_meaning
               ,vset.parent_flex_value_set_id
               ,vset.minimum_value
               ,vset.maximum_value
               ,vset.number_precision
        FROM    fnd_id_flex_segments fs
               ,fnd_segment_attribute_values sa1
               ,fnd_flex_value_sets vset
        WHERE   sa1.id_flex_num =
                 (SELECT bg.cost_allocation_structure
                  FROM   per_business_groups_perf bg
                  WHERE  bg.business_group_id = :pBusinessGroupId)
        AND     sa1.id_flex_code = ''COST''
        AND     fs.flex_value_set_id = vset.flex_value_set_id(+)
        AND     sa1.application_id = fs.application_id
        AND     sa1.attribute_value = ''Y''
        AND     sa1.segment_attribute_type = ''ELEMENT ENTRY''
        AND     fs.id_flex_num =
                 (SELECT bg.cost_allocation_structure
                  FROM   per_business_groups_perf bg
                  WHERE  bg.business_group_id = :pBusinessGroupId)
        AND     fs.id_flex_code = ''COST''
        AND     fs.enabled_flag  = ''Y''
        AND     fs.display_flag = ''Y''
        AND     fs.application_id = 801
        AND     fs.application_column_name = sa1.application_column_name
        ORDER BY fs.segment_num ';

  xxas_com_sr_pkg.seed_query_service
    (p_service_name      => 'QueryCostAllocationKffSegments'
    ,p_securing_function => 'XXAS_SRV_Q_COST_KFF_SEG_PERM'
    ,p_sql               => l_sql
    ,p_parameters        => xxas_com_sr_param_varray
                              (xxas_com_sr_param_type('pBusinessGroupId', xxas_com_sr_pkg.g_NUMBER)));

END;
/
COMMIT;
EXIT;
