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

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

DECLARE

  --
  -- Local constants, exceptions and variables
  --
  l_FFFUNC_NAME    CONSTANT varchar2(80)  := 'XXAS_GET_PHONE_ATTRIBUTES';
  l_FFFUNC_DEFN    CONSTANT varchar2(100) := 'xxas_com_2fa_pkg.get_phone_attribute_details';
  l_FFFUNC_DESC    CONSTANT varchar2(240) := 'Get Phone flex attributes';
  l_EFF_DT         CONSTANT date          := to_date('0001-01-01','YYYY-MM-DD');
  l_BG_ID          CONSTANT number        := to_number('&1');
  l_YES            CONSTANT varchar2(1)   := 'Y';
  l_NUMBER_TYPE    CONSTANT varchar2(1)   := 'N';
  l_TEXT_TYPE      CONSTANT varchar2(1)   := 'T';
  l_DATE_TYPE      CONSTANT varchar2(1)   := 'D';

  e_already_exists          EXCEPTION;

  l_exists                  varchar2(1);
  l_fffunc_id               number;
  l_ovn                     number;
  l_sequence                number;
  l_bg_id_ctx               number;
  l_eff_date_ctx            number;
  l_asg_id_ctx              number;

  --
  -- Gets the FF function if already created
  --
  CURSOR csr_get_fffunc IS
  SELECT l_YES
  FROM   ff_functions fff
  WHERE  fff.name = l_FFFUNC_NAME
  AND    fff.business_group_id = l_BG_ID;

BEGIN

  --
  -- Check if the FF function exists
  --
  OPEN  csr_get_fffunc;
  FETCH csr_get_fffunc INTO l_exists;
  CLOSE csr_get_fffunc;

  IF l_exists IS NOT NULL THEN
    RAISE e_already_exists;
  END IF;


  --
  -- Create the FF function
  --
  ff_functions_api.create_function
    (p_validate              => FALSE
    ,p_effective_date        => l_EFF_DT
    ,p_name                  => l_FFFUNC_NAME
    ,p_class                 => 'E'
    ,p_business_group_id     => l_BG_ID
    ,p_data_type             => l_TEXT_TYPE    
    ,p_definition            => l_FFFUNC_DEFN
    ,p_description           => l_FFFUNC_DESC
    ,p_function_id           => l_fffunc_id
    ,p_object_version_number => l_ovn);

  --
  -- Create the FF parameters
  --
  ff_function_parameters_api.create_parameter
    (p_validate              => FALSE
    ,p_effective_date        => l_EFF_DT
    ,p_function_id           => l_fffunc_id
    ,p_class                 => 'I'
    ,p_data_type             => l_TEXT_TYPE
    ,p_name                  => 'p_phone_id'
    ,p_optional              => 'N'
    ,p_continuing_parameter  => 'N'
    ,p_sequence_number       => l_sequence
    ,p_object_version_number => l_ovn);

  ff_function_parameters_api.create_parameter
    (p_validate              => FALSE
    ,p_effective_date        => l_EFF_DT
    ,p_function_id           => l_fffunc_id
    ,p_class                 => 'I'
    ,p_data_type             => l_TEXT_TYPE
    ,p_name                  => 'p_required_value'
    ,p_optional              => 'N'
    ,p_continuing_parameter  => 'N'
    ,p_sequence_number       => l_sequence
    ,p_object_version_number => l_ovn);

EXCEPTION

  WHEN e_already_exists THEN
    --
    -- No action required; FF Function already exists
    --
    NULL;

END;
/
COMMIT;
EXIT;
