/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET ESCAPE '\'
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_MGR';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Absence calendar view for managers and HR Professionals';
  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 := q'[

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

Formula Name: XXAS_CAL_MGR
Formula Type: QuickPaint
Description:  Seeded calendar for managers, showing unrestricted details for all
              types of events.

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

  /* Specify defaults for all items that are used. */

  Default for Event_Type           is 'Unknown'
  Default for Public_Holiday_Name  is 'Unknown'
  Default for Status               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 Course_Name          is 'Unknown'
  Default for Class_Name           is 'Unknown'
  Default for Enrollment_Status    is 'Unknown'
  Default for Free_Busy_Type       is 'Unknown'
  Default for Sch_Duration         is 0
  Default for Item_Type            is 'Unknown'
  Default for Item_Key             is 'Unknown'

  /* Declare all items that are used. */

  Inputs are Event_Type
            ,Public_Holiday_Name
            ,Status
            ,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
            ,Course_Name
            ,Class_Name
            ,Enrollment_Status
            ,Free_Busy_Type
            ,Sch_Duration
            ,Item_Type
            ,Item_Key


  /* Other items that are used in this formula */

  Precedence      =  0
  Description     = 'Unknown'
  Start           = 'Unknown'
  Projected_Start = 'N'
  End             = 'Unknown'
  Projected_End   = 'N'
  Date_Suffix     = 'Unknown'
  Duration        = ' '
  Unknown_Start   = 'N'
  Half_Day        = ' '
  Dest_URI        = ' '

  /* Set the values for each type of event */

  If Event_Type = 'Weekend' Then (

      Show_Event   = 'Y'
      Cell_Code    = 'X'
      Tip_Text     = 'Weekend'
      CSS_Style    = 'XxasCellSilver'
      /* A list of the days of the week that make up the weekend.
         Here 6 is the sixth day of the week (Sat) and 7 is the seventh
         day of the week (Sun).
         Use any combination of values.  For example, 25 would make
         the weekend Tue (2) and Fri (5). Here the week always starts
         on Mon (1). */
      Weekend_Days = '67'
  )

  Else If Event_Type = 'Public Holiday' Then (

      Show_Event = 'Y'
      Precedence =  2
      Cell_Code  = 'X'
      Tip_Text   =  Public_Holiday_Name
      CSS_Style  = 'XxasCellSilver'
  )

  Else If Event_Type = 'Absence' Then (

      Show_Event = 'Y'
      Precedence =  6
      Cell_Code  =  upper(substr(Absence_Type, 1, 1))


      /* Make the Tip_Text as comprehensive as possible. Detail a
         description, then the start, end, status and duration. */
      If Absence_Reason was defaulted Then

          (Description = Absence_Type)

      Else

          (Description = Absence_Type + ' (' + Absence_Reason + ')' )

      /* Determine the start date and time */
      If Date_Start was not defaulted Then (
          If Time_Start was defaulted Then
              (Start = to_char(Date_Start, 'DD-Mon-YYYY'))
          Else
              (Start = to_char(Date_Start, 'DD-Mon-YYYY') + ' ' + Time_Start)
      )
      Else (
          Projected_Start = 'Y'
          If Time_Projected_Start was defaulted Then
              (Start = to_char(Date_Projected_Start, 'DD-Mon-YYYY'))
          Else
              (Start = to_char(Date_Projected_Start, 'DD-Mon-YYYY') + ' ' + Time_Projected_Start)
      )

      /* Determine the end date and time */
      If Date_End was not defaulted Then (
          If Time_End was defaulted Then
              (End = to_char(Date_End, 'DD-Mon-YYYY'))
          Else
              (End = to_char(Date_End, 'DD-Mon-YYYY') + ' ' + Time_End)
      )
      Else If Date_Projected_End was not defaulted Then (
          Projected_End = 'Y'
          If Time_Projected_End was defaulted Then
              (End = to_char(Date_Projected_End, 'DD-Mon-YYYY'))
          Else
              (End = to_char(Date_Projected_End, 'DD-Mon-YYYY') + ' ' + Time_Projected_End)
      )
      Else
          (End = 'Open ended')

      /* Clarify the status of the dates, if needed */
      If Status = 'Pending' Then
          (Date_Suffix = ' (pending approval)')
      Else If Projected_Start = 'Y' Then
          (Date_Suffix = ' (projected)')
      Else If Projected_End = 'Y' Then
          (Date_Suffix = ' (projected end)')

      /* Determine the duration */
      If Absence_Days was not defaulted Then (
          If Absence_Days = 1 Then
              (Duration = ', ' + to_char(Absence_Days) + ' Day')
          Else
              (Duration = ', ' + to_char(Absence_Days) + ' Days')
      )
      Else If Absence_Hours was not defaulted Then (
          If Absence_Hours = 1 Then
              (Duration = ', ' + to_char(Absence_Hours) + ' Hour')
          Else
              (Duration = ', ' + to_char(Absence_Hours) + ' Hours')
      )

      /* Bring all the parts together */
      If Date_Suffix = 'Unknown' Then
        (Tip_Text = Description + ', ' + Start + ' to ' + End + Duration)
      Else
        (Tip_Text = Description + ', ' + Start + ' to ' + End + Date_Suffix + Duration)

      /* Use different styles for different types of absence. If the absence
         is pending approval and the notification is sitting with the
         logged in user allow a direct link. */
      If Status = 'Pending' Then (

          /* Check to see if an open notification exists for this user */
          Notification_Id = xxas_open_notif_id(Item_Type, Item_Key)

          /* If a notification was found (-1 means not found) set this to a
             clickable cell */
          If Notification_Id <> -1 Then (

              CSS_Style = 'XxasCellBeigeOnGrayWithRedBorder'
              Status = Status + ' - click to view notification'
              Dest_URI = 'OA.jsp?OAFunc=FND_WFNTF_DETAILS\&NtfId=' +
                          to_char(Notification_Id) +
                         '\&addBreadCrumb=Y\&retainAM=Y'
          )

          Else

             (
                    
              CSS_Style = 'XxasCellBeigeOnGray')

      )

      /* These are all approved absences */
      Else (

          If upper(Absence_Type) like '%HOLIDAY%' Or
             upper(Absence_Type) like '%ANNUAL%LEAVE%' Or
             upper(Absence_Type) like '%VACATION%' Then

              (CSS_Style = 'XxasCellPowderBlueOnBlue')

          Else If upper(Absence_Type) like '%SICK%' Or
                  Absence_Category = 'S' Then

              (CSS_Style = 'XxasCellMoccasinOnRed')

          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

              (CSS_Style = 'XxasCellLinenOnGreen')

          Else

              (CSS_Style = 'XxasCellChocolateOnYellow')

      )

      /* If this is an hours-based absence, is less than 6 hours
         and occurred in the morning or afternoon flag as a half day. */
      If Date_Start = Date_End And
         Time_Start was not defaulted And
         Time_End was not defaulted And
         Absence_Hours > 0 And Absence_Hours < 6 Then (

            If to_number(substr(Time_End, 1, 2)) <= 13 Then
                (Half_Day = 'AM')

            Else If to_number(substr(Time_Start, 1, 2)) >= 12 Then
                (Half_Day = 'PM')

      )

      /* Same half-day detection but for projected absences. */
      Else If Date_Start was defaulted And
              Date_End was defaulted And
              Time_Start was defaulted And
              Time_End was defaulted And
              Date_Projected_Start = Date_Projected_End And
              Time_Projected_Start was not defaulted And
              Time_Projected_End was not defaulted And
              to_number(substr(Time_Projected_End, 1, 2)) -
              to_number(substr(Time_Projected_Start, 1, 2)) < 6 Then (

          If to_number(substr(Time_Projected_End, 1, 2)) <= 13 Then
              (Half_Day = 'AM')

          Else If to_number(substr(Time_Projected_Start, 1, 2)) >= 12 Then
              (Half_Day = 'PM')

      )

      /* Half-day detection for days-based absences. */
      Else If Date_Start = Date_End And
              Absence_Days > 0 And Absence_Days < 1 Then (

          Half_Day = 'Midday'

      )

      /* Fill only half the cell for partial days */
      If Half_Day = 'AM' Then
        (CSS_Style = CSS_Style + 'TopHalf')

      Else If Half_Day = 'PM' Then
        (CSS_Style = CSS_Style + 'BottomHalf')

      Else If Half_Day = 'Midday' Then
        (CSS_Style = CSS_Style + 'MiddleHalf')

  )

  Else If Event_Type = 'Training' Then (

      If Enrollment_Status = 'Attended' Or
         Enrollment_Status = 'Enrolled' Or
         Enrollment_status = 'Requested' Or
         Enrollment_Status = 'Placed' Or
         Enrollment_Status = 'Waitlisted' Then
          (Show_Event = 'Y')
      Else
          (Show_Event = 'N')

      Precedence =  4
      Cell_Code  = 'T'

      /* Make the Tip_Text as comprehensive as possible. Detail a
         description, then the start, end and enrollment status. */

      If Course_Name was not defaulted And Class_Name was not defaulted Then
          (Description = Course_Name + ' (' + Class_Name + ')')
      Else If Course_Name was not defaulted Then
          (Description = Course_Name)
      Else If Class_Name was not defaulted Then
          (Description = Class_Name)

      /* Determine the start date and time */
      If Date_Start was not defaulted Then (
          If Time_Start was defaulted Then
              (Start = to_char(Date_Start, 'DD-Mon-YYYY'))
          Else
              (Start = to_char(Date_Start, 'DD-Mon-YYYY') + ' ' + Time_Start)
      )
      Else
          (Unknown_Start = 'Y')

      /* Determine the end date and time */
      If Date_End was not defaulted Then (
          If Time_End was defaulted Then
              (End = to_char(Date_End, 'DD-Mon-YYYY'))
          Else
              (End = to_char(Date_End, 'DD-Mon-YYYY') + ' ' + Time_End)
      )
      Else
          (End = 'Ongoing')

      /* Bring all the parts together */
      If Unknown_Start = 'Y' Then
          (Tip_Text = Description + ', ' + Enrollment_Status)
      Else
          (Tip_Text = Description + ', ' + Start + ' to ' + End + ', ' + Enrollment_Status)

      /* Use different styles for different types of absence */
      If Enrollment_Status = 'Requested' Or
         Enrollment_Status = 'Waitlisted' Then

          (CSS_Style = 'XxasCellBeigeOnGray')

      Else

          (CSS_Style = 'XxasCellPapayaWhipOnMaroon')
  )

  Else If Event_Type = 'CAC Work Schedule' Then (

      Show_Event = 'Y'

      If Free_Busy_Type = 'FREE' Then (

          /* Most days include both working and non-working time (unless
             working 24 hours a day!) so give Working time a higher precedence
             over non-working time so that on a working day non-working time
             does not override. */
          Precedence =  3.5
          Cell_Code  = 'S'
          CSS_Style  = 'XxasCellWhite'
          Tip_Text   = 'Working '
      
          If Date_Start = Date_End Then (

             If Time_Start was not defaulted And
                Time_End   was not defaulted Then
                (Tip_Text = Tip_Text + Time_Start + ' to ' + Time_End)
          )

          Else If add_days(Date_Start, 1) = Date_End And
                  Time_Start was not defaulted And
                  Time_End was not defaulted Then (

              Tip_Text = Tip_Text + Time_Start + ' to ' + Time_End +
                         ' (' + to_char(Date_Start, 'FMddth') +
                         ' to ' +
                         to_char(Date_End, 'FMddth') + ')'
          )

          Else (

              Start = to_char(Date_Start, 'DD-Mon-YYYY')
              End   = to_char(Date_End  , 'DD-Mon-YYYY')

              If Time_Start was not defaulted And
                 Time_End   was not defaulted Then (
                  Start = Start + ' (' + Time_Start + ')'
                  End   = End   + ' (' + Time_End   + ')'
              )

              Tip_Text = Tip_Text + Start + ' to ' + End
          )

      )

      Else (

	      /* Hide the event if no schedules were retrieved */
          If Date_Start = to_date('01-01-' + to_char(Date_Start, 'YYYY'), 'DD-MM-YYYY') And
             Date_End = to_date('31-12-' + to_char(Date_End, 'YYYY'), 'DD-MM-YYYY') Then
            (Show_Event = 'N')

          Precedence =  3
          Cell_Code  = 'X'
          CSS_Style  = 'XxasCellSilver'
          Tip_Text   = 'Not working'
      )

  )

  Else If Event_Type = 'PQP Work Pattern' Then (

      If Sch_Duration was not Defaulted Then (
      
          Show_Event = 'Y'
           Precedence = 1
           
        If Free_Busy_Type = 'FREE' Then (

          If Sch_Duration = 1 Then
              (Description = 'Working (1 Hour)')
          Else
              (Description = 'Working (' + to_char(Sch_Duration) + ' Hours)')

          Tip_Text   =  Description
          Cell_Code  = 'S'
          CSS_Style  = 'XxasCellWhite'
        )
        Else If Free_Busy_Type = 'BUSY' Then (
        
           Cell_Code  = 'X'
           CSS_Style  = 'XxasCellSilver'
           Tip_Text   = 'Not Working'        
        )
      )

      Else
          (Show_Event = 'N')

  )

  RETURN Show_Event
        ,Cell_Code
        ,Tip_Text
        ,CSS_Style
        ,Dest_URI
        ,Precedence
        ,Weekend_Days
]';


  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;
