/*****************************************************************************
*
* Filename         - $Workfile:   add_flight.sql  $
*
*                     Name              Date      Version     CR Number
*											Martin Helliwell	8/8/2				1.9					15666
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - ULTRATRAK(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
*
*****************************************************************************/

/****************************************************************************
 *                                                                          *
 * Stored procedure to add a row to the flight table                        *
 *                                                                          *
 ****************************************************************************/
PROMPT CREATE OR REPLACE PROCEDURE add_flight

CREATE OR REPLACE PROCEDURE add_flight (
	fltno       IN    CHAR,
	fltdate     IN    CHAR,
  depcode     IN    CHAR,
	fltstime    IN    CHAR,
	fltstatus   IN    CHAR,
	procname    IN    CHAR,
	unitname    IN    CHAR,
	dcsid       IN    CHAR,
	subunitname IN    CHAR,
	depsecs     IN    CHAR,
	dcsfno      IN    CHAR,
	fltid       OUT   NUMBER,
        bvis_subhead IN   flight.bvis_subheader%TYPE
) 
AS
	std        flight.flight_sch_time%TYPE;
  v_type     flight.flight_type%TYPE := 'O';
  airport_id  site_info.site_id%type;
  error_code number(20); 
BEGIN
	/*
	 * - Ensure Carrier exists.
	 * - IF fltstime is equal to "-" (NULL VALUE) then
	 *   assign '0000' to fltstime
	 * - insert a record into the flight table using the parameters passed
	 * - Call the procedure flight_id
	 */

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

  airport_id := multi_site.get_site_id(depcode);

	IF (fltstime = '-') THEN 
		std := '0000';
	ELSE
		std := fltstime;
	END IF;

  IF (fltstatus = 'IB') THEN
		 v_type := 'I';
  END IF;
 
	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,
		flight_type,
		security_clearance, -- CR 15666
                bvis_subheader
	) VALUES (
		0,
		fltno,
		fltdate,
		airport_id,
		std,
		std,
		NVL(fltstatus,'-'),	-- Set NULL to invalid status of dash
		procname,
		unitname,
		dcsid,
		subunitname,
		TO_DATE(depsecs, 'DD-MON-YYYY HH24:MI'),
		dcsfno,
		v_type,
		DECODE(security_screening.security_clearance(depcode,fltno),'U','N','Y','Y','N'), -- CR 15666
                bvis_subhead
	)
	RETURNING flight_id INTO fltid; 

	-- Update possible early AOSS traces for this new flight
	--
	if config.get_value('AOSS_ENABLED') = 'Y' then
	    AOSS_Data.Set_Missing_Flight_ID(fltno, fltdate, fltid);
	end if;
END add_flight;
/
