/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 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_OUT_ABS_EVENT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Sets calendar invite content for Absence events';
  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);

  --
  -- 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 := '
/*============================================================================

Formula Name: XXAS_OUT_ABS_EVENT
Formula Type: QuickPaint
Description:  Sets calendar invite content for Absence events.

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


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

  Default for To_Email                 is ''Unknown''
  Default for Component                is ''Unknown''
  Default for Change_Type              is ''Unknown''
  Default for Absence_Id               is -1
  Default for Absence_Type             is ''Unknown''
  Default for Absence_Reason           is ''Unknown''
  Default for Date_Notification        is ''0001/01/01 00:00:00'' (date)
  Default for Date_Projected_Start     is ''0001/01/01 00:00:00'' (date)
  Default for Time_Projected_Start     is ''Unknown''
  Default for Date_Projected_End       is ''4712/12/31 00:00:00'' (date)
  Default for Time_Projected_End       is ''Unknown''
  Default for Date_Start               is ''0001/01/01 00:00:00'' (date)
  Default for Time_Start               is ''Unknown''
  Default for Date_End                 is ''4712/12/31 00:00:00'' (date)
  Default for Time_End                 is ''Unknown''
  Default for Absence_Days             is 0
  Default for Absence_Hours            is 0
  Default for Old_Absence_Reason       is ''Unknown''
  Default for Old_Date_Notification    is ''0001/01/01 00:00:00'' (date)
  Default for Old_Date_Projected_Start is ''0001/01/01 00:00:00'' (date)
  Default for Old_Time_Projected_Start is ''Unknown''
  Default for Old_Date_Projected_End   is ''4712/12/31 00:00:00'' (date)
  Default for Old_Time_Projected_End   is ''Unknown''
  Default for Old_Date_Start           is ''0001/01/01 00:00:00'' (date)
  Default for Old_Time_Start           is ''Unknown''
  Default for Old_Date_End             is ''4712/12/31 00:00:00'' (date)
  Default for Old_Time_End             is ''Unknown''
  Default for Old_Absence_Days         is 0
  Default for Old_Absence_Hours        is 0


/* +-------------------< SECTION 2: INPUTS >---------------------------------+
   This formula lists all the available items that you can use in this
   type of formula.
   The items beginning with ''Old_'' are only available for Absence Updates,
   ie, when Change_Type = ''Updated''; they will be defaulted for New and
   Cancelled absences.                                                      */

  Inputs are To_Email, Component, Change_Type, Absence_Id, Absence_Type,
             Absence_Reason, Date_Notification (date),
             Date_Projected_Start (date), Time_Projected_Start,
             Date_Projected_End (date), Time_Projected_End,
             Date_Start (date), Time_Start, Date_End (date), Time_End,
             Absence_Days, Absence_Hours,
             Old_Absence_Reason, Old_Date_Notification (date),
             Old_Date_Projected_Start (date), Old_Time_Projected_Start,
             Old_Date_Projected_End (date), Old_Time_Projected_End,
             Old_Date_Start (date), Old_Time_Start,
             Old_Date_End (date), Old_Time_End,
             Old_Absence_Days, Old_Absence_Hours


/* +-------------------< 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. */

  Send               = ''Yes''    /* You should set this to ''Yes'' or ''No''     */
  From_Email         = ''Oracle-HR''
                                /* Set the From email address, typically
                                   your workflow mailer address or a
                                   ''noreply@'' email address.  All calendar
                                   invites will be from this address. If
                                   this value is not returned, the From
                                   email is the same as the recipient.      */
  Subject            = ''Absent'' /* This is the subject of the calendar
                                   invite                                   */
  Free_Busy          = ''Busy''   /* Set this to ''Busy'' or ''Free''.  In
                                   compliant calendars, this controls how
                                   free/busy time is displayed in the user''s
                                   calendar.                                */
  Tentative          = ''No''     /* Set this to ''Yes'' or ''No''.  In compliant
                                   calendars, this will flag the invite as
                                   tentative.                               */
  Privacy            = ''Public'' /* Set to ''Public'', ''Private'' or
                                   ''Confidential''.  In compliant calendars,
                                    this will set its privacy accordingly.  */
  Priority           =  0       /* Set to a value from 0-9. 0 is an
                                   unassigned priority, 1 is the highest
                                   priority and 9 is the lowest priority.   */
  Default_Start_Time = ''08:00''  /* If the absence has no start time, the
                                   calendar invite will start on this time. */
  Default_End_Time   = ''18:00''  /* If the absence has no end time, the
                                   calendar invite will end on this time.   */
  Open_Ended_Days    =  365     /* An End Date for the calendar invite must
                                   be set, so for open-ended absences the
                                   end date is set to this number of days
                                   from the start date.                     */
  Recur_Daily        = ''No''     /* For absences spanning several days this
                                   sets the invite to recur daily rather
                                   than use one long meeting.  This is
                                   particularly useful for Lotus Notes users
                                   because Notes does not support meetings
                                   that are longer than 24 hours.           */

  /* OVERRIDE TO EMAIL: */
  /* Invites are sent to the email address of the person being enrolled or
     the person who is absent (it uses the email against their user if set,
     otherwise it takes it from their person record ).
     If you wish to override this email address you can set it here; you
     should also uncomment it in the Return statement in section 5. */
  /* Override_To_Email = ''somebody@example.org'' */

  /* LOCATION: */
  /* The Location Output is free-text and appears in the Location field in
     your calendar client.  It is not used in the seeded formula: if you 
     wish to use this Output you should uncomment it here and in the Return
     statement in section 5. */
  /* Location = ''Out the Office'' */

  /* TIMEZONE: */
  /* The timezone helps ensure that the times of an absence (including the
     default times above) appear in the calendar correctly when moving to
     and from daylight saving.  Outlook calendar invites will attempt to
     derive the user''s timezone with the following precedence:

       1) The Override_Timezone Output set in this formula
       2) The ''Client Timezone'' profile option at the User level
       3) The timezone of the Location on the User''s HR Assignment
       4) The ''Client Timezone'' profile option at the Site level

     If no timezone is available, floating time is used. This will defer to
     the behaviour of the client''s Calendar application to determine how it
     interprets floating time.  At the time of writing, Outlook 2007 and
     Outlook 2010 do not fully support the ''floating time'' iCalendar
     implementation.

     If all your users are based in the same timezone, you can simply set
     the timezone here and add it to the Return statement.  If your users
     are based in multiple timezones, it is recommended that you set timezone
     on all HR locations.  For a list of available timezones, refer to the
     Integration Guide. */
  /* Override_Timezone = ''Europe/London'' */

  /* HTML BODY: */
  /* The HTML_Body output gives you up to 255 characters of HTML text and
     appears in the calendar invite body.  The 255 character limit includes
     substituted tokens so ensure that this limit is not exceeded - you will
     receive a Fast Formula error if it is.

     If you require more than 255 characters, a more advanced alternative
     is available, which gives you up to 4000 characters of HTML text (this
     limit also includes substituted tokens). This alternative requires you
     to define the HTML text in FND Messages and then set Outputs here
     that define the Message Names, Message Application and Message Tokens.
     Do not return both HTML_Body and the advanced Outputs - if you do this
     only HTML_Body is used. */

  /* The Simple option with a 255 character limit: */
  /* HTML_Body = ''<html><ul><li>Reason: <b>'' + Absence_Reason +
                 ''</b></li></ul></html>'' */

  /* The advanced alternative option with a 4000 character limit:
     The HTML_Message_App is the Application Short Name of the Application
     that owns the FND Message.  You should use your own Custom Application
     and message naming conventions to avoid problems should Applaud
     Solutions happen to deliver a new message with the same name.

     HTML_Message1 is the name of an FND Message that contains the HTML text.
     An FND Message has a 2000 character limit so you can define up to two
     messages here (HTML_Message1 and HTML_Message2).  If you specify both
     messages, they are concatenated together.

     The Tokens are specified as a Name and Value pair.  You can define up to
     10 tokens in each of the two messages.  In the below example, the first
     message specifies two tokens and the second message specifies just
     one.

     In this example, all 9 of the below variables should also be listed in
     the Return statement in Section 5. */
  /* HTML_Message_App           = ''XXAS''
     HTML_Message1              = ''XXAS_OUT_ABS_FND_MSG1''
     HTML_Message1_Token1_Name  = ''ABSENCE_TYPE''
     HTML_Message1_Token1_Value = Absence_Type
     HTML_Message1_Token2_Name  = ''ABSENCE_REASON''
     HTML_Message1_Token2_Value = Absence_Reason
     HTML_Message2              = ''XXAS_OUT_ABS_FND_MSG2''
     HTML_Message2_Token1_Name  = ''DATE_NOTIFIED''
     HTML_Message2_Token1_Value = to_char(Date_Notification, ''DD-MON-YYYY'') */


/* +-------------------< SECTION 4: MAIN LOGIC >-----------------------------+
   This contains logic that adjusts the Outputs according to certain
   conditions.  For example, the below excludes suspension and gardening
   leave, and flags all but annual leave as ''Private''.                      */

  /* Exclude invites for suspension or gardening leave.  You can update this
     section to exclude invites being sent for other types of absence. */
   If upper(Absence_Type) like ''%SUSPENSION%'' Or
      upper(Absence_Type) like ''%GARDENING%'' Then
      (Send = ''No'')

  /* Set all absence types to private except annual leave.  This will only
     take effect in Calendars that support the iCalendar ''privacy''
     standard. */
   If upper(Absence_Type) like ''%HOLIDAY%'' Or
      upper(Absence_Type) like ''%ANNUAL%LEAVE%'' Or
      upper(Absence_Type) like ''%VACATION%'' Then
      (Privacy = ''Public'')
   Else
      (Privacy = ''Private'')

  /* For new absences, set the Subject to the Absence Type */
  If Change_Type = ''New'' Then
    (Subject = Absence_Type)
  Else
    /* For updated or cancelled absences, prefix the Absence Type with the
       type of change. */
    (Subject = Change_Type + '': '' + Absence_Type)

  /* Set to tentative if the absence is projected */
  If Date_Start Was Defaulted Or
    (Date_End Was Defaulted And Date_Projected_End Was Not Defaulted) Then
    (Tentative = ''Yes'')

  /* Do not send the invite if none of the dates have changed.  */
  If Date_Start           = Old_Date_Start And
     Date_End             = Old_Date_End And
     Time_Start           = Old_Time_Start And
     Time_End             = Old_Time_End And
     Date_Projected_Start = Old_Date_Projected_Start And
     Date_Projected_End   = Old_Date_Projected_End And
     Time_Projected_Start = Old_Time_Projected_Start And
     Time_Projected_End   = Old_Time_Projected_End Then
    (Send = ''No'')


/* +-------------------< SECTION 5: RETURN OUTPUTS >-------------------------+
   This section returns the Outputs.  The Outputs that are not used by
   default are commented out.  If you wish to use any of these you should
   uncomment them and set their values further up.                          */

RETURN Send, From_Email, Subject, Free_Busy, Tentative, Privacy, Priority
      ,Default_Start_Time, Default_End_Time, Open_Ended_Days, Recur_Daily
   /* ,Override_To_Email, Location, Override_Timezone */
   /* ,HTML_Body */
   /* ,HTML_Message_App, HTML_Message1 */
   /* ,HTML_Message1_Token1_Name, HTML_Message1_Token1_Value */
   /* ,HTML_Message1_Token2_Name, HTML_Message1_Token2_Value */
   /* ,HTML_Message1_Token3_Name, HTML_Message1_Token3_Value */
   /* ,HTML_Message1_Token4_Name, HTML_Message1_Token4_Value */
   /* ,HTML_Message1_Token5_Name, HTML_Message1_Token5_Value */
   /* ,HTML_Message1_Token6_Name, HTML_Message1_Token6_Value */
   /* ,HTML_Message1_Token7_Name, HTML_Message1_Token7_Value */
   /* ,HTML_Message1_Token8_Name, HTML_Message1_Token8_Value */
   /* ,HTML_Message1_Token9_Name, HTML_Message1_Token9_Value */
   /* ,HTML_Message1_Token10_Name, HTML_Message1_Token10_Value */
   /* ,HTML_Message2 */
   /* ,HTML_Message2_Token1_Name, HTML_Message2_Token1_Value */
   /* ,HTML_Message2_Token2_Name, HTML_Message2_Token2_Value */
   /* ,HTML_Message2_Token3_Name, HTML_Message2_Token3_Value */
   /* ,HTML_Message2_Token4_Name, HTML_Message2_Token4_Value */
   /* ,HTML_Message2_Token5_Name, HTML_Message2_Token5_Value */
   /* ,HTML_Message2_Token6_Name, HTML_Message2_Token6_Value */
   /* ,HTML_Message2_Token7_Name, HTML_Message2_Token7_Value */
   /* ,HTML_Message2_Token8_Name, HTML_Message2_Token8_Value */
   /* ,HTML_Message2_Token9_Name, HTML_Message2_Token9_Value */
   /* ,HTML_Message2_Token10_Name, HTML_Message2_Token10_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;
