/* $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('1900-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_EMP_VIEW_ACCRUAL_BAL';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'View Accrual Balance (Sample)';
  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 := '
/*==============================================================================

Formula Name: XXAS_EMP_VIEW_ACCRUAL_BAL
Formula Type: Applaud Assignment
Description:  Applaud EmployeeCenter View: Accrual Balance

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

  Alias Sysdate as Todays_Date

  Default for Sysdate is ''0001/01/01 00:00:00'' (date)
 
  /* By default this uses a concentric pie chart. If you fancy a stacked bar chart,
     just change this to ''BAR'' */
  CHART_TYPE = ''PIE''
 
  /* Define the holiday year.  This formula uses a fixed calendar year */
  This_Year          = to_char(Todays_Date, ''YYYY'')
  Start_of_Year      = to_date(This_Year + ''0101'', ''YYYYMMDD'')
  End_of_Year        = to_date(This_Year + ''1231'', ''YYYYMMDD'')
  
  Accrual_Plan_Id    = 0
  Accrual_Plan_Name  = ''Unknown''
  Accrual_UOM        = ''Unknown''
  
  Is_On_Plan = xxas_get_accrual_plan(Accrual_Plan_Id, Accrual_Plan_Name, Accrual_UOM)

  /* Is_On_Plan will be zero if this assignment has an Accrual Plan enrollment
     and 1 if not. */
  If Is_On_Plan <> 0 Then
  (
    /* No accrual plans */
    HTML = xxas_emp_set_long_output
         (''HTML'', ''XXAS'', ''XXAS_EMP_VIEW_NO_ACC_BAL'')    
  )
  Else
  (
    /* This employee is on an accrual plan, fetch the various details */
    
    Entitlement   = xxas_get_gross_accrual_balance(Accrual_Plan_Name)
    Carry_Over    = xxas_get_carry_over(Accrual_Plan_Id, End_of_Year, Start_of_Year)
    Holiday_Taken = xxas_get_absence(Accrual_Plan_Id, End_of_Year, Start_of_Year)
    Adjustments   = xxas_get_other_net_contribution(Accrual_Plan_Id, End_of_Year, Start_of_Year)
  
    /* Adjustments are other net calculations. If these sum to a negative number we
       deduct from the amount taken.  If they sum to a positive number we add
       to the entitlement */
    If Adjustments < 0 then 
    (
      Total_Taken = Holiday_Taken + (Adjustments * -1)
      Remaining = (Entitlement + Carry_Over) - Total_taken
    ) 
    Else
    (
      Total_Taken = Holiday_Taken
      Remaining = (Entitlement + Carry_Over + Adjustments) - Holiday_Taken
    )
    
    Overall_Entitlement = Total_Taken + Remaining
  
    /* Define the various messages for singular/plural values */
    If Accrual_UOM = ''D'' Then
    (
      Singular_Text = xxas_get_message(''XXAS'', ''XXAS_COM_ONE_DAY'')
      Plural_Message = ''XXAS_COM_NUM_DAYS''
    )
    Else
    (
      Singular_Text = xxas_get_message(''XXAS'', ''XXAS_COM_ONE_HOUR'')
      Plural_Message = ''XXAS_COM_NUM_HOURS''
    )
    
    /* Pluralise the various values */
    If Entitlement = 1 Then
      (Entitlement_Text = Singular_Text)
    Else
      (Entitlement_Text = xxas_get_message(''XXAS'', Plural_Message, ''NUM'', to_char(Entitlement)))

    If Holiday_Taken = 1 Then
      (Holiday_Taken_Text = Singular_Text)
    Else
      (Holiday_Taken_Text = xxas_get_message(''XXAS'', Plural_Message, ''NUM'', to_char(Holiday_Taken)))

    If Total_Taken = 1 Then
      (Total_Taken_Text = Singular_Text)
    Else
      (Total_Taken_Text = xxas_get_message(''XXAS'', Plural_Message, ''NUM'', to_char(Total_Taken)))
      
    If Carry_Over = 1 Then
      (Carry_Over_Text = Singular_Text)
    Else
      (Carry_Over_Text = xxas_get_message(''XXAS'', Plural_Message, ''NUM'', to_char(Carry_Over)))
    
    If Adjustments = 1 Then
      (Adjustments_Text = Singular_Text)
    Else
      (Adjustments_Text = xxas_get_message(''XXAS'', Plural_Message, ''NUM'', to_char(Adjustments)))

    If Remaining = 1 Then
      (Remaining_Text = Singular_Text)
    Else
      (Remaining_Text = xxas_get_message(''XXAS'', Plural_Message, ''NUM'', to_char(Remaining)))

    If Overall_Entitlement = 1 Then
      (Overall_Entitlement_Text = Singular_Text)
    Else
      (Overall_Entitlement_Text = xxas_get_message(''XXAS'', Plural_Message, ''NUM'', to_char(Overall_Entitlement)))
      
    /* Define the legend values */
    Legend_Total_Taken_Text   = xxas_get_message(''XXAS'', ''XXAS_EMP_VIEW_ACC_USED'', ''AMOUNT'', Total_Taken_Text)
    Legend_Holiday_Taken_Text = xxas_get_message(''XXAS'', ''XXAS_EMP_VIEW_ACC_BOOK'', ''AMOUNT'', Holiday_Taken_Text)
    Legend_Entitlement_Text   = xxas_get_message(''XXAS'', ''XXAS_EMP_VIEW_ACC_ENT'' , ''AMOUNT'', Entitlement_Text)
    Legend_Carry_Over_Text    = xxas_get_message(''XXAS'', ''XXAS_EMP_VIEW_ACC_CO''  , ''AMOUNT'', Carry_Over_Text)
    Legend_Adjustments_Text   = xxas_get_message(''XXAS'', ''XXAS_EMP_VIEW_ACC_ADJ'' , ''AMOUNT'', Adjustments_Text)
    Legend_Remaining_Text     = xxas_get_message(''XXAS'', ''XXAS_EMP_VIEW_ACC_REM'' , ''AMOUNT'', Remaining_Text)

    /* Construct the name of the FND message for the chart */
    Chart_Message_Prefix = ''XXAS_EMP_VIEW_ACC_''
    If Adjustments < 0 then 
    (
      Positive_Negative_Part = ''NEG_''
      Adjustments_Token = Adjustments * -1
    )
    Else
    (
      Positive_Negative_Part = ''POS_''
      Adjustments_Token = Adjustments
    )
    
    Chart_Message = Chart_Message_Prefix + Positive_Negative_Part + CHART_TYPE

    /* Show the HTML for this view */
    Html = xxas_emp_set_long_output
      (''HTML'', ''XXAS'', Chart_Message
      ,''PLAN_NAME'', Accrual_Plan_Name
      ,''OVERALL_ENTITLEMENT_TEXT'', Overall_Entitlement_Text
      ,''TOTAL_TAKEN_TEXT'', Total_taken_Text
      ,''REMAINING_TEXT'', Remaining_Text
      ,''ENTITLEMENT'', to_char(Entitlement)
      ,''LEGEND_ENTITLEMENT_TEXT'', Legend_Entitlement_Text
      ,''CARRY_OVER'', to_char(Carry_Over)
      ,''LEGEND_CARRY_OVER_TEXT'', Legend_Carry_Over_Text
      ,''ADJUSTMENTS'', to_char(Adjustments_Token)
      ,''LEGEND_ADJUSTMENTS_TEXT'', Legend_Adjustments_Text
      ,''HOLIDAY_BOOKED'', to_char(Holiday_taken)
      ,''LEGEND_HOLIDAT_TAKEN_TEXT'', Legend_Holiday_taken_Text
      ,''REMAINING'' , to_char(Remaining)
      ,''LEGEND_REMANING_TEXT'', Legend_Remaining_Text)
  )

  /* Return just a single Dummy output; all the real Outputs are returned above with the
     xxas_emp_set_long_output calls above. */
  Dummy_Output = ''Dummy''

  RETURN Dummy_Output
  ';


  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;
