rem *********************************************************************

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Filename:             merge_bag.sql                               *

rem * Module Name:          merge_bag                                   *

rem * Subsystem Name:       stored_procs                                *

rem * Originating Author:   James Creegan                               *

rem * Design Reference:     FAS/BBTRK/SD/225                            *

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Description:                                                      *

rem *  Procedure to unload a bag from on ULD, and load it on to another *

rem *********************************************************************

rem *                                                                   *

rem * Program Identification Array:                                     *

rem *                                                                   *

rem * "%Z%%M% VERSION %I% %E% %U%"

rem *                                                                   *

rem *********************************************************************

rem *                                                                   *

rem * Modification History:                                             *

rem *                                                                   *

rem * Rel Date/Time      Proj/Id   CA Ref/Details Of Change             *

rem * --- -------------- --------  ------------------------------------ *

rem * 1.0 28 May 03                File initially checked in            *

rem *********************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE merge_bag

CREATE OR REPLACE PROCEDURE
merge_bag
(p_old_flightid IN FLIGHT.FLIGHT_ID%TYPE,
 p_new_flightid IN FLIGHT.FLIGHT_ID%TYPE,
 p_uld          IN BAG.ULD_NO%TYPE,
 p_bag_tag      IN BAG.BAG_TAG%TYPE,
 p_loc          IN bag_trace.location_code%TYPE,
 p_user_id      IN bag_trace.user_id%TYPE,
 p_old_pid      IN pax.pax_id%TYPE,
 p_ret_code     OUT NUMBER)

AS
  l_merged_bag    bag%ROWTYPE;
  l_bag_tag       bag.bag_tag%TYPE;        -- local copy of bag_tag to use as an OUT argument
  l_loc           bag_trace.location_code%TYPE;
  l_user_id       bag_trace.user_id%TYPE;
  l_error_message mws_web.error_msg_type;
  l_new_pax_id    pax.pax_id%TYPE;
  l_class         pax.pax_class%TYPE;
  l_loadall_pax   NUMBER:=0 ;                      -- using the LOADALL pax?
  l_airport_code  site_info.airport_code%TYPE;  
  l_ret_code     NUMBER;
  v_bag_tag       bag.bag_tag%TYPE;                                                                -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
 
BEGIN
  -- Ensure bag tag is in numeric format
  v_bag_tag := get_numeric_bag_tag(p_bag_tag, 0);                                                  -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)

  l_bag_tag      := v_bag_tag;                                                                     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
  p_ret_code     := 0;

  /* copy into local variables to use as OUT parameters */
  l_loc          := p_loc;
  l_user_id      := p_user_id;


  -- get the details of the bag on the old row
  SELECT * 
  INTO l_merged_bag
  FROM bag
  WHERE bag_tag      = v_bag_tag                                                                   -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
    AND pax_id       = p_old_pid;

  -- find out the pax_id for the pax row on the new flight by
  -- looking for someone with the same bag_tag, surname and initial.
  BEGIN
    SELECT pax_new.pax_id, pax_new.pax_class 
    INTO l_new_pax_id, l_class
    FROM pax pax_new, pax pax_old, bag bag_old, bag bag_new
    WHERE  
     --      pax_old.delete_marker  != 1 AND   
     --      passengers may have been deleted from the old flight, and now we want to transfer their bags
           pax_new.delete_marker  != 1
     AND   pax_old.pax_initials    = pax_new.pax_initials
     AND   pax_old.pax_surname     = pax_new.pax_surname
     AND   pax_old.flight_id       = p_old_flightid    
     AND   pax_new.flight_id       = p_new_flightid
     AND   pax_new.pax_id          = bag_new.pax_id
     AND   pax_old.pax_id          = bag_old.pax_id
     AND   pax_old.pax_id          = p_old_pid
     AND   bag_old.bag_tag         = v_bag_tag                                                     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
     AND   bag_new.bag_tag         = v_bag_tag;                                                    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
  EXCEPTION
    WHEN no_data_found THEN
      l_new_pax_id := 0;
      l_class      := '*';
    WHEN others THEN
      l_new_pax_id := 0;
      l_class      := '*'; 
      raise;
  END;
  
  -- If we couldn't find a new pax row, see if a loadall_pax already exists on the new flight
  IF l_new_pax_id = 0 THEN
    l_loadall_pax := 1;
    BEGIN
      SELECT pax_id, pax_class
      INTO l_new_pax_id, l_class
      FROM pax
      WHERE pax_sequence_no = -1 
        AND flight_id       = p_new_flightid
        AND delete_marker  != 1;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          l_new_pax_id := 0;
          l_class      := '*';
       WHEN OTHERS THEN
          l_new_pax_id := 0;
          l_class      := '*';      
          raise;
    END;   
  END IF;

  -- we may need the airport code for raising alerts
  SELECT site_info.airport_code
  INTO l_airport_code
  FROM site_info, flight
  WHERE site_info.site_id = flight.site_id
    AND flight.flight_id  = p_new_flightid;

  -- unload the bag from its old flight
  -- this procedure may try to ROLLBACK previously made changes
  COMMIT;
  mws_web.unload_a_bag
    (p_loc,
     p_user_id,
     p_old_flightid,
     v_bag_tag,                                                                                    -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
     0,
     l_error_message,
     p_ret_code);

  IF p_ret_code = -1003 THEN  -- duplicate tag, doesn't matter offload it anyway
    
    offload_bag ( l_merged_bag.bag_tag2a,
                  l_merged_bag.bag_tag2b,
                  l_merged_bag.bag_tag3,
                  l_merged_bag.pax_id,
                  l_merged_bag.uld_no,
                  l_loc,
                  '0',   -- dummy scanner number
                  l_merged_bag.uld_flight_id,
                  p_ret_code);

    add_bag_trace_record ( l_user_id,
                           p_loc,
                           p_ret_code,
                           l_merged_bag.bag_tag,
                           l_merged_bag.bag_tag1,
                           l_merged_bag.bag_tag2a,
                           l_merged_bag.bag_tag2b,
                           l_merged_bag.bag_tag3,
                           'OFFLOAD',
                           '0',   -- dummy scanner number
                           p_old_flightid,
                           l_merged_bag.pax_id,
                           l_merged_bag.uld_no);    -- pass ULD number (Nigel, 31/03/04, CA598)
  END IF;            

  -- add a new bag row to the new flight, either on the new pax row, an existing LOADALL pax
  -- or by creating a new LOADALL pax
  --
  -- N.B. although this is adding another BAG row, a BUM/BDM will be received that
  -- will delete the old bag from the previous flight

  IF l_loadall_pax = 0 THEN
    -- a BSM has been received for this passenger, load the bag
    -- this procedure may try to ROLLBACK previously made changes
    COMMIT;
    mws_web.load_a_bag(p_loc,
                       p_user_id,
                       p_new_flightid, 
                       p_uld,
                       v_bag_tag,                                                                  -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                       0,
                       l_error_message,
                       p_ret_code);

    -- if the new pax can not be found eg because the pax is  deleted or a
		-- duplicate tag; doesn't matter load it anyway
    IF p_ret_code = -1003  OR p_ret_code = -1403  OR p_ret_code = 100 THEN  

     /*  ignore the ret code as we have already failed to load cleanly */
       load_bag (l_merged_bag.bag_tag2a,
                 l_merged_bag.bag_tag2b,
                 l_merged_bag.bag_tag3,
                 l_new_pax_id,
                 p_uld,
                 p_loc,
                 '0', -- scanner id
                 p_new_flightid,
                 l_ret_code);
    END IF;

    
  ELSE
    -- no BSM has been received for this passenger, must be loaded under the LOADALL pax.
    -- so either use the existing LOADALL pax, or create a new one 
    add_loaded_bag( p_uld,
                    v_bag_tag,                                                                     -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                    l_merged_bag.bag_tag1,
                    l_merged_bag.bag_tag2a,                
                    l_merged_bag.bag_tag2b,
                    l_merged_bag.bag_tag3,
                    l_new_pax_id,
                    p_new_flightid,
                    l_merged_bag.bag_destination,
                    l_class,
                    p_loc,
                    '0',        -- scanner id 
                    p_ret_code);
       if p_ret_code > 0 then
                  p_ret_code := 0;
       end if;
  END IF;
  -- loading over, do we need to raise any alerts?




  IF l_loadall_pax = 0 THEN
    -- trace this action
    add_bag_trace_record( l_user_id, 
                          l_loc,
                          '0',
                          p_bag_tag,
                          l_merged_bag.bag_tag1,
                          l_merged_bag.bag_tag2a,                
                          l_merged_bag.bag_tag2b,
                          l_merged_bag.bag_tag3,
                          'REALLOCATE', 
                          '0',  -- scanner id
                          p_new_flightid,
                          l_new_pax_id,
                          l_merged_bag.uld_no);    -- pass ULD number (Nigel, 31/03/04, CA598)

    -- not a LOADALL pax, but did it load properly?

    IF p_ret_code = 0 THEN

       NULL;    -- success!

    ELSE  

      -- problem merging the bag, it must be unloaded
      INSERT INTO bag_alert (pax_id,
                             no_warnings,
                             bag_tag2b,
                             bag_tag3,
                             last_time_stamp,
                             alert_on_action,
                             bag_tag,
                             bag_tag2a, 
                             bag_tag1, 
                             flight_id,
                             airport_code)
      VALUES                ( l_new_pax_id,
                             '1',
                              l_merged_bag.bag_tag2b,
                              l_merged_bag.bag_tag3,                           
                              sysdate,
                              'UNLOAD',
                              v_bag_tag,                                                           -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                              l_merged_bag.bag_tag2a,    
                              l_merged_bag.bag_tag1,
                              p_new_flightid,
                              l_airport_code);
    END IF;

  ELSE  -- LOADALL pax, raise an 'UNKNOWN' alert
    
    INSERT INTO bag_alert (pax_id,
                           no_warnings,
                           bag_tag2b,
                           bag_tag3,
                           last_time_stamp,
                           alert_on_action,
                           bag_tag,
                           bag_tag2a, 
                           bag_tag1, 
                           flight_id,
                           airport_code)
            VALUES        ( l_new_pax_id,
                           '1',
                            l_merged_bag.bag_tag2b,
                            l_merged_bag.bag_tag3,                           
                            sysdate,
                            'UNKNOWN',
                            v_bag_tag,                                                             -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                            l_merged_bag.bag_tag2a,    
                            l_merged_bag.bag_tag1,
                            p_new_flightid,
                            l_airport_code);

    add_bag_trace_record( l_user_id, 
                          l_loc,
                          '-1010',
                          v_bag_tag,                                                               -- UAS/DUBRS/SD/01225 Section 5.1.3 (g)
                          l_merged_bag.bag_tag1,
                          l_merged_bag.bag_tag2a,                
                          l_merged_bag.bag_tag2b,
                          l_merged_bag.bag_tag3,
                          'UNKNOWN', 
                          p_user_id,
                          p_new_flightid,
                          l_new_pax_id,
                          l_merged_bag.uld_no);    -- pass ULD number (Nigel, 31/03/04, CA598)
  END IF;     
END merge_bag;
/

