/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2012 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_EMP_VIEW_ABS_MGMT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'View current and historical R12 absences';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Assignment';
  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 := q'[

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

Formula Name: XXAS_EMP_VIEW_ABS_MGMT
Formula Type: Applaud Assignment
Description:  Assignment R12 Absence EmployeeCenter View

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

  Default for Transaction_Type        is 'NONE'
  Default for Workflow_Status_Code    is 'NONE'
  Default for Workflow_Status_Meaning is 'NONE'
  Default for Date_Start              is '0001/01/01 00:00:00' (date)
  Default for Date_Projected_Start    is '0001/01/01 00:00:00' (date)
  Default for Date_End                is '4712/12/31 00:00:00' (date)
  Default for Date_Projected_End      is '4712/12/31 00:00:00' (date)
  Default for Time_Start              is '00:00'
  Default for Time_Projected_Start    is '00:00'
  Default for Time_End                is '00:00'
  Default for Time_Projected_End      is '00:00'
  Default for Absence_Days            is 0
  Default for Absence_Hours           is 0
  Default for Absence_Type            is 'Unknown'
  Default for Absence_Reason          is 'Unknown'
  Default for Absence_Category        is 'Unknown'
  Default for Absence_Category_Code   is 'Unknown'
  Default for Date_Start_p            is '0001/01/01 00:00:00' (date)
  Default for Date_Projected_Start_p  is '0001/01/01 00:00:00' (date)
  Default for Date_End_p              is '4712/12/31 00:00:00' (date)
  Default for Date_Projected_End_p    is '4712/12/31 00:00:00' (date)
  Default for Time_Start_p            is '00:00'
  Default for Time_Projected_Start_p  is '00:00'
  Default for Time_End_p              is '00:00'
  Default for Time_Projected_End_p    is '00:00'
  Default for Absence_Days_p          is 0
  Default for Absence_Hours_p         is 0
  Default for Absence_Type_p          is 'Unknown'
  Default for Absence_Reason_p        is 'Unknown'
  Default for Absence_Category_p      is 'Unknown'
  Default for Absence_Category_Code_p is 'Unknown'
  Default for Replacement_Person_Id   is 'Unknown'
  Default for Absence_Attendance_Id_p is 0
  Default for Transaction_Id          is 0
  Default for Transaction_Step_Id     is 0
  Default for Absence_Attendance_Id   is 0
  Inputs are Person_Id, Transaction_Id, Transaction_Step_Id,  Transaction_Type, Workflow_Status_Code, Workflow_Status_Meaning,
             Date_Start, Date_Projected_Start, Date_End, Date_Projected_End,
             Time_Start, Time_Projected_Start, Time_End, Time_Projected_End,
             Absence_Days, Absence_Hours, Absence_Type, Absence_Reason,
             Absence_Category, Absence_Category_Code,
             Date_Start_p, Date_Projected_Start_p, Date_End_p, Date_Projected_End_p,Time_Start_p, Time_Projected_Start_p, Time_End_p,         Time_Projected_End_p,
             Absence_Days_p, Absence_Hours_p, Absence_Type_p, Absence_Reason_p,
             Absence_Category_p, Absence_Category_Code_p,Absence_Attendance_Id,Absence_Attendance_Id_p,Replacement_Person_Id
  Replacement_Person_Id_Visible = 'true'
  Replacement_Person_Id_Enabled = 'true'
 
  COLOR_GREEN    = xxas_get_color_by_name('balanced')
  COLOR_AMBER    = xxas_get_color_by_name('energized')
  COLOR_RED      = xxas_get_color_by_name('assertive')
  COLOR_GREY     = xxas_get_color_by_name('grey')
  COLOR_BLUE     = xxas_get_color_by_name('positive')
  START_OF_TIME  = to_date('0001/01/01 00:00:00', 'YYYY/MM/DD HH24:MI:SS')
  END_OF_TIME    = to_date('4712/12/31 00:00:00', 'YYYY/MM/DD HH24:MI:SS')

  /* Derive the various properties from the transaction table
     if pending, or the base if not  */
  Have_Start_Time = 'N'
  Have_End_Time   = 'N'
  Projected       = 'N'
  Pending         = 'N'


  summary_context = ' '
  If Transaction_Type was not defaulted then
  (
    Pending = 'Y'

    If Workflow_Status_Code = 'RFC' Then
      (Status_Color = COLOR_RED)
    Else If Workflow_Status_Code = 'ERROR' Then
      (Status_Color = COLOR_RED)
    Else If Workflow_Status_Code = 'SFL' Then
      (Status_Color = COLOR_GREY)
    Else If Workflow_Status_Code = 'APPROVED' Then
      ( Status_Text='APPROVED'
        Status_Color = COLOR_GREEN)
    Else
      (
     Status_Color = COLOR_AMBER)

    Status_Text = Workflow_Status_Meaning
    Summary_Context = '(<font class="color-' + Status_Color + '">' + Status_Text +'</font>)'
    Detail_Header_Context = Summary_Context

    If Date_Start_p was not defaulted then
      (Start_Date = Date_Start_p)
    Else
    (
      Projected = 'Y'
      Start_Date = Date_Projected_Start_p
    )

    If Date_End_p was not defaulted then
      (End_Date = Date_End_p)
    Else
    (
      Projected = 'Y'
      End_Date = Date_Projected_End_p
    )

    If Time_Start_p was not defaulted then
    (
      Start_Time = Time_Start_p
      Have_Start_Time = 'Y'
    )
    Else If Time_Projected_Start_p was not defaulted then
    (
      Start_Time = Time_Projected_Start_p
      Have_Start_Time = 'Y'
    )

    If Time_End_p was not defaulted then
    (
      End_Time = Time_End_p
      Have_End_Time = 'Y'
    )
    Else If Time_Projected_End_p was not defaulted then
    (
      End_Time = Time_Projected_End_p
      Have_End_Time = 'Y'
    )

    Duration_Days = Absence_Days_p
    Duration_Hours = Absence_Hours_p
    Type_of_Absence = Absence_Type_p
    Reason = Absence_Reason_p
    Category = Absence_Category_p
    Category_Code = Absence_Category_Code_p

  )
  Else
  (
    If Date_Start was not defaulted then
      (Start_Date = Date_Start)
    Else
    (
      Projected = 'Y'
      Start_Date = Date_Projected_Start
    )

    If Date_End was not defaulted then
      (End_Date = Date_End)
    Else
    (
      Projected = 'Y'
      End_Date = Date_Projected_End
    )

    If Time_Start was not defaulted then
    (
      Start_Time = Time_Start
      Have_Start_Time = 'Y'
    )
    Else If Time_Projected_Start was not defaulted then
    (
      Start_Time = Time_Projected_Start
      Have_Start_Time = 'Y'
    )

    If Time_End was not defaulted then
    (
      End_Time = Time_End
      Have_End_Time = 'Y'
    )
    Else If Time_Projected_End was not defaulted then
    (
      End_Time = Time_Projected_End
      Have_End_Time = 'Y'
    )

    Duration_Days = Absence_Days
    Duration_Hours = Absence_Hours
    Type_of_Absence = Absence_Type
    Reason = Absence_Reason
    Category = Absence_Category
    Category_Code = Absence_Category_Code

  )

  /* Update the Start and End variables to include
     the time component if specified */
  Start = Start_Date
  End = End_Date
  If Have_Start_Time = 'Y' And Have_End_Time = 'Y' Then
  (
    Start = to_date(to_char(Start_Date, 'YYYY-MM-DD') + ' ' + Start_Time
                   ,'YYYY-MM-DD HH24:MI:SS')
    End   = to_date(to_char(End_Date  , 'YYYY-MM-DD') + ' ' + End_Time
                   ,'YYYY-MM-DD HH24:MI:SS')
  )

  Absence_Dates = to_char(Start_Date, 'fmDD Mon')
  If End_Date <> END_OF_TIME Then
    (Absence_Dates = Absence_Dates + ' - ' + to_char(End_Date, 'fmDD Mon YYYY'))
  Else
    (Absence_Dates = Absence_Dates + ' ' + to_char(Start_Date, 'YYYY') +
                     xxas_get_message('XXAS','XXAS_ABS_ONGOING'))

  /* Calendar properties */
  CalEvent_Title = Type_of_Absence
  CalEvent_Start = Start
  CalEvent_End = End
  CalEvent_Text_Color = '#ffffff'

  /* Specify the type of absence if it's holiday/annual leave/vacation */
  If upper(Type_of_Absence) like '%HOLIDAY%' Or
     upper(Type_of_Absence) like '%ANNUAL%LEAVE%' Or
     upper(Type_of_Absence) like '%VACATION%' Then
  (
    Summary_Icon   = 'VISAPASSPORT'
    CalEvent_Color = xxas_get_color_by_name('blue')
  )
  Else If upper(Type_of_Absence) like '%SICK%' Or
          Category_Code = 'S' Then
  (
    Summary_Icon   = 'SICK'
    CalEvent_Color = xxas_get_color_by_name('red')
  )
  Else If upper(Type_of_Absence) like '%FAMILY%' Or
          upper(Type_of_Absence) like '%PARENT%' Then
  (
    Summary_Icon   = 'PARENT'
    CalEvent_Color = xxas_get_color_by_name('light_green')
  )
  Else If upper(Type_of_Absence) like '%MATERNITY%' Or
          upper(Type_of_Absence) like '%ADOPTION%' Or
          upper(Type_of_Absence) like '%PATERNITY%' Or
          Absence_Category = 'M' Or
          Absence_Category = 'GB_ADO' Or
          Absence_Category = 'GB_PAT_ADO' Or
          Absence_Category = 'GB_PAT_BIRTH' Then
  (
    Summary_Icon   = 'BABY'
    CalEvent_Color = xxas_get_color_by_name('green')
  )
  Else
  (
    Summary_Icon = 'BRIEFCASE'
    CalEvent_Color = xxas_get_color_by_name('light_blue')
  )

  /* Override to grey if pending */
  If Transaction_Type was not defaulted then
  (
    CalEvent_Color = xxas_get_color_by_name('grey')
    CalEvent_Title = CalEvent_Title + ' (pending)'
  )

  Summary_Text = Absence_Dates
  
  /* Set the summary message to the duration */
  Summary_Message = Type_of_Absence
  Duration_Text = ' '
  If Duration_Hours > 0 Then
    (
     If Duration_Hours = 1 Then
       (Duration_Text = xxas_get_message('XXAS','XXAS_EMP_ONE_HOUR'))
     Else
       (Duration_Text = to_char(Duration_Hours) + ' ' +
                        xxas_get_message('XXAS','XXAS_COM_HOURS'))
     Summary_Message = Duration_Text + ': ' + Type_of_Absence
    )
  Else If Duration_Days > 0 Then
    (
     If Duration_Days = 1 Then
       (Duration_Text = xxas_get_message('XXAS','XXAS_EMP_ONE_DAY'))
     Else
       (Duration_Text = to_char(Duration_Days) + ' ' +
                        xxas_get_message('XXAS','XXAS_COM_DAYS'))
     Summary_Message = Duration_Text + ': ' + Type_of_Absence
    )

  /* Set the detail header to the summary header */
  Detail_Header_Text    = Summary_Text
  Detail_Header_Message = Summary_Message

  /* Set the Absence Type and Reason */
  Item1_Prompt = xxas_get_message('XXAS','XXAS_EMP_TYPE_REASON')
  Item1_HTML = Type_of_Absence
  If Reason <> 'Unknown' Then
    (Item1_HTML = Item1_HTML)

  Item2_Prompt = xxas_get_message('XXAS','XXAS_RB_TCM_GROUP')
  Item2_HTML = Category

  /* Set the Dates */
  Item3_Prompt = xxas_get_message('XXAS','XXAS_EMP_DATES')
  Item3_HTML = Absence_Dates

  /* Set the Duration */
  Item4_Prompt = xxas_get_message('XXAS','XXAS_EMP_DURATION')
  Item4_HTML = Duration_Text

  /* Set the reason */
  Item5_Prompt = xxas_get_message('XXAS','XXAS_EMP_ABS_REASON')
  Item5_HTML =   reason

  /* Set the start date */
  Item6_Prompt = xxas_get_message('XXAS','XXAS_ABS_SORT_START_LAT_FIRST')
  Item6_HTML = Start_Date

  Item7_Prompt = xxas_get_message('XXAS','XXAS_ABS_EMP_SEARCH_YEAR')
  Item7_HTML   = to_char(Start_Date,'YYYY')

  Item8_Prompt = xxas_get_message('XXAS','XXAS_ABS_EMP_SEARCH_MONTH')
  Item8_HTML   = to_char(Start_Date,'Mon')
  
  /* Set the start date ascending */
  Item9_Prompt = xxas_get_message('XXAS','XXAS_ABS_SORT_START_LAT_LAST')
  Item9_HTML = Start_Date



  /* Set the update/delete permissions */
  Edit_Allowed = xxas_ess_mss_policy('Y', 'Y')
  Update_Allowed = Edit_Allowed
  Delete_Allowed = Edit_Allowed

  If Absence_Attendance_Id_p was not defaulted Then
  (
  Absence_Attendance_Id = Absence_Attendance_Id_p
  )
 
  summary_text = summary_text + ' - ' + Type_of_Absence
  summary_message = Duration_Text + ' ' + summary_context

  If Transaction_Type = 'DELETE' Then
    (Summary_Text = '<s>' + Summary_Text + '</s>')

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

  If Absence_Attendance_Id = 0 Then (
      Object_Id = Object_Id + '_'
  )
  Else (
      Object_Id = Object_Id + to_char(Absence_Attendance_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 + '_' + XXAS_GET_EMP_CAL_SB() + '_'

  Target = 'modal'


  Return Update_Allowed, Delete_Allowed,
         Summary_Icon, 
         Summary_Text, 
         Summary_Message,
         Status_Color,
  /*       Detail_Header_Text, Detail_Header_Message, */
         Item1_Prompt, Item1_HTML,
         Item2_Prompt, Item2_HTML,
         Item3_Prompt, Item3_HTML,
         Item4_Prompt, Item4_HTML,
         Item5_Prompt, Item5_HTML,
         Item6_Prompt, Item6_HTML,
		 Item7_Prompt, Item7_HTML,
         Item8_Prompt, Item8_HTML,
		 Item9_Prompt, Item9_HTML,


     /*    CalEvent_Title, CalEvent_Start, CalEvent_End, */
         CalEvent_Color, CalEvent_Text_Color,Replacement_Person_Id_Visible,
Replacement_Person_Id_Enabled, Page_Id, Object_Id, Target




]';


  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;
