/*****************************************************************************
*
* Filename         - $Workfile:   add_bag.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell  8/8/2                           1.0                                     15644
* 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
*
*****************************************************************************/

PROMPT create or replace procedure add_bag

create or replace procedure add_bag (
        tagno    IN VARCHAR2,
        tag1     IN CHAR,
        tag2a    IN CHAR,
        tag2b    IN CHAR,
        tag3     IN NUMBER,
        pid      IN NUMBER,
        atldcs   IN CHAR,
        dest     IN CHAR,
        bsi      IN CHAR,
        excep    IN CHAR,
        proc     IN CHAR,
        unit     IN CHAR,
        dcsid    IN CHAR,
        subunit  IN CHAR,
        dcstagno IN CHAR,
        uldno    IN CHAR,
        con_type IN CHAR,
        btype    IN CHAR DEFAULT NULL, 
        loc      IN bag_trace.location_code%TYPE,
        user_id  IN bag_trace.user_id%TYPE,
        scs      IN bag.security_clearance_status%TYPE default NULL, /* CA 134 Add Security Clearance Status */
        del_instr                  IN bag.delivery_instr%TYPE,
        wt_ref   IN bag.worldtracer_ref%TYPE,
        p_kg_weight IN bag.kg_weight%TYPE DEFAULT 0,
        bvis_subhead IN bag.bvis_subheader%TYPE,
	auth_source IN bag.authority_source%TYPE default 'DCS', -- FALCP/CA/01979
	p_security_result in bag.security_result%type default null,
	p_security_reason in bag.security_reason%type default null
) 
as  

  local_tag2a live.bag.bag_tag2a%TYPE;
  local_tag2b live.bag.bag_tag2b%TYPE;
  l_depair      pax.departure_airport_code%type := null;
  l_fid     flight.flight_id%type;
  l_nobags  NUMBER;                         -- hold the number of bags returned by delete_bag
  l_loadall_pax  pax.pax_id%TYPE;           -- the pax_id of the loadall passenger
  l_loc     bag_trace.location_code%TYPE;    -- must supply the location when calling other procedures 
  l_user_id bag_trace.user_id%TYPE;         -- needed as an OUT parameter for procedure calls 
  l_return_code  NUMBER;                    -- needed for procedure calls 
  l_uld_no  bag.uld_no%TYPE;                -- the ULD of the existing bag
  l_security_clearance_status  bag.security_clearance_status%TYPE := null; /*  CA 134 Add Security Clearance Status */
        v_flt_no                     flight.flight_no%TYPE := NULL;
  v_flt_date                   flight.flight_date%TYPE := NULL;
  v_inbound_airport_code       pax.inbound_boarding_point%TYPE := NULL;
 
BEGIN
      
        BEGIN
          SELECT 
                 pax.flight_id,
                 pax.departure_airport_code
          INTO   
                 l_fid, l_depair
          FROM   pax
          WHERE  pax.pax_id = pid ;
        EXCEPTION 
          WHEN NO_DATA_FOUND THEN
                 l_depair  := NULL;
                 l_fid     := NULL;
      
        END;

       /********************************************************************** 
        * CJP 10/03/2004 CA1190 BARNZ Hold Baggage Screening: 
        * Use determine_security_status.sql for bag.security_clearance_status.
        **********************************************************************/
 
        select flight_no
             , flight_date
        into   v_flt_no
             , v_flt_date
        from   flight
        where  flight_id = l_fid;
 
        IF bsi = 'T' THEN
   
            SELECT inbound_boarding_point
            INTO   v_inbound_airport_code
            FROM   pax
            WHERE  pax_id = pid;
  
        END IF;
 
        determine_security_status (
                                     v_flt_no
                                   , v_flt_date
                                   , tagno
                                   , bsi
                                   , SUBSTR(l_depair,1,3)
                                   , SUBSTR(v_flt_no,1,2)
                                   , excep
                                   , v_inbound_airport_code
                                   , scs /* security_screening */
                                   , l_security_clearance_status
                                   , l_return_code
                                  );

        /*
         * IF tag2a is blank (ie -) then
         *   do not assign local_tag2a
         * else
         *   assign local_tag2a to tag2a
         * IF tag2b is blank (ie -) then
         *   do not assign local_tag2b
         * else
         *   assign local_tag2b to tag2b
         * Insert a record into the bag table using local_tag2a, local_tag2b
         * and the parameters supplied.
         */
   
        /*
         * We use local vars to hold these values as we can't
         * assign to 'in' parameter variables.
         */
        IF (tag2a = '-') THEN 
          local_tag2a := NULL;
        ELSE
          local_tag2a := tag2a;
        END IF;

        IF (tag2b = '-') THEN 
                local_tag2b := NULL;
        ELSE
                local_tag2b := tag2b;
        END IF;
        
        IF ltrim(loc) IS NULL THEN
          SELECT location_code 
          INTO l_loc
          FROM LOCATION
          WHERE LOCATION_CODE like multi_site.get_auto_location(l_depair)||'%';
        ELSE
          l_loc := loc;
        END IF;

        -- set the user id to a dummy value
        l_user_id := 1;       

        INSERT INTO bag (
                bag_tag,
                pax_id,
                uld_no,
                bag_tag1,
                bag_tag2a,
                bag_tag2b,
                bag_tag3,
                location_code,
                unassociated_bag,
                authority_to_load_dcs,
                bag_destination,
                loaded_status,
                bag_exception,
                bag_source_indicator,
                time_bag_stored,
                process_name,
                unit_name,
                dcsid,
                subunit_name,
                dcs_bag_tag,
                security_clearance_status,
                connection_type,
                bag_type,
                delete_marker,
          delivery_instr,
                worldtracer_ref,
                kg_weight,
                bvis_subheader,
		authority_source,	-- FALCP/CA/01979
		security_result,
		security_reason
		) 
        VALUES (
                tagno,
                pid,
                uldno,
                tag1,
                local_tag2a,
                local_tag2b,
                tag3,
                l_loc,
                'N',
                atldcs,
                dest,
                'N',
                excep,
                bsi,
                SYSDATE + get_offset.airport_code(l_depair),
                proc,
                unit,
                dcsid,
                subunit,
                dcstagno,
                l_security_clearance_status,
                con_type,
                btype,
                0,
                del_instr,
                wt_ref,
                p_kg_weight,
                bvis_subhead,
		auth_source,	-- FALCP/CA/01979
		p_security_result,
		p_security_reason
        ); 

	-- Update possibly early AOSS traces for this bag
	--
        if config.get_value('AOSS_ENABLED') = 'Y' then
	    AOSS_Data.Set_Missing_PAX(local_tag2a, local_tag2b, tag3, pid);
	end if;

        /* If this bag_tag matches one already loaded with the loadall passenger, */
        /* remove that bag and its alerts, then load the bag under the real PAX   */

        /* Get the LOADALL passenger ID, if one exists*/

        BEGIN
          SELECT pax.pax_id, bag.uld_no
          INTO l_loadall_pax, l_uld_no
          FROM pax, bag
          WHERE bag.bag_tag         = tagno  
            AND bag.pax_id          = pax.pax_id
            AND pax.pax_sequence_no = -1 -- LOADALL Pax
            AND pax.flight_id       = l_fid;
        EXCEPTION 
          WHEN OTHERS THEN
            l_loadall_pax := 0;
        END;

        IF l_loadall_pax > 0  THEN

          /* remove the bag loaded under the loadall passenger */
          /* don't use the delete_bag procedure as it will raise */
          /* an UNLOAD alert which is not needed in this case  */

          DELETE FROM bag
          WHERE pax_id  =  l_loadall_pax 
            AND bag_tag =  tagno ;
 
          UPDATE
            pax
          SET 
            no_of_bags = no_of_bags-1
          WHERE
            pax_id =  l_loadall_pax; 

          /**********************************************************/
          /* load_bag will increase the bag count on the ULD        */
          /* So although the bag is not actually moving anywhere    */
          /* the count should be decremented, and then incremented  */
          /**********************************************************/
          UPDATE 
            ulds
          SET 
            uld_no_of_bags = uld_no_of_bags-1 
          WHERE
            uld_no        = l_uld_no 
            AND flight_id = l_fid ;

          /* load the bag under its proper pax_id, record a trace of the load */

          add_bag_trace_record( l_user_id,
                                l_loc,
                                0,    -- error_id, no error
                                tagno,
                                tag1,
                                local_tag2a,
                                local_tag2b,
                                tag3,
                                'BSM RECVD',
                                '0',   -- scanner_id, dummy value
                                l_fid,
                                pid,
                                l_uld_no);    -- pass ULD number (Nigel, 31/03/04, CA598)

          load_bag(local_tag2a,
                   local_tag2b,
                   tag3,
                   pid,
                   l_uld_no,
                   l_loc,
                   '1',
                   l_fid,
                   l_return_code);

          IF atldcs = 'N' OR l_security_clearance_status = 'N' THEN
          -- bag is already loaded, but does not have authority to load or is not security cleared
            INSERT INTO bag_alert (pax_id,
                                   flight_id,
                                   bag_tag,
                                   bag_tag1,
                                   bag_tag2a,
                                   bag_tag2b,
                                   bag_tag3,
                                   no_warnings,
                                   last_time_stamp,
                                   alert_on_action)
            VALUES
                                  (pid,
                                   l_fid,
                                   tagno,
                                   tag1,
                                   local_tag2a,
                                   local_tag2b,
                                   tag3,
                                   1,
                                   SYSDATE + get_offset.airport_code(l_depair),
                                   'UNLOAD');
          END IF;
            
        END IF;  -- loadall_pax > 0

        --  If the bag's pax is in the inbound_pax table update the inbound pax status
        update inbound_pax
                 set RESULT_PAX_STATUS = 'AMENDED'
                 where RESULT_PAX_ID  = pid
                   and RESULT_PAX_STATUS = 'CREATED';


END add_bag;
/
