/*****************************************************************************
*
* Filename         - $Workfile:   codeshare.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 - Codeshare package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   codeshare.sql  $  $Revision:   1.5  $
*
* 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/codeshare.svl  $
* 
*    Rev 1.5   06 Jan 2003 14:35:26   mik
* CR 15778: departing_airport_code added to insert into the flight table.
* 
*    Rev 1.4   03 Jan 2003 15:41:16   mik
* CR: 15778
* 
* Added departing_airport_code support
* 
*    Rev 1.3   21 Jun 2001 14:42:16   tagtrak
* OR:
* CR:
* Re-baseline version 4.0.0 to include patches.
* 
*    Rev 1.2   20 Sep 1999 14:40:24   tagtrak
*  
* 
*    Rev 1.1   12 Oct 1998 16:42:40   tagtrak
* OR: 12682 12683 12678 12654 12419 12265 12805 12285
* CR: 12682 12683 12678 12654 12419 12265 12805
* REV:
* Vienna enhancements/fixes for database V0.4.1E
* branch ATS revision moved to trunk.
* Andrew Walker
* 
*    Rev 1.0.1.0   25 Sep 1998 12:33:30   tagtrak
* OR: 12682 12683 12678 12654 12419 12265 12285
* CR: 12682 12683 12678 12654 12419 12265
* REV:
* Vienna enhancements for Database 0.4.1
* Andrew Walker
* 
*    Rev 1.0   03 Jun 1998 12:19:58   tagtrak
* Initial Check-In to PVCS
*
****************************************************************************/
PROMPT create or replace package codeshare

create or replace package codeshare
is

	procedure maintain_codeshare (
		op_carrier   in    char,
		op_fltno     in    char,
		op_date      in    date,
    siteid       in    number,
		cs_carrier   in    char,
		cs_fltno     in    char,
		cs_date      in    date
	);

	procedure compose_flight_no (
		op_carrier   in    char,
		op_fltno     in    char,
		op_flt       out   char
	);

	procedure update_codeshare (
		cs_id        in    number,
		op_id        in    number
	);

	procedure make_codeshare (
		cs_id        in    number,
		op_id        in    number
	);

	procedure get_operating_flight (
		cs_flt         in    char,
		cs_date        in    char,
    siteid         in    number,
		cs_id          in    number,
		op_flt         out   char,
		op_date        out   char,
		op_id          out   number
	);

	procedure resolve_flight (
		cs_flt         in    char,
		cs_date        in    char,
    siteid         in    number,
		cs_id          in    number,
		op_flt         out   char,
		op_date        out   char,
		op_id          out   number
	);

	procedure add_codeshare_flight (
		fltno          in   char,
		fltdate        in   char,
    siteid         in   number,
		fltstime       in   char,
		op_fid         in   number,
		fltstatus      in   char,
		procname       in   char,
		unitname       in   char,
		dcsid          in   char,
		subunitname    in   char,
		depsecs        in   char,
		dcsfno         in   char,
		fltid          out  number
	);

end codeshare;
/


PROMPT create or replace package body codeshare 

create or replace package body codeshare is

procedure maintain_codeshare (

	op_carrier   in    char,
	op_fltno     in    char,
	op_date      in    date,
  siteid       in    number,
	cs_carrier   in    char,
	cs_fltno     in    char,
	cs_date      in    date

) is

	op_flt char(8);
	cs_flt char(8);
	op_id number;
	cs_id number;
	op_deptime char(20);
	cs_deptime char(20);

	old_op_flt char(8);
	old_op_date char(5);
	old_op_id number;

begin

	/*
	 * Create a standard flight number from the carrier and service number.
	 */
	compose_flight_no( op_carrier, op_fltno, op_flt );
	compose_flight_no( cs_carrier, cs_fltno, cs_flt );

	/*
	 * Check for the existence of the operating flight.
	 */
	flight_id(op_flt, to_char(op_date,'DDMON'), siteid, op_id);
	
	/*
	 * If the operating flight doesn't exist, add it with some dummy values
	 * to represent information which will be updated by the DCS later.
	 */
	if ( op_id <= 0 ) then

		op_deptime := to_char(op_date,'DD-MON-YYYY') || ' 00:00';

		add_codeshare_flight(
			op_flt,
			to_char(op_date, 'DDMON'),
      siteid,
			'0000',
			null,
			'-',
			'FIDS',
			1,
			1,
			1,
			op_deptime,
			null,
			op_id
		);
	
	else

		/*
		 * Ensure this doesn't reference another operating flight.
		 */
		update
			flight
		set
			op_flight_id = null
		where
			flight_id = op_id;

	end if;


	/*
	 * Check for the existence of the code-sharing flight.
	 */
	flight_id(cs_flt, to_char(cs_date,'DDMON'),siteid, cs_id);

	/*
	 * If the codeshare flight doesn't exist, add it with some dummy values
	 * to represent information which will be updated by the DCS later. We
	 * also fill in the Operating Flight ID field here.
	 */
	if ( cs_id <= 0 ) then

		cs_deptime := to_char(op_date,'DD-MON-YYYY') || ' 00:00';

		add_codeshare_flight(
			cs_flt,
			to_char(cs_date, 'DDMON'),
      siteid,
			'0000',
			op_id,
			'-',
			'FIDS',
			1,
			1,
			1,
			cs_deptime,
			null,
			cs_id
		);

	else

		/*
		 * The flight already exists. we need to check if the code-sharing
		 * information has changed at all.
		 */
		get_operating_flight( cs_flt, to_char(cs_date,'DDMON'), siteid, 0,
			old_op_flt, old_op_date, old_op_id );

		if ( old_op_id = 0 ) then
			/*
			 * This was previously an operating flight.
			 */
			make_codeshare( cs_id, op_id );

		elsif ( old_op_id != op_id ) then
			/*
			 * This was previously a code-share but for a
			 * different operating flight
			 */
			update_codeshare( cs_id, op_id );

		end if;


	end if;

end maintain_codeshare;


procedure update_codeshare(
	cs_id        in    number,
	op_id        in    number
) is

	found number;

begin


	/*
	 * Can't change a flight's details once loading has commenced.
	 * (actually when ULDS have been assigned to the flight).
	 */
	select
		count(*)
	into
		found
	from	
		ulds
	where
		flight_id = cs_id;

	if ( found > 0 ) then
		raise_application_error(-20036,
			'Attempt to change Flight codeshare info while loading');
		return;
	end if;
		

	/*
	 * In the flight table, update the flight's operating
	 * flight ID so it references the correct one.
	 */
	update
		flight
	set
		op_flight_id = op_id
	where
		flight_id = cs_id;

	/*
	 * In the pax table, update the pax's operating
	 * flight ID so it references the correct one.
	 */
	update
		pax
	set
		flight_id = op_id
	where
		booked_flight_id = cs_id;


	/*
	flight_access
	*/

end update_codeshare;


procedure make_codeshare (
	cs_id        in    number,
	op_id        in    number
) is

	found number;

begin

	/*
	 * Can't change a flight's details once loading has commenced.
	 * (actually when ULDS have been assigned to the flight).
	 */
	select
		count(*)
	into
		found
	from	
		ulds
	where
		flight_id = cs_id;

	if ( found > 0 ) then
		raise_application_error(-20036,
			'Attempt to change Flight codeshare info while loading');
		return;
	end if;

	/*
	 * In the flight table, update the flight's operating
	 * flight ID so it references the correct one.
	 */
	update
		flight
	set
		op_flight_id = op_id
	where
		flight_id = cs_id;

	/*
	 * In the pax table, update the pax's operating
	 * flight ID so it references the correct one, and
	 * make the booked_flight_ID reference the code share
	 * flight.
	 */
	update
		pax
	set
		flight_id = op_id,
		booked_flight_id = cs_id
	where
		flight_id = cs_id;

	/*
	flight_access
	*/

end make_codeshare;


procedure get_operating_flight(
	cs_flt         in    char,
	cs_date        in    char,
  siteid         in    number,
	cs_id          in    number,
	op_flt         out   char,
	op_date        out   char,
	op_id          out   number
) is

begin


	/*
	 * We can handle looking up the flight either by ID or
	 * by number and date.
	 */
	if ( cs_id <= 0 ) then

		select
			op.flight_id,
			op.flight_no,
			op.flight_date
		into
			op_id,
			op_flt,
			op_date
		from
			flight_outbound_v1 cs, flight_outbound_v1 op
		where
			cs.flight_no   = cs_flt and
			cs.flight_date = cs_date and
      cs.site_id     = siteid  and
			op.flight_id   = cs.op_flight_id;

	else

		select
			op.flight_id,
			op.flight_no,
			op.flight_date
		into
			op_id,
			op_flt,
			op_date
		from
			flight cs, flight op
		where
			cs.flight_id = cs_id and
			op.flight_id = cs.op_flight_id;

	end if;

	exception
		when NO_DATA_FOUND then
			op_flt := '';
			op_date := '';
			op_id := 0;

end get_operating_flight;


PROCEDURE add_codeshare_flight (
    fltno          in   char,
    fltdate        in   char,
    siteid         in   number,
    fltstime       in   char,
    op_fid         in   number,
    fltstatus      in   char,
    procname       in   char,
    unitname       in   char,
    dcsid          in   char,
    subunitname    in   char,
    depsecs        in   char,
    dcsfno         in   char,
    fltid          out  number
) IS
  dbvalue          db_config.db_value%type;

BEGIN

-- First off, we check if the carrier needs to be added.
    carrier.maintain_carrier (substr (fltno,1,3));


    INSERT INTO flight (
        flight_id,
        flight_no,
        flight_date,
        site_id,
        flight_sch_time,
        flight_est_date,
        flight_status,
        process_name,
        unit_name,
        dcsid,
        subunit_name,
        departure_time,
        dcs_flight_no,
        op_flight_id
    )
    VALUES (
        0,
        fltno,
        fltdate,
        siteid, 
        fltstime,
        fltstime,
        fltstatus,
        procname,
        unitname,
        dcsid,
        subunitname,
        to_date (depsecs, 'DD-MON-YYYY HH24:MI'),
        dcsfno,
        op_fid
    );

    flight_id (fltno, fltdate, siteid, fltid);
END add_codeshare_flight;


procedure resolve_flight(
	cs_flt         in    char,
	cs_date        in    char,
  siteid         in    number,
	cs_id          in    number,
	op_flt         out   char,
	op_date        out   char,
	op_id          out   number
) is

	local_op_id number;

begin

	get_operating_flight( 
		cs_flt,
		cs_date,
    siteid,
		cs_id,
		op_flt,
		op_date,
		local_op_id
	);

	op_id := local_op_id;

	/*
	 * If no operating flight was found, we shall use the details of
	 * the codeshare flight instead, as it must be an operating flight
	 * itself.
	 */
	if ( local_op_id = 0 ) then

		/*
		 * We can handle looking up the flight either by ID or
		 * by number and date.
		 */
		select
			flight_id,
			flight_no,
			flight_date
		into
			op_id,
			op_flt,
			op_date
		from
			flight_outbound_v1
		where
			( cs_id > 0 and flight_id = cs_id ) or
			( cs_id <= 0 and flight_no = cs_flt and
			  flight_date = cs_date and site_id = siteid );

	end if;

	exception
		when NO_DATA_FOUND then
			op_flt := '';
			op_date := '';
			op_id := 0;

end resolve_flight;



procedure compose_flight_no (
	op_carrier    in     char,
	op_fltno      in     char,
	op_flt        out    char
) is

begin

	/*
	 * build a flight number.
	 * Not very advanced as it assumes the supplied fltno value
	 * is padded with leading zeroes.
	 */
	op_flt := rpad(op_carrier,3) || ltrim(op_fltno);

end compose_flight_no;

end codeshare;
/
