/*****************************************************************************
*
* Filename         - $Workfile:   lba_pkg.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: triggers for flight table
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1999
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   lba_pkg.sql  $  $Revision:   1.2  $
*
* 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:   /pvcsarch/db_ora/db/source/live/procedures/lba_pkg.svl  $
* 
*    Rev 1.2   15 Jun 2000 12:17:28   tagtrak
*  
* 
*    Rev 1.1   31 Jan 2000 14:49:30   tagtrak
*  
* 
*    Rev 1.0.1.0   07 May 1999 17:14:20   tagtrak
* OR:
* CR: 13934
* REV:
* Oracle 8.
* FASL gate width change to Oracle char(5).
* LBA interface (excluding archive and backup).
* Clean build fixes from DB v1.0.0 (arch schema onward_flight &flight_dests).
* Change of expected FASL flight_no format.
* 
* Peter Dawson, 7th May 1999
* 
* 
*
****************************************************************************/


PROMPT create or replace package lba_pkg

create or replace package lba_pkg
is

	/*
	 * Accept LBA board/deboard/deboardall data, 
	 * and use it to update paxs' boarding status.
	 */
	procedure handle_lba_info_inserts (
		incoming       in out   lba_info%rowtype,
		retcode        in out   number
	);

end lba_pkg;
/

PROMPT create or replace package body lba_pkg

create or replace package body lba_pkg
as

/*
* Note that:
*   1. incoming.seq_no = -1 indicates null value.
*   2. incoming.seat = '-' indicates null value.
*/
procedure handle_lba_info_inserts (
	incoming       in out  lba_info%rowtype,
	retcode        in out  number
)
is

	v_board_match_using  carrier_lookup.board_match_using%type ;
	v_dcs_boarding       carrier_lookup.dcs_boarding%type ;
	v_pax_rec            pax%rowtype ;
  pinterface_id        flight_info.interface_id%type;
  depcode              site_info.airport_code%type;  
  ret                  number(10);
  psiteid site_info.site_id%type;
begin
   
  pinterface_id  := flightinfo.lba_interface;
  depcode := trim (config.get_value ('FIDS_IF_' || pinterface_id));
  psiteid := multi_site.get_site_id(depcode);

	retcode := 0 ;      /* Initialise */

	dbms_output.put_line('incoming.transaction_id = '||incoming.transaction_id);
    dbms_output.put_line('incoming.flight_no      = '||incoming.flight_no);
    dbms_output.put_line('incoming.std_date       = '||incoming.std_date);
    dbms_output.put_line('incoming.transaction    = '||incoming.transaction);
    dbms_output.put_line('incoming.seq_no         = '||incoming.seq_no);
    dbms_output.put_line('incoming.seat           = '||incoming.seat);
    dbms_output.put_line('incoming.pax_name       = '||incoming.pax_name);
    dbms_output.put_line('incoming.flight_id      = '||incoming.flight_id);

/*
	* Check for unknown flight.
	*/
	flight_id2 (
		incoming.flight_no,
		to_char(incoming.std_date, 'DDMON'),
    psiteid,
		incoming.flight_id );

	dbms_output.put_line('incoming.flight_id      = '||incoming.flight_id);

	if (incoming.flight_id = -1 ) then   /* { */
		retcode := -139 ;     /* Unknown flight */
	elsif ( incoming.flight_id = -2 ) then
		retcode := -138 ;     /* Duplicate flight */
	end if ;             /* } */

	/*
	* Check that DCS is not boarding this flight and
	* assume that it is if the lookup fails.
	*/
	if ( retcode = 0 ) then   /* { */
		begin
			select
				dcs_boarding,
				board_match_using
			into
				v_dcs_boarding,
				v_board_match_using
			from
				carrier_lookup
			where
				alpha_code = substr(incoming.flight_no, 1, 3) ;

			exception
				when no_data_found then
					v_dcs_boarding := 'Y' ;
				when too_many_rows then
					v_dcs_boarding := 'Y' ;
		end ;

		if ( v_dcs_boarding != 'N' ) then /* { */
			retcode := -1060 ;  /* DCS is boarding this flight */
		end if ;   /* } */

	end if ;    /* } */

	/*
	* Process BOARD / DEBOARD transactions.
	*/
	if ( retcode = 0 AND 
		( incoming.transaction = 'BOARD' 
		  OR 
		  incoming.transaction = 'DEBOARD' )) then   /* { */

		/*
		* Check we have the data we need to match the pax according to the matching method
		* (sequence no. in this case).
		*/
		if ( incoming.seq_no = -1 AND v_board_match_using = 'SEQUENCE_NO' ) then /* { */
			retcode := -600 ;   /* No sequence number */

		/*
		* Process lba_info insert using sequence no. to match the pax.
		*
		* Check the pax exists and get some of his data for later use.
		*/
		elsif ( v_board_match_using = 'SEQUENCE_NO' ) then
			begin
				select
					pax_id,
					nvl( pax_seat_no, '-' ),
					pax_status
				into
					v_pax_rec.pax_id,
					v_pax_rec.pax_seat_no,
					v_pax_rec.pax_status
				from
					pax
				where
					pax_dcs_sequence_no = incoming.seq_no and
					flight_id = incoming.flight_id and
					delete_marker = 0;
				exception
					when no_data_found then
						retcode := -1056 ;  /* Unknown sequence number */
					when too_many_rows then
						retcode := -1054 ;  /* Duplicate sequence number */
			end ;

			dbms_output.put_line('v_pax_rec.pax_id    = '||v_pax_rec.pax_id);
			dbms_output.put_line('v_pax_rec.pax_seat_no    = '||v_pax_rec.pax_seat_no);
			dbms_output.put_line('v_pax_rec.pax_status    = '||v_pax_rec.pax_status);

			/*
			* Refuse to board/deboard pax unless his status is 'C' or 'B'.
			*/
			if ( v_pax_rec.pax_status not in ('C','B') ) then   /* { */
				retcode := -1048 ;   /* Pax not checked in */
			end if ;   /* } */

			/*
			* Now board/deboard the pax for the different cases.
			*
			* If we have found a matching pax using the sequence no. but the database's
			* seat no. doesn't match LBA's (if supplied) then flag a warning and continue.
			* Note that if :new.seat is null then incoming.seat will be passed in as '-'.
			*/
			if ( retcode = 0 ) then   /* { */

				if ( incoming.seat != '-' AND 
					 v_pax_rec.pax_seat_no != '-' AND 
					 incoming.seat != v_pax_rec.pax_seat_no ) then   /* { */

						retcode := -1052 ;  /* Warning - seq_no matches and not seat */

				end if ; /* } */

				/*
				* Now process boarding transactions:-
				* If the pax is already boarded then give a warning but do it anyway,
				* giving precedence to warnings raised previously.
				*/
				if ( incoming.transaction = 'BOARD' ) then   /* { */

					if ( v_pax_rec.pax_status = 'B' and retcode >= 0 ) then   /* { */

						retcode := -1058 ;  /* Warning - passenger boarded twice */

					elsif ( v_pax_rec.pax_status = 'C' ) then

						update
							pax
						set
							pax_status = 'B'
						where
							pax_id = v_pax_rec.pax_id ;
					
					end if ;   /* } */

				end if ;   /* } */

				/*
				* Now process deboarding transactions:-
				* If the pax is already deboarded then give a warning but do it anyway,
				* giving precedence to warnings raised previously.
				*/
				if ( incoming.transaction = 'DEBOARD' ) then   /* { */

					if ( v_pax_rec.pax_status = 'C' and retcode >= 0 ) then   /* { */

						retcode := -1059 ;  /* Warning - deboarded passenger is not boarded */

					elsif ( v_pax_rec.pax_status = 'B' ) then

						update
							pax
						set
							pax_status = 'C'
						where
							pax_id = v_pax_rec.pax_id ;
					
					end if ;   /* } */

				end if ;   /* } end of if ( incoming.transaction = 'DEBOARD' ) */
				
			end if ;  /* } end of if ( retcode = 0 ) */

		end if ;  /* } end of if ( incoming.seq_no = -1 AND v_board_match_using = 'SEQUENCE_NO' ) */

		/*
		* Check we have the data we need to match the pax according to the matching method
		* (seat no. in this case).
		*/
		if ( incoming.seat = '-' AND v_board_match_using = 'SEAT_NO' ) then /* { */
			retcode := -1061 ;   /* No seat number */

		elsif ( v_board_match_using = 'SEAT_NO' ) then
			begin
				select
					pax_id,
					nvl(pax_dcs_sequence_no, '-'),
					pax_status
				into
					v_pax_rec.pax_id,
					v_pax_rec.pax_dcs_sequence_no,
					v_pax_rec.pax_status
				from
					pax
				where
					pax_seat_no = incoming.seat and
					flight_id = incoming.flight_id and
					delete_marker = 0;
				exception
					when no_data_found then
						retcode := -1057 ;  /* Unknown seat number */
					when too_many_rows then
						retcode := -1055 ;  /* Duplicate seat number */
			end ;

			dbms_output.put_line('v_pax_rec.pax_id    = '||v_pax_rec.pax_id);
			dbms_output.put_line('v_pax_rec.pax_dcs_sequence_no    = '||v_pax_rec.pax_dcs_sequence_no);
			dbms_output.put_line('v_pax_rec.pax_status    = '||v_pax_rec.pax_status);

			/*
			* Refuse to board pax unless his status is 'C' or 'B'.
			*/
			if ( v_pax_rec.pax_status not in ('C','B') ) then   /* { */
				retcode := -1048 ;   /* Pax not checked in */
			end if ;   /* } */

			/*
			* Now board/deboard the pax for the different cases.
			*
			* If we have found a matching pax using the seat no. but the database's
			* sequence no. doesn't match LBA's (if supplied) then flag a warning and continue.
			* Note that if :new.seq_no is null then incoming.seq_no will be passed in as -1.
			*/
			if ( retcode = 0 ) then   /* { */
				if ( incoming.seq_no != -1 AND v_pax_rec.pax_dcs_sequence_no != '-' AND 
				     incoming.seq_no != to_number( v_pax_rec.pax_dcs_sequence_no ) ) then   /* { */

					retcode := -1053 ;  /* Warning - seat matches and not seq_no */

				end if ;   /* } */

				/*
				* Now process boarding transactions:-
				* If the pax is already boarded then give a warning but do it anyway,
				* giving precedence to warnings raised previously.
				*/
				if ( incoming.transaction = 'BOARD' ) then   /* { */

					if ( v_pax_rec.pax_status = 'B' AND retcode >= 0 ) then   /* { */

						retcode := -1058 ;  /* Warning - passenger boarded twice */

					elsif ( v_pax_rec.pax_status = 'C' ) then

					update
						pax
					set
						pax_status = 'B'
					where
						pax_id = v_pax_rec.pax_id ;

					end if ;   /* } */
				end if ;   /* } */
						
				/*
				* Now process deboard transactions:-
				* If the pax is already deboarded then give a warning but do it anyway,
				* giving precedence to warnings raised previously.
				*/
				if ( incoming.transaction = 'DEBOARD' ) then   /* { */

					if ( v_pax_rec.pax_status = 'C' and retcode >= 0 ) then   /* { */

						retcode := -1059 ;  /* Warning - deboarded passenger is not boarded */

					elsif ( v_pax_rec.pax_status = 'B' ) then

						update
							pax
						set
							pax_status = 'C'
						where
							pax_id = v_pax_rec.pax_id ;
					
					end if ;   /* } */

				end if ;   /* } */

			end if ;  /* } end of boarding/deboarding the pax using seat no. */

		end if ;  /* } end of processing using seat number */

	end if ;    /* }  end of processing BOARD / DEBOARD transactions */

	/*
	* Process DEBOARD_ALL transactions
	*/
	if ( retcode = 0 AND incoming.transaction = 'DEBOARD_ALL' ) then   /* { */

		update
			pax
		set
			pax_status = 'C'
		where
			pax_status = 'B' and
			flight_id = incoming.flight_id ;
					
	end if ;   /* } end of if ( retcode = 0 AND incoming.transaction = 'DEBOARD_ALL' */

end handle_lba_info_inserts;

end lba_pkg;
/
