/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 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_DETAILS';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Seeded new hire detail formula';
  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_NEW_HIRE_DETAILS
Formula Type: QuickPaint
Description:  Seeded personal contact detail formula  

==============================================================================*/
Default for Person_Id                    is -1
Default for NEW_DR_JOB                   is 'Unknown'
Default for NEW_DIRECT_REPORT_Value_In   is -1
Default for IS_MANAGER_Value_In          is 'N'     
Default for DATE_FROM_Value_In           is '0001/01/01 00:00:00' (date)
Default for BUSINESS_GROUP_ID_Value_In is -1
Default for BUSINESS_GROUP_1_Value_In is -1
Default for BUSINESS_GROUP_2_Value_In is -1
Default for PROPOSED_CHANGE_PAY_Value_In is 'N'
Default for Style_Value_in             is 'Unknown'
Default for LASTNAME_Value_In          is 'Unknown'
Default for ORGANIZATION_ID_Value_In   is -1
Default for NEW_DR_EFFECTIVE_DATE_Value_In is '0001/01/01 00:00:00' (date)
Default for START_DATE_ID_VALUE_IN is '0001/01/01 00:00:00' (date)
Default for MATCHES_Value_In             is 'Unknown'
Default for DUP_PERSON_Value_In       is 'Unknown'
Default for NEW_MANAGER_VALUE_In      is -1
Default for CHG_COMP_Value_In         is 'N'

Inputs are  Person_Id, NEW_DR_JOB, NEW_DIRECT_REPORT_Value_In, IS_MANAGER_Value_In,DATE_FROM_Value_In,BUSINESS_GROUP_ID_Value_In, BUSINESS_GROUP_1_Value_In, BUSINESS_GROUP_2_Value_In, PROPOSED_CHANGE_PAY_Value_In, style_value_in, LASTNAME_Value_In, 
ORGANIZATION_ID_Value_In,Person_Id_Value_In, NEW_DR_EFFECTIVE_DATE_Value_In, START_DATE_ID_VALUE_IN, BUSINESS_GROUP_3_Value_In, MATCHES_Value_In, NEW_MANAGER_VALUE_In, CHG_COMP_Value_In

l_return = 0
l_org_id = 0
l_loc_id = 0
l_loc_name = 'Unknown'

IF CHG_COMP_Value_In = 'Y' then
(
  CHANGE_AMOUNT_Visible = 'false'
  CHANGE_EQUIVALENT_Visible = 'false'
  CHANGE_ANNUAL_FTE_Visible = 'false'
)
else
(
/*  CHANGE_AMOUNT_Value = 'XXASNULLVAL'
  CHANGE_EQUIVALENT_Value = 'XXASNULLVAL'
  CHANGE_ANNUAL_FTE_Value = 'XXASNULLVAL' */
  CHANGE_AMOUNT_Required = 'true'
  CHANGE_EQUIVALENT_Required = 'true'
  CHANGE_ANNUAL_FTE_Required = 'true'
)

IF DATE_FROM_Value_In was defaulted then
(
  START_DATE_ID_VALUE = trunc(sysdate)
  NEW_DR_EFFECTIVE_DATE_Value = trunc(sysdate)
)

IF NEW_MANAGER_VALUE_In was defaulted then
(
  NEW_MANAGER_VALUE = Person_Id
)

If IS_MANAGER_Value_In = 'N' then
(
  ASSIGN_NEW_DR_Visible = 'false'
)
If NEW_DIRECT_REPORT_Value_In was not defaulted then
(
NEW_DR_JOB_Value = XXAS_GET_JOB_TITLE(NEW_DIRECT_REPORT_Value_In,trunc(sysdate))
) 

IF BUSINESS_GROUP_1_Value_In was defaulted then 
(
  BUSINESS_GROUP_1_Value = BUSINESS_GROUP_ID_Value_In
  BUSINESS_GROUP_2_Value = BUSINESS_GROUP_ID_Value_In
  BUSINESS_GROUP_3_Value = BUSINESS_GROUP_ID_Value_In
  Style_Value = XXAS_GET_ADDRESS_STYLE(BUSINESS_GROUP_ID_Value_In)
  l_return = XXAS_GET_ORG_LOC(BUSINESS_GROUP_ID_Value_In,l_org_id,l_loc_id,l_loc_name)
  ORGANIZATION_ID_Value = l_org_id
  LOCATION_ID_Value = l_loc_id
  LOCATION_NAME_Value = l_loc_name
  ORGANIZATION_ID_list_provider = 'REST:newhire/organization?bgId=' || to_char(BUSINESS_GROUP_ID_Value_In)

  PAY_BASIS_ID_Value = XXAS_GET_PAY_BASIS_ID(BUSINESS_GROUP_ID_Value_In)
  PAY_BASIS_ID_list_provider = 'REST:newhire/payBasis?bgId=' || to_char(BUSINESS_GROUP_ID_Value_In) 

  PERSON_TYPE_ID_Value = XXAS_GET_PERSON_TYPE_ID(BUSINESS_GROUP_ID_Value_In)
  PERSON_TYPE_ID_list_provider = 'REST:newhire/personType?bgId=' || to_char(BUSINESS_GROUP_ID_Value_In)

  JOB_TITLE_list_provider = 'REST:newhire/job?bgId=' || to_char(BUSINESS_GROUP_ID_Value_In)
  GRADE_LADDER_ID_list_provider = 'REST:newhire/gradeladder?bgId=' || to_char(BUSINESS_GROUP_ID_Value_In)
  ALT_BG_SUPERVISOR_list_provider = 'REST:newhire/AltBGSupervisor?bgId=' || to_char(BUSINESS_GROUP_ID_Value_In)
  
)

If LASTNAME_Value_In was not defaulted then
(
  LAST_NAME_APP_Value = LASTNAME_Value_In
)
/* Added by Vinay  */
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'
    )
  )

If Style_Value_in = 'US' then
  (
  Town_or_City_refresh_on_change = 'true'
  Postal_Code_refresh_on_change = 'true'
  )

If PROPOSED_CHANGE_PAY_Value_In = 'Y' then 
(
  CHANGE_PAY_VISIBLE = 'true'
)

RETURN 
NEW_DR_JOB_Value,
START_DATE_ID_VALUE,
NEW_MANAGER_VALUE,
ASSIGN_NEW_DR_Visible,
BUSINESS_GROUP_1_Value,
BUSINESS_GROUP_2_Value,
BUSINESS_GROUP_3_Value,
CHANGE_PAY_VISIBLE,
Town_or_City_refresh_on_change,
Postal_Code_refresh_on_change,
Style_Value,
Style_Label,
Country_Value,
Country_Visible,
LAST_NAME_APP_Value,
NEW_DR_EFFECTIVE_DATE_Value,
ORGANIZATION_ID_Value,
LOCATION_ID_Value,
LOCATION_NAME_Value,
ORGANIZATION_ID_list_provider,
PAY_BASIS_ID_Value,
PAY_BASIS_ID_list_provider,
PERSON_TYPE_ID_Value,
PERSON_TYPE_ID_list_provider,
CHANGE_AMOUNT_Visible,
CHANGE_EQUIVALENT_Visible,
CHANGE_ANNUAL_FTE_Visible,
/* CHANGE_AMOUNT_Value,
CHANGE_EQUIVALENT_Value,
CHANGE_ANNUAL_FTE_Value, */
CHANGE_AMOUNT_Required,
CHANGE_EQUIVALENT_Required,
CHANGE_ANNUAL_FTE_Required,
JOB_TITLE_list_provider,
GRADE_LADDER_ID_list_provider,
ALT_BG_SUPERVISOR_list_provider

]';


  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;
