/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 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('0001-01-01','YYYY-MM-DD');
  l_END     CONSTANT  DATE          := xxas_util_pkg.eot;
  l_FF_NAME CONSTANT  VARCHAR2(80)  := 'XXAS_EMP_PERSONSIT_TEMPL_FF';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Template Person SIT Fast Formula';
  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'[
  
  Default for Transaction_Type            is 'NONE'
  Default for Workflow_Status_Code        is 'NONE'
  Default for Workflow_Status_Meaning     is 'NONE'
  Default for Id_Flex_Structure_Code      is 'Unknown'
  Default for Segment1_Meaning            is 'Unknown'
  Default for Segment1_Meaning_p          is 'Unknown'
  Default for Segment2_Meaning            is 'Unknown'
  Default for Segment2_Meaning_p          is 'Unknown'
  Default for Segment3_Meaning            is 'Unknown'
  Default for Segment3_Meaning_p          is 'Unknown'
  Default for Segment4_Meaning            is 'Unknown'
  Default for Segment4_Meaning_p          is 'Unknown'
  Default for Segment5_Meaning            is 'Unknown'
  Default for Segment5_Meaning_p          is 'Unknown'
  Default for Segment6_Meaning            is 'Unknown'
  Default for Segment6_Meaning_p          is 'Unknown'
  Default for Segment7_Meaning            is 'Unknown'
  Default for Segment7_Meaning_p          is 'Unknown'
  Default for Segment8_Meaning            is 'Unknown'
  Default for Segment8_Meaning_p          is 'Unknown'
  Default for Segment9_Meaning            is 'Unknown'
  Default for Segment9_Meaning_p          is 'Unknown'
  Default for Segment10_Meaning           is 'Unknown'
  Default for Segment10_Meaning_p         is 'Unknown'
  Default for Segment11_Meaning           is 'Unknown'
  Default for Segment11_Meaning_p         is 'Unknown'
  Default for Segment12_Meaning           is 'Unknown'
  Default for Segment12_Meaning_p         is 'Unknown'
  Default for Segment13_Meaning           is 'Unknown'
  Default for Segment13_Meaning_p         is 'Unknown'
  Default for Segment14_Meaning           is 'Unknown'
  Default for Segment14_Meaning_p         is 'Unknown'
  Default for Segment15_Meaning           is 'Unknown'
  Default for Segment15_Meaning_p         is 'Unknown'
  Default for Segment16_Meaning           is 'Unknown'
  Default for Segment16_Meaning_p         is 'Unknown'
  Default for Segment17_Meaning           is 'Unknown'
  Default for Segment17_Meaning_p         is 'Unknown'
  Default for Segment18_Meaning           is 'Unknown'
  Default for Segment18_Meaning_p         is 'Unknown'
  Default for Segment19_Meaning           is 'Unknown'
  Default for Segment19_Meaning_p         is 'Unknown'
  Default for Segment20_Meaning           is 'Unknown'
  Default for Segment20_Meaning_p         is 'Unknown'
  Default for Segment21_Meaning           is 'Unknown'
  Default for Segment21_Meaning_p         is 'Unknown'
  Default for Segment22_Meaning           is 'Unknown'
  Default for Segment22_Meaning_p         is 'Unknown'
  Default for Segment23_Meaning           is 'Unknown'
  Default for Segment23_Meaning_p         is 'Unknown'
  Default for Segment24_Meaning           is 'Unknown'
  Default for Segment24_Meaning_p         is 'Unknown'
  Default for Segment25_Meaning           is 'Unknown'
  Default for Segment25_Meaning_p         is 'Unknown'
  Default for Segment26_Meaning           is 'Unknown'
  Default for Segment26_Meaning_p         is 'Unknown'
  Default for Segment27_Meaning           is 'Unknown'
  Default for Segment27_Meaning_p         is 'Unknown'
  Default for Segment28_Meaning           is 'Unknown'
  Default for Segment28_Meaning_p         is 'Unknown'
  Default for Segment29_Meaning           is 'Unknown'
  Default for Segment29_Meaning_p         is 'Unknown'
  Default for Segment30_Meaning           is 'Unknown'
  Default for Segment30_Meaning_p         is 'Unknown'

  Inputs are Transaction_Type, Workflow_Status_Code, Workflow_Status_Meaning,
             Id_Flex_Structure_Code,
             Segment1_Meaning, Segment1_Meaning_p,
             Segment2_Meaning, Segment2_Meaning_p,
             Segment3_Meaning, Segment3_Meaning_p,
             Segment4_Meaning, Segment4_Meaning_p,
             Segment5_Meaning, Segment5_Meaning_p,
             Segment6_Meaning, Segment6_Meaning_p,
             Segment7_Meaning, Segment7_Meaning_p,
             Segment8_Meaning, Segment8_Meaning_p,
             Segment9_Meaning, Segment9_Meaning_p,
             Segment10_Meaning, Segment10_Meaning_p,
             Segment11_Meaning, Segment11_Meaning_p,
             Segment12_Meaning, Segment12_Meaning_p,
             Segment13_Meaning, Segment13_Meaning_p,
             Segment14_Meaning, Segment14_Meaning_p,
             Segment15_Meaning, Segment15_Meaning_p,
             Segment16_Meaning, Segment16_Meaning_p,
             Segment17_Meaning, Segment17_Meaning_p,
             Segment18_Meaning, Segment18_Meaning_p,
             Segment19_Meaning, Segment19_Meaning_p,
             Segment20_Meaning, Segment20_Meaning_p,
             Segment21_Meaning, Segment21_Meaning_p,
             Segment22_Meaning, Segment22_Meaning_p,
             Segment23_Meaning, Segment23_Meaning_p,
             Segment24_Meaning, Segment24_Meaning_p,
             Segment25_Meaning, Segment25_Meaning_p,
             Segment26_Meaning, Segment26_Meaning_p,
             Segment27_Meaning, Segment27_Meaning_p,
             Segment28_Meaning, Segment28_Meaning_p,
             Segment29_Meaning, Segment29_Meaning_p,
             Segment30_Meaning, Segment30_Meaning_p

  COLOR_GREEN    = xxas_get_color_by_name('green')
  COLOR_AMBER    = xxas_get_color_by_name('amber')
  COLOR_RED      = xxas_get_color_by_name('red')
  COLOR_GREY     = xxas_get_color_by_name('grey')
  COLOR_BLUE     = xxas_get_color_by_name('blue')
  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')

  /* Set the status color and row header based on the transaction status */
  Current_Field_Num = 1
  Pending           = 'N'
  Status_Color      = COLOR_BLUE

  If Transaction_Type was not defaulted then
    (
     Pending = 'Y'
     If Transaction_Type = 'INSERT' then
       (Status_Text  = xxas_get_message('XXAS','XXAS_EMP_NEW'))
     Else If Transaction_Type = 'UPDATE' then
       (Status_Text  = xxas_get_message('XXAS','XXAS_EMP_UPDATED'))
     Else
       (Status_Text  = xxas_get_message('XXAS','XXAS_EMP_DELETED'))

     Status_Text = Status_Text + ' ' + Workflow_Status_Meaning

     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_Color = COLOR_GREEN)
     Else
       (Status_Color = COLOR_AMBER)

    Summary_Context = '<font color="' + Status_Color + '">' + Status_Text + '</font>'
    )
  
  /* Segment1 */
  If Segment1_Meaning was not defaulted or
     Segment1_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT1'
      ,Segment1_Meaning, Pending, Segment1_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment2 */
  If Segment2_Meaning was not defaulted or
     Segment2_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT2'
      ,Segment2_Meaning, Pending, Segment2_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment3 */
  If Segment3_Meaning was not defaulted or
     Segment3_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT3'
      ,Segment3_Meaning, Pending, Segment3_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment4 */
  If Segment4_Meaning was not defaulted or
     Segment4_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT4'
      ,Segment4_Meaning, Pending, Segment4_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment5 */
  If Segment5_Meaning was not defaulted or
     Segment5_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT5'
      ,Segment5_Meaning, Pending, Segment5_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment6 */
  If Segment6_Meaning was not defaulted or
     Segment6_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT6'
      ,Segment6_Meaning, Pending, Segment6_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment7 */
  If Segment7_Meaning was not defaulted or
     Segment7_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT7'
      ,Segment7_Meaning, Pending, Segment7_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment8 */
  If Segment8_Meaning was not defaulted or
     Segment8_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT8'
      ,Segment8_Meaning, Pending, Segment8_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment9 */
  If Segment9_Meaning was not defaulted or
     Segment9_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT9'
      ,Segment9_Meaning, Pending, Segment9_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment10 */
  If Segment10_Meaning was not defaulted or
     Segment10_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT10'
      ,Segment10_Meaning, Pending, Segment10_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment11 */
  If Segment11_Meaning was not defaulted or
     Segment11_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT11'
      ,Segment11_Meaning, Pending, Segment11_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment12 */
  If Segment12_Meaning was not defaulted or
     Segment12_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT12'
      ,Segment12_Meaning, Pending, Segment12_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment13 */
  If Segment13_Meaning was not defaulted or
     Segment13_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT13'
      ,Segment13_Meaning, Pending, Segment13_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment14 */
  If Segment14_Meaning was not defaulted or
     Segment14_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT14'
      ,Segment14_Meaning, Pending, Segment14_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment15 */
  If Segment15_Meaning was not defaulted or
     Segment15_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT15'
      ,Segment15_Meaning, Pending, Segment15_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment16 */
  If Segment16_Meaning was not defaulted or
     Segment16_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT16'
      ,Segment16_Meaning, Pending, Segment16_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment17 */
  If Segment17_Meaning was not defaulted or
     Segment17_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT17'
      ,Segment17_Meaning, Pending, Segment17_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment18 */
  If Segment18_Meaning was not defaulted or
     Segment18_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT18'
      ,Segment18_Meaning, Pending, Segment18_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment19 */
  If Segment19_Meaning was not defaulted or
     Segment19_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT19'
      ,Segment19_Meaning, Pending, Segment19_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment20 */
  If Segment20_Meaning was not defaulted or
     Segment20_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT20'
      ,Segment20_Meaning, Pending, Segment20_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment21 */
  If Segment21_Meaning was not defaulted or
     Segment21_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT21'
      ,Segment21_Meaning, Pending, Segment21_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment22 */
  If Segment22_Meaning was not defaulted or
     Segment22_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT22'
      ,Segment22_Meaning, Pending, Segment22_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment23 */
  If Segment23_Meaning was not defaulted or
     Segment23_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT23'
      ,Segment23_Meaning, Pending, Segment23_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment24 */
  If Segment24_Meaning was not defaulted or
     Segment24_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT24'
      ,Segment24_Meaning, Pending, Segment24_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment25 */
  If Segment25_Meaning was not defaulted or
     Segment25_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT25'
      ,Segment25_Meaning, Pending, Segment25_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment26 */
  If Segment26_Meaning was not defaulted or
     Segment26_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT26'
      ,Segment26_Meaning, Pending, Segment26_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment27 */
  If Segment27_Meaning was not defaulted or
     Segment27_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT27'
      ,Segment27_Meaning, Pending, Segment27_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment28 */
  If Segment28_Meaning was not defaulted or
     Segment28_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT28'
      ,Segment28_Meaning, Pending, Segment28_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment29 */
  If Segment29_Meaning was not defaulted or
     Segment29_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT29'
      ,Segment29_Meaning, Pending, Segment29_Meaning_p
      ,Status_Color, 'Unknown')
      
  /* Segment30 */
  If Segment30_Meaning was not defaulted or
     Segment30_Meaning_p was not defaulted then
    Func_Result = xxas_emp_set_person_sit_field
      (Current_Field_Num
      ,Id_Flex_Structure_Code, 'SEGMENT30'
      ,Segment30_Meaning, Pending, Segment30_Meaning_p
      ,Status_Color, 'Unknown')
  
  /* Set the summary text. This defaults to the first item''s value */
  Summary_Text = xxas_emp_get_long_output('ITEM1_HTML')
  
  /* Default permissions */
  ESS_Update_Allowed = 'N'
  ESS_Delete_Allowed = 'N'
  MSS_Update_Allowed = 'N'
  MSS_Delete_Allowed = 'N'
  
  /* Autoconfig permissions: start */
  
  /* Autoconfig permissions: end */
  
  /* If you wish to override the auto-config permissions, set them
     underneath the autoconfig permissions */
  /* ESS_Update_Allowed = 'N'
     ESS_Delete_Allowed = 'N'
     MSS_Update_Allowed = 'N'
     MSS_Delete_Allowed = 'N' */

  Update_Allowed = xxas_ess_mss_policy(ESS_Update_Allowed, MSS_Update_Allowed)
  Delete_Allowed = xxas_ess_mss_policy(ESS_Delete_Allowed, MSS_Delete_Allowed)

  Return Update_Allowed, Delete_Allowed,
         Status_Text, Status_Color,
         Summary_Context, Summary_Text
             
]';


  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;
