CREATE OR REPLACE PACKAGE config is

/*****************************************************************************
*
* Filename         - $Workfile:   config.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live schema config package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   config.sql  $  $Revision:   1.3  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /shared/pvcs/aisarch/db_ora/db/source/live/procedures/config.svl  $
*
*    Rev 1.3   29 Nov 2002 11:27:08   mik
* CR: 15724
*
* Added the get_item() function
*
*    Rev 1.2   21 Jun 2001 14:42:18   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
*
*    Rev 1.1   20 Sep 1999 14:40:26   tagtrak
*
*
*    Rev 1.0   28 Sep 1998 13:18:14   tagtrak
* OR: 12682 12683
* CR: 12682 12683
* REV:
* Vienna enhancements - Use MACH flight numbers,
* Create flights from flight_info records
* Andrew Walker
*
****************************************************************************/



FUNCTION get_value (p_key IN db_config.db_item%TYPE)
RETURN db_config.db_value%TYPE;

PROCEDURE update_db_config(p_item   db_config.db_item%TYPE,
                           p_value  db_config.db_value%TYPE);

PROCEDURE insert_site_config(p_site_id      site_config.site_id%TYPE,
                             p_db_item      site_config.db_item%TYPE,
                             p_db_value     site_config.db_value%TYPE,
                             p_description  site_config.description%TYPE);

PROCEDURE update_site_config(p_site_id      site_config.site_id%TYPE,
                             p_db_item      site_config.db_item%TYPE,
                             p_db_value     site_config.db_value%TYPE,
                             p_description  site_config.description%TYPE);

PROCEDURE delete_site_config(p_site_id      site_config.site_id%TYPE,
                             p_db_item      site_config.db_item%TYPE);

PROCEDURE insert_carrier_site_config(p_site_id       carrier_site_config.site_id%TYPE,
                                     p_alpha_code    carrier_site_config.alpha_code%TYPE,
                                     p_config_item   carrier_site_config.config_item%TYPE,
                                     p_config_value  carrier_site_config.config_value%TYPE);

PROCEDURE update_carrier_site_config(p_site_id       carrier_site_config.site_id%TYPE,
                                     p_alpha_code    carrier_site_config.alpha_code%TYPE,
                                     p_config_item   carrier_site_config.config_item%TYPE,
                                     p_config_value  carrier_site_config.config_value%TYPE);

PROCEDURE delete_carrier_site_config(p_site_id       carrier_site_config.site_id%TYPE,
                                     p_alpha_code    carrier_site_config.alpha_code%TYPE,
                                     p_config_item   carrier_site_config.config_item%TYPE);

end config;
/
CREATE OR REPLACE PACKAGE BODY config is

FUNCTION get_value (p_key   in   db_config.db_item%TYPE)
RETURN db_config.db_value%TYPE IS

  l_result  db_config.db_value%TYPE;

BEGIN

  SELECT db_value
  INTO l_result
  FROM db_config
  WHERE db_item = p_key;

  RETURN l_result;

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN NULL;

END get_value;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE update_db_config(p_item   db_config.db_item%TYPE,
                           p_value  db_config.db_value%TYPE) IS

  l_editable  db_config.mws_editable%TYPE;

BEGIN

  SELECT mws_editable
  INTO l_editable
  FROM db_config
  WHERE db_item = p_item;

  IF l_editable = 'Y' THEN

    UPDATE db_config
    SET db_value = p_value
    WHERE db_item = p_item;

    COMMIT;

  ELSE

    raise_application_error(-20000, 'Update not allowed for this item');

  END IF;

END update_db_config;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE insert_site_config(p_site_id      site_config.site_id%TYPE,
                             p_db_item      site_config.db_item%TYPE,
                             p_db_value     site_config.db_value%TYPE,
                             p_description  site_config.description%TYPE) IS

BEGIN

  INSERT INTO site_config
  (site_id, db_item, db_value, description)
  VALUES
  (p_site_id, p_db_item, p_db_value, p_description);

  COMMIT;

END insert_site_config;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE update_site_config(p_site_id      site_config.site_id%TYPE,
                             p_db_item      site_config.db_item%TYPE,
                             p_db_value     site_config.db_value%TYPE,
                             p_description  site_config.description%TYPE) IS

BEGIN

  UPDATE site_config
  SET db_value = nvl(p_db_value, db_value),
      description = nvl(p_description, description)
  WHERE db_item = p_db_item
  AND site_id = p_site_id;

  COMMIT;

END update_site_config;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE delete_site_config(p_site_id      site_config.site_id%TYPE,
                             p_db_item      site_config.db_item%TYPE) IS

BEGIN

  DELETE site_config
  WHERE db_item = p_db_item
  AND site_id = p_site_id;

  COMMIT;

END delete_site_config;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE insert_carrier_site_config(p_site_id       carrier_site_config.site_id%TYPE,
                                     p_alpha_code    carrier_site_config.alpha_code%TYPE,
                                     p_config_item   carrier_site_config.config_item%TYPE,
                                     p_config_value  carrier_site_config.config_value%TYPE) IS

BEGIN

  INSERT INTO carrier_site_config
  (site_id, alpha_code, config_item, config_value)
  VALUES
  (p_site_id, p_alpha_code, p_config_item, p_config_value);

END insert_carrier_site_config;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE update_carrier_site_config(p_site_id       carrier_site_config.site_id%TYPE,
                                     p_alpha_code    carrier_site_config.alpha_code%TYPE,
                                     p_config_item   carrier_site_config.config_item%TYPE,
                                     p_config_value  carrier_site_config.config_value%TYPE) IS

BEGIN

  UPDATE carrier_site_config
  SET config_value = p_config_value
  WHERE site_id = p_site_id
  AND alpha_code = p_alpha_code
  AND config_item = p_config_item;

END update_carrier_site_config;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE delete_carrier_site_config(p_site_id       carrier_site_config.site_id%TYPE,
                                     p_alpha_code    carrier_site_config.alpha_code%TYPE,
                                     p_config_item   carrier_site_config.config_item%TYPE) IS

BEGIN

  DELETE carrier_site_config
  WHERE site_id = p_site_id
  AND alpha_code = p_alpha_code
  AND config_item = p_config_item;

END delete_carrier_site_config;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

END config;
/
