/*****************************************************************************
*
* Filename		- cancel_flight.sql
*
* Project Name		- UltraTrak Database
*
* Source Code Identifier - UAS/FALCP/
*
* Description		- Procedure to perform cancel flight functionality
*
* Language		- Oracle PL/SQL
*
* File History		- For the development history run the Show History report in PVCS
*
* Copyright Ultra Electronics Airport Systems, 2004
*
*****************************************************************************/


/******************************************************************************
 *
 * PROCEDURE CANCEL_FLIGHT
 *
 * Description:
 *
 * This procedure carries out actions associated with the cancellation of a 
 * flight. This procedure should be called by the flight triggers when
 * the status of a flight is set to FX. Note: this procedure inserts into
 * the INTERIM table used by afterstatement trigger on flight table, so this
 * procedure should only every be called in this way.
 *
 * The action carried out depends on the CANCEL_ACTION db_config setting:
 *
 * If REMOVE_ACCESS - all entries in flight_access are removed for the 
 *                    given flight are removed
 * If STOP_BAGS     - all bags associated with the given flight are
 *                    stopped. 
 * If FREEZE_FLIGHT - the flight is frozen (assigned to handler FZ<AIRPORT_CODE>)
 * 
 * If IGNORE        - no action is taken 
 *
 * Parameters:
 *
 * p_flight_id   - flight id of the flight that has been cancelled
 * p_site_id     - site id associated with flight
 * p_ignore_flag - flag is set if the action is set to ignore. This indicates to
 *                 calling trigger that the flight cancellation should be ignored
 *
 ******************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE cancel_flight 

CREATE OR REPLACE PROCEDURE cancel_flight 
(
   p_flight_id    IN  flight.flight_id%TYPE,
   p_site_id      IN  site_info.site_id%TYPE,
   p_ignore_flag  OUT boolean 
)
IS
   l_action         db_config.db_value%type;
   l_user_id        tagtrak_users.user_id%type;
   l_airport_code   site_info.airport_code%type;
   l_location       location.location_code%type;
   l_agent_code     agents.agent_code%type;
   l_error_message  mws_web.error_msg_type;
   l_error_number   mws_web.error_num_type;

BEGIN

   -- This flag is set if the action is set to ignore. This indicates to 
   -- calling trigger that the flight cancellation should be ignored

   p_ignore_flag := FALSE;

   -- Determine what action is required by looking at the db_config setting

   l_action := trim(config.get_value('CANCEL_ACTION'));

   IF l_action IS NOT NULL THEN

         -- In order to create the log of the action we need a user id and a location

         BEGIN

            -- Use auto user and location for any logs created

            SELECT airport_code INTO l_airport_code FROM site_info WHERE
               site_id = p_site_id; 

            SELECT user_id INTO l_user_id FROM tagtrak_users WHERE
               RTRIM(loginname) = RTRIM(multi_site.get_auto_loginname(l_airport_code));

            l_location := multi_site.get_auto_location(TRIM(l_airport_code));

         EXCEPTION

            WHEN OTHERS THEN
               -- Ignore any errors that errors when logging, as we don't
               -- want the entire operation to fail
               null;
         END;

      IF l_action = 'REMOVE_ACCESS' THEN

         -- Remove flight access for this flight
         revoke_all_by_flight(p_flight_id);

      ELSIF l_action = 'STOP_BAGS' THEN
        
         -- All bags not currently stopped should
         -- now be stopped. This will be dealt with
         -- in the flight after statement trigger 

         -- Add entry into interim table to generate bag trace
         INSERT INTO interim (id, numeric) values
            (7, p_flight_id);

      ELSIF l_action = 'FREEZE_FLIGHT' THEN

        -- Get a valid agent that can currently access the flight

        BEGIN

           SELECT
              agent_code
           INTO
              l_agent_code
           FROM
              flight_access
           WHERE
              flight_id = p_flight_id AND rownum = 1;
      
           mws_web.flight_restriction (p_flight_id
                                      ,l_agent_code
                                      ,'FZ' || l_airport_code
                                      ,l_user_id -- user_id
                                      ,0 -- kit_id
                                      ,l_error_message
                                      ,l_error_number);
        EXCEPTION

           WHEN OTHERS THEN
              -- Ignore any errors that occur
              null;
        END;

      ELSIF l_action = 'IGNORE' THEN
 
         p_ignore_flag := TRUE;

      END IF;

   END IF;

END;
/
