/*********************************************************************
*                                                                   *
*             Copyright (c) Ultra Electronics Airport Systems       *
*                           All rights reserved.                    *
*                                                                   *
*********************************************************************
*                                                                   *
* Filename:             auto_copy_through.sql                       *
* Module Name:          auto_copy_through                           *
* Subsystem Name:       stored_procs                                *                            *
* Design Reference:     UAS/FALCP/SS/1940                           *
*                                                                   *
*********************************************************************
*                                                                   *
* Description:                                                      *
*  Procedure to copy passengers, ULDs and baggage from an inbound   *
*  to an outbound flight where the flight numbers are the same      *
*  for both flights (i.e. it is a through flight)                   *
*********************************************************************/
CREATE OR REPLACE PROCEDURE auto_copy_through (
   p_in_flightid  IN NUMBER,
   p_out_flightid IN NUMBER
)
IS
 l_inbound_flightno     FLIGHT_INFO.FLIGHT_NO%TYPE;
 l_inbound_flightdate   FLIGHT_INFO.FLIGHT_DATE%TYPE;
 l_outbound_flightno    FLIGHT_INFO.FLIGHT_NO%TYPE;
 l_outbound_flightdate  FLIGHT_INFO.FLIGHT_DATE%TYPE;
 l_inbound_flight_reg   FLIGHT_INFO.AIRCRAFT_REGISTRATION%TYPE;
 l_outbound_flight_reg  FLIGHT_INFO.AIRCRAFT_REGISTRATION%TYPE;
 l_inbound_flight_type  FLIGHT_INFO.AIRCRAFT_TYPE%TYPE;
 l_outbound_flight_type FLIGHT_INFO.AIRCRAFT_TYPE%TYPE;
 l_plane_change         NUMBER;
 l_hold_count           NUMBER;               -- whether the ULD specified is a hold or not
 l_uld_hold             ULDS.ULD_HOLD_NO%TYPE;
 l_home_airport         SITE_INFO.AIRPORT_CODE%TYPE;
 l_in_site_id           SITE_INFO.SITE_ID%TYPE;
 l_out_site_id          SITE_INFO.SITE_ID%TYPE;
 l_out_location         BAG_TRACE.LOCATION_CODE%TYPE; 
 l_user_id              BAG_TRACE.USER_ID%TYPE;
 l_ret_code             NUMBER;                -- the call to copy_uld requires a return code
 l_posn_result          NUMBER;

 /* Local declarations */
 l_process_type    inbound_uld.inbound_uld_status%TYPE := 'THROUGH';
  
 
CURSOR u1 IS
  SELECT * 
  FROM  inbound_uld_v1 
  WHERE inbound_flight_id = p_in_flightid 
  AND   inbound_uld_status = l_process_type   /* Should be THROUGH */
  AND   result_uld_status = 'UNPROCESSED';
  
BEGIN
  l_posn_result:=0;

  /* Check the parameters and ensure the flights are linked */
  IF (NVL(p_in_flightid, 0) = 0 OR NVL(p_out_flightid, 0) = 0) THEN

    /* Invalid Options - Abort */
    RETURN;

  END IF;

  /* Link the Inbound Flight to the Outbound Flight */
  UPDATE flight SET next_leg_id = p_out_flightid WHERE flight_id = p_in_flightid;

  /* Get the site_id for the inbound flight  */
  SELECT site_id
  INTO   l_in_site_id
  FROM   flight
  WHERE  flight_id = p_in_flightid;
  
  /* Get the airport the outward flight will depart from */
  /* the 'automatic' location, and the site_id           */
  SELECT flight.site_id,
         site_info.airport_code,
         location.location_code
  INTO   l_out_site_id,
         l_home_airport,
         l_out_location
  FROM   flight,
         site_info,
         location
  WHERE  p_out_flightid = flight.flight_id AND
         flight.site_id = site_info.site_id AND
         site_info.site_id = location.site_id AND
         /* get the 'automatic' location */
         location.location_code like 'AU%';

  /* Get the 'automatic' user_id for this site */
  SELECT tagtrak_users.user_id 
  INTO   l_user_id 
  FROM   tagtrak_users,
         agents 
  WHERE  tagtrak_users.agent_code = agents.agent_code AND
         agents.site_id           = l_out_site_id AND
         tagtrak_users.loginname  LIKE 'AUTO%';

  /* Get the flight no and flight date for the in and outbound flights */
  flight_no(p_in_flightid, l_inbound_flightno, l_inbound_flightdate);
  flight_no(p_out_flightid, l_outbound_flightno, l_outbound_flightdate);

  /* get the reg and type of the inbound flight */
  BEGIN
    SELECT TRIM(aircraft_registration), TRIM(aircraft_type)
    INTO l_inbound_flight_reg, l_inbound_flight_type
    FROM flight_info_inbound, site_info
    WHERE normalise_flight_no(flight_info_inbound.flight_no) = normalise_flight_no(l_inbound_flightno) AND
          flight_info_inbound.flight_date=l_inbound_flightdate AND
          flight_info_inbound.airport_code = site_info.airport_code AND
          site_info.site_id = l_in_site_id;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN

      /* Not found in INBOUND table - try to find OUTBOUND flight at previous station */
      BEGIN
        SELECT TRIM(fi.aircraft_registration),
               TRIM(fi.aircraft_type)
        INTO   l_inbound_flight_reg,
               l_inbound_flight_type
        FROM   flight_info fi,
               site_info si
        WHERE  normalise_flight_no(fi.flight_no) = normalise_flight_no(l_inbound_flightno)
          AND  fi.flight_date = l_inbound_flightdate
          AND  fi.airport_code = si.airport_code
          AND  si.site_id = inbound_flight_utils.get_inbound_orig_site(p_in_flightid);

      EXCEPTION
        WHEN NO_DATA_FOUND THEN
          l_inbound_flight_reg  := NULL;
          l_inbound_flight_type := NULL;
      END;
  END;

  /* get the reg and type of the outbound flight */
  BEGIN
    SELECT TRIM(aircraft_registration), TRIM(aircraft_type)
    INTO l_outbound_flight_reg, l_outbound_flight_type
    FROM flight_info
    WHERE normalise_flight_no(flight_no) = normalise_flight_no(l_outbound_flightno) AND
          flight_date=l_outbound_flightdate AND
          airport_code=l_home_airport;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_outbound_flight_reg  := NULL;
      l_outbound_flight_type := NULL;
  END;
  
  l_plane_change := 0;
  IF (l_inbound_flight_reg IS NOT NULL AND l_outbound_flight_reg IS NOT NULL) THEN
    IF (l_inbound_flight_reg <> l_outbound_flight_reg) THEN
      l_plane_change:=1;
    END IF;
  END IF;
  IF (l_inbound_flight_type IS NOT NULL AND l_outbound_flight_type IS NOT NULL) THEN
    IF (l_inbound_flight_type <> l_outbound_flight_type) THEN
      l_plane_change:=1;
    END IF;
  END IF;

  /********************************************************/
  /* Move all through passengers onto the outbound flight */
  /* The ULD is null since this is through processing     */
  /* and so is process flow is controlled by the flight_id*/
  /********************************************************/
  inbound_flight.copy_pax (p_in_flightid, p_out_flightid, NULL,l_process_type);

  FOR inbound_uld IN u1 LOOP

    /* Use a NULL hold if the plane has changed, otherwise copy the position to the outbound flight */
    IF l_plane_change = 0 THEN  -- copy the hold to the next flight
      l_uld_hold := inbound_uld.uld_hold_no;
    ELSE
      l_uld_hold := NULL;
    END IF;

    SELECT 
      count(*)
    INTO l_hold_count
    FROM holds
    WHERE hold_no=inbound_uld.uld_no;

    IF l_hold_count=0 THEN

    BEGIN
      inbound_flight.copy_uld (p_in_flightid,
                               p_out_flightid,
                               inbound_uld.uld_no,
                               l_uld_hold,
                               l_user_id,
                               l_out_location,
                               NULL,
                               l_process_type,
                               l_ret_code,
                               l_posn_result
                               );

      inbound_flight.copy_bags(p_in_flightid,
                               p_out_flightid,
                               inbound_uld.uld_no,
                               l_user_id,
                               l_process_type
                               );

    EXCEPTION
      WHEN inbound_flight.uld_already_exists THEN
       add_app_msg.log_oracle_error('AUTO_COPY_THROUGH - ULD EXISTS - ULD IGNORED',SQLERRM,SQLCODE);
      WHEN OTHERS THEN
        add_app_msg.log_oracle_error('AUTO_COPY_THROUGH - UNDEFINED ERROR',SQLERRM,SQLCODE);
    END;
    END IF;
  END LOOP;

  /* Copy the remaining bags not in a ULD */
  inbound_flight.copy_non_uld_bags(p_in_flightid, p_out_flightid,l_process_type);
                           
END auto_copy_through;
/

