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

create or replace package AOSS_Data as

    -- Sets the FLIGHT ID for GSA entries that have not been linked to a flight
    -- ID, but have recorded the flight no/date.
    --
    procedure Set_Missing_Flight_ID(p_flight_no flight.flight_no%type,
				    p_flight_date flight.flight_date%type,
				    p_flight_id flight.flight_id%type);

    -- Sets the PAX ID for GSA entries that have not been linked to a PAX
    --
    procedure Set_Missing_PAX(p_tag2a bag.bag_tag2a%type,
			      p_tag2b bag.bag_tag2b%type,
			      p_tag3 bag.bag_tag3%type,
			      p_pax_id bag.pax_id%type);

end AOSS_Data;
/

create or replace package body AOSS_Data is

    procedure Set_Missing_Flight_ID(p_flight_no flight.flight_no%type,
				    p_flight_date flight.flight_date%type,
				    p_flight_id flight.flight_id%type)
    as
    begin

	update bag_trace
	    set flight_id_scan = p_flight_id,
	    	flight_no = p_flight_no,
		flight_date = p_flight_date
	    where loginname = 'GSA' and flight_id_scan = 0 and
		    aoss_flight_no = p_flight_no and
		    aoss_flight_date = p_flight_date;

    end Set_Missing_Flight_ID;


    procedure Set_Missing_PAX(p_tag2a bag.bag_tag2a%type,
			      p_tag2b bag.bag_tag2b%type,
			      p_tag3 bag.bag_tag3%type,
			      p_pax_id bag.pax_id%type)
    as

	l_flight_no		flight.flight_no%type;
	l_flight_date	flight.flight_date%type;

    begin

	select f.flight_no, f.flight_date
	    into l_flight_no, l_flight_date
	    from flight f, pax p
	    where p.pax_id = p_pax_id and p.flight_id = f.flight_id;

	update bag_trace set pax_id = p_pax_id
	    where loginname = 'GSA' and pax_id = 0 and
		    (bag_tag2a = p_tag2a or bag_tag2b = p_tag2b) and
			bag_tag3 = p_tag3 and
			    flight_no = l_flight_no and
			    flight_date = l_flight_date;

    end Set_Missing_PAX;

end AOSS_Data;
/
