/*****************************************************************************
*
* Filename         - $Workfile:   stop_and_unstop.sql  $
*
*                     Name              Date      Version     CR Number
*                     Martin Helliwell 04/03/02     1.2         15572
* 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:   stop_and_unstop.sql  $  $Revision:   1.3  $
*
* 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/stop_and_unstop.svl  $
* 
*    Rev 1.3   01 Aug 2002 13:54:42   yomi
* DESC: Timezone change
* CR:   15508
* 
*    Rev 1.2   07 Mar 2002 11:06:10   martin
* OR:
* CR: 15572
* REV: 1.2
* DESC: Distinguish column names and parameter names
*
*****************************************************************************/
PROMPT create or replace package stop_and_unstop_bag

create or replace package stop_and_unstop_bag
as

procedure check_bag_count
(
  fid								 in    flight.flight_id%type,
	tag3               in    bag.bag_tag3%type,
	tag2a              in    bag.bag_tag2a%type,
	tag2b              in    bag.bag_tag2b%type,
	seq_no             in    pax.pax_sequence_no%type,
	paxid              out   pax.pax_id%type,
	diff_flight        out   number,
	bagcount           out   number
);

procedure  change_atl_brs
(
	v_tag3               in    bag.bag_tag3%type,
	v_tag2a              in    bag.bag_tag2a%type,
	v_tag2b              in    bag.bag_tag2b%type,
	atl                  in    bag.authority_to_load_brs%type
);


procedure add_stop_record
( 
  flight_id          in    flight.flight_id%type,
  tag3               in    bag.bag_tag3%type,
  tag2a              in    bag.bag_tag2a%type,
  tag2b              in    bag.bag_tag2b%type,
  loc                in    bag_trace.location_code%type,
  pin                in    bag_trace.user_id%type,
  action             in    bag_trace.trace_action%type
 );

procedure update_atl
(
  fid                in    flight.flight_id%type,
	tag3               in    bag.bag_tag3%type,
	tag2a              in    bag.bag_tag2a%type,
	tag2b              in    bag.bag_tag2b%type,
	atl                in    bag.authority_to_load_brs%type
);

end stop_and_unstop_bag;

/

PROMPT create or replace package body stop_and_unstop_bag

create or replace package body stop_and_unstop_bag
as

   procedure check_bag_count(
	 fid								in    flight.flight_id%type,
	 tag3               in    bag.bag_tag3%type,
	 tag2a              in    bag.bag_tag2a%type,
	 tag2b              in    bag.bag_tag2b%type,
	 seq_no             in    pax.pax_sequence_no%type,
	 paxid              out   pax.pax_id%type,
	 diff_flight        out   number,
	 bagcount           out   number
 )
is

   bag_counter            number(10) := 0;
   fid_counter            number(10) := 0;
   flt_id                 flight.flight_id%type := -1 ; 

	 cursor count_bag is
			select
              p.pax_id pax,
              bag_tag1,
              bag_tag2a,
              bag_tag2b,
              bag_tag3,
              flight_id
        from
              bag b,
              pax p
       where
              p.pax_id        = b.pax_id
         and
              p.flight_id     = fid
         and
              bag_tag3        = tag3
         and
              b.delete_marker = 0
         and
              (bag_tag2a      = tag2a
               or
               bag_tag2b       = tag2b); 

begin


         For count_bag_rec IN count_bag Loop


                if flt_id <> count_bag_rec.flight_id then

                     fid_counter := fid_counter + 1;
                     flt_id      := count_bag_rec.flight_id; 
                 
                end if;

                bag_counter := bag_counter + 1;

                paxid       := count_bag_rec.pax;
         End Loop; 

   
         diff_flight := fid_counter;
         bagcount    := bag_counter;

end  check_bag_count;



procedure change_atl_brs
(
  v_tag3               in    bag.bag_tag3%type,
	v_tag2a              in    bag.bag_tag2a%type,
  v_tag2b              in    bag.bag_tag2b%type,
	atl                  in    bag.authority_to_load_brs%type
) 
is

begin

	
			update bag
				 set authority_to_load_brs = atl
       where bag_tag3   = v_tag3
				 and (bag_tag2a = v_tag2a
							or
							bag_tag2b = v_tag2b
             );

      commit;

end change_atl_brs;


procedure add_stop_record
(
  flight_id          in    flight.flight_id%type,
  tag3               in    bag.bag_tag3%type,
  tag2a              in    bag.bag_tag2a%type,
  tag2b              in    bag.bag_tag2b%type,
  loc                in    bag_trace.location_code%type,
  pin                in    bag_trace.user_id%type,
  action             in    bag_trace.trace_action%type
)is

 v_fid    flight.flight_id%type;
 v_fno    flight.flight_no%type;
 v_fda    flight.flight_date%type;
 v_pid    pax.pax_id%type;
 depair   pax.departure_airport_code%type := null;


 cursor bagcount is
        select p.pax_id pax,
               bag_tag, 
               bag_tag1, 
               bag_tag2a,
               bag_tag2b,
               bag_tag3,
               flight_id
          from bag b, pax p
         where p.pax_id        = b.pax_id
           and p.flight_id     = flight_id
           and bag_tag3        = tag3
           and b.delete_marker = 0
           and (bag_tag2a      = tag2a
                or
                bag_tag2b      = tag2b
               );  
               


 
begin

  depair := get_offset.get_user_location(pin);

  For bagcount_rec IN bagcount Loop


       insert into bag_trace
         ( pax_id,
           bag_trace_time,
           user_id,
           location_code,
           error_id,
           trace_action,
           scanner_id,
           bag_tag2b,
           bag_tag3,
           flight_id_scan,
           bag_tag1,
           bag_tag,
           bag_tag2a
         ) values
         ( bagcount_rec.pax,
           sysdate + get_offset.airport_code(depair),
           pin,
           loc,
           0,
           action,
           '0',
           bagcount_rec.bag_tag2b,
           bagcount_rec.bag_tag3,
           bagcount_rec.flight_id,
           bagcount_rec.bag_tag1,
           bagcount_rec.bag_tag,
           bagcount_rec.bag_tag2a
         );

  End Loop;


end add_stop_record;

procedure update_atl
(
	fid                in    flight.flight_id%type,
	tag3               in    bag.bag_tag3%type, 
	tag2a              in    bag.bag_tag2a%type,
	tag2b              in    bag.bag_tag2b%type,
	atl                in    bag.authority_to_load_brs%type
) is

  Type tabrec is Record
	(
	 v_paxid   pax.pax_id%type
  );


	Type tabtyp is table of tabrec
							index by Binary_integer;

  vtabtyp   tabtyp;
  counter   number(10) := 0;


	cursor get_bag is
				 select p.pax_id
					 from pax p , bag b
          where p.pax_id     = b.pax_id
						and p.flight_id  = fid
						and b.bag_tag3   = tag3
						and (b.bag_tag2a = tag2a
								 or
								 b.bag_tag2b = tag2b
                );


begin


	 For rec in get_bag Loop

	 counter := counter + 1 ;
	 vtabtyp(counter).v_paxid := rec.pax_id;

	 End Loop;


	 if counter != 0 then

	 For j IN 1..vtabtyp.count Loop

				 update bag
						set authority_to_load_brs = atl
          where pax_id = vtabtyp(j).v_paxid;

   End Loop;

	 end if;

end update_atl;
             
end stop_and_unstop_bag;

/
