PROMPT CREATE OR REPLACE PACKAGE handle_bpm

CREATE OR REPLACE PACKAGE HANDLE_BPM AS  
  
/*****************************************************************************  
*  
* Filename              - $Workfile$  
*  
* Project Name          - UltraTrak Database  
*  
* Source Code Identifier        - 0/3/300/8010/112  
*  
* Description           - BPM Interface handling routines.  
*  
* Language              - Oracle PL/SQL}  
*  
* File History          - For the development history run the Show History report in PVCS  
*  
* (c) Ultra Electronics Limited, 2001  
*  
*****************************************************************************/  
  
    TYPE get_unique_bag_rec IS RECORD 
     (pax_sequence_no pax.pax_sequence_no%TYPE
     ,flight_id pax.flight_id%TYPE);

    TYPE get_unique_bag_cur IS REF CURSOR RETURN get_unique_bag_rec;

    PROCEDURE get_unique_bags
      (p_departure_airport_code IN pax.departure_airport_code%TYPE
      ,p_flight_no              IN flight.flight_no%TYPE
      ,p_flight_date            IN flight.flight_date%TYPE
      ,p_bag_tag                IN bag.bag_tag%TYPE
      ,get_unique_bag_ref_cur   IN OUT get_unique_bag_cur
      );

    FUNCTION validate_userid ( p_agent_id in tagtrak_users.loginname%TYPE
                              ,p_airport_code in site_info.airport_code%TYPE) RETURN tagtrak_users.user_id%TYPE;  
    FUNCTION validate_location ( p_location_code in location.location_code%TYPE
                                ,p_airport_code  in site_info.airport_code%TYPE) RETURN location.location_code%TYPE;  

    -- Validates a location
    --
    -- Arguments:
    --     p_location_code - The location to validate.
    --
    -- Returns:
    --     The location code if valid, otherwise NULL.
    --
    FUNCTION validate_location_no_def
    		(p_location_code in location.location_code%TYPE)
		 	RETURN location.location_code%TYPE;
END handle_bpm;
/

PROMPT CREATE OR REPLACE PACKAGE BODY handle_bpm

CREATE OR REPLACE PACKAGE BODY HANDLE_BPM AS 
 
/*****************************************************************************  
*  
* PL*SQL Package Function - validate_userid  
*  
* Description   - Validates a tagtrak_users.loginname and returns the userid  
*  
* CR History:  
* Date  Name            CR Number       CR Description  
*  
*****************************************************************************/  
  
    FUNCTION validate_userid ( p_agent_id     IN tagtrak_users.loginname%TYPE
                              ,p_airport_code IN site_info.airport_code%TYPE) RETURN tagtrak_users.user_id%TYPE AS  
      
        l_user_id tagtrak_users.user_id%TYPE := NULL;  
        l_configured_user_id tagtrak_users.user_id%TYPE := multi_site.get_user_id(multi_site.get_site_config('DEF_USER',multi_site.get_site_id(p_airport_code)));
        l_default_user_id tagtrak_users.user_id%TYPE := multi_site.get_user_id(config.get_value ('DEF_USER'));  
    BEGIN

        SELECT  
            user_id INTO l_user_id  
        FROM  
            tagtrak_users  
        WHERE  
            UPPER (loginname) = UPPER (p_agent_id);  
  
        RETURN l_user_id;  
  
-- Only handle NO_DATA_FOUND other exception are passed to the calling block to pass on a meaningfull message
    EXCEPTION  
        WHEN NO_DATA_FOUND THEN
        
        -- Check if configured user is in site_config
           IF l_configured_user_id IS NOT NULL THEN
              RETURN l_configured_user_id;
           ELSE
              -- Use the default user in db_config
              RETURN l_default_user_id;
           END IF;

        -- If agent_id is invalid and no db_config setup then raise an exception otherwise return the default user_id
            RAISE_APPLICATION_ERROR (-20000, 'The agent_id (loginname) ' || p_agent_id || ' supplied is Invalid. DEF_USER is not set up in db_config.');  

    END validate_userid;  
  
/*****************************************************************************  
*  
* PL*SQL Package Function - validate_userid  
*  
* Description   - Validates a given location.location_code and returns the  
*                 location_code if valid ir a default if the input parameter  
*                 is NULL   
*  
* CR History:  
* Date  Name            CR Number       CR Description  
*  
*****************************************************************************/  
  
    FUNCTION validate_location ( p_location_code IN location.location_code%TYPE
                                ,p_airport_code IN site_info.airport_code%TYPE) RETURN location.location_code%TYPE AS  
      
        l_configured_location location.location_code%TYPE := TRIM(multi_site.get_site_config('DEF_LOCATION',multi_site.get_site_id(p_airport_code)));
        l_default_location location.location_code%TYPE := TRIM (config.get_value ('DEF_LOCATION')); 
        dummy NUMBER := 0;  
    BEGIN

    -- Test to see if the location_code exists on the database
        SELECT  
            1 INTO dummy  
        FROM  
            location  
        WHERE  
            location_code = p_location_code;  
  
        RETURN trim(p_location_code);  

-- Only handle NO_DATA_FOUND other exception are passed to the calling block to pass on a meaningfull message
    EXCEPTION  
        WHEN NO_DATA_FOUND THEN

        -- Check if configured location is in site_config
           IF l_configured_location IS NOT NULL THEN
              RETURN l_configured_location;
           ELSE
              -- Use the default location in db_config
              RETURN l_default_location;
           END IF;

        -- If location_code is invalid and no db_config setup then raise an exception otherwise return the default
            RAISE_APPLICATION_ERROR (-20000, 'Location code ' || p_location_code || ' is invalid. DEF_LOCATION is not set up in db_config.');  

    END validate_location;  

/*****************************************************************************  
*  
* PL*SQL Package Procedure - get_unique_bags
*  
* Description   - Obtain all the flights and sequence numbers for a bag so as
*                 to uniquely identify all possible occurrences of this bag
*                 departing from a given airport.
*  
*****************************************************************************/  

    PROCEDURE get_unique_bags
      (p_departure_airport_code IN pax.departure_airport_code%TYPE
      ,p_flight_no              IN flight.flight_no%TYPE
      ,p_flight_date            IN flight.flight_date%TYPE
      ,p_bag_tag                IN bag.bag_tag%TYPE
      ,get_unique_bag_ref_cur   IN OUT get_unique_bag_cur
      ) AS
      BEGIN
        IF p_flight_no IS NULL OR p_flight_date IS NULL THEN
          OPEN get_unique_bag_ref_cur FOR
            SELECT  p.pax_sequence_no, p.flight_id
            FROM bag b, pax p
            WHERE p.pax_id               = b.pax_id   
            AND b.delete_marker          = 0          
            AND p.departure_airport_code = p_departure_airport_code 
            AND bag_tag3                 = TO_NUMBER(SUBSTR(p_bag_tag,-6))
            AND (bag_tag2a               = SUBSTR(p_bag_tag,-9,3) 
              OR bag_tag2b               = SUBSTR(p_bag_tag,-9,3));
        ELSE
          OPEN get_unique_bag_ref_cur FOR
            SELECT  p.pax_sequence_no, p.flight_id
            FROM bag b, pax p, flight_outbound_v1 f
            WHERE f.flight_no            = p_flight_no
            AND f.flight_date            = p_flight_date
            AND f.flight_id              = p.flight_id
            AND p.pax_id                 = b.pax_id   
            AND b.delete_marker          = 0          
            AND p.departure_airport_code = p_departure_airport_code 
            AND bag_tag3                 = TO_NUMBER(SUBSTR(p_bag_tag,-6))
            AND (bag_tag2a               = SUBSTR(p_bag_tag,-9,3) 
              OR bag_tag2b               = SUBSTR(p_bag_tag,-9,3));
        END IF;

      EXCEPTION  
        WHEN OTHERS THEN
          RAISE_APPLICATION_ERROR (-20000, 'Fatal Error attempting to retrieve details for Airport code ' || p_departure_airport_code || ' and Bag tag ' || p_bag_tag );  

      END get_unique_bags;


    FUNCTION validate_location_no_def
    		(p_location_code in location.location_code%TYPE)
		 	RETURN location.location_code%TYPE IS
        dummy NUMBER := 0;  
    BEGIN

        SELECT 1 INTO dummy  
	    FROM location  
	    WHERE location_code = p_location_code;  
  
        RETURN trim(p_location_code);  

    EXCEPTION  

        WHEN NO_DATA_FOUND THEN
           RETURN null;

    END validate_location_no_def;

END handle_bpm;
/
