/* $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_CAL_INDIVIDUAL';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Seeded individual calendar';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Person';
  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 := q'[
/*==============================================================================
Formula Name: XXAS_CAL_INDIVIDUAL
Formula Type: Applaud Person
Description:  Seeded individual calendar
==============================================================================*/

  /* Defaults must be specified for all Inputs */

  Default for Event_Type                 is 'Unknown'
  Default for Public_Holiday_Name        is 'Unknown'
  Default for Payroll_Name               is 'Unknown'
  Default for Birthday_Name              is 'Unknown'
  Default for Country                    is 'Unknown'
  Default for Status                     is 'Unknown'
  Default for Status_Code                is 'Unknown'
  Default for Absence_Type               is 'Unknown'
  Default for Absence_Reason             is 'Unknown'
  Default for Absence_Category           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 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 Absence_Days               is 0
  Default for Absence_Hours              is 0
  Default for Hours_Or_Days              is 'Unknown'
  Default for Course_Name                is 'Unknown'
  Default for Class_Name                 is 'Unknown'
  Default for Training_Event_Id          is 0
  Default for Session_Or_Scheduled       is 'Unknown'
  Default for Delivery_Mode              is 'Unknown'
  Default for Training_Duration          is 0
  Default for Training_Duration_Units    is 'Unknown'
  Default for Item_Type                  is 'Unknown'
  Default for Item_Key                   is 'Unknown'
  Default for Person_Id                  is 0
  Default for Assignment_Id              is 0
  Default for Absence_Id                 is 0
  Default for Transaction_Id             is 0
  Default for Transaction_Step_Id        is 0
  Default for Element_Name               is 'Unknown'
  Default for Log_Entry_Id               is 0
  Default for Time_Entry_Duration_Hours  is 0
  Default for Field1                     is 'Unknown'
  Default for Field2                     is 'Unknown'
  Default for Field3                     is 'Unknown'
  Default for Field4                     is 'Unknown'
  Default for Field5                     is 'Unknown'
  Default for Current_Approver_Role      is 'Unknown'
  Default for Override_Approver_Role     is 'Unknown'
  Default for Cost_Alloc_Concat_Segments is 'Unknown'
  Default for Source_Block               is 'Unknown'
  Default for Saved_Search_Id            is 0
  Default for Manager_Id                 is 0
  Default for Payment_Type_Id            is 0
  Default for Otl_Entry_Type             is 'Unknown'
  Default for Otl_Entry_Transferred_To   is 'Unknown'
  Default for Otl_Timecard_Id            is 0
  Default for Otl_Hourstype_Id           is 0
  Default for Otl_Absence_Days           is 0
  Default for Otl_Absence_Hours          is 0
  Default for Otl_Timecard_Ovn           is 0
  Default for Otl_Timecard_Date_From     is '4712/12/31 00:00:00' (date)
  Default for Pending_Clock_Id           is 0
  Default for missed_in_punch_flag       is 'Unknown'
  Default for missed_out_punch_flag      is 'Unknown'  
  
  /* Declare all items that are used. */

  Inputs are Event_Type
            ,Public_Holiday_Name
            ,Payroll_Name
            ,Birthday_Name
            ,Country
            ,Status
            ,Status_Code
            ,Absence_Type
            ,Absence_Reason
            ,Absence_Category
            ,Date_Start (date)
            ,Time_Start
            ,Date_End (date)
            ,Time_End
            ,Date_Projected_Start (date)
            ,Time_Projected_Start
            ,Date_Projected_End (date)
            ,Time_Projected_End
            ,Absence_Days
            ,Absence_Hours
            ,Hours_Or_Days
            ,Course_Name
            ,Class_Name
            ,Training_Event_Id
            ,Session_Or_Scheduled
            ,Delivery_Mode
            ,Training_Duration
            ,Training_Duration_Units
            ,Item_Type
            ,Item_Key
            ,Person_Id
            ,Assignment_Id
            ,Absence_Id
            ,Transaction_Id
            ,Transaction_Step_Id
            ,Element_Name
            ,Log_Entry_Id
            ,Time_Entry_Duration_Hours
            ,Field1
            ,Field2
            ,Field3
            ,Field4
            ,Field5
            ,Current_Approver_Role
            ,Override_Approver_Role
            ,Cost_Alloc_Concat_Segments
            ,Source_Block
            ,Saved_Search_Id
            ,Manager_Id
            ,Payment_Type_Id
            ,Otl_Entry_Type
            ,Otl_Entry_Transferred_To
            ,Otl_Timecard_Id
            ,Otl_Hourstype_Id
            ,Otl_Absence_Days
            ,Otl_Absence_Hours
            ,Otl_Timecard_Ovn
            ,Otl_Timecard_Date_From
			      ,Pending_Clock_Id
			      ,missed_in_punch_flag
			      ,missed_out_punch_flag

  /* Date Formats */
  DATE_FORMAT        = 'Dy ddth Mon'
  DATE_CANONICAL     = 'YYYY-MM-DD'
  DATETIME_CANONICAL = 'YYYY-MM-DD HH24:MI:SS'

  /* Color palette */
  COLOR_WHITE        = '#FFFFFF'
  COLOR_BLACK        = '#000000'
  COLOR_DARK_GREY    = '#585858'
  COLOR_GREY         = XXAS_GET_COLOR_BY_NAME('grey')
  COLOR_GREEN        = XXAS_GET_COLOR_BY_NAME('green')
  COLOR_LIGHT_GREEN  = XXAS_GET_COLOR_BY_NAME('light_green')
  COLOR_LIME         = XXAS_GET_COLOR_BY_NAME('lime')
  COLOR_PURPLE       = XXAS_GET_COLOR_BY_NAME('purple')
  COLOR_RED          = XXAS_GET_COLOR_BY_NAME('red')
  COLOR_CYAN         = XXAS_GET_COLOR_BY_NAME('cyan')
  COLOR_BLUE         = XXAS_GET_COLOR_BY_NAME('blue')
  COLOR_BLUE_GREY    = XXAS_GET_COLOR_BY_NAME('blue_grey')
  COLOR_ORANGE       = XXAS_GET_COLOR_BY_NAME('orange')

  STATUS_DRAFT       = 'positive'
  STATUS_PENDING     = 'energized'
  STATUS_APPROVED    = 'balanced'
  STATUS_TRANSFERRED = COLOR_LIME
  STATUS_RETURNED    = 'assertive'

  /* Defaults for Outputs */
  Start           = Date_Start
  End             = Date_End
  Event_Editable  = 'false'  /* Control whether to Drag or Drop Event */
  Event_Name      = Event_Type
  
  /* PUBLIC HOLIDAYS */
  If Event_Type = 'Public Holiday' Then (

      Show_Event    = 'Y'
      Text_Color    = COLOR_DARK_GREY
      Title         = Public_Holiday_Name +  ' (' + Country + ')'
      Tooltip_Line1 = Title
      Tooltip_Line2 = to_char(Date_Start, DATE_FORMAT)
      If Date_Start <> Date_End Then
        (Tooltip_Line2 = Tooltip_Line2 + ' - ' + to_char(Date_End, DATE_FORMAT))

  )

  /* PAY DATES */
  Else If Event_Type = 'Payroll Cut Off' Then (

      Show_Event    = 'Y'
      Text_Color    = COLOR_DARK_GREY
      Title         = Event_Type + ' - ' + Payroll_Name
      Tooltip_Line1 = Title
      Tooltip_Line2 = to_char(Date_Start, DATE_FORMAT)
      If Date_Start <> Date_End Then
        (Tooltip_Line2 = Tooltip_Line2 + ' - ' + to_char(Date_End, DATE_FORMAT))
  )

  /* BIRTHDAYS */
  Else If Event_Type = 'Birthday' Then (

      If XXAS_PERSON_IS_ME(Person_Id) = 'Y' Then (
          Show_Event = 'Y'
      )
      Else (
          Show_Event = 'N'
      )

      Text_Color    = COLOR_DARK_GREY
      Title         = Birthday_Name
      Tooltip_Line1 = Title
      Icon          = 'gift'

  )
  /* WORK ANNIVERSARY */
  Else If Event_Type = 'Work Anniversary' Then (

      If XXAS_PERSON_IS_ME(Person_Id) = 'Y' Then (
          Show_Event = 'Y'
      )
      Else (
          Show_Event = 'N'
      )

      Text_Color    = COLOR_DARK_GREY
      Title         = Birthday_Name
      Tooltip_Line1 = Title
      Icon          = 'gift'

  )
  Else If Event_Type = 'Missed Punches' Then (

      Show_Event    = 'Y'
      Text_Color    = COLOR_DARK_GREY
	  Border_Color  = STATUS_RETURNED
	  Icon          = 'timecard'
	  Start_String  = ' '
	  End_String    = ' '
	  Tooltip_Line1 = ' '
	  Time_Range    = ' '
	  IF Time_Start was not defaulted THEN 
	  (
	  Start  = to_date(to_char(Date_Start, DATE_CANONICAL) + ' ' + Time_Start, DATETIME_CANONICAL)
	  Start_Min   = to_char(Start, 'MI')
	  Start_AM_PM = to_char(Start, 'AM')
      Start_String = to_char(Start, 'FmHH')
	  If to_number(Start_Min) <> 0 Then (
		Start_String = Start_String + '.' + RTRIM(Start_Min, '0')
	  )
	  Start_String = Start_String + lower(substr(Start_AM_PM, 1, 1))
	  )
	  ELSE
	  (
	  Start_String = '?'
	  )
	  
	  IF Time_End was not defaulted THEN 
	  (
	  End  = to_date(to_char(Date_End, DATE_CANONICAL) + ' ' + Time_End, DATETIME_CANONICAL)
	  End_Min   = to_char(End, 'MI')
	  End_AM_PM = to_char(End, 'AM')
      End_String = to_char(End, 'FmHH')
	  If to_number(End_Min) <> 0 Then (
		End_String = End_String + '.' + RTRIM(End_Min, '0')
	  )
	  End_String = End_String + lower(substr(End_AM_PM, 1, 1))  
	  )
      ELSE
	  (
	  End_String = '?'
      )
	  
	  Time_Range = Start_String+'-' + End_String
	  
	  IF missed_in_punch_flag = 'Y' AND missed_out_punch_flag = 'Y' THEN 
	  (
	    Tooltip_Line1 = 'Missed in/out'
	  )
	  ELSE IF missed_in_punch_flag = 'Y' THEN
	  (
	    Tooltip_Line1 = 'Missed in'
		IF Start_String = '?' AND End_String = '?' THEN
		(
		  Time_Range = '?'
		)
	  )
	  ELSE IF missed_out_punch_flag = 'Y' THEN
	  (
	    Tooltip_Line1 = 'Missed out'
	  )
	  
      Title = Time_Range + ' ' + Element_Name	  
      Tooltip_Line2 = Element_Name + ',' + Time_Range
	  
	  Page_Id      = 'missed-punch'
      Object_Id    = to_char(Person_Id)+'_'+to_char(Assignment_Id)+'_'+to_char(Pending_Clock_Id)
	  Target       = 'modal'	
  )
  Else If Event_Type = 'Clocked In' Then (

      Show_Event    = 'Y'
      Text_Color    = COLOR_DARK_GREY
	  Border_Color  = STATUS_DRAFT
	  Icon          = 'timecard'
	  Start_String  = ' '
	  Tooltip_Line1 = 'Clocked in '

	  IF Time_Start was not defaulted THEN
	  (
	  Start  = to_date(to_char(Date_Start, DATE_CANONICAL) + ' ' + Time_Start, DATETIME_CANONICAL)
	  Start_Min   = to_char(Start, 'MI')
	  Start_AM_PM = to_char(Start, 'AM')
      Start_String = to_char(Start, 'FmHH')
	  If to_number(Start_Min) <> 0 Then (
		Start_String = Start_String + '.' + RTRIM(Start_Min, '0')
	  )
	  Start_String = Start_String + lower(substr(Start_AM_PM, 1, 1))
	  )
	  
	  Time_Range = Start_String + '-' + '?'
      Title = Time_Range + ' ' + Element_Name
      Tooltip_Line2 = Element_Name + ',' + Time_Range
	  
	  Page_Id      = 'clocked-in'
      Object_Id    = to_char(Person_Id)+'_'+to_char(Assignment_Id)+'_'+to_char(Pending_Clock_Id)
	  Target       = 'modal'
  )  
  /* ABSENCE */
  Else If Event_Type = 'Absence' Then (

      Show_Event     = 'Y'
      Color          = COLOR_LIGHT_GREEN
      Text_Color     = COLOR_WHITE
      Title          = Absence_Type
      Tooltip_Line1  = Title
      Event_Editable = 'true'
      If Status_Code = 'Y' Or Status_Code = 'E' Then (
        Event_Editable = 'false'
      )

      If upper(Absence_Type) like '%HOLIDAY%' Or
         upper(Absence_Type) like '%ANNUAL%LEAVE%' Or
         upper(Absence_Type) like '%VACATION%' Then (
          Icon  = 'visapassport'
      )
      Else If upper(Absence_Type) like '%SICK%' Or
              Absence_Category = 'S' Then (
          Icon = 'sick'
          Color = COLOR_RED
      )
      Else If upper(Absence_Type) like '%FAMILY%' Or
              upper(Absence_Type) like '%PARENT%' Then (
          Icon = 'parent'
          Color = COLOR_CYAN
      )
      Else If upper(Absence_Type) like '%MATERNITY%' Or
              upper(Absence_Type) like '%ADOPTION%' Or
              upper(Absence_Type) like '%PATERNITY%' Or
              Absence_Category = 'M' Or
              Absence_Category = 'GB_ADO' Or
              Absence_Category = 'GB_PAT_ADO' Or
              Absence_Category = 'GB_PAT_BIRTH' Then (
          Icon = 'baby'
          Color = COLOR_CYAN
      )
      Else (
          Icon = 'briefcase'
      )

      If Status_Code = 'Y' Then (
        Color        = COLOR_GREY
        Border_Color = STATUS_PENDING
        Title        = Title + ' (' + Status + ')'
      )
      Else If Status_Code = 'RI' Or Status_Code = 'E' Then (
        Color        = COLOR_GREY
        Border_Color = STATUS_RETURNED
        Title        = Title + ' (' + Status + ')'
      )

      /* Determine the start date and time */

      Projected_Start = 'N'
      Projected_End   = 'N'

      If Date_Start was not defaulted Then (
          If Time_Start was defaulted Then (
              Start = Date_Start
          )
          Else (
              Start = to_date(to_char(Date_Start, DATE_CANONICAL) + ' ' + Time_Start + ':00', DATETIME_CANONICAL)
          )
      )
      Else (
          Projected_Start = 'Y'
          If Time_Projected_Start was defaulted Then (
              Start = Date_Projected_Start
          )
          Else (
              Start = to_date(to_char(Date_Projected_Start, DATE_CANONICAL) + ' ' + Time_Projected_Start + ':00', DATETIME_CANONICAL)
          )
      )

      /* Determine the end date and time */
      If Date_End was not defaulted Then (
          If Time_End was defaulted Then (
              End = Date_End
          )
          Else (
              End = to_date(to_char(Date_End, DATE_CANONICAL) + ' ' + Time_End + ':00', DATETIME_CANONICAL)
          )
      )
      Else If Date_Projected_End was not defaulted Then (
          Projected_End = 'Y'
          If Time_Projected_End was defaulted Then (
              End = Date_Projected_End
          )
          Else (
              End = to_date(to_char(Date_Projected_End, DATE_CANONICAL) + ' ' + Time_Projected_End + ':00', DATETIME_CANONICAL)
          )
      )

      /* Determine the duration based on whether it is hours or days  */
      HOUR_SUFFIX  = ' ' + xxas_get_message('XXAS','XXAS_CAL_EVENT_DUR_HOUR')
      HOURS_SUFFIX = ' ' + xxas_get_message('XXAS','XXAS_CAL_EVENT_DUR_HOURS')
      DAY_SUFFIX   = ' ' + xxas_get_message('XXAS','XXAS_CAL_EVENT_DUR_DAY')
      DAYS_SUFFIX  = ' ' + xxas_get_message('XXAS','XXAS_CAL_EVENT_DUR_DAYS')

      If Hours_Or_Days = 'HOURS' Then (

          If Absence_Hours = 1 Then (
            Duration_Suffix = HOUR_SUFFIX
          )
          Else (
            Duration_Suffix = HOURS_SUFFIX
          )

          Duration     = to_char(Absence_Hours) + Duration_Suffix
          Duration_det = to_char(Start, DATE_FORMAT) + ' - ' + to_char(End, DATE_FORMAT) + ' (' + Duration + ')'
      )

      Else (

          If Absence_Days = 1 Then (
            Duration_Suffix = DAY_SUFFIX
          )
          Else (
            Duration_Suffix = DAYS_SUFFIX
          )

          Duration     = to_char(Absence_Days) + Duration_Suffix
          Duration_det = to_char(Start, DATE_FORMAT) + ' - ' + to_char(End, DATE_FORMAT) + ' (' + Duration + ')'

      )

      Scheduler_Additional_Info = Duration
      Tooltip_Line2             = Duration_det

      /* Define the hyperlink.  Link to the open notification for direct approval if
         possible. */
      Target               = 'modal'
      Link_to_notification = 'N'
      If Status_Code = 'Y' Then (
        If XXAS_PERSON_IS_ME(Person_Id) = 'N' Then (
          /* Check to see if an open notification exists for this user */
          Notification_Id = XXAS_CAL_GET_OPEN_NOTIF_ID(Item_Type, Item_Key)

          If Notification_Id <> -1 Then (
            Link_to_notification = 'Y'
          )
        )
      )

      If Link_to_notification = 'Y' Then (
        Page_Id      = 'notification'
        Object_Id    = to_char(Notification_Id)
      )
      Else (
        Page_Id = 'absence-view'
        If Person_Id = 0 Then (
            Object_Id = '_'
        )
        Else (
            Object_Id = to_char(Person_Id) + '_'
        )

        If Absence_Id = 0 Then (
            Object_Id = Object_Id + '_'
        )
        Else (
            Object_Id = Object_Id + to_char(Absence_Id) + '_'
        )

        If Transaction_Id = 0 Then (
            Object_Id = Object_Id + '_'
        )
        Else (
            Object_Id = Object_Id + to_char(Transaction_Id) + '_'
        )

        If Transaction_Step_Id = 0 Then (
            Object_Id = Object_Id
        )
        Else (
            Object_Id = Object_Id + to_char(Transaction_Step_Id)
        )
        Object_Id = Object_Id + '_' + replace(Source_Block,'_','$') + '_'

        If Saved_Search_Id = 0 Then (
            Object_Id = Object_Id + '_'
        )
        Else (
            Object_Id = Object_Id + to_char(Saved_Search_Id)
        )
        If Manager_Id = 0 Then (
            Object_Id = Object_Id
        )
        Else (
            Object_Id = Object_Id + to_char(Manager_Id)
        )

      )
  )

  /* TRAINING */
  Else If Event_Type = 'Training' Then (

      If Status_Code = 'A' Or
         Status_Code = 'E' Or
         Status_Code = 'R' Or
         Status_Code = 'P' Or
         Status_Code = 'W' Then (
          Show_Event = 'Y'
      )
      Else (
          Show_Event = 'N'
      )

      Color         = COLOR_PURPLE
      Text_Color    = COLOR_WHITE
      Icon          = 'training'
      Title         = Event_Type
      Tooltip_Line1 = Title

      /* If requested or waitlisted, add the border color */
      If Status_Code = 'R' Or
         Status_Code = 'W' Then (
          Color        = COLOR_GREY
          Border_Color = COLOR_ORANGE
      )

      /* Include basic details about the course */

      /* Determine the start date and time */
      If Date_Start was not defaulted Then (
          If Time_Start was defaulted Then (
              Start = Date_Start
          )
          Else (
              Start = to_date(to_char(Date_Start, DATE_CANONICAL) + ' ' + Time_Start + ':00', DATETIME_CANONICAL)
          )
      )

      /* Determine the end date and time */
      If Date_End was not defaulted Then (
          If Time_End was defaulted Then (
              End = Date_End
          )
          Else (
              End = to_date(to_char(Date_End, DATE_CANONICAL) + ' ' + Time_End + ':00', DATETIME_CANONICAL)
          )
      )

      Scheduler_Additional_Info = to_char(Training_Duration) + ' ' + lower(Training_Duration_Units)
      Tooltip_Line2             = to_char(Start, DATE_FORMAT) + ' - '
                                + to_char(End, DATE_FORMAT)
                                + ' (' + Scheduler_Additional_Info + ')'
  )

  /* TIME ENTRY */
  Else If Event_Type = 'Time Entry' Then (

      Show_Event        = 'Y'
      Color             = '#f9fafb'
      Text_Color        = COLOR_DARK_GREY
      Event_Editable    = 'true'

      /* Use a heavily abbreviated format to display the dates/times/duration */
      Show_Times   = 'N'
      If Time_Start was not defaulted And Time_End was not defaulted And
         Time_Start <> Time_End Then (

        Start  = to_date(to_char(Date_Start, DATE_CANONICAL) + ' ' + Time_Start, DATETIME_CANONICAL)
        End    = to_date(to_char(Date_End, DATE_CANONICAL) + ' ' + Time_End, DATETIME_CANONICAL)

        Show_Times  = 'Y'
        Start_Min   = to_char(Start, 'MI')
        Start_AM_PM = to_char(Start, 'AM')
        End_Min     = to_char(End, 'MI')
        End_AM_PM   = to_char(End, 'AM')

        Start_String = to_char(Start, 'FmHH')
        If to_number(Start_Min) <> 0 Then (
          Start_String = Start_String + '.' + RTRIM(Start_Min, '0')
        )
        Start_String = Start_String + lower(substr(Start_AM_PM, 1, 1))

        End_String = to_char(End, 'FmHH')
        If to_number(End_Min) <> 0 Then (
          End_String = End_String + '.' + RTRIM(End_Min, '0')
        )
        End_String = End_String + lower(substr(End_AM_PM, 1, 1))
      )

      Show_Duration = 'N'
      If Time_Entry_Duration_Hours was not defaulted Then (
        Show_Duration = 'Y'
        Duration_String = ltrim(to_char(Time_Entry_Duration_Hours), '0') + 'h'
      )

      If Show_Times = 'Y' And Show_Duration = 'Y' Then (
        Time_Dur_String = Duration_String + ' (' + Start_String + '-' + End_String + ') ' + Element_Name
      )
      Else If Show_Times = 'Y' Then (
        Time_Dur_String = Start_String + '-' + End_String + ' ' + Element_Name
      )
      Else If Show_Duration = 'Y' Then (
        Time_Dur_String = Duration_String + ' ' + Element_Name
      )
      Else (
        Time_Dur_String = Element_Name
      )

      Title                     = Time_Dur_String
      Scheduler_Additional_Info = Status
      Tooltip_Line1             = Title + ' (' + Status + ')'
    /* Reserved for showing additional tcm fields
      Tooltip_Line2             = TODO */

      If Status_Code = 'D' Then (
        Border_Color = STATUS_DRAFT
      )
      Else If Status_Code = 'P' Then (
        Event_Editable = 'false'
        Border_Color = STATUS_PENDING
      )
      Else If Status_Code = 'A' Then (
        Border_Color = STATUS_APPROVED
      )
      Else If Status_Code = 'T' Then (
        Border_Color = STATUS_TRANSFERRED
      )
      Else If Status_Code = 'R' Then (
        Border_Color = STATUS_RETURNED
      )

      Page_Id    = 'time-view'

      If Assignment_Id = 0 Then (
        Object_Id = '_'
      )
      Else (
        Object_Id = to_char(Assignment_Id) + '_'
      )
      If Log_Entry_Id = 0 Then (
        Object_Id = Object_Id + '_'
      )
      Else (
        Object_Id = Object_Id + to_char(Log_Entry_Id) + '_'
      )
      If Payment_Type_Id = 0 Then (
        Object_Id = Object_Id + '_'
      )
      Else (
        Object_Id = Object_Id + to_char(Payment_Type_Id) + '_'
      )

      Object_Id = Object_Id + replace(Source_Block,'_','$') + '_'

      If Saved_Search_Id = 0 Then (
        Object_Id = Object_Id
      )
      Else (
        Object_Id = Object_Id + to_char(Saved_Search_Id)
      )

      Target               = 'modal'

  )

  /* OTL TIME ENTRY */
  Else If Event_Type = 'OTL Entry' Then (

      Show_Event        = 'Y'
      Color             = '#f9fafb'
      Text_Color        = COLOR_DARK_GREY
      Event_Editable    = 'false'

      /* Use a heavily abbreviated format to display the dates/times/duration */
      Show_Times   = 'N'
      If Time_Start was not defaulted And Time_End was not defaulted And
         Time_Start <> Time_End Then (

        Start  = to_date(to_char(Date_Start, DATE_CANONICAL) + ' ' + Time_Start, DATETIME_CANONICAL)
        End    = to_date(to_char(Date_End, DATE_CANONICAL) + ' ' + Time_End, DATETIME_CANONICAL)

        If Otl_Entry_Type = 'RANGE' Then (
            Show_Times  = 'Y'
            Start_Min   = to_char(Start, 'MI')
            Start_AM_PM = to_char(Start, 'AM')
            End_Min     = to_char(End, 'MI')
            End_AM_PM   = to_char(End, 'AM')

            Start_String = to_char(Start, 'FmHH')
            If to_number(Start_Min) <> 0 Then (
                Start_String = Start_String + '.' + RTRIM(Start_Min, '0')
            )
            Start_String = Start_String + lower(substr(Start_AM_PM, 1, 1))

            End_String = to_char(End, 'FmHH')
            If to_number(End_Min) <> 0 Then (
                End_String = End_String + '.' + RTRIM(End_Min, '0')
            )
            End_String = End_String + lower(substr(End_AM_PM, 1, 1))
        )
        Else If Otl_Entry_Type = 'MEASURE' Then (
            Show_Times  = 'N'
        )
      )

      Show_Duration = 'N'
      If Time_Entry_Duration_Hours was not defaulted Then (
        Show_Duration = 'Y'
        Duration_String = to_char(Time_Entry_Duration_Hours) + 'h'
      )
      Else If Otl_Absence_Days was not defaulted Then (
        Show_Duration = 'Y'
        Duration_String = to_char(Otl_Absence_Days) + 'd'
      )
      Else If Otl_Absence_Hours was not defaulted Then (
        Show_Duration = 'Y'
        Duration_String = to_char(Otl_Absence_Hours) + 'h'
      )

      If Show_Times = 'Y' And Show_Duration = 'Y' Then (
        Time_Dur_String = Duration_String + ' (' + Start_String + '-' + End_String + ') ' + Element_Name
      )
      Else If Show_Times = 'Y' Then (
        Time_Dur_String = Start_String + '-' + End_String + ' ' + Element_Name
      )
      Else If Show_Duration = 'Y' Then (
        Time_Dur_String = Duration_String + ' ' + Element_Name
      )
      Else (
        Time_Dur_String = Element_Name
      )

      Title                     = Time_Dur_String
      Scheduler_Additional_Info = Status
      Tooltip_Line1             = Title + ' (' + Status + ')'
      /* Reserved for showing additional otl fields
         Tooltip_Line2             = TODO */

      If Status_Code = 'WORKING' Then (
        Border_Color = STATUS_DRAFT
      )
      Else If Status_Code = 'SUBMITTED' Then (
        Border_Color = STATUS_PENDING
      )
      Else If Otl_Entry_Transferred_To was not defaulted And
              Otl_Entry_Transferred_To <> 'None' Then (
        Border_Color = STATUS_TRANSFERRED
      )
      Else If Status_Code = 'APPROVED' Then (
        Border_Color = STATUS_APPROVED
      )
      Else If Status_Code = 'REJECTED' Then (
        Border_Color = STATUS_RETURNED
      )

      /* Define the hyperlink.  Link to the open notification for direct approval if
         possible. */
      Target               = 'modal'
      Link_to_notification = 'N'
      If Status_Code = 'SUBMITTED' Then (
        If XXAS_PERSON_IS_ME(Person_Id) = 'N' Then (
          /* Check to see if an open notification exists for this user */
          Notification_Id = XXAS_CAL_GET_TCM_OPEN_NOTIF_ID(Otl_Timecard_Id)

          If Notification_Id <> -1 Then (
            Link_to_notification = 'Y'
          )
        )
      )

      If Link_to_notification = 'Y' Then (
        Page_Id      = 'notification'
        Object_Id    = to_char(Notification_Id)
      )
      Else (
        Page_Id    = 'otl-time-view'

        If Person_Id = 0 Then (
            Object_Id = '_'
        )
        Else (
            Object_Id = to_char(Person_Id) + '_'
        )
        If Otl_Timecard_Id = 0 Then (
            Object_Id = Object_Id + '_'
        )
        Else (
            Object_Id = Object_Id + to_char(Otl_Timecard_Id) + '_'
        )
        If Otl_Hourstype_Id = 0 Then (
            Object_Id = Object_Id + '_'
        )
        Else (
            Object_Id = Object_Id + to_char(Otl_Hourstype_Id) + '_'
        )

        Object_Id = Object_Id + replace(Source_Block,'_','$') + '_'

        If Saved_Search_Id = 0 Then (
            Object_Id = Object_Id + '_'
        )
        Else (
            Object_Id = Object_Id + to_char(Saved_Search_Id) + '_'
        )

        If Otl_Timecard_Ovn = 0 Then (
            Object_Id = Object_Id
        )
        Else (
            Object_Id = Object_Id + to_char(Otl_Timecard_Ovn) + '_'
        )

        If Otl_Timecard_Date_From was defaulted Then (
            Object_Id = Object_Id
        )
        Else (
            Object_Id = Object_Id + to_char(Otl_Timecard_Date_From,DATETIME_CANONICAL)
        )

      )
  )

  RETURN Show_Event
        ,Start
        ,End
        ,Event_Editable
        ,Title
        ,Scheduler_Additional_Info
        ,Tooltip_Line1
        ,Tooltip_Line2
        ,Icon
        ,Color
        ,Text_Color
        ,Border_Color
        ,Page_Id
        ,Object_Id
        ,Target
		,Event_Name

]';

  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;
