PROMPT create or replace procedure valid_agent

 create or replace procedure valid_agent (
 	ag_code in char,
 	flt_no in char,
 	return_val out number)
 as
 begin



	/* check that an agent code is valid for a carrier and flight number*/
	/* by selecting the agent codes for a flight number */

	/* 	first look to see if the flight number's numeric part  */
	/*	in in a carrier subgroup  */

	/*	by using the exists clause to find if there is not */
	/*	a subgroup for this flight number */
	/*	get the agents for all flight ie 0 to 9999 */

	return_val := 0;

	/* agent code has to be  char(5)  not varchar2 */

	select 1 
	into return_val
	from dual
	where substr( (ag_code||'     '),1,5) in 
	(
 		select ca.agent_code
 		from carrier_subgroup cs , contracted_agents ca
 		where ca.MIN_FLIGHT_NO = cs.MIN_FLIGHT_NO
			and ca.MAX_FLIGHT_NO = cs.MAX_FLIGHT_NO
			and ca.AIRLINE_ALPHA_CODE = cs.alpha_code
			and cs.ALPHA_CODE = substr(flt_no,1,3)
			and cs.MIN_FLIGHT_NO <= substr(flt_no,4,4) 
			and cs.MAX_FLIGHT_NO >= substr(flt_no,4,4)
 		union
 		select ca.AGENT_CODE
 		from carrier_lookup cl,contracted_agents ca
 		where ca.MIN_FLIGHT_NO = 0
			and ca.MAX_FLIGHT_NO = 9999
			and ca.AIRLINE_ALPHA_CODE = cl.alpha_code
			and ALPHA_CODE = substr(flt_no,1,3)  
			and not exists 
				(select 1 
				from contracted_agents cag, carrier_subgroup cs
				where cag.airline_alpha_code = substr(flt_no,1,3)
					and cag.MIN_FLIGHT_NO <= substr(flt_no,4,4) 
					and cag.MAX_FLIGHT_NO >= substr(flt_no,4,4)
					and cag.MIN_FLIGHT_NO =  cs.MIN_FLIGHT_NO
					and cag.MAX_FLIGHT_NO =  cs.MAX_FLIGHT_NO
				)
	) ;
	exception 
	when NO_DATA_FOUND then 
		return_val :=0;

	end;
/
