/*****************************************************************************
*
* Filename         - get_bpm_status
*
*                     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         - 
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
PROMPT create or replace procedure get_bpm_status

-- This procedure accepts the DCS, flight ID and requested BPM type and decides
-- via the DCS settings and the settings for the flight's carrier as to whether
-- a BPM should be generated.
--
-- Arguments:
--    p_process         - DCS for the bag.
--    p_unit            - DCS for the bag.
--    p_dcsid           - DCS for the bag.
--    p_subunit         - DCS for the bag.
--    p_bag_exception   - The excetpion identifier (if any) for the bag.
--    p_flight_id       - The flight the bag belongs to.
--    p_bpm_type        - The BPM type that is to be generated.
--    p_bpm_status      - An out paremeter -- 'Y' is the BPM is to be generated.
--
create or replace procedure get_bpm_status
(
    p_process		in  system_cfg.process_name%TYPE,
    p_unit		in  system_cfg.unit_name%TYPE,
    p_dcsid		in  system_cfg.dcsid%TYPE,
    p_subunit		in  system_cfg.subunit_name%TYPE,
    p_bag_exception	in  bag.bag_exception%TYPE,
    p_flight_id		in  flight.flight_id%TYPE,
    p_bpm_type		in  varchar2,
    p_bpm_status	out char
)
as

    l_bag_exception     bag.bag_exception%TYPE := p_bag_exception;
    l_global_bpm_status	system_cfg.bpm_status%type;
    l_carrier		carrier_lookup%rowtype;
    l_bpm_id		bpm_types.id%type;
    l_group_id		bpm_groups.id%type;
    l_exceptions	bpm_groups.exceptions%type;

    cursor c_bpm_search(p_id bpm_groups.bpm_types%type) is
	select id, exceptions from bpm_groups
	    where bpm_types like '%' || p_id || '%';

begin

    -- Set initial state
    --
    p_bpm_status := 'N';

    -- Get global setting for this DCS
    --
    select bpm_status into l_global_bpm_status from system_cfg
	where process_name = p_process and
	      unit_name    = p_unit    and
	      dcsid        = p_dcsid   and
	      subunit_name = p_subunit;

    -- Only carry on if this DCS support BPM at all
    --
    if l_global_bpm_status is not null and l_global_bpm_status = 'Y' then

	-- Fetch the carrier data
	--
    	select * into l_carrier from carrier_lookup
	    where alpha_code = (select carrier_alpha_code
				    from flight where flight_id = p_flight_id);

	-- Check to see if this exception is really a conection indicator
	--
	declare
	    term_count number;
	begin
	    select count(*) into term_count
	    	from connection_configuration
		where connect_type = l_bag_exception;

	    if term_count > 0 then
		l_bag_exception := null;
	    end if;
	end;

	-- Get the BPM types ID
	--
	select id into l_bpm_id from bpm_types where bpm_type = p_bpm_type;

	-- See if this carrier supports the matching groups
	--
	if l_carrier.bpm_groups is not null then

	    for l_group_rec in c_bpm_search(l_bpm_id) loop

		if instr(l_carrier.bpm_groups,l_group_rec.id) > 0 then

		    -- If it does, check if we need to worry about exception
		    -- types.
		    --
		    if l_bag_exception is not null and
			    length(l_bag_exception)>0 and
				l_group_rec.exceptions is not null and
				    l_group_rec.exceptions = 'Y' then

			declare
			    l_excid bag_exception_types.bag_excep_id%type;
			begin
			    select bag_excep_id into l_excid
				from bag_exception_types
				where bag_excep_code = l_bag_exception;

			    -- Check if the carrier supports the exception type
			    --
			    if l_carrier.bpm_exceptions is not null and
				instr(l_carrier.bpm_exceptions,l_excid) > 0 then

				    p_bpm_status := 'Y';

				    -- Work done, exit the loop
				    --
				    exit;

			    end if;
			end;

		    else

			-- No exception worries, so we support BPM
			--
			p_bpm_status := 'Y';

			-- Work done, exit the loop
			--
			exit;

		    end if; -- Exception handling check

		end if; -- Carrier handles group check

	    end loop; -- Cursor loop

	end if; -- Carrier supports any groups

    end if; -- BPM enabled for this DCS

exception

    when NO_DATA_FOUND then
    	p_bpm_status := 'N';

end get_bpm_status;
/
