/*****************************************************************************
*
* Filename         - $Workfile:   security_screening.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell  5/8/2      1.0          15666
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - ULTRATRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Package
*
* Language         - Oracle PL/SQL script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2002
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   security_screening.sql  $  $Revision:   1.0  $
*
* 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/security_screening.svl  $
* 
* 
*****************************************************************************/
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                                                           *
 * Package header for BA BRS Security screening processes declaring global   *
 * functions                                                                 *
 *                                                                           *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
PROMPT CREATE OR REPLACE PACKAGE security_screening

CREATE OR REPLACE PACKAGE security_screening AS
   
   SUBTYPE error_msg_type      IS VARCHAR2(512);
   SUBTYPE error_num_type      IS NUMBER;
  
FUNCTION security_clearance
   (p_airport_code IN airport_configuration.airport_code%TYPE
   ,p_flight_no      IN flight.flight_no%TYPE
   ) RETURN flight.security_clearance%TYPE;

PRAGMA RESTRICT_REFERENCES(security_clearance, WNDS, WNPS);

FUNCTION site_approval_clearance
   (p_airport_code IN site_info.airport_code%TYPE
   ) RETURN site_info.approved_security_status%TYPE;
   
PROCEDURE set_a_security_bag
   (p_location_code               IN              location.location_code%TYPE
   ,p_user_id                     IN              tagtrak_users.user_id%TYPE
   ,p_flight_id                   IN              flight.flight_id%TYPE
   ,p_bag_tag                     IN              bag.bag_tag%TYPE
   ,p_security_clearance_status   IN              bag.security_clearance_status%TYPE
   ,p_serial_no                   IN              kit.serial_number%TYPE
   ,p_security_reason             IN              bag.security_reason%TYPE
   ,p_security_result             IN              bag.security_result%TYPE
   ,p_error_message               OUT             error_msg_type
   ,p_error_number                OUT             error_num_type
   );

procedure Needs_Security_Reason(p_flight_id flight.flight_id%type,
				p_security_result in varchar2,
				p_security_reason in varchar2,
				p_retcode out number);

END security_screening;
/

CREATE OR REPLACE PACKAGE BODY security_screening AS

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                                                           *
 * Function to obtain the security clearance for a flight based on how the   *
 * airport is configured                                                     *
 * This function is local to the package                                     *
 *                                                                           *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
FUNCTION airport_security_clearance
   (p_airport_code IN airport_configuration.airport_code%TYPE
   ) RETURN flight.security_clearance%TYPE IS
   v_security_clearance airport_configuration.security_clearance%TYPE DEFAULT 'U';
   BEGIN
    -- obtain the configured security clearance for the airport
      SELECT security_clearance
      INTO v_security_clearance
      FROM airport_configuration
      WHERE airport_code = p_airport_code;

      RETURN v_security_clearance;

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN NULL;
      WHEN OTHERS THEN
         RAISE;
   END airport_security_clearance;


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                                                           *
 * Function to obtain the security clearance for a flight based on whether   *
 * the flight is configured                                                  *
 *                                                                           *
 * This function is local to the package                                     *
 *                                                                           *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
FUNCTION flight_security_clearance
   (p_flight_no      IN flight.flight_no%TYPE
   ) RETURN flight.security_clearance%TYPE IS
   v_security_clearance airport_configuration.security_clearance%TYPE DEFAULT 'U';
   BEGIN
    -- obtain the configured security clearance for the flight
      SELECT security_clearance
      INTO v_security_clearance
      FROM flight_configuration
      WHERE flight_no = p_flight_no;

      RETURN v_security_clearance;

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN NULL;
      WHEN OTHERS THEN
         RAISE;
   END flight_security_clearance;

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                                                           *
 * Function to set the security clearance for a flight based on how the      *
 * flight or airport is configured                                           *
 *                                                                           *
 * This function is global                                                   *
 *                                                                           *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
FUNCTION security_clearance
   (p_airport_code IN airport_configuration.airport_code%TYPE
   ,p_flight_no      IN flight.flight_no%TYPE
   ) RETURN flight.security_clearance%TYPE IS

   BEGIN
      -- Obtain the security clearance for the flight
      -- if it does not exist then obtain the security clearance for the airport
      -- if it does not exist then set the security clearance to not required
   IF flight_security_clearance(p_flight_no) IS NOT NULL THEN
      RETURN flight_security_clearance(p_flight_no);
   ELSIF airport_security_clearance(p_airport_code) IS NOT NULL THEN
      RETURN airport_security_clearance(p_airport_code);
   ELSE
      RETURN 'U';
      END IF;

   END security_clearance;


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                                                           *
 * Function to obtain the clearance from the site_info table based on the    *
 * airport code                                                                                           *
 *                                                                           *
 * This function is global                                                   *
 *                                                                           *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
FUNCTION site_approval_clearance
   (p_airport_code IN site_info.airport_code%TYPE
   ) RETURN site_info.approved_security_status%TYPE IS
   v_security_clearance site_info.approved_security_status%TYPE DEFAULT 'U';

   BEGIN
    -- obtain the security clearance from site information via airport code
      SELECT DECODE(approved_security_status, 'Y', 'N', 'U')
      INTO v_security_clearance
      FROM site_info
      WHERE airport_code = p_airport_code;

      RETURN v_security_clearance;

   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         RETURN NULL;
      WHEN OTHERS THEN
         RAISE;

   END site_approval_clearance;
  
  
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
 *                                                                           *
 * Procedure to set security on a bag.                                       *    
 *                                                                           *
 * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */   
PROCEDURE set_a_security_bag
        (p_location_code               IN              location.location_code%TYPE
        ,p_user_id                     IN              tagtrak_users.user_id%TYPE
        ,p_flight_id                   IN              flight.flight_id%TYPE
        ,p_bag_tag                     IN              bag.bag_tag%TYPE
        ,p_security_clearance_status   IN              bag.security_clearance_status%TYPE
        ,p_serial_no                   IN              kit.serial_number%TYPE
        ,p_security_reason             IN              bag.security_reason%TYPE
        ,p_security_result             IN              bag.security_result%TYPE
        ,p_error_message               OUT             error_msg_type
        ,p_error_number                OUT             error_num_type
        ) AS
        
        l_pax_id                    pax.pax_id%TYPE;
        l_pax_sequence_no           pax.pax_sequence_no%TYPE;
        l_bag_tag1                  bag.bag_tag1%TYPE;
        l_bag_tag2a                 bag.bag_tag2a%TYPE;
        l_bag_tag2b                 bag.bag_tag2b%TYPE;
        l_bag_tag3                  bag.bag_tag3%TYPE;
        l_user_id                   tagtrak_users.user_id%TYPE;
        n_bag_tag                   bag.bag_tag%TYPE;
        l_bt1                       bag.bag_tag1%TYPE;
        l_bt2a                      bag.bag_tag2a%TYPE;
        l_bt2b                      bag.bag_tag2b%TYPE;
        l_bt3                       bag.bag_tag3%TYPE;
        l_tag_num                   bag.bag_tag%TYPE;
        l_tag_type                  NUMBER;
        l_event_count               NUMBER;
        l_no_of_errs                NUMBER;        
        l_flight_status             flight.flight_status%TYPE;
        l_uld_no                    ulds.uld_no%TYPE;
        l_error_message             error_msg_type DEFAULT NULL;
        l_error_number              error_num_type DEFAULT 0;
        l_action                    bag_trace.trace_action%TYPE;
        
        l_check_constraint          EXCEPTION;
        PRAGMA EXCEPTION_INIT(l_check_constraint, -2290);
        
        BEGIN
          /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
           *                                                                                             *
           * set the message for a trace record depending on the security_clearance_status               *
           * if the bag has been through xray (status = 'C') then set the message to 'CLEAR XRAY'        *
           * if the bag has not been through xray (status = 'N') then set the message to 'NOT XRAY'      *
           *                                                                                             *
           * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * */
           SELECT
           DECODE(p_security_clearance_status,'C','CLEAR XRAY','NOT XRAY')
           INTO
           l_action
           FROM
           dual; 

           -- Create a trace entry (HANDLER_TRACE) for this flight
           handler_tracing.mws_handler_trace(p_user_id, p_serial_no, p_flight_id, '', 0, '', 0, 0);

           -- Ensure bag tag is in numeric format                                      
           n_bag_tag := get_numeric_bag_tag(p_bag_tag);                                          

           split_tags(n_bag_tag, l_bt1, l_bt2a, l_bt2b, l_bt3, l_tag_num, l_tag_type, 0);  
           
           --Check for Post Departure
           SELECT flight_status
           INTO l_flight_status
           FROM flight
           WHERE flight_id = p_flight_id;   

           IF l_flight_status = 'PD' THEN
             l_error_message := 'flight id '
                  || p_flight_id ||
                  ' ';
                   l_error_number := -208;
             SELECT CONCAT(l_error_message,error_description) 
             INTO l_error_message 
             FROM error 
             WHERE error_id = l_error_number;
             
             RAISE_APPLICATION_ERROR(l_error_number, 'Flight is PD' || l_error_message);
           END IF;
        
        BEGIN            
            SELECT p.pax_id, p.pax_sequence_no, b.bag_tag1, b.bag_tag2a, NVL(b.bag_tag2b,'-'), b.bag_tag3
            INTO l_pax_id, l_pax_sequence_no, l_bag_tag1, l_bag_tag2a, l_bag_tag2b, l_bag_tag3
            FROM pax p, bag b, flight f
            WHERE p.flight_id = f.flight_id
            AND p.pax_id = b.pax_id
            AND b.bag_tag2b = l_bt2b                                                
            AND b.bag_tag3 = l_bt3                                                   
            AND f.flight_id = p_flight_id
            AND b.delete_marker = 0
            AND b.security_clearance_status <> p_security_clearance_status
            FOR UPDATE OF b.security_clearance_status;
        
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
               l_error_message := 'bag ' || n_bag_tag || ' not found for flight ' || p_flight_id;                                                    
               l_error_number := SQLCODE;
               RAISE_APPLICATION_ERROR(l_error_number, l_error_message);
            WHEN TOO_MANY_ROWS THEN
               l_error_number := -1003;
               SELECT error_description INTO l_error_message FROM error WHERE error_id = -1003;
               RAISE_APPLICATION_ERROR(l_error_number, l_error_message);
            WHEN OTHERS THEN
               RAISE;
        END;

        -- Fire any automatic tracking events for this bag (ignore errors, as these
        -- should not affect the operation itself)

        tracking.auto_events(n_bag_tag, p_flight_id, NULL, l_pax_id, 0, p_user_id,         
                         p_location_code, NULL, l_event_count, l_no_of_errs);

        BEGIN
           IF l_action = 'CLEAR XRAY' THEN
              UPDATE bag
              SET security_clearance_status = p_security_clearance_status
                , sc_user_id = p_user_id
                , sc_location = p_location_code
                , sc_time = SYSDATE
                , security_reason = p_security_reason
                , security_result = p_security_result
              WHERE bag_tag = n_bag_tag
              AND pax_id = l_pax_id;

           ELSE -- 'NOT XRAY' 'N' remove security certificate
              UPDATE bag
              SET security_clearance_status = p_security_clearance_status
                , sc_user_id  = NULL
                , sc_location = NULL
                , sc_time     = NULL
                , security_reason = p_security_reason
                , security_result = p_security_result
              WHERE bag_tag = n_bag_tag
              AND pax_id = l_pax_id;
           END IF;
          
        EXCEPTION
           WHEN l_check_constraint THEN
              l_error_message := SQLERRM;
              l_error_number := SQLCODE;
              RAISE_APPLICATION_ERROR(l_error_number, l_error_message);
        END;
        
        -- Get the ULD number of the bag for adding the bag trace record.
        BEGIN
           SELECT b.uld_no
           INTO l_uld_no
           FROM bag b
           WHERE b.bag_tag = p_bag_tag
           AND b.pax_id = l_pax_id;
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
              l_uld_no := NULL;
        END;

        l_user_id := p_user_id;

        add_bag_trace_record(l_user_id
                            ,p_location_code
                            ,0      -- error_code
                            ,n_bag_tag                                                                
                            ,l_bag_tag1
                            ,l_bag_tag2a
                            ,l_bag_tag2b
                            ,l_bag_tag3
                            ,l_action -- trace_action
                            ,0 -- scanner_id
                            ,p_flight_id
                            ,l_pax_id
                            ,l_uld_no);    

        p_error_number := 0;
        p_error_message := 'set_a_security_bag successful for bag ' || n_bag_tag 
                           || ' on flight ' || p_flight_id;            

        IF p_security_clearance_status  = 'C' THEN

           IF mws_web.store_bpm1(p_location_code, l_user_id, p_flight_id, l_pax_id, n_bag_tag, l_pax_sequence_no, 
                         l_action) THEN
              NULL;
           END IF;

        END IF;
        
     END set_a_security_bag;

/******************************************************************************
*
* PROCEDURE Needs_Security_Reason
*
* Description:
*   Sees if the passed flight needs a security result and reason setting.
*
* Input Parameters:
*   p_flight_id         - The flight's ID
*   p_security_result   - The user supplied result.  '-' means not supplied.
*   p_security_reason   - The user supplied reason.  '-' means not supplied.
*   p_retcode           - The returned status.
*
* Errors Raised:
*   None
*
******************************************************************************/
procedure Needs_Security_Reason(p_flight_id flight.flight_id%type,
				p_security_result in varchar2,
				p_security_reason in varchar2,
				p_retcode out number) is
    l_rp1745 system_cfg.rp1745%type;
begin
    select s.rp1745 into l_rp1745
	from flight f, system_cfg s
	where f.flight_id = p_flight_id and
	      s.process_name = f.process_name and
	      s.subunit_name = f.subunit_name and
	      s.dcsid = f.dcsid and
	      s.unit_name = f.unit_name;

    if l_rp1745 = 27 and (p_security_reason = '-' or
    				p_security_result = '-') then
	p_retcode := -20803;
    else
	p_retcode := 0;
    end if;

end Needs_Security_Reason;
          
END security_screening;
/
