/* $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_EDIT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud EmployeeCenter Absence Event 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_EMP_ABSENCE_EVENT_EDIT
Formula Type: Applaud Assignment
Description:  Absence Event Edit

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

  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 Date_Time_Range_Value_in         is 'Unknown'
  Default for Date_Range_Value_in              is 'Unknown'
  Default for Startdate_Value_in               is 'Unknown'
  Default for Enddate_Value_in                 is 'Unknown'
  Default for Starttime_Value_in               is '00:00'
  Default for Endtime_Value_in                 is '00:00'
  Default for Assignment_Id_Value_in           is 0
  Default for Accrual_Plan_Id_Value_in         is 0
  Default for Accrual_Plan_Id_List_Provider_in is 'Unknown'

  Inputs are Absence_Type_Id_Value_in
            ,Leave_Status_Value_in
            ,Absence_Attendance_Id
            ,Date_Time_Range_Value_in
            ,Date_Range_Value_in
            ,Startdate_Value_in
            ,Enddate_Value_in
            ,Starttime_Value_in
            ,Endtime_Value_in
            ,Assignment_Id_Value_in
            ,Accrual_Plan_Id_Value_in
            ,Accrual_Plan_Id_List_Provider_in

  Replacement_Person_Id_Visible = 'true'
  Replacement_Person_Id_Enabled = 'true'
  NULL_VALUE = 'XXASNULLVAL'
  NULL_DATA  = 'XXASNODATA'
  Date_Start = NULL_VALUE
  Date_End   = NULL_VALUE
  Time_Start = NULL_VALUE
  Time_End   = NULL_VALUE
  Absence_Days  = 0
  Absence_Hours = 0
  Use_Formula   = 0
  Min_Max_Failure  = NULL_VALUE
  Warning_Or_Error = NULL_VALUE
  Page_Error_Msg   = NULL_VALUE
  Abs_Dur_Auto_Overwrite = NULL_VALUE

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

  Date_Time_Range_Visible = 'false'
  Date_Time_Range_Start_Date_Required = 'false'
  Date_Time_Range_End_Date_Required = 'false'
  Date_Time_Range_End_Date_Description = NULL_VALUE
  Date_Range_Visible = 'false'
  Date_Range_Start_Date_Required = 'false'
  Date_Range_End_Date_Required = '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'
      Date_Time_Range_Start_Date_Required = 'true' 
    )
    Else
    (
      Date_Range_Visible = 'true'
      Date_Range_Start_Date_Required = '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'

    /* Make the end date optional for sickness */
    If upper(Absence_Type) like '%SICK%' Or Absence_Category = 'S' Then
     (
      Date_Time_Range_End_Date_Required = 'false'
      Date_Range_End_Date_Required = 'false'
     )
    Else
     (
      Date_Time_Range_End_Date_Required = 'true'
      Date_Range_End_Date_Required = 'true'
     )

    /* Display the absence days or absence hours */
    If Hours_or_Days = 'H' Or 
       Hours_or_Days = NULL_DATA Then
    (
      If Date_Time_Range_Value_in was not defaulted Then
      (
          /* Get the Dates from Date Time Range */
          Result = xxas_abs_dtr_get_dates
            (Date_Time_Range_Value_in,Date_Start,Date_End)
      )
      Else
      (
          Result = 0
          If Startdate_Value_in was not defaulted And 
             Starttime_Value_in was not defaulted Then
          (
              Date_Start = Startdate_Value_in + ' ' + Starttime_Value_in
              Date_Time_Range_Value = Date_Start
              Date_Range_Value = Startdate_Value_in
          )
          Else If Startdate_Value_in was not defaulted Then
          (
              Date_Start = Startdate_Value_in
              Date_Time_Range_Value = Date_Start
              Date_Range_Value = Startdate_Value_in
          )
          Else If Starttime_Value_in was not defaulted Then
          (
              Date_Time_Range_Value = Starttime_Value_in
          )

          If Enddate_Value_in was not defaulted And 
             Endtime_Value_in was not defaulted Then
          (
              Date_End = Enddate_Value_in + ' ' + Endtime_Value_in
              Date_Time_Range_Value = Date_Time_Range_Value + '_' + Date_End
              Date_Range_Value = Date_Range_Value + '_' + Enddate_Value_in
          )
          Else If Enddate_Value_in was not defaulted Then
          (
              Date_End = Enddate_Value_in
              Date_Time_Range_Value = Date_Time_Range_Value + '_' + Date_End
              Date_Range_Value = Date_Range_Value + '_' + Enddate_Value_in
          )
          Else If Endtime_Value_in was not defaulted Then
          (
              Date_Time_Range_Value = Date_Time_Range_Value + '_' + Endtime_Value_in
          )
      )

      If Result = 0 Then 
      (
          If Date_Start <> NULL_VALUE And 
             Date_End   <> NULL_VALUE Then
          (
              Date_Start_p = trunc(to_date(Date_Start,'YYYY-MM-DD HH24:MI'))
              Date_End_p   = trunc(to_date(Date_End,'YYYY-MM-DD HH24:MI'))
              Time_Start   = to_char(to_date(Date_Start,'YYYY-MM-DD HH24:MI'),'HH24:MI')
              Time_End     = to_char(to_date(Date_End,'YYYY-MM-DD HH24:MI'),'HH24:MI')

              /* Calculate the duration hours using standard Oracle functionality */
              Calculated_Duration = xxas_calculate_absence_duration_full
                (Absence_Type_Id_Value_in, Date_Start_p, Date_End_p
                ,Time_Start, Time_End, Absence_Days, Absence_Hours, Use_Formula
                ,Min_Max_Failure, Warning_Or_Error, Page_Error_Msg)
          )
      )

      If Hours_or_Days = 'H' Then
      (
          Duration_Hours_Visible = 'true'

          Abs_Dur_Auto_Overwrite = xxas_get_profile_option('PER_ABSENCE_DURATION_AUTO_OVERWRITE')

          If Abs_Dur_Auto_Overwrite = 'Y' Then
          (
              Duration_Hours_Enabled = 'false'
          )
          Else
          (
              Duration_Hours_Enabled = 'true'
          )
          Duration_Hours_Value   = Absence_Hours
      )
      Else If Hours_or_Days = NULL_DATA Then
      (
          Duration_Days_Visible  = 'true'
          Duration_Hours_Visible = 'true'

          Abs_Dur_Auto_Overwrite = xxas_get_profile_option('PER_ABSENCE_DURATION_AUTO_OVERWRITE')

          If Abs_Dur_Auto_Overwrite = 'Y' Then
          (
              Duration_Days_Enabled  = 'false'
              Duration_Hours_Enabled = 'false'
          )
          Else
          (
              Duration_Days_Enabled  = 'true'
              Duration_Hours_Enabled = 'true'
          )
          Duration_Days_Value    = Absence_Days
          Duration_Hours_Value   = Absence_Hours
      )

    )
    Else
    (
      If Date_Range_Value_in was not defaulted then
      (
          /* Get the Date and Time values from Date Range */
          Result = xxas_abs_dtr_get_dates
            (Date_Range_Value_in,Date_Start,Date_End)
      )
      Else
      (
          Result = 0
          If Startdate_Value_in was not defaulted Then
          (
              Date_Start = Startdate_Value_in
              Date_Range_Value = Date_Start
              Date_Time_Range_Value = Date_Start
          )

          If Enddate_Value_in was not defaulted Then
          (
              Date_End = Enddate_Value_in
              Date_Range_Value = Date_Range_Value + '_' + Date_End
              Date_Time_Range_Value = Date_Time_Range_Value + '_' + Date_End
          )
      )

      If Result = 0 Then 
      (
          If Date_Start <> NULL_VALUE And 
             Date_End   <> NULL_VALUE Then
          (
              Date_Start_p = trunc(to_date(Date_Start,'YYYY-MM-DD HH24:MI'))
              Date_End_p   = trunc(to_date(Date_End,'YYYY-MM-DD HH24:MI'))
              Time_Start   = to_char(to_date(Date_Start,'YYYY-MM-DD HH24:MI'),'HH24:MI')
              Time_End     = to_char(to_date(Date_End,'YYYY-MM-DD HH24:MI'),'HH24:MI')

              /* Calculate the duration hours using standard Oracle functionality */
              Calculated_Duration = xxas_calculate_absence_duration_full
                (Absence_Type_Id_Value_in, Date_Start_p, Date_End_p
                ,Time_Start, Time_End, Absence_Days, Absence_Hours, Use_Formula
                ,Min_Max_Failure, Warning_Or_Error, Page_Error_Msg)
          )
      )

      Abs_Dur_Auto_Overwrite = xxas_get_profile_option('PER_ABSENCE_DURATION_AUTO_OVERWRITE')

      Duration_Days_Visible = 'true'
      If Abs_Dur_Auto_Overwrite = 'Y' Then
      (
          Duration_Days_Enabled = 'false'
      )
      Else
      (
          Duration_Days_Enabled = 'true'
      )
      Duration_Days_Value = Absence_Days

    )

  )
  Else 
  (

      Date_Time_Range_Visible = 'true'
      Date_Time_Range_Start_Date_Required = 'true'

  )

  If Absence_Attendance_Id was not Defaulted Then
  (
    Absence_Type_Id_Enabled = 'false'
    Employees_Enabled       = 'false'
  )

  /* Accrual Balance Chart - Show this only when absence type is linked to an accrual plan 
     the person is enrolled onto as of the Absence End Date(Date_End) */
  If Absence_Type_Id_Value_in was not defaulted And
     Assignment_Id_Value_in was not defaulted Then
  (
      Accrual_Plan_Id     = 0
      Is_On_Plan = xxas_abs_accrual_plan_enr_per_as_of_date
                     (Assignment_Id_Value_in, Date_End, Absence_Type_Id_Value_in, Accrual_Plan_Id)

      If Is_On_Plan <> 0 Then
      (
          Accrual_Plan_Id = 0
      )
      Else If Accrual_Plan_Id_Value_in was not defaulted Then
      (
          Accrual_Plan_Id = Accrual_Plan_Id_Value_in
      )

      If Accrual_Plan_Id = 0 Then
      (
          Projected_Balance_Visible = 'false'
      )
      Else
      (
          Projected_Balance_Visible = 'true'
          Effective_At_Value = Date_End_p

          Accrual_Plan_Name  = 'Unknown'
          Accrual_UOM        = 'Unknown'
          Accrual_UOM_Name   = 'Unknown'
          Start_Date         = '0001/01/01 00:00:00' (date)
          End_Date           = '4712/12/31 00:00:00' (date)
          Gross_Total        = 0
          Entitlement        = 0
          Carry_Over         = 0
          Entitlement_Adjust = 0
          Used_Total         = 0
          Used               = 0
          Pending            = 0
          Used_Adjust        = 0
          Remaining          = 0
          Return_Value = xxas_abs_accrual_plan_details_as_of_date
                           (Assignment_Id_Value_in, Date_End, Accrual_Plan_Id, 'Y', Accrual_Plan_Name, Accrual_UOM, Accrual_UOM_Name
                           ,Start_Date, End_Date, Gross_Total, Entitlement, Carry_Over, Entitlement_Adjust, Used_Total, Used,  Pending
                           ,Used_Adjust, Remaining)

          /* Success in getting Plan details(Return_Value=0) and Accrual plan Balance is less than zero */
          If Return_Value = 0 And
             Remaining < 0 Then
          (
              /* Accrual plan Balance is less than zero */
              HTML_Text_Value = xxas_emp_set_long_output
                     ('HTML_TEXT_VALUE', 'XXAS', 'XXAS_ACC_REM_BAL_LES_THAN_ZERO'
                     ,'DATE', to_char(Effective_At_Value, 'FMDd Mon YYYY'))
              HTML_Text_Visible = 'true'
          )

          Parameters = 'pAssignmentId=' + to_char(Assignment_Id_Value_in) + '&' + 'pEffectiveDate=' + to_char(Effective_At_Value, 'YYYY-MM-DD')
          Parameters = Parameters + '&' + 'pAccrualPlanId=' + to_char(Accrual_Plan_Id)
          Accrual_Chart_Value = Parameters
          Accrual_Plan_Id_Value = Accrual_Plan_Id
          Accrual_Plan_Id_List_Provider = 'REST:jobs/${field.ASSIGNMENT_ID}/accrual-plans/absence-type/' + to_char(Absence_Type_Id_Value_in) + '?effectiveDate=' + to_char(Effective_At_Value, 'YYYY-MM-DD')
      )
  )

  RETURN Leave_Status_Visible
        ,Leave_Status_Value
        ,Leave_Status_Required
        ,Date_Time_Range_Visible
        ,Date_Time_Range_Value
        ,Date_Range_Visible
        ,Date_Range_Value
        ,Date_Time_Range_Start_Date_Required
        ,Date_Range_Start_Date_Required
        ,Date_Time_Range_End_Date_Required
        ,Date_Range_End_Date_Required
        ,Date_Time_Range_End_Date_Description
        ,Date_Range_End_Date_Description
        ,Duration_Type_Value
        ,Duration_Days_Visible
        ,Duration_Hours_Visible
        ,Duration_Days_Enabled
        ,Duration_Hours_Enabled
        ,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
        ,Absence_Type_Id_Enabled
        ,Employees_Enabled
        ,Replacement_Person_Id_Visible
        ,Replacement_Person_Id_Enabled
        ,Projected_Balance_Visible
        ,Effective_At_Value
        ,Accrual_Plan_Id_List_Provider
        ,Accrual_Plan_Id_Value
        ,HTML_Text_Visible
        ,Accrual_Chart_Value

]';


  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;
