/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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_BK_PAY_DETAILS_EDIT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud Block Pay details 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_BK_PAY_DETAILS_EDIT
Formula Type: Applaud Assignment
Description:  Applaud Block Pay details Edit
==============================================================================*/

Default for Pay_Transaction_Id is -1
Default for Transaction_Id is -1
Default for Pay_Basis_Id_Value_In is -1
Default for Pay_Increase_By_Value_In is 'Unknown'
Default for Change_Amount_Value_In is -1
Default for Change_Percent_Value_In is -1
Default for Change_Equivalent_Value_In is -1
Default for T_Change_Amount_Value_In is -987123654
Default for T_Change_Percent_Value_In is -987123654
Default for T_Change_Equivalent_Value_In is -987123654
Default for T_Change_Percent_Value is -987123654
Default for T_Change_Annual_Fte_Value is -987123654
Default for T_N_INCREASE_AMOUNT_Value_In IS -987123654
Default for T_Comparatio_Value is -987123654
Default for T_Percentile_Value is -987123654
Default for Pay_Annualization_Factor_Value_In is -1
Default for Proposed_Salary_N_Value_In is -1
Default for Annualized_Salary_Value_In is -1
Default for N_Increase_Amount_Value is -1
Default for Salary_Basis_Id_Value_In is -1
Default for Pay_Basis_Value is 'Unknown'
Default for Change_Date_Value_In is '0001/01/01 00:00:00'
Default for Last_Annual_Sal_Value_In is -1
Default for Grade_id is -1

Inputs are Person_Id, Assignment_id, Pay_Proposal_id, Pay_Transaction_Id,Transaction_Id,Pay_Increase_By_Value_In, Change_Amount_Value_In, Change_Percent_Value_In, Change_Equivalent_Value_In,Pay_Annualization_Factor_Value_In,Proposed_Salary_N_Value_In,Annualized_Salary_Value_In,N_Increase_Amount_Value, Pay_Basis_Id_Value_In,Salary_Basis_Id_Value_In,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,T_Change_Amount_Value_In, T_Change_Percent_Value_In, T_Change_Equivalent_Value_In,Change_Date_Value_In,Grade_id, T_Change_Annual_Fte_Value, T_Comparatio_Value, T_Percentile_Value,T_Change_Amount_Value, Last_Annual_Sal_Value_In, T_N_INCREASE_AMOUNT_Value_In

DATE_FORMAT_MASK    = 'FMDD-Mon-YYYY'

Change_Percent_Visible = 'false'
Change_Amount_Visible = 'false'
Change_Equivalent_Visible = 'false'
Change_Fte_Visible = 'false'
Increase_Percentage_Visible = 'false'
Increase_Amount_Visible = 'false'
Proposed_Salary_N_Visible = 'false'

T_Change_Percent_Visible = 'false'
T_Change_Amount_Visible = 'false'
T_Change_Equivalent_Visible = 'false'
Effective_From_Description = 'Cannot be earlier than ' + to_char(to_date(Change_Date_Value_In, 'YYYY/MM/DD HH24:MI:SS'), DATE_FORMAT_MASK)

If Pay_Increase_By_Value_In = 'PERCENT' Then
(
  T_Change_Percent_Visible = 'true'
  T_Change_Percent_Enabled = 'true'
)

If Pay_Increase_By_Value_In = 'PERCENT' AND T_Change_Percent_Value_In Was Not Defaulted Then
(
  Change_Percent_Value = T_Change_Percent_Value_In
  CHANGE_ANNUAL_FTE_Value = -987123654
  N_Increase_Amount_Value = -987123654
  Change_Equivalent_Value = -987123654
  Change_Amount_Value = -987123654
  N_COMPARATIO_Value = -987123654
  N_PERCENTILE_Value = -987123654
  N_QUARTILE_Value = 'XXASNULLVAL'
  N_QUINTILE_Value = 'XXASNULLVAL'
  
  result = xxas_get_new_salary_func(Assignment_id, Pay_Annualization_Factor_Value_In,Pay_Basis_Id_Value_In,Grade_id,Proposed_Salary_N_Value_In,Last_Annual_Sal_Value_In,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,N_Increase_Amount_Value, CHANGE_ANNUAL_FTE_Value, N_COMPARATIO_Value, N_PERCENTILE_Value, N_QUINTILE_Value, N_QUARTILE_Value)
  
  
  Change_Equivalent_Enabled = 'false'
  Change_Amount_Enabled = 'false'
  N_Increase_Amount_Enabled = 'false'
  Change_Annual_Fte_Enabled = 'false'
  Change_Percent_Enabled = 'false'
  N_Comparatio_Enabled = 'false'
  N_Percentile_Enabled = 'false'
  N_Quintile_Enabled = 'false'
  N_Quartile_Enabled = 'false'
  
  Change_Amount_Visible = 'true'
  Change_Equivalent_Visible = 'true'
  N_Increase_Amount_Visible = 'true'

  Change_Annual_Fte_Visible  = 'true'
  N_Comparatio_Visible  = 'true'
  N_Percentile_Visible  = 'true'
  N_Quintile_Visible  = 'true'
  N_Quartile_Visible  = 'true'  
        
)
If Pay_Increase_By_Value_In = 'AMOUNT' Then
(
  T_Change_Amount_Visible = 'true'
  T_Change_Amount_Enabled = 'true'
)
If Pay_Increase_By_Value_In = 'AMOUNT' And T_Change_Amount_Value_In Was not Defaulted Then
(
  Change_Percent_Value =  -987123654
  CHANGE_ANNUAL_FTE_Value = -987123654
  N_Increase_Amount_Value = -987123654
  Change_Equivalent_Value = -987123654
  Change_Amount_Value = T_Change_Amount_Value_In
  N_COMPARATIO_Value = -987123654
  N_PERCENTILE_Value = -987123654
  N_QUARTILE_Value = 'XXASNULLVAL'
  N_QUINTILE_Value = 'XXASNULLVAL'
  
  result = xxas_get_new_salary_func(Assignment_id, Pay_Annualization_Factor_Value_In,Pay_Basis_Id_Value_In,Grade_id,Proposed_Salary_N_Value_In,Last_Annual_Sal_Value_In,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,N_Increase_Amount_Value, CHANGE_ANNUAL_FTE_Value, N_COMPARATIO_Value, N_PERCENTILE_Value, N_QUINTILE_Value, N_QUARTILE_Value)
  
  
  Change_Equivalent_Enabled = 'false'
  Change_Amount_Enabled = 'false'
  N_Increase_Amount_Enabled = 'false'
  Change_Annual_Fte_Enabled = 'false'
  Change_Percent_Enabled = 'false'
  N_Comparatio_Enabled = 'false'
  N_Percentile_Enabled = 'false'
  N_Quintile_Enabled = 'false'
  N_Quartile_Enabled = 'false'
  
  /*Change_Amount_Visible = 'true'*/
  Change_Equivalent_Visible = 'true'
  N_Increase_Amount_Visible = 'true'

  Change_Annual_Fte_Visible  = 'true'
  N_Comparatio_Visible  = 'true'
  N_Percentile_Visible  = 'true'
  N_Quintile_Visible  = 'true'
  N_Quartile_Visible  = 'true'  
          
)

If Pay_Increase_By_Value_In = 'INCREASE_AMOUNT' Then
(
  T_N_INCREASE_AMOUNT_Visible = 'true'
  T_N_INCREASE_AMOUNT_Enabled = 'true'
)
If Pay_Increase_By_Value_In = 'INCREASE_AMOUNT' And T_N_INCREASE_AMOUNT_Value_In Was not Defaulted Then
(
  Change_Percent_Value =  -987123654
  CHANGE_ANNUAL_FTE_Value = -987123654
  N_Increase_Amount_Value = T_N_INCREASE_AMOUNT_Value_In
  Change_Equivalent_Value = -987123654
  Change_Amount_Value = -987123654
  N_COMPARATIO_Value = -987123654
  N_PERCENTILE_Value = -987123654
  N_QUARTILE_Value = 'XXASNULLVAL'
  N_QUINTILE_Value = 'XXASNULLVAL'
  
  result = xxas_get_new_salary_func(Assignment_id, Pay_Annualization_Factor_Value_In,Pay_Basis_Id_Value_In,Grade_id,Proposed_Salary_N_Value_In,Last_Annual_Sal_Value_In,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,N_Increase_Amount_Value, CHANGE_ANNUAL_FTE_Value, N_COMPARATIO_Value, N_PERCENTILE_Value, N_QUINTILE_Value, N_QUARTILE_Value)
  
  
  Change_Equivalent_Enabled = 'false'
  Change_Amount_Enabled = 'false'
  N_Increase_Amount_Enabled = 'false'
  Change_Annual_Fte_Enabled = 'false'
  Change_Percent_Enabled = 'false'
  N_Comparatio_Enabled = 'false'
  N_Percentile_Enabled = 'false'
  N_Quintile_Enabled = 'false'
  N_Quartile_Enabled = 'false'
  
  Change_Amount_Visible = 'true'
  Change_Equivalent_Visible = 'true'
  /*N_Increase_Amount_Visible = 'true'*/

  Change_Annual_Fte_Visible  = 'true'
  N_Comparatio_Visible  = 'true'
  N_Percentile_Visible  = 'true'
  N_Quintile_Visible  = 'true'
  N_Quartile_Visible  = 'true'  
          
)

If Pay_Increase_By_Value_In = 'EQUIVALENT' Then
(
  T_Change_Equivalent_Visible = 'true'
  T_Change_Equivalent_Enabled = 'true'
)

If Pay_Increase_By_Value_In = 'EQUIVALENT' And T_Change_Equivalent_Value_In Was Not Defaulted Then
(
  Change_Percent_Value =  -987123654
  CHANGE_ANNUAL_FTE_Value = -987123654
  N_Increase_Amount_Value = -987123654
  Change_Equivalent_Value = T_Change_Equivalent_Value_In
  Change_Amount_Value = -987123654
  N_COMPARATIO_Value = -987123654
  N_PERCENTILE_Value = -987123654
  N_QUARTILE_Value = 'XXASNULLVAL'
  N_QUINTILE_Value = 'XXASNULLVAL'
  
  result = xxas_get_new_salary_func(Assignment_id, Pay_Annualization_Factor_Value_In,Pay_Basis_Id_Value_In,Grade_id,Proposed_Salary_N_Value_In,Last_Annual_Sal_Value_In,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,N_Increase_Amount_Value, CHANGE_ANNUAL_FTE_Value, N_COMPARATIO_Value, N_PERCENTILE_Value, N_QUINTILE_Value, N_QUARTILE_Value)
  
  
  Change_Equivalent_Enabled = 'false'
  Change_Amount_Enabled = 'false'
  N_Increase_Amount_Enabled = 'false'
  Change_Annual_Fte_Enabled = 'false'
  Change_Percent_Enabled = 'false'
  N_Comparatio_Enabled = 'false'
  N_Percentile_Enabled = 'false'
  N_Quintile_Enabled = 'false'
  N_Quartile_Enabled = 'false'
  
  Change_Amount_Visible = 'true'
  /*Change_Equivalent_Visible = 'true'*/
  N_Increase_Amount_Visible = 'true'

  Change_Annual_Fte_Visible  = 'true'
  N_Comparatio_Visible  = 'true'
  N_Percentile_Visible  = 'true'
  N_Quintile_Visible  = 'true'
  N_Quartile_Visible  = 'true'  
)
If Pay_Increase_By_Value_In = 'FTE' Then
(
  T_CHANGE_ANNUAL_FTE_Visible = 'true'
  T_CHANGE_ANNUAL_FTE_Enabled = 'true'
)

If Pay_Increase_By_Value_In = 'FTE' And T_Change_Annual_Fte_Value Was Not Defaulted Then
(
  Change_Percent_Value =  -987123654
  CHANGE_ANNUAL_FTE_Value = T_Change_Annual_Fte_Value
  N_Increase_Amount_Value = -987123654
  Change_Equivalent_Value = -987123654
  Change_Amount_Value = -987123654
  N_COMPARATIO_Value = -987123654
  N_PERCENTILE_Value = -987123654
  N_QUARTILE_Value = 'XXASNULLVAL'
  N_QUINTILE_Value = 'XXASNULLVAL'
  
  result = xxas_get_new_salary_func(Assignment_id, Pay_Annualization_Factor_Value_In,Pay_Basis_Id_Value_In,Grade_id,Proposed_Salary_N_Value_In,Last_Annual_Sal_Value_In,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,N_Increase_Amount_Value, CHANGE_ANNUAL_FTE_Value, N_COMPARATIO_Value, N_PERCENTILE_Value, N_QUINTILE_Value, N_QUARTILE_Value)
  
  
  Change_Equivalent_Enabled = 'false'
  Change_Amount_Enabled = 'false'
  N_Increase_Amount_Enabled = 'false'
  Change_Annual_Fte_Enabled = 'false'
  Change_Percent_Enabled = 'false'
  N_Comparatio_Enabled = 'false'
  N_Percentile_Enabled = 'false'
  N_Quintile_Enabled = 'false'
  N_Quartile_Enabled = 'false'
  
  Change_Amount_Visible = 'true'
  Change_Equivalent_Visible = 'true'
  N_Increase_Amount_Visible = 'true'

  /*Change_Annual_Fte_Visible  = 'true'*/
  N_Comparatio_Visible  = 'true'
  N_Percentile_Visible  = 'true'
  N_Quintile_Visible  = 'true'
  N_Quartile_Visible  = 'true'  
)

If Pay_Increase_By_Value_In = 'COMPARATIO' Then
(
  IF Grade_Id WAS NOT Defaulted THEN
  (
    T_Comparatio_Visible = 'true'
    T_Comparatio_Enabled = 'true'
  )
  Else
  (
    Pay_Increase_By_Description = 'N/A as Grade is not set'
  )
)

If Pay_Increase_By_Value_In = 'COMPARATIO' And T_Comparatio_Value Was Not Defaulted Then
(
  Change_Percent_Value =  -987123654
  CHANGE_ANNUAL_FTE_Value = -987123654
  N_Increase_Amount_Value = -987123654
  Change_Equivalent_Value = -987123654
  Change_Amount_Value = -987123654
  N_COMPARATIO_Value = T_Comparatio_Value
  N_PERCENTILE_Value = -987123654
  N_QUARTILE_Value = 'XXASNULLVAL'
  N_QUINTILE_Value = 'XXASNULLVAL'
  
  result = xxas_get_new_salary_func(Assignment_id, Pay_Annualization_Factor_Value_In,Pay_Basis_Id_Value_In,Grade_id,Proposed_Salary_N_Value_In,Last_Annual_Sal_Value_In,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,N_Increase_Amount_Value, CHANGE_ANNUAL_FTE_Value, N_COMPARATIO_Value, N_PERCENTILE_Value, N_QUINTILE_Value, N_QUARTILE_Value)
  
  
  Change_Equivalent_Enabled = 'false'
  Change_Amount_Enabled = 'false'
  N_Increase_Amount_Enabled = 'false'
  Change_Annual_Fte_Enabled = 'false'
  Change_Percent_Enabled = 'false'
  N_Comparatio_Enabled = 'false'
  N_Percentile_Enabled = 'false'
  N_Quintile_Enabled = 'false'
  N_Quartile_Enabled = 'false'
  
  Change_Amount_Visible = 'true'
  Change_Equivalent_Visible = 'true'
  N_Increase_Amount_Visible = 'true'

  Change_Annual_Fte_Visible  = 'true'
  /*N_Comparatio_Visible  = 'true'*/
  N_Percentile_Visible  = 'true'
  N_Quintile_Visible  = 'true'
  N_Quartile_Visible  = 'true'  
)

If Pay_Increase_By_Value_In = 'PERCENTILE' Then
(
  IF Grade_Id WAS NOT Defaulted THEN
  (
    T_Percentile_Visible = 'true'
    T_Percentile_Enabled = 'true'
  )
  Else
  (
    Pay_Increase_By_Description = 'N/A as Grade is not set'
  )  
)

If Pay_Increase_By_Value_In = 'PERCENTILE' And T_Percentile_Value Was Not Defaulted Then
(
  Change_Percent_Value =  -987123654
  CHANGE_ANNUAL_FTE_Value = -987123654
  N_Increase_Amount_Value = -987123654
  Change_Equivalent_Value = -987123654
  Change_Amount_Value = -987123654
  N_COMPARATIO_Value = -987123654
  N_PERCENTILE_Value = T_Percentile_Value
  N_QUARTILE_Value = 'XXASNULLVAL'
  N_QUINTILE_Value = 'XXASNULLVAL'
  
  result = xxas_get_new_salary_func(Assignment_id, Pay_Annualization_Factor_Value_In,Pay_Basis_Id_Value_In,Grade_id,Proposed_Salary_N_Value_In,Last_Annual_Sal_Value_In,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,N_Increase_Amount_Value, CHANGE_ANNUAL_FTE_Value, N_COMPARATIO_Value, N_PERCENTILE_Value, N_QUINTILE_Value, N_QUARTILE_Value)
  
  
  Change_Equivalent_Enabled = 'false'
  Change_Amount_Enabled = 'false'
  N_Increase_Amount_Enabled = 'false'
  Change_Annual_Fte_Enabled = 'false'
  Change_Percent_Enabled = 'false'
  N_Comparatio_Enabled = 'false'
  N_Percentile_Enabled = 'false'
  N_Quintile_Enabled = 'false'
  N_Quartile_Enabled = 'false'
  
  Change_Amount_Visible = 'true'
  Change_Equivalent_Visible = 'true'
  N_Increase_Amount_Visible = 'true'

  Change_Annual_Fte_Visible  = 'true'
  N_Comparatio_Visible  = 'true'
  /*N_Percentile_Visible  = 'true'*/
  N_Quintile_Visible  = 'true'
  N_Quartile_Visible  = 'true'
)
Show_Submit='true'

Return Change_Percent_Visible,Change_Amount_Visible,Change_Equivalent_Visible,Change_Fte_Visible,Increase_Percentage_Visible,Increase_Amount_Visible,Proposed_Salary_N_Visible,Change_Amount_Value, Change_Equivalent_Value,Change_Percent_Value,N_Increase_Amount_Value,   N_Increase_Amount_Visible,Proposed_Salary_N_Value_In,Change_Amount_Value_in,N_Increase_Amount_Enabled,Change_Percent_Enabled,Change_Amount_Enabled,Change_Equivalent_Enabled,/*Pay_Annualization_Factor_Value,*/
  T_CHANGE_ANNUAL_FTE_Enabled,T_Change_Amount_Value, T_Change_Percent_Value, T_Change_Percent_Visible,T_Change_Percent_Enabled,Effective_From_Description, CHANGE_ANNUAL_FTE_Value, N_COMPARATIO_Value, N_PERCENTILE_Value, N_QUINTILE_Value, N_QUARTILE_Value, Change_Annual_Fte_Enabled,  N_Comparatio_Enabled,  N_Percentile_Enabled,  N_Quintile_Enabled,  N_Quartile_Enabled, Change_Annual_Fte_Visible,  N_Comparatio_Visible,  N_Percentile_Visible,  N_Quintile_Visible,  N_Quartile_Visible, T_Change_Amount_Visible, T_Change_Amount_Enabled, T_N_INCREASE_AMOUNT_Visible, T_N_INCREASE_AMOUNT_Enabled, T_Change_Equivalent_Visible, T_Change_Equivalent_Enabled, T_CHANGE_ANNUAL_FTE_Visible,  T_Comparatio_Visible, T_Comparatio_Enabled, T_Percentile_Visible, T_Percentile_Enabled, Pay_Increase_By_Description
  
    ]';


  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;
