/***********************************************************************
 *                                                                     *
 *             Copyright (c) Ultra Electronics Limited 2004            *
 *                         All rights reserved                         *
 *                                                                     *
 ***********************************************************************
 *
 * Filename:           uld_max_weight.sql
 * Module Name:        UltraTrak Database
 * Originating Author: Gary Roberts
 * Design Reference:   UAS/FALCP/SS/3142
 *
 ***********************************************************************
 *
 * Description:
 *
 *    This function finds the maximum weight for the supplied ULD from
 *    the ULD_WEIGHTS table in the database. The function determines the
 *    site_id and carrier from the supplied flight_id.
 *
 *
 ***********************************************************************
 * 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_max_weight(p_uld_no IN ulds.uld_no%TYPE,
                                          p_flight_id IN flight.flight_id%TYPE)
RETURN NUMBER
AS
max_uld_weight uld_weights.max_weight%TYPE;

/* 
 * Find the maximum weight of a ULD, using the flight_id to determine
 * the carrier and site_id.
 */

BEGIN
   SELECT max_weight
   INTO  max_uld_weight
   FROM  uld_weights u,
         flight f
   WHERE f.flight_id = p_flight_id
   AND   f.carrier_alpha_code = u.carrier
   AND   f.site_id = u.site_id
   AND   substr(p_uld_no,1,3) = u.uld_prefix;

RETURN max_uld_weight;
   

EXCEPTION
   WHEN NO_DATA_FOUND THEN
       RETURN NULL;       
END;

/
