/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2011 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_EMP_PAY_METH_VIEW';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud EmployeeCenter Payment Methods 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 := '/*==============================================================================

Formula Name: XXAS_EMP_PAY_METH_VIEW
Formula Type: Applaud Assignment
Description:  Applaud EmployeeCenter Payment Banks View

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

  Default for Priority               is ''Unknown''
  Default for Id_flex_num            is 0
  Default for Max_priority           is ''Unknown''
  Default for Payment_type_name      is ''Unknown''
  Default for Currency_code          is ''Unknown''  
  Default for Currency               is ''Unknown''
  Default for Segment1_meaning       is ''Unknown''
  Default for Segment2_meaning       is ''Unknown''
  Default for Segment3_meaning       is ''Unknown''
  Default for Segment4_meaning       is ''Unknown''
  Default for Segment5_meaning       is ''Unknown''
  Default for Segment6_meaning       is ''Unknown''
  Default for Segment7_meaning       is ''Unknown''
  Default for Segment8_meaning       is ''Unknown''
  Default for Segment9_meaning       is ''Unknown''
  Default for Segment10_meaning      is ''Unknown''
  Default for Segment11_meaning      is ''Unknown''
  Default for Segment12_meaning      is ''Unknown''
  Default for Segment13_meaning      is ''Unknown''
  Default for Segment14_meaning      is ''Unknown''
  Default for Segment15_meaning      is ''Unknown''
  Default for Segment16_meaning      is ''Unknown''
  Default for Segment17_meaning      is ''Unknown''
  Default for Segment18_meaning      is ''Unknown''
  Default for Segment19_meaning      is ''Unknown''
  Default for Segment20_meaning      is ''Unknown''
  Default for Segment21_meaning      is ''Unknown''
  Default for Segment22_meaning      is ''Unknown''
  Default for Segment23_meaning      is ''Unknown''
  Default for Segment24_meaning      is ''Unknown''
  Default for Segment25_meaning      is ''Unknown''
  Default for Segment26_meaning      is ''Unknown''
  Default for Segment27_meaning      is ''Unknown''
  Default for Segment28_meaning      is ''Unknown''
  Default for Segment29_meaning      is ''Unknown''
  Default for Segment30_meaning      is ''Unknown''
  Default for Amount                 is ''Unknown''
  Default for Percentage             is ''Unknown''
  
  
  Inputs are Priority, Id_flex_num, Max_priority, Currency_code, Payment_type_name, Currency, 
             Segment1_meaning, Segment2_meaning, Segment3_meaning,
             Segment4_meaning, Segment5_meaning, Segment6_meaning, Segment7_meaning, Segment8_meaning,
             Segment9_meaning, Segment10_meaning, Segment11_meaning, Segment12_meaning, Segment13_meaning, 
             Segment14_meaning, Segment15_meaning, Segment16_meaning, Segment17_meaning, Segment18_meaning,
             Segment19_meaning, Segment20_meaning, Segment21_meaning, Segment22_meaning, Segment23_meaning,
             Segment24_meaning, Segment25_meaning, Segment26_meaning, Segment27_meaning, Segment28_meaning,
             Segment29_meaning, Segment30_meaning, Amount, Percentage
            
  DATE_FORMAT_MASK     = ''FMDd Mon YYYY''
  
  Current_Field_Num = 1
  kff_code          = ''BANK''
  Application_id    = 801
  Percentage_symbol = ''%''
  
  /* Set the Summary text */
  
  If Payment_type_name = ''Check'' OR Payment_type_name = ''Cheque'' then
  (
    Summary_Icon = ''CREDITCARD''
    If Max_priority was not defaulted and Max_priority = ''Y'' then
    (
     Summary_Text= Payment_type_name + '' - ''+ ''Remaining Pay''     
    )    
    Else If Percentage was not defaulted then
    (
      Summary_Text= Payment_type_name + '' - ''+ Percentage + ''%''      
    )
    Else If Amount was not defaulted and Currency_code was not defaulted then
    (
      Summary_Text= Payment_type_name + '' - ''+ Currency_code +  Amount
     )  
  )
  Else If Payment_type_name = ''Deposit'' then
  (
    Summary_Icon = ''LAPTOP''
    
    If Max_priority was not defaulted and Max_priority = ''Y'' then
    (
     Summary_Text= Payment_type_name + '' - ''+ ''Remaining Pay''      
    )
    Else If Percentage was not defaulted then
    (
      Summary_Text= Payment_type_name + '' - ''+ Percentage + ''%''
    )
    Else If Amount was not defaulted and Currency_code was not defaulted then
    (
      Summary_Text= Payment_type_name + '' - ''+ Currency_code + Amount
    )
  )
  Else If Payment_type_name = ''Cash'' then
  (
    Summary_Icon = ''COINSTACK''
    
    If Max_priority was not defaulted and Max_priority = ''Y'' then
    (
     Summary_Text= Payment_type_name + '' - ''+ ''Remaining Pay''      
    )
    Else If Percentage was not defaulted then
    (
      Summary_Text= Payment_type_name +'' - ''+ Percentage + ''%''
    )
    Else If Amount was not defaulted and Currency_code was not defaulted then
    (
      Summary_Text= Payment_type_name +'' - '' + Currency_code + Amount
    )
  )  
  
  /* Set the Detail Header Context */  
  Detail_Header_Context = Summary_Text
    
  /* Set the priority field */
  If priority was not defaulted then (
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, xxas_get_message(''XXAS'', ''XXAS_EMP_PM_PTY''), Priority)
  )
  
  /* Set the Payment Type field */
  If Payment_type_name was not defaulted then (
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, xxas_get_message(''XXAS'', ''XXAS_EMP_PM_PAY_TYPE''), Payment_type_name)
  )
  
  /* Set the Segment1 Meaning field */
  If Segment1_Meaning was not defaulted Then
  (
    Segment1_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT1'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment1_Prompt, Segment1_Meaning)
  )

  /* Set the Segment2 Meaning field */
  If Segment2_Meaning was not defaulted Then
  (
    Segment2_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT2'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment2_Prompt, Segment2_Meaning)
  )
  
  /* Set the Segment3 Meaning field */
  If Segment3_Meaning was not defaulted Then
  (
    Segment3_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT3'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment3_Prompt, Segment3_Meaning)
  )
  
  /* Set the Segment4 Meaning field */
  If Segment4_Meaning was not defaulted Then
  (
    Segment4_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT4'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment4_Prompt, Segment4_Meaning)
  )
  
  /* Set the Segment5 Meaning field */
  If Segment5_Meaning was not defaulted Then
  (
    Segment5_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT5'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment5_Prompt, Segment5_Meaning)
  )
  
  /* Set the Segment6 Meaning field */
  If Segment6_Meaning was not defaulted Then
  (
    Segment6_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT6'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment6_Prompt, Segment6_Meaning)
  )
  
  /* Set the Segment7 Meaning field */
  If Segment7_Meaning was not defaulted Then
  (
    Segment7_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT7'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment7_Prompt, Segment7_Meaning)
  )
  
  /* Set the Segment8 Meaning field */
  If Segment8_Meaning was not defaulted Then
  (
    Segment8_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT8'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment8_Prompt, Segment8_Meaning)
  )
  
  /* Set the Segment9 Meaning field */
  If Segment9_Meaning was not defaulted Then
  (
    Segment9_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT9'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment9_Prompt, Segment9_Meaning)
  )
  
  /* Set the Segment10 Meaning field */
  If Segment10_Meaning was not defaulted Then
  (
    Segment10_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT10'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment10_Prompt, Segment10_Meaning)
  )
  
  /* Set the Segment11 Meaning field */
  If Segment11_Meaning was not defaulted Then
  (
    Segment11_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT11'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment11_Prompt, Segment11_Meaning)
  )
  
  /* Set the Segment12 Meaning field */
  If Segment12_Meaning was not defaulted Then
  (
    Segment12_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT12'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment12_Prompt, Segment12_Meaning)
  )
  
  /* Set the Segment13 Meaning field */
  If Segment13_Meaning was not defaulted Then
  (
    Segment13_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT13'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment13_Prompt, Segment13_Meaning)
  )
  
  /* Set the Segment14 Meaning field */
  If Segment14_Meaning was not defaulted Then
  (
    Segment14_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT14'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment14_Prompt, Segment14_Meaning)
  )
  
  /* Set the Segment15 Meaning field */
  If Segment15_Meaning was not defaulted Then
  (
    Segment15_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT15'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment15_Prompt, Segment15_Meaning)
  )
  
  /* Set the Segment16 Meaning field */
  If Segment16_Meaning was not defaulted Then
  (
    Segment16_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT16'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment16_Prompt, Segment16_Meaning)
  )
  
  /* Set the Segment17 Meaning field */
  If Segment17_Meaning was not defaulted Then
  (
    Segment17_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT17'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment17_Prompt, Segment17_Meaning)
  )
  
  /* Set the Segment18 Meaning field */
  If Segment18_Meaning was not defaulted Then
  (
    Segment18_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT18'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment18_Prompt, Segment18_Meaning)
  )
  
  /* Set the Segment19 Meaning field */
  If Segment19_Meaning was not defaulted Then
  (
    Segment19_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT19'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment19_Prompt, Segment19_Meaning)
  )
    
  /* Set the Segment20 Meaning field */
  If Segment20_Meaning was not defaulted Then
  (
    Segment20_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT20'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment20_Prompt, Segment20_Meaning)
  )
  
  /* Set the Segment21 Meaning field */
  If Segment21_Meaning was not defaulted Then
  (
    Segment21_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT21'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment21_Prompt, Segment21_Meaning)
  )
  
  /* Set the Segment22 Meaning field */
  If Segment22_Meaning was not defaulted Then
  (
    Segment22_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT22'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment22_Prompt, Segment22_Meaning)
  )
  
  /* Set the Segment23 Meaning field */
  If Segment23_Meaning was not defaulted Then
  (
    Segment23_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT23'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment23_Prompt, Segment23_Meaning)
  )
  
  /* Set the Segment24 Meaning field */
  If Segment24_Meaning was not defaulted Then
  (
    Segment24_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT24'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment24_Prompt, Segment24_Meaning)
  )
  
  /* Set the Segment25 Meaning field */
  If Segment25_Meaning was not defaulted Then
  (
    Segment25_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT25'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment25_Prompt, Segment25_Meaning)
  )
  
  /* Set the Segment26 Meaning field */
  If Segment26_Meaning was not defaulted Then
  (
    Segment26_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT26'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment26_Prompt, Segment26_Meaning)
  )
  
  /* Set the Segment27 Meaning field */
  If Segment27_Meaning was not defaulted Then
  (
    Segment27_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT27'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment27_Prompt, Segment27_Meaning)
  )
  
  /* Set the Segment28 Meaning field */
  If Segment28_Meaning was not defaulted Then
  (
    Segment28_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT28'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment28_Prompt, Segment28_Meaning)
  )
  
  /* Set the Segment29 Meaning field */
  If Segment29_Meaning was not defaulted Then
  (
    Segment29_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT29'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment29_Prompt, Segment29_Meaning)
  )
  
  /* Set the Segment30 Meaning field */
  If Segment30_Meaning was not defaulted Then
  (
    Segment30_Prompt = xxas_emp_get_kff_seg_name(kff_code, id_flex_num, ''SEGMENT30'',Application_id)
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, Segment30_Prompt, Segment30_Meaning)
  )
  
  /* Set the Amount field */
  If Max_priority =''Y'' then 
  (
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, xxas_get_message(''XXAS'', ''XXAS_EMP_PM_AMT''), ''Remaining Pay'')
   )  
  Else If Amount was not defaulted and Currency_code was not defaulted then 
  (    
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, xxas_get_message(''XXAS'', ''XXAS_EMP_PM_AMT''), Currency_code + Amount)
   )
  Else If Percentage was not defaulted then 
  (
    Func_Result = xxas_emp_set_text_field(Current_Field_Num, xxas_get_message(''XXAS'', ''XXAS_EMP_PM_AMT''), Percentage + '' '' +Percentage_symbol)
   )
  
  /* Set the update/delete permissions to employee-only
     (prevent manager updates out the box)            */
  Edit_Allowed = xxas_ess_mss_policy(''Y'', ''N'')
  Update_Allowed = Edit_Allowed
  Delete_Allowed = ''Y''

  /* The return value here is not actually used because the return items are set above instead */
    Return Update_Allowed,
           Delete_Allowed,
           Summary_Icon,
           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;
