/*********************************************************************
 *                                                                   *
 *             Copyright (c) Ultra Electronics Airport Systems       *
 *                           All rights reserved.                    *
 *                                                                   *
 *********************************************************************
 *                                                                   *
 * Filename:             auto_copy_uld.sql                           *
 * Module Name:          auto_copy_uld                               *
 * Subsystem Name:       stored_procs                                *
 * Design Reference:     UAS/FALCP/SS/1940                           *
 *                                                                   *
 *********************************************************************
 *                                                                   *
 * Description:                                                      *
 *  Procedure to copy a ULD and its baggage from an inbound to an    *
 *  outbound flight and return the various totals of the types of    *
 *  bags which have been transferred                                 *
 *********************************************************************
 *                                                                   *
 * Program Identification Array:                                     *
 *                                                                   *
 * "%Z%%M% VERSION %I% %E% %U%"                                      *                                                                   *
 *********************************************************************/

/**************************************************************/
/* Copy the ULD p_uld_no from p_in_flightid to p_out_flightid */
/**************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE auto_copy_uld

CREATE OR REPLACE PROCEDURE auto_copy_uld (
   p_in_flightid  IN flight.flight_id%type,
   p_out_flightid IN flight.flight_id%type,
   p_uld_no       IN ulds.uld_no%type,
   p_user_id      IN bag_trace.user_id%type,
   p_loc          IN bag_trace.location_code%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_posn_result  OUT NUMBER)
IS
  l_in_flightid NUMBER;
  l_out_flightid NUMBER;
  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_return_code NUMBER;
BEGIN

  l_in_flightid  := p_in_flightid;
  l_out_flightid := p_out_flightid;
  l_uld_no       := RPAD(p_uld_no,10);
  l_user_id      := p_user_id;
  l_loc          := p_loc;
  l_hold_no      := p_hold;
  p_posn_result  := 0;

  p_ok           := 0;
  p_unknown      := 0;
  p_unauth       := 0;
  p_standby      := 0;
  p_total        := 0;


  validate_action.check_access (p_user_id, 
                                p_out_flightid, -- we only care about the outbound status
                                16,    -- ID set up for this procedure
                                NULL,
                                l_return_code);
  
  IF l_return_code != 0  THEN  -- we don't have access to this
    p_total := l_return_code;  -- return the error code to the calling procedure
  ELSE 
    BEGIN

      /* Checking for whether this ULD is a hold will be done in the copy_uld procedure */
      inbound_flight.copy_uld (l_in_flightid, 
                               l_out_flightid, 
                               l_uld_no, 
                               l_hold_no,
                               l_user_id, 
                               l_loc, 
                               NULL, 
                               'TRANSFER',
                               l_return_code,
                               p_posn_result);
    EXCEPTION
      WHEN inbound_flight.uld_already_exists THEN
        l_return_code := pkg_constants.ERROR_ULD_ALREADY_EXISTS;
    END;
  
    IF l_return_code < 0 THEN 
      /* There has been a problem with copying the ULD, no point in continuing. */
      /* Set the total to the error code from copy_uld so that                  */
      /* the calling application can see there is a problem                     */
      p_total := l_return_code;
    ELSE
      /***************************************************************/
      /* Copy the pax details and then copy the bags afterwards      */
      /* We probably would need to trap errors if the copy_pax fails */
      /***************************************************************/
      BEGIN
      inbound_flight.copy_pax(p_in_flightid  => l_in_flightid
                             ,p_out_flightid => l_out_flightid
                             ,p_uld_no       => l_uld_no
                             ,p_process_type => 'TRANSFER');
                             
      EXCEPTION
         WHEN OTHERS THEN
              RAISE;
      END;
      
      /****************************************************************/
      /* Only carry on copying the bags if no errors have been raised */
      /****************************************************************/
      inbound_flight.copy_bags (l_in_flightid, l_out_flightid, l_uld_no, l_user_id, 'TRANSFER');
  
      /* Populate the counts of several types of bags which have been transferred */
      
      /* Type: OK */
      SELECT COUNT(*)
      INTO p_ok
      FROM bag
      WHERE
      NVL(uld_no, -999)        = l_uld_no AND
      NVL(uld_flight_id,-999)  = p_out_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_out_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, pax
      WHERE
        NVL(uld_no, -999)= l_uld_no AND
        NVL(uld_flight_id, -999)=p_out_flightid AND
        bag.authority_to_load_dcs='N' AND loaded_status='Y' AND
        pax.pax_status <> 'S' AND
        pax.pax_id = bag.pax_id AND
        bag.delete_marker=0;

      p_unauth := Greatest(p_unauth - p_unknown, 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_out_flightid AND
        bag.authority_to_load_dcs    = 'N' AND 
        pax.pax_status               ='S' AND
        bag.delete_marker            = 0 AND
        pax.delete_marker            = 0 AND
        bag.pax_id                   = pax.pax_id AND
        pax.flight_id                = p_out_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_out_flightid AND
        delete_marker            = 0;
 
    END IF;  -- if there was a problem with copy_uld
  END IF; -- access denied 
  COMMIT;

EXCEPTION
  WHEN inbound_flight.uld_already_exists THEN
    p_total := -1313;
END AUTO_COPY_ULD;
/

