
rem *********************************************************************
rem *                                                                   *
rem *             Copyright (c) Ultra Electronics Airport Systems       *
rem *                           All rights reserved.                    *
rem *                                                                   *
rem *********************************************************************
rem *                                                                   *
rem * Filename:             copy_uld.sql                                *
rem * Module Name:          copy_uld                                    *
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 copy the details of a ULD and its baggage from one  *
rem * flight to another.                                                *
rem *********************************************************************
rem *                                                                   *
rem * Program Identification Array:                                     *
rem *                                                                   *
rem * "@(#)copy_uld.sql VERSION 1.2 03/11/05 16:23:12"
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 copy_uld

CREATE OR REPLACE PROCEDURE copy_uld (
     p_in_flightid IN NUMBER,
     p_out_flightid IN NUMBER,
     p_uld_no IN ulds.uld_no%TYPE,
     p_hold_no IN VARCHAR2,
     p_user_id IN VARCHAR2,
     p_loc IN VARCHAR2,
     p_reallocate_flag IN VARCHAR2,
     p_return_code OUT NUMBER,
     p_posn_result OUT NUMBER)
IS
  l_hold_flag NUMBER;
  l_dup_vals  NUMBER;
  l_inbound_uld     ULDS%ROWTYPE;
  l_home_airport    SITE_INFO.AIRPORT_CODE%TYPE;
  l_connection_type ULDS.CONNECTION_TYPE%TYPE;
  l_out_flight_no   FLIGHT.FLIGHT_NO%TYPE;
  l_out_flight_date FLIGHT.FLIGHT_DATE%TYPE;
  l_in_flightid     FLIGHT.FLIGHT_ID%TYPE;
  l_out_flightid    FLIGHT.FLIGHT_ID%TYPE;
  l_uld_no          ULDS.ULD_NO%TYPE;
  l_hold_no         ULDS.ULD_HOLD_NO%TYPE;
  l_user_id         BAG_TRACE.USER_ID%TYPE;
  l_loc             BAG_TRACE.LOCATION_CODE%TYPE;
  l_uld_dest_class	ULD_DESTS.ULD_CLASS_LIST%TYPE;
  l_uld_dest_cnt    NUMBER(2) := 0;

  CURSOR ud1 IS
    SELECT * FROM uld_dests
    WHERE uld_no           = RPAD(p_uld_no,10)
      AND flight_id        = p_in_flightid
      AND uld_destination != l_home_airport;
BEGIN

  p_return_code := 0;
  /****************************************************************/
  /* Copy the parameters into local variables so we can use them  */
  /* as IN/OUT parameters to call pre-existing procedures         */
  /****************************************************************/


  l_in_flightid  := p_in_flightid;
  l_out_flightid := p_out_flightid;
  l_uld_no       := RPAD(p_uld_no,10);
  l_hold_no      := p_hold_no;
  l_user_id      := p_user_id;
  l_loc          := p_loc;
  l_uld_dest_class := '*';

  /**************************************************************************/
  /* We can't transfer a hold, so only do the rest if this is a genuine ULD */
  /**************************************************************************/

  SELECT count(*)
  INTO l_hold_flag
  FROM holds
  WHERE hold_no = l_uld_no ;

  IF l_hold_flag = 0 then
  
    -- Fetch the destination airport code for the old flight ID.
    SELECT site_info.airport_code
    INTO l_home_airport
    FROM site_info, flight
    WHERE site_info.site_id = flight.site_id AND
          flight.flight_id  = p_out_flightid;

    -- Check the uld given exists on the old flight ID.
    BEGIN
       SELECT *
       INTO l_inbound_uld
       FROM ulds
       WHERE uld_no = l_uld_no AND
             flight_id = p_in_flightid;

    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          p_return_code := -1100;
          RETURN;
    END;

    SELECT count(*)
    INTO l_dup_vals
    FROM
      (SELECT count(*), bag_tag
       FROM bag a, pax p
       WHERE
         NVL(a.uld_no, '?')     = l_uld_no
         AND a.uld_flight_id    = p_in_flightid
         AND a.pax_id           = p.pax_id
         AND NOT EXISTS
         (SELECT *
          FROM bag a2, pax p2
          WHERE p2.flight_id     = p_out_flightid
            AND p.pax_surname    = p2.pax_surname
            AND p.pax_full_inits = p2.pax_full_inits
            AND a2.pax_id        = p2.pax_id
            AND a.bag_tag        = a2.bag_tag)
       GROUP BY bag_tag
       HAVING count(*) > 1);

    IF l_dup_vals > 0 THEN
      p_return_code := -1003;  -- we can't load duplicate bag tags to the LOADALL pax
    END IF;

    IF p_return_code = 0 THEN
      IF l_inbound_uld.next_flight_no IS NULL THEN

        /***********************************************/
        /* the only thing we cannot determine is       */
        /* the connection type at the next airport- so */
        /* leave it blank                              */
        /***********************************************/
        l_connection_type := NULL;
        add_uld_record (l_uld_no,
                        l_out_flightid,
                        l_inbound_uld.uld_category,
                        l_inbound_uld.uld_type,
                        l_user_id,
                        l_loc,
                        l_connection_type,
                        l_inbound_uld.uld_id_code );

        /* Set the position if it's known */
        IF ltrim(p_hold_no) IS NOT NULL THEN
          rec_uld_posn(l_uld_no, l_out_flightid, l_hold_no, l_user_id, l_loc, p_posn_result, '0');
        ELSE
          p_posn_result := 0;
        END IF;

        l_uld_dest_cnt := 0;
        FOR inbound_uld_dest IN ud1 LOOP
          l_uld_dest_cnt := l_uld_dest_cnt + 1;
          add_uld_dests_record(l_uld_no,
                               inbound_uld_dest.uld_destination,
                               inbound_uld_dest.uld_class_list,
                               l_out_flightid,
                               inbound_uld_dest.onward_flight_no,
                               inbound_uld_dest.onward_flight_date);
        END LOOP;

        /*
         * Ensure there is a entry for the final destination for
         * transfer ULDS that have no onward destination.
         *
         * Note: The class associated with the ULD cannot be
         *       obtained, so all class of bags are acceptable
         *       by default.
         */
        IF l_uld_dest_cnt = 0
        THEN
          add_uld_dests_record(l_uld_no,
                               l_home_airport,
                               l_uld_dest_class,
                               l_out_flightid,
                               NULL,
                               NULL);
        END IF;

        flight_no (p_out_flightid, l_out_flight_no, l_out_flight_date);

        /*******************************************************/
        /* Update the NEXT... details of the inbound uld,      */
        /* but only if transferring, rather than reallocating  */
        /*******************************************************/
        IF NVL(p_reallocate_flag, '??') != 'MERGE' THEN
          UPDATE ulds
            SET next_flight_no=l_out_flight_no,
                next_flight_date=l_out_flight_date
          WHERE
            uld_no    = l_uld_no AND
            flight_id = p_in_flightid AND
            pd_marker = 'Y';
        END IF;
      ELSE
        p_return_code := -2; -- Must untransfer this ULD first
      END IF;
    END IF;  -- return code already set, either inbound ULD does not exist, or there are
             -- duplicate tags not matched to passengers
  ELSE
    p_return_code := -1; -- this is a hold, not a ULD
  END IF; -- is this a hold?
EXCEPTION
   WHEN OTHERS THEN
      p_return_code := -3; -- some other error
      raise;
END copy_uld;
/

