/*****************************************************************************
*
* Filename         - $Workfile:   carrier_details.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 Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
PROMPT create or replace package carrier_details

create or replace package carrier_details  as
procedure get_pax_identifier (
	p_fltid					in     flight.flight_id%TYPE,
	p_pax_cred       out credentials_lookup.pax_cred%TYPE
);

procedure get_tagged_crew_bag (
	p_fltid                   in  flight.flight_id%TYPE,
	p_allow_tagged_bags       out char,
	p_retval                  out number
);


END  carrier_details;
/

PROMPT create or replace package BODY carrier_details

create or replace package BODY carrier_details  as
procedure get_pax_identifier (
	p_fltid					in     flight.flight_id%TYPE,
	p_pax_cred       out credentials_lookup.pax_cred%TYPE
)
as
	flt_no            flight.flight_no%TYPE;
	found       			boolean := TRUE;
	airline_code			carrier_lookup.alpha_code%TYPE;

begin

	/*
	 * Get the flight_no from the flight_id
	 */
	begin
		select
			flight_no
		into
			flt_no
		from
			flight
		where
			flight_id = p_fltid;

		exception
			when NO_DATA_FOUND then
				found := false;
	end;

	if (found) then
		/*
		 * Parse the flight number to extract the airline code for the lookup
		 */

		/* Get the first 3 characters of the p_fltno variable */
		airline_code := substr(flt_no, 1, 3);

		begin
			select
				pax_cred
			into
				p_pax_cred
			from
				carrier_lookup
			where
				rtrim(alpha_code) = rtrim(airline_code);

			exception
				when NO_DATA_FOUND then
					found := FALSE;
		end;
	end if;

	if ( not found ) then
		begin
			/* Get the default entry from the db_config table */
			select
				substr(db_value, 1, 15)
			into
				p_pax_cred
			from
				db_config
			where
				db_item = 'default_gate_paxcred';

			exception
				when NO_DATA_FOUND then
					p_pax_cred := 'SEQUENCE_NO';
		end;
	end if;

end get_pax_identifier;




/*
 * This procedure will check whether the airline supports tagged or
 * tagged crew bags.
 */
procedure get_tagged_crew_bag (
	p_fltid                   in  flight.flight_id%TYPE,
	p_allow_tagged_bags       out char,
	p_retval                  out number
)
as
	flight_num          flight.flight_no%TYPE;
	a_code              carrier_lookup.alpha_code%TYPE;
	allow_tagged_bags   carrier_lookup.tag_crew_bags%TYPE;

	retval number(10) := 0;

begin
	retval           := 0;

	/* Firstly, get the flight_no from the flight_table */
	begin
		select
			flight_no
		into
			flight_num
		from
			flight
		where
			flight_id = p_fltid;

		exception
			when NO_DATA_FOUND then
				retval := -851;
	end;

	if (retval != -851) then
		/* We have a flight_no, so we can use this to get the alpha_code */
		a_code := substr(flight_num, 1, 3);

		begin
			select
				tag_crew_bags
			into
				allow_tagged_bags
			from
				carrier_lookup
			where
				alpha_code = a_code;

			exception
				when NO_DATA_FOUND then
					retval := -851;
		end;

		if (retval = -851) then
			p_allow_tagged_bags := '-';
			p_retval              := retval;
			return;
		end if;

		p_allow_tagged_bags := allow_tagged_bags;
		p_retval              := retval;
	else
		p_allow_tagged_bags := '-';
		p_retval              := retval;
	end if;

end get_tagged_crew_bag;



END  carrier_details;
/
