/*****************************************************************************
*
* Filename         - $Workfile:$
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

--
-- This added for FALCP/CA/01979
--

prompt create or replace procedure Set_PAX_VFDB

create or replace procedure Set_PAX_VFDB
(
   p_pax_id   in pax.pax_id%TYPE,
   p_flight   in flight.flight_id%TYPE,
   p_pax_vfdb in pax.vfdb%TYPE,
   p_site_id  in location.site_id%TYPE
) 
as  

    allow boolean := false;

begin

    declare
	carrier flight.carrier_alpha_code%type;
	config carrier_site_config.config_value%type;
    begin
	select f.carrier_alpha_code into carrier from flight f
	    where f.flight_id = p_flight;

	select c.config_value into config from carrier_site_config c
	    where c.alpha_code = carrier and c.site_id = p_site_id and
		    c.config_item = 'VFDB';

	if (config is not null and config='Y') then
	     allow := true;
	end if;
    exception
	when NO_DATA_FOUND then
	    null;
    end;

    if allow then
	update pax set pax.vfdb = p_pax_vfdb where pax.pax_id = p_pax_id;
    else
	update pax set pax.vfdb = 'N' where pax.pax_id = p_pax_id;
    end if;
end Set_PAX_VFDB;
/
