/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2013 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 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_EMP_PI_CON_PER_EDIT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Personal Information Contact Details EmployeeCenter Edit';
  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);

  --
  -- 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;

  --
  -- 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 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 := '
/*==============================================================================

Formula Name: XXAS_EMP_PI_CON_PER_EDIT
Formula Type: Applaud Person
Description:  Personal Information Contact Details EmployeeCenter Edit

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

Default for Emrg_Contact_Flag_Value_in is ''N''

Inputs are Emrg_Contact_Flag_Value_in

/* Hide all flexfield segments by default */
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''

Per_Information_Category_Visible = ''false''
Per_Information1_Visible = ''false''
Per_Information2_Visible = ''false''
Per_Information3_Visible = ''false''
Per_Information4_Visible = ''false''
Per_Information5_Visible = ''false''
Per_Information6_Visible = ''false''
Per_Information7_Visible = ''false''
Per_Information8_Visible = ''false''
Per_Information9_Visible = ''false''
Per_Information10_Visible = ''false''
Per_Information11_Visible = ''false''
Per_Information12_Visible = ''false''
Per_Information13_Visible = ''false''
Per_Information14_Visible = ''false''
Per_Information15_Visible = ''false''
Per_Information16_Visible = ''false''
Per_Information17_Visible = ''false''
Per_Information18_Visible = ''false''
Per_Information19_Visible = ''false''
Per_Information20_Visible = ''false''
Per_Information21_Visible = ''false''
Per_Information22_Visible = ''false''
Per_Information23_Visible = ''false''
Per_Information24_Visible = ''false''
Per_Information25_Visible = ''false''
Per_Information26_Visible = ''false''
Per_Information27_Visible = ''false''
Per_Information28_Visible = ''false''
Per_Information29_Visible = ''false''
Per_Information30_Visible = ''false''

Cont_Attribute_Category_Visible = ''false''
Cont_Attribute1_Visible = ''false''
Cont_Attribute2_Visible = ''false''
Cont_Attribute3_Visible = ''false''
Cont_Attribute4_Visible = ''false''
Cont_Attribute5_Visible = ''false''
Cont_Attribute6_Visible = ''false''
Cont_Attribute7_Visible = ''false''
Cont_Attribute8_Visible = ''false''
Cont_Attribute9_Visible = ''false''
Cont_Attribute10_Visible = ''false''
Cont_Attribute11_Visible = ''false''
Cont_Attribute12_Visible = ''false''
Cont_Attribute13_Visible = ''false''
Cont_Attribute14_Visible = ''false''
Cont_Attribute15_Visible = ''false''
Cont_Attribute16_Visible = ''false''
Cont_Attribute17_Visible = ''false''
Cont_Attribute18_Visible = ''false''
Cont_Attribute19_Visible = ''false''
Cont_Attribute20_Visible = ''false''

Cont_Information_Category_Visible = ''false''
Cont_Information1_Visible = ''false''
Cont_Information2_Visible = ''false''
Cont_Information3_Visible = ''false''
Cont_Information4_Visible = ''false''
Cont_Information5_Visible = ''false''
Cont_Information6_Visible = ''false''
Cont_Information7_Visible = ''false''
Cont_Information8_Visible = ''false''
Cont_Information9_Visible = ''false''
Cont_Information10_Visible = ''false''
Cont_Information11_Visible = ''false''
Cont_Information12_Visible = ''false''
Cont_Information13_Visible = ''false''
Cont_Information14_Visible = ''false''
Cont_Information15_Visible = ''false''
Cont_Information16_Visible = ''false''
Cont_Information17_Visible = ''false''
Cont_Information18_Visible = ''false''
Cont_Information19_Visible = ''false''
Cont_Information20_Visible = ''false''

  /* Toggle the display of the primary flag */
  If Emrg_Contact_Flag_Value_in = ''Y'' Then
    (Primary_Contact_Flag_Visible = ''true'')
  Else
    (Primary_Contact_Flag_Visible = ''false'')

  /* Set the NI label */
  NI_Label = xxas_national_identifier_label()
  National_Identifier_Label = NI_Label

  /*Set Contact Address FF to reduce maxlength*/
  CONTACT_ADDRESSES_Formula_Name = ''XXAS_EMP_CONTACT_ADDRESSES''
 
Return 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,
       Per_Information_Category_Visible,
       Per_Information1_Visible,
       Per_Information2_Visible,
       Per_Information3_Visible,
       Per_Information4_Visible,
       Per_Information5_Visible,
       Per_Information6_Visible,
       Per_Information7_Visible,
       Per_Information8_Visible,
       Per_Information9_Visible,
       Per_Information10_Visible,
       Per_Information11_Visible,
       Per_Information12_Visible,
       Per_Information13_Visible,
       Per_Information14_Visible,
       Per_Information15_Visible,
       Per_Information16_Visible,
       Per_Information17_Visible,
       Per_Information18_Visible,
       Per_Information19_Visible,
       Per_Information20_Visible,
       Per_Information21_Visible,
       Per_Information22_Visible,
       Per_Information23_Visible,
       Per_Information24_Visible,
       Per_Information25_Visible,
       Per_Information26_Visible,
       Per_Information27_Visible,
       Per_Information28_Visible,
       Per_Information29_Visible,
       Per_Information30_Visible,
       Cont_Attribute1_Visible,
       Cont_Attribute2_Visible,
       Cont_Attribute3_Visible,
       Cont_Attribute4_Visible,
       Cont_Attribute5_Visible,
       Cont_Attribute6_Visible,
       Cont_Attribute7_Visible,
       Cont_Attribute8_Visible,
       Cont_Attribute9_Visible,
       Cont_Attribute10_Visible,
       Cont_Attribute11_Visible,
       Cont_Attribute12_Visible,
       Cont_Attribute13_Visible,
       Cont_Attribute14_Visible,
       Cont_Attribute15_Visible,
       Cont_Attribute16_Visible,
       Cont_Attribute17_Visible,
       Cont_Attribute18_Visible,
       Cont_Attribute19_Visible,
       Cont_Attribute20_Visible,
       Cont_Information_Category_Visible,
       Cont_Information1_Visible,
       Cont_Information2_Visible,
       Cont_Information3_Visible,
       Cont_Information4_Visible,
       Cont_Information5_Visible,
       Cont_Information6_Visible,
       Cont_Information7_Visible,
       Cont_Information8_Visible,
       Cont_Information9_Visible,
       Cont_Information10_Visible,
       Cont_Information11_Visible,
       Cont_Information12_Visible,
       Cont_Information13_Visible,
       Cont_Information14_Visible,
       Cont_Information15_Visible,
       Cont_Information16_Visible,
       Cont_Information17_Visible,
       Cont_Information18_Visible,
       Cont_Information19_Visible,
       Cont_Information20_Visible,
       Primary_Contact_Flag_Visible,
       National_Identifier_Label,
       CONTACT_ADDRESSES_Formula_Name
';


  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;
