/*****************************************************************************
*
* Filename         - $Workfile:   erase_records.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 Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1999
*
*****************************************************************************/
/****************************************************************************
* 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.
*
****************************************************************************/

PROMPT create or replace package erase_records

create or replace package erase_records
is

	/*
	 * - Declare Procedures and functions
	 */

	/*
	 * erase_flight_record :- Accepts a Flight_ID as an input parameter and
	 * does a bottom-up delete of the records.  Used for Archive and Backup.
	 * Neil Bishop.  1/6/99.
	 */
	procedure erase_flight_record (
		p_flight_id  in flight.flight_id%type
	);

	/*
	 * erase_pax_record :- Accepts a pax_id as an input parameter and
	 * does a bottom-up delete of the records.  Used for Archive and Backup.
	 * Neil Bishop.  1/6/99.
	 */
	procedure erase_pax_record (
		fl_id  in flight.flight_id%type
	);

end erase_records;
/

PROMPT create or replace package body erase_records

create or replace package body erase_records is

	/*
	 * erase_flight_record :- Accepts a Flight_ID as an input parameter and
	 * does a bottom-up delete of the records.  Used for Archive and Backup.
	 * The commit's have been added because without them, the main tables
	 * such as bag and pax become locked, so the applications cannot access
	 * the database.
	 * We also need to grant execute to the ARCH schema because when this
	 * package is called from db_arbk.pc, it is run from the ARCH schema.
	 * Neil Bishop.  1/6/99.
	 */
	procedure erase_flight_record (
		p_flight_id  in flight.flight_id%type
	)
	is
	begin
		/*
		 * Delete from the bottom-up.
		 */

                /* Delete from handler_trace */

                delete from handler_trace where
                   flight_id = p_flight_id;

                /* Delete from tracking_log */

                delete from tracking_log where
                   flight_id = p_flight_id;

		/*
		 * Loop around the bag_trace, deleting so that
		 * no locks are accumulated.
		 */


		delete from
			bag_trace
		where
			flight_id_scan = p_flight_id;

		commit;


		delete from
			flight_dests
		where
			flight_id = p_flight_id;

		commit;

		delete from
			flight_status
		where
			flight_id = p_flight_id;

		commit;

		delete from
			flight_trace
		where
			flight_id = p_flight_id;

		commit;

		delete from
			fs_events
		where
			flight_id = p_flight_id;

		commit;

		delete from
			mba_flights
		where
			flight_id = p_flight_id;

		commit;

		delete from
			uld_dests
		where
			flight_id = p_flight_id;

		commit;

		delete from
			uld_trace
		where
			flight_id = p_flight_id;

		commit;

		delete from
			flight_access
		where
			flight_id = p_flight_id;

		commit;

            delete from 
                   hbmd
             where 
                 flight_id = p_flight_id;

             commit;
             
                delete from
                   unknown_bag
                where
                   flight_id = p_flight_id;
                commit;

		
		/*
		 * Now loop around the pax records to delete the pax.
		 * Its better to use a cursor than a join on the delete statement
		 * because it doesn't lock up the pax table.
		 */


		erase_records.erase_pax_record (p_flight_id);

		delete from	
			bag
		where   
			NVL(uld_flight_id, 0) = p_flight_id;

		commit;

		delete from
			ulds
		where
			flight_id = p_flight_id;

		commit;

		/*
		 * Now delete the flight record.
		 */


		delete from
			flight
		where
			flight_id = p_flight_id;

		commit;

	end erase_flight_record;



	/*
	 * erase_pax_record :- Accepts a pax_id as an input parameter and
	 * does a bottom-up delete of the records.  Used for Archive and Backup.
	 * Neil Bishop.  1/6/99.
	 */
	procedure erase_pax_record (
		fl_id  in flight.flight_id%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 c1 is 
				 select   pax_id
					 from   pax
					 where  flight_id = fl_id; 
	begin

	For c1_rec in c1 Loop

	counter := counter + 1;

	vtabtyp(counter).v_paxid  := c1_rec.pax_id;

		/*
		 * Bottom-up approach.
		 */


		delete from
			onward_flight
		where
			pax_id = c1_rec.pax_id; 

		commit;

		delete from
			bag
		where
			pax_id = c1_rec.pax_id;

		commit;

 End Loop;

 if counter != 0 then

 For j IN 1..vtabtyp.count Loop

		  delete from pax
			where pax_id = vtabtyp(j).v_paxid;

			commit;

 End Loop;

 end if;

	end erase_pax_record;

end erase_records;
/
grant execute on erase_records to arch;

