/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2017 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_ABSENCE_EVENT_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'View current and historical absences';
  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_ABSENCE_EVENT_VIEW
Formula Type: Applaud Assignment
Description:  Assignment Absence Event EmployeeCenter View

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

  Default for Absence_Type_Id_Value_in      is 0
  Default for Leave_Status_Value_in         is 'CONFIRMED'
  Default for Absence_Attendance_Id         is 0
  Default for Duration_Days_Value_in        is 0
  Default for Duration_Hours_Value_in       is 0
  Default for Transaction_Id_Value_in       is 0
  Default for Date_Time_Range_Value_in      is 'Unknown'
  Default for Date_Range_Value_in           is 'Unknown'

  Inputs are Absence_Type_Id_Value_in
            ,Leave_Status_Value_in
            ,Absence_Attendance_Id
            ,Duration_Days_Value_in
            ,Duration_Hours_Value_in
            ,Transaction_Id_Value_in
            ,Date_Time_Range_Value_in
            ,Date_Range_Value_in

  Replacement_Person_Id_Visible = 'true'
  NULL_VALUE = 'XXASNULLVAL'
  NULL_DATA  = 'XXASNODATA'

  /* Flip these to Yes for PLANNED absence support */
  Leave_Status_Visible = 'true'

  Date_Time_Range_Visible = 'false'
  Date_Time_Range_End_Date_Description = NULL_VALUE
  Date_Range_Visible = 'false'
  Date_Range_End_Date_Description = NULL_VALUE
  Comments_to_Approver_Visible = 'false'
  Attribute_Category_Visible = 'false'
  Attribute1_Visible = 'false'
  Attribute2_Visible = 'false'
  Attribute3_Visible = 'false'
  Attribute4_Visible = 'false'
  Attribute5_Visible = 'false'
  Attribute6_Visible = 'false'
  Attribute7_Visible = 'false'
  Attribute8_Visible = 'false'
  Attribute9_Visible = 'false'
  Attribute10_Visible = 'false'
  Attribute11_Visible = 'false'
  Attribute12_Visible = 'false'
  Attribute13_Visible = 'false'
  Attribute14_Visible = 'false'
  Attribute15_Visible = 'false'
  Attribute16_Visible = 'false'
  Attribute17_Visible = 'false'
  Attribute18_Visible = 'false'
  Attribute19_Visible = 'false'
  Attribute20_Visible = 'false'

  /* Determine if the absence type is set */
  If Absence_Type_Id_Value_in > 0 Then
    (Has_Absence_Type = 'Y')
  Else
    (Has_Absence_Type = 'N')

  /* Absence Status is transient, which means it will be defaulted unless picked by the user or set here */
  If Leave_Status_Value_in was not defaulted Then
    (Leave_Status_Value = Leave_Status_Value_in)
  Else
    (Leave_Status_Value = 'CONFIRMED')

  /* Toggle the visibility of the start date/times */
  If Has_Absence_Type = 'Y' Then
  (
    /* Get absence type information */
    Hours_or_Days = xxas_get_absence_type_value
      (Absence_Type_Id_Value_in, 'HOURS_OR_DAYS')
    Absence_Type = xxas_get_absence_type_value
      (Absence_Type_Id_Value_in, 'NAME')
    Absence_Category = xxas_get_absence_type_value
      (Absence_Type_Id_Value_in, 'ABSENCE_CATEGORY')

    Duration_Type_Value = Hours_or_Days

    If Hours_or_Days = 'H' Or 
       Hours_or_Days = NULL_DATA Then
    (
     Date_Time_Range_Visible = 'true'
    )
    Else
    (
     Date_Range_Visible = 'true'
    )

    Comments_to_Approver_Visible = 'true'
    Attribute_Category_Visible = 'true'
    Attribute1_Visible = 'true'
    Attribute2_Visible = 'true'
    Attribute3_Visible = 'true'
    Attribute4_Visible = 'true'
    Attribute5_Visible = 'true'
    Attribute6_Visible = 'true'
    Attribute7_Visible = 'true'
    Attribute8_Visible = 'true'
    Attribute9_Visible = 'true'
    Attribute10_Visible = 'true'
    Attribute11_Visible = 'true'
    Attribute12_Visible = 'true'
    Attribute13_Visible = 'true'
    Attribute14_Visible = 'true'
    Attribute15_Visible = 'true'
    Attribute16_Visible = 'true'
    Attribute17_Visible = 'true'
    Attribute18_Visible = 'true'
    Attribute19_Visible = 'true'
    Attribute20_Visible = 'true'

    /* Display the absence days or absence hours */
    If Hours_or_Days = 'H' Then
    (
        Duration_Hours_Value = Duration_Hours_Value_in
    )
    Else If Hours_or_Days = NULL_DATA Then
    (
        Duration_Days_Value  = Duration_Days_Value_in
        Duration_Hours_Value = Duration_Hours_Value_in
    )
    Else
    (
        Duration_Days_Value  = Duration_Days_Value_in
    )

    If Hours_or_Days = 'H' Then
    (
        Duration_Hours_Visible = 'true'
    )
    Else If Hours_or_Days = NULL_DATA Then
    (
        Duration_Days_Visible  = 'true'
        Duration_Hours_Visible = 'true'
    )
    Else
    (
        Duration_Days_Visible  = 'true'
    )
  )

  /* Set the approvers for pending transactions */
  If Transaction_Id_Value_in was not defaulted Then
    (Approvers_Visible = 'true')
  Else
    (Approvers_Visible = 'false')

  RETURN Leave_Status_Visible
        ,Leave_Status_Value
        ,Date_Time_Range_Visible
        ,Date_Range_Visible
        ,Date_Time_Range_End_Date_Description
        ,Date_Range_End_Date_Description
        ,Duration_Type_Value
        ,Duration_Days_Visible
        ,Duration_Hours_Visible
        ,Duration_Days_Value
        ,Duration_Hours_Value
        ,Comments_to_Approver_Visible
        ,Attribute_Category_Visible
        ,Attribute1_Visible
        ,Attribute2_Visible
        ,Attribute3_Visible
        ,Attribute4_Visible
        ,Attribute5_Visible
        ,Attribute6_Visible
        ,Attribute7_Visible
        ,Attribute8_Visible
        ,Attribute9_Visible
        ,Attribute10_Visible
        ,Attribute11_Visible
        ,Attribute12_Visible
        ,Attribute13_Visible
        ,Attribute14_Visible
        ,Attribute15_Visible
        ,Attribute16_Visible
        ,Attribute17_Visible
        ,Attribute18_Visible
        ,Attribute19_Visible
        ,Attribute20_Visible
        ,Replacement_Person_Id_Visible
        ,Approvers_Visible

]';


  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;
