/***********************************************************************
 *                                                                     *
 *             Copyright (c) Ultra Electronics Limited 2004            *
 *                         All rights reserved                         *
 *                                                                     *
 ***********************************************************************
 *                                                                     
 * Filename:           uld_bag_weight.sql                                
 * Module Name:        UltraTrak Database         
 * Originating Author: Gary Roberts     
 * Design Reference:   UAS/FALCP/SS/3142 
 *                                                                     
 ***********************************************************************
 *                                                                     
 * Description:
 *
 *    This function finds the total weight of all the bags on a given 
 *    ULD. If a bag has a weight stored for it in the KG_WEIGHT column
 *    of the BAG table, then this is taken to be the weight of the bag.
 *    Otherwise, a nominal weight is used. If no nominal weight is
 *    defined for the flight number that the bag is on, and the carrier
 *    for that flight number has no general nominal weight defined, then
 *    the bag weight is set to zero. 
 *                                                                     
 *                                                                     
 *                                                                     
 ***********************************************************************
 * 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 uld_bag_weight(
      p_uld_no IN ulds.uld_no%TYPE, 
      p_flight_id IN flight.flight_id%TYPE) 
RETURN NUMBER 
IS
l_tot_bag_weight NUMBER; 

/*
 * Select the weights of the bags on the uld into a cursor.
 */
CURSOR c_bags_on_uld IS SELECT nvl(kg_weight,0) kg_weight
  FROM bag b 
  WHERE b.uld_no=p_uld_no 
  AND uld_flight_id=p_flight_id;
l_nominal_weight NUMBER;

BEGIN
--Initialise the total bag weight to zero
l_tot_bag_weight := 0;


BEGIN
/*
 * Find the nominal weight for the flight number. If there is no nominal weight for
 * this flight then use the generic nominal weight for the carrier if one is defined,
 * otherwise use a nominal weight of zero.
 */

   SELECT weight 
   INTO l_nominal_weight 
   FROM weights,flight 
   WHERE p_flight_id=flight.flight_id
   AND weights.site_id=flight.site_id 
   AND flight.flight_no=weights.flight_no
   AND flight.carrier_alpha_code = weights.carrier;
EXCEPTION
   WHEN no_data_found THEN
   BEGIN
       SELECT weight 
       INTO l_nominal_weight 
       FROM weights,flight 
       WHERE p_flight_id=flight.flight_id
       AND weights.site_id=flight.site_id 
       AND flight.carrier_alpha_code = weights.carrier
       AND weights.flight_no IS NULL;
   EXCEPTION
       WHEN no_data_found THEN
       l_nominal_weight := 0;
   END;
END;

/*
 * Cycle through the bags on the ULD and sum the bag weights. If a bag's weight is
 * zero then use the nominal weight for that bag.
 */

FOR rec IN c_bags_on_uld LOOP

   IF rec.kg_weight = 0
   THEN
      l_tot_bag_weight := l_tot_bag_weight + l_nominal_weight;
   ELSE
      l_tot_bag_weight := l_tot_bag_weight + rec.kg_weight;
   END IF;
END LOOP;
  
RETURN(l_tot_bag_weight);
END uld_bag_weight;

/
