/* $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_ENR_EVENT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Sets calendar invite content for ' ||
                                       'Training Enrollment 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_ENR_EVENT
Formula Type: QuickPaint
Description:  Sets calendar invite content for Training Enrollment events.

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


/* +-------------------< SECTION 1: DEFAULTS >-------------------------------+
   Defaults must be specified for every item listed in the Inputs section. When
   a value is not set ont he enrollment 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 Class_or_Session     is ''Unknown''
  Default for Class_Name           is ''Unknown''
  Default for Session_Name         is ''Unknown''
  Default for Course_Name          is ''Unknown''
  Default for Training_Center      is ''Unknown''
  Default for Training_Center_Id   is -1
  Default for Location_Name        is ''Unknown''
  Default for Location_Id          is -1
  Default for Booking_Id           is -1
  Default for Event_Id             is -1
  Default for Start_Datetime       is ''0001/01/01 00:00:00'' (date)
  Default for End_Datetime         is ''4712/12/31 00:00:00'' (date)
  Default for Customer_Id          is -1
  Default for Booking_Status_Type  is ''Unknown''
  Default for Delivery_Mode        is ''Unknown''


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

  Inputs are To_Email, Component, Change_Type,
             Class_or_Session, Class_Name, Session_Name, Course_Name,
             Training_Center, Training_Center_Id,
             Location_Name, Location_Id,
             Booking_Id, Event_Id,
             start_Datetime (date), End_Datetime (date),
             Customer_Id, Booking_Status_Type, Delivery_Mode


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


  /* Default Outputs */
  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     = ''Training''  /* This is the subject of the calendar invite    */
  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.                          */
  Location    = ''Unknown''   /* The location of the course.                   */
  HTML_Body   = ''Unknown''   /* Simple HTML Body.  See below.                 */
  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.                */
  RSVP        = ''Y''         /* Set this to Y for acceptance / decline email */
                            /* to be sent to the From_Email                 */

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

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

  /* See Section 4 for the seeded example of using HTML_Body. */

  /* 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_ENR_FND_MSG1''
     HTML_Message1_Token1_Name  = ''CLASS_NAME''
     HTML_Message1_Token1_Value = Class_Name
     HTML_Message1_Token2_Name  = ''COURSE_NAME''
     HTML_Message1_Token2_Value = Course_Name
     HTML_Message2              = ''XXAS_OUT_ENR_FND_MSG2''
     HTML_Message2_Token1_Name  = ''TRAINING_CENTER''
     HTML_Message2_Token1_Value = Training_Center */

     /* Default variables used to retrieve Resource Information */
     First_Name             = ''Unknown''
     Last_Name              = ''Unknown''
     Trainer_Email          = ''Unknown''
     Trainer_Id             = 0
     Resource_Name          = ''Unknown''
     Resource_Address       = ''Unknown''
     Supplied_Resource_Id   = 0
     result                 = 0

/* +-------------------< SECTION 4: MAIN LOGIC >-----------------------------+
   This contains logic that adjusts the Outputs according to certain
   conditions.  For example, the below prevents invites from being sent for
   e-Learning and CD-ROM delivery modes.                                    */


  /* Send invites depending on the Delivery Mode.  You can update this
     section to include the delivery modes you do not wish to send invites
     for. */
   If Delivery_Mode = ''eLearning'' Or
      Delivery_Mode = ''CD-ROM''
   Then
      (Send = ''No'')

  /* Set the Subject */
  if Class_or_Session = ''C'' Then
    (Subject = ''Training: '' + Course_Name)
  else
    (Subject = ''Training: '' + Course_Name + '' - '' + Session_Name)

  /* For updated or cancelled enrollments, prefix the Subject */
  If Change_Type <> ''New'' Then
    (Subject = Change_Type + '': '' + Subject)

  /* Set the Location */
  If Training_Center was defaulted Then
    (Location = Location_Name)
  Else
    (Location = Training_Center)

  /* Set the HTML Body */
  (HTML_Body = ''<html>Training Enrollment Status: <b>'' +
                Booking_Status_Type + ''</b></html>'')

/* +-------------------< SECTION 4.1 Embed Resource Information        ------+ 
   This section calls the supported Formula Functions to embed information 
   into the formula for resources and trainers. 

   Important: The delivered Applaud Formula Functions only work if you have
   one of each resource type allocated to a class or session. If you have 
   multiple resources for a single type against a single class/session, you
   must code your own Formula Function 

   +-------------------------------------------------------------------------+ */

   /***********************************************************/
   /* Get the name of the trainer. Need a switch depending on */
   /* if someone is internal employee vs external resource    */
   /***********************************************************/

   result = xxas_get_internal_trainer(Event_id, First_Name, Last_Name, 
                                      Trainer_Email, Trainer_Id)
   if result = 1 then
   (
      HTML_Body = HTML_Body + ''<p>Trainer Name: '' + First_Name + '' '' + Last_Name
   )
   else
   (
      /* If no internal trainer found, see if an external one exists */
      result = xxas_get_resource_booking(Event_Id, ''Trainer'', 
                                         Resource_Name, Resource_Address, 
                                         Supplied_Resource_Id)

      if result = 1 then 
      ( HTML_Body = HTML_Body + ''<p>External Trainer Name: '' + Resource_Name)
   )
   
   /* This Code block retrieves details of the resource associated to the class or session       */
   /* The second parameter determines which resource type you are interested in                  */
   /* Only one instance of a given resource type is returned using the default supplied function */
   /* Change the string ''Venue'' to match the name of the Resource Type you want to use           */

   result = xxas_get_resource_booking(Event_id, ''Venue'', 
                                      Resource_Name, Resource_Address,Supplied_Resource_Id)

   if result = 1 then
   (
      /* Only append to body text if Venue found                     */
      /* This section can be repeated for each type of Resource that */
      /* you wish to display in the Calendar Body                    */
      HTML_Body = HTML_Body + ''<p>Venue Name: '' + Resource_Name
   )

/* +-------------------< 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, Tentative, Privacy, Priority
      ,Location, Recur_Daily
      ,HTML_Body, RSVP
   /* ,Override_To_Email */
   /* ,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;
