/***********************************************************************
 *                                                                     *
 *             Copyright (c) Ultra Electronics Limited 2004            *
 *                         All rights reserved                         *
 *                                                                     *
 ***********************************************************************
 *
 * Filename:           check_uld_weight.sql
 * Module Name:        UltraTrak Database
 * Originating Author: Gary Roberts
 * Design Reference:   UAS/FALCP/SS/3142
 *
 ***********************************************************************
 *
 * Description:
 *
 *    This function takes the details of a bag which is to be loaded,
 *    and details of the ULD that the bag is to be loaded onto, and
 *    checks that loading the bag will not cause the ULD's maximum
 *    weight to be exceeded. The function returns zero if the bag can
 *    be loaded.
 *
 * Return Codes:
 *
 *    0        Bag can be loaded.
 *
 *    -1012    Unresolvable duplicate occurred.
 *
 *    -20791   Bag will exceed ULD weight limit. Do not load.
 *
 ***********************************************************************
 * Program Identification Array
 *
 * "%Z%%M% VERSION %I% %E% %U%"
 ***********************************************************************
 * Modification History
 *
 * Rel Date/Time      Proj/Id   CA Ref/Details of Change
 * --- -------------- --------  --------------------------------------
 ***********************************************************************/

CREATE OR REPLACE FUNCTION check_uld_weight
  (p_flight_id  IN flight.flight_id%TYPE
  ,p_uldno      IN ulds.uld_no%TYPE
  ,p_bag_tag2a  IN bag.bag_tag2a%TYPE
  ,p_bag_tag2b  IN bag.bag_tag2b%TYPE
  ,p_bag_tag3   IN bag.bag_tag3%TYPE
  ) RETURN error.error_id%TYPE IS
  
  l_kg_weight                  bag.kg_weight%TYPE DEFAULT 0;
  l_uld_current_weight         uld_loading_progress_v.uld_weight%TYPE;
  l_max_weight                 uld_weights.max_weight%TYPE;
  
  BEGIN

    /* find the maximum weight that is allowed on the ULD */
    BEGIN
       SELECT max_weight
       INTO l_max_weight
       FROM uld_weights u, flight f
       WHERE f.flight_id=p_flight_id 
       AND u.uld_prefix = substr(p_uldno,1,3)
       AND u.site_id = f.site_id
       AND u.carrier = f.carrier_alpha_code;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
       RETURN 0;
    END;
    
    /* find the current weight of the ULD from the view uld_loading_progress_v*/
    SELECT DISTINCT uld_weight
    INTO l_uld_current_weight
    FROM uld_loading_progress_v
    WHERE uld_no = p_uldno
    AND flight_id = p_flight_id;
    
    /* find the bag's weight from the BAG table */
    SELECT kg_weight
    INTO l_kg_weight
    FROM bag b, pax p
    WHERE p.flight_id = p_flight_id
    AND p.pax_id = b.pax_id
    AND b.bag_tag3 = p_bag_tag3
    AND (b.bag_tag2a = p_bag_tag2a
    OR b.bag_tag2b = p_bag_tag2b);
    
    /* if the bag's weight obtained from the BAG table is zero, determine */
    IF l_kg_weight = 0 THEN
       BEGIN
          /* see if a nominal weight is defined for the flight number */
          SELECT w.weight
          INTO l_kg_weight
          FROM weights w, flight f
          WHERE f.flight_id = p_flight_id
          AND w.flight_no=f.flight_no
          AND w.carrier = f.carrier_alpha_code
          AND w.site_id = f.site_id;
       EXCEPTION
          WHEN NO_DATA_FOUND THEN
          BEGIN
             /* No nominal weight is defined for the flight number. Therefore 
                use the carrier's generic nominal weights */
             SELECT weight 
             INTO l_kg_weight
             FROM weights w, flight f
             WHERE f.flight_id=p_flight_id
             AND w.site_id = f.site_id
             AND f.carrier_alpha_code = w.carrier
             AND w.flight_no IS NULL;
          EXCEPTION
             WHEN NO_DATA_FOUND THEN
             /* No nominal weight has been created for the carrier. 
                The bag's weight will be set to zero so that it can be 
                loaded without a problem */
             l_kg_weight := 0;
          END;
       END;     
    END IF;
    
    IF (l_uld_current_weight + l_kg_weight) > l_max_weight THEN
       RETURN -20791;
    ELSE
       RETURN 0;
    END IF;
 EXCEPTION
    WHEN TOO_MANY_ROWS THEN
      RETURN -1021; -- unresolvable duplicate   
 END;


/
