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

rem *                                                                   *

rem *             Copyright (c) Ultra Electronics Airport Systems       *

rem *                           All rights reserved.                    *

rem *                                                                   *

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

rem *                                                                   *

rem * Filename:             untransfer_uld.sql                          *

rem * Module Name:          untransfer_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 undo the transferral of a ULD from an inbound to    *

rem * an outbound flight                                                *

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 untransfer_uld

CREATE OR REPLACE PROCEDURE untransfer_uld
  (p_in_flightid  IN flight.flight_id%TYPE,
   p_out_flightid IN flight.flight_id%TYPE,
   p_uldno        IN ulds.uld_no%TYPE,
   p_userid       IN bag_trace.user_id%TYPE,
   p_loc          IN bag_trace.location_code%TYPE,
   p_retval       OUT NUMBER )

AS
  l_retval NUMBER;     -- return value for calling other procedures
  l_is_hold NUMBER;    -- check if this 'ULD' is really a hold
  l_out_flightid flight.flight_id%TYPE;   -- local copy of the outbound flight id
  l_uldno        ulds.uld_no%TYPE;        -- local copy of the uld number
  l_userid		 bag_trace.user_id%TYPE;  -- local copy of user identity
  l_pax_num_bags   NUMBER;
  l_uld_no_of_bags ulds.uld_no_of_bags%TYPE;
  l_trtime         uld_trace.flight_date%TYPE;

  /*Locate the bags using the INBOUND_BAG.RESULT_BAG_ID column for the
    Inbound Flight ID and ULD Number.  This prevents removal of any bags
    independently added to the ULD on outbound flight.*/
  CURSOR c_bags IS
    SELECT bag.*, ibag.source_bag_id, ibag.result_bag_status, ibag.inbound_bag_id, ibag.inbound_pax_id
    FROM bag, inbound_bag ibag, inbound_uld iuld
    WHERE bag.uld_no          = p_uldno AND
          bag.uld_flight_id = p_out_flightid AND
          bag.bag_id = ibag.result_bag_id AND
          ibag.result_bag_status IN ( 'AMENDED','CREATED') AND
          ibag.inbound_uld_id = iuld.inbound_uld_id AND
          iuld.result_uld_no  = p_uldno AND
          iuld.inbound_flight_id = p_in_flightid AND
          iuld.result_uld_status IN ( 'AMENDED','CREATED');
BEGIN
  /*******************************************************/
  /* in order to use these variables as IN/OUT arguments */
  /* we need to copy them to local variables             */
  /*******************************************************/

    l_out_flightid := p_out_flightid;
    l_uldno        := p_uldno;
	l_userid	   := p_userid;

    /* check if it is a hold */
    SELECT
      COUNT(hold_no)
    INTO
      l_is_hold
    FROM
      holds
    WHERE
      hold_no = p_uldno;

    IF l_is_hold = 0 THEN

	  FOR outbound_bag IN c_bags LOOP
		UPDATE bag
        SET next_flight_no   = NULL,
            next_flight_date = NULL
        WHERE bag_tag        = outbound_bag.bag_tag AND
            bag_id = outbound_bag.source_bag_id;

        IF LTRIM(outbound_bag.dcs_bag_tag) IS NOT NULL
           OR UPPER(outbound_bag.result_bag_status) = 'AMENDED'  THEN
           /* If the bag has a BSM, unload it */
			offload_bag(outbound_bag.bag_tag2a, outbound_bag.bag_tag2b,
            outbound_bag.bag_tag3, outbound_bag.pax_id, outbound_bag.uld_no,
            outbound_bag.location_code, 'AUTO',
            outbound_bag.uld_flight_id, p_retval);

            UPDATE inbound_bag
               SET result_bag_status = 'UNPROCESSED',
                   result_bag_id     = NULL
             WHERE inbound_bag_id = outbound_bag.inbound_bag_id;

        ELSE

		  /* otherwise, delete it along with any bag alerts */
            DELETE FROM bag_alert
            WHERE
                bag_tag = outbound_bag.bag_tag AND
                pax_id  = outbound_bag.pax_id ;

            /*Reverse unknown_bag and unauthorised_bag entries which were
              created when bag was copied across during uld transfer*/

            DELETE FROM unknown_bag
            WHERE
                bag_tag = outbound_bag.bag_tag AND
                pax_id  = outbound_bag.pax_id ;

            DELETE FROM unauthorised_bag
            WHERE
                bag_tag = outbound_bag.bag_tag AND
                pax_id  = outbound_bag.pax_id ;


			DELETE FROM bag
            WHERE bag_tag = outbound_bag.bag_tag AND
                  pax_id  = outbound_bag.pax_id;

            /* Create trace record for Delete Inbound Transfer Bag */

            add_bag_trace_record(p_user_id        => l_userid,
                                 p_location_code  => outbound_bag.location_code,
                                 p_error_id       => 0,
                                 p_bagtag         => outbound_bag.bag_tag,
                                 p_tag1           => outbound_bag.bag_tag1,
                                 p_tag2a          => outbound_bag.bag_tag2a,
                                 p_tag2b          => outbound_bag.bag_tag2b,
                                 p_tag3           => outbound_bag.bag_tag3,
                                 p_trace_action   => 'DIN_TR_BAG',
                                 p_scanner_id     => NVL(outbound_bag.scanner_id, 0),
                                 p_flight_id_scan => p_out_flightid,
                                 p_paxid          => outbound_bag.pax_id,
                                 p_uld_no         => outbound_bag.uld_no
                                );

			/*********************************************************************/
            /* decrease the bag count in this ULD (assuming it is +ve) note that */
            /* the retval returned from this procedure has no chance or actually */
            /* being returned, it will be overwritten by deassign_uld            */
            /*********************************************************************/


            /* update the pax record bag count */
            UPDATE pax
            SET no_of_bags = no_of_bags - 1
            WHERE pax_id =  outbound_bag.pax_id
            RETURNING no_of_bags INTO l_pax_num_bags;

            remove_bag_from_uld ( l_out_flightid, l_uldno, p_retval);

            UPDATE inbound_bag
               SET result_bag_status = 'UNPROCESSED',
                   result_bag_id     = NULL
             WHERE inbound_bag_id = outbound_bag.inbound_bag_id;


            /*If the pax bag count is now zero and
              INBOUND_PAX.RESULT_PAX_STATUS = CREATED then delete the pax.
              If the final bag is removed from the LoadAll pax, delete the
              pax regardless of the INBOUND_PAX record status.
              Change the INBOUND_PAX.RESULT_PAX_STATUS to UNPROCESSED*/

			IF l_pax_num_bags = 0 THEN

			   DELETE pax
                WHERE pax.pax_id = outbound_bag.pax_id;

               /* Create trace record for Delete Inbound Transfer Pax */

			   add_log_rec( 'DELETE_INBOUND_TRANSFER_PAX',
                            'delete: PAX ID: '||outbound_bag.pax_id||' from FLIGHT: '||
                            p_out_flightid,
                            NULL);

            END IF;
        END IF;

        UPDATE inbound_pax
           SET result_pax_status = 'UNPROCESSED',
               result_pax_id     = NULL
         WHERE inbound_pax_id     = outbound_bag.inbound_pax_id;

      END LOOP;

      /* Deassign the ULD if empty*/
	  BEGIN
      SELECT uld_no_of_bags
        INTO l_uld_no_of_bags
        FROM ulds
       WHERE uld_no = p_uldno
         AND flight_id = p_out_flightid;
      EXCEPTION
      WHEN NO_DATA_FOUND THEN
         p_retval := 0;
      END;
      /*return in the block above ensures that if control comes to this point
       then l_uld_no_of_bags is always correctly populated*/

	  IF l_uld_no_of_bags = 0 THEN
         deassign_uld(p_uldno, p_userid, p_loc, p_retval);

         /* Ignore any deassign error as may already have been done */
         p_retval := 0;

         /* Create trace record for Delete Inbound Transfer ULD */

		 add_uld_trace_record (l_uldno,p_out_flightid, l_userid,'0',p_loc,l_trtime,'DIN_TR_ULD','Y');
      END IF;

      /*Need to explicitly set result_* columns to null because unlike inbound_bag
        or inbound_pax no foreign keys with 'on delete set null' clause exist on
        result_* fields in inbound_uld table
      */
      UPDATE inbound_uld
         SET result_uld_status    = 'UNPROCESSED'
            ,result_uld_no        = NULL
            ,result_uld_flight_id = NULL
       WHERE result_uld_no        = p_uldno
         AND inbound_flight_id    = p_in_flightid
         AND result_uld_status IN ('CREATED','AMENDED');

      /**************************************************/
      /* Possible retval:                               */
      /*       -1001 = no uld records found             */
      /*       -1101 = bags loaded in the ULD           */
      /*       -1100 = a hold, not a ULD                */
      /**************************************************/

      UPDATE ulds
      SET next_flight_no   = NULL,
          next_flight_date = NULL
      WHERE
        ULD_NO    = p_uldno AND
        FLIGHT_ID = (SELECT flight_id
                       FROM flight
                      WHERE next_leg_id = p_in_flightid);
    ELSE
      p_retval := -1100;        -- This 'ULD' is actually a hold
    END IF;  -- is this a hold?
END untransfer_uld;
/



