/*****************************************************************************
*
* Filename         - $Workfile:   get_hbmd_details.sql  $
*
*                     Name              Date      Version     CA Number
* Author           -  Chris Reason      03/06/03
* Last Update      -  See revision history below.
*
* Project Name     - FALCP (UltraTrak).
*
* Source Code
* Identifier       - N/A.
*
* Design Spec
* Section          - FAS/BBTRK/SD/238.
*
* Contents         - Live Schema Stored Procedure
*                    This procedure derives the HBMD details from the database.
*
* Language         - Oracle PL/SQL.
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2003
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE get_hbmd_details

CREATE OR REPLACE PROCEDURE get_hbmd_details
   (p_flight_id           IN  flight.flight_id%TYPE,
    p_flight_date         OUT flight.flight_date%TYPE,
    p_user_id             IN  hbmd.user_id%TYPE,
    p_dest                OUT flight_dests.flight_destination%TYPE,
    p_version             OUT hbmd.version%TYPE,
    p_checked_bags_brs    OUT hbmd.checked_bags_brs%TYPE,
    p_crew_bags_brs       OUT hbmd.crew_bags_brs%TYPE,
    p_rush_bags_brs       OUT hbmd.rush_bags_brs%TYPE,
    p_gate_bags_brs       OUT hbmd.gate_bags_brs%TYPE,
    p_mail_bags_brs       OUT hbmd.mail_bags_brs%TYPE,
    p_dipl_bags_brs       OUT hbmd.dipl_bags_brs%TYPE,
    p_other_bags_brs      OUT hbmd.other_bags_brs%TYPE,
    p_checked_bags_manual OUT hbmd.checked_bags_manual%TYPE,
    p_crew_bags_manual    OUT hbmd.crew_bags_manual%TYPE,
    p_rush_bags_manual    OUT hbmd.rush_bags_manual%TYPE,
    p_gate_bags_manual    OUT hbmd.gate_bags_manual%TYPE,
    p_mail_bags_manual    OUT hbmd.mail_bags_manual%TYPE,
    p_dipl_bags_manual    OUT hbmd.dipl_bags_manual%TYPE,
    p_other_bags_manual   OUT hbmd.other_bags_manual%TYPE,
    p_retcode             OUT INTEGER) IS

   -- Set up a cursor to read the last destination for the flight
   -- (must use a cursor to guarentee the last entry)
   CURSOR c_flight_dests_data IS
      SELECT flight_destination FROM flight_dests
         WHERE flight_id = p_flight_id
            ORDER BY dest_order DESC;

BEGIN

   -- Initialise the return variables
   p_retcode             := 0;
   p_dest                := NULL;
   p_checked_bags_brs    := 0;
   p_crew_bags_brs       := 0;
   p_rush_bags_brs       := 0;
   p_gate_bags_brs       := 0;
   p_mail_bags_brs       := 0;
   p_dipl_bags_brs       := 0;
   p_other_bags_brs      := 0;
   p_checked_bags_manual := 0;
   p_crew_bags_manual    := 0;
   p_rush_bags_manual    := 0;
   p_gate_bags_manual    := 0;
   p_mail_bags_manual    := 0;
   p_dipl_bags_manual    := 0;
   p_other_bags_manual   := 0;
   p_version := 0;

   -- Check whether the user has access to the flight
   user_authentication.validate_access(p_user_id, p_flight_id, '-', '-', p_retcode); 

   IF  p_retcode != 0 THEN
     -- The user does not have access to the flight, so exit
      return;
   END IF;

   -- Get the bag count details for this flight
   hbmd_pbc.get_hbmd_bag_count(p_flight_id, p_checked_bags_brs, p_crew_bags_brs, p_rush_bags_brs,
                               p_gate_bags_brs, p_mail_bags_brs, p_dipl_bags_brs, p_other_bags_brs);

   -- Get the flight details
   BEGIN
      SELECT
             flight_date
      INTO
             p_flight_date
      FROM
             flight
      WHERE
             flight_id = p_flight_id;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         -- This flight/manifest does not exist, return an error and exit
         p_retcode := -2;
         return;
   END;

   -- Get the HBMD's current version
   SELECT
          NVL(MAX(version), '0')
   INTO
          p_version
   FROM
          hbmd
   WHERE
          flight_id = p_flight_id;

   -- Get the flights final destination
   OPEN c_flight_dests_data;
   FETCH c_flight_dests_data INTO p_dest;
   CLOSE c_flight_dests_data;

   -- The HBMD exists, so check if the flight is eligible for
   -- authorisation - i.e. no alerts and flight has closed
   hbmd_check(p_flight_id, p_retcode);

END get_hbmd_details;
/

