/*****************************************************************************
*
* Filename         - $Workfile:   unknown_bag_bpm.sql  $
*
*                     Name              Date
* Author           -  Phil Burns        16/07/2006
*
* Project Name     - FALCP
*
* Source Code
* Identifier       -
*
* Design Spec
* Section          - UAS/FALCP/CA/
*
* Contents         - This stored procedure will accept a bag tag and check to see
*                    if it already exists, if it doesn't then a unknown bag row
*                    will be created.
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

PROMPT create or replace procedure unknown_bag_bpm

create or replace procedure unknown_bag_bpm (
        p_depair          in      pax.departure_airport_code%type,
        p_user_id         in      tagtrak_users.user_id%type,
        p_bag_tag         in      bag.bag_tag%type
)

as

        l_tag                bag.bag_tag%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_type           number;
        l_user_id            tagtrak_users.user_id%type;
        l_bag_count          number := 0;
        l_unknown_bag_count  number := 0;
        l_site_id            site_config.site_id%type;

BEGIN

        l_site_id := multi_site.get_site_id(p_depair);
        l_user_id := p_user_id;
        split_tags(p_bag_tag, l_tag1, l_tag2a, l_tag2b, l_tag3, l_tag, l_tag_type);

        -- check the BAG table to see if this tag exists and has not been deleted
        SELECT count(b.bag_tag) INTO l_bag_count FROM bag b, pax p, flight f WHERE 
            (b.bag_tag2a = l_tag2a OR b.bag_tag2b = l_tag2b) AND
            b.bag_tag3 = l_tag3 AND
            b.pax_id = p.pax_id AND
            p.flight_id = f.flight_id AND
            f.site_id = l_site_id AND
            b.delete_marker = 0;

        -- check the UNKNOWN_BAG table to see if this tag has already been created as a unknown bag 
        -- either with or without a flight id
        IF l_bag_count = 0 THEN
           SELECT count(ub.bag_tag) INTO l_unknown_bag_count FROM unknown_bag ub, tagtrak_users tu, agents a WHERE
               (ub.bag_tag2a = l_tag2a OR ub.bag_tag2b = l_tag2b) AND
               ub.bag_tag3 = l_tag3 AND
               ub.user_id = tu.user_id AND
               tu.agent_code = a.agent_code AND
               a.site_id = l_site_id;
        END IF;
 
        -- if no bag currently exists matching this tag then create a unknown bag with no flight or pax id
        IF l_bag_count = 0 AND l_unknown_bag_count = 0 THEN
     
           unauthorised_bags.add_unknown_bag (l_tag,l_tag1,l_tag2a,l_tag2b,l_tag3,0,0,0,l_user_id,'',NULL);

        END IF;

END;
/
