/*****************************************************************************
 *
 * Filename         - $Workfile:   hbmd_authorise.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_authorise procedure.
 *
 * Language         - Oracle PL/SQL.
 *
 * Classification   - Commercial In Confidence
 *                    (c) Ultra Electronics Limited, 2003
 *
 *****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE hbmd_authorise

CREATE OR REPLACE PROCEDURE hbmd_authorise
   (p_flight_id               IN  hbmd.flight_id%TYPE,
    p_user_id                 IN  hbmd.user_id%TYPE,
    p_checked_bags_brs_edited IN  hbmd.checked_bags_brs_edited%TYPE,
    p_crew_bags_brs_edited    IN  hbmd.crew_bags_brs_edited%TYPE,
    p_rush_bags_brs_edited    IN  hbmd.rush_bags_brs_edited%TYPE,
    p_gate_bags_brs_edited    IN  hbmd.gate_bags_brs_edited%TYPE,
    p_mail_bags_brs_edited    IN  hbmd.mail_bags_brs_edited%TYPE,
    p_dipl_bags_brs_edited    IN  hbmd.dipl_bags_brs_edited%TYPE,
    p_other_bags_brs_edited   IN  hbmd.other_bags_brs_edited%TYPE, 
    p_checked_bags_manual     IN  hbmd.checked_bags_manual%TYPE,
    p_crew_bags_manual        IN  hbmd.crew_bags_manual%TYPE,
    p_rush_bags_manual        IN  hbmd.rush_bags_manual%TYPE,
    p_gate_bags_manual        IN  hbmd.gate_bags_manual%TYPE,
    p_mail_bags_manual        IN  hbmd.mail_bags_manual%TYPE,
    p_dipl_bags_manual        IN  hbmd.dipl_bags_manual%TYPE,
    p_other_bags_manual       IN  hbmd.other_bags_manual%TYPE, 
    p_hbmd_type               IN  hbmd.auth_type%TYPE,
    p_authorised              OUT INTEGER) IS

   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
   l_firstname tagtrak_users.firstname%TYPE; -- First name of the authorising user
   l_surname   tagtrak_users.surname%TYPE;   -- Surname of the authorising user

   -- 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 user's name
   SELECT firstname, surname INTO l_firstname, l_surname
      FROM tagtrak_users WHERE user_id = p_user_id;

   -- 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;
   
   -- Perform the manifests authorisation
   UPDATE hbmd
      SET user_id                 = p_user_id,
          auth_date               = SYSDATE + get_offset.airport_code( get_offset.get_user_location(p_user_id) ),
          firstname               = l_firstname,
          surname                 = l_surname,
          auth_type               = DECODE(p_hbmd_type, 'M', 'MANUAL', 'E', 'ELECTRONIC', 'ERROR'),
          status                  = 'AUTHORISED',
          checked_bags_brs_edited = p_checked_bags_brs_edited,
          crew_bags_brs_edited    = p_crew_bags_brs_edited,
          rush_bags_brs_edited    = p_rush_bags_brs_edited,
          gate_bags_brs_edited    = p_gate_bags_brs_edited,
          mail_bags_brs_edited    = p_mail_bags_brs_edited,
          dipl_bags_brs_edited    = p_dipl_bags_brs_edited,
          other_bags_brs_edited   = p_other_bags_brs_edited,
          checked_bags_manual     = p_checked_bags_manual,
          crew_bags_manual        = p_crew_bags_manual,
          rush_bags_manual        = p_rush_bags_manual,
          gate_bags_manual        = p_gate_bags_manual,
          mail_bags_manual        = p_mail_bags_manual,
          dipl_bags_manual        = p_dipl_bags_manual,
          other_bags_manual       = p_other_bags_manual
    WHERE flight_id               = p_flight_id
    AND   version                 = l_version;

   -- Check whether the authorisation worked or not
   IF SQL%ROWCOUNT = 0 THEN
      -- The authorisation update did not work, set the error flag
      p_authorised := -1;
   ELSE
      -- The authorisation update was okay
      p_authorised := 0;

      -- Set the flight status to Finalised
      UPDATE flight SET flight_status = 'FF'
         WHERE flight_id = p_flight_id;
   END IF;

   -- Save the transactions
   COMMIT;

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      -- Could not find user details, set the error flag
      p_authorised := -1;

END hbmd_authorise;
/
