/*****************************************************************************
 *
 * Filename         - $Workfile:   hbmd_check.sql    $
 *
 *                    Name              Date      Version     CR Number
 * Author           - Chris Reason      27/05/03  
 * Last Update      - See revision history below.
 *
 * Project Name     - FALCP.
 *
 * Source Code
 * Identifier       - N/A.
 *
 * Design Spec
 * Section          - FAS/BBTRK/SD/238.
 *
 * Contents         - Live schema package: Creates the hbmd_check procedure.
 *
 * Language         - Oracle PL/SQL.
 *
 * Classification   - Commercial In Confidence
 *                    (c) Ultra Electronics Limited, 2003
 *
 *****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE hbmd_check

CREATE OR REPLACE PROCEDURE hbmd_check
   (p_flight_id IN  flight.flight_id%TYPE,
    p_auth_code OUT INTEGER) IS

   l_flight_status flight.flight_status%TYPE; -- Flight's status
   l_alerts        INTEGER;                   -- Number of bag alerts for the flight

BEGIN

   -- Get the flight's current status
   SELECT flight_status INTO l_flight_status
      FROM flight WHERE flight_id = p_flight_id;

   -- Get the number of bag alerts there are for this flight
   SELECT count(*) INTO l_alerts
      FROM bag_alert WHERE flight_id = p_flight_id;

   -- Check whether the flight's manifest can be authorised
   IF l_alerts > 0 OR l_flight_status != 'FC' THEN
      -- There are alerts for this flight and/or the flight is
      -- not at the closed status, authorisation is not allowed 
      p_auth_code := 1;
   ELSE
      -- Flight authorisation is allowed 
      p_auth_code := 0;
   END IF;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- Flight was not found, authorisation is not allowed
      p_auth_code := 1;

END hbmd_check;
/

