/*****************************************************************************
*
* Filename         - get_bag_id.sql
*
* Project Name     - UltraTrak
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2007
*
*****************************************************************************/

create or replace package Get_Bag_ID as

    -- Returns the BAG.BAG_ID for the supplied bag tag and passenger.
    --
    -- If it cannot be resolved, zero is returned.
    --
    function From_PAX (p_tag in bag.bag_tag%type,
		       p_pax in bag.pax_id%type)
		       			return bag.bag_id%type;

    -- Returns the BAG.BAG_ID and PAX.PAX_ID for the supplied bag tag, flight
    -- and surname.
    --
    -- If it cannot be resolved, zero is returned.
    --
    procedure From_Flight_And_Surname (p_tag     in  bag.bag_tag%type,
				       p_flight  in  flight.flight_id%type,
				       p_surname in  pax.pax_surname%type,
				       p_pax_id  out pax.pax_id%type,
				       p_bag_id  out bag.bag_id%type);

end Get_Bag_ID;
/

create or replace package body Get_Bag_ID as

    function From_PAX (p_tag in bag.bag_tag%type,
		       p_pax in bag.pax_id%type) return bag.bag_id%type as  

	l_tag		bag.bag_tag%type;
	l_tag1		bag.bag_tag1%type;
	l_tag2a		bag.bag_tag2a%type;
	l_tag2b		bag.bag_tag2b%type;
	l_tag3		bag.bag_tag3%type;
	l_tag_type	number;

	l_bag_id	bag.bag_id%type;

    begin

	split_tags(p_tag, l_tag1, l_tag2a, l_tag2b, l_tag3, l_tag, l_tag_type);

	select b.bag_id into l_bag_id from bag b where
	    b.pax_id = p_pax and
	    b.bag_tag3 = l_tag3 and
	    (b.bag_tag2b = l_tag2b or b.bag_tag2a = l_tag2a);

	return l_bag_id;

    exception

	when TOO_MANY_ROWS then
	    return 0;

	when NO_DATA_FOUND then
	    return 0;

    end From_PAX;


    procedure From_Flight_And_Surname (p_tag     in  bag.bag_tag%type,
				       p_flight  in  flight.flight_id%type,
				       p_surname in  pax.pax_surname%type,
				       p_pax_id  out pax.pax_id%type,
				       p_bag_id  out bag.bag_id%type) as

	l_tag		bag.bag_tag%type;
	l_tag1		bag.bag_tag1%type;
	l_tag2a		bag.bag_tag2a%type;
	l_tag2b		bag.bag_tag2b%type;
	l_tag3		bag.bag_tag3%type;
	l_tag_type	number;

    begin

	p_bag_id := 0;
	p_pax_id := 0;

	split_tags(p_tag, l_tag1, l_tag2a, l_tag2b, l_tag3, l_tag, l_tag_type);

	select b.bag_id, p.pax_id into p_bag_id, p_pax_id
		from bag b, pax p where
		    b.pax_id = p.pax_id and
		    p.flight_id = p_flight and
		    ((p.pax_surname is null and p_surname is null) or
			(p.pax_surname = p_surname)) and
		    b.bag_tag3 = l_tag3 and
		    (b.bag_tag2b = l_tag2b or b.bag_tag2a = l_tag2a);

    exception

	when TOO_MANY_ROWS then
	    p_bag_id := 0;
	    p_pax_id := 0;


	when NO_DATA_FOUND then
	    p_bag_id := 0;
	    p_pax_id := 0;

    end From_Flight_And_Surname;

end Get_Bag_ID;
/
