/* $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_EMP_PI_ADDR_EDIT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud EmployeeCenter Addresses Edit';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Assignment';
  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_EMP_PI_ADDR_EDIT
Formula Type: Applaud Assignment
Description:  Applaud EmployeeCenter Addresses Edit

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

Default for Primary_Flag_Value_in      is 'N'
Default for Dt_Effective_Date_Value_in is '0001/01/01 00:00:00' (date)
Default for Action_Type_Value_in       is 'Unknown'
Default for Sysdate                    is '0001/01/01 00:00:00' (date)
Default for Date_From_Value_in         is '0001/01/01 00:00:00' (date)
Default for Style_Value_in             is 'Unknown'

Inputs are Primary_Flag_Value_in, Dt_Effective_Date_Value_in, Action_Type_Value_in,
           Date_From_Value_in, Style_Value_in
           
Today = trunc(Sysdate)

If Date_From_Value_in was defaulted then
  (Date_From_Value = Today)

If Dt_Effective_Date_Value_in was defaulted then
  (Dt_Effective_Date_Value = Today)
Else
  (Dt_Effective_Date_Value = Dt_Effective_Date_Value_in)
  
If Action_Type_Value_in = 'CORRECT' then
  (Dt_Effective_Date_Value = Today)
  
Dt_Effective_Date_Refresh_on_Change = 'true'
Action_Type_Visible = 'true'
Action_Type_Enabled = 'true'
Action_Type_Required = 'true'
  
/* Override the primary flag to Y if no other addresses */
Primary_Flag_Value = Primary_Flag_Value_in
Number_of_Addresses = XXAS_EMP_GET_NO_OF_ADDRESSES()
If Number_of_Addresses < 1 Then
  (Primary_Flag_Value = 'Y')
  
If Action_Type_Value_in = 'CHANGE' Then
(
  /* For secondary addresses in CHANGE mode, the end-date is set to
     the date of the new address - 1.  This is needed so the APIs end-date
     the old address and create the new address so do not remove this line. */
  If Primary_Flag_Value_in = 'N' Then
    (Date_To_Value = add_days(Dt_Effective_Date_Value, -1))
    
  If Dt_Effective_Date_Value <= Date_From_Value_in Then
  (
    Dt_Effective_Date_Value = add_days(Date_From_Value_in, 1)
    Dt_Effective_Date_Description =
      '<font color="#FF4000">The earliest date of change is ' +
      to_char(Dt_Effective_Date_Value, 'FMDd Mon YYYY') +
      '</font>'
  )
  
  Dt_Effective_Date_Visible = 'true'
  Dt_Effective_Date_Required = 'true'
  Action_Type_Description = xxas_get_message('XXAS', 'XXAS_EMP_ADDR_CHANGE')
)
Else
(
  Dt_Effective_Date_Visible = 'false'
  Dt_Effective_Date_Required = 'false'
  Action_Type_Description = xxas_get_message('XXAS', 'XXAS_EMP_ADDR_CORRECT')
)
  
Telephone_Number_1_Visible = 'false'
Telephone_Number_2_Visible = 'false'

Style_Label = 'Country'



If Style_Value_in was not defaulted then
  (
  If Style_Value_in <> 'GENERIC' Then
    (
     Country_Value = Style_Value_in
     Underscore_Location = instr(Country_Value, '_')
     If Underscore_Location > 0 Then
       (Country_Value = substr(Country_Value, 1, Underscore_Location - 1))
     Country_Visible = 'false'
    )
  )
  /* ticket 86705 */
If Style_Value_in = 'US' then
  (
  Town_or_City_refresh_on_change = 'true' 
  Postal_Code_refresh_on_change = 'true'
  )
  /* ticket 86705 */
Address_Line1_autocomplete_hint = 'address-line1'
Address_Line2_autocomplete_hint = 'address-line2'
Address_Line3_autocomplete_hint = 'address-line3'
 
Return Action_Type_Visible
      ,Action_Type_Enabled
      ,Action_Type_Required
      ,Action_Type_Description
      ,Date_From_Value
      ,Date_To_Value
      ,Dt_Effective_Date_Value
      ,Dt_Effective_Date_Description
      ,Dt_Effective_Date_Refresh_on_Change
      ,Dt_Effective_Date_Visible
      ,Dt_Effective_Date_Required
      ,Telephone_Number_1_Visible
      ,Telephone_Number_2_Visible
      ,Style_Label
      ,Country_Value
      ,Country_Visible
      ,Primary_Flag_Value
	  ,Town_or_City_refresh_on_change
	  ,Postal_Code_refresh_on_change
	  ,Address_Line1_Autocomplete_hint
	  ,Address_Line2_Autocomplete_hint
	  ,Address_Line3_Autocomplete_hint
       
]';


  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;
