/*****************************************************************************
*
* Filename         - $Workfile:   reuse_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
*
*****************************************************************************/

PROMPT create or replace procedure reuse_bag

create or replace procedure reuse_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,
        p_dcsid   in char,
        subunit   in char,
        dcstagno  in char,
        conn_type in char,
        btype     in char DEFAULT NULL,
        scs       in bag.security_clearance_status%TYPE default NULL,  /* CA 134 Add Security Clearance Status */
        p_deliv_instr in char,
        wt_ref    in bag.worldtracer_ref%TYPE,
        rv                              out number,
        p_kg_weight IN bag.kg_weight%TYPE DEFAULT 0,
        bvis_subhead in bag.bvis_subheader%TYPE,
	p_security_result in bag.security_result%type default null,
	p_security_reason in bag.security_reason%type default null

) as 


v_fid                        flight.flight_id%TYPE;
depair                       pax.departure_airport_code%TYPE := null;
l_security_clearance_status  bag.security_clearance_status%TYPE := null;
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;
bid                              bag.bag_id%type := 0;

begin
        /*
         * - IF tag2a is not assigned then
         *   Update the bag table, setting the relevant columns where the
         *    pax_id, bag_tag3 and bag_tag2b are the same as the relevant
         *    parameters passed in.
         * - ELSEIF tag2b is not assigned then
         *   Update the bag table, setting the relevant columns where the
         *    pax_id, bag_tag3 and bag_tag2a are the same as the relevant
         *    parameters passed in.
         * - ELSE
         *   Update the bag table, setting the relevant columns where the
         *    pax_id, bag_tag3, bag_tag2a and bag_tag2b are the same as the relevant
         *    parameters passed in.
         */


   rv := 0;

  /* get departing airport code */

  select flight_id
    into v_fid
    from pax
   where pax_id = pid;  

  select flight_no
       , flight_date
  into   v_flt_no
       , v_flt_date
  from   flight
  where  flight_id = v_fid;

  IF bsi = 'T' THEN
   
      SELECT inbound_boarding_point
      INTO   v_inbound_airport_code
      FROM   pax
      WHERE  pax_id = pid;
  
  END IF;

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

  /********************************************************************** 
    CJP 10/03/2004 CA1190 BARNZ Hold Baggage Screening: 
    Use determine_security_status.sql for bag.security_clearance_status.
   **********************************************************************/



    determine_security_status (
                                 v_flt_no
                               , v_flt_date
                               , tagno
                               , bsi
                               , SUBSTR(depair,1,3)
                               , SUBSTR(v_flt_no,1,2)
                               , excep
                               , v_inbound_airport_code
                               , scs /* security_screening */
                               , l_security_clearance_status
                               , rv
                              ); 

        if (tag2a = '-' or tag2a IS NULL) then 

                update
                        bag
                set 
                        bag_tag               = tagno,
                        bag_tag1              = tag1,
                        unassociated_bag      = 'N',
                        authority_to_load_dcs = atldcs,
                        bag_destination       = dest,
                        bag_exception         = excep,
                        bag_source_indicator  = bsi,
                        time_bag_stored       = SYSDATE + get_offset.airport_code(depair),
                        delete_marker         = 0,
                        process_name          = proc,
                        unit_name             = unit,
                        dcsid                 = p_dcsid,
                        subunit_name          = subunit,
                        dcs_bag_tag           = dcstagno,
                        security_clearance_status = l_security_clearance_status,  
                        connection_type       = conn_type,
                        delivery_instr        = p_deliv_instr,
                        bag_type              = btype,
                        worldtracer_ref       = wt_ref,
                        bvis_subheader        = bvis_subhead,
			security_result       = p_security_result,
			security_reason       = p_security_reason
                where
                        pax_id    =  pid  and
                        bag_tag3  =  tag3 and
                        bag_tag2b =  tag2b;

                if(p_kg_weight !=0) then
                    update
                            bag
                    set
                            kg_weight             = p_kg_weight
                    where
                            pax_id    =  pid  and
                            bag_tag3  =  tag3 and
                            bag_tag2b =  tag2b;
                end if;

        elsif (tag2b = '-' or tag2b IS NULL) then 

                update
                        bag
                set 
                        bag_tag               = tagno,
                        bag_tag1              = tag1,
                        unassociated_bag      = 'N',
                        authority_to_load_dcs = atldcs,
                        bag_destination       = dest,
                        bag_exception         = excep,
                        bag_source_indicator  = bsi,
                        time_bag_stored       = SYSDATE + get_offset.airport_code(depair),
                        delete_marker         = 0,
                        process_name          = proc,
                        unit_name             = unit,
                        dcsid                 = p_dcsid,
                        subunit_name          = subunit,
                        dcs_bag_tag           = dcstagno,
                        security_clearance_status = l_security_clearance_status,
                        connection_type       = conn_type,
                        delivery_instr        = p_deliv_instr,
                        bag_type              = btype,
                        worldtracer_ref       = wt_ref,
                        bvis_subheader        = bvis_subhead,
			security_result       = p_security_result,
			security_reason       = p_security_reason
                where
                        pax_id    =  pid  and
                        bag_tag3  =  tag3 and
                        bag_tag2a =  tag2a;

                if(p_kg_weight !=0) then
                    update
                            bag
                    set
                            kg_weight             = p_kg_weight
                    where
                            pax_id    =  pid  and
                            bag_tag3  =  tag3 and
                            bag_tag2a =  tag2a;
                end if;

        else 
                update
                        bag
                set 
                        bag_tag               = tagno,
                        bag_tag1              = tag1,
                        unassociated_bag      = 'N',
                        authority_to_load_dcs = atldcs,
                        bag_destination       = dest,
                        bag_exception         = excep,
                        bag_source_indicator  = bsi,
                        time_bag_stored       = SYSDATE + get_offset.airport_code(depair),
                        delete_marker         = 0,
                        process_name          = proc,
                        unit_name             = unit,
                        dcsid                 = p_dcsid,
                        subunit_name          = subunit,
                        dcs_bag_tag           = dcstagno,
                        security_clearance_status = l_security_clearance_status, 
                        connection_type       = conn_type,
                        delivery_instr        = p_deliv_instr,
                        bag_type              = btype,
                        worldtracer_ref       = wt_ref,
                        bvis_subheader        = bvis_subhead,
			security_result       = p_security_result,
			security_reason       = p_security_reason
                where
                        pax_id    =  pid   and
                        bag_tag3  =  tag3  and
                        bag_tag2a =  tag2a and
                        bag_tag2b =  tag2b;

                if(p_kg_weight != 0) then
                    update
                            bag
                    set
                            kg_weight             = p_kg_weight
                    where
                            pax_id    =  pid   and
                            bag_tag3  =  tag3  and
                            bag_tag2a =  tag2a and
                            bag_tag2b =  tag2b;
                end if;

        end if;

        rv := pid;

       --  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';
                   
                   
        --  If the bag is in the inbound_bag table update the inbound bag status
        update inbound_bag
                 set RESULT_BAG_STATUS = 'AMENDED'
                 where RESULT_BAG_ID  = bid
                   and RESULT_BAG_STATUS = 'CREATED';


 
end reuse_bag;
/
