/*****************************************************************************
*
* Filename         - $Workfile:   check_all_alerts.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       - FALCP/SD/1289
*
* Design Spec
* Section          - FALCP/SD/1289 Section 3.2.4.1
*
* Contents         - Live Schema Package
*
* Language         - Oracle PL/SQL (Procedure) script
*
* Description      - Checks the flight for any alerts which have been
*                    generated. Alert types include Offload Bag Alerts,
*                    Authorised Bag Alerts.
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2005
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PROCEDURE check_all_alerts

CREATE OR REPLACE PROCEDURE check_all_alerts
  ( p_flight_id    IN  flight.flight_id%TYPE
   ,p_user_privs   IN  NUMBER
   ,p_alert_type   OUT NUMBER)
AS
  /* Declare and initialise */
  l_tot_offload_bag_alerts NUMBER := 0;
  l_tot_auth_bag_alerts    NUMBER := 0;
  l_auth_bag_alert_type    NUMBER := 0;
  l_offload_alert_type     NUMBER := 0;

  /* Constants of bag alert types */
  AUTH_BAG_ALERT_TYPE     CONSTANT NUMBER := 2; -- Authorised Bag Alert Type
  OFFLOAD_BAG_ALERT_TYPE  CONSTANT NUMBER := 1; -- Offload Alert Type

  BEGIN

  /***************************************************/
  /* This should find the highest alert_type and the */
  /* total number of mixed alerts for the flight     */
  /* being queried.                                  */
  /* The highest alert could be as follows           */
  /*                                                 */
  /* 000 (0)- NO alerts                              */
  /* 001 (1)- ONLY Offload bag alerts                */
  /* 010 (2)- ONLY Authorised bag alerts             */
  /* 011 (3)- BOTH Offload and Authorised bag alerts */
  /***************************************************/
  IF (BITAND(p_user_privs,OFFLOAD_BAG_ALERT_TYPE) <> 0 ) THEN
      dbms_output.put_line('offload bag for 1 and 3');

   SELECT COUNT(*)
   INTO l_tot_offload_bag_alerts
   FROM  bag_alert
   WHERE flight_id = p_flight_id;
   dbms_output.put_line('l_tot_offload_bag_alerts :' || l_tot_offload_bag_alerts);

   /* Set the offload_alert_type */
   IF l_tot_offload_bag_alerts > 0 THEN
      l_offload_alert_type := OFFLOAD_BAG_ALERT_TYPE;
   END IF;
  END IF;

  /******************************************/
  /* Set the l_tot_auth_bag_alerts if found */
  /******************************************/
  IF (BITAND(p_user_privs,AUTH_BAG_ALERT_TYPE) <> 0 ) THEN
      dbms_output.put_line('authorised bag for 2 and 3');
    SELECT COUNT(*)
    INTO l_tot_auth_bag_alerts
    FROM
       (SELECT flight_id,MAX(pax_id),bag_tag2b,bag_tag3
          FROM unauthorised_bag
         WHERE ok_to_fly = 1
           AND flight_id = p_flight_id
      GROUP BY flight_id,bag_tag2b,bag_tag3
    UNION
--     bags previously unknown that are not ATL DCS 'N'
       SELECT flight_id,MAX(pax_id),bag_tag2b,bag_tag3
         FROM unknown_bag
        WHERE
              flight_id||pax_id||bag_tag2b||bag_tag3 NOT IN
                       (SELECT flight_id||p.pax_id||bag_tag2b||bag_tag3
                         FROM bag b, pax p
                        WHERE b.authority_to_load_dcs = 'N'
                          AND p.flight_id = p_flight_id
                          AND b.pax_id = p.pax_id)
    AND ok_to_fly = 1
    AND flight_id = p_flight_id
    GROUP BY flight_id,bag_tag2b,bag_tag3
    );

   dbms_output.put_line('l_tot_auth_bag_alerts :' || l_tot_auth_bag_alerts);

   /* Set the authorised_alert_type */
   IF l_tot_auth_bag_alerts > 0 THEN
      l_auth_bag_alert_type := AUTH_BAG_ALERT_TYPE;
   END IF;
  END IF;

  /* Add the total alert_types */
  p_alert_type := l_auth_bag_alert_type + l_offload_alert_type;

  /* Total the alert types */
  dbms_output.put_line ('p_alert_type   :'||p_alert_type);

  EXCEPTION
    WHEN OTHERS THEN
     dbms_output.put_line ('Exception OTHERS');
     p_alert_type   := l_auth_bag_alert_type + l_offload_alert_type;
  END check_all_alerts;
/
