/*****************************************************************************
*
* Filename         - $Workfile:   check_bag_transfer.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
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   check_bag_transfer.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/check_bag_transfer.sqv  $
* 
*    Rev 1.7   16 Dec 1998 11:34:04   tagtrak
* OR: 12872
* CR: 12872
* REV:
* Correct problem with database error occuring when transferring
* bags from a Hold.
* -------------
* Andrew Walker
* -------------
* (branch ATS revision copied to trunk.)
* Andrew Walker
* 
*    Rev 1.6.1.0   19 Nov 1998 16:14:26   tagtrak
* OR: 12872
* CR: 12872
* REV:
* Correct problem with database error occuring when transferring
* bags from a Hold.
* -------------
* Andrew Walker
* 
*    Rev 1.6   25 Sep 1998 10:02:42   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merged Vienna (0.3.1E) and Core Product (0.2.5E) -
* branch ATS revision moved to trunk.
* Andrew Walker
* 
*    Rev 1.5.2.0   18 Aug 1998 09:34:38   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
* 
*    Rev 1.5.1.0   04 Jun 1998 15:32:00   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna Modifications
* Andrew Walker
* 
*    Rev 1.5   02 Apr 1998 19:32:12   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.4   26 Feb 1998 10:08:20   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
* 
*    Rev 1.3   15 Jan 1998 12:11:18   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.2   24 Oct 1997 11:01:36   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure check_bag_transfer

create or replace procedure check_bag_transfer (
  puserid       in     number,	
  to_uld_no     in     char,
	tag1          in     char,
	tag2a         in     char,
	tag2b         in     char,
	tag3          in     integer,
	sno           in     integer,
	input_fid     in     integer,
	ufid          in     integer,
	codeshare_fid in     integer,
	retval        in out integer,
	from_uld_no   in out char,
	pax_id           out integer,
	output_fid    in out integer,
	rtag             out char
)
as 

	flt_id_of_pax_no_sno         integer;
	last_flt_id_of_pax_no_sno         integer;
	fidcount     integer;
	bag_count    integer;
	tpax_id      integer;
        tfrom_uld_no char(10);
        tflt_id_of_pax	   integer;
        trtag        char(10);
	tflt_id_of_pax_no_sno	integer;
	uld_count    integer;
	tpsn         integer;
	flt_id_of_pax      integer;
	back_uld_no  char(10);
	last_flt_id_of_pax  integer;
	flt_id_count integer;
	new_pax_id   integer;
	is_to_hold      smallint;
	is_from_hold      smallint;
	tbooked_flight_id number;
	last_pax_id  number;
	match_pax    number;
  depair       site_info.airport_code%type;
        atl_brs      number;
        atl_dcs      number;
        del_mar      number;
        delete_flag  number;
        brs_flag     char(1);
        dcs_flag     char(1);
        ldd_sts      char(1);
        ldd_count    number;

	cursor c1 is
		select
			p.pax_sequence_no,
			nvl(b.dcs_bag_tag, ' '),
			nvl(b.uld_no, ' '),
			p.flight_id,
			p.pax_id,
			p.booked_flight_id,
			b.authority_to_load_brs,
			b.authority_to_load_dcs,
			b.delete_marker,
			b.loaded_status
		from
			pax p,
			bag b
		where
			p.pax_sequence_no = sno      and
			p.pax_id          = b.pax_id and
			p.delete_marker   = 0        and
			b.delete_marker   = 0        and
			b.bag_tag2b       = tag2b    and
			b.bag_tag3        = tag3     and
      b.bag_source_location =  multi_site.get_user_loc(puserid);

	cursor c2 is
		select
			p.pax_sequence_no,
			nvl(b.dcs_bag_tag, ' '),
			nvl(b.uld_no, ' '),
			p.flight_id,
			p.pax_id,
			p.booked_flight_id,
			b.authority_to_load_brs,
			b.authority_to_load_dcs,
			b.delete_marker,
			b.loaded_status
		from
			pax p,
			bag b
		where
			p.pax_sequence_no = sno      and
			p.pax_id          = b.pax_id and
			p.delete_marker   = 0        and
			b.delete_marker   = 0        and
			b.bag_tag2a       = tag2a    and
			b.bag_tag3        = tag3     and
      b.bag_source_location =  multi_site.get_user_loc(puserid);

	cursor c3 is
		select
			p.pax_sequence_no,
			nvl(b.dcs_bag_tag, ' '),
			nvl(b.uld_no, ' '),
			p.flight_id,
			p.pax_id,
			p.booked_flight_id,
			b.authority_to_load_brs,
			b.authority_to_load_dcs,
			b.delete_marker,
			b.loaded_status
		from
			pax p,
			bag b
		where
			p.pax_sequence_no = sno      and
			p.flight_id       = input_fid      and
			p.pax_id          = b.pax_id and
			p.delete_marker   = 0        and
			b.delete_marker   = 0        and
			b.bag_tag2b       = tag2b    and
			b.bag_tag3        = tag3;

	cursor c4 is
		select
			p.pax_sequence_no,
			nvl(b.dcs_bag_tag, ' '),
			nvl(b.uld_no, ' '),
			p.flight_id,
			p.pax_id,
			p.booked_flight_id,
			b.authority_to_load_brs,
			b.authority_to_load_dcs,
			b.delete_marker,
			b.loaded_status
		from
			pax p,
			bag b
		where
			p.pax_sequence_no = sno      and
			p.flight_id       = input_fid      and
			p.pax_id          = b.pax_id and
			p.delete_marker   = 0        and
			b.delete_marker   = 0        and
			b.bag_tag2a       = tag2a    and
			b.bag_tag3        = tag3;

	cursor c5 is
		select
			nvl(b.dcs_bag_tag, ' '),
			nvl(b.uld_no, ' '),
			b.pax_id,
			p.flight_id,
			p.booked_flight_id,
			b.authority_to_load_brs,
			b.authority_to_load_dcs,
			b.delete_marker,
			b.loaded_status
		from
			bag b,
			pax p
		where
			b.bag_tag2b     = tag2b and
			b.bag_tag3      = tag3  and
			b.delete_marker = 0     and
			p.pax_id        = b.pax_id and
      b.bag_source_location =  multi_site.get_user_loc(puserid);

	cursor c6 is
		select
			nvl(b.dcs_bag_tag, ' '),
			nvl(b.uld_no, ' '),
			b.pax_id,
			p.flight_id,
			p.booked_flight_id,
			b.authority_to_load_brs,
			b.authority_to_load_dcs,
			b.delete_marker,
			b.loaded_status
		from
			bag b,
			pax p
		where
			b.bag_tag2a     = tag2a and
			b.bag_tag3      = tag3  and
			b.delete_marker = 0     and
			p.pax_id        = b.pax_id and
      b.bag_source_location =  multi_site.get_user_loc(puserid);

	cursor c7 is
		select
			nvl(b.dcs_bag_tag, ' '),
			nvl(b.uld_no, ' '),
			b.pax_id,
			p.flight_id,
			p.booked_flight_id,
			b.authority_to_load_brs,
			b.authority_to_load_dcs,
			b.delete_marker,
			b.loaded_status
		from
			bag b,
			pax p
		where
			p.pax_id        = b.pax_id and
			b.delete_marker = 0        and
			b.bag_tag2b     = tag2b    and
			b.bag_tag3      = tag3     and
			p.flight_id     = ufid;

	cursor c8 is
		select
			nvl(b.dcs_bag_tag, ' '),
			nvl(b.uld_no, ' '),
			b.pax_id,
			p.flight_id,
			p.booked_flight_id,
			b.authority_to_load_brs,
			b.authority_to_load_dcs,
			b.delete_marker,
			b.loaded_status
		from
			bag b,
			pax p
		where
			p.pax_id        = b.pax_id and
			b.delete_marker = 0        and
			b.bag_tag2a     = tag2a    and
			b.bag_tag3      = tag3     and
			p.flight_id     = ufid;

begin

	/*
	 * - Initialise Variables
	 * - Count the number of holds from the holds table for this particular
	 *    hold_no
	 * - IF sno is not equal to 0 and fid is equal to 0 THEN
	 *   IF tag2b is not equal to '-' THEN
	 *     Open the c1 cursor
	 *     LOOP
	 *       Fetch the c1 cursor variables into the relevant variables and
	 *        exit when no more records are found.
	 *       Increment bag_count
	 *       IF last_flt_id_of_pax is not equal to flt_id_of_pax THEN
	 *         Assign flt_id_of_pax to last_flt_id_of_pax
	 *         Increment flt_id_count
	 *     END LOOP
	 *     Close the c1 cursor
	 *   ELSE
	 *     Open the c2 cursor
	 *     LOOP
	 *       Fetch the c2 cursor variables into the relevant variables and
	 *        exit when no more records are found.
	 *       Increment bag_count
	 *       IF last_flt_id_of_pax is not equal to flt_id_of_pax THEN
	 *         Assign flt_id_of_pax to last_flt_id_of_pax
	 *         Increment flt_id_count
	 *     END LOOP
	 *     Close the c2 cursor
	 * - ELSEIF sno is not equal to 0 and fid is not equal to 0 THEN
	 *   IF tag2b is not equal to '-' THEN
	 *     Open the c3 cursor
	 *     LOOP
	 *       Fetch the c3 cursor variables into the relevant variables and
	 *        exit when no more records are found.
	 *       Increment bag_count
	 *       IF last_flt_id_of_pax is not equal to flt_id_of_pax THEN
	 *         Assign flt_id_of_pax to last_flt_id_of_pax
	 *         Increment flt_id_count
	 *     END LOOP
	 *     Close the c3 cursor
	 *   ELSE
	 *     Open the c4 cursor
	 *     LOOP
	 *       Fetch the c4 cursor variables into the relevant variables and
	 *        exit when no more records are found.
	 *       Increment bag_count
	 *       IF last_flt_id_of_pax is not equal to flt_id_of_pax THEN
	 *         Assign flt_id_of_pax to last_flt_id_of_pax
	 *         Increment flt_id_count
	 *     END LOOP
	 *     Close the c4 cursor
	 * - ELSEIF ufid > 0 THEN
	 *      Open the c7 cursor
	 *     LOOP
	 *       Fetch the c7 cursor variables into the relevant variables and
	 *        exit when no more records are found.
	 *       Increment bag_count
	 *       IF flt_id_of_pax_no_sno is not equal to last_flt_id_of_pax_no_sno THEN
	 *         Increment fidcount variable
	 *       IF last_flt_id_of_pax_no_sno is equal to 0 THEN
	 *         Assign flt_id_of_pax_no_sno to last_flt_id_of_pax_no_sno
	 *     END LOOP
	 *     Close the c7 cursor
	 *   ELSE
	 *     Open the c8 cursor
	 *     LOOP
	 *       Fetch the c8 cursor variables into the relevant variables and
	 *        exit when no more records are found.
	 *       Increment bag_count
	 *       IF flt_id_of_pax_no_sno is not equal to last_flt_id_of_pax_no_sno THEN
	 *         Increment fidcount variable
	 *       IF last_flt_id_of_pax_no_sno is equal to 0 THEN
	 *         Assign flt_id_of_pax_no_sno to last_flt_id_of_pax_no_sno
	 *     END LOOP
	 *     Close the c8 cursor
	 * - ELSEIF tag2b is not equal to '-' THEN
	 *     Open the c5 cursor
	 *     LOOP
	 *       Fetch the c5 cursor variables into the relevant variables and
	 *        exit when no more records are found.
	 *       Increment bag_count
	 *       IF flt_id_of_pax_no_sno is not equal to last_flt_id_of_pax_no_sno THEN
	 *         Increment fidcount variable
	 *       IF last_flt_id_of_pax_no_sno is equal to 0 THEN
	 *         Assign flt_id_of_pax_no_sno to last_flt_id_of_pax_no_sno
	 *     END LOOP
	 *     Close the c5 cursor
	 * - ELSE
	 *   Open the c6 cursor
	 *   LOOP
	 *     Fetch the c6 cursor variables into the relevant variables and
	 *      exit when no more records are found.
	 *     Increment bag_count
	 *     IF flt_id_of_pax_no_sno is not equal to last_flt_id_of_pax_no_sno THEN
	 *       Increment fidcount variable
	 *     IF last_flt_id_of_pax_no_sno is equal to 0 THEN
	 *       Assign flt_id_of_pax_no_sno to last_flt_id_of_pax_no_sno
	 *   END LOOP
	 *   Close the c6 cursor
	 * - IF bag_count = 0 THEN
	 *   Assign -1012 to the return value
	 * - ELSEIF bag_count > 1 and the sno equals 0 THEN
	 *   IF fidcount > 1 THEN
	 *     Assign -1003 (Duplicate tag) to the return value
	 *   ELSE
	 *     Assign -1021 (Unresolvable Duplicate tag) to the return value
	 * - ELSEIF flt_id_count > 1 THEN
	 *   Assign -138 to the return value
	 * - ELSEIF sno is equal to 0 THEN
	 *   IF is_to_hold is equal to 0 THEN
	 *     Select the flight_id from the ulds table where the uld_no is
	 *      equal to the from_uld_no and the pd_marker is equal to 'N'.
	 *   ELSE
	 *     assign fid to the flt_id_of_pax variable
	 * - IF is_to_hold is equal to 0 THEN
	 *   IF sno is equal to 0 THEN
	 *     assign the flt_id_of_pax variable to the flight_id variable
	 *   ELSE
	 *     Select the flight_id from the ulds table where the uld_no is
	 *      equal to the the to_uld_no variable and the pd_marker is equal to
	 *      'N'.
	 * - ELSE
	 *   Assign fid to the flight_id variable
	 * - IF the return value is equal to 0 and the flight_id's are not the
	 *    Same then
	 *   Assign -1006 to the return value
	 */
			
	retval       := 0;
	tpax_id      := 0;
	tflt_id_of_pax     := 0;
        tfrom_uld_no := NULL;
        trtag        := NULL;
        tflt_id_of_pax_no_sno	:= 0;
	bag_count    := 0;
	uld_count    := 0;
	back_uld_no  := NULL;
	last_flt_id_of_pax  := -1;
	flt_id_count := 0;
	flt_id_of_pax      := 0;
	flt_id_of_pax_no_sno         := 0;
	last_flt_id_of_pax_no_sno         := 0;
	fidcount     := 0;
	from_uld_no      := NULL;
	pax_id       := 0;
	output_fid    := 0;
	rtag         := ' ';
	last_pax_id  := 0;
	match_pax    := 0;
	atl_brs      := 0;
	atl_dcs      := 0;
	del_mar      := 0;
	delete_flag  := 0;
	brs_flag     := 0;
	dcs_flag     := 0;
	ldd_sts      := 'N';
	ldd_count    := 0;

	/*
	 * NOTE: if it is a hold we know that we will
	 * have been passed the flight_id.
	 */
	select
		count(hold_no)
	into
		is_to_hold
	from
		holds
	where
		hold_no = to_uld_no;


	/* step through the bags */
	if (sno != 0 and input_fid = 0) then		
	/* now join the pax and bag table */
		if (tag2b != '-') then
			open c1;

			loop
				fetch
					c1
				into
					tpsn, 
					trtag,
					tfrom_uld_no,
					tflt_id_of_pax,
					tpax_id,
					tbooked_flight_id,
					brs_flag,
					dcs_flag,
					del_mar,
                                        ldd_sts;

				exit when c1%notfound;
	
				if ( codeshare_fid = 0 or codeshare_fid = tbooked_flight_id ) then
				        bag_count := bag_count + 1;

				        if ( ldd_sts = 'Y' ) then
				                ldd_count := ldd_count + 1;

			                        from_uld_no := tfrom_uld_no;
			                        flt_id_of_pax := tflt_id_of_pax;
			                        rtag := trtag;
	    			                /* Check for > 1 Pax with same sequence No. */
					        pax_id := tpax_id;
					        if ( tpax_id != last_pax_id ) then
				  	                match_pax := match_pax + 1;
						        last_pax_id := tpax_id;
					        end if;
                                        end if;

				end if;

			        if ( ldd_sts = 'Y' ) then
			            if (brs_flag = 'N') then
			                    atl_brs := atl_brs + 1;
			            elsif (dcs_flag = 'N') then
 			                    atl_dcs := atl_dcs + 1;
				    elsif (del_mar = 1) then
			                    delete_flag := delete_flag + 1;
			            end if;

				    if (last_flt_id_of_pax != flt_id_of_pax) then
				           last_flt_id_of_pax  := flt_id_of_pax;
					   flt_id_count := flt_id_count + 1;
				    end if;
			        end if;

			end loop;
			close c1;
		else
			open c2;

			loop
				fetch
					c2
				into
					tpsn, 
					trtag,
					tfrom_uld_no,
					tflt_id_of_pax,
					tpax_id,
					tbooked_flight_id,
					brs_flag,
					dcs_flag,
					del_mar,
					ldd_sts;

				exit when c2%notfound;
	
				if ( codeshare_fid = 0 or codeshare_fid = tbooked_flight_id ) then
				        bag_count := bag_count + 1;

				        if ( ldd_sts = 'Y' ) then
				                ldd_count := ldd_count + 1;

                                                from_uld_no := tfrom_uld_no;
                                                flt_id_of_pax := tflt_id_of_pax;
                                                rtag := trtag;
	 				        /* Check for > 1 Pax with same sequence No. */
					        pax_id := tpax_id;
					        if ( tpax_id != last_pax_id ) then
						        match_pax := match_pax + 1;
						        last_pax_id := tpax_id;
					        end if;
				        end if;

				end if;

			        if ( ldd_sts = 'Y' ) then
			            if (brs_flag = 'N') then
			                    atl_brs := atl_brs + 1;
			            elsif (dcs_flag = 'N') then
			                    atl_dcs := atl_dcs + 1;
			            elsif (del_mar = 1) then
			                    delete_flag := delete_flag + 1;
			            end if;
                                
				    if (last_flt_id_of_pax != flt_id_of_pax) then
					    last_flt_id_of_pax  := flt_id_of_pax;
					    flt_id_count := flt_id_count + 1;
				    end if;
			         end if;
			end loop;

			close c2;
		end if;

	elsif (sno != 0 and input_fid != 0) then		
		if (tag2b != '-') then
			open c3;

			loop
				fetch
					c3
				into
					tpsn, 
					trtag,
					tfrom_uld_no,
					tflt_id_of_pax,
					tpax_id,
					tbooked_flight_id,
					brs_flag,
					dcs_flag,
					del_mar,
					ldd_sts;

				exit when c3%notfound;
	
				if ( codeshare_fid = 0 or codeshare_fid = tbooked_flight_id ) then
					bag_count := bag_count + 1;

				        if ( ldd_sts = 'Y' ) then
				                ldd_count := ldd_count + 1;

                                                from_uld_no := tfrom_uld_no;
                                                flt_id_of_pax := tflt_id_of_pax;
                                                rtag := trtag;
	 			  	        /* Check for > 1 Pax with same sequence No. */
					        pax_id := tpax_id;
					        if ( tpax_id != last_pax_id ) then
						        match_pax := match_pax + 1;
						        last_pax_id := tpax_id;
					        end if;
				        end if;

				end if;

			        if ( ldd_sts = 'Y' ) then
			            if (brs_flag = 'N') then
			                    atl_brs := atl_brs + 1;
			            elsif (dcs_flag = 'N') then
			                    atl_dcs := atl_dcs + 1;
			            elsif (del_mar = 1) then
			                    delete_flag := delete_flag + 1;
			            end if;

				    if (last_flt_id_of_pax != flt_id_of_pax) then
					    last_flt_id_of_pax  := flt_id_of_pax;
					    flt_id_count := flt_id_count + 1;
				    end if;
			        end if;
			end loop;
			close c3;
		else
			open c4;

			loop
				fetch
					c4
				into
					tpsn, 
					trtag,
					tfrom_uld_no,
					tflt_id_of_pax,
					tpax_id,
					tbooked_flight_id,
					brs_flag,
					dcs_flag,
					del_mar,
					ldd_sts;

				exit when c4%notfound;
	
				if ( codeshare_fid = 0 or codeshare_fid = tbooked_flight_id ) then
					bag_count := bag_count + 1;

				        if ( ldd_sts = 'Y' ) then
				                ldd_count := ldd_count + 1;

                                                from_uld_no := tfrom_uld_no;
                                                flt_id_of_pax := tflt_id_of_pax;
                                                rtag := trtag;
					        /* Check for > 1 Pax with same sequence No. */
					        pax_id := tpax_id;
					        if ( tpax_id != last_pax_id ) then
						        match_pax := match_pax + 1;
						        last_pax_id := tpax_id;
					        end if;
				        end if;

				end if;

			        if ( ldd_sts = 'Y' ) then
			            if (brs_flag = 'N') then
			                    atl_brs := atl_brs + 1;
			            elsif (dcs_flag = 'N') then
			                    atl_dcs := atl_dcs + 1;
			            elsif (del_mar = 1) then
			                    delete_flag := delete_flag + 1;
			            end if;

				    if (last_flt_id_of_pax != flt_id_of_pax) then
					    last_flt_id_of_pax  := flt_id_of_pax;
					    flt_id_count := flt_id_count + 1;
				    end if;
			        end if;
			end loop;
			close c4;
		end if;

	elsif (ufid > 0) then

		if ( tag2b != '-' ) then

			open c7;

			loop
				fetch
					c7
				into
					trtag,
					tfrom_uld_no,
					tpax_id,
					tflt_id_of_pax_no_sno,
					tbooked_flight_id,
					brs_flag,
					dcs_flag,
					del_mar,
					ldd_sts;

				exit when c7%notfound;
	
				if ( codeshare_fid = 0 or codeshare_fid = tbooked_flight_id ) then
					bag_count := bag_count + 1;
				end if;

			        if ( ldd_sts = 'Y' ) then
			            ldd_count := ldd_count + 1;
                                    from_uld_no := tfrom_uld_no;
                                    flt_id_of_pax_no_sno := tflt_id_of_pax_no_sno;
                                    rtag := trtag;
                                    pax_id := tpax_id;

				    if ( flt_id_of_pax_no_sno != last_flt_id_of_pax_no_sno ) then
				            fidcount := fidcount + 1;
				    end if;

			            if (brs_flag = 'N') then
			                    atl_brs := atl_brs + 1;
			            elsif (dcs_flag = 'N') then
			                    atl_dcs := atl_dcs + 1;
			            elsif (del_mar = 1) then
			                    delete_flag := delete_flag + 1;
			 	    end if;

				    if ( last_flt_id_of_pax_no_sno = 0 ) then
					    last_flt_id_of_pax_no_sno := flt_id_of_pax_no_sno;
				    end if;
			        end if;

			end loop;

			close c7;

		else

			open c8;

			loop
				fetch
					c8
				into
					trtag,
					tfrom_uld_no,
					tpax_id,
					tflt_id_of_pax_no_sno,
					tbooked_flight_id,
					brs_flag,
					dcs_flag,
					del_mar,
					ldd_sts;

				exit when c8%notfound;
	

				if ( codeshare_fid = 0 or codeshare_fid = tbooked_flight_id ) then
					bag_count := bag_count + 1;
				end if;

			        if ( ldd_sts = 'Y' ) then
			            ldd_count := ldd_count + 1;
                                    from_uld_no := tfrom_uld_no;
                                    flt_id_of_pax_no_sno := tflt_id_of_pax_no_sno;
                                    rtag := trtag;
                                    pax_id := tpax_id;

				    if ( flt_id_of_pax_no_sno != last_flt_id_of_pax_no_sno ) then
					    fidcount := fidcount + 1;
				    end if;

 			            if (brs_flag = 'N') then
			                    atl_brs := atl_brs + 1;
			            elsif (dcs_flag = 'N') then
 			                    atl_dcs := atl_dcs + 1;
			            elsif (del_mar = 1) then
			                    delete_flag := delete_flag + 1;
			            end if;

				    if ( last_flt_id_of_pax_no_sno = 0 ) then
					    last_flt_id_of_pax_no_sno := flt_id_of_pax_no_sno;
				    end if;
			        end if;

			end loop;

			close c8;

		end if;
			
	elsif (tag2b != '-') then
		open c5;

		loop
			fetch
				c5
			into
				trtag,
				tfrom_uld_no,
				tpax_id,
				tflt_id_of_pax_no_sno,
				tbooked_flight_id,
				brs_flag,
				dcs_flag,
				del_mar,
				ldd_sts;

			exit when c5%notfound;

			if ( codeshare_fid = 0 or codeshare_fid = tbooked_flight_id ) then
			        bag_count := bag_count + 1;
			end if;

			if ( ldd_sts = 'Y' ) then
			    ldd_count := ldd_count + 1;
                            from_uld_no := tfrom_uld_no;
                            flt_id_of_pax_no_sno := tflt_id_of_pax_no_sno;
                            rtag := trtag;
                            pax_id := tpax_id;

			    if (brs_flag = 'N') then
			            atl_brs := atl_brs + 1;
			    elsif (dcs_flag = 'N') then
			            atl_dcs := atl_dcs + 1;
			    elsif (del_mar = 1) then
			            delete_flag := delete_flag + 1;
			    end if;

			    if (flt_id_of_pax_no_sno != last_flt_id_of_pax_no_sno) then
				    fidcount := fidcount + 1;
			    end if;

			    if (last_flt_id_of_pax_no_sno = 0) then
				    last_flt_id_of_pax_no_sno := flt_id_of_pax_no_sno;
		            end if;
			end if;

		end loop;

		close c5;

	else

		open c6;

		loop
			fetch
				c6
			into
				trtag,
				tfrom_uld_no,
				tpax_id,
				tflt_id_of_pax_no_sno,
				tbooked_flight_id,
				brs_flag,
				dcs_flag,
				del_mar,
				ldd_sts;

			exit when c6%notfound;


			if ( codeshare_fid = 0 or codeshare_fid = tbooked_flight_id ) then
			        bag_count := bag_count + 1;
			end if;


			if ( ldd_sts = 'Y' ) then
			    ldd_count := ldd_count + 1;
                            from_uld_no := tfrom_uld_no;
                            flt_id_of_pax_no_sno := tflt_id_of_pax_no_sno;
                            rtag := trtag;
                            pax_id := tpax_id;

			    if (brs_flag = 'N') then
			            atl_brs := atl_brs + 1;
			    elsif (dcs_flag = 'N') then
			            atl_dcs := atl_dcs + 1;
			    elsif (del_mar = 1) then
			            delete_flag := delete_flag + 1;
			    end if;

			    if (flt_id_of_pax_no_sno != last_flt_id_of_pax_no_sno) then
				    fidcount := fidcount + 1;
			    end if;

			    if (last_flt_id_of_pax_no_sno = 0) then
				    last_flt_id_of_pax_no_sno := flt_id_of_pax_no_sno;
			    end if;
			end if;

		end loop;
		close c6;

	end if;

	/* now you can do your checks */
	if (bag_count = 0) then
		retval := -1002; /* Unknown Bag */

	elsif ( ldd_count = 0 ) then
	        retval := -1012; /* Bag not Loaded */
		
	elsif ( atl_brs >= 1) then
	        retval := -1005;
	        
	elsif ( atl_dcs >= 1) then
	        retval := -1004;
	
	elsif ( delete_flag >= 1) then
	        retval := -1020;

	elsif ( ldd_count > 1 and match_pax > 1 and codeshare_fid = 0 ) then

		/* retval := -1039; */ /* Duplicate sequence No (resolvable) */
		/* Scanner wants unresolvable duplicate here */
		retval := -1021; /* unresolvable duplicate tag */

	elsif (ldd_count > 1 and sno = 0) then

		if ( fidcount > 1 ) then
			retval := -1003; /* duplicate tag */
		else
			retval := -1021; /* unresolvable duplicate tag */
		end if;

	elsif (flt_id_count > 1) then

		retval := -138;

	elsif (sno = 0) then
		/* I need to get the flight_id for the flights that didn't pass 
			 a sequence number */

		select
			count(hold_no)
		into
			is_from_hold
		from
			holds
		where
			hold_no = from_uld_no;

		if (is_from_hold = 0) then
			/* Is not from a hold, and had no seq no. */
			select
				flight_id
			into
				flt_id_of_pax
			from
				ulds
			where
				uld_no    = from_uld_no and
				pd_marker = 'N';
		else
			/* Is from a hold, and had no seq no. */
			flt_id_of_pax := flt_id_of_pax_no_sno;

		end if;

	end if;

	/* get flt id to return */
	if (is_to_hold = 0) then
		if (sno = 0) then
			/* It's to a ULD, and we had no seq no */
			output_fid := flt_id_of_pax;
		else
			/* It's to a ULD, and we did have a seq no */
			select
				flight_id
			into
				output_fid
			from
				ulds
			where
				uld_no    = to_uld_no and
				pd_marker = 'N';
		end if;
	else
		/* Is to a hold */
		output_fid := input_fid;
	end if;

	if (retval = 0 and flt_id_of_pax != output_fid) then
		retval := -1006;
	end if;

end check_bag_transfer;
/


