/*****************************************************************************
*
* Filename         - $Workfile:   add_loaded_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:   add_loaded_bag.sql  $  $Revision:   1.7  $
*
* 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:   /shared/pvcs/aisarch/db_ora/db/source/live/procedures/add_loaded_bag.sqv  $
* 
*    Rev 1.7   31 Jul 2002 17:48:20   yomi
* DESC: Timezone change
* CR:   15508
* 
*    Rev 1.7   31 Jul 2002 16:37:56   yomi
* DESC: Timezone change
* CR:   15508
* 
*    Rev 1.6   02 Apr 1998 19:31:30   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.5   26 Feb 1998 10:07:30   tagtrak
* OR:
* CR:
* REV:
* Initial core product version
* 
*    Rev 1.4   15 Jan 1998 12:10:54   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.3   24 Oct 1997 10:59:34   tagtrak
* Added PVCS header information
*
****************************************************************************/


                                    
PROMPT create or replace procedure add_loaded_bag
                                    
create or replace procedure add_loaded_bag (
        uldno         in      ulds.uld_no%TYPE,
        tagno         in      varchar2,
        tag1          in      char,
        tag2a         in out  char,
        tag2b         in out  char,
        tag3          in      number,
        pid           in out  number,
        fid           in      number,
        dest          in      char,
        cls           in      char,
        location_code in      char,
        scanner_id    in      char,
        retval        in out  number
) 

as 

        bsno     number(10); 
        addpax   number(10); 
        proc     char(20); 
        unit     char(10); 
        dcsid    char(7); 
        subunit  char(4);

        /*
         * add_loadall_pax Parameters.
         */
        num_bags pax.no_of_bags%TYPE:=1;

depair      pax.departure_airport_code%type := null;

begin

   depair := get_offset.find_airport_code(fid,  pid);

        /*
         * - Initialise variables
         * - if pid = 0 then
         *   assign addpax to 1
         *   call the add_loadall_pax procedure
         * - if pid > 0 then
         *   select details from the flight table using the flight_id
         *   call the add_bag_to_uld procedure
         *   if bsno = 0 then the retval = -1
         *   else
         *     assign the tag2a and tag2b variables
         *     insert into the bag table details from the parameter list
         *   if addpax = 0 the increment the no_of_bags by 1 in the pax table
         * - else
         *   set retval to -2
         * - if retval = 0 then
         *      retval = pid
         */

        retval := 0;
        bsno   := 0;
        addpax := 0;

        /* no loadall pax yet */
        if (pid = 0) then 
                addpax := 1;
                add_loadall_pax (fid, num_bags, dest, cls, pid);
        end if;


        if (pid > 0) then 
                select 
                        process_name,
                        unit_name,
                        dcsid,
                        subunit_name 
                into
                        proc,
                        unit,
                        dcsid,
                        subunit 
                from
                        flight
                where
                        flight_id =  fid; 

                add_bag_to_uld ( fid, uldno, bsno);
 
                if (bsno = 0) then 
                        /* unknown uld */
                        retval := -1;
                else 
                        if (tag2a = '-') then 
                                tag2a := NULL;
                        end if;
 
                        if (tag2b = '-') then 
                                tag2b := NULL;
                        end if;
 
                        insert into bag ( 
                                bag_tag,
                                pax_id,
                                uld_no,
                                bag_tag1,
                                bag_tag2a,
                                bag_tag2b,
                                bag_tag3,
                                authority_to_load_dcs,
                                authority_to_load_brs,
                                unassociated_bag,
                                bag_destination,
                                loaded_status,
                                bag_sequence_no,
                                bag_source_indicator,
                                time_loaded,
                                time_bag_stored,
                                location_code,
                                scanner_id,
                                process_name,
                                unit_name,
                                dcsid,
                                subunit_name,
                                uld_flight_id
                        ) values ( 
                                tagno,
                                pid,
                                uldno,
                                tag1,
                                tag2a,
                                tag2b,
                                tag3,
                                'Y',
                                'Y',
                                'N',
                                dest,
                                'Y',
                                bsno,
                                'L',
                                SYSDATE  + get_offset.airport_code(depair),
                                SYSDATE  + get_offset.airport_code(depair),
                                location_code,
                                scanner_id,
                                proc,
                                unit,
                                dcsid,
                                subunit,
                                fid
                        );
                end if;
 
                if (addpax = 0) then 
                        update pax set
                                no_of_bags = no_of_bags + 1
                        where
                                pax_id =  pid;
                end if;
        else
                /* no loadalll pax */
                retval := -2;
        end if;
 
        if (retval = 0) then 
                retval := pid;
        end if;
         
end add_loaded_bag;
/
