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

--
-- This added for FALCP/CA/02233
--

prompt create or replace procedure Set_Bag_Checkin

-- p_count will be 1 on exit if a single bag was updated.  Otherwise it
-- indicates failure and how many matches there were
--
create or replace procedure Set_Bag_Checkin
(
    p_flight_id		in  flight.flight_id%type,
    p_tag		in  char,
    p_checkin_time	in  bag.checkin_time%TYPE,
    p_checkin_loc	in  bag.checkin_location%TYPE,
    p_checkin_loc_desc	in  bag.checkin_location_desc%TYPE,
    p_count             out number
) 
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_count	number;

begin

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

    select count(*) into l_bag_count
	from bag b where b.bag_tag2b = l_tag2b and
			 b.bag_tag3 = l_tag3 and
			 b.pax_id in (select p.pax_id from pax p
					    where p.flight_id = p_flight_id);

    if l_bag_count = 1 then

	update bag b
	    set b.checkin_time = p_checkin_time,
	    	b.checkin_location = p_checkin_loc,
		b.checkin_location_desc = p_checkin_loc_desc
	    where b.bag_tag2b = l_tag2b and
		  b.bag_tag3 = l_tag3 and
		  b.pax_id in (select p.pax_id from pax p
					where p.flight_id = p_flight_id);

    end if;

    p_count := l_bag_count;

end Set_Bag_Checkin;
/
