/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2014 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('0001-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_EMP_VIEW_ASG_HIST_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Assignment History View';
  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);

  --
  -- 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_EMP_VIEW_ASG_HIST_VIEW
Formula Type: Applaud Assignment
Description:  Assignment History
==============================================================================*/

  Default for Start_Date               is '0001/01/01 00:00:00' (date)
  Default for Assignment_Status        is 'Null'
  Default for Assignment_Status_Prev   is 'Null'
  Default for Change_Reason            is 'Null'
  Default for Supervisor               is 'Null'
  Default for Supervisor_Prev          is 'Null'
  Default for Job                      is 'Null'
  Default for Job_Prev                 is 'Null'  
  Default for Position                 is 'Null'
  Default for Position_Prev            is 'Null'  
  Default for Grade                    is 'Null'
  Default for Grade_Prev               is 'Null'  
  Default for Department               is 'Null'
  Default for Department_Prev          is 'Null'  
  Default for Location                 is 'Null'
  Default for Location_Prev            is 'Null'  
  Default for People_Group             is 'Null'
  Default for People_Group_Prev        is 'Null'
  Default for Employment_Category      is 'Null'
  Default for Employment_Category_Prev is 'Null'
  Default for Payroll                  is 'Null'
  Default for Payroll_Prev             is 'Null'
  Default for Working_Hours            is 'Null'
  Default for Working_Hours_Prev       is 'Null'
  Default for Working_Frequency        is 'Null'
  Default for Working_Frequency_Prev   is 'Null'

  Inputs are Start_Date, Assignment_Status, Assignment_Status_Prev
            ,Change_Reason, Supervisor, Supervisor_Prev, Job, Job_Prev
            ,Position, Position_Prev, Grade, Grade_Prev, Department
            ,Department_Prev, Location, Location_Prev, People_Group
            ,People_Group_Prev, Employment_Category, Employment_Category_Prev
            ,Payroll, Payroll_Prev, Working_Hours, Working_Hours_Prev
            ,Working_Frequency, Working_Frequency_Prev

  /*************************************************************************/
  /*  BLOCK #1 - Change to meet your requirements for summary rows         */
  /*************************************************************************/

  COLOR_GREY        = '#333333'
  Number_of_Changes = 0
  Change_1          = 'Null'
  Change_2          = 'Null'
  Change_3          = 'Null'
  Change_Fields     = 'Null'

  /* Combine hours and frequency */
  Hours      = 'Null'
  Hours_Prev = 'Null'
  If Working_hours was not defaulted Then
  (
    Hours = Working_hours
    If Working_Frequency was not defaulted Then
      (Hours = Hours + ' - ' + Working_Frequency)
  )
  
  If Working_hours_Prev was not defaulted Then
  (
    Hours_Prev = Working_hours_Prev
    If Working_Frequency_Prev was not defaulted Then
      (Hours_Prev = Hours_Prev + ' - ' + Working_Frequency_Prev)
  )
  
  /* Work out which fields have changed and highlight the differences */
  
  If Department <> Department_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'department')
      Else
        (Change_Fields = Change_Fields + ', department')
      Summary_Icon = 'orgchart'
      If Department_Prev was defaulted Then
        (This_Text = 'Department added: ' + Department)
      Else If Department was defaulted Then
        (This_Text = 'Department removed: <font color="#999999"><s>' + Department_Prev + '</s></font>')
      Else
        (This_Text = 'Department: ' + Department + ' <font color="#999999"><s>' + Department_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Location <> Location_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'location')
      Else
        (Change_Fields = Change_Fields + ', location')
      Summary_Icon = 'location'
      If Location_Prev was defaulted Then
        (This_Text = 'Location added: ' + Location)
      Else If Location was defaulted Then
        (This_Text = 'Location removed: <font color="#999999"><s>' + Location_Prev + '</s></font>')
      Else
        (This_Text = 'Location: ' + Location + ' <font color="#999999"><s>' + Location_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Supervisor <> Supervisor_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'manager')
      Else
        (Change_Fields = Change_Fields + ', manager')
      Summary_Icon = 'diversity'
      If Supervisor_Prev was defaulted Then
        (This_Text = 'Manager added: ' + Supervisor)
      Else If Supervisor was defaulted Then
        (This_Text = 'Manager removed: <font color="#999999"><s>' + Supervisor_Prev + '</s></font>')
      Else
        (This_Text = 'Manager: ' + Supervisor + ' <font color="#999999"><s>' + Supervisor_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Job <> Job_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'job')
      Else
        (Change_Fields = Change_Fields + ', job')
      Summary_Icon = 'leaf'
      If Job_Prev was defaulted Then
        (This_Text = 'Job added: ' + Job)
      Else If Job was defaulted Then
        (This_Text = 'Job removed: <font color="#999999"><s>' + Job_Prev + '</s></font>')
      Else
        (This_Text = 'Job: ' + Job + ' <font color="#999999"><s>' + Job_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Position <> Position_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'position')
      Else
        (Change_Fields = Change_Fields + ', position')
      Summary_Icon = 'changeperson'
      If Position_Prev was defaulted Then
        (This_Text = 'Position added: ' + Position)
      Else If Position was defaulted Then
        (This_Text = 'Position removed: <font color="#999999"><s>' + Position_Prev + '</s></font>')
      Else
        (This_Text = 'Position: ' + Position + ' <font color="#999999"><s>' + Position_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Hours <> Hours_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'hours')
      Else
        (Change_Fields = Change_Fields + ', hours')
      Summary_Icon = 'clock'
      If Hours_Prev = 'Null' Then
        (This_Text = 'Hours added: ' + Hours)
      Else If Hours = 'Null' Then
        (This_Text = 'Hours removed: <font color="#999999"><s>' + Hours_Prev + '</s></font>')
      Else
        (This_Text = 'Hours: ' + Hours + ' <font color="#999999"><s>' + Hours_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Grade <> Grade_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'grade')
      Else
        (Change_Fields = Change_Fields + ', grade')
      Summary_Icon = 'biline'
      If Grade_Prev was defaulted Then
        (This_Text = 'Grade added: ' + Grade)
      Else If Grade was defaulted Then
        (This_Text = 'Grade removed: <font color="#999999"><s>' + Grade_Prev + '</s></font>')
      Else
        (This_Text = 'Grade: ' + Grade + ' <font color="#999999"><s>' + Grade_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Payroll <> Payroll_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'payroll')
      Else
        (Change_Fields = Change_Fields + ', payroll')
      Summary_Icon = 'banknotecrown'
      If Payroll_Prev was defaulted Then
        (This_Text = 'Payroll added: ' + Payroll)
      Else If Payroll was defaulted Then
        (This_Text = 'Payroll removed: <font color="#999999"><s>' + Payroll_Prev + '</s></font>')
      Else
        (This_Text = 'Payroll: ' + Payroll + ' <font color="#999999"><s>' + Payroll_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Assignment_Status <> Assignment_Status_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'status')
      Else
        (Change_Fields = Change_Fields + ', status')
      Summary_Icon = 'banknotecrown'
      If Assignment_Status_Prev was defaulted Then
        (This_Text = 'Status added: ' + Assignment_Status)
      Else If Assignment_Status was defaulted Then
        (This_Text = 'Status removed: <font color="#999999"><s>' + Assignment_Status_Prev + '</s></font>')
      Else
        (This_Text = 'Status: ' + Assignment_Status + ' <font color="#999999"><s>' + Assignment_Status_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If Employment_Category <> Employment_Category_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'category')
      Else
        (Change_Fields = Change_Fields + ', category')
      Summary_Icon = 'banknotecrown'
      If Employment_Category_Prev was defaulted Then
        (This_Text = 'Category added: ' + Employment_Category)
      Else If Employment_Category was defaulted Then
        (This_Text = 'Category removed: <font color="#999999"><s>' + Employment_Category_Prev + '</s></font>')
      Else
        (This_Text = 'Category: ' + Employment_Category + ' <font color="#999999"><s>' + Employment_Category_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  If People_Group <> People_Group_Prev Then
  (
    Number_of_Changes = Number_of_Changes + 1
    If Number_of_Changes <= 3 Then
    (
      If Change_Fields = 'Null' Then
        (Change_Fields = 'additional details')
      Else
        (Change_Fields = Change_Fields + ', additional details')
      Summary_Icon = 'banknotecrown'
      If People_Group_Prev was defaulted Then
        (This_Text = 'Additional Details added: ' + People_Group)
      Else If People_Group was defaulted Then
        (This_Text = 'Additional Details removed: <font color="#999999"><s>' + People_Group_Prev + '</s></font>')
      Else
        (This_Text = 'Additional Details: ' + People_Group + ' <font color="#999999"><s>' + People_Group_Prev + '</s></font>')
        
      If Number_of_Changes = 1 Then
        (Change_1 = This_Text)
      Else If Number_of_Changes = 2 Then
        (Change_2 = This_Text)
      Else
        (Change_3 = This_Text)
    )
  )
  
  Timeline_Text = to_char(start_date, 'FMDD Month YYYY')
  
  If Number_of_Changes = 0 Then
  (
    Summary_Icon = 'circlequestion'
    Summary_Text = xxas_get_message('XXAS','XXAS_RB_JOB_CHANGES_UNKNOWN')
  )
  Else If Number_of_Changes > 1 Then
  (
    Summary_Icon = 'multiple'
  )
      
  If Number_of_Changes > 0 Then
  (
    Summary_Context = 'Change of ' + Change_Fields
    If Number_of_Changes > 3 Then
      (Summary_Context = Summary_Context + ' + ' + to_char(Number_of_Changes - 3) + ' more')
    If Number_of_Changes = 1 Then
      (Summary_Text = Change_1)
    Else If Number_of_Changes = 2 Then
    (
      Func_Result = xxas_emp_set_long_output
                     ('SUMMARY_TEXT', 'XXAS', 'XXAS_COM_TEXT'
                     ,'TEXT', Change_1 + '<br/>' + Change_2)
    )
    Else
    (
      Func_Result = xxas_emp_set_long_output
                     ('SUMMARY_TEXT', 'XXAS', 'XXAS_COM_TEXT'
                     ,'TEXT', Change_1 + '<br/>' + Change_2 + '<br/>' + Change_3)   
    ) 
  )

  /*************************************************************************/
  /*  End of BLOCK #1                                                      */
  /*************************************************************************/


  /*************************************************************************/
  /*  BLOCK #2 - No need to change if you want the Summary info repeated   */
  /*             on the header                                             */
  /*************************************************************************/

  /* Set the detail header to the summary header, no need to repeat
     the other fields - that just uses vertical space  */
  If Number_of_Changes > 0 Then
    (Detail_Header_Context = Summary_Context)

  /*************************************************************************/
  /*  End of BLOCK #2                                                      */
  /*************************************************************************/

  /*************************************************************************/
  /*  BLOCK #3 - Detail fields                                             */
  /*************************************************************************/

  Current_Field_Num = 1
  
  /* Set the Change Reason */
  If Change_Reason was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_CHANGE_REASON')
      ,Change_Reason)
  
  /* Set the Department */
  If Department was not defaulted or Department_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_RB_EMP_DEPT')
      ,Department_Prev, 'Y', Department, COLOR_GREY, 'Null')

  /* Set the Location */
  If Location was not defaulted or Location_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_RB_EMP_LOC')
      ,Location_Prev, 'Y', Location, COLOR_GREY, 'Null')

  /* Set the Manager */
  If Supervisor was not defaulted or Supervisor_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_RB_EMP_MGR')
      ,Supervisor_Prev, 'Y', Supervisor, COLOR_GREY, 'Null')
    
  /* Set the Job */
  If Job was not defaulted or Job_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_JOB')
      ,Job_Prev, 'Y', Job, COLOR_GREY, 'Null')
    
  /* Set the Position */
  If Position was not defaulted or Position_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_RB_EMP_POSITION')
      ,Position_Prev, 'Y', Position, COLOR_GREY, 'Null')
    
  /* Set the Hours */
  If Hours <> 'Null' Or Hours_Prev <> 'Null' Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_WORK_HOURS')
      ,Hours_Prev, 'Y', Hours, COLOR_GREY, 'Null')
   
  /* Set the Grade */
  If Grade was not defaulted or Grade_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_GRADE')
      ,Grade_Prev, 'Y', Grade, COLOR_GREY, 'Null')
    
  /* Set the Payroll */
  If Payroll was not defaulted or Payroll_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_PAYROLL')
      ,Payroll_Prev, 'Y', Payroll, COLOR_GREY, 'Null')   
    
  /* Set the Status */
  If Assignment_Status was not defaulted or Assignment_Status_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_ASSI_STATUS')
      ,Assignment_Status_Prev, 'Y', Assignment_Status, COLOR_GREY, 'Null')   

  /* Set the Category */
  If Employment_Category was not defaulted or Employment_Category_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_ASSI_CAT')
      ,Employment_Category_Prev, 'Y', Employment_Category, COLOR_GREY, 'Null')      
 
  /* Set the People Group */
  If People_Group was not defaulted or People_Group_Prev was not defaulted Then
    Func_Result = xxas_emp_set_text_field
      (Current_Field_Num, xxas_get_message('XXAS','XXAS_EMP_PEOPLE_GROUP')
      ,People_Group_Prev, 'Y', People_Group, COLOR_GREY, 'Null')   
  

  /*************************************************************************/
  /*  RETURN BLOCK - Remove ItemX_Prompt/HTML attributes that aren't used  */
  /*************************************************************************/

  Return Timeline_Text
        ,Summary_Icon
        ,Summary_Context
        ,Summary_Text
        ,Detail_Header_Context
        
]';


  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;
