/****************************************************************************
*
* Filename         - $Workfile:   modify_flight_status.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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   modify_flight_status.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:   /shared/pvcs/tagtarch/db_ora/db/source/live/procedures/modify_flight_status.sqv  $
* 
*    Rev 1.4   02 Apr 1998 19:37:14   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.3   15 Jan 1998 12:13:42   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.2   24 Oct 1997 11:17:42   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT create or replace procedure modify_flight_status

create or replace procedure modify_flight_status (
      flt_id     in     flight.flight_id%type,
      flt_no     in     flight.flight_no%type,
      flt_date   in     flight.flight_date%type,
      psiteid    in     flight.site_id%type,
      user_id    in     tagtrak_users.user_id%type,
      scanner_id in     flight_trace.scanner_id%type,
      flt_status in     flight.flight_status%type,
      p_ret_code    out integer
) as

 ab varchar2(80);

 l_status   hbmd.status%TYPE;        -- Current status of the HBMD
 l_db_value db_config.db_value%TYPE; -- Derived db_config value
 l_flt_id   flight.flight_id%TYPE;   -- local copy of given flt id
 l_user_id  tagtrak_users.user_id%type; -- local copy of given user id

begin

      -- Initialise the return code
      p_ret_code := 0;

      -- If NULL or DASH flight status, leave the status of flight unchanged
      if ( (flt_status IS NULL) OR flt_status = '-' )
      then
	    return;
      end if;

      if flt_status IN ('FF', 'PD') then
         -- The flight status is set to Finalised or Post Departed,
         -- check whether this transaction is allowed,
         -- i.e. the HBMD has been set
      
         begin
            -- Determine whether HBMD processing is required/switched on
            select
                   db_value
            into
                   l_db_value
            from
                   db_config
            where
                   db_item = 'HBMD_PROCESSING';
         exception
            when no_data_found then
               -- The entry does not exist - default to no HBMD processing required
               l_db_value := 'N';
         end;

         if l_db_value = 'Y' then
            -- Attempting to set the status to PD, HBMD processing is switched on,
            -- check that the flight's HBMD has been authorised

            begin
               select status into l_status from hbmd
                  where flight_id = flt_id and
                        version = (select max(version) from hbmd where flight_id = flt_id);
            exception
               when no_data_found then
                  -- The HBMD does not exist - set the status to NULL
                  l_status := NULL;
            end;

            -- Default status (in case there is no data in the HBMD table for this flight)
            l_status  := NVL(l_status, 'UNAUTHORISED');

            -- Check if a new HBMD is required
            if l_status != 'AUTHORISED' then
               -- This flight is not allowed to reach post departed as the manifest
               -- has not been authorised, set the error flag and exit
               p_ret_code := '-1';
               return;
            end if; -- l_status != 'AUTHORISED' 

         end if; -- l_db_value = 'Y'

      end if; -- flt_status IN ('FF', 'PD')

	/*
	 * - IF flt_id is not equal to 0 (ie. a flight_id has been specified) then
	 *   Update the flight table, setting the flight_status to the flt_status
	 *    parameter where the flight_id is equal to the flt_id parameter.
	 * - ELSE
	 *   IF flt_date is not equal to '-' (ie. we have a flight date) then
	 *     Update the flight table, setting the flight_status to the
	 *      flt_status parameter where the flight_no is equal to the flt_no
	 *      parameter.
	 *   ELSE
	 *     Update the flight table, setting the flight_status to the
	 *      flt_status parameter where the flight_no is equal to the flt_no
	 *      parameter and the flight_date is equal to the flt_date
	 *      parameter.
	 */

	if (flt_id != 0) then
		update
			flight
		set 
			flight_status = flt_status
		where
			flight_id     = flt_id;
	else 
		if (flt_date = '-') then 
			update
				flight_outbound_v1
			set 
				flight_status = flt_status
			where
				flight_no     = flt_no
        and site_id   = psiteid;
		else 
			update
				flight_outbound_v1
			set 
				flight_status = flt_status
			where
				flight_no     = flt_no and
				flight_date   = flt_date and
        site_id       = psiteid;
		end if;
	end if;

      if SQL%ROWCOUNT = 0 then
         -- The update failed, return the error flag
         p_ret_code:= -2;
      else

         BEGIN

            -- Take local copies of flt and user ids as add_flight_trace_record
            -- expects these to be written to

            l_flt_id := flt_id;
            l_user_id := user_id;

            -- the update succeeded, so create an audit trail entry
            add_flight_trace_record ( l_flt_id, l_user_id , scanner_id, flt_status);

         EXCEPTION

            -- Ignore any errors, to ensure that modify status operation does
            -- not fail just because of a logging failure

            WHEN OTHERS THEN
               null;
         END;
 
      end if;

end modify_flight_status;
/
