/*****************************************************************************
 *
 * Filename         - $Workfile:   hbmd_create.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_create

CREATE OR REPLACE PROCEDURE hbmd_create
   (p_flight_id       IN  hbmd.flight_id%TYPE,
    p_flight_no       IN  hbmd.flight_no%TYPE,
    p_flight_date     IN  hbmd.flight_date%TYPE,
    p_flight_sch_time IN  hbmd.flight_sch_time%TYPE) IS

   l_bags_loaded  hbmd.checked_bags_brs%TYPE := 0; -- Number of checked bags for the flight
   l_bags_crew    hbmd.crew_bags_brs%TYPE    := 0; -- Number of crew bags for the flight
   l_bags_rush    hbmd.rush_bags_brs%TYPE    := 0; -- Number of rush bags for the flight
   l_bags_gate    hbmd.gate_bags_brs%TYPE    := 0; -- Number of gate bags for the flight
   l_bags_mail    hbmd.mail_bags_brs%TYPE    := 0; -- Number of mail bags for the flight
   l_bags_dipl    hbmd.dipl_bags_brs%TYPE    := 0; -- Number of dipl bags for the flight
   l_bags_standby hbmd.other_bags_brs%TYPE   := 0; -- Number of other bags for the flight
   l_version      hbmd.version%TYPE;               -- Number of versions of the HBMD for the flight
   l_status       hbmd.status%TYPE;                -- Current status of the latest HBMD version

   -- Set up a cursor to read the last HBMD entry for this flight
   -- (must use a cursor to guarentee the last entry)
   CURSOR c_hbmd_data IS
      SELECT version, status FROM hbmd WHERE flight_id = p_flight_id
         ORDER BY version DESC;

BEGIN

   -- Get the bag count details for this flight
   hbmd_pbc.get_hbmd_bag_count(p_flight_id, l_bags_loaded, l_bags_crew, l_bags_rush,
                               l_bags_gate, l_bags_mail, l_bags_dipl, l_bags_standby);

   -- Check whether a HBMD already exists for this flight
   -- (and get the maximum version number at the same time)
   OPEN c_hbmd_data;
   FETCH c_hbmd_data INTO l_version, l_status;
   CLOSE c_hbmd_data;

   -- Default version and status (in case there is no data in the HBMD table for this flight)
   l_version := NVL(l_version, '0');
   l_status  := NVL(l_status, 'UNAUTHORISED');

   -- Check if a new HBMD is required
   IF l_status != 'UNAUTHORISED' OR l_version = 0 THEN
      -- Create a new HBMD version if the STATUS is invalid/unauthorised
      -- or there is no HBMD defined (version = '0')

      -- Check if there are currently any HBMD's for this flight
      IF l_version > 0 THEN
         -- Ensure any existing HBMD's are invalid for this flight
         UPDATE hbmd SET status = 'INVALID'
            WHERE flight_id = p_flight_id AND status != 'INVALID';
      END IF;

      -- Insert the HBMD details into the database
      INSERT INTO hbmd
         (flight_id,
          version,
          flight_no,
          flight_date,
          flight_sch_time,
          status,
          checked_bags_brs,
          crew_bags_brs,
          rush_bags_brs,
          gate_bags_brs,
          mail_bags_brs,
          dipl_bags_brs,
          other_bags_brs,
          checked_bags_brs_edited,
          crew_bags_brs_edited,
          rush_bags_brs_edited,
          gate_bags_brs_edited,
          mail_bags_brs_edited,
          dipl_bags_brs_edited,
          other_bags_brs_edited,
          checked_bags_manual,
          crew_bags_manual,
          rush_bags_manual,
          gate_bags_manual,
          mail_bags_manual,
          dipl_bags_manual,
          other_bags_manual
      )
      VALUES 
         (p_flight_id,
          l_version + 1,
          p_flight_no,
          p_flight_date,
          p_flight_sch_time,
          'UNAUTHORISED',    -- Set the status initially to unauthorised
          l_bags_loaded,
          l_bags_crew,
          l_bags_rush,
          l_bags_gate,
          l_bags_mail,
          l_bags_dipl,
          l_bags_standby,
          l_bags_loaded,
          l_bags_crew,
          l_bags_rush,
          l_bags_gate,
          l_bags_mail,
          l_bags_dipl,
          l_bags_standby,
          0,
          0,
          0,
          0,
          0,
          0,
          0
      );
   ELSE
      -- A new manifest is not required - just refresh the current one
      UPDATE hbmd
         SET checked_bags_brs        = l_bags_loaded
           , crew_bags_brs           = l_bags_crew
           , rush_bags_brs           = l_bags_rush
           , gate_bags_brs           = l_bags_gate
           , mail_bags_brs           = l_bags_mail
           , dipl_bags_brs           = l_bags_dipl
           , other_bags_brs          = l_bags_standby
           , checked_bags_brs_edited = l_bags_loaded
           , crew_bags_brs_edited    = l_bags_crew
           , rush_bags_brs_edited    = l_bags_rush
           , gate_bags_brs_edited    = l_bags_gate
           , mail_bags_brs_edited    = l_bags_mail
           , dipl_bags_brs_edited    = l_bags_dipl
           , other_bags_brs_edited   = l_bags_standby
      WHERE  flight_id = p_flight_id
      AND    version = l_version;
                  

   END IF; -- l_status != 'UNAUTHORISED' OR l_version = 0

END hbmd_create;
/

