CREATE OR REPLACE PROCEDURE
merge_one_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_ret_code     OUT NUMBER)

AS
  l_bag_tag       bag.bag_tag%TYPE;            -- local copy of bag_tag to use as an OUT argument
  l_new_flightid  flight.flight_id%TYPE;       -- local copy of flight_id to use as an OUT argument
  l_error_message mws_web.error_msg_type;
  uld_closed      exception;
  close_flag      number(10);

  tag1            bag.bag_tag1%TYPE;
  tag2a           bag.bag_tag2a%TYPE;
  tag2b           bag.bag_tag2b%TYPE;
  tag3            bag.bag_tag3%TYPE;
  tag             bag.bag_tag%TYPE;
  t               NUMBER(10);

 
BEGIN


  validate_action.check_access (p_user_id,
                                p_new_flightid, -- we only care about the new flight status
                                18,             -- ID set up for this procedure
                                NULL,
                                p_ret_code);

  IF p_ret_code   = 0 THEN
    validate_action.check_access (p_user_id,
                                  p_new_flightid, -- we only care about the new flight status
                                  18,             -- ID set up for this procedure
                                  NULL,
                                  p_ret_code);
  END IF;
  
  split_tags(p_bag_tag, tag1, tag2a, tag2b, tag3, tag, t);
  
  -- Find the security_clearance_status and if the bag is Not cleared then  
  -- return an error_code (-1068) to the calling unit to indicate that the  
  -- bag has Not been cleared  
                                               
  IF p_ret_code   = 0 THEN
    BEGIN
      SELECT DECODE(security_clearance_status
                   ,'N',  -1068
                   ,0)
      INTO p_ret_code
      FROM bag_v2
      WHERE flight_id = p_new_flightid
      AND (bag_tag2b = tag2b OR bag_tag2a = tag2a)
      AND bag_tag3 = tag3
      AND delete_marker = 0;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        p_ret_code := 100;   -- unknown tag
      WHEN TOO_MANY_ROWS THEN
        p_ret_code := -1003; -- duplicate tag
    END;
  END IF;
    
  -- Can the bag be reallocated?
  IF p_ret_code  = 0 THEN

    -- Before offloading the bag check to see if it can be reallocated:
    -- Reject any 'N' ATL statuses
    -- Reject any unresolvable duplicates
    -- Reject any unknown or deleted bags
    -- Otherwise reallocate the bag

    BEGIN 
      SELECT DECODE(authority_to_load_dcs
                   ,'N',  -1004        -- Not ATL DCS
                   ,DECODE(authority_to_load_brs
                          ,'N',  -1005 -- Not ATL BRS
                          ,0))
      INTO p_ret_code
      FROM bag_v2
      WHERE flight_id = p_new_flightid
      AND (bag_tag2b = tag2b OR bag_tag2a = tag2a)
      AND bag_tag3 = tag3
      AND delete_marker = 0;
    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        p_ret_code := 100;   -- unknown tag
      WHEN TOO_MANY_ROWS THEN
        p_ret_code := -1003; -- duplicate tag
    END;
  END IF; -- Can the bag be reallocated?

  IF p_ret_code   = 0 THEN
    l_bag_tag      := p_bag_tag;
    l_new_flightid := p_new_flightid;
    p_ret_code     := 0;
  
  
    close_flag := wan_msg.get_uld_close_flag(p_uld, p_new_flightid);
 
    IF (close_flag = -1099) THEN
       raise uld_closed;
    END IF;     
    -- unload from the existing ULD and flight
    
    mws_web.unload_a_bag
      (p_loc,
       p_user_id,
       p_old_flightid,
       l_bag_tag,
       0,
       l_error_message,
       p_ret_code);
  
    IF p_ret_code = 0 THEN -- some unexpected error
      mws_web.load_a_bag ( p_loc,
                           p_user_id, 
                           p_new_flightid,
                           p_uld,
                           l_bag_tag,
                           0,
                           l_error_message,
                           p_ret_code);
    END IF;     
  END IF; -- access allowed and bag can be reallocated

EXCEPTION
   WHEN uld_closed THEN
      p_ret_code := -1099;
      rollback;   
END merge_one_bag;
/
