/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2008 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_PG_ADD_OBJECT_EDIT';
  l_FF_DESC CONSTANT  VARCHAR2(240) := 'Edit Formula for Add Object Edit Page';
  l_FF_TYPE CONSTANT  varchar2(30)  := 'Applaud Person';
  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_PG_ADD_OBJECT_EDIT
Formula Type: Applaud Person
Description:  Edit Formula for Add Object Edit Page

==============================================================================*/
Default for Object_Source_Value_In is 'Unknown'
Default for Selected_Object_Value_In is 'Unknown'
/*Default for Display_Name_Value_In is 'Unknown'
Default for Database_Object_Name_Value_In is 'Unknown'
Default for Code_Value_In is 'Unknown'
Default for Pk1_Column_name_Value_In is 'Unknown'
Default for Pk2_Column_name_Value_In is 'Unknown'
Default for Pk3_Column_name_Value_In is 'Unknown'
Default for Pk4_Column_name_Value_In is 'Unknown'
Default for Pk5_Column_name_Value_In is 'Unknown'
Default for View_Sql_Value_In is 'Unknown'
*/
Inputs are Object_Source_Value_In,Selected_Object_Value_In,Display_Name_Value_In, Database_Object_Name_Value_In, Code_Value_In
,Pk1_Column_name_Value_In, Pk2_Column_name_Value_In, Pk3_Column_name_Value_In, Pk4_Column_name_Value_In, Pk5_Column_name_Value_In,View_Sql_Value_In,View_Sql_Value

  Selected_Object_Visible = 'false'
  Display_name_Visible = 'false'
  Database_Object_Name_Visible = 'false'
  Display_Name_New_Visible = 'false'
  Application_Id_Visible = 'false'
  Code_Visible = 'false'
  New_Code_Visible = 'false'
  Description_Visible = 'false'
  Pk1_Column_Name_Visible = 'false'
  Pk2_Column_Name_Visible = 'false'
  Pk3_Column_Name_Visible = 'false'
  Pk4_Column_Name_Visible = 'false'
  Pk5_Column_Name_Visible = 'false'
  View_Sql_Visible = 'false'

  E_Pk1_Column_Name_Visible = 'false'
  E_Pk2_Column_Name_Visible = 'false'
  E_Pk3_Column_Name_Visible = 'false'
  E_Pk4_Column_Name_Visible = 'false'
  E_Pk5_Column_Name_Visible = 'false'  

  Display_name_Enabled = 'false'
  Display_Name_New_Enabled = 'false'
  Database_Object_Name_Enabled = 'false'
  Code_Enabled = 'false'
  Description_Enabled = 'false'
  Pk1_Column_Name_Enabled = 'false'
  Pk2_Column_Name_Enabled = 'false'
  Pk3_Column_Name_Enabled = 'false'
  Pk4_Column_Name_Enabled = 'false'
  Pk5_Column_Name_Enabled = 'false'
  View_Sql_Enabled = 'false'

  If Object_Source_Value_In = 'NEW_OBJECT' Then
  (
    Display_name_Visible = 'True'
    Display_name_Enabled = 'True'
    Application_Id_Visible = 'True'
    Database_Object_Name_Visible = 'True'
    Database_Object_Name_Enabled = 'True'
    Description_Visible = 'True'
    Description_Enabled = 'True'
    Code_Visible = 'True'
    Code_Enabled = 'True'
    Pk1_Column_Name_Visible = 'True'
    Pk2_Column_Name_Visible = 'True'
    Pk3_Column_Name_Visible = 'True'
    Pk4_Column_Name_Visible = 'True'
    Pk5_Column_Name_Visible = 'True'
    Pk1_Column_Name_Enabled = 'True'
    Pk2_Column_Name_Enabled = 'True'
    Pk3_Column_Name_Enabled = 'True'
    Pk4_Column_Name_Enabled = 'True'
    Pk5_Column_Name_Enabled = 'True'
 
    View_Sql_Visible = 'True'
    View_Sql_Enabled = 'True'
  )

  If Object_Source_Value_In = 'EXISTING_OBJECT' Then
  (
    Application_Id_Visible = 'True'
    Selected_Object_Visible = 'True'

    /*Database_Object_Name_Value = 'Unknown'*/
    
    If Selected_Object_Value_In Was Not Defaulted Then
    (

      View_Sql_Visible = 'false' 
      Display_name_Visible = 'True'
      Display_Name_Enabled = 'True'
      
      /*Database_Object_Name_Visible = 'True'
      Database_Object_Name_Label = 'Database Object Name'*/
      Code_Enabled = 'True'
      Code_Visible = 'True'
      Description_Visible = 'True'
      Description_Enabled = 'True'    
      E_Pk1_Column_Name_Visible = 'True'
      E_Pk2_Column_Name_Visible = 'True'
      E_Pk3_Column_Name_Visible = 'True'
      E_Pk4_Column_Name_Visible = 'True'
      E_Pk5_Column_Name_Visible = 'True'
      E_Pk1_Column_Name_Enabled = 'True'
      E_Pk2_Column_Name_Enabled = 'True'
      E_Pk3_Column_Name_Enabled = 'True'
      E_Pk4_Column_Name_Enabled = 'True'
      E_Pk5_Column_Name_Enabled = 'True'      

    )
  )  


Return Selected_Object_Visible, Display_name_Visible, Database_Object_Name_Visible, Code_Visible, Description_Visible, Pk1_Column_Name_Visible, Pk2_Column_Name_Visible, Pk3_Column_Name_Visible, Pk4_Column_Name_Visible, Pk5_Column_Name_Visible, View_Sql_Visible,  Display_name_Enabled, Database_Object_Name_Enabled, Code_Enabled, Description_Enabled, Pk1_Column_Name_Enabled, Pk2_Column_Name_Enabled, Pk3_Column_Name_Enabled, Pk4_Column_Name_Enabled, Pk5_Column_Name_Enabled, View_Sql_Enabled, View_Sql_Value
,Display_name_New_Visible,E_Pk1_Column_Name_Visible,Application_Id_Visible,
E_Pk2_Column_Name_Visible,E_Pk3_Column_Name_Visible,E_Pk4_Column_Name_Visible,E_Pk5_Column_Name_Visible,E_Pk1_Column_Name_Enabled,E_Pk2_Column_Name_Enabled,
E_Pk3_Column_Name_Enabled,E_Pk4_Column_Name_Enabled,E_Pk5_Column_Name_Enabled

]';


  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;
