/*****************************************************************************
*
* Filename         - $Workfile:   process_btm.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          - FAS/DUBRS/SD/1265
*
* Contents         - BTM Processing procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2004
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE process_btm

CREATE OR REPLACE PACKAGE process_btm AS  
  
/*****************************************************************************  
*  
* Filename              - $Workfile$  
*  
* Project Name          - UltraTrak Database  
*  
* Source Code Identifier        - 0/3/300/8010/112  
*  
* Description           - BTM Interface handling routines.  
*  
* Language              - Oracle PL/SQL
*  
* File History          - For the development history run the Show History report in PVCS  
*  
* (c) Ultra Electronics Limited, 2004  
*  
*****************************************************************************/  

    TYPE check_bag_exists_rec IS RECORD 
     (flight_id       pax.flight_id%TYPE
     ,pax_sequence_no pax.pax_sequence_no%TYPE
     ,pax_id          pax.pax_id%TYPE
     ,scanner_id      bag_trace.scanner_id%TYPE
     ,uld_no          bag_trace.uld_no%TYPE
     ,bag_tag1        bag.bag_tag1%TYPE
     ,bag_tag2a       bag.bag_tag2a%TYPE
     ,bag_tag2b       bag.bag_tag2b%TYPE
     ,bag_tag3        bag.bag_tag3%TYPE);

    TYPE check_bag_exists_cur IS REF CURSOR RETURN check_bag_exists_rec;

    -- Checks to see whether the bag has a BSM or not.
    PROCEDURE check_bag_exists
      (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
      ,p_pax_surname            IN pax.pax_surname%TYPE
      ,check_bag_exists_cur     IN OUT check_bag_exists_cur
      );

    -- Validates the user id  
    FUNCTION validate_userid (p_agent_id in tagtrak_users.loginname%TYPE) 
    RETURN tagtrak_users.user_id%TYPE;  

    -- Validates the location
    FUNCTION validate_location (p_location_code in location.location_code%TYPE) 
    RETURN location.location_code%TYPE;  
END process_btm;
/

PROMPT CREATE OR REPLACE PACKAGE BODY process_btm

CREATE OR REPLACE PACKAGE BODY process_btm 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) 
    RETURN tagtrak_users.user_id%TYPE AS  
      
        l_user_id tagtrak_users.user_id%TYPE := NULL;  
        l_default_user_id tagtrak_users.user_id%TYPE := config.get_value ('BTM_USER_ID');  
    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 default exists in db_config
            IF l_default_user_id IS NOT NULL THEN
                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 (-20781, 'The agent_id (loginname) ' || p_agent_id || ' supplied is Invalid. BTM_USER_ID is not set up in db_config.');  

    END validate_userid;  
  
/*****************************************************************************  
*  
* PL*SQL Package Function - validate_location  
*  
* Description   - Validates a given location.location_code and returns the  
*                 location_code if valid or 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) RETURN location.location_code%TYPE AS  
      
        l_default_location location.location_code%TYPE := TRIM (config.get_value ('BTM_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 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 default exists in db_config
            IF l_default_location IS NOT NULL THEN
                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 (-20782, 'Location code ' || p_location_code || ' is invalid. BTM_LOCATION is not set up in db_config.');  

    END validate_location;  

/*****************************************************************************  
*  
* PL*SQL Package Procedure - check_bag_exists
*  
* Description   - Check to see whether for this combination of flight_no,  
*                 flight_date, bag_tag,pax_surname whether a match is found
*                 for this combination. 
*  
* CR History:  
* Date  Name            CR Number       CR Description  
*****************************************************************************/  

    PROCEDURE check_bag_exists
      (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
      ,p_pax_surname            IN pax.pax_surname%TYPE
      ,check_bag_exists_cur     IN OUT  check_bag_exists_cur
      ) AS

      l_bag_tag1                 bag.bag_tag1%TYPE  := NULL;
      l_bag_tag2a                bag.bag_tag2a%TYPE := NULL;
      l_bag_tag2b                bag.bag_tag2b%TYPE := NULL;
      l_bag_tag3                 bag.bag_tag3%TYPE  := NULL;
      l_tagno_num                CHAR(12)           := NULL;
      l_tag_type                 NUMBER             := NULL;
      -- Normalise the flight no
      l_norm_flight_no           flight.flight_no%TYPE  := normalise_flight_no(p_flight_no);

      BEGIN

        -- Split the tag to use
        IF (p_bag_tag IS NOT NULL ) THEN
            split_tags (  tagno     => p_bag_tag
                         ,tag1      => l_bag_tag1
                         ,tag2a     => l_bag_tag2a
                         ,tag2b     => l_bag_tag2b
                         ,tag3      => l_bag_tag3
                         ,tagno_num => l_tagno_num
                         ,tag_type  => l_tag_type
                        );
        END IF;
        
        -- Check to see whether this bag has been received
        -- If a match is found then a bag_trace record with trace_action
        -- 'BTM' is to be created in the bag_trace table. Need to use the
        -- normalised flight_no

        OPEN check_bag_exists_cur FOR
            SELECT  p.flight_id
                   ,p.pax_sequence_no
                   ,p.pax_id
                   ,b.scanner_id
                   ,b.uld_no
                   ,b.bag_tag1
                   ,b.bag_tag2a
                   ,b.bag_tag2b
                   ,b.bag_tag3
            FROM bag b, pax p, flight f
            WHERE f.flight_no            = l_norm_flight_no
            AND f.flight_date            = p_flight_date
            AND f.flight_id              = p.flight_id
            AND p.pax_surname            = p_pax_surname
            AND p.pax_id                 = b.pax_id   
            AND b.delete_marker          = 0          
            AND p.departure_airport_code = p_departure_airport_code 
            AND bag_tag3                 = l_bag_tag3
            AND (bag_tag2a               = l_bag_tag2a
              OR bag_tag2b               = l_bag_tag2b);

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

      END check_bag_exists;

END process_btm;
/
