/*****************************************************************************
*
* Filename         - $Workfile:   grant_contract_access.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         - grant_contract_access stored procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   grant_contract_access.sql  $  $Revision:   1.4  $
*
* 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/grant_contract_access.svl  $
* 
*    Rev 1.4   15 Jun 2000 12:17:28   tagtrak
*  
* 
*    Rev 1.3   06 Jun 2000 09:48:14   tagtrak
*  
* 
*    Rev 1.2   20 Sep 1999 14:41:24   tagtrak
*  
* 
*    Rev 1.1   02 Apr 1998 19:36:32   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.0   26 Feb 1998 10:21:26   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
*
****************************************************************************/

/*
 * grant_contract_access - flight access for contracted agents.
 * This procedure will select the contracted agents for the given
 * flight and add flight_access record for each of them.
 */

PROMPT create or replace procedure grant_contract_access 

create or replace procedure grant_contract_access (
	fid            in  flight.flight_id%type,
	fno            in  flight.flight_no%type,
	fdt            in  flight.flight_date%type,
  psiteid        in  flight.site_id%type,
	carrier_alpha  in  carrier_lookup.alpha_code%type
)

as

fnumber       number;
l_flight_type flight.flight_type%type;

/*
		select 
			agent_code
		from
			contracted_agents
		where
			airline_alpha_code = carrier_alpha;
*/

cursor agents_this_flight is
select
ca.agent_code, ca.flight_type
from carrier_subgroup cs , contracted_agents ca,   agents a
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 ca.agent_code         = a.agent_code
  and a.site_id             = psiteid
  and cs.ALPHA_CODE     = carrier_alpha
  and cs.MIN_FLIGHT_NO <= fnumber and cs.MAX_FLIGHT_NO >= fnumber
 union
 select
  ca.AGENT_CODE, ca.flight_type
 from carrier_lookup cl,contracted_agents ca, agents a
 where ca.MIN_FLIGHT_NO = 0
  and ca.MAX_FLIGHT_NO  = 9999
  and a.site_id         = psiteid
  and ca.agent_code     = a.agent_code
  and ca.AIRLINE_ALPHA_CODE = cl.alpha_code
  and ALPHA_CODE = carrier_alpha  and
  not exists (select 1 from contracted_agents cag,
  carrier_subgroup cs , agents ag
  where cag.airline_alpha_code = carrier_alpha
  and   ag.site_id             = psiteid
  and cag.agent_code           = a.agent_code  
  and cag.MIN_FLIGHT_NO<= fnumber and cag.MAX_FLIGHT_NO >= fnumber
  and cag.MIN_FLIGHT_NO =  cs.MIN_FLIGHT_NO
  and cag.MAX_FLIGHT_NO =  cs.MAX_FLIGHT_NO
  )
  ;

begin


	/*
	 * For each contracted agent for the carrier, add
	 * flight_access and stats_access records.
	 */
	fnumber := to_number(substr(fno,4,4));

	SELECT flight_type
        INTO   l_flight_type
        FROM   flight
        WHERE  flight_id = fid;

	for agent in agents_this_flight 
        loop
                if ( agent.flight_type = 'B' OR agent.flight_type = l_flight_type )
                then
		    add_flt_access( fid, agent.agent_code, NULL );

		    add_stats_access( agent.agent_code, NULL, fno, fdt );
                end if;

	end loop;
end grant_contract_access;
/
