CREATE OR REPLACE PACKAGE BODY missing_bag AS

/******************************************************************************
 *
 * PROCEDURE detect_missing_bags
 *
 * Description:
 *   Detects missing bags given a missing bag rule.
 *
 * In parameters:
 *   p_for_site            - the site at which the rule will apply.
 *   p_loc_grp1            - the first timing location group of the rule.
 *   p_loc_grp2            - the second timing location group of the rule.
 *   p_period              - the period of time a bag must exceed between the 
 *                           two points to become missing.
 *
 ******************************************************************************/
PROCEDURE detect_missing_bags
(
   p_for_site         IN missing_bag_rule.site_id%TYPE
  ,p_loc_group1       IN missing_bag_rule.timing_group_1%TYPE
  ,p_loc_group2       IN missing_bag_rule.timing_group_2%TYPE
  ,p_period           IN missing_bag_rule.time_period%TYPE
) IS

l_airport_code    site_info.airport_code%TYPE;
l_auto_user       tagtrak_users.user_id%TYPE; -- The automatic user for the site.
l_auto_location   location.location_code%TYPE; -- The automatic location for the site.

BEGIN
   -- Get the airport code given the site_id.
   SELECT airport_code
   INTO l_airport_code
   FROM site_info
   WHERE site_id = p_for_site;

   -- For each missing bag...
   FOR dest_rec IN (SELECT DISTINCT b.bag_id, b.pax_id, p.flight_id, b.bag_tag,
                   b.bag_tag1, b.bag_tag2a, b.bag_tag2b, b.bag_tag3,
                   b.uld_no
                    FROM flight f, pax p, bag b, bag_trace t
                    WHERE p.flight_id = f.flight_id
                    AND b.pax_id = p.pax_id
                    AND (t.flight_id_scan = 0 OR t.flight_id_scan = f.flight_id)
                    AND b.delete_marker = 0
                    AND b.loaded_status <> 'Y'
                    AND SUBSTR(b.bag_tag,2) = SUBSTR(t.bag_tag,2)
                    AND (p_loc_group1 IS NULL OR t.location_code IN (
                                                                     SELECT location_code
                                                                     FROM location
                                                                     WHERE group_name = p_loc_group1)
                    )
                    AND NOT EXISTS (
                                    SELECT 1
                                    FROM bag_trace t2, location l
                                    WHERE SUBSTR(b.bag_tag,2) = SUBSTR(t2.bag_tag,2)
                                    AND t2.location_code = l.location_code
                                    AND l.ignore_for_missing_bag = 'Y')
                                    AND t.bag_trace_time < (sysdate - p_period)
                    AND (p_loc_group2 IS NULL OR NOT EXISTS (
                                                       SELECT 1
                                                       FROM bag_trace t3
                                                       WHERE SUBSTR(b.bag_tag,2) = SUBSTR(t3.bag_tag,2)
                                                       AND t3.location_code IN (
                                                                                SELECT location_code
                                                                                FROM location
                                                                                WHERE group_name = p_loc_group2
                                                       )
                                                       AND (t3.bag_trace_time - t.bag_trace_time) < p_period
                                                       ))
                    AND NOT EXISTS (
                                      SELECT 1
                                      FROM missing_bag_alert ba
                                      WHERE ba.bag_id = b.bag_id
                                   )) LOOP

      -- Create a new missing bag alert.
      INSERT INTO missing_bag_alert(bag_id, site_id)
      VALUES(dest_rec.bag_id, p_for_site);

      -- Create a new bag alert.
      INSERT INTO bag_alert(pax_id, flight_id, bag_tag, bag_tag1, bag_tag2a,
                            bag_tag2b, bag_tag3, no_warnings, last_time_stamp,
                            alert_on_action)
      VALUES(dest_rec.pax_id, dest_rec.flight_id, dest_rec.bag_tag,
             dest_rec.bag_tag1, dest_rec.bag_tag2a, dest_rec.bag_tag2b,
             dest_rec.bag_tag3, 1,
             SYSDATE + get_offset.airport_code(l_airport_code), 'MISSING');

      -- Get auto user and auto location for site.
      l_auto_user := multi_site.get_auto_user(l_airport_code);
      l_auto_location := multi_site.get_auto_location(l_airport_code);

      -- Add a bag trace record.
      add_bag_trace_record(l_auto_user, l_auto_location, 0,
                           dest_rec.bag_tag, dest_rec.bag_tag1,
                           dest_rec.bag_tag2a, dest_rec.bag_tag2b,
                           dest_rec.bag_tag3, 'MISSING', 0, dest_rec.flight_id,
                           dest_rec.pax_id, dest_rec.uld_no);
   END LOOP;
END detect_missing_bags;

/******************************************************************************
 *
 * PROCEDURE detect_found_bags
 *
 * Description:
 *   Detects found bags in the system. A missing bag is said to be found if it
 *   it either loaded or deleted.
 *
 ******************************************************************************/
PROCEDURE detect_found_bags IS

l_bagtag          bag.bag_tag%TYPE;
l_tag1            bag.bag_tag1%TYPE;
l_tag2a           bag.bag_tag2a%TYPE;
l_tag2b           bag.bag_tag2b%TYPE;
l_tag3            bag.bag_tag3%TYPE;
l_pax_id          bag.pax_id%TYPE;
l_flight_id       pax.flight_id%TYPE;
l_uld_no          bag.uld_no%TYPE;
l_site_id         site_info.site_id%TYPE;
l_airport_code    site_info.airport_code%TYPE;
l_auto_user       tagtrak_users.user_id%TYPE; -- The automatic user for the site.
l_auto_location   location.location_code%TYPE; -- The automatic location for the site.

BEGIN
   
   -- A bag is considered found if it is marked missing and then either
   -- subsequently loaded or deleted.
   FOR dest_rec IN (SELECT mba.bag_id
                    FROM bag b, missing_bag_alert mba
                    WHERE b.bag_id = mba.bag_id
                    AND (b.delete_marker = 1 OR b.loaded_status = 'Y')
                    AND mba.cleared = 'N') LOOP

      -- Clear the missing bags.
      UPDATE missing_bag_alert
      SET cleared = 'Y'
      WHERE bag_id = dest_rec.bag_id;

      -- Get all necessary data for creating a bag trace record.
      SELECT b.bag_tag, b.bag_tag1, b.bag_tag2a, b.bag_tag2b, b.bag_tag3,
             b.pax_id, p.flight_id, b.uld_no, f.site_id
      INTO l_bagtag, l_tag1, l_tag2a, l_tag2b, l_tag3, l_pax_id,
           l_flight_id, l_uld_no, l_site_id
      FROM bag b, pax p, flight f
      WHERE b.bag_id = dest_rec.bag_id
      AND b.pax_id = p.pax_id
      AND p.flight_id = f.flight_id;

      -- Get auto user and auto location for site.
      l_auto_user := multi_site.get_auto_user(l_airport_code);
      l_auto_location := multi_site.get_auto_location(multi_site.get_airport_code(l_site_id));

      -- Create a bag trace record to show bag has been found.
      add_bag_trace_record(l_auto_user, l_auto_location, 0,
                           l_bagtag, l_tag1, l_tag2a, l_tag2b, l_tag3,
                           'FOUND', 0, l_flight_id, l_pax_id, l_uld_no);

      -- Remove any bag alerts that are classified as 'Missing'.
      DELETE FROM bag_alert
      WHERE pax_id = l_pax_id
      AND bag_tag = l_bagtag
      AND trim(alert_on_action) = 'MISSING';

   END LOOP;
END detect_found_bags;


PROCEDURE insert_rule
(      p_site_id          IN  missing_bag_rule.site_id%TYPE
     , p_timing_group_1   IN  missing_bag_rule.timing_group_1%TYPE
     , p_timing_group_2   IN  missing_bag_rule.timing_group_2%TYPE
     , p_period           IN  missing_bag_rule.time_period%TYPE
     , p_out_row_id       OUT missing_bag_rule.appn_row_id%TYPE
) IS

BEGIN
   IF p_timing_group_1 = p_timing_group_2 AND p_timing_group_1 IS NOT NULL AND p_timing_group_2 IS NOT NULL THEN
      RAISE_APPLICATION_ERROR(-20792, 'Timing points cannot be identical');
   END IF;
   
   INSERT INTO missing_bag_rule(site_id, timing_group_1, timing_group_2, time_period)
   VALUES (p_site_id, p_timing_group_1, p_timing_group_2, p_period)
   RETURNING appn_row_id INTO p_out_row_id;
END insert_rule;


PROCEDURE edit_rule
(      p_row_id           IN  missing_bag_rule.appn_row_id%TYPE
     , p_timing_group_1   IN  missing_bag_rule.timing_group_1%TYPE
     , p_timing_group_2   IN  missing_bag_rule.timing_group_2%TYPE
     , p_period           IN  missing_bag_rule.time_period%TYPE
) IS

BEGIN
   IF p_timing_group_1 = p_timing_group_2 AND p_timing_group_1 IS NOT NULL AND p_timing_group_2 IS NOT NULL THEN
      RAISE_APPLICATION_ERROR(-20792, 'Timing points cannot be identical');
   END IF;
   
   UPDATE missing_bag_rule
   SET timing_group_1 = p_timing_group_1
      ,timing_group_2 = p_timing_group_2
      ,time_period = p_period
   WHERE appn_row_id = p_row_id;
END edit_rule;


PROCEDURE delete_rule
(      p_row_id           IN  missing_bag_rule.appn_row_id%TYPE
) IS

BEGIN
   DELETE FROM missing_bag_rule
   WHERE appn_row_id = p_row_id;
END delete_rule;


PROCEDURE acknowledge_missing_bag
(      p_row_id           IN  missing_bag_alert.appn_row_id%TYPE
      ,p_user_id          IN  missing_bag_alert.acknowledged_by%TYPE
      ,p_location         IN  missing_bag_alert.acknowledged_where%TYPE
) IS

l_bagtag bag.bag_tag%TYPE;
l_tag1 bag.bag_tag1%TYPE;
l_tag2a bag.bag_tag2a%TYPE;
l_tag2b bag.bag_tag2b%TYPE;
l_tag3 bag.bag_tag3%TYPE;
l_pax_id bag.pax_id%TYPE;
l_flight_id pax.flight_id%TYPE;
l_uld_no bag.uld_no%TYPE;

BEGIN
   -- Update the missing bag alert to show that it has been acknowledged.
   UPDATE missing_bag_alert
   SET acknowledged = 'Y'
      ,acknowledged_by = p_user_id
      ,acknowledged_where = p_location
      ,acknowledged_when = sysdate
   WHERE appn_row_id = p_row_id;

   -- Get all necessary data for creating a bag trace record.
   SELECT b.bag_tag, b.bag_tag1, b.bag_tag2a, b.bag_tag2b, b.bag_tag3,
          b.pax_id, p.flight_id, b.uld_no
   INTO l_bagtag, l_tag1, l_tag2a, l_tag2b, l_tag3, l_pax_id,
        l_flight_id, l_uld_no
   FROM bag b, pax p
   WHERE b.bag_id = (
                       SELECT bag_id
                       FROM missing_bag_alert
                       WHERE appn_row_id = p_row_id
                    )
   AND b.pax_id = p.pax_id;

   -- Create a bag trace record.
   add_bag_trace_record(p_user_id, p_location, 0, l_bagtag, l_tag1,
                        l_tag2a, l_tag2b, l_tag3, 'MISSINGCLR', 0,
                        l_flight_id, l_pax_id, l_uld_no);

   -- Remove any bag alerts that are classified as 'Missing'.
   DELETE FROM bag_alert
   WHERE pax_id = l_pax_id
   AND bag_tag = l_bagtag
   AND trim(alert_on_action) = 'MISSING';

END acknowledge_missing_bag;


PROCEDURE process_missing_bags IS

BEGIN

   -- DETECT MISSING BAGS.
   -- For each missing bag rule...
   FOR dest_rec IN (SELECT site_id, timing_group_1, timing_group_2, time_period
                    FROM missing_bag_rule) LOOP

      -- Detect missing bags for rule.
      detect_missing_bags(dest_rec.site_id, dest_rec.timing_group_1,
                          dest_rec.timing_group_2, (dest_rec.time_period/(24 * 60)));

   END LOOP;

   -- DETECT FOUND BAGS.
   detect_found_bags();

END process_missing_bags;

END missing_bag;
/