/*****************************************************************************
*
* Filename         - $Workfile:   check_ulds.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell 01/10/02    1.8          15691
* 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
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   check_ulds.sql  $  $Revision:   1.8  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /shared/pvcs/aisarch/db_ora/db/source/live/procedures/check_ulds.sqv  $
* 
*    Rev 1.8   01 Oct 2002 13:49:10   tagtrak
* OR:
* CR: 15691
* Amend check_ulds to check change of connection_type
* 
*    Rev 1.7   20 Sep 1999 14:40:20   tagtrak
*  
* 
*    Rev 1.6   02 Apr 1998 19:32:18   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.5   15 Jan 1998 12:11:22   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.4   24 Oct 1997 11:02:02   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure check_ulds

create or replace procedure check_ulds (
        uldno             in out  char,
        fltid             in out  number,
        ucategory         in out  char,
        retval               out  number,
        p_connection_type in      ulds.connection_type%TYPE DEFAULT NULL -- CR 15691
)

as 

        ishold       number(6);
        is_bag_group number;
        retval_param number;

        cursor c1 is
                select
                        flight_id,
                        pd_marker,
                        connection_type, -- CR 15691
                        uld_category
                from
                        ulds
                where
                        uld_no = uldno; 

begin

        /*
         * - Initialise variable
         * - check that given uld name does not exist as a bag group
         * - count the number of holds from the holds table where the hold_no
         *   is equal to the parameter which was passed in.
         * - if the count is equal to 0 (ie. nothing was found, not a hold) then
         *   loop around the c1_rec cursor
         *     if the flight_id is not equal to the flight id passed in as a
         *     parameter then
         *       if the delete marker is 'N' then
         *         return value = -200
         *     else if the parameter is 0 then
         *       if they are different uld_types then
         *         return value = -204
         *       else if pd_marker = 'N' then
         *         return value = 8
         *       else
         *         return value = 9
         *   end loop
         * - else
         *   count the number of ulds from the ulds table where the uld_no is
         *   equal to the uld_no passed in as a parameter and also the flight_id
         *   is equal to the flight_id passed in as a parameter.
         *   IF the count is 0 then
         *     return value = 7
         *   ELSE
         *     return value = 8
         *   IF return value is equal to 0 then
         *     return value = 7
         */

        retval_param := 0;

 
        /* is it a hold */
        select
                count(hold_no) 
        into
                ishold 
        from
                holds
        where
                hold_no =  uldno; 

        if (ishold = 0) then 
                /* not a hold so do all the checks for flight, etc */
                /* For Statement */
                for c1_rec in c1 loop 


                        if (c1_rec.flight_id != fltid) then 

                                if (c1_rec.pd_marker = 'N') then 
                                        /* uld for different flight */
                                        retval_param := -200;
                                end if;

                        elsif (retval_param = 0) then 
                                /* fid = fltid */

                                /* CR 15691 set retval to 10 if either type or connection_type change */
                                if (c1_rec.uld_category != ucategory) 
                                or (c1_rec.connection_type != p_connection_type) then 
                                        /* uld for different type or connection type */
                                        retval_param := 10;
                                end if;

                                if (c1_rec.pd_marker = 'N') then 
                                        /* uld already assigned to this flight */
                                        retval_param := retval_param + 8;
                                else
                                        /* uld was assigned to this flight, this flight can no longer
                                                 be PD so reuse this record */
                                        retval_param := retval_param + 9;
                                end if;

                        end if;
                end loop;

        else 
                /* it is a hold so check it is assigned */
                select
                        count(uld_no) 
                into
                        ishold 
                from
                        ulds
                where
                        uld_no =  uldno and
                        flight_id =  fltid; 

                if (ishold = 0) then 
                        /* uld not yet assigned anywhere */
                        retval_param := 7;
                else
                        /* uld already assigned to this flight */
                        retval_param := 8;
                end if;
        end if;
 
        if (retval_param = 0) then 

                /* uld not yet assigned anywhere */

 
                /* check that there is no bag group with this name */
                select count(*) into is_bag_group from bag_group where group_no = uldno and
                   site_id in (select site_id from flight where flight_id = fltid);

                if (is_bag_group > 0) then
                   /* Bag group exists */
                   retval_param := -20058;
                else
                   retval_param := 7;
                end if;
        end if;

        retval := retval_param;
 
end check_ulds;
/
