/* $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_EDIT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Applaud EmployeeCenter Payment Methods Edit';
  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_PAY_METH_EDIT
Formula Type: Applaud Assignment
Description:  Payment Methods editing view 

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

Alias sysdate As Todays_Date

Default for Org_payment_method_id_value_in is 0
Default for payment_type_name_value_in     is 'Unknown'
Default for Amount_type_Value_in           is 'Unknown'
Default for Priority_Value_in is 0
Default for Priority_Description is 'Unknown'
Default for Personal_Payment_Method_Id_Value_In is 0

Inputs are Org_payment_method_id_value_in, payment_type_name_value_in, Amount_type_Value_in,Priority_Value_In,Personal_Payment_Method_Id_Value_In

  Payment_type_name                       = 'Unknown'
  Org_payment_method_id_Refresh_on_Change = 'true'
  Priority_Visible                        ='false'
  Segment1_Visible                        ='false'
  Segment1_Required                       ='false'
  Segment2_Visible                        ='false'
  Segment2_Required                       ='false'
  Segment3_Visible                        ='false'
  Segment3_Required                       ='false'
  Segment4_Visible                        ='false'
  Segment4_Required                       ='false'
  Segment5_Visible                        ='false'
  Segment5_Required                       ='false'
  Segment6_Visible                        ='false'
  Segment6_Required                       ='false'
  Attribute_Category_Visible              = 'false'
  Attribute1_Visible                      ='false'
  Amount_type_Visible                     ='false'
  Amount_type_Refresh_on_Change           = 'true'
  Amount_Visible                          = 'false'
  Percentage_Visible                      = 'false'
  

  l_temp = xxas_chk_dup_payment_priority(Priority_Value_In,TRUNC(SYSDATE),Personal_Payment_Method_Id_Value_In )
  If  l_temp = 'Y' Then
  (
    Priority_Description = xxas_get_message('XXAS','XXAS_DUP_PRIORITY_PAYMENTS')
  )
  /* Determine if the Payment type is selected */
  If Org_payment_method_id_value_in > 0 Then
    (Has_Payment_Method = 'Y')
  Else
    (Has_Payment_Method = 'N')

  /* Toggle the visibility of the Segments*/
  If Has_Payment_Method= 'Y' Then
  (
    /* Get Payment Type Name information */

    Payment_type_name = xxas_emp_get_payment_method_name
      (Org_payment_method_id_value_in)
   )

    If Payment_type_name = 'Check' or Payment_type_name = 'Cheque' then
     (
      Priority_Visible ='true'
      Amount_type_Visible  ='true'
     )
     Else If Payment_type_name = 'Deposit' then
     (
      Priority_Visible     ='true'
      Segment1_Visible     ='true'
      Segment2_Visible     ='true'
      Segment3_Visible     ='true'
      Segment4_Visible     ='true'
      Segment5_Visible     ='true'
      Segment6_Visible     ='true'
      Amount_type_Visible  ='true'
     )

   IF payment_type_name_value_in was not defaulted then
   (
     If payment_type_name_value_in = 'Check' or payment_type_name_value_in = 'Cheque' then
     (
      Priority_Visible ='true'
      Amount_type_Visible  ='true'
     )
     Else If payment_type_name_value_in = 'Deposit' then
     (
      Priority_Visible     ='true'
      Segment1_Visible     ='true'
      Segment2_Visible     ='true'
      Segment3_Visible     ='true'
      Segment4_Visible     ='true'
      Segment5_Visible     ='true'
      Segment6_Visible     ='true'
      Amount_type_Visible  ='true'
     )
   )
  /* Toggle the visibility of Amount/Percentage */
  If Amount_type_Value_in was not defaulted then
  (
    If Amount_type_Value_in = 'MONETARY' Then
    (
    Amount_Visible = 'true'
    Amount_Required = 'true'
    Percentage_Visible = 'false'
    )
    Else If Amount_type_Value_in = 'PERCENTAGE' Then
    (
    Amount_Visible = 'false'
    Percentage_Required = 'true'
    Percentage_Visible = 'true'
    )
   )

  If Personal_Payment_Method_Id_Value_In Was Defaulted Then
    Priority_Visible ='false'

RETURN Org_payment_method_id_Refresh_on_Change, Priority_Visible,
       Segment1_Visible,Segment2_Visible, Segment3_Visible,Segment4_Visible, Segment5_Visible,Segment6_Visible, Attribute_Category_Visible,
       Attribute1_Visible, Amount_type_Visible, Amount_type_Refresh_on_Change, Amount_Visible, Percentage_Visible, Amount_Required, Percentage_Required,Priority_Description

 /*,
       Segment1_Required,Segment2_Required,Segment3_Required,Segment4_Required,Segment5_Required,
       Segment6_Required         */
    ]';
  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;
