/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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_NEW_HIRE_REVIEW_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'New Hire Review View';
  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 := q'[
/*==============================================================================
Formula Name: XXAS_NEWHIRE_REVIEW_VIEW 
Formula Type: Applaud Assignment
Description:  New Hire Review View 
==============================================================================*/

  Default for Address_Type        is 'Unknown'
  Default for Address_Line1       is 'Unknown'
  Default for Address_Line2       is 'Unknown'
  Default for Address_Line3       is 'Unknown'
  Default for Town_or_City        is 'Unknown'
  Default for Region_1            is 'Unknown'
  Default for Region_2            is 'Unknown'
  Default for Region_3            is 'Unknown'
  Default for Postal_Code         is 'Unknown'
  Default for Country             is 'Unknown'
  Default for telephone_number_1  is 'Unknown'
  Default for telephone_number_2  is 'Unknown'
  Default for telephone_number_3  is 'Unknown'
  Default for Add_Information1    is 'Unknown'
  Default for Add_Information2    is 'Unknown'
  Default for Add_Information3    is 'Unknown'
  Default for Add_Information4    is 'Unknown'
  Default for Add_Information5    is 'Unknown'
  Default for Add_Information6    is 'Unknown'
  Default for Add_Information7    is 'Unknown'
  Default for Add_Information8    is 'Unknown'
  Default for Add_Information9    is 'Unknown'
  Default for Add_Information10   is 'Unknown'
  Default for Add_Information11   is 'Unknown'
  Default for Add_Information12   is 'Unknown'
  Default for Add_Information13   is 'Unknown'
  Default for Add_Information14   is 'Unknown'
  Default for Add_Information15   is 'Unknown'
  Default for Add_Information16   is 'Unknown'
  Default for Add_Information17   is 'Unknown'
  Default for Add_Information18   is 'Unknown'
  Default for Add_Information19   is 'Unknown'
  Default for Add_Information20   is 'Unknown'
  Default for Style               is 'Unknown'
  Default for segment1            is 'Unknown'
  Default for segment2            is 'Unknown'
  Default for segment3            is 'Unknown'
  Default for segment4            is 'Unknown'
  Default for segment5            is 'Unknown'
  Default for segment6            is 'Unknown'
  Default for segment7            is 'Unknown'
  Default for segment8            is 'Unknown'
  Default for segment9            is 'Unknown'
  Default for segment10           is 'Unknown'  
  Default for segment11           is 'Unknown'
  Default for segment12           is 'Unknown'
  Default for segment13           is 'Unknown'
  Default for segment14           is 'Unknown'
  Default for segment15           is 'Unknown'
  Default for segment16           is 'Unknown'
  Default for segment17           is 'Unknown'
  Default for segment18           is 'Unknown'
  Default for segment19           is 'Unknown'
  Default for segment20           is 'Unknown'
  Default for segment21           is 'Unknown'
  Default for segment22           is 'Unknown'
  Default for segment23           is 'Unknown'
  Default for segment24           is 'Unknown'
  Default for segment25           is 'Unknown'
  Default for segment26           is 'Unknown'
  Default for segment27           is 'Unknown'
  Default for segment28           is 'Unknown'
  Default for segment29           is 'Unknown'
  Default for segment30           is 'Unknown'

  Inputs are Address_Type,
             Address_Line1,
             Address_Line2,
             Address_Line3,
             Town_or_City,
             Region_1,
             Region_2,
             Region_3,
             Postal_Code,
             Country,
             telephone_number_1,
             telephone_number_2,
             telephone_number_3,
             Add_Information1,
             Add_Information2,
             Add_Information3,
             Add_Information4,
             Add_Information5,
             Add_Information6,
             Add_Information7,
             Add_Information8,
             Add_Information9,
             Add_Information10,
             Add_Information11,
             Add_Information12,
             Add_Information13,
             Add_Information14,
             Add_Information15,
             Add_Information16,
             Add_Information17,
             Add_Information18,
             Add_Information19,
             Add_Information20,
			       Style,
			 segment1,
			 segment2,
			 segment3,
			 segment4,
			 segment5,
			 segment6,
			 segment7,
			 segment8,
			 segment9,
			 segment10,
			 segment11,
			 segment12,
			 segment13,
			 segment14,
			 segment15,
			 segment16,
			 segment17,
			 segment18,
			 segment19,
			 segment20,
			 segment21,
			 segment22,
			 segment23,
			 segment24,
			 segment25,
			 segment26,
			 segment27,
			 segment28,
			 segment29,
			 segment30

  Address_Type_enabled = 'false'
  Address_Line1_enabled = 'false'
  Address_Line2_enabled = 'false'
  Address_Line3_enabled = 'false'
  Town_or_City_enabled = 'false'
  Region_1_enabled = 'false'
  Region_2_enabled = 'false'
  Region_3_enabled = 'false'
  Postal_Code_enabled = 'false'
  Country_enabled = 'false'
  telephone_number_1_enabled = 'false'
  telephone_number_2_enabled = 'false'
  telephone_number_3_enabled = 'false'
  Add_Information1_enabled = 'false'
  Add_Information2_enabled = 'false'
  Add_Information3_enabled = 'false'
  Add_Information4_enabled = 'false'
  Add_Information5_enabled = 'false'
  Add_Information6_enabled = 'false'
  Add_Information7_enabled = 'false'
  Add_Information8_enabled = 'false'
  Add_Information9_enabled = 'false'
  Add_Information10_enabled = 'false'
  Add_Information11_enabled = 'false'
  Add_Information12_enabled = 'false'
  Add_Information13_enabled = 'false'
  Add_Information14_enabled = 'false'
  Add_Information15_enabled = 'false'
  Add_Information16_enabled = 'false'
  Add_Information17_enabled = 'false'
  Add_Information18_enabled = 'false'
  Add_Information19_enabled = 'false'
  Add_Information20_enabled = 'false'
  Style_enabled = 'false'
  segment1_enabled = 'false'
  segment2_enabled = 'false'
  segment3_enabled = 'false'
  segment4_enabled = 'false'
  segment5_enabled = 'false'
  segment6_enabled = 'false'
  segment7_enabled = 'false'
  segment8_enabled = 'false'
  segment9_enabled = 'false'
  segment10_enabled = 'false'
  segment11_enabled = 'false'
  segment12_enabled = 'false'
  segment13_enabled = 'false'
  segment14_enabled = 'false'
  segment15_enabled = 'false'
  segment16_enabled = 'false'
  segment17_enabled = 'false'
  segment18_enabled = 'false'
  segment19_enabled = 'false'
  segment20_enabled = 'false'
  segment21_enabled = 'false'
  segment22_enabled = 'false'
  segment23_enabled = 'false'
  segment24_enabled = 'false'
  segment25_enabled = 'false'
  segment26_enabled = 'false'
  segment27_enabled = 'false'
  segment28_enabled = 'false'
  segment29_enabled = 'false'
  segment30_enabled = 'false'

  If Address_Type was defaulted then 
  (
    Address_Type_Visible = 'false'
  )
  If Address_Line1 was defaulted then 
  (
    Address_Line1_Visible = 'false'
  )
  If Address_Line2 was defaulted then 
  (
    Address_Line2_Visible = 'false'
  )
  If Address_Line3 was defaulted then 
  (
    Address_Line3_Visible = 'false'
  )
  If Town_or_City was defaulted then 
  (
    Town_or_City_Visible = 'false'
  )
  If Region_1 was defaulted then 
  (
    Region_1_Visible = 'false'
  )
  If Region_2 was defaulted then 
  (
    Region_2_Visible = 'false'
  )
  If Region_3 was defaulted then 
  (
    Region_3_Visible = 'false'
  )
  If Postal_Code was defaulted then 
  (
    Postal_Code_Visible = 'false'
  )

  Country_Visible = 'false'

  If telephone_number_1 was defaulted then 
  (
    telephone_number_1_Visible = 'false'
  )
  If telephone_number_2 was defaulted then 
  (
    telephone_number_2_Visible = 'false'
  )
  If telephone_number_3 was defaulted then 
  (
    telephone_number_3_Visible = 'false'
  )
  If Add_Information1 was defaulted then 
  (
    Add_Information1_Visible = 'false'
  )
  If Add_Information2 was defaulted then 
  (
    Add_Information2_Visible = 'false'
  )
  If Add_Information3 was defaulted then 
  (
    Add_Information3_Visible = 'false'
  )
  If Add_Information4 was defaulted then 
  (
    Add_Information4_Visible = 'false'
  )
  If Add_Information5 was defaulted then 
  (
    Add_Information5_Visible = 'false'
  )
  If Add_Information6 was defaulted then 
  (
    Add_Information6_Visible = 'false'
  )
  If Add_Information7 was defaulted then 
  (
    Add_Information7_Visible = 'false'
  )
  If Add_Information8 was defaulted then 
  (
    Add_Information8_Visible = 'false'
  )
  If Add_Information9 was defaulted then 
  (
    Add_Information9_Visible = 'false'
  )
  If Add_Information10 was defaulted then 
  (
    Add_Information10_Visible = 'false'
  )
  If Add_Information11 was defaulted then 
  (
    Add_Information11_Visible = 'false'
  )
  If Add_Information12 was defaulted then 
  (
    Add_Information12_Visible = 'false'
  )
  If Add_Information13 was defaulted then 
  (
    Add_Information13_Visible = 'false'
  )
  If Add_Information4 was defaulted then 
  (
    Add_Information14_Visible = 'false'
  )
  If Add_Information15 was defaulted then 
  (
    Add_Information15_Visible = 'false'
  )
  If Add_Information16 was defaulted then 
  (
    Add_Information16_Visible = 'false'
  )
  If Add_Information17 was defaulted then 
  (
    Add_Information17_Visible = 'false'
  )
  If Add_Information18 was defaulted then 
  (
    Add_Information18_Visible = 'false'
  )
  If Add_Information19 was defaulted then 
  (
    Add_Information19_Visible = 'false'
  )
  If Add_Information20 was defaulted then 
  (
    Add_Information20_Visible = 'false'
  )
  If Style was defaulted then 
  (
    Style_Visible = 'false'
  )
  If segment1 was defaulted then 
  (
    segment1_Visible = 'false'
  )
  If segment2 was defaulted then 
  (
    segment2_Visible = 'false'
  )
  If segment3 was defaulted then 
  (
    segment3_Visible = 'false'
  )
  If segment4 was defaulted then 
  (
    segment4_Visible = 'false'
  )
  If segment5 was defaulted then 
  (
    segment5_Visible = 'false'
  )
  If segment6 was defaulted then 
  (
    segment6_Visible = 'false'
  )
  If segment7 was defaulted then 
  (
    segment7_Visible = 'false'
  )
  If segment8 was defaulted then 
  (
    segment8_Visible = 'false'
  )
  If segment9 was defaulted then 
  (
    segment9_Visible = 'false'
  )
  If segment10 was defaulted then 
  (
    segment10_Visible = 'false'
  )
  If segment11 was defaulted then 
  (
    segment11_Visible = 'false'
  )
  If segment12 was defaulted then 
  (
    segment12_Visible = 'false'
  )
  If segment13 was defaulted then 
  (
    segment13_Visible = 'false'
  )
  If segment14 was defaulted then 
  (
    segment14_Visible = 'false'
  )
  If segment15 was defaulted then 
  (
    segment15_Visible = 'false'
  )
  If segment16 was defaulted then 
  (
    segment16_Visible = 'false'
  )
  If segment17 was defaulted then 
  (
    segment17_Visible = 'false'
  )
  If segment18 was defaulted then 
  (
    segment18_Visible = 'false'
  )
  If segment19 was defaulted then 
  (
    segment19_Visible = 'false'
  )
  If segment20 was defaulted then 
  (
    segment20_Visible = 'false'
  )
  If segment21 was defaulted then 
  (
    segment21_Visible = 'false'
  )
  If segment22 was defaulted then 
  (
    segment22_Visible = 'false'
  )
  If segment23 was defaulted then 
  (
    segment23_Visible = 'false'
  )
  If segment24 was defaulted then 
  (
    segment24_Visible = 'false'
  )
  If segment25 was defaulted then 
  (
    segment25_Visible = 'false'
  )
  If segment26 was defaulted then 
  (
    segment26_Visible = 'false'
  )
  If segment27 was defaulted then 
  (
    segment27_Visible = 'false'
  )
  If segment28 was defaulted then 
  (
    segment28_Visible = 'false'
  )
  If segment29 was defaulted then 
  (
    segment29_Visible = 'false'
  )
  If segment30 was defaulted then 
  (
    segment30_Visible = 'false'
  )

  Style_Label = xxas_get_message('XXAS','XXAS_NH_COUNTRY')

  Return Address_Type_enabled,
         Address_Line1_enabled,
         Address_Line2_enabled,
         Address_Line3_enabled,
         Town_or_City_enabled,
         Region_1_enabled,
         Region_2_enabled,
         Region_3_enabled,
         Postal_Code_enabled,
         Country_enabled,
         telephone_number_1_enabled,
         telephone_number_2_enabled,
         telephone_number_3_enabled,
         Add_Information1_enabled,
         Add_Information2_enabled,
         Add_Information3_enabled,
         Add_Information4_enabled,
         Add_Information5_enabled,
         Add_Information6_enabled,
         Add_Information7_enabled,
         Add_Information8_enabled,
         Add_Information9_enabled,
         Add_Information10_enabled,
         Add_Information11_enabled,
         Add_Information12_enabled,
         Add_Information13_enabled,
         Add_Information14_enabled,
         Add_Information15_enabled,
         Add_Information16_enabled,
         Add_Information17_enabled,
         Add_Information18_enabled,
         Add_Information19_enabled,
         Add_Information20_enabled,
		 Style_enabled,
		 Address_Type_Visible,
		 Address_Line1_Visible,
		 Address_Line2_Visible,
		 Address_Line3_Visible,
		 Town_or_City_Visible,
		 Region_1_Visible,
         Region_2_Visible,
         Region_3_Visible,
         Postal_Code_Visible,
         Country_Visible,
         telephone_number_1_Visible,
         telephone_number_2_Visible,
         telephone_number_3_Visible,
         Add_Information1_Visible,
         Add_Information2_Visible,
         Add_Information3_Visible,
         Add_Information4_Visible,
         Add_Information5_Visible,
         Add_Information6_Visible,
         Add_Information7_Visible,
         Add_Information8_Visible,
         Add_Information9_Visible,
         Add_Information10_Visible,
         Add_Information11_Visible,
         Add_Information12_Visible,
         Add_Information13_Visible,
         Add_Information14_Visible,
         Add_Information15_Visible,
         Add_Information16_Visible,
         Add_Information17_Visible,
         Add_Information18_Visible,
         Add_Information19_Visible,
         Add_Information20_Visible,
		     Style_Visible,
		     Style_Label,
         segment1_enabled,
         segment2_enabled,		 
         segment3_enabled,
         segment4_enabled,
		     segment5_enabled,
         segment6_enabled,
		     segment7_enabled,
         segment8_enabled,
		     segment9_enabled,
         segment10_enabled,
		     segment11_enabled,
         segment12_enabled,		 
         segment13_enabled,
         segment14_enabled,
		     segment15_enabled,
         segment16_enabled,
		     segment17_enabled,
         segment18_enabled,
		     segment19_enabled,
         segment20_enabled,
		     segment21_enabled,
         segment22_enabled,		 
         segment23_enabled,
         segment24_enabled,
		     segment25_enabled,
         segment26_enabled,
		     segment27_enabled,
         segment28_enabled,
		     segment29_enabled,
         segment30_enabled,
		     segment1_visible,
         segment2_visible,		 
         segment3_visible,
         segment4_visible,
		     segment5_visible,
         segment6_visible,
		     segment7_visible,
         segment8_visible,
		     segment9_visible,
         segment10_visible,
		     segment11_visible,
         segment12_visible,		 
         segment13_visible,
         segment14_visible,
		     segment15_visible,
         segment16_visible,
		     segment17_visible,
         segment18_visible,
		     segment19_visible,
         segment20_visible,
		     segment21_visible,
         segment22_visible,		 
         segment23_visible,
         segment24_visible,
		     segment25_visible,
         segment26_visible,
		     segment27_visible,
         segment28_visible,
		     segment29_visible,
         segment30_visible	 

	 
]';


  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;
