/************************************************************************
 * Filename:             unthrough_flight.sql                           *
 *                                                                      *
 ************************************************************************
 * Module Name:          unthrough_flight                               *
 * Subsystem Name:       stored_procs                                   *
 * Originating Author:   Peter Charalambous                             *
 * Design Reference:     UAS/FALCP/SS01940                              *
 *                                                                      *
 ************************************************************************
 *                                                                      *
 * Description:                                                         *
 *  If a flight is no longer a through flight, this script will         *
 * untransfer the ULDS, pax and bags which have been automatically      *
 * transferred previously                                               *
 ************************************************************************
 *                                                                      *
 * Program Identification Array:                                        *
 *                                                                      *
 * "%Z%%M% VERSION %I% %E% %U%"                                         *
 *                                                                      *
 ************************************************************************
 *                                                                      *
 * Modification History:                                                *
 *                                                                      *
 * Rel Date/Time      Proj/Id   CA Ref/Details Of Change                *
 * --- -------------- --------  ------------------------------------    *
 * 1.0 25 Aril 06               File initially checked in               *
 ************************************************************************
 *           Copyright (c) Ultra Electronics Airport Systems 2006       *
 ************************************************************************/

PROMPT REPLACE PROCEDURE unthrough_flight                                 

CREATE OR REPLACE PROCEDURE unthrough_flight(p_inbound_flight_id  IN NUMBER
                                            ,p_outbound_flight_id IN NUMBER)
AS
   -- Type declarations
   TYPE bag_id_tt            IS TABLE OF bag.bag_id%TYPE        INDEX BY BINARY_INTEGER;
   TYPE bag_tag_tt           IS TABLE OF bag.bag_tag%TYPE       INDEX BY BINARY_INTEGER;
   TYPE bag_tag1_tt          IS TABLE OF bag.bag_tag1%TYPE      INDEX BY BINARY_INTEGER;
   TYPE bag_tag2a_tt         IS TABLE OF bag.bag_tag2a%TYPE     INDEX BY BINARY_INTEGER;
   TYPE bag_tag2b_tt         IS TABLE OF bag.bag_tag2b%TYPE     INDEX BY BINARY_INTEGER;
   TYPE bag_tag3_tt          IS TABLE OF bag.bag_tag3%TYPE      INDEX BY BINARY_INTEGER;
   TYPE bag_pax_id_tt        IS TABLE OF bag.pax_id%TYPE        INDEX BY BINARY_INTEGER;
   TYPE bag_uld_no_tt        IS TABLE OF bag.uld_no%TYPE        INDEX BY BINARY_INTEGER;
   TYPE bag_location_code_tt IS TABLE OF bag.location_code%TYPE INDEX BY BINARY_INTEGER;
   TYPE bag_scanner_id_tt    IS TABLE OF bag.scanner_id%TYPE    INDEX BY BINARY_INTEGER;
   TYPE location_code_tt     IS TABLE OF bag.location_code%TYPE INDEX BY BINARY_INTEGER;
   TYPE scanner_id_tt        IS TABLE OF bag.scanner_id%TYPE    INDEX BY BINARY_INTEGER;
   TYPE pax_flight_id_tt     IS TABLE OF pax.flight_id%TYPE     INDEX BY BINARY_INTEGER;
   TYPE error_id_tt          IS TABLE OF NUMBER                 INDEX BY BINARY_INTEGER;
   TYPE pax_no_of_bags_tt    IS TABLE OF pax.no_of_bags%TYPE    INDEX BY BINARY_INTEGER;

   TYPE uld_no_tt            IS TABLE OF ulds.uld_no%TYPE        INDEX BY BINARY_INTEGER;
   TYPE uld_user_id_tt       IS TABLE OF ulds.user_id%TYPE       INDEX BY BINARY_INTEGER;
   TYPE uld_location_code_tt IS TABLE OF ulds.location_code%TYPE INDEX BY BINARY_INTEGER;
   TYPE result_bag_status_tt IS TABLE OF inbound_bag.result_bag_status%TYPE INDEX BY BINARY_INTEGER;

   -- PL/SQL table declarations
   t_bag_id            bag_id_tt;
   t_bag_bag_tag       bag_tag_tt;
   t_bag_tag1          bag_tag1_tt;
   t_bag_tag2a         bag_tag2a_tt;
   t_bag_tag2b         bag_tag2b_tt;
   t_bag_tag3          bag_tag3_tt;
   t_bag_pax_id        bag_pax_id_tt;
   t_bag_uld_no        bag_uld_no_tt;
   t_bag_location_code bag_location_code_tt;
   t_bag_scanner_id    bag_scanner_id_tt;
   t_pax_flight_id     pax_flight_id_tt;
   t_pax_no_of_bags    pax_no_of_bags_tt;
   t_error_id          error_id_tt;

   t_uld_no            uld_no_tt;
   t_uld_user_id       uld_user_id_tt;
   t_uld_location_code uld_location_code_tt;
   t_result_bag_status result_bag_status_tt;
   t_deleted_pax_ids   bag_pax_id_tt;

   -- Local variables
   l_process_name       oracle_error.process_name%TYPE := 'UNTHROUGH_FLIGHT';
   l_outbound_flight_id flight.flight_id%TYPE          := p_outbound_flight_id;
   l_inbound_flight_id  flight.flight_id%TYPE          := p_inbound_flight_id;
   l_user_id            tagtrak_users.user_id%TYPE     := multi_site.get_auto_user(multi_site.get_flight_loc(p_outbound_flight_id));
   l_error_msg          oracle_error.errror_msg%TYPE   := NULL;
   l_error_code         oracle_error.error_code%TYPE   := NULL;
   l_error_number       NUMBER                         := NULL;
   l_error_time         oracle_error.error_time%TYPE   := NULL;
   l_error              NUMBER := 0;
   l_trtime             uld_trace.flight_date%TYPE;

   -- Index counters
   indx                 BINARY_INTEGER              := 0;

BEGIN

/**************************************************************************/
/* Get bag details of the outbound_flight to do the Offload               */
/* BULK COLLECT this into arrays for easier processing                    */
/**************************************************************************/
  SELECT        bag.bag_id
               ,bag.bag_tag
               ,bag.bag_tag1
               ,bag.bag_tag2a
               ,bag.bag_tag2b
               ,bag.bag_tag3
               ,bag.pax_id
               ,bag.uld_no
               ,bag.location_code
               ,nvl(bag.scanner_id,0)
               ,pax.flight_id
               ,nvl(pax.no_of_bags,0)
               ,ib.result_bag_status
  BULK COLLECT INTO
                t_bag_id
               ,t_bag_bag_tag
               ,t_bag_tag1
               ,t_bag_tag2a
               ,t_bag_tag2b
               ,t_bag_tag3
               ,t_bag_pax_id
               ,t_bag_uld_no
               ,t_bag_location_code
               ,t_bag_scanner_id
               ,t_pax_flight_id
               ,t_pax_no_of_bags
               ,t_result_bag_status
  FROM BAG, PAX,INBOUND_BAG IB , INBOUND_PAX IP
  WHERE  PAX.PAX_ID = BAG.PAX_ID
  AND    PAX.DELETE_MARKER = 0
  AND    BAG.DELETE_MARKER = 0
  AND    PAX.FLIGHT_ID     = p_outbound_flight_id
  AND    IB.RESULT_BAG_ID = BAG.BAG_ID
  AND    IB.RESULT_BAG_STATUS IN ('CREATED','AMENDED')
  AND    IB.INBOUND_PAX_ID    = IP.INBOUND_PAX_ID
  AND    IP.INBOUND_FLIGHT_ID = p_inbound_flight_id;


/*************************************************************************/
/* Offload ALL BAGS for this Outbound flight                             */
/*************************************************************************/
 IF t_bag_bag_tag.COUNT > 0 THEN
  FOR i IN t_bag_bag_tag.FIRST..t_bag_bag_tag.LAST LOOP

     t_error_id(i) := 0;

     IF t_bag_uld_no(i) IS NOT NULL THEN
         offload_bag(tag2a           => t_bag_tag2a(i),
                    tag2b           => t_bag_tag2b(i),
                    tag3            => t_bag_tag3(i),
                    pid             => t_bag_pax_id(i),
                    uldno           => t_bag_uld_no(i),
                    p_location_code => t_bag_location_code(i),
                    p_scanner_id    => t_bag_scanner_id(i),
                    fltid           => l_outbound_flight_id,
                    retval          => l_error);

         t_error_id(i) := l_error;

         dbms_output.put_line('tag2a           => '||t_bag_tag2a(i));
         dbms_output.put_line('tag2b           => '||t_bag_tag2b(i));
         dbms_output.put_line('tag3            => '||t_bag_tag3(i));
         dbms_output.put_line('pid             => '||t_bag_pax_id(i));
         dbms_output.put_line('uldno           => '||t_bag_uld_no(i));
         dbms_output.put_line('p_location_code => '||t_bag_location_code(i));
         dbms_output.put_line('p_scanner_id    => '||t_bag_scanner_id(i));
         dbms_output.put_line('fltid           => '||l_outbound_flight_id);
         dbms_output.put_line('retval          => '||t_error_id(i));
         dbms_output.put_line('bag_tag1        => '||t_bag_tag1(i));
         /*************************************************************************/
         /* Generate an Offload trace action (error and non-error)                */
         /*************************************************************************/
         add_bag_trace_record(p_user_id        => l_user_id,
                              p_location_code  => t_bag_location_code(i),
                              p_error_id       => t_error_id(i),
                              p_bagtag         => t_bag_bag_tag(i),
                              p_tag1           => t_bag_tag1(i),
                              p_tag2a          => t_bag_tag2a(i),
                              p_tag2b          => t_bag_tag2b(i),
                              p_tag3           => t_bag_tag3(i),
                              p_trace_action   => 'OFFLOAD',
                              p_scanner_id     => t_bag_scanner_id(i),
                              p_flight_id_scan => p_outbound_flight_id,
                              p_paxid          => t_bag_pax_id(i),
                              p_uld_no         => t_bag_uld_no(i)
                               );
         /************************************************************************/
         /* Debug information                                                    */
         /************************************************************************/
        IF (t_bag_tag1.COUNT > 0) THEN
            dbms_output.put_line('-----------------UNTHROUGH_FLIGHT------------');
            dbms_output.put_line('user_id             =>:'|| l_user_id);
            dbms_output.put_line('location_code       =>:'|| t_bag_location_code(i));
            dbms_output.put_line('error_id            =>:'|| t_error_id(i));
            dbms_output.put_line('bagtag              =>:'|| t_bag_bag_tag(i));
            dbms_output.put_line('tag1                =>:'|| t_bag_tag1(i));
            dbms_output.put_line('tag2a               =>:'|| t_bag_tag2a(i));
            dbms_output.put_line('tag2b               =>:'|| t_bag_tag2b(i));
            dbms_output.put_line('tag3                =>:'|| t_bag_tag3(i));
            dbms_output.put_line('scanner_id          =>:'|| t_bag_scanner_id(i));
            dbms_output.put_line('outbound_flight_id  =>:'|| p_outbound_flight_id);
            dbms_output.put_line('inbound_flight_id   =>:'|| p_inbound_flight_id);
            dbms_output.put_line('paxid               =>:'|| t_bag_pax_id(i));
            dbms_output.put_line('uld_no              =>:'|| t_bag_uld_no(i));
            dbms_output.put_line('pax.uld_no_of_bags  =>:'|| t_pax_no_of_bags(i));
            dbms_output.put_line('bag.bag_id          =>:'|| t_bag_id(i));
            dbms_output.put_line('-----------------UNTHROUGH_FLIGHT------------');
        END IF;
     END IF; /*t_bag_uld_no(i) IS NOT NULL*/

     IF t_result_bag_status(i) = 'CREATED' THEN
      /************************************************************************/
      /* Delete the Outbound BAG created during through flight processing     */
      /* Assuming that offload is ALL Success, now delete the BAGS            */
      /* to reverse the through flight process.                               */
      /************************************************************************/
         DELETE FROM BAG WHERE bag_id = t_bag_id(i);

      /*************************************************************************/
      /* Generate a Delete Inbound Through Bag Trace Record                    */
      /*************************************************************************/
         add_bag_trace_record(p_user_id        => l_user_id,
                              p_location_code  => t_bag_location_code(i),
                              p_error_id       => 0,
                              p_bagtag         => t_bag_bag_tag(i),
                              p_tag1           => t_bag_tag1(i),
                              p_tag2a          => t_bag_tag2a(i),
                              p_tag2b          => t_bag_tag2b(i),
                              p_tag3           => t_bag_tag3(i),
                              p_trace_action   => 'DIN_TH_BAG',
                              p_scanner_id     => t_bag_scanner_id(i),
                              p_flight_id_scan => p_outbound_flight_id,
                              p_paxid          => t_bag_pax_id(i),
                              p_uld_no         => t_bag_uld_no(i)
                               );
     END IF;
  END LOOP;
 END IF;

/************************************************************************/
/* Update the pax table setting the pax.uld_no_of_bags to zero          */
/************************************************************************/
 IF t_bag_pax_id.COUNT > 0 THEN
  FOR i IN t_bag_pax_id.FIRST..t_bag_pax_id.LAST LOOP
  UPDATE PAX
  SET    PAX.NO_OF_BAGS = (SELECT COUNT(bag_id)
                           FROM BAG
                           WHERE BAG.PAX_ID = t_bag_pax_id(i))
  WHERE  PAX.PAX_ID     = t_bag_pax_id(i)
  AND    PAX.FLIGHT_ID  = p_outbound_flight_id;
  END LOOP;
 END IF;

/**************************************************************************/
/* This should update ALL the INBOUND_BAG records for the inbound flight  */
/* which is having its through flight REVERSED, regardless to what it was */
/* prior.                                                                 */
/**************************************************************************/
  UPDATE INBOUND_BAG
  SET    RESULT_BAG_STATUS = 'REVERSED'
  WHERE  RESULT_BAG_ID IN
       (SELECT IB.RESULT_BAG_ID
        FROM  INBOUND_BAG IB
             ,INBOUND_PAX IP
        WHERE IB.INBOUND_PAX_ID    = IP.INBOUND_PAX_ID
        AND   IP.INBOUND_FLIGHT_ID = p_inbound_flight_id);

/**************************************************************************/
/*  Delete ALL the paxes with zero bags on the outbound flight            */
/*  The pax.no_of_bags should have been set to zero after deleting bags   */
/**************************************************************************/
  DELETE FROM PAX
  WHERE PAX_ID IN ( SELECT RESULT_PAX_ID
                    FROM  INBOUND_PAX IP
                    WHERE IP.RESULT_PAX_STATUS = 'CREATED'
                    AND   IP.INBOUND_FLIGHT_ID = p_inbound_flight_id
                   )
  AND PAX.NO_OF_BAGS = 0
  AND PAX.FLIGHT_ID  = p_outbound_flight_id
  RETURNING pax_id BULK COLLECT INTO t_deleted_pax_ids;

/************************************************************************/
/* Generate Delete Inbound Through Pax Trace Records                    */
/************************************************************************/
 IF t_deleted_pax_ids.COUNT > 0 THEN
  FOR i IN t_deleted_pax_ids.FIRST..t_deleted_pax_ids.LAST LOOP
    add_log_rec( 'DELETE_INBOUND_THROUGH_PAX',
                 'delete: PAX ID: '||t_deleted_pax_ids(i)||' from FLIGHT: '||
                  p_inbound_flight_id,
                  NULL);
  END LOOP;
 END IF;
/**************************************************************************/
/* Check for LOADALL paxes having zero bags, these need to be             */
/* deleted, regardless of RESULT_PAX_STATUS                               */
/**************************************************************************/

t_deleted_pax_ids.DELETE;

  DELETE FROM PAX
  WHERE PAX_ID IN (SELECT RESULT_PAX_ID
                   FROM  INBOUND_PAX IP
                        ,INBOUND_BAG IB
                   WHERE IP.INBOUND_FLIGHT_ID = p_inbound_flight_id
                   AND   IB.INBOUND_PAX_ID    = IP.INBOUND_PAX_ID)
  AND PAX.NO_OF_BAGS      = 0
  AND PAX.PAX_SEQUENCE_NO = -1 /* LoadALL Pax */
  AND PAX.FLIGHT_ID       = p_outbound_flight_id
  RETURNING pax_id BULK COLLECT INTO t_deleted_pax_ids;

/************************************************************************/
/* Generate Delete Inbound Through Pax Trace Records                    */
/************************************************************************/
 IF t_deleted_pax_ids.COUNT > 0 THEN
  FOR i IN t_deleted_pax_ids.FIRST..t_deleted_pax_ids.LAST LOOP
    add_log_rec( 'DELETE_INBOUND_THROUGH_PAX',
                 'delete: PAX ID: '||t_deleted_pax_ids(i)||' from FLIGHT: '||
                  p_inbound_flight_id,
                  NULL);
  END LOOP;
 END IF;

/*************************************************************************/
/* Change the RESULT_PAX_STATUS to REVERSED                              */
/*************************************************************************/
  UPDATE INBOUND_PAX IP
  SET    IP.RESULT_PAX_STATUS = 'REVERSED'
  WHERE  IP.INBOUND_FLIGHT_ID = p_inbound_flight_id;

/**************************************************************************/
/* Get ULD details with zero bags on outbound flight                      */
/* BULK COLLECT this into arrays for easier processing                    */
/**************************************************************************/
  SELECT        uld_no
               ,NVL(user_id,l_user_id)
               ,NVL(location_code, multi_site.get_auto_location(multi_site.get_flight_loc(p_outbound_flight_id)))
  BULK COLLECT INTO
                t_uld_no
               ,t_uld_user_id
               ,t_uld_location_code
  FROM ULDS
  WHERE ULD_NO IN (SELECT RESULT_ULD_NO
                   FROM  INBOUND_ULD
                   WHERE RESULT_ULD_STATUS = 'CREATED'
                   AND INBOUND_FLIGHT_ID   = p_inbound_flight_id)
  AND ULDS.ULD_NO_OF_BAGS = 0
  AND ULDS.FLIGHT_ID      = p_outbound_flight_id;

/*************************************************************************/
/*  Delete ALL the ULDS  with zero bags on the outbound flight           */
/*************************************************************************/
  DELETE FROM ULDS
  WHERE ULD_NO IN (SELECT RESULT_ULD_NO
                   FROM  INBOUND_ULD
                   WHERE RESULT_ULD_STATUS = 'CREATED'
                   AND INBOUND_FLIGHT_ID   = p_inbound_flight_id)
  AND ULDS.ULD_NO_OF_BAGS = 0
  AND ULDS.FLIGHT_ID      = p_outbound_flight_id;

/************************************************************************/
/* Generate Delete Inbound Through ULD Trace Records                    */
/************************************************************************/
 IF t_uld_no.COUNT > 0 THEN
  FOR i IN t_uld_no.FIRST..t_uld_no.LAST LOOP
    add_uld_trace_record (t_uld_no(i),
	                      p_outbound_flight_id,
                          t_uld_user_id(i),
                          '0',
                          t_uld_location_code(i),
                          l_trtime,
                          'DIN_TH_ULD',
                          'Y');
  END LOOP;
 END IF;
/************************************************************************/
/* Change the RESULT_ULD_STATUS to REVERSED                             */
/************************************************************************/
  UPDATE INBOUND_ULD
  SET    RESULT_ULD_STATUS = 'REVERSED'
  WHERE  INBOUND_FLIGHT_ID = p_inbound_flight_id;

/************************************************************************/
/* Finally Set the next leg of the Inbound Flight to NULL               */
/************************************************************************/
  UPDATE  FLIGHT
  SET     NEXT_LEG_ID = NULL
  WHERE   FLIGHT_ID   = p_inbound_flight_id;

EXCEPTION
    WHEN OTHERS THEN
         add_app_msg.log_oracle_error(l_process_name,SQLERRM,SQLCODE);
         RAISE;
/************************************************************************/
/* Reversal Through Flight Process is now complete                      */
/************************************************************************/

END unthrough_flight;
/

