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

SET VERIFY OFF
SET ESCAPE '\'
SET FEEDBACK OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  e_no_bg_id          EXCEPTION;
  e_bg_not_exists     EXCEPTION;
  e_ff_type_not_found EXCEPTION;
  l_BG_ID   CONSTANT  NUMBER        := to_number('&1');
  l_USER_ID CONSTANT  NUMBER        := to_number('&2');
  l_START   CONSTANT  DATE          := to_date('0001-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_2FA_VERIFICATION';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud 2FA Verification';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Person';
  l_formula_text      LONG;
  l_formula_type_id   NUMBER;
  l_ff_id             NUMBER;
  l_bg_exists         VARCHAR2(1);

  --
  -- Tests if the Business Group already exists
  --
  CURSOR csr_bg_exists IS
  SELECT 'Y'
  FROM   per_business_groups_perf bg
  WHERE  bg.business_group_id = l_BG_ID;

  --
  -- Gets the FF Id
  --
  CURSOR csr_ff_id IS
  SELECT ff.formula_id
  FROM   ff_formulas_f ff
  WHERE  ff.business_group_id = l_BG_ID
  AND    ff.formula_name = l_FF_NAME;

  --
  -- Gets the Formula Type Id
  --
  CURSOR csr_ff_type IS
  SELECT fft.formula_type_id
  FROM   ff_formula_types fft
  WHERE  fft.formula_type_name = l_FF_TYPE;

BEGIN

  --
  -- Check that the Business Group Id was passed in
  --
  IF l_BG_ID IS NULL THEN
    RAISE e_no_bg_id;
  END IF;

  --
  -- Check the Business Group exists
  --
  OPEN  csr_bg_exists;
  FETCH csr_bg_exists INTO l_bg_exists;
  CLOSE csr_bg_exists;

  IF l_bg_exists IS NULL THEN
    RAISE e_bg_not_exists;
  END IF;

  --
  -- Fetch the Fast Formula Id
  --
  OPEN  csr_ff_id;
  FETCH csr_ff_id INTO l_ff_id;
  CLOSE csr_ff_id;

  --
  -- Get the Formula Type; error if it cannot be found
  --
  OPEN  csr_ff_type;
  FETCH csr_ff_type INTO l_formula_type_id;
  CLOSE csr_ff_type;

  IF l_formula_type_id IS NULL THEN
    RAISE e_ff_type_not_found;
  END IF;

  --
  -- Set the Formula Text
  --
  l_formula_text := q'[

/*==============================================================================

Formula Name: XXAS_2FA_VERIFICATION
Formula Type: Applaud Person
Description:

==============================================================================*/


  Default for PERSON_ID                         is -1
  Default for Phone_Type_Value_In               is 'Unknown'
  Default for PHONE_NUMBER_VALUE_in             is 'Unknown'
  Default for Phone_Type_OLD_Value_In           is 'Unknown'
  Default for PHONE_NUMBER_OLD_VALUE_in         is 'Unknown'
  Default for phone_id                          is 'Unknown'

  Inputs are PERSON_ID,PHONE_NUMBER_VALUE_in,PHONE_NUMBER_OLD_VALUE_IN,Phone_Type_VALUE_IN,Phone_Type_OLD_Value_IN,PHONE_ID,
     ATTRIBUTE_CATEGORY_VALUE_IN ,
     ATTRIBUTE1_VALUE_IN         ,
     ATTRIBUTE2_VALUE_IN         ,
     ATTRIBUTE3_VALUE_IN         ,
     ATTRIBUTE4_VALUE_IN         ,
     ATTRIBUTE5_VALUE_IN         ,
     ATTRIBUTE6_VALUE_IN         ,
     ATTRIBUTE7_VALUE_IN         ,
     ATTRIBUTE8_VALUE_IN         ,
     ATTRIBUTE9_VALUE_IN         ,
     ATTRIBUTE10_VALUE_IN         ,
     ATTRIBUTE11_VALUE_IN         ,
     ATTRIBUTE12_VALUE_IN         ,
     ATTRIBUTE13_VALUE_IN         ,
     ATTRIBUTE14_VALUE_IN         ,
     ATTRIBUTE15_VALUE_IN         ,
     ATTRIBUTE16_VALUE_IN         ,
     ATTRIBUTE17_VALUE_IN         ,
     ATTRIBUTE18_VALUE_IN         ,
     ATTRIBUTE19_VALUE_IN         ,
     ATTRIBUTE20_VALUE_IN         ,
     ATTRIBUTE21_VALUE_IN         ,
     ATTRIBUTE22_VALUE_IN         ,
     ATTRIBUTE23_VALUE_IN         ,
     ATTRIBUTE24_VALUE_IN         ,
     ATTRIBUTE25_VALUE_IN         ,
     ATTRIBUTE26_VALUE_IN         ,
     ATTRIBUTE27_VALUE_IN         ,
     ATTRIBUTE28_VALUE_IN         ,
     ATTRIBUTE29_VALUE_IN         ,
     ATTRIBUTE30_VALUE_IN


     NULL_VALUE = 'XXASNULLVAL'
     PHONE_ID_value             = XXAS_GET_PHONE_NUMBER(PERSON_ID,Phone_Type_Value_In,'Phone_id')
     CHANGE_NUMBER_visible      = 'false'
     PHONE_ID                   =   PHONE_ID_value
     ATTRIBUTE_CATEGORY_ENABLED = 'FALSE'
     ATTRIBUTE1_ENABLED         = 'FALSE'
     ATTRIBUTE2_ENABLED         = 'FALSE'
     ATTRIBUTE3_ENABLED         = 'FALSE'
     ATTRIBUTE4_ENABLED         = 'FALSE'
     ATTRIBUTE5_ENABLED         = 'FALSE'
     ATTRIBUTE6_ENABLED         = 'FALSE'
     ATTRIBUTE7_ENABLED         = 'FALSE'
     ATTRIBUTE8_ENABLED         = 'FALSE'
     ATTRIBUTE9_ENABLED         = 'FALSE'
     ATTRIBUTE10_ENABLED        = 'FALSE'
     ATTRIBUTE11_ENABLED        = 'FALSE'
     ATTRIBUTE12_ENABLED        = 'FALSE'
     ATTRIBUTE13_ENABLED        = 'FALSE'
     ATTRIBUTE14_ENABLED        = 'FALSE'
     ATTRIBUTE15_ENABLED        = 'FALSE'
     ATTRIBUTE16_ENABLED        = 'FALSE'
     ATTRIBUTE17_ENABLED        = 'FALSE'
     ATTRIBUTE18_ENABLED        = 'FALSE'
     ATTRIBUTE19_ENABLED        = 'FALSE'
     ATTRIBUTE20_ENABLED        = 'FALSE'
     ATTRIBUTE21_ENABLED        = 'FALSE'
     ATTRIBUTE22_ENABLED        = 'FALSE'
     ATTRIBUTE23_ENABLED        = 'FALSE'
     ATTRIBUTE24_ENABLED        = 'FALSE'
     ATTRIBUTE25_ENABLED        = 'FALSE'
     ATTRIBUTE26_ENABLED        = 'FALSE'
     ATTRIBUTE27_ENABLED        = 'FALSE'
     ATTRIBUTE28_ENABLED        = 'FALSE'
     ATTRIBUTE29_ENABLED        = 'FALSE'
     ATTRIBUTE30_ENABLED        = 'FALSE'
     ATTRIBUTE_CATEGORY_VISIBLE = 'FALSE'
     ATTRIBUTE1_VISIBLE         = 'FALSE'
     ATTRIBUTE2_VISIBLE         = 'FALSE'
     ATTRIBUTE3_VISIBLE         = 'FALSE'
     ATTRIBUTE4_VISIBLE         = 'FALSE'
     ATTRIBUTE5_VISIBLE         = 'FALSE'
     ATTRIBUTE6_VISIBLE         = 'FALSE'
     ATTRIBUTE7_VISIBLE         = 'FALSE'
     ATTRIBUTE8_VISIBLE         = 'FALSE'
     ATTRIBUTE9_VISIBLE         = 'FALSE'
     ATTRIBUTE10_VISIBLE        = 'FALSE'
     ATTRIBUTE11_VISIBLE        = 'FALSE'
     ATTRIBUTE12_VISIBLE        = 'FALSE'
     ATTRIBUTE13_VISIBLE        = 'FALSE'
     ATTRIBUTE14_VISIBLE        = 'FALSE'
     ATTRIBUTE15_VISIBLE        = 'FALSE'
     ATTRIBUTE16_VISIBLE        = 'FALSE'
     ATTRIBUTE17_VISIBLE        = 'FALSE'
     ATTRIBUTE18_VISIBLE        = 'FALSE'
     ATTRIBUTE19_VISIBLE        = 'FALSE'
     ATTRIBUTE20_VISIBLE        = 'FALSE'
     ATTRIBUTE21_VISIBLE        = 'FALSE'
     ATTRIBUTE22_VISIBLE        = 'FALSE'
     ATTRIBUTE23_VISIBLE        = 'FALSE'
     ATTRIBUTE24_VISIBLE        = 'FALSE'
     ATTRIBUTE25_VISIBLE        = 'FALSE'
     ATTRIBUTE26_VISIBLE        = 'FALSE'
     ATTRIBUTE27_VISIBLE        = 'FALSE'
     ATTRIBUTE28_VISIBLE        = 'FALSE'
     ATTRIBUTE29_VISIBLE        = 'FALSE'
     ATTRIBUTE30_VISIBLE        = 'FALSE'
     ATTRIBUTE_CATEGORY_VALUE   =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE_CATEGORY')
     ATTRIBUTE1_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE1')
     ATTRIBUTE2_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE2')
     ATTRIBUTE3_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE3')
     ATTRIBUTE4_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE4')
     ATTRIBUTE5_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE5')
     ATTRIBUTE6_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE6')
     ATTRIBUTE7_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE7')
     ATTRIBUTE8_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE8')
     ATTRIBUTE9_VALUE           =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE9')
     ATTRIBUTE10_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE10')
     ATTRIBUTE11_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE11')
     ATTRIBUTE12_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE12')
     ATTRIBUTE13_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE13')
     ATTRIBUTE14_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE14')
     ATTRIBUTE15_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE15')
     ATTRIBUTE16_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE16')
     ATTRIBUTE17_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE17')
     ATTRIBUTE18_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE18')
     ATTRIBUTE19_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE19')
     ATTRIBUTE20_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE20')
     ATTRIBUTE21_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE21')
     ATTRIBUTE22_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE22')
     ATTRIBUTE23_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE23')
     ATTRIBUTE24_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE24')
     ATTRIBUTE25_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE25')
     ATTRIBUTE26_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE26')
     ATTRIBUTE27_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE27')
     ATTRIBUTE28_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE28')
     ATTRIBUTE29_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE29')
     ATTRIBUTE30_VALUE          =  XXAS_GET_PHONE_ATTRIBUTES(PHONE_ID,'ATTRIBUTE30')

    IF Phone_Type_OLD_Value_In <> Phone_Type_Value_In THEN
    (   
            
        PHONE_NUMBER_VALUE = XXAS_GET_PHONE_NUMBER(PERSON_ID,Phone_Type_Value_In,'P')
        PHONE_ID = XXAS_GET_PHONE_NUMBER(PERSON_ID,Phone_Type_Value_In,'Phone_id')
        IF PHONE_NUMBER_VALUE = '-' OR PHONE_NUMBER_VALUE_IN = '-' THEN
        (
            PHONE_NUMBER_VALUE = NULL_VALUE

        )
        
        CHANGE_NUMBER_visible = 'false'
        CHANGE_NUMBER_DESC_VISIBLE = 'false'
        Phone_Type_OLD_Value = Phone_Type_Value_IN
        PHONE_NUMBER_OLD_VALUE = PHONE_NUMBER_VALUE
        IF ISNULL(ATTRIBUTE_CATEGORY_VALUE)= 'Y' THEN
          ( ATTRIBUTE_CATEGORY_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE1_VALUE)= 'Y' THEN
          ( ATTRIBUTE1_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE2_VALUE)= 'Y' THEN
          ( ATTRIBUTE2_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE3_VALUE)= 'Y' THEN
          ( ATTRIBUTE3_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE4_VALUE)= 'Y' THEN
          ( ATTRIBUTE4_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE5_VALUE)= 'Y' THEN
          ( ATTRIBUTE5_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE6_VALUE)= 'Y' THEN
          ( ATTRIBUTE6_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE7_VALUE)= 'Y' THEN
          ( ATTRIBUTE7_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE8_VALUE)= 'Y' THEN
          ( ATTRIBUTE8_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE9_VALUE)= 'Y' THEN
          ( ATTRIBUTE9_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE10_VALUE)= 'Y' THEN
          ( ATTRIBUTE10_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE11_VALUE)= 'Y' THEN
          ( ATTRIBUTE11_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE12_VALUE)= 'Y' THEN
          ( ATTRIBUTE12_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE13_VALUE)= 'Y' THEN
          ( ATTRIBUTE13_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE14_VALUE)= 'Y' THEN
          ( ATTRIBUTE14_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE15_VALUE)= 'Y' THEN
          ( ATTRIBUTE15_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE16_VALUE)= 'Y' THEN
          ( ATTRIBUTE16_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE17_VALUE)= 'Y' THEN
          ( ATTRIBUTE17_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE18_VALUE)= 'Y' THEN
          ( ATTRIBUTE18_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE19_VALUE)= 'Y' THEN
          ( ATTRIBUTE19_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE20_VALUE)= 'Y' THEN
          ( ATTRIBUTE20_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE21_VALUE)= 'Y' THEN
          ( ATTRIBUTE21_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE22_VALUE)= 'Y' THEN
          ( ATTRIBUTE22_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE23_VALUE)= 'Y' THEN
          ( ATTRIBUTE23_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE24_VALUE)= 'Y' THEN
          ( ATTRIBUTE24_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE25_VALUE)= 'Y' THEN
          ( ATTRIBUTE25_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE26_VALUE)= 'Y' THEN
          ( ATTRIBUTE26_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE27_VALUE)= 'Y' THEN
          ( ATTRIBUTE27_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE28_VALUE)= 'Y' THEN
          ( ATTRIBUTE28_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE29_VALUE)= 'Y' THEN
          ( ATTRIBUTE29_VISIBLE = 'FALSE' )
        IF ISNULL(ATTRIBUTE30_VALUE)= 'Y' THEN
          ( ATTRIBUTE30_VISIBLE = 'FALSE' )
    )
  ELSE IF PHONE_NUMBER_VALUE_in <> PHONE_NUMBER_OLD_VALUE_in THEN
  (         ATTRIBUTE_CATEGORY_VISIBLE = 'TRUE'
            ATTRIBUTE1_VISIBLE         = 'TRUE'
            ATTRIBUTE2_VISIBLE         = 'TRUE'
            ATTRIBUTE3_VISIBLE         = 'TRUE'
            ATTRIBUTE4_VISIBLE         = 'TRUE'
            ATTRIBUTE5_VISIBLE         = 'TRUE'
            ATTRIBUTE6_VISIBLE         = 'TRUE'
            ATTRIBUTE7_VISIBLE         = 'TRUE'
            ATTRIBUTE8_VISIBLE         = 'TRUE'
            ATTRIBUTE9_VISIBLE         = 'TRUE'
            ATTRIBUTE10_VISIBLE        = 'TRUE'
            ATTRIBUTE11_VISIBLE        = 'TRUE'
            ATTRIBUTE12_VISIBLE        = 'TRUE'
            ATTRIBUTE13_VISIBLE        = 'TRUE'
            ATTRIBUTE14_VISIBLE        = 'TRUE'
            ATTRIBUTE15_VISIBLE        = 'TRUE'
            ATTRIBUTE16_VISIBLE        = 'TRUE'
            ATTRIBUTE17_VISIBLE        = 'TRUE'
            ATTRIBUTE18_VISIBLE        = 'TRUE'
            ATTRIBUTE19_VISIBLE        = 'TRUE'
            ATTRIBUTE20_VISIBLE        = 'TRUE'
            ATTRIBUTE21_VISIBLE        = 'TRUE'
            ATTRIBUTE22_VISIBLE        = 'TRUE'
            ATTRIBUTE23_VISIBLE        = 'TRUE'
            ATTRIBUTE24_VISIBLE        = 'TRUE'
            ATTRIBUTE25_VISIBLE        = 'TRUE'
            ATTRIBUTE26_VISIBLE        = 'TRUE'
            ATTRIBUTE27_VISIBLE        = 'TRUE'
            ATTRIBUTE28_VISIBLE        = 'TRUE'
            ATTRIBUTE29_VISIBLE        = 'TRUE'
            ATTRIBUTE30_VISIBLE        = 'TRUE'
            NUMBER_PENDING_DESC_VISIBLE= 'FALSE'
        IF XXAS_2FA_CHK_PEND_PHNO(Phone_Type_VALUE_IN,PHONE_NUMBER_VALUE_in) = 'Y' THEN
        (
            NUMBER_PENDING_DESC_VISIBLE = 'TRUE'
            SHOW_SUBMIT ='false'
        )
        ELSE
        (
            ATTRIBUTE_CATEGORY_ENABLED  = 'TRUE'
            ATTRIBUTE1_ENABLED          = 'TRUE'
            ATTRIBUTE2_ENABLED          = 'TRUE'
            ATTRIBUTE3_ENABLED          = 'TRUE'
            ATTRIBUTE4_ENABLED          = 'TRUE'
            ATTRIBUTE5_ENABLED          = 'TRUE'
            ATTRIBUTE6_ENABLED          = 'TRUE'
            ATTRIBUTE7_ENABLED          = 'TRUE'
            ATTRIBUTE8_ENABLED          = 'TRUE'
            ATTRIBUTE9_ENABLED          = 'TRUE'
            ATTRIBUTE10_ENABLED         = 'TRUE'
            ATTRIBUTE11_ENABLED         = 'TRUE'
            ATTRIBUTE12_ENABLED         = 'TRUE'
            ATTRIBUTE13_ENABLED         = 'TRUE'
            ATTRIBUTE14_ENABLED         = 'TRUE'
            ATTRIBUTE15_ENABLED         = 'TRUE'
            ATTRIBUTE16_ENABLED         = 'TRUE'
            ATTRIBUTE17_ENABLED         = 'TRUE'
            ATTRIBUTE18_ENABLED         = 'TRUE'
            ATTRIBUTE19_ENABLED         = 'TRUE'
            ATTRIBUTE20_ENABLED         = 'TRUE'
            ATTRIBUTE21_ENABLED         = 'TRUE'
            ATTRIBUTE22_ENABLED         = 'TRUE'
            ATTRIBUTE23_ENABLED         = 'TRUE'
            ATTRIBUTE24_ENABLED         = 'TRUE'
            ATTRIBUTE25_ENABLED         = 'TRUE'
            ATTRIBUTE26_ENABLED         = 'TRUE'
            ATTRIBUTE27_ENABLED         = 'TRUE'
            ATTRIBUTE28_ENABLED         = 'TRUE'
            ATTRIBUTE29_ENABLED         = 'TRUE'
            ATTRIBUTE30_ENABLED         = 'TRUE'
            CHANGE_NUMBER_visible = 'TRUE'
            CHANGE_NUMBER_REQUIRED = 'TRUE'
            CHANGE_NUMBER_DESC_VISIBLE = 'TRUE'
            CHANGE_NUMBER_list_provider = 'REST:2FA/GetChangeNumberRadio?oldNo=' || (PHONE_NUMBER_OLD_value_IN)||'\&newNo='|| (PHONE_NUMBER_VALUE_in)
            if PHONE_NUMBER_OLD_VALUE_IN =  NULL_VALUE OR PHONE_NUMBER_OLD_VALUE_IN WAS DEFAULTED then
                (
                    CHANGE_NUMBER_visible = 'false'
                    CHANGE_NUMBER_REQUIRED = 'false'
                    CHANGE_NUMBER_DESC_VISIBLE = 'false'
                     CHANGE_NUMBER_value = 'INSERT'
                )

            PHONE_NUMBER_OLD_VALUE_IN = PHONE_NUMBER_VALUE_in
            Phone_Type_OLD_Value_IN = Phone_Type_Value_IN
        )
    )
    
    
    Return PHONE_NUMBER_VALUE   ,
    PHONE_NUMBER_VALUE_in       ,
    CHANGE_NUMBER_VISIBLE       ,
    CHANGE_NUMBER_list_provider ,
    Phone_Type_OLD_Value        ,
    PHONE_NUMBER_OLD_VALUE      ,
    CHANGE_NUMBER_DESC_VISIBLE  ,
    NUMBER_PENDING_DESC_VISIBLE ,
    SHOW_SUBMIT                 ,
    CHANGE_NUMBER_REQUIRED      ,
    PHONE_ID_value              ,
     ATTRIBUTE_CATEGORY_ENABLED ,
     ATTRIBUTE1_ENABLED         ,
     ATTRIBUTE2_ENABLED         ,
     ATTRIBUTE3_ENABLED         ,
     ATTRIBUTE4_ENABLED         ,
     ATTRIBUTE5_ENABLED         ,
     ATTRIBUTE6_ENABLED         ,
     ATTRIBUTE7_ENABLED         ,
     ATTRIBUTE8_ENABLED         ,
     ATTRIBUTE9_ENABLED         ,
     ATTRIBUTE10_ENABLED        ,
     ATTRIBUTE11_ENABLED        ,
     ATTRIBUTE12_ENABLED        ,
     ATTRIBUTE13_ENABLED        ,
     ATTRIBUTE14_ENABLED        ,
     ATTRIBUTE15_ENABLED        ,
     ATTRIBUTE16_ENABLED        ,
     ATTRIBUTE17_ENABLED        ,
     ATTRIBUTE18_ENABLED        ,
     ATTRIBUTE19_ENABLED        ,
     ATTRIBUTE20_ENABLED        ,
     ATTRIBUTE21_ENABLED        ,
     ATTRIBUTE22_ENABLED        ,
     ATTRIBUTE23_ENABLED        ,
     ATTRIBUTE24_ENABLED        ,
     ATTRIBUTE25_ENABLED        ,
     ATTRIBUTE26_ENABLED        ,
     ATTRIBUTE27_ENABLED        ,
     ATTRIBUTE28_ENABLED        ,
     ATTRIBUTE29_ENABLED        ,
     ATTRIBUTE30_ENABLED        ,
     ATTRIBUTE_CATEGORY_VISIBLE ,
     ATTRIBUTE1_VISIBLE         ,
     ATTRIBUTE2_VISIBLE         ,
     ATTRIBUTE3_VISIBLE         ,
     ATTRIBUTE4_VISIBLE         ,
     ATTRIBUTE5_VISIBLE         ,
     ATTRIBUTE6_VISIBLE         ,
     ATTRIBUTE7_VISIBLE         ,
     ATTRIBUTE8_VISIBLE         ,
     ATTRIBUTE9_VISIBLE         ,
     ATTRIBUTE10_VISIBLE        ,
     ATTRIBUTE11_VISIBLE        ,
     ATTRIBUTE12_VISIBLE        ,
     ATTRIBUTE13_VISIBLE        ,
     ATTRIBUTE14_VISIBLE        ,
     ATTRIBUTE15_VISIBLE        ,
     ATTRIBUTE16_VISIBLE        ,
     ATTRIBUTE17_VISIBLE        ,
     ATTRIBUTE18_VISIBLE        ,
     ATTRIBUTE19_VISIBLE        ,
     ATTRIBUTE20_VISIBLE        ,
     ATTRIBUTE21_VISIBLE        ,
     ATTRIBUTE22_VISIBLE        ,
     ATTRIBUTE23_VISIBLE        ,
     ATTRIBUTE24_VISIBLE        ,
     ATTRIBUTE25_VISIBLE        ,
     ATTRIBUTE26_VISIBLE        ,
     ATTRIBUTE27_VISIBLE        ,
     ATTRIBUTE28_VISIBLE        ,
     ATTRIBUTE29_VISIBLE        ,
     ATTRIBUTE30_VISIBLE        ,
     ATTRIBUTE_CATEGORY_VALUE   ,
     ATTRIBUTE1_VALUE           ,
     ATTRIBUTE2_VALUE           ,
     ATTRIBUTE3_VALUE           ,
     ATTRIBUTE4_VALUE           ,
     ATTRIBUTE5_VALUE           ,
     ATTRIBUTE6_VALUE           ,
     ATTRIBUTE7_VALUE           ,
     ATTRIBUTE8_VALUE           ,
     ATTRIBUTE9_VALUE           ,
     ATTRIBUTE10_VALUE          ,
     ATTRIBUTE11_VALUE          ,
     ATTRIBUTE12_VALUE          ,
     ATTRIBUTE13_VALUE          ,
     ATTRIBUTE14_VALUE          ,
     ATTRIBUTE15_VALUE          ,
     ATTRIBUTE16_VALUE          ,
     ATTRIBUTE17_VALUE          ,
     ATTRIBUTE18_VALUE          ,
     ATTRIBUTE19_VALUE          ,
     ATTRIBUTE20_VALUE          ,
     ATTRIBUTE21_VALUE          ,
     ATTRIBUTE22_VALUE          ,
     ATTRIBUTE23_VALUE          ,
     ATTRIBUTE24_VALUE          ,
     ATTRIBUTE25_VALUE          ,
     ATTRIBUTE26_VALUE          ,
     ATTRIBUTE27_VALUE          ,
     ATTRIBUTE28_VALUE          ,
     ATTRIBUTE29_VALUE          ,
     ATTRIBUTE30_VALUE          ,
     CHANGE_NUMBER_value
]';


  BEGIN

    IF l_ff_id IS NULL THEN
      --
      -- Create the Fast Formula
      --
      INSERT INTO ff_formulas_f
        (formula_id
        ,effective_start_date
        ,effective_end_date
        ,business_group_id
        ,formula_type_id
        ,formula_name
        ,description
        ,formula_text)
      VALUES
        (ff_formulas_s.nextval
        ,l_START
        ,l_END
        ,l_BG_ID
        ,l_formula_type_id
        ,l_FF_NAME
        ,l_FF_DESC
        ,l_formula_text);

    ELSE
      --
      -- Update the existing Fast Formula text
      -- (if it has multiple date-track versions the text of
      -- all versions is updated).
      --
      UPDATE ff_formulas_f
      SET    formula_text = l_formula_text
            ,description  = l_FF_DESC
      WHERE  formula_id = l_ff_id;

    END IF;

    COMMIT;

    EXCEPTION
      WHEN others THEN
        ROLLBACK;
        RAISE;
    END;

    --
    -- Compile the Fast Formula
    --
    xxas_util_pkg.compile_ff
      (p_user_id => l_USER_ID
      ,p_ff_type => l_FF_TYPE
      ,p_ff_name => l_FF_NAME);

EXCEPTION

  WHEN e_no_bg_id THEN
    RAISE no_data_found;

  WHEN e_bg_not_exists THEN
    RAISE no_data_found;

  WHEN e_ff_type_not_found THEN
    RAISE no_data_found;

END;
/
COMMIT;
EXIT;
