/*****************************************************************************
*
* Filename         - $Workfile:   deassign_uld.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/

PROMPT create or replace procedure deassign_uld

create or replace procedure deassign_uld (
        p_uld_no   in     char,
        userid     in     number,
        loc        in     char,
        retval        out integer
)
as
        flight_id        flight.flight_id%type;
        flight_no        flight.flight_no%type;
        flight_date      flight.flight_date%type;
        uld_no_of_bags   integer;
        is_hold          smallint;
        depair           pax.departure_airport_code%type := null;
        trace_time       char(5);
        
        l_user_id        tagtrak_users.user_id%TYPE := userid;
        l_uld_no         ulds.uld_no%TYPE := p_uld_no;
begin

        depair :=  get_offset.get_user_location(userid);

        /*
         * - Initialise variables
         * - Check if the uld_no passed in is a hold
         * - If it is not a hold then
         *   Select the uld_no_of_bags from the ulds table where the uld_no
         *    is equal to the uld_no passed in and the pd_marker = 'N'.  If
         *    no records found, then set the number of bags to -1.
         *   IF the number of bags = -1 (ie. no rows found) then
         *     return value = -1001 (no uld records found)
         *   else IF the number bags > 0 theb
         *     return value =  -1101 (bags loaded in the ULD)
         *   else
         *     create a ULD_TRACE entry
         *     delete this ULD
         * - else
         *   return value = -1100 (its a hold)
         */

        uld_no_of_bags := -1;
        retval         := 0;

        /* check if it is a hold */
        select
                count(hold_no)
        into
                is_hold
        from
                holds
        where
                hold_no = p_uld_no;


        if (is_hold = 0) then
        /* try and get the uld_details */
        begin
                select
                        a.uld_no_of_bags, b.flight_id, b.flight_no, b.flight_date
                into
                        uld_no_of_bags , flight_id, flight_no, flight_date
                from
                        ulds a, flight_outbound_v1 b
                where   a.flight_id = b.flight_id
                  and   a.uld_no    = p_uld_no
                  and   b.site_id   = multi_site.get_user_siteid(userid)
                  and   a.pd_marker = 'N';

                exception
                        when NO_DATA_FOUND then
                                uld_no_of_bags := -1;
                end;

                if (uld_no_of_bags = -1) then
                        retval := -1001;      /* no uld records found */
                elsif (uld_no_of_bags > 0) then
                        retval := -1101;  /* bags loaded in the uld */
                else
                        /* now its alright to delete the uld from the ulds table */

                        -- Create a ULD_TRACE entry to show the ULD has been deassigned.
                        add_uld_trace_record(l_uld_no, flight_id, l_user_id, '0', loc, trace_time, 'DEASSIGN', 'Y');

                        -- Ensure that the ULD Number is not stored in the ULD_RECORDED_ID_CODES table
                        begin
                          update uld_recorded_id_codes
                             set uld_no = null
                           where uld_no = p_uld_no
                             and flight_no = RTrim(flight_no)
                             and flight_date = flight_date;
                        exception
                          when no_data_found then
                            null;
                        end;

                        delete from
                                ulds
                        where
                                uld_no    = p_uld_no  and
                                pd_marker = 'N';
                end if;
        else
                /* return error cos can't deassign a bulk, well not without major
                surgery to the plane */
                retval := -1100;
         end if;

end deassign_uld;
/


