/*********************************************************************
 *                                                                   *
 *             Copyright (c) Ultra Electronics Limited 2007.         *
 *                           All rights reserved.                    *
 *                                                                   *
 *********************************************************************
 *                                                                   *
 * Filename:             weight_config.sql                           *
 * Module Name:          UltraTrak Database                          *
 * Originating Author:   Gary Roberts                                *
 * Design Reference:     UAS/FALCP/SS/3142                           *
 *                                                                   *
 *********************************************************************
 *                                                                   *
 * Description:                                                      *
 *    This file contains stored procedures used by the MWS+ to       *
 *    insert, update and delete rows from the WEIGHTS and            *
 *    ULD_WEIGHTS tables.                                            *
 *                                                                   *
 *********************************************************************
 *                                                                   *
 * Program Identification Array:                                     *
 *                                                                   *
 * "%Z%%M% VERSION %I% %E% %U%"
 *                                                                   *
 *********************************************************************
 *                                                                   *
 * Modification History:                                             *
 *                                                                   *
 * Rel Date/Time      Proj/Id   CA Ref/Details Of Change             *
 * --- -------------- --------  ------------------------------------ *
 *********************************************************************/


PROMPT CREATE OR REPLACE PACKAGE weight_config
CREATE OR REPLACE PACKAGE weight_config is

/**********************************************************************
 *
 * PROCEDURE add_weights
 *
 * Description:
 *     This procedure is used to add nominal weights config entries
 *     into the WEIGHTS table. These entries define nominal weights
 *     on a per flight or per carrier basis. To define a nominal weight
 *     for a flight, p_site_id, p_carrier, p_flight_no and p_weight
 *     must all have non-NULL values. To define a nominal weight for
 *     a carrier, p_flight_no should be NULL. Weights defined on a 
 *     per-flight basis take precendece over those defined on a per
 *     carrier basis. 
 *    
 * Parameters:
 *     p_site_id       The site_id that the nominal weight is to be
 *                     configured for.
 *     p_carrier       The carrier that the nominal weight is to be 
 *                     configured for.
 *     p_flight_no     The flight_no that the weight is to be configured
 *                     for. Note that p_flight number should be set to
 *                     NULL to configure a carrier's generic nominal
 *                     weight.
 *     p_weight        This is the nominal weight value.
 *     p_units         This is the value of units. Currently, database
 *                     processing is all performed in kilograms.
 *                     
 *
 **********************************************************************/
PROCEDURE add_weights
(
    p_site_id      weights.site_id%TYPE,
    p_carrier      weights.carrier%TYPE,
    p_flight_no    weights.flight_no%TYPE,
    p_weight       weights.weight%TYPE,
    p_units        weights.units%TYPE
);

/**********************************************************************
 *
 * PROCEDURE update_weights
 *
 * Description:
 *     This procedure is used to update nominal weights config entries
 *     in the WEIGHTS table. These entries define nominal weights
 *     on a per flight or per carrier basis. To define a nominal weight
 *     for a flight, p_site_id, p_carrier, p_flight_no and p_weight
 *     must all have non-NULL values. To define a nominal weight for
 *     a carrier, p_flight_no should be NULL. Weights defined on a
 *     per-flight basis take precendece over those defined on a per
 *     carrier basis.
 *
 * Parameters:
 *     p_site_id       The site_id that the nominal weight is to be
 *                     configured for.
 *     p_carrier       The carrier that the nominal weight is to be
 *                     configured for.
 *     p_flight_no     The flight_no that the weight is to be configured
 *                     for. Note that p_flight number should be set to
 *                     NULL to configure a carrier's generic nominal
 *                     weight.
 *     p_weight        This is the nominal weight value.
 *     p_units         This is the value of units. Currently, database
 *                     processing is all performed in kilograms.
 *
 *
 **********************************************************************/

PROCEDURE update_weights
(
    p_site_id      weights.site_id%TYPE,
    p_carrier      weights.carrier%TYPE,
    p_flight_no    weights.flight_no%TYPE,
    p_weight       weights.weight%TYPE,
    p_units        weights.units%TYPE
);

/**********************************************************************
 *
 * PROCEDURE delete_weights
 *
 * Description:
 *     This procedure is used to delete nominal weights config entries
 *     from the WEIGHTS table. These entries define nominal weights
 *     on a per flight or per carrier basis. To define a nominal weight
 *     for a flight, p_site_id, p_carrier, p_flight_no and p_weight
 *     must all have non-NULL values. To define a nominal weight for
 *     a carrier, p_flight_no should be NULL. Weights defined on a
 *     per-flight basis take precendece over those defined on a per
 *     carrier basis.
 *
 * Parameters:
 *     p_site_id       The site_id that the nominal weight is
 *                     configured for.
 *     p_carrier       The carrier that the nominal weight is
 *                     configured for.
 *     p_flight_no     The flight_no that the weight is configured
 *                     for. 
 *
 **********************************************************************/

PROCEDURE delete_weights
(
    p_site_id      weights.site_id%TYPE,
    p_carrier      weights.carrier%TYPE,
    p_flight_no    weights.flight_no%TYPE
);

/**********************************************************************
 *
 * PROCEDURE add_uld_weights
 *
 * Description:
 *     This procedure is used to add entries to the ULD_WEIGHTS table
 *     These entries are used to define maximum load weights for 
 *     different types of ULDs on each carrier.
 *
 * Parameters:
 *     p_site_id       The site_id that the nominal weight is to be
 *                     configured for.
 *     p_carrier       The carrier that the nominal weight is to be
 *                     configured for.
 *     p_uld_prefix    The ULD prefix, which defines the type of ULD.
 *     p_max_weight    The maximum load weight of the ULD.
 *
 **********************************************************************/

PROCEDURE add_uld_weights
(
    p_site_id    uld_weights.site_id%TYPE,
    p_carrier    uld_weights.carrier%TYPE,
    p_uld_prefix uld_weights.uld_prefix%TYPE,
    p_max_weight uld_weights.max_weight%TYPE
);

/**********************************************************************
 *
 * PROCEDURE update_uld_weights
 *
 * Description:
 *     This procedure is used to update entries to the ULD_WEIGHTS table
 *     These entries are used to define maximum load weights for
 *     different types of ULDs on each carrier.
 *
 * Parameters:
 *     p_site_id       The site_id that the nominal weight is to be
 *                     configured for.
 *     p_carrier       The carrier that the nominal weight is to be
 *                     configured for.
 *     p_uld_prefix    The ULD prefix, which defines the type of ULD.
 *     p_max_weight    The maximum load weight of the ULD
 *
 **********************************************************************/

PROCEDURE update_uld_weights
(
    p_site_id    uld_weights.site_id%TYPE,
    p_carrier    uld_weights.carrier%TYPE,
    p_uld_prefix uld_weights.uld_prefix%TYPE,
    p_max_weight uld_weights.max_weight%TYPE
);

/**********************************************************************
 *
 * PROCEDURE delete_uld_weights
 *
 * Description:
 *     This procedure is used to delete entries from the ULD_WEIGHTS 
 *     table. These entries are used to define maximum load weights for
 *     different types of ULDs on each carrier.
 *
 * Parameters:
 *     p_site_id       The site_id that the nominal weight is to be
 *                     configured for.
 *     p_carrier       The carrier that the nominal weight is to be
 *                     configured for.
 *     p_uld_prefix    The ULD prefix, which defines the type of ULD.
 *
 **********************************************************************/

PROCEDURE delete_uld_weights
(
    p_site_id    uld_weights.site_id%TYPE,
    p_carrier    uld_weights.carrier%TYPE,
    p_uld_prefix uld_weights.uld_prefix%TYPE
);

END weight_config;
/

PROMPT CREATE OR REPLACE PACKAGE BODY weight_config
CREATE OR REPLACE PACKAGE BODY weight_config IS

/**********************************************************************
 *
 * PROCEDURE add_weights
 *
 **********************************************************************/

PROCEDURE add_weights
(
    p_site_id      weights.site_id%TYPE,
    p_carrier      weights.carrier%TYPE,
    p_flight_no    weights.flight_no%TYPE,
    p_weight       weights.weight%TYPE,
    p_units        weights.units%TYPE
)
IS

BEGIN

    INSERT INTO weights(site_id, carrier, flight_no, weight, units) 
        VALUES (p_site_id, p_carrier, p_flight_no, p_weight,p_units);

END add_weights;

/**********************************************************************
 *
 * PROCEDURE update_weights
 *
 **********************************************************************/
PROCEDURE update_weights
(
    p_site_id      weights.site_id%TYPE,
    p_carrier      weights.carrier%TYPE,
    p_flight_no    weights.flight_no%TYPE,
    p_weight       weights.weight%TYPE,
    p_units        weights.units%TYPE
)
IS

BEGIN

    UPDATE weights SET weight = p_weight, units = p_units
        WHERE site_id = p_site_id
        AND carrier = p_carrier
        AND (flight_no = p_flight_no
             OR (flight_no IS NULL AND p_flight_no IS NULL));

END update_weights;

/**********************************************************************
 *
 * PROCEDURE delete_weights
 *
 **********************************************************************/
PROCEDURE delete_weights
(
    p_site_id      weights.site_id%TYPE,
    p_carrier      weights.carrier%TYPE,
    p_flight_no    weights.flight_no%TYPE
)
IS

BEGIN
    DELETE FROM weights 
        WHERE site_id = p_site_id
        AND carrier = p_carrier
        AND (flight_no = p_flight_no
             OR (flight_no IS NULL AND p_flight_no IS NULL));

END delete_weights;


/**********************************************************************
 *
 * PROCEDURE add_uld_weights
 *
 **********************************************************************/
PROCEDURE add_uld_weights
(
    p_site_id    uld_weights.site_id%TYPE,
    p_carrier    uld_weights.carrier%TYPE,
    p_uld_prefix uld_weights.uld_prefix%TYPE,
    p_max_weight uld_weights.max_weight%TYPE
)
IS

BEGIN
    INSERT INTO uld_weights(site_id, carrier, uld_prefix, max_weight)
        VALUES (p_site_id, p_carrier, p_uld_prefix, p_max_weight);

END add_uld_weights;

/**********************************************************************
 *
 * PROCEDURE update_uld_weights
 *
 **********************************************************************/
PROCEDURE update_uld_weights
(
    p_site_id    uld_weights.site_id%TYPE,
    p_carrier    uld_weights.carrier%TYPE,
    p_uld_prefix uld_weights.uld_prefix%TYPE,
    p_max_weight uld_weights.max_weight%TYPE
)
IS

BEGIN
    UPDATE uld_weights SET max_weight = p_max_weight
        WHERE site_id = p_site_id
        AND carrier = p_carrier
        AND uld_prefix = p_uld_prefix;

END update_uld_weights;

/**********************************************************************
 *
 * PROCEDURE delete_uld_weights
 *
 **********************************************************************/

procedure delete_uld_weights
(
    p_site_id    uld_weights.site_id%TYPE,
    p_carrier    uld_weights.carrier%TYPE,
    p_uld_prefix uld_weights.uld_prefix%TYPE
)

IS

BEGIN

    DELETE FROM uld_weights
        WHERE site_id = p_site_id
        AND carrier = p_carrier
        AND uld_prefix = p_uld_prefix;

END delete_uld_weights;

END weight_config;
/
