/*****************************************************************************
*
* Filename         - $Workfile:   insert_inbound_data.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, 2006
*
*****************************************************************************/
CREATE OR REPLACE PACKAGE insert_inbound_data IS


FUNCTION get_next_leg_id
  (p_out_flight_id IN flight.flight_id%TYPE) RETURN flight.flight_id%TYPE;

FUNCTION is_transit_airport
  (p_out_flight_id IN flight.flight_id%TYPE) RETURN BOOLEAN;

FUNCTION is_configured_for_inbound RETURN BOOLEAN;

PROCEDURE insert_inbound_pax(p_in_flight_id IN flight.flight_id%TYPE, p_out_flight_id IN flight.flight_id%TYPE);
PROCEDURE insert_inbound_pax_onward_dest(p_in_flight_id IN flight.flight_id%TYPE,
                                         p_out_flight_id IN flight.flight_id%TYPE,
                                         p_in_pax_id IN pax.pax_id%TYPE);
PROCEDURE insert_inbound_uld(p_in_flight_id IN flight.flight_id%TYPE, p_out_flight_id IN flight.flight_id%TYPE);
PROCEDURE insert_inbound_uld_dests(p_in_flight_id IN flight.flight_id%TYPE,
                                   p_out_flight_id IN flight.flight_id%TYPE,
                                   p_in_uld_id IN inbound_uld.inbound_uld_id%TYPE);
PROCEDURE insert_inbound_bag(p_in_flight_id IN flight.flight_id%TYPE,
                             p_out_flight_id IN flight.flight_id%TYPE);
PROCEDURE insert_all(p_out_flight_id IN flight.flight_id%TYPE);

END insert_inbound_data;
/

----------------------------------------------------------------------------------
CREATE OR REPLACE PACKAGE BODY insert_inbound_data IS

---------------------------------
-- get_next_leg_id
--
FUNCTION get_next_leg_id
  (p_out_flight_id IN flight.flight_id%TYPE) RETURN flight.flight_id%TYPE IS

  l_next_leg_id        flight.flight_id%TYPE DEFAULT NULL;
  l_flight_no          flight.flight_no%TYPE;
  l_flight_date        flight.flight_date%TYPE;
  l_flight_destination flight_dests.flight_destination%TYPE;

BEGIN

  -- find the flight id of the next leg
  -- if it cannot be found (does not exist) then create it
  -- whatever the case return the flight id of the inbound flight (the next leg)
  l_next_leg_id := inbound_flight_utils.match_inbound_outbound_flts(p_out_flight_id);

  -- If the next leg id is null, then this has not been linked to its inbound flight, so link it
  IF (l_next_leg_id IS NULL) THEN

    -- Get some details about the Outbound Flight
    SELECT f.flight_no,
           f.flight_date,
           fd.flight_destination
    INTO   l_flight_no,
           l_flight_date,
           l_flight_destination
    FROM   flight_dests fd,
           flight f
    WHERE  f.flight_id = fd.flight_id
      AND  fd.flight_id = p_out_flight_id
      AND  fd.dest_order = 1;

    -- Add the Inbound Flight
    add_flight(l_flight_no,
               l_flight_date,
               l_flight_destination,                                 -- converts to site id
               '0000',                                               -- flight sch time
               'IB',                                                 -- flight status
               'DUMMY',                                              -- process name
               '1',                                                  -- unit name
               'DUMMY',                                              -- dcs id
               '0',                                                  -- subunit name
               TO_CHAR(inbound_flight_utils.flt_date_to_date(l_flight_date), 'DD-MON-YYYY HH24:MI'), -- departure time
               NULL,                                                 -- dcs flight no
               l_next_leg_id,                                        -- flight id returned as OUT parameter
               NULL                                                  -- bvis subheader
              );

  END IF;

  -- Store the Next Leg Id against the Outbound Flight
  UPDATE flight
  SET    next_leg_id = l_next_leg_id
  WHERE  flight_id = p_out_flight_id;

  RETURN l_next_leg_id;

END get_next_leg_id;

---------------------------------
-- is_transit_airport
--
FUNCTION is_transit_airport
  (p_out_flight_id IN flight.flight_id%TYPE) RETURN BOOLEAN IS

  transit_agents NUMBER;

BEGIN

  -- If there are no agents at the transit airport then
  -- the destination of the initiating flight is not a transit airport on
  -- the system and there is no requirement to insert any inbound data
  SELECT COUNT(*)
  INTO   transit_agents
  FROM   agents a,
         flight_dests fd,
         site_info s
  WHERE  a.site_id = s.site_id
    AND  fd.flight_destination = s.airport_code
    AND  fd.dest_order = 1
    AND  fd.flight_id = p_out_flight_id;

  RETURN (transit_agents > 0);

END is_transit_airport;

---------------------------------
-- is_configured_for_inbound
--
FUNCTION is_configured_for_inbound RETURN BOOLEAN IS

BEGIN

  IF (config.get_value('AUTO_TRANSFER') = 'Y'
      AND config.get_value('MULTI_STATION') = 'Y') THEN
     RETURN TRUE;
  ELSE
     RETURN FALSE;
  END IF;

END is_configured_for_inbound;

---------------------------------
-- insert_inbound_uld
--
PROCEDURE insert_inbound_uld(p_in_flight_id IN flight.flight_id%TYPE, p_out_flight_id IN flight.flight_id%TYPE) IS

  l_in_uld_id inbound_uld.inbound_uld_id%TYPE;

  CURSOR c_ulds (p_flight_id flight.flight_id%TYPE) IS
         SELECT uld_no
         FROM   ulds
         WHERE  flight_id = p_flight_id
           AND  uld_no_of_bags > 0;

BEGIN

  FOR r_uld IN c_ulds(p_out_flight_id) LOOP

    INSERT INTO inbound_uld
    (
      inbound_uld_id,
      inbound_flight_id,
      source_uld_no,
      source_message_id,
      result_uld_no,
      result_uld_flight_id,
      result_uld_status,
      error_code,
      inbound_uld_status,
      uld_number,
      uld_type,
      uld_category,
      uld_position
    ) VALUES (
      0,                                -- zero replaced by trigger with value from sequence
      p_in_flight_id,
      r_uld.uld_no,
      NULL,                             -- source message id
      NULL,                             -- result uld no
      NULL,                             -- result uld flight id
      'UNPROCESSED',
      NULL,                             -- error code
      'TERMINATING',                    -- assume terminating for present
      NULL,                             -- uld_no
      NULL,                             -- uld type
      NULL,                             -- uld category
      NULL                              -- uld hold no
    ) RETURN inbound_uld_id
      INTO   l_in_uld_id;

    IF (l_in_uld_id IS NOT NULL) THEN

      insert_inbound_uld_dests(p_in_flight_id, p_out_flight_id, l_in_uld_id);

    END IF;

  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END insert_inbound_uld;

---------------------------------
-- insert_inbound_uld_dests
--
PROCEDURE insert_inbound_uld_dests(p_in_flight_id IN flight.flight_id%TYPE,
                                   p_out_flight_id IN flight.flight_id%TYPE,
                                   p_in_uld_id IN inbound_uld.inbound_uld_id%TYPE) IS

  l_count NUMBER;
  l_uld_id_code ulds.uld_id_code%TYPE;

BEGIN

  -- There is a complication with this routine, since INBOUND_ULD has a Destination Sequence column
  -- but ULD_DESTS does not.
  -- To resolve this, the following approach will be used
  -- 1) Check how many destinations this ULD has - if there is just 1, add it
  -- 2) For ULDs with more than 1 destination, check whether a ULD_ID_CODE exists.
  --    If it does, use this to define the destination sequence
  -- 3) If there are more than 1 destinations, but no ULD_ID_CODE then the destination
  --    information will have to be read from ULD_DESTS in an arbitrary order

  -- How many destinations does this ULD have
  SELECT COUNT(*)
  INTO   l_count
  FROM   uld_dests ud,
         inbound_uld iu
  WHERE  ud.uld_no = iu.source_uld_no
    AND  iu.inbound_flight_id = p_in_flight_id
    AND  ud.flight_id = p_out_flight_id
    AND  iu.inbound_uld_id = p_in_uld_id;

  IF (l_count = 0) THEN

    -- This should not really happen, but just define a single destination to the next airport
    INSERT INTO inbound_uld_dests
    (
      inbound_uld_id,
      destination_sequence,
      destination,
      class_list,
      onward_flight_no,
      onward_flight_date
    ) VALUES (
      p_in_uld_id,
      1,
      '*',
      '*',
      NULL,
      NULL
    );

  ELSIF (l_count = 1) THEN

    -- Only a single destination - copy it over
    INSERT INTO inbound_uld_dests
    (
      inbound_uld_id,
      destination_sequence,
      destination,
      class_list,
      onward_flight_no,
      onward_flight_date
    ) (
      SELECT p_in_uld_id,
             1,
             ud.uld_destination,
             ud.uld_class_list,
             ud.onward_flight_no,
             ud.onward_flight_date
      FROM   uld_dests ud,
             inbound_uld iu
      WHERE  iu.inbound_flight_id = p_in_flight_id
        AND  iu.source_uld_no = ud.uld_no
        AND  iu.inbound_uld_id = p_in_uld_id
        AND  ud.flight_id = p_out_flight_id
    );

  ELSE

    -- Check if a ULD_ID_CODE exists for this ULD
    SELECT u.uld_id_code
    INTO   l_uld_id_code
    FROM   ulds u,
           inbound_uld iu
    WHERE  iu.inbound_uld_id = p_in_uld_id
      AND  iu.inbound_flight_id = p_in_flight_id
      AND  u.uld_no = iu.source_uld_no
      AND  u.flight_id = p_out_flight_id;

    IF (l_uld_id_code IS NOT NULL) THEN

      -- Use the ULD_RECORDED_ID_CODES table to determine the destination sequence
      INSERT INTO inbound_uld_dests
      (
        inbound_uld_id,
        destination_sequence,
        destination,
        class_list,
        onward_flight_no,
        onward_flight_date
      ) (
        SELECT p_in_uld_id,
               1,
               uric.dest1,
               uric.class1,
               uric.onward_flight_no1,
               uric.onward_flight_date1
        FROM   uld_recorded_id_codes uric
        WHERE  uric.uld_id_code = l_uld_id_code
          AND  uric.dest1 IS NOT NULL
      UNION ALL
        SELECT p_in_uld_id,
               2,
               uric.dest2,
               uric.class2,
               uric.onward_flight_no2,
               uric.onward_flight_date2
        FROM   uld_recorded_id_codes uric
        WHERE  uric.uld_id_code = l_uld_id_code
          AND  uric.dest2 IS NOT NULL
      UNION ALL
        SELECT p_in_uld_id,
               3,
               uric.dest3,
               uric.class3,
               uric.onward_flight_no3,
               uric.onward_flight_date3
        FROM   uld_recorded_id_codes uric
        WHERE  uric.uld_id_code = l_uld_id_code
          AND  uric.dest3 IS NOT NULL
      UNION ALL
        SELECT p_in_uld_id,
               4,
               uric.dest4,
               uric.class4,
               uric.onward_flight_no4,
               uric.onward_flight_date4
        FROM   uld_recorded_id_codes uric
        WHERE  uric.uld_id_code = l_uld_id_code
          AND  uric.dest4 IS NOT NULL
      UNION ALL
        SELECT p_in_uld_id,
               5,
               uric.dest5,
               uric.class5,
               uric.onward_flight_no5,
               uric.onward_flight_date5
        FROM   uld_recorded_id_codes uric
        WHERE  uric.uld_id_code = l_uld_id_code
          AND  uric.dest5 IS NOT NULL
      UNION ALL
        SELECT p_in_uld_id,
               6,
               uric.dest6,
               uric.class6,
               uric.onward_flight_no6,
               uric.onward_flight_date6
        FROM   uld_recorded_id_codes uric
        WHERE  uric.uld_id_code = l_uld_id_code
          AND  uric.dest6 IS NOT NULL
      );

    ELSE

      -- Cannot determine a destination sequence - use arbitrary sequence
      INSERT INTO inbound_uld_dests
      (
        inbound_uld_id,
        destination_sequence,
        destination,
        class_list,
        onward_flight_no,
        onward_flight_date
      ) (
        SELECT p_in_uld_id,
               ROWNUM,
               ud.uld_destination,
               ud.uld_class_list,
               ud.onward_flight_no,
               ud.onward_flight_date
        FROM   uld_dests ud,
               inbound_uld iu
        WHERE  iu.inbound_flight_id = p_in_flight_id
          AND  iu.source_uld_no = ud.uld_no
          AND  iu.inbound_uld_id = p_in_uld_id
          AND  ud.flight_id = p_out_flight_id
      );

    END IF;

  END IF;

EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END insert_inbound_uld_dests;

---------------------------------
-- insert_inbound_pax
--
PROCEDURE insert_inbound_pax(p_in_flight_id IN flight.flight_id%TYPE, p_out_flight_id IN flight.flight_id%TYPE) IS

  CURSOR c_paxes (p_flight_id flight.flight_id%TYPE) IS
         SELECT p.pax_id
         FROM   pax p
         WHERE  p.flight_id = p_flight_id
           AND  p.delete_marker = 0
           AND  EXISTS (SELECT 1
                        FROM   bag b
                        WHERE  b.pax_id = p.pax_id
                          AND  b.delete_marker = 0
                          AND  b.loaded_status = 'Y');

  l_in_pax_id inbound_pax.inbound_pax_id%TYPE;

BEGIN

  FOR r_pax IN c_paxes(p_out_flight_id) LOOP

    INSERT INTO inbound_pax
    (
      inbound_pax_id,
      inbound_flight_id,
      source_pax_id,
      source_message_id,
      result_pax_id,
      result_pax_status,
      error_code,
      inbound_pax_status,
      pax_surname,
      pax_initials,
      pax_number_same_surname,
      frequent_flyer
    ) VALUES (
      0,                                -- zero replaced by trigger with value from sequence
      p_in_flight_id,
      r_pax.pax_id,
      NULL,                             -- source message id
      NULL,                             -- result pax id
      'UNPROCESSED',
      NULL,                             -- error code
      'TERMINATING',
      NULL,                             -- pax surname
      NULL,                             -- pax initials
      NULL,                             -- no same surname
      NULL                              -- freq flyer
    ) RETURN inbound_pax_id
      INTO   l_in_pax_id;

    IF (l_in_pax_id IS NOT NULL) THEN

      insert_inbound_pax_onward_dest(p_in_flight_id, p_out_flight_id, l_in_pax_id);

    END IF;

  END LOOP;

EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END insert_inbound_pax;

---------------------------------
-- insert_inbound_pax_onward_dest
--
PROCEDURE insert_inbound_pax_onward_dest(p_in_flight_id IN flight.flight_id%TYPE,
                                         p_out_flight_id IN flight.flight_id%TYPE,
                                         p_in_pax_id IN pax.pax_id%TYPE) IS

  l_dest_order_offset NUMBER := 0;
  l_pax_destination pax.pax_destination%TYPE;
  l_pax_class pax.pax_class%TYPE;
  l_this_flight_no flight.flight_no%TYPE;
  l_this_flight_date flight.flight_date%TYPE;
  l_this_airport site_info.airport_code%TYPE;

BEGIN

  -- Get the airport code of the current airport
  SELECT si.airport_code,
         f.flight_no,
         f.flight_date
  INTO   l_this_airport,
         l_this_flight_no,
         l_this_flight_date
  FROM   flight f,
         site_info si
  WHERE  f.site_id = si.site_id
    AND  f.flight_id = p_in_flight_id;

  -- This first onward dest for an inbound pax is the pax_destination
  -- UNLESS the pax destination is the current airport (site id of the
  -- inbound flight).
  BEGIN

    SELECT p.pax_destination,
           p.pax_class
    INTO   l_pax_destination,
           l_pax_class
    FROM   pax p,
           inbound_pax ip
    WHERE  ip.inbound_flight_id = p_in_flight_id
      AND  ip.inbound_pax_id = p_in_pax_id
      AND  p.flight_id = p_out_flight_id
      AND  p.pax_id = ip.source_pax_id;

  EXCEPTION
    WHEN OTHERS THEN
      l_pax_destination := l_this_airport;
  END;

  -- Do we need to insert an initial destination?
  IF (l_pax_destination <> l_this_airport) THEN

    -- Yes we do
    l_dest_order_offset := 1;

    INSERT INTO inbound_pax_onward_dests
    (
      inbound_pax_id,
      destination_sequence,
      onward_flight_no,
      onward_flight_date,
      onward_destination,
      onward_class
    ) VALUES (
      p_in_pax_id,
      l_dest_order_offset,
      l_this_flight_no,
      l_this_flight_date,
      l_pax_destination,
      l_pax_class
    );

  END IF;

  -- Now insert onward flight destinations
  INSERT INTO inbound_pax_onward_dests
  (
    inbound_pax_id,
    destination_sequence,
    onward_flight_no,
    onward_flight_date,
    onward_destination,
    onward_class
  ) (
    SELECT p_in_pax_id,
           o.dest_order + l_dest_order_offset,
           o.norm_fno,
           o.odate,
           o.dest,
           o.class
    FROM   onward_flight o,
           pax p,
           inbound_pax ip
    WHERE  p.flight_id = p_out_flight_id
      AND  ip.source_pax_id = p.pax_id
      AND  o.pax_id = p.pax_id
      AND  ip.inbound_pax_id = p_in_pax_id
  );

EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END insert_inbound_pax_onward_dest;

---------------------------------
-- insert_inbound_bag
--
PROCEDURE insert_inbound_bag(p_in_flight_id IN flight.flight_id%TYPE,
                             p_out_flight_id IN flight.flight_id%TYPE) IS

BEGIN

  INSERT INTO inbound_bag
  (
    inbound_bag_id,
    inbound_pax_id,
    inbound_uld_id,
    source_bag_id,
    source_message_id,
    result_bag_id,
    result_bag_status,
    error_code,
    bag_tag,
    bag_weight,
    bag_load_sequence,
    bag_exception_type,
    bag_security_status
  ) (
    SELECT 0,                             -- zero replaced by trigger with value from sequence
           ip.inbound_pax_id,
           iu.inbound_uld_id,
           b.bag_id,
           NULL,                          -- source message id
           NULL,                          -- result bag id
           'UNPROCESSED',
           NULL,                          -- error code
           NULL,                          -- bag tag
           NULL,                          -- kg weight
           NULL,                          -- bag sequence no
           NULL,                          -- bag exception
           NULL                           -- security clearance status
    FROM   pax p,
           bag b,
           ulds u,
           inbound_pax ip,
           inbound_uld iu
    WHERE  p.flight_id = p_out_flight_id
      AND  p.pax_id = b.pax_id
      AND  u.uld_no = b.uld_no
      AND  u.flight_id = p.flight_id
      AND  ip.inbound_flight_id = p_in_flight_id
      AND  ip.source_pax_id = p.pax_id
      AND  iu.source_uld_no = u.uld_no
      AND  iu.inbound_flight_id = ip.inbound_flight_id
      AND  p.delete_marker = 0
      AND  b.delete_marker = 0
      AND  b.loaded_status = 'Y'
      AND  ip.result_pax_status = 'UNPROCESSED'
      AND  iu.result_uld_status = 'UNPROCESSED'
  );

EXCEPTION
  WHEN OTHERS THEN
     RAISE;
END insert_inbound_bag;

---------------------------------
-- insert_all
--
PROCEDURE insert_all(p_out_flight_id IN flight.flight_id%TYPE) IS

  l_in_flight_id flight.flight_id%TYPE;
  l_uld_status inbound_uld.inbound_uld_status%TYPE;
  l_pax_status inbound_pax.inbound_pax_status%TYPE;

  CURSOR c_ulds (p_in_flight_id flight.flight_id%TYPE) IS
         SELECT inbound_flight_id,
                source_uld_no
         FROM   inbound_uld
         WHERE  inbound_flight_id = p_in_flight_id
           AND  result_uld_status IN ('UNPROCESSED', 'CREATED', 'AMENDED')
         FOR UPDATE OF inbound_uld_status;

  CURSOR c_paxes (p_in_flight_id flight.flight_id%TYPE) IS
         SELECT inbound_flight_id,
                inbound_pax_id
         FROM   inbound_pax
         WHERE  inbound_flight_id = p_in_flight_id
           AND  result_pax_status IN ('UNPROCESSED', 'CREATED', 'AMENDED')
         FOR UPDATE OF inbound_pax_status;

BEGIN

  -- Get the Inbound Flight for this Outbound Flight
  l_in_flight_id := get_next_leg_id(p_out_flight_id);

  -- Copy all relevant data to the Inbound tables
  insert_inbound_uld(l_in_flight_id, p_out_flight_id);
  insert_inbound_pax(l_in_flight_id, p_out_flight_id);
  insert_inbound_bag(l_in_flight_id, p_out_flight_id);

  -- Update the status of the new Inbound records
  FOR r_uld IN c_ulds(l_in_flight_id) LOOP

    l_uld_status := inbound_flight_utils.get_inbound_status('ULD', r_uld.inbound_flight_id, NULL, NULL, r_uld.source_uld_no);

    UPDATE inbound_uld
    SET    inbound_uld_status = l_uld_status
    WHERE CURRENT OF c_ulds;

  END LOOP;

  FOR r_pax IN c_paxes(l_in_flight_id) LOOP

    l_pax_status := inbound_flight_utils.get_inbound_status('PAX', r_pax.inbound_flight_id, r_pax.inbound_pax_id, NULL, NULL);

    UPDATE inbound_pax
    SET    inbound_pax_status = l_pax_status
    WHERE CURRENT OF c_paxes;

  END LOOP;

END insert_all;


END insert_inbound_data;
/
