/*****************************************************************************
*
* Filename         - $Workfile:   clear_bag.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          - 
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   clear_bag.sql  $  $Revision:   1.10  $
*
* 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/clear_bag.svl  $
* 
*    Rev 1.10   21 Jun 2001 14:42:14   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
* 
*    Rev 1.9   20 Sep 1999 14:40:22   tagtrak
*  
* 
*    Rev 1.8   12 Oct 1998 16:42:32   tagtrak
* OR: 12682 12683 12678 12654 12419 12265 12805 12285
* CR: 12682 12683 12678 12654 12419 12265 12805
* REV:
* Vienna enhancements/fixes for database V0.4.1E
* branch ATS revision moved to trunk.
* Andrew Walker
* 
*    Rev 1.7.1.0   25 Sep 1998 12:33:22   tagtrak
* OR: 12682 12683 12678 12654 12419 12265 12285
* CR: 12682 12683 12678 12654 12419 12265
* REV:
* Vienna enhancements for Database 0.4.1
* Andrew Walker
* 
*    Rev 1.6.2.0   18 Aug 1998 09:34:46   tagtrak
* OR: 12484
* CR: 12484
* REV:
* Merging Vienna (0.3.1E) and Core Product (0.2.5E).
* Andrew Walker
* 
*    Rev 1.6.1.0   04 Jun 1998 15:31:36   tagtrak
* OR:
* CR: 12098
* REV:
* Vienna Modifications
* Andrew Walker
* 
*    Rev 1.6   02 Apr 1998 19:32:24   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.5   19 Mar 1998 08:19:48   tagtrak
* OR:
* CR:
* REV:
* Did not take into account of the delete marker
* when resolving duplicate bags.  Also doesn't allow
* for a non RUSH, GATE or CREW bag to be cleared.
* Neil Bishop.
* 
*    Rev 1.4   18 Mar 1998 18:14:14   tagtrak
* OR:
* CR:
* REV:
* Did not take into account of the delete marker when resolving
* duplicate bags.  Now checks the delete marker of all
* tags when clearing a bag.  Neil Bishop.
* 
*    Rev 1.3   12 Mar 1998 13:38:42   tagtrak
* OR:
* CR:
* REV:
* This procedure now does a 9 digit bag tag match instead of
* a 10 digit bag_tag match.  Neil Bishop.
* 
*    Rev 1.2   10 Mar 1998 15:38:54   tagtrak
* OR:
* CR:
* REV:
* Now checks the number of bags to be cleared is
* not greater than the number of bags for that pax.
* If it is greater than the number of bags for that
* pax, it will return -701 (TOO_MANY_BAGS).  Neil Bishop.
* 
*    Rev 1.1   26 Feb 1998 14:06:46   tagtrak
* OR:
* CR:
* REV:
* Change to check number of bags the Pax has.
* 
*    Rev 1.0   26 Feb 1998 10:19:24   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
* 
*    Rev 1.3   15 Jan 1998 12:11:28   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.2   24 Oct 1997 11:02:34   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT create or replace procedure clear_bag

create or replace procedure clear_bag (
        p_user_id        in  tagtrak_users.user_id%TYPE,
        p_fltid          in  flight.flight_id%TYPE,
        p_pax_id         in  pax.pax_id%TYPE,
        p_tag            in  bag.bag_tag%TYPE,
        p_no_of_bags     in  number,
        codeshare_fid    in  number,
        p_bags_remaining out number,
        p_retval         out number
)
as 

        flight_num              flight.flight_no%TYPE;
        alp_code                        carrier_lookup.alpha_code%TYPE;
        bag_exception bag.bag_exception%TYPE;
        delete_marker bag.delete_marker%TYPE;
        paxid         bag.pax_id%TYPE;
        fltid         flight.flight_id%TYPE;
        tag1          bag.bag_tag1%TYPE;
        tag2a         bag.bag_tag2a%TYPE;
        tag2b         bag.bag_tag2b%TYPE;
        tag3          bag.bag_tag3%TYPE;
        inits         pax.pax_initials%type;
        full_inits    pax.pax_full_inits%type;


        retval                                                                          number;
        bags_on_flight            number;
        bags_to_delete            number;
        bag_tag_count             number;
        tag_type                  number;
        deleted_tags              number;
        invalid_bag_tag           number;
        match_pax                 number;
        last_pax_id               number;

        access_check_ret_val      integer;

        support_tag_crew_bags                   char;
        tagno_num                 char(10);

        /* Declare Cursors here */
        cursor c1 (t2b in char, t3 in number) is
                select
                        b.bag_exception,
                        b.delete_marker,
                        b.pax_id,
                        p.flight_id
                from
                        bag b,
                        pax p
                where
                        b.bag_tag2b = t2b and
                        b.bag_tag3  = t3  and
                        b.pax_id    = p.pax_id and
      b.bag_source_location = multi_site.get_user_loc(p_user_id);

        cursor c2 (t2b in char, t3 in number, pfltid in number) is
                select
                        b.bag_exception,
                        b.delete_marker,
                        b.pax_id,
                        p.booked_flight_id
                from
                        bag b,
                        pax p
                where
                        b.bag_tag2b = t2b    and
                        b.bag_tag3  = t3     and
                        b.pax_id    = p.pax_id and
                        p.flight_id = pfltid;

        cursor c3 (pfltid in number, ppaxid in number) is
                select
                        delete_marker,
                        flight_id,
                        pax_id
                from
                        pax
                where
                        flight_id       = pfltid and
                        pax_id          = ppaxid and
                        pax_sequence_no = 997;

        cursor c4 (pfltid in number) is
                select
                        delete_marker,
                        flight_id,
                        pax_id
                from
                        pax
                where
                        flight_id       = pfltid and
                        pax_sequence_no = 997;

        cursor c5 (t2b in char, t3 in number, ppaxid in number) is
                select
                        b.bag_exception,
                        b.delete_marker,
                        p.flight_id
                from
                        bag b,
                        pax p
                where
                        b.bag_tag2b = t2b    and
                        b.bag_tag3  = t3     and
                        b.pax_id    = ppaxid and
                        b.pax_id    = p.pax_id;

begin

        p_retval         := 0;
        p_bags_remaining := 0;
        retval           := 0;
        bag_tag_count    := 0;
        deleted_tags     := 0;
        invalid_bag_tag  := 0;
        match_pax        := 0;
        last_pax_id      := 0;

        if (p_tag != '-') then
                /* split the tags up first */
                split_tags (
                        p_tag,
                        tag1,
                        tag2a,
                        tag2b,
                        tag3,
                        tagno_num,
                        tag_type
                );
        end if;

        /*
         * The user has access rights, so check to see if we have a
         * DUPLICATE_TAG, UNRESOLVABLE_TAG or DELETED_BAG
         */

        if (p_tag != '-' and p_fltid = 0 and p_pax_id = 0) then
                /* We have just the tag number, so use it */
                for c1_rec in c1 (tag2b, tag3) loop
                        if (c1_rec.delete_marker = 1) then
                                /* Increment the deleted tags */
                                deleted_tags := deleted_tags + 1;
                        elsif (c1_rec.delete_marker = 0 and
                                        nvl(c1_rec.bag_exception, '-') != 'RUSH' and
                                        nvl(c1_rec.bag_exception, '-') != 'CREW' and
                                        nvl(c1_rec.bag_exception, '-') != 'MAIL' and
                                        nvl(c1_rec.bag_exception, '-') != 'DIPL' and
                                        nvl(c1_rec.bag_exception, '-') != 'GATE') then
                                invalid_bag_tag := invalid_bag_tag + 1;
                        else
                                /* Bag tag must be 0 and bag_exception is RUSH, CREW or GATE */
                                bag_tag_count := bag_tag_count + 1;
                                paxid         := c1_rec.pax_id;
                                fltid         := c1_rec.flight_id;
                                bag_exception := c1_rec.bag_exception;
                        end if;
                end loop;

                dbms_output.put_line( 'bag_tag_count = '||bag_tag_count );
                dbms_output.put_line( 'tag3 = '||tag3 );
                dbms_output.put_line( 'tag2b = '||tag2b );

                /* operation id 6 = clear bag */
                validate_action.check_access(p_user_id, fltid, '6', '', access_check_ret_val);

                if (access_check_ret_val = -1031) then

                        /* ACCESS_DENIED */
                        p_retval := -1031;
                        p_bags_remaining := 0;

                        -- There is no point in continuing as the user does not have access, so return
                        return;

                end if;

                /* Now do the checks */
                if (invalid_bag_tag > 0 and bag_tag_count < 1) then
                        /* WRONG_BAG_TYPE */
                        p_retval := -1038;
                        return;
                elsif (bag_tag_count = 0 and deleted_tags > 0) then
                        /* DELETED_TAG */
                        p_retval := -1019;
                        return;
                elsif (bag_tag_count > 1) then
                        /* DUPLICATE_TAG */
                        p_retval := -1003;
                        return;
                elsif (deleted_tags = 0 and bag_tag_count = 0 and invalid_bag_tag = 0) then
                        /* UNKNOWN_TAG */
                        p_retval := -1002;
                        return;
                end if;

        elsif (p_fltid != 0 and p_tag != '-' and p_pax_id = 0) then
                /* We have the flight_no and bag tag */
                for c2_rec in c2 (tag2b, tag3, p_fltid) loop
                        if (c2_rec.delete_marker = 1) then
                                /* Increment the deleted tags */
                                deleted_tags := deleted_tags + 1;
                        elsif (c2_rec.delete_marker = 0 and
                                        nvl(c2_rec.bag_exception, '-') != 'RUSH' and
                                        nvl(c2_rec.bag_exception, '-') != 'CREW' and
                                        nvl(c2_rec.bag_exception, '-') != 'DIPL' and
                                        nvl(c2_rec.bag_exception, '-') != 'MAIL' and
                                        nvl(c2_rec.bag_exception, '-') != 'GATE') then
                                invalid_bag_tag := invalid_bag_tag + 1;
                        elsif ( codeshare_fid = 0 or
                                c2_rec.booked_flight_id = codeshare_fid ) then
                                /* Bag tag must be 0 and bag_exception is RUSH, CREW or GATE */
                                bag_tag_count := bag_tag_count + 1;
                                paxid         := c2_rec.pax_id;
                                bag_exception := c2_rec.bag_exception;

                                if ( paxid != last_pax_id ) then
                                        match_pax := match_pax + 1;
                                        last_pax_id := paxid;
                                end if;

                        end if;
                end loop;

                /* Now do the checks */
                if (invalid_bag_tag > 0 and bag_tag_count < 1) then
                        /* WRONG_BAG_TYPE */
                        p_retval := -1038;
                        return;
                elsif (bag_tag_count = 0 and deleted_tags > 0) then
                        /* DELETED_TAG */
                        p_retval := -1019;
                        return;
                elsif (bag_tag_count > 1) then
                        if ( match_pax > 1  and codeshare_fid = 0 ) then
                                /* Duplicate PAx Sequence No on Flight */
                                /*p_retval := -1039;*/
                                /* Scanner wants unresolvable duplicate here. */
                                p_retval := -1021;
                        else
                                /* UNRESOLVABLE DUPLICATE */
                                p_retval := -1021;
                        end if;
                        return;
                elsif (deleted_tags = 0 and bag_tag_count = 0 and invalid_bag_tag = 0) then
                        /* UNKNOWN_TAG */
                        p_retval := -1002;
                        return;
                end if;

        elsif (p_fltid != 0 and p_tag = '-' and p_pax_id != 0) then
                /* We have the flight_no and bag tag */
                for c3_rec in c3 (p_fltid, p_pax_id) loop
                        if (c3_rec.delete_marker = 1) then
                                /* Increment the deleted tags */
                                deleted_tags := deleted_tags + 1;
                        elsif (c3_rec.delete_marker = 0) then
                                bag_tag_count := bag_tag_count + 1;
                                paxid         := c3_rec.pax_id;
                                fltid         := c3_rec.flight_id;
                                bag_exception := 'CREW';
                        end if;
                end loop;

                /* Now do the checks */
                if (bag_tag_count = 0 and deleted_tags > 0) then
                        /* DELETED_TAG */
                        p_retval := -1019;
                        return;
                elsif (bag_tag_count > 1) then
                        /* DUPLICATE TAG */
                        p_retval := -1003;
                        return;
                elsif (deleted_tags = 0 and bag_tag_count = 0) then
                        /* TAGGED_CREW_BAG_REQUIRED */
                        p_retval := -1026;
                        return;
                end if;

        elsif (p_fltid != 0 and p_tag = '-' and p_pax_id = 0) then
                /* We have a flight_id and the number of tags we wish to delete */
                for c4_rec in c4 (p_fltid) loop
                        if (c4_rec.delete_marker = 1) then
                                /* Increment the deleted tags */
                                deleted_tags := deleted_tags + 1;
                        elsif (c4_rec.delete_marker = 0) then
                                bag_tag_count := bag_tag_count + 1;
                                paxid         := c4_rec.pax_id;
                                fltid         := c4_rec.flight_id;
                                bag_exception := 'CREW';
                        end if;
                end loop;

                /* Now do the checks */
                if (bag_tag_count = 0 and deleted_tags > 0) then
                        /* DELETED_TAG */
                        p_retval := -1019;
                        return;
                elsif (bag_tag_count > 1) then
                        /* DUPLICATE TAG */
                        p_retval := -1003;
                        return;
                elsif (deleted_tags = 0 and bag_tag_count = 0) then
                        /* TAGGED_CREW_BAG_REQUIRED */
                        p_retval := -1026;
                        return;
                end if;

        else
                /* Must be pax_id and tag_no from the scanner */
                for c5_rec in c5 (tag2b, tag3, p_pax_id) loop
                        if (c5_rec.delete_marker = 1) then
                                /* Increment the deleted tags */
                                deleted_tags := deleted_tags + 1;
                        elsif (c5_rec.delete_marker = 0 and
                                        nvl(c5_rec.bag_exception, '-') != 'RUSH' and
                                        nvl(c5_rec.bag_exception, '-') != 'CREW' and
                                        nvl(c5_rec.bag_exception, '-') != 'MAIL' and
                                        nvl(c5_rec.bag_exception, '-') != 'DIPL' and
                                        nvl(c5_rec.bag_exception, '-') != 'GATE') then
                                invalid_bag_tag := invalid_bag_tag + 1;
                        else
                                /* Bag tag must be 0 and bag_exception is RUSH, CREW or GATE */
                                bag_tag_count := bag_tag_count + 1;
                                fltid         := c5_rec.flight_id;
                                bag_exception := c5_rec.bag_exception;
                        end if;
                end loop;


                /* Now do the checks */
                if (invalid_bag_tag > 0 and bag_tag_count < 1) then
                        /* WRONG_BAG_TYPE */
                        p_retval := -1038;
                        return;
                elsif (bag_tag_count = 0 and deleted_tags > 0) then
                        /* DELETED_TAG */
                        p_retval := -1019;
                        return;
                elsif (bag_tag_count > 1) then
                        /* DUPLICATE TAG */
                        p_retval := -1003;
                        return;
                elsif (deleted_tags = 0 and bag_tag_count = 0 and invalid_bag_tag = 0) then
                        /* UNKNOWN_TAG */
                        p_retval := -1002;
                        return;
                end if;
        end if;

        /* Assign the fltid variable */
        if (p_fltid != 0) then
                fltid := p_fltid;
        end if;

        /* check if the user_id has access rights to this flight */
        user_authentication.validate_access (p_user_id, fltid, '-', '-' ,retval);

        if (retval != 0) then
                p_retval := retval;
        end if;

        /* If we have an error here, then return it */
        if (retval != 0) then
                p_retval := retval;
                return;
        end if;

        /*
         * Assign both paxid and fltid variables
         */

        if (p_pax_id != 0) then
                paxid := p_pax_id;
        end if;

        if (p_fltid != 0) then
                fltid := p_fltid;
        end if;


        /* Get the flight_no from the flight table */
        begin
                select
                        flight_no
                into
                        flight_num
                from
                        flight
                where
                        flight_id = fltid;

                exception
                        when NO_DATA_FOUND then
                                retval := -851;
                                return;
        end;

        if (bag_exception = 'CREW') then
                /* Check if this airline supports untagged crew bags */
                alp_code := substr(flight_num, 1, 3);

                begin
                        select
                                tag_crew_bags
                        into
                                support_tag_crew_bags
                        from
                                carrier_lookup
                        where alpha_code = alp_code;

                        exception
                                when NO_DATA_FOUND then
                                        support_tag_crew_bags := 'Y';
                end;

                /* Work out if the right parameter is added */
                if (support_tag_crew_bags = 'N' and p_tag != '-') then
                        p_retval := -1024;
                        return;
                elsif (support_tag_crew_bags = 'Y' and p_tag = '-') then
                        p_retval := -1026;
                        return;
                end if;
        end if;


        /* Check the number of bags we wish to delete is not greater */
        /* than the number of bags for the flight !!!                */
        begin
                select
                        nvl(no_of_bags, 0)
                into
                        bags_on_flight
                from
                        pax
                where
                        flight_id     = fltid and
                        pax_id        = paxid and
                        delete_marker = 0;
                exception
                        when NO_DATA_FOUND then
                                p_retval := -1010;
                                return;
        end;

        /* Initialise the bags_to_delete variable */
        if (p_tag != '-') then
                bags_to_delete := 1;
        else
                bags_to_delete := p_no_of_bags;
        end if;

        if (bags_on_flight < bags_to_delete) then
                /* TOO_MANY_BAGS */
                p_retval := -701;
                return;
        end if;

        /*
         * Now do the updates              BAG TAG   SUPPLIED
         */

        if (p_tag != '-') then
                /* Set the delete marker of the bag to Delete */
                /* Update the bag */
                update
                        bag
                set
                        delete_marker = 1
                where
                        bag_tag2b     = tag2b   and
                        bag_tag3      = tag3    and
                        pax_id        = paxid   and
                        delete_marker = 0;

                if (SQL%NOTFOUND) then
                        /* Bag not found */
                        p_retval := -1002;
                        return;
                end if;

                /* Decrement the number of bags in the pax table */
                
                        update
                                pax
                        set
                                no_of_bags = no_of_bags - 1
                        where
                                pax_id          = paxid and
                                flight_id       = fltid;

                        if (SQL%NOTFOUND) then
                                /* Pax not found */
                                p_retval := -1010;
                                return;
                        end if;
                
        else
				 /*   NO  BAGTAG  SUPPLIED    exception MUST be CREW */
                if (bag_exception = 'CREW') then
                        update
                                pax
                        set
                                no_of_bags = no_of_bags - p_no_of_bags
                        where
                                pax_id          = paxid and  
                                flight_id       = fltid and
                                pax_sequence_no = 997;

                        if (SQL%NOTFOUND) then
                                /* Pax not found */
                                p_retval := -1010;
                                return;
                        end if;
                else
										/*  Only Crew bags can be untagged */
                        /* TAGGED_CREW_BAG_REQUIRED */
                        p_retval := -1026;
                        return;
                end if;
        end if;

        /* Now check the number of bags remaining for this pax */
        /* If it is 0, then delete the pax.                    */

        if (p_tag != '-') then
                bag_tag_count := 0;
                select
                        nvl(count(bag_tag), 0)
                into
                        bag_tag_count
                from
                        bag
                where
                        pax_id        = paxid and
                        delete_marker = 0;
        else
                begin
                        select
                                nvl(no_of_bags, 0)
                        into
                                bag_tag_count
                        from
                                pax
                        where
                                pax_id = paxid;

                        exception
                                when NO_DATA_FOUND then
                                        bag_tag_count := NULL;
                end;
        end if;


        /*
         * After clearing a Gate bag, mark this pax deleted if
         * there are no bags remaining.
         */
        if ( bag_tag_count <= 0 and (bag_exception = 'GATE' or bag_exception = 'RUSH')) then
        
                begin
                        update
                                pax
                        set
                                delete_marker = 1
                        where
                                pax_id = paxid;

                        exception
                                when NO_DATA_FOUND then
                                        NULL;
                end;

        end if;


        /* Get the number of bags remaining in for the flight */
  begin
                select
                        nvl(sum(no_of_bags), 0)
                into
                        p_bags_remaining
                from
                        pax
                where
                        flight_id       = fltid and
                        pax_sequence_no = '997' and
                        delete_marker   = 0;

                exception
                        when NO_DATA_FOUND then
                                p_bags_remaining := 0;
        end;

end clear_bag;
/
