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

create or replace package Gateway_Data as

    procedure Add_BPM (p_bsm_data   in  bsm_log.bsm_log%type,
		       p_depcode    in  timezone.airport_code%type,
		       p_bsmid      out bsm_log.bsm_id%type,
		       p_mtype      in  bsm_log.msg_type%type,
		       p_rx_tx_flag in  bsm_log.rx_tx%type);

    procedure Add_BPM_Data (p_bsm_id      in out bsm_data.bsm_id%type,
			    p_bag_tag     in     bsm_data.bag_tag%type,
			    p_pax_surname in     bsm_data.pax_surname%type,
			    p_pax_id      in     bsm_data.pax_id%type);

    procedure Add_BSM (p_bsm_data   in  bsm_log.bsm_log%type,
		       p_depcode    in  timezone.airport_code%type,
		       p_bsmid      out bsm_log.bsm_id%type,
		       p_mtype      in  bsm_log.msg_type%type,
		       p_rx_tx_flag in  bsm_log.rx_tx%type);

    procedure Add_BSM_Data (p_bsm_id      in out bsm_data.bsm_id%type,
			    p_bag_tag     in     bsm_data.bag_tag%type,
			    p_pax_surname in     bsm_data.pax_surname%type);

    procedure Set_BSM_Flight (p_bsm_id   in bsm_log.bsm_id%type,
			      p_flt_id   in bsm_log.flight_id%type);

end Gateway_Data;
/

create or replace package body Gateway_Data as

    procedure Add_BPM (p_bsm_data   in  bsm_log.bsm_log%type,
		       p_depcode    in  timezone.airport_code%type,
		       p_bsmid      out bsm_log.bsm_id%type,
		       p_mtype      in  bsm_log.msg_type%type,
		       p_rx_tx_flag in  bsm_log.rx_tx%type) as

    	l_fno flight.flight_no%type;
    	l_fdate flight.flight_date%type;

    begin

	select flight_no, flight_date into l_fno, l_fdate
	    from flight where flight_id = -1;

	select bsm_gen.nextval into p_bsmid from dual;

	insert into bsm_log 
	    (
		bsm_id,
		bsm_log,
		log_time, 
		msg_type,
		rx_tx,
		flight_id,
		flight_no,
		flight_date
	    ) 
	values 
	    (
		p_bsmid,
		p_bsm_data,
		SYSDATE + get_offset.airport_code(p_depcode),
		p_mtype,
		p_rx_tx_flag,
		-1,
		l_fno,
		l_fdate
	    );

    end Add_BPM;


    procedure Add_BPM_Data (p_bsm_id      in out bsm_data.bsm_id%type,
			    p_bag_tag     in     bsm_data.bag_tag%type,
			    p_pax_surname in     bsm_data.pax_surname%type,
			    p_pax_id      in     bsm_data.pax_id%type) as
    	l_bag_id bsm_data.bag_id%type;
    begin

	/* inserts the bpm_data obtained about each bag from the gateway */

	l_bag_id := Get_Bag_ID.From_Pax(p_bag_tag, p_pax_id);

	if l_bag_id = 0 then
	    l_bag_id := null;
	end if;

	insert into bsm_data
	    (
		BSM_ID,
		BAG_TAG,
		PAX_SURNAME,
		BAG_ID,
		PAX_ID
	    )
	values
	    (
		p_bsm_id,
		p_bag_tag,
		p_pax_surname,
		l_bag_id,
		p_pax_id
	    );

    end Add_BPM_Data;


    procedure Add_BSM (p_bsm_data   in  bsm_log.bsm_log%type,
		       p_depcode    in  timezone.airport_code%type,
		       p_bsmid      out bsm_log.bsm_id%type,
		       p_mtype      in  bsm_log.msg_type%type,
		       p_rx_tx_flag in  bsm_log.rx_tx%type) as

    	l_fno flight.flight_no%type;
    	l_fdate flight.flight_date%type;

    begin

	select flight_no, flight_date into l_fno, l_fdate
	    from flight where flight_id = -1;

	select bsm_gen.nextval into p_bsmid from dual;

	insert into bsm_log 
	    (
		bsm_id,
		bsm_log,
		log_time, 
		msg_type,
		rx_tx,
		flight_id,
		flight_no,
		flight_date
	    ) 
	values 
	    (
		p_bsmid,
		p_bsm_data,
		SYSDATE + get_offset.airport_code(p_depcode),
		p_mtype,
		p_rx_tx_flag,
		-1,
		l_fno,
		l_fdate
	    );

    end Add_BSM;


    procedure Add_BSM_Data (p_bsm_id      in out bsm_data.bsm_id%type,
			    p_bag_tag     in     bsm_data.bag_tag%type,
			    p_pax_surname in     bsm_data.pax_surname%type) as
    begin

	/* inserts the bsm_data obtained about each bag from the gateway */

	insert into bsm_data
	    (
		BSM_ID,
		BAG_TAG,
		PAX_SURNAME
	    )
	values
	    (
		p_bsm_id,
		p_bag_tag,
		p_pax_surname
	    );

    end Add_BSM_Data;


    procedure Set_BSM_Flight (p_bsm_id   in bsm_log.bsm_id%type,
			      p_flt_id   in bsm_log.flight_id%type) as

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

    begin

    	select flight_no, flight_date into l_flight_no, l_flight_date
	    from flight where flight_id = p_flt_id;

    	update bsm_log bsm
	    set bsm.flight_id = p_flt_id,
	    	bsm.flight_no = l_flight_no,
		bsm.flight_date = l_flight_date where bsm.bsm_id = p_bsm_id;

    end Set_BSM_Flight;

end Gateway_Data;
/
