/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2010 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_COM_AVAIL_CUSTOM';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Seeded custom availability formula';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'QuickPaint';
  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_COM_AVAIL_CUSTOM
Formula Type: QuickPaint
Description:  Seeded custom availability formula.

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


/* +-------------------< SECTION 1: DEFAULTS >-------------------------------+
   Defaults must be specified for every item listed in the Inputs section. When
   a value is not passed into the formula it will be defaulted to the default
   specified here.                                                          */

  Default for Input_DateTime            is ''4712/12/31 00:00:00'' (date)
  Default for Timezone_Short_Code       is ''Unknown''
  Default for Employee_Flag             is ''N'' /* Y or N */
  Default for CWK_Flag                  is ''N'' /* Y or N */
  Default for Is_Current_Worker         is ''Y'' /* Y or N */
  Default for Is_Ex_Worker              is ''N'' /* Y or N */
  Default for Is_Future_Worker          is ''N'' /* Y or N */
  Default for Asg_Effective_Start_Date  is ''0001/01/01 00:00:00'' (date)
  Default for Asg_Effective_End_Date    is ''4712/12/31 00:00:00'' (date)
  Default for Assignment_Hours          is 40
  Default for Assignment_Frequency      is ''W'' /* Lookup Code in FREQUENCY
                                                  Lookup Type */
  Default for Assignment_Time_Start     is ''09:00''
  Default for Assignment_Time_End       is ''17:00''
  Default for Position_Hours            is 40
  Default for Position_Frequency        is ''W''
  Default for Position_Time_Start       is ''09:00''
  Default for Position_Time_End         is ''17:00''
  Default for Organization_Hours        is 40
  Default for Organization_Frequency    is ''W''
  Default for Organization_Time_Start   is ''09:00''
  Default for Organization_Time_End     is ''17:00''
  Default for Business_Group_Hours      is 40
  Default for Business_Group_Frequency  is ''W''
  Default for Business_Group_Time_Start is ''09:00''
  Default for Business_Group_Time_End   is ''17:00''


/* +-------------------< SECTION 2: INPUTS >---------------------------------+
   This formula lists all the available items that you can use in this
   type of formula.                                                         */

  Inputs are Input_DateTime (date), Timezone_Short_Code, Employee_Flag, CWK_Flag,
             Is_Current_Worker, Is_Ex_Worker, Is_Future_Worker,
             Asg_Effective_Start_Date, Asg_Effective_End_Date,
             Assignment_Hours, Assignment_Frequency,
             Assignment_Time_Start, Assignment_Time_End,
             Position_Hours, Position_Frequency,
             Position_Time_Start, Position_Time_End,
             Organization_Hours, Organization_Frequency,
             Organization_Time_Start, Organization_Time_End,
             Business_Group_Hours, Business_Group_Frequency,
             Business_Group_Time_Start, Business_Group_Time_End


/* +-------------------< SECTION 3: OTHER DEFAULTS >-------------------------+
   It is good practice to define default values for the Formula Outputs and
   other variables that are used in this formula.  This helps to guard against
   empty outputs being returned and keeps the formula tidy.

   The Formula Outputs are the reason for this Formula: it gives you finite
   control to set the Outputs to whatever values you choose.  You can use
   information in the Formula Inputs to conditionally set the Outputs - you
   can see examples of this in Section 4.

   Here they are defaulted to initial values and later tweaked.             */

  Free_Busy      = ''Busy''         /* Choose from Busy, Free, Tentative, Do Not
                                     Disturb, and Unknown. Invisible is not
                                     used here.                             */
  Color          = ''#FF0011''      /* Choose from Aqua, Black, Blue, Fuchsia,
                                     Gray, Green, Lime, Maroon, Navy, Olive,
                                     Purple, Red, Silver, Teal, White, and
                                     Yellow.                                */
  Availability   = ''Placeholder''  /* A title for the Free_Busy status. You can
                                     use any text, such as ''Out the Office''.
                                                                            */
  Description    = ''Placeholder''  /* This is set in section 4 - see below for
                                     details.                               */
  Icon           = ''Timecard''     /* Set to the name of the availability icon
                                     you wish to set. Optional.             */
  Start_DateTime = Input_DateTime /* This is set in section 4 - see below for
                                     details.                               */
  End_DateTime   = Input_DateTime /* This is set in section 4 - see below for
                                     details.                               */


/* +-------------------< SECTION 4: MAIN LOGIC >-----------------------------+
   This contains logic that uses the Input_DateTime to determine whether the
   person is currently scheduled to work or not.                            */

  /* Inherit the normal Start Time */
  If Assignment_Time_Start was not defaulted Then
    (Time_Start = Assignment_Time_Start)
  Else If Position_Time_Start was not defaulted Then
    (Time_Start = Position_Time_Start)
  Else If Organization_Time_Start was not defaulted Then
    (Time_Start = Organization_Time_Start)
  Else If Business_Group_Time_Start was not defaulted Then
    (Time_Start = Business_Group_Time_Start)
  Else
    (Time_Start = ''09:00'')

  /* Inherit the normal End Time */
  If Assignment_Time_End was not defaulted Then
    (Time_End = Assignment_Time_End)
  Else If Position_Time_End was not defaulted Then
    (Time_End = Position_Time_End)
  Else If Organization_Time_End was not defaulted Then
    (Time_End = Organization_Time_End)
  Else If Business_Group_Time_End was not defaulted Then
    (Time_End = Business_Group_Time_End)
  Else
    (Time_End = ''17:00'')

  /* Calculate the day of the week using ISO weeks (so that Monday is
     always 1) and the working day */
  Input_Day  = days_between(trunc(Input_DateTime), trunc(Input_DateTime, ''IW'')) + 1
  Input_Time = to_char(Input_DateTime, ''HH24:MI'')
  Workday_Start = to_date(to_char(Input_DateTime, ''YYYY/MM/DD'') + Time_Start
                          ,''YYYY/MM/DD HH24:MI'')
  Workday_End = to_date(to_char(Input_DateTime, ''YYYY/MM/DD'') + Time_End
                          ,''YYYY/MM/DD HH24:MI'')

  If Is_Current_Worker = ''N'' Then
    (
      If Is_Future_Worker = ''Y'' Then
        (
        Free_Busy      = ''Busy''
        Color          = ''#FF0011''
        Icon           = ''ROSETTE''
        Availability   = xxas_get_message
                         (''XXAS'',''XXAS_EMP_STARTS_ON'',''START_DATE'', to_char
                         (Asg_Effective_Start_Date, ''FMDD Month''))
        Description    = xxas_get_message(''XXAS'',''XXAS_EMP_NEW_JOINER'')
        Start_DateTime = trunc(Input_DateTime)
        End_DateTime   = add_days(Start_DateTime, 30)
        )
      Else
        (
        Free_Busy      = ''Busy''
        Color          = ''#FF0011''
        Icon           = ''WAVE''
        Availability   =  xxas_get_message
                          (''XXAS'',''XXAS_EMP_LEFT_ON'',''LEFT_DATE'', to_char
                          (Asg_Effective_End_Date, ''FMDD Mon YYYY''))
        Description    = xxas_get_message(''XXAS'',''XXAS_EMP_LEFT'')
        Start_DateTime = trunc(Input_DateTime)
        End_DateTime   = add_days(Start_DateTime, 30)
        )
    )
  /* If it''s the weekend, specify that this person is not available */
  Else If Input_Day = 6 Or Input_Day = 7 Then
    (
    Free_Busy    = ''Busy''
    Color        = ''#FF0011''
    Availability = xxas_get_message(''XXAS'',''XXAS_EMP_AWAY'')
    Description  = xxas_get_message(''XXAS'',''XXAS_EMP_NOT_SCHEDULED'')
    Start_DateTime = trunc(Input_DateTime)

    /* Set the end date/time to the end of the weekend */
    If Input_Day = 6 Then
      (End_DateTime = add_days(Start_DateTime, 1))
    Else
      (End_DateTime = Start_DateTime)
    End_DateTime = to_date(to_char(End_DateTime, ''YYYY/MM/DD'') + '' 23:59''
                          ,''YYYY/MM/DD HH24:MI'')
    )
  Else
    ( /* It''s a weekday */
    If Input_DateTime < Workday_Start Then
      (
      Free_Busy    = ''Busy''
      Color        = ''#FF0011''
      Availability = xxas_get_message(''XXAS'',''XXAS_EMP_UNAVAILABLE'')
      Description  = xxas_get_message(''XXAS'',''XXAS_EMP_NOT_SCHEDULED_UNTIL'',
                     ''TIME_START'',Time_Start,''TIMEZONE_SHORT_CODE'',Timezone_Short_Code)
                   
      Start_DateTime = trunc(Input_DateTime)
      End_DateTime = add_days(Workday_Start, (1 / 1440) * -1)
      )
    Else If Input_DateTime >= Workday_Start And
            Input_DateTime <= Workday_End Then
      (
      Free_Busy    = ''Free''
      Icon         = ''Tick''
      Color        = ''#33CC33''
      Availability = xxas_get_message(''XXAS'',''XXAS_EMP_AVAILABLE'')
      Description  = xxas_get_message(''XXAS'',''XXAS_EMP_SCHEDULED'',
                     ''TIME_END'',Time_End,''TIMEZONE_SHORT_CODE'',Timezone_Short_Code)
      Start_DateTime = Workday_Start
      End_DateTime = Workday_End
      )
    Else
      (
      Free_Busy    = ''Busy''
      Color        = ''#FF0011''
      Availability = xxas_get_message(''XXAS'',''XXAS_EMP_UNAVAILABLE'')
      Description  = xxas_get_message(''XXAS'',''XXAS_EMP_SCHEDULED_TO'',
                     ''TIME_END'',Time_End,''TIMEZONE_SHORT_CODE'',Timezone_Short_Code)
      Start_DateTime = add_days(Workday_End, (1 / 1440))
      End_DateTime = to_date(to_char(Workday_End, ''YYYY/MM/DD'') + '' 23:59''
                          ,''YYYY/MM/DD HH24:MI'')
      )
    )


/* +-------------------< SECTION 5: RETURN OUTPUTS >-------------------------+
   This section returns the Outputs.  All 6 Outputs must be returned with
   a value.                                                                 */

RETURN Free_Busy, Color, Availability, Icon, Description,
       Start_DateTime, End_DateTime
';


  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;
