CREATE OR REPLACE PACKAGE terminal_config AS

/******************************************************************************
 * PROCEDURE add_stand_setup_record
 *
 * Description: Inserts a record into stand_setup
 * Parameters: p_terminal - terminal name from terminals table
 *             p_stand - new stand name
 *             p_site_id - site id at which stand exists
 * Errors raised: 20500 - no such terminal
 *                20500 - stand already exists
 ******************************************************************************/
PROCEDURE add_stand_setup_record(p_terminal IN stand_setup.terminal%TYPE,
                                 p_stand    IN stand_setup.stand%TYPE,
                                 p_site_id  IN stand_setup.site_id%TYPE);

/******************************************************************************
 * PROCEDURE del_stand_setup_record
 *
 * Description: Deletes a record from stand_setup
 * Parameters: p_stand - stand name
 *             p_site_id - site id at which stand exists
 * Errors raised: 20500 - no such stand
 *
 ******************************************************************************/
PROCEDURE del_stand_setup_record(p_stand    IN stand_setup.stand%TYPE,
                                 p_site_id  IN stand_setup.site_id%TYPE);

/******************************************************************************
 * PROCEDURE add_mct_record
 *
 * Description: Adds a record to the mct table
 * Parameters: p_agent     agent code for which the record applies
 *             p_terminal1 1st terminal connection times are between
 *             p_terminal2 2nd terminal connection times are between
 *             p_min_ct    minimum connection time between terminals
 *             p_max_ct    maximum connection time between terminals
 *             p_site_id   site id the record applies to
 * Errors raised: none.
 *
 ******************************************************************************/
PROCEDURE add_mct_record(p_agent     IN mct.agent%TYPE,
                         p_terminal1 IN mct.terminal1%TYPE,
                         p_terminal2 IN mct.terminal2%TYPE,
                         p_min_ct    IN mct.min_connection_time%TYPE,
                         p_max_ct    IN mct.max_connection_time%TYPE,
                         p_site_id   IN mct.site_id%TYPE);

/******************************************************************************
 * PROCEDURE delete_mct_record
 *
 * Description: Deletes a record from the mct table
 * Parameters: p_agent     agent code for which the record applies
 *             p_terminal1 1st terminal connection times are between
 *             p_terminal2 2nd terminal connection times are between
 *             p_site_id   site id the record applies to
 * Errors raised: 20500 - no such record.
 *
 ******************************************************************************/
PROCEDURE delete_mct_record(p_agent      IN mct.agent%TYPE,
                            p_terminal1  IN mct.terminal1%TYPE,
                            p_terminal2  IN mct.terminal2%TYPE,
                            p_site_id    IN mct.site_id%TYPE);
                            
/******************************************************************************
 * PROCEDURE update_mct_record
 *
 * Description: Updates min_ct and max_ct on a particular mct record
 * Parameters: p_agent     agent code for which the record applies
 *             p_terminal1 1st terminal connection times are between
 *             p_terminal2 2nd terminal connection times are between
 *             p_min_ct    new minimum connection time between terminals
 *             p_max_ct    new maximum connection time between terminals
 *             p_site_id   site id the record applies to
 * Errors raised: 20794 - no such record.
 *
 ******************************************************************************/                            
PROCEDURE update_mct_record(p_agent     IN mct.agent%TYPE,
                            p_terminal1 IN mct.terminal1%TYPE,
                            p_terminal2 IN mct.terminal2%TYPE,
                            p_min_ct    IN mct.min_connection_time%TYPE,
                            p_max_ct    IN mct.max_connection_time%TYPE,
                            p_site_id   IN mct.site_id%TYPE);
  
END terminal_config;
/
CREATE OR REPLACE PACKAGE BODY terminal_config AS

PROCEDURE add_stand_setup_record(p_terminal IN stand_setup.terminal%TYPE,
                                 p_stand    IN stand_setup.stand%TYPE,
                                 p_site_id  IN stand_setup.site_id%TYPE) AS

  n NUMBER;

BEGIN
  
  SELECT 1
  INTO n
  FROM terminals
  WHERE terminal = p_terminal 
  AND site_id  = p_site_id;

  INSERT INTO stand_setup
  (terminal, stand, site_id)
  VALUES
  (p_terminal, p_stand, p_site_id);

  COMMIT;
  
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    raise_application_error(-20500, 'no such terminal.');
    
  WHEN DUP_VAL_ON_INDEX THEN
    raise_application_error(-20500, 'stand already exists.');

END add_stand_setup_record;

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

PROCEDURE del_stand_setup_record(p_stand    IN stand_setup.stand%TYPE,
                                 p_site_id  IN stand_setup.site_id%TYPE)
AS

BEGIN
  
  DELETE FROM stand_setup
  WHERE stand   = p_stand 
  AND site_id = p_site_id;

  IF SQL%ROWCOUNT = 0 THEN
    raise_application_error(-20500, 'no such stand');
  END IF;
  
  COMMIT;
  
END del_stand_setup_record;

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

PROCEDURE add_mct_record(p_agent     IN mct.agent%TYPE,
                         p_terminal1 IN mct.terminal1%TYPE,
                         p_terminal2 IN mct.terminal2%TYPE,
                         p_min_ct    IN mct.min_connection_time%TYPE,
                         p_max_ct    IN mct.max_connection_time%TYPE,
                         p_site_id   IN mct.site_id%TYPE)
AS

BEGIN

  INSERT INTO mct
  (agent,
   terminal1,
   terminal2,
   min_connection_time,
   max_connection_time,
   site_id)
  VALUES
  (p_agent,
   p_terminal1,
   p_terminal2,
   p_min_ct,
   p_max_ct,
   p_site_id);

  COMMIT;
  
END add_mct_record;

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

PROCEDURE delete_mct_record(p_agent      IN mct.agent%TYPE,
                            p_terminal1  IN mct.terminal1%TYPE,
                            p_terminal2  IN mct.terminal2%TYPE,
                            p_site_id    IN mct.site_id%TYPE) AS
BEGIN

  -- attempt to delete the row
  DELETE FROM mct
  WHERE AGENT = p_agent
  AND terminal1 = p_terminal1
  AND terminal2 = p_terminal2
  AND site_id = p_site_id;

  IF SQL%ROWCOUNT = 0 THEN
    raise_application_error(-20500, 'no such record');
  END IF;
  
  COMMIT;

END delete_mct_record;

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

PROCEDURE update_mct_record(p_agent     IN mct.agent%TYPE,
                            p_terminal1 IN mct.terminal1%TYPE,
                            p_terminal2 IN mct.terminal2%TYPE,
                            p_min_ct    IN mct.min_connection_time%TYPE,
                            p_max_ct    IN mct.max_connection_time%TYPE,
                            p_site_id   IN mct.site_id%TYPE)
AS

BEGIN

  UPDATE mct
  SET min_connection_time = p_min_ct,
      max_connection_time = p_max_ct
  WHERE terminal1           = p_terminal1 
  AND terminal2           = p_terminal2 
  AND agent               = p_agent 
  AND site_id             = p_site_id;

  IF SQL%ROWCOUNT = 0 THEN
    raise_application_error(-20500, 'no such record');
  END IF;
  
  COMMIT;
  
END update_mct_record;

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

END terminal_config;
/
