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

CREATE OR REPLACE procedure modify_flight (
   fltno        in out  char,
   fltdate      in out  char,
  depcode      in out  char,
   fltstime     in out  char,
   fltstatus    in out  char,
   procname     in out  char,
   unitname     in out  char,
   p_dcsid     in out  char,
   subunitname  in out  char,
   depsecs      in out  char,
   dcsfno       in out  char,
   fltid        in out  number,
   last_pax_sno in out  char,
        bvis_subhead in      flight.bvis_subheader%TYPE
)
as
   old_status char(2);
   existing_flt boolean := TRUE;
   use_std db_config.db_value%type;
   psiteid site_info.site_id%type;
   ret     number(10);
   l_flt_type char;
begin

    psiteid := multi_site.get_site_id(depcode);

   /*
    * - Initialise Variable
    * - Call the flight_id procedure to obtain the flight_id
    * - IF the flight id is equal to 0 (ie. it is not found) then
    *   call the add_flight stored procedure to add this flight.
    *   Initialise the last pax sequence number variable to '000'.
    * - ELSE
    *   IF flight time is not equal to '-' then
    *     Update the flight table by setting the flight_est_date to
    *      the fltstime parameter and the flight_sch_time to the
    *      fltstime parameter and the departure date to the depsecs
    *      parameter in the format DD-MON-YYYY HH:MI, where HH is the
    *      24 hour clock notation where the flight_id is equal to the
    *      fltid parameter.
    *   Select the next pax sequence number from the pax table.
    *   IF the pax sequence number is NULL then
    *     Assign '000' to the next pax sequence number
    */

   fltid := 0;


   /*
    * Find out whether we are configured to use scheduled time
    * of departure information sent from a DCS for updates.
    */

   use_std := config.get_value('USE_DCS_STD');

   /*
    * If this is unconfigured, we will default to 'Y'.
    */

   if ( use_std is null ) then
      use_std := 'Y';
   end if;
   
   l_flt_type := 'O';
   if ( fltstatus = 'IB' ) then
      l_flt_type := 'I';
   end if;

   check_flight (
      fltno,
      fltdate,
      psiteid,
      l_flt_type,
      fltid,
      old_status
   );

   if (fltid = 0) then
      existing_flt := FALSE;

      /* need to add flight */
      add_flight (
         fltno,
         fltdate,
      depcode,
         fltstime,
         fltstatus,
         procname,
         unitname,
         p_dcsid,
         subunitname,
         depsecs,
         dcsfno,
         fltid,
         bvis_subhead
      );

      last_pax_sno := '000';

   else
      /*
       * Ensure DCS Address Map Attributes are updated when flight is updated.
       */

      /*
       * If the scheduled time of departure is supplied, and the system
       * is configured to use it, then we will use this in the update.
       */
      if ( substr(use_std,1,1) = 'Y' and fltstime != '-') then
         update
            flight
         set
            flight_status = DECODE (trim(old_status), '-', fltstatus, flight_status),
            flight_est_date = fltstime,
            flight_sch_time = fltstime,
            departure_time  = to_date(depsecs, 'DD-MON-YYYY HH24:MI'),
            process_name = procname,
            unit_name = unitname,
            subunit_name = subunitname,
            dcsid = p_dcsid,
            dcs_flight_no = dcsfno,
            bvis_subheader = bvis_subhead
         where
            flight_id = fltid;
      else
         update
            flight
         set
            flight_status = DECODE (trim(old_status), '-', fltstatus, flight_status),
            process_name = procname,
            unit_name = unitname,
            subunit_name = subunitname,
            dcsid = p_dcsid,
            dcs_flight_no = dcsfno,
            bvis_subheader = bvis_subhead
         where
            flight_id = fltid;
      end if;
   end if;

   if ( existing_flt ) then

                /* Baglink interface may send different BVIS Subheaders for existing flights.
                   We need to ensure that this value is reflected in the flight table (also the
                   bag table). We can't receive a null subheader from Baglink */

                if ( bvis_subhead IS NOT NULL ) then
                    update
                            flight
                    set
                            bvis_subheader = bvis_subhead
                    where
                            flight_id       = fltid;
                end if;


      select
         to_char(max(pax_sequence_no))
      into
         last_pax_sno
      from
         pax
      where
         flight_id        = fltid and
         pax_sequence_no != 999;

      if (last_pax_sno is null) then
         last_pax_sno := '000';
      end if;
   end if;

   -- CA/2250
   -- We may get a DCS flight number for a flight that was created via
   -- FIDS.  A simple blanket check for this is to simply update the
   -- DCS flight number if it is currently NULL and one has been
   -- supplied.
   --
   if dcsfno is not null then
       update flight
      set dcs_flight_no = dcsfno
      where flight_id = fltid and dcs_flight_no is null;
   end if;

end modify_flight;
/


