rem *********************************************************************
rem *                                                                   *
rem *             Copyright (c) Ultra Electronics Limited 2007.         *
rem *                           All rights reserved.                    *
rem *                                                                   *
rem *********************************************************************
rem *                                                                   *
rem * Filename:             reflighting_config.sql                      *
rem * Module Name:          Database                                    *
rem * Subsystem Name:       UltraTrak                                   *
rem * Originating Author:   Rob Cooke                                   *
rem * Design Reference:     UAS/FALCP/CA/02723                          *
rem *                                                                   *
rem *********************************************************************
rem *                                                                   *
rem * Description:                                                      *
rem *        Package of procedures to maintain reflighting config data  *
rem *                                                                   *
rem *********************************************************************/

CREATE OR REPLACE PACKAGE reflighting_config is

/******************************************************************************
 *
 * PROCEDURE add_reflight_setup
 *
 * Description:
 *   Inserts a reflight_setup_record
 *
 * Parameters:
 *   p_site_id          site_id record is for  
 *   p_carrier          carrier record is for 
 *   p_bags_per_flight  number of reflighted bags allowed on flight
 *   p_mins_in_future   Minimum number of minutes allowed between the flight the bags are
 *                      on and any flight they can be reflighted to.
 *   p_send_wt_msg      flag to indicate if a world tracer message should be sent
 *   p_tag_default      tag_default
 *   p_tag_other        tag_other
 *
 * Errors raised:
 *   none
 *
 ******************************************************************************/
PROCEDURE add_reflight_setup(p_site_id          reflight_setup.site_id%TYPE, 
                             p_carrier          reflight_setup.carrier%TYPE, 
                             p_bags_per_flight  reflight_setup.bags_per_flight%TYPE,
                             p_mins_in_future   reflight_setup.mins_in_future%TYPE,
                             p_send_wt_msg      reflight_setup.send_wt_msg%TYPE,
                             p_tag_default      reflight_setup.tag_default%TYPE,
                             p_tag_other        reflight_setup.tag_other%TYPE);
                             
/******************************************************************************
 *
 * PROCEDURE update_reflight_setup
 *
 * Description:
 *   Updates a reflight_setup_record
 *
 * Parameters:
 *   Same as add_reflight_setup above.
 *
 * Errors raised:
 *   none
 *
 ******************************************************************************/
PROCEDURE update_reflight_setup(p_site_id          reflight_setup.site_id%TYPE, 
                                p_carrier          reflight_setup.carrier%TYPE, 
                                p_bags_per_flight  reflight_setup.bags_per_flight%TYPE,
                                p_mins_in_future   reflight_setup.mins_in_future%TYPE,
                                p_send_wt_msg      reflight_setup.send_wt_msg%TYPE,
                                p_tag_default      reflight_setup.tag_default%TYPE,
                                p_tag_other        reflight_setup.tag_other%TYPE);

/******************************************************************************
 *
 * PROCEDURE delete_reflight_setup
 *
 * Description:
 *   Deletes a reflight_setup_record
 *
 * Parameters:
 *   p_site_id          site_id record is for  
 *   p_carrier          carrier record is for 
 *
 * Errors raised:
 *   none
 *
 ******************************************************************************/
PROCEDURE delete_reflight_setup(p_site_id  reflight_setup.site_id%TYPE, 
                                p_carrier  reflight_setup.carrier%TYPE);

/******************************************************************************
 *
 * PROCEDURE maintain_reflight_dest_tx
 *
 * Description:
 *   Maintains the set of reflight_dest_tx records for a given carrier and site
 *
 * Parameters:
 *   p_site_id      site_id records are for  
 *   p_carrier      carrier records are for 
 *   p_dest_list    current list of destinations and teletype addresses for the specified
 *                  site and carrier
 * Errors raised:
 *   none
 *
 ******************************************************************************/
PROCEDURE maintain_reflight_dest_tx(p_site_id    reflight_dest_tx.site_id%TYPE,
                                    p_carrier    reflight_dest_tx.carrier%TYPE,
                                    p_dest_list  reflight_dest_tx_tab);
                                    
/******************************************************************************
 *
 * PROCEDURE maintain_reflight_avoid_dest
 *
 * Description:
 *   Maintains the set of reflight_avoid_dest records for a given carrier and site
 *
 * Parameters:
 *   p_site_id      site_id records are for  
 *   p_carrier      carrier records are for 
 *   p_dest_list    current list of destinations for the specified site and carrier
 *
 * Errors raised:
 *   none
 *
 ******************************************************************************/
PROCEDURE maintain_reflight_avoid_dest(p_site_id    reflight_avoid_dest.site_id%TYPE,
                                       p_carrier    reflight_avoid_dest.carrier%TYPE,
                                       p_dest_list  destination_list);

/******************************************************************************
 *
 * PROCEDURE maintain_reflight_avoid_dest
 *
 * Description:
 *   Maintains the set of reflight_accept_from records for a given carrier and site
 *
 * Parameters:
 *   p_site_id       site_id records are for  
 *   p_carrier       carrier records are for 
 *   p_accept_list  current list of details for the specified site and carrier
 *
 * Errors raised:
 *   none
 *
 ******************************************************************************/                                       
PROCEDURE maintain_reflight_accept_from(p_site_id      reflight_accept_from.site_id%TYPE,
                                        p_carrier      reflight_accept_from.carrier%TYPE,
                                        p_accept_list  reflight_accept_from_tab);

END reflighting_config;
/
CREATE OR REPLACE PACKAGE BODY reflighting_config is

PROCEDURE add_reflight_setup(p_site_id          reflight_setup.site_id%TYPE, 
                             p_carrier          reflight_setup.carrier%TYPE, 
                             p_bags_per_flight  reflight_setup.bags_per_flight%TYPE,
                             p_mins_in_future   reflight_setup.mins_in_future%TYPE,
                             p_send_wt_msg      reflight_setup.send_wt_msg%TYPE,
                             p_tag_default      reflight_setup.tag_default%TYPE,
                             p_tag_other        reflight_setup.tag_other%TYPE) IS

BEGIN

  --nvls mimic the defaults on the table itself.
  INSERT INTO reflight_setup
  (site_id, carrier, bags_per_flight, mins_in_future, 
   send_wt_msg, tag_default, tag_other)
  VALUES
  (p_site_id, p_carrier, nvl(p_bags_per_flight, 0), nvl(p_mins_in_future, 0),
   nvl(p_send_wt_msg, 'N'), p_tag_default, p_tag_other);

END add_reflight_setup;

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

PROCEDURE update_reflight_setup(p_site_id          reflight_setup.site_id%TYPE, 
                                p_carrier          reflight_setup.carrier%TYPE, 
                                p_bags_per_flight  reflight_setup.bags_per_flight%TYPE,
                                p_mins_in_future   reflight_setup.mins_in_future%TYPE,
                                p_send_wt_msg      reflight_setup.send_wt_msg%TYPE,
                                p_tag_default      reflight_setup.tag_default%TYPE,
                                p_tag_other        reflight_setup.tag_other%TYPE) IS
BEGIN

  --nvls mimic the defaults on the table itself.
  UPDATE reflight_setup
  SET bags_per_flight = nvl(p_bags_per_flight, 0),
  mins_in_future = nvl(p_mins_in_future, 0),
  send_wt_msg = nvl(p_send_wt_msg, 'N'),
  tag_default = p_tag_default,
  tag_other = p_tag_other
  WHERE site_id = p_site_id
  AND carrier = p_carrier;

END update_reflight_setup;

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

PROCEDURE delete_reflight_setup(p_site_id  reflight_setup.site_id%TYPE, 
                                p_carrier  reflight_setup.carrier%TYPE) IS
BEGIN

  DELETE FROM reflight_setup
  WHERE carrier = p_carrier
  AND site_id = p_site_id;

END delete_reflight_setup;

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

PROCEDURE maintain_reflight_dest_tx(p_site_id    reflight_dest_tx.site_id%TYPE,
                                    p_carrier    reflight_dest_tx.carrier%TYPE,
                                    p_dest_list  reflight_dest_tx_tab) IS
                               
BEGIN

  --Delete anything thats in the table but not the passed in list.
  DELETE FROM reflight_dest_tx
  WHERE site_id = p_site_id
  AND carrier = p_carrier
  AND (dest, teletype_address) IN (SELECT dest, teletype_address 
                                   FROM reflight_dest_tx
                                   WHERE site_id = p_site_id
                                   AND carrier = p_carrier
                                   MINUS
                                   SELECT dest, teletype_address
                                   FROM TABLE(CAST(p_dest_list AS reflight_dest_tx_tab)));
                                   
  --insert anything thats in the passed in list but not the table.
  INSERT INTO reflight_dest_tx (site_id, carrier, dest, teletype_address)
  (SELECT p_site_id, p_carrier, dest, teletype_address
   FROM TABLE(CAST(p_dest_list AS reflight_dest_tx_tab))
   MINUS 
   SELECT p_site_id, p_carrier, dest, teletype_address
   FROM reflight_dest_tx
   WHERE site_id = p_site_id
   AND carrier = p_carrier);
   
END maintain_reflight_dest_tx;

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

PROCEDURE maintain_reflight_avoid_dest(p_site_id    reflight_avoid_dest.site_id%TYPE,
                                       p_carrier    reflight_avoid_dest.carrier%TYPE,
                                       p_dest_list  destination_list) IS
                                       
BEGIN

  --Delete anything thats in the table but not the passed in list.
  DELETE FROM reflight_avoid_dest
  WHERE site_id = p_site_id
  AND carrier = p_carrier
  AND avoid_dest IN (SELECT avoid_dest
                     FROM reflight_avoid_dest
                     WHERE site_id = p_site_id
                     AND carrier = p_carrier
                     MINUS
                     SELECT column_value
                     FROM TABLE(CAST(p_dest_list AS destination_list)));

  --insert anything thats in the passed in list but not the table.
  INSERT INTO reflight_avoid_dest (site_id, carrier, avoid_dest)
  (SELECT p_site_id, p_carrier, column_value
   FROM TABLE(CAST(p_dest_list AS destination_list))
   MINUS 
   SELECT p_site_id, p_carrier, avoid_dest
   FROM reflight_avoid_dest
   WHERE site_id = p_site_id
   AND carrier = p_carrier);

END maintain_reflight_avoid_dest;

------------------------------------------------------------------------------
------------------------------------------------------------------------------
PROCEDURE maintain_reflight_accept_from(p_site_id      reflight_accept_from.site_id%TYPE,
                                        p_carrier      reflight_accept_from.carrier%TYPE,
                                        p_accept_list reflight_accept_from_tab) IS
                      
BEGIN
  
  --nvls mimic the defaults on the table itself.
  
  --update records where add_rush_auth, bags_per_flight, mins_in_future have changed
  --but accept_from_carrier hasn't.
  FOR rec IN (SELECT new_rec.accept_from_carrier, 
                     new_rec.add_rush_auth, 
                     new_rec.bags_per_flight, 
                     new_rec.mins_in_future
              FROM TABLE(CAST(p_accept_list AS reflight_accept_from_tab)) new_rec,
                   reflight_accept_from old_rec
              WHERE old_rec.site_id = p_site_id
              AND old_rec.carrier = p_carrier
              AND new_rec.accept_from_carrier = old_rec.accept_from_carrier
              AND (nvl(new_rec.add_rush_auth, 'N') != nvl(old_rec.add_rush_auth, 'N')
                   OR nvl(new_rec.bags_per_flight, 0) != nvl(old_rec.bags_per_flight, 0)
                   OR nvl(new_rec.mins_in_future, 0) != nvl(old_rec.mins_in_future, 0))) LOOP
                   
    UPDATE reflight_accept_from
    SET add_rush_auth = nvl(rec.add_rush_auth, 'N'),
        bags_per_flight = nvl(rec.bags_per_flight, 0),
        mins_in_future = nvl(rec.mins_in_future, 0)
    WHERE site_id = p_site_id
    AND carrier = p_carrier
    AND accept_from_carrier = rec.accept_from_carrier;
    
  END LOOP;
        
  --Delete anything thats in the table but not the passed in list. 
  DELETE FROM reflight_accept_from
  WHERE site_id = p_site_id
  AND carrier = p_carrier
  AND accept_from_carrier IN (SELECT accept_from_carrier
                              FROM reflight_accept_from
                              WHERE site_id = p_site_id
                              AND carrier = p_carrier
                              MINUS
                              SELECT accept_from_carrier
                              FROM TABLE(CAST(p_accept_list AS reflight_accept_from_tab)));

  --insert anything thats in the passed in list but not the table.
  INSERT INTO reflight_accept_from 
  (site_id, carrier, accept_from_carrier, add_rush_auth, bags_per_flight, mins_in_future)
  (SELECT p_site_id, 
          p_carrier, 
          accept_from_carrier, 
          nvl(add_rush_auth, 'N'), 
          nvl(bags_per_flight, 0), 
          nvl(mins_in_future, 0)
   FROM TABLE(CAST(p_accept_list AS reflight_accept_from_tab))
   WHERE accept_from_carrier IN (SELECT accept_from_carrier
                                 FROM TABLE(CAST(p_accept_list AS reflight_accept_from_tab))
                                 MINUS 
                                 SELECT accept_from_carrier
                                 FROM reflight_accept_from
                                 WHERE site_id = p_site_id
                                 AND carrier = p_carrier));

END maintain_reflight_accept_from;

END reflighting_config;
/
