/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET ESCAPE '\'
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_VIEW_SAL_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Salary History View';
  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);

  --
  -- 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_EMP_VIEW_SAL_VIEW
Formula Type: Applaud Assignment
Description:  Salary History
==============================================================================*/

  Default for Annualized_Salary            is 0
  Default for Salary_Rate                  is 0
  Default for Salary_Frequency             is 'Unknown'
  Default for Currency                     is 'Unknown'
  Default for Change_Date                  is '0001/01/01 00:00:00' (date)
  Default for Grade                        is 'Unknown'
  Default for Increase_Percentage          is 0
  Default for Increase_Amount              is 0
  Default for Proposal_Reason              is 'Unknown'
  Default for Salary_Review_Period         is 0
  Default for Salary_Review_Frequency      is 'Unknown'
  Default for Next_Review_Date             is '0001/01/01 00:00:00' (date)
  Default for Perf_Review_period           is 0
  Default for Perf_Review_period_Frequency is 'Unknown'
  Default for Payroll_Name                 is 'Unknown'
  Default for Multiple_Components          is 'N'

  Inputs are Annualized_Salary, Salary_Rate, Salary_Frequency, Currency,
             Change_Date, Grade, Increase_Percentage, Increase_Amount,
             Proposal_Reason, Salary_Review_Period, Salary_Review_Frequency,
             Next_Review_Date, Perf_Review_period, Perf_Review_period_Frequency,
             Payroll_Name, Multiple_Components, Assignment_id

  CURRENCY_FORMAT_MASK = 'FM999G999G999G990D00'
  DATE_FORMAT_MASK     = 'FMDD Month YYYY'
  SUMMARY_CONTEXT = ' '
  /*************************************************************************/
  /*  BLOCK #1 - Set the summary contents                                  */
  /*************************************************************************/

   Timeline_Text = to_char(Change_Date, DATE_FORMAT_MASK)
   If Proposal_Reason was not defaulted Then
     (Summary_Context = Proposal_Reason)

   Formatted_Salary = xxas_to_char(Annualized_Salary, CURRENCY_FORMAT_MASK)
   If Currency was not defaulted Then
   (
     Symbol = Currency
     If Currency = 'USD' Then
       (Symbol = '$')
     Else If Currency = 'GBP' Then
       (Symbol = '£')
     Else If Currency = 'EUR' Then
       (Symbol = '€')

     Formatted_Salary = Symbol + ' ' + Formatted_Salary
   )
   Summary_Text = Formatted_Salary

   If Increase_Percentage was not defaulted Then
     (Summary_Decorator = to_char(round(Increase_Percentage, 2)) + '%')

  /*************************************************************************/
  /*  BLOCK #2 - Set the detail fields                                     */
  /*************************************************************************/

  Current_Field_Num = 1

  /* Set the Annual Salary */
  If Annualized_Salary was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, 'Annualized Salary', Formatted_Salary)

  /* Set the Salary Rate */
  If Salary_Rate was not defaulted Then
  (
    Formatted_Salary_Rate = xxas_to_char(Salary_Rate, CURRENCY_FORMAT_MASK)
   If Currency was not defaulted Then
     (Formatted_Salary_Rate = Symbol + ' ' + Formatted_Salary_Rate)
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, Salary_Frequency
      ,Formatted_Salary_Rate)
  )

  /* Set the Increase */
  If Increase_Percentage was not defaulted Or
     Increase_Amount was not defaulted Then
  (
    If Increase_Amount was not defaulted Then
    (
      Formatted_Increase = xxas_to_char(Increase_Amount, CURRENCY_FORMAT_MASK)
      If Currency was not defaulted Then
        (Formatted_Increase = Symbol + ' ' + Formatted_Increase)
      If Increase_Percentage was not defaulted Then
      (
        Formatted_Increase = Formatted_Increase + ' (' + to_char(Increase_Percentage) + '%)'
      )
    ) else
    (
      Formatted_Increase = ' (' + to_char(Increase_Percentage) + '%)'
    )
	IF Multiple_Components = 'Y' Then
	 ( components = xxas_get_sal_components(Assignment_id,Change_Date,Symbol)
	   Formatted_Increase = Formatted_Increase + components
	 )
	
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_CHNG_CHANGE')
      ,Formatted_Increase)
	  
  )

  /* Set the Change Date */
  If Change_Date was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_CHANGE_DATE')
      ,to_char(Change_Date, DATE_FORMAT_MASK))

  /* Set the Proposal Reason */
  If Proposal_Reason was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_REASON')
      ,Proposal_Reason)

  /* Set the Payroll */
  If Payroll_Name was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_PAYROLL')
      ,Payroll_Name)

  /* Set the Grade */
  If Grade was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_GRADE')
      ,Grade)

  /* Set the Salary Review and Period */
  If Salary_Review_Period was not defaulted Then
  (
    Salary_Review = to_char(Salary_Review_Period)
    If Salary_Review_Frequency was not defaulted Then
      (Salary_Review = Salary_Review + ' ' + Salary_Review_Frequency)
    If Next_Review_Date was not defaulted Then
    (
      Next_Review = xxas_get_message
        ('XXAS', 'XXAS_EMP_NEXT_RD'
        ,'NEXT_REVIEW_DATE', to_char(Next_Review_Date, DATE_FORMAT_MASK))
      Salary_Review = Salary_Review + ' (' + Next_Review + ')'
    )
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_REVIEW_PERIOD')
      ,Salary_Review)
  )

  /*************************************************************************/
  /*  RETURN BLOCK - Remove ItemX_Prompt/HTML attributes that aren't used  */
  /*************************************************************************/

  Return Timeline_Text
        ,Summary_Context
        ,Summary_Text
        ,Summary_Decorator

]';


  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;
