/***********************************************************************************************************
*
* Filename         - $Workfile:   determine_security_status.sql  $
*
*                     Name         Date       Version     CR Number
*                     Chris Penny  11/02/2005    
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 
*
* Design Spec
* Section          - FALCP\Segregated\SS\SS01062 BARNZ Hold Baggage Screening 
*
* Contents         - Live Schema Stored Procedure
*
* Language - Oracle SQL*Plus script
*
* Comments - Performs the following checks and returns the appropriate 
*            security clearance status that the bag should be created with 
*            (i.e. 'U'neccesary, 'N'ot Cleared ,'C'leared ) and any application 
*            error code as applicable:
*            1.  Check if the bag has already been security cleared before the arrival 
*               of the BSM (query batch_log table for bag_tag where function is 'CLEAR XRAY' and 
*               bag_tag does not yet exist in the bag table). 
*            2.  Determine if the Bag Exception type for the airline carrier is configured to 
*               require security clearance by querying carrier_site_config table.
*            3.  If the bag has a baggage security screening element then return appropriate value 
*               (i.e. 'SEL' or 'XRAY' return 'N', 'CLR' return 'C', 'NON' return 'U').
*            4.  If the bag is a DIPLOMATIC bag, then the security status is always 'U'.  
*               This would be true even if the BSM included an .X security screening element.
*            5.  Determine if airport code (from .V for 'L'ocal or .I for 'T'ransfer if known) is approved 
*               (set security status variable to 'U')  or non-approved (set security status variable to 'N') 
*               by querying airport_configuration table.  If the airport_code is unknown 
*               (i.e 'T' bag and no .I airport_code information) then set security status variable to 'N'.
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
***********************************************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE determine_security_status

CREATE OR REPLACE PROCEDURE determine_security_status (
     p_flt_no               IN flight.flight_no%TYPE
   , p_flt_date             IN flight.flight_date%TYPE
   , p_bag_tag              IN bag.bag_tag%TYPE
   , p_bsi                  IN bag.bag_source_indicator%TYPE
   , p_airport_code         IN site_info.airport_code%TYPE
   , p_carrier              IN carrier_lookup.alpha_code%TYPE
   , p_bag_exception        IN bag.bag_exception%TYPE DEFAULT NULL
   , p_inbound_airport_code IN site_info.airport_code%TYPE DEFAULT NULL
   , p_security_screening   IN VARCHAR2 DEFAULT NULL
   , p_security_status      IN OUT bag.security_clearance_status%TYPE
   , p_retcode              IN OUT NUMBER
)
   AS

    l_proc_name                   VARCHAR2(100);
    l_err_msg                     oracle_error.errror_msg%TYPE;
    l_err_code                    oracle_error.error_code%TYPE;
    l_tag1                        bag.bag_tag1%TYPE;
    l_tag2a                       bag.bag_tag2a%TYPE;
    l_tag2b                       bag.bag_tag2b%TYPE;
    l_tag3                        bag.bag_tag3%TYPE;
    l_tag_num                     bag.bag_tag%TYPE;
    l_tag_type                    NUMBER;

    l_flt_no                      flight.flight_no%TYPE;
    l_flt_date                    flight.flight_date%TYPE;
    l_bag_tag                     bag.bag_tag%TYPE;
    l_bsi                         bag.bag_source_indicator%TYPE;
    l_airport_code                site_info.airport_code%TYPE;
    l_carrier                     carrier_lookup.alpha_code%TYPE;
    l_bag_exception               bag.bag_exception%TYPE;
    l_in_airport_code             site_info.airport_code%TYPE;
    l_security_screening          VARCHAR2(4); /* from BSM .X  ('SEL','CLR','XRAY','NON') */
    l_site_id                     site_info.site_id%TYPE;
    l_config_item                 carrier_site_config.config_item%TYPE;
    l_config_value                carrier_site_config.config_value%TYPE := '-';
    l_security_precleared         VARCHAR2(1);
    l_security_clearance          airport_configuration.security_clearance%TYPE;

    CURSOR c_get_pax IS
        SELECT pax.pax_id
        FROM   pax    pax
             , flight_outbound_v1 flt
        WHERE  pax.flight_id    = flt.flight_id
        AND    flt.flight_no    = l_flt_no
        AND    flt.flight_date  = l_flt_date
        AND    flt.site_id      = l_site_id;

    CURSOR c_carrier_site_config (l_config_item IN carrier_site_config.config_item%TYPE) IS
        SELECT NVL(config_value, '-') as config_value
        FROM   carrier_site_config
        WHERE  config_item = l_config_item
        AND    site_id     = l_site_id
        AND    alpha_code  = p_carrier;


BEGIN

    l_proc_name           := 'determine_security_status';
    l_flt_no              := p_flt_no;
    l_flt_date            := p_flt_date;
    l_bag_tag             := p_bag_tag;
    l_bsi                 := p_bsi;
    l_airport_code        := p_airport_code;
    l_carrier             := p_carrier;
    l_bag_exception       := p_bag_exception;
    l_in_airport_code     := p_inbound_airport_code;
    l_security_screening  := NVL(TRIM(p_security_screening), 'NON');
    l_security_precleared := 'N'; /* Assume no security pre-clearance by default */
    p_security_status     := 'U'; /* If security status cannot be determined then default to 'U' */
    p_retcode             := 0;


    -- Get site id
    l_site_id := multi_site.get_site_id(l_airport_code);

    IF (l_bag_tag IS NOT NULL) THEN

       -- Use SPLIT_TAGS to break the tag down into its components for bag tag matching
       split_tags( l_bag_tag, l_tag1, l_tag2a, l_tag2b, l_tag3, l_tag_num, l_tag_type );

       -- Check if the bag has already been security cleared before the arrival of the BSM
       FOR r_get_pax IN c_get_pax LOOP

          BEGIN
             SELECT 'C'
             INTO   l_security_precleared
             FROM   batch_log
             WHERE  function = 'CLEAR XRAY'
               AND    tag3 = l_tag3
               AND  (  tag2b = NVL(NVL(l_tag2b,alpha_to_numeric(l_tag2a)),'-') )
               AND NOT EXISTS (SELECT 'x'
                               FROM   bag
                               WHERE  bag_tag3   = l_tag3
                                 AND (
                                        bag_tag2b  = NVL(l_tag2b,'-')
                                      OR
                                        bag_tag2a  = NVL(l_tag2a,'-')
                                     )
                                 AND  bag.pax_id = r_get_pax.pax_id );

          EXCEPTION
             WHEN No_Data_Found THEN
                l_security_precleared := 'N';
          END;

       END LOOP;

    END IF;

    -- Find out if security screening is configured for bag exception type or for ALL bags
    l_config_item := 'SCREEN_' || NVL(l_bag_exception,'ALL');

    OPEN  c_carrier_site_config (l_config_item);
    FETCH c_carrier_site_config INTO l_config_value;
    CLOSE c_carrier_site_config;

    -- For Diplomatic bags security screening is never necessary unless this rule is
    -- over-ridden by configuration for site and carrier in the carrier_site_config table
    IF (l_bag_exception = 'DIPL') THEN

       IF (l_config_value = 'Y') THEN

          p_security_status := 'N';

       ELSE

          p_security_status := 'U';

       END IF;

    ELSE

       -- Not a diplomatic bag or SCREEN_DIPL is set for this carrier at the airport
       -- Get security_clearance for airport
       l_security_clearance := security_screening.security_clearance(l_airport_code, l_flt_no);

       IF (l_security_clearance <> 'Y')  THEN

          -- If this is a transfer bag check whether the bag is from an approved airport
          IF (l_bsi = 'T') THEN

             IF (l_in_airport_code IS NOT NULL) THEN

                l_security_clearance := security_screening.site_approval_clearance (l_in_airport_code);

                IF (l_security_clearance IS NULL) THEN

                   p_security_status := 'U';

                ELSE

                   p_security_status := l_security_clearance;

                END IF;

             ELSE

                -- Only check transfer bags with no .I field if ENABLE_I_IN_BSM_CHK set
		IF trim(config.get_value('ENABLE_I_IN_BSM_CHK')) = 'Y' 
		THEN
			p_security_status := 'N';
		ELSE
			p_security_status := 'U';
		END IF;

             END IF;

          ELSE

             p_security_status := 'U';

          END IF;

       ELSE

          p_security_status := 'N';

       END IF;

       IF (l_config_value = 'Y') THEN

          p_security_status := 'N';

       END IF;

       -- Check clearance on BSM
       IF (p_security_status = 'U') THEN

          -- This value can be over-ridden by exception configuration or security pre-clearance
          IF (l_security_screening IN ('SEL','XRAY')) THEN

             p_security_status := 'N';

          ELSIF (l_security_screening = 'CLR') THEN

             p_security_status := 'C';

          END IF; -- End of (l_security_screening IN ('SEL','XRAY'))

       END IF; -- End of (p_security_status = 'U')

       -- There is no .X security screening element
       IF (p_security_status = 'N') THEN

          IF (l_security_precleared = 'C') THEN

             -- but carrier_site_config value explicitly
             -- DOES NOT require screening for bag exception type
             -- set to U not N
             p_security_status := 'C';

          END IF; -- End of (l_security_precleared = 'C')

       END IF; -- End of (p_security_status = 'N')

    END IF; -- End of (l_bag_exception = 'DIPL')

    -- If at the end we have decided it hasn't cleared security, then
    -- see if the carrier lookup wants us to override this.
    --
    IF p_security_status = 'N' THEN
    	DECLARE
	    l_selectee carrier_lookup.selectee_pax%type;
	BEGIN
	    SELECT selectee_pax INTO l_selectee FROM carrier_lookup
	    	WHERE alpha_code = p_carrier;

	    -- Set to unneeded if to be ignored
	    --
	    IF l_selectee = 'N' THEN
	    	p_security_status := 'U';
	    END IF;
	END;
    END IF;

EXCEPTION

    -- Default security TO 'N' for any error conditions and return the SQL error code
    WHEN NO_DATA_FOUND THEN

        l_err_code := SQLCODE;
        l_err_msg  := SQLERRM;
        p_retcode  := SQLCODE;

        p_security_status := 'N';
        add_app_msg.log_oracle_error ( l_proc_name, l_err_msg, l_err_code );


    WHEN OTHERS THEN

        l_err_code := SQLCODE;
        l_err_msg  := SQLERRM;
        p_retcode  := SQLCODE;
        p_security_status := 'N';
        add_app_msg.log_oracle_error ( l_proc_name, l_err_msg, l_err_code );

END determine_security_status;
/


