/*****************************************************************************
*
* Filename         - $Workfile:   get_uld_data.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 Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_uld_data.sql  $  $Revision:   1.7  $
*
* 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/tagtarch/db_ora/db/source/live/procedures/get_uld_data.sqv  $
* 
*    Rev 1.7   05 Mar 1999 11:36:40   tagtrak
* OR:
* CR: 13677
* REV:
* Changes as a result of System Testing.
* 
* Neil Bishop.
* 
*    Rev 1.6   02 Apr 1998 19:36:14   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.5   15 Jan 1998 12:13:04   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:12:50   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace package get_uld_data_pack

create or replace package get_uld_data_pack
as

	/*
	 * - Declare record types and procedures
	 */

	type get_uld_data_rec is record (
		ucls  uld_dests.uld_class_list%type, --CHAR(10) - was CHAR(3) originally!
		utype ulds.uld_type%type,            --CHAR(1)
		udest uld_dests.uld_destination%type,--CHAR(3)
		mloc  bag.location_code%type         --CHAR(6)
	);

	type get_uld_data_table_type is table of get_uld_data_rec
		index by binary_integer;

	procedure get_uld_data (
		fid                in  number,
		get_uld_data_table out get_uld_data_table_type
	); -- Procedure Declaration

end get_uld_data_pack;
/


PROMPT create or replace package body get_uld_data_pack 

create or replace package body get_uld_data_pack as

	procedure get_uld_data (
		fid                in  number,
		get_uld_data_table out get_uld_data_table_type
	)
	is

		dcount number(6);
		i      binary_integer:=1;

		cursor csr_1 is
			select
				uld_no uno,
				uld_type utyp
			from
				ulds
			where
				flight_id      = fid and
				uld_no_of_bags > 0;

	begin

		/*
		 * - Loop around the cursor csr_1
		 *   Call the get_first_bag_locn procedure
		 *   Initialise the dcount variable
		 *   declare a cursor that will select the uld_destination and the
		 *    uld_class_list from the uld_dests table where the uld_no is
		 *    equal to the cursor uno element and the flight_id is equal to
		 *    the fid variable.
		 *   loop around this cursor
		 *     increment dcount
		 *     IF cursor element ucl is not like '_         ' then
		 *       Assign 'Amx' to the ucls
		 *     ELSE IF dcount = 1 then
		 *       ucls = ucl
		 *     ELSE IF dcount > 1 then
		 *       IF ucls != ucl then
		 *         ucls = 'Amx'
		 *     IF dcount > 1 then
		 *       Assign 'mix' to udest
		 *     ELSE
		 *       udest = udst
		 *    End Loop
		 *    utype = utyp
		 *    IF utype = 'L' then
		 *      blank out utype by assigning '' to it
		 *    Increment i
		 * - End Loop
		 */

		for csr_rec_1 in csr_1 loop
		
		/*
		 * using Amx for Mix class is to fool the procedure that calls this one
		 * to sort it correctly
		 */

			get_first_bag_locn (
				fid,
				csr_rec_1.uno,
				get_uld_data_table(i).mloc
			);

			dcount := 0;

			declare cursor csr_0 is
				select
					uld_destination udst,
					uld_class_list ucl
				from
					uld_dests
				where
					uld_no    = csr_rec_1.uno and
					flight_id = fid;

			begin
				for csr_rec_0 in csr_0 loop
					dcount := dcount + 1;
					if (csr_rec_0.ucl NOT LIKE '_         ') then

						/*
						 * check if class is mixed ie. if uld was assigned to > 1 class
						 * or assigned to more than 1 dest with different classes.
						 */

						get_uld_data_table(i).ucls := 'Amx';

					elsif (dcount = 1) then

						get_uld_data_table(i).ucls := csr_rec_0.ucl;

					elsif (dcount > 1) then
						if (get_uld_data_table(i).ucls != csr_rec_0.ucl) then
							get_uld_data_table(i).ucls := 'Amx';
						end if;
					end if;

					if (dcount > 1) then
						/*
						 * check if dest is mixed, ie uld has been assigned to > 1 dest
						 * use mix for Mix to help the c code sort it
						 */
						get_uld_data_table(i).udest := 'mix';

					else
						get_uld_data_table(i).udest := csr_rec_0.udst;
					end if;
				end loop;
			end;

			get_uld_data_table(i).utype := csr_rec_1.utyp;

			/*
			 * Commented out the if statement below because it was causing
			 * the ULD Breakdown report not to report any type 'L' bags.
			 * Neil Bishop.  25/02/99.
			 */

			/*
			if (get_uld_data_table(i).utype = 'L') then
			*/
				/* blank out type if it is 'L' */
			/*
				get_uld_data_table(i).utype := '';
			end if;
			*/

			i := i + 1;

		end loop;

	end get_uld_data;

end get_uld_data_pack;
/
