
rem *********************************************************************
rem *                                                                   *
rem *             Copyright (c) Ultra Electronics Airport Systems       *
rem *                           All rights reserved.                    *
rem *                                                                   *
rem *********************************************************************
rem *                                                                   *
rem * Filename:             merge_uld.sql                               *
rem * Module Name:          merge_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 unload a ULD and its contents from one flight and   *
rem * load it onto another.                                             *
rem *********************************************************************
rem *                                                                   *
rem * Program Identification Array:                                     *
rem *                                                                   *
rem * "@(#)merge_uld.sql VERSION 1.2 03/11/05 16:23:13"
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_uld 

CREATE OR REPLACE PROCEDURE merge_uld 
(p_old_flightid IN     flight.flight_id%TYPE,
 p_new_flightid IN     flight.flight_id%TYPE,
 p_uld_no       IN     ulds.uld_no%TYPE,
 p_loc          IN     bag_trace.location_code%TYPE,
 p_user_id      IN     bag_trace.user_id%TYPE,
 p_hold         IN     ulds.uld_hold_no%TYPE,
 p_ok           OUT NUMBER,
 p_unknown      OUT NUMBER,
 p_unauth       OUT NUMBER,
 p_standby      OUT NUMBER,
 p_total        OUT NUMBER,
 p_return_code  OUT NUMBER,
 p_posn_result  OUT NUMBER)

AS
  l_abort         EXCEPTION;
  l_location      bag_trace.location_code%TYPE;
  l_user_id       bag_trace.user_id%TYPE;
  l_ret_code      NUMBER;  -- dummy return_code needed to call bag_load
  l_valid_uld_count NUMBER;
  l_new_pax_id    pax.pax_id%TYPE;
  l_class         pax.pax_class%TYPE;
  l_hold_no       ulds.uld_hold_no%TYPE;
  l_trace_time    char(5);
  l_uld_no        ulds.uld_no%TYPE := RPAD(p_uld_no, 10);

  CURSOR b1 IS
    SELECT * from 
    bag WHERE 
    nvl(uld_no,'???')  = l_uld_no
    AND uld_flight_id  = p_old_flightid
    AND loaded_status  = 'Y' ;
BEGIN
  
  p_return_code := 0;
  p_ok          := 0;
  p_unknown     := 0;
  p_unauth      := 0;
  p_standby     := 0;
  p_total       := 0;
  p_posn_result := 0;

  l_location    := p_loc ;
  l_user_id     := p_user_id;  -- copy to the local variables to pass in as OUT parameters
  l_hold_no     := p_hold;

  validate_action.check_access (p_user_id,
                                p_new_flightid, -- Check the flight we are reallocating to
                                17,             -- ID set up for this procedure
                                NULL,
                                p_return_code);
  IF p_return_code != 0 THEN
    RAISE l_abort;
  ELSE
    validate_action.check_access (p_user_id,
                                  p_old_flightid, -- Check the flight we are reallocating from
                                  17,             -- ID set up for this procedure
                                  NULL,
                                  p_return_code);
  END IF;

  IF p_return_code != 0 THEN   -- we have access to perform this action
    RAISE l_abort;
  ELSE

    /****************************************/
    /* Do we have one valid ULD to merge?   */
    /****************************************/
 
    SELECT count(*) 
    INTO l_valid_uld_count
    FROM ulds 
    WHERE uld_no    = l_uld_no  
      AND flight_id = p_old_flightid
      AND pd_marker = 'N';

    IF l_valid_uld_count = 1 THEN
 
      /*************************************************/
      /* We need to set the PD Marker to 'Y'           */
      /* temporarily, but in actual fact the ULD must  */
      /* not have already departed                     */
      /*************************************************/
  
      UPDATE ulds 
      SET pd_marker = 'Y' 
      WHERE uld_no = l_uld_no 
        AND flight_id = p_old_flightid;

      copy_uld(p_old_flightid,
               p_new_flightid, 
               l_uld_no, 
               l_hold_no,
               l_user_id,
               l_location,
               'MERGE',
               p_return_code,
               p_posn_result);

      IF p_return_code = 0 THEN  -- if ULD is copied succesfully
        FOR bag_on_uld IN b1 LOOP
          /* Unload each bag in turn, then reload it on the new flight */ 

          merge_bag(p_old_flightid,
                    p_new_flightid,
                    l_uld_no,
                    bag_on_uld.bag_tag,
                    l_location,
                    l_user_id,
                    bag_on_uld.pax_id,
                    p_return_code);

          IF p_return_code < 0 THEN
            ROLLBACK;
            RAISE l_abort;
          END IF;  
        END LOOP;  
    
        -- If a hold position is set then we want to create bag_trace entries for 
        -- the bags in the ULD on the new flight. This isn't done in the copy_uld 
        -- stored_proc when the ULD itself is positioned because at that time the 
        -- ULD is empty. Therefore, now bags have been transferred between the ULD's
        -- on the two flights, we want to create logs to show that the bags are in 
        -- a positioned ULD.
        IF ltrim(l_hold_no) IS NOT NULL THEN
           add_bag_trace_for_bags_in_uld(l_user_id, l_location, '0', p_new_flightid, 'POSITION', l_uld_no);
        END IF;
            
        /* now deassign the ULD from this flight N.B we cannot use deassign_uld as */
        /* there are multiple ULDs with this number */
        add_uld_trace_record(l_uld_no, p_old_flightid, l_user_id, '0', l_location, l_trace_time, 'DEASSIGN', 'Y');
  
        -- remove the old ULD
        DELETE FROM ulds
        WHERE uld_no = l_uld_no 
          AND flight_id = p_old_flightid;

      ELSE     -- copy_uld unsuccessful
        RAISE l_abort;
      END IF;  -- copy_uld is successful
    ELSIF l_valid_uld_count > 0 THEN
      p_return_code := -5 ;  -- too many valid ULDS
      RAISE l_abort;         -- skip to the end
    ELSE  
      p_return_code := -6 ;  -- no valid ULDS
      RAISE l_abort;         -- skip to the end
    END IF;
 
    /*******************************************/
    /* Count how many of various types of bags */
    /* have been copied                        */
    /*******************************************/

      SELECT COUNT(*)
      INTO p_ok
      FROM bag
      WHERE
         NVL(uld_no, -999)       = l_uld_no AND
         NVL(uld_flight_id,-999) = p_new_flightid AND
         authority_to_load_dcs   = 'Y' AND
         loaded_status           = 'Y' AND
         delete_marker           = 0 AND
         (NVL(uld_flight_id, -999), pax_id, bag_tag)
         NOT IN    (SELECT  flight_id, pax_id, bag_tag 
                    FROM   bag_alert b_a
                    WHERE  b_a.bag_tag=bag_tag
                      AND  b_a.pax_id=pax_id AND
                           b_a.flight_id=NVL(uld_flight_id,-999));

      /* Type: Unknown */
      SELECT COUNT(*)
      INTO p_unknown
      FROM bag
      WHERE
        NVL(uld_no, -999)        = l_uld_no AND
        NVL(uld_flight_id, -999) = p_new_flightid AND
        delete_marker            = 0 AND
        (NVL(uld_flight_id, -999), pax_id,bag_tag)
        IN (SELECT flight_id, pax_id, bag_tag FROM bag_alert B_A WHERE alert_on_action='UNKNOWN' AND
                                                  b_a.bag_tag=bag_tag AND
                                                  b_a.pax_id=pax_id AND
                                                  b_a.flight_id=NVL(uld_flight_id,-999));
      /* Type: Unauthorised */
      SELECT COUNT(*)
      INTO p_unauth
      FROM bag
      WHERE
        NVL(uld_no, -999)        = l_uld_no AND
        NVL(uld_flight_id, -999) = p_new_flightid AND
        authority_to_load_dcs    = 'N' AND 
        loaded_status            = 'Y' AND
        delete_marker            = 0;

      /* Type: Standby */
      SELECT COUNT(bag.bag_tag)
      INTO p_standby
      FROM bag, pax
      WHERE
        NVL(bag.uld_no, -999)         = l_uld_no AND
        NVL(bag.uld_flight_id, -999)  = p_new_flightid AND
        bag.authority_to_load_dcs     = 'N' AND 
        pax.pax_status                = 'S' AND
        pax.delete_marker             = 0 AND
        bag.delete_marker             = 0 AND
        bag.pax_id                    = pax.pax_id AND
        pax.flight_id                 = p_new_flightid ;
  
      /* Type: Total */
      SELECT COUNT(*)
      INTO p_total
      FROM bag
      WHERE
        NVL(uld_no, -999)        = l_uld_no AND
        NVL(uld_flight_id, -999) = p_new_flightid AND
        delete_marker            = 0;
  END IF; -- access check

EXCEPTION
  WHEN l_abort THEN
    UPDATE ulds 
    SET pd_marker = 'N' 
    WHERE uld_no = l_uld_no
      AND flight_id = p_old_flightid;
  WHEN others THEN
    UPDATE ulds 
    SET pd_marker = 'N' 
    WHERE uld_no = l_uld_no
      AND flight_id = p_old_flightid;
    RAISE;
END;
/

