/*****************************************************************************
*
* Filename         - $Workfile : reflighting.sql $
*
* Project Name     - ULTRATRAK(Oracle Database)
*
* Source Code
* Identifier       - FAS/FARFL
*
* Description      - Re-flighting Module
*
* Language         - Oracle PL/SQL package
*
* File History     - For the development history run the Show History report in PVCS
*
* Coptright Ultra Electronics Airport Systems 2004
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PACKAGE reflighting

CREATE OR REPLACE PACKAGE reflighting
AS
   /**
      These are cursors used to return flight/passenger selections.
   **/

   TYPE gFlightIdType        IS TABLE OF flight.flight_id%TYPE             INDEX BY BINARY_INTEGER;
   TYPE gFlightNoType        IS TABLE OF flight.flight_no%TYPE             INDEX BY BINARY_INTEGER;
   TYPE gFlightDateType      IS TABLE OF flight.flight_date%TYPE           INDEX BY BINARY_INTEGER;
   TYPE gFlightSchTimeType   IS TABLE OF flight.flight_sch_time%TYPE       INDEX BY BINARY_INTEGER;
   TYPE gBagTagType          IS TABLE OF bag.bag_tag%TYPE                  INDEX BY BINARY_INTEGER;
   TYPE gPaxIdType           IS TABLE OF pax.pax_id%TYPE                   INDEX BY BINARY_INTEGER;
   TYPE gPaxInitialsType     IS TABLE OF pax.pax_initials%TYPE             INDEX BY BINARY_INTEGER;
   TYPE gPaxSurnameType      IS TABLE OF pax.pax_surname%TYPE              INDEX BY BINARY_INTEGER;
   TYPE gCarrierType         IS TABLE OF flight.carrier_alpha_code%TYPE    INDEX BY BINARY_INTEGER;
   TYPE gDestinationType     IS TABLE OF pax.pax_destination%TYPE          INDEX BY BINARY_INTEGER;
   TYPE gFlightDestType      IS TABLE OF CHAR(20)                          INDEX BY BINARY_INTEGER;
   TYPE gPaxClassType        IS TABLE OF pax.pax_class%TYPE                INDEX BY BINARY_INTEGER;
   TYPE gULDType             IS TABLE OF bag.uld_no%TYPE                   INDEX BY BINARY_INTEGER;
   TYPE gRLCodeType          IS TABLE OF reflight_bag.reason_for_loss%TYPE INDEX BY BINARY_INTEGER;
   TYPE gFaultStationType    IS TABLE OF reflight_bag.fault_station%TYPE   INDEX BY BINARY_INTEGER;
   TYPE gWorldTracerRefType  IS TABLE OF reflight_bag.worldtracer_ref%TYPE INDEX BY BINARY_INTEGER;
   TYPE gReflightSpaceType   IS TABLE OF NUMBER(6)                         INDEX BY BINARY_INTEGER;
   TYPE gReturnStatusType    IS TABLE OF NUMBER(6)                         INDEX BY BINARY_INTEGER;
   TYPE gPrintTagType        IS TABLE OF CHAR(1)                           INDEX BY BINARY_INTEGER;
   TYPE gWTMsgType           IS TABLE OF CHAR(1)                           INDEX BY BINARY_INTEGER;

   ORACLE_ERROR              NUMBER(6) := -20100;
   MULTIPLE_TAG_FLIGHT       NUMBER(6) := -20101;
   MULTIPLE_TAG_PAX          NUMBER(6) := -20102;
   REVAL_BAG_MULTI_FLIGHT    NUMBER(6) := -20103;
   REVAL_BAG_MULTI_PAX       NUMBER(6) := -20104;
   REVALIDATE_BAG            NUMBER(6) := -20105;
   ZOMBIE_BAG                NUMBER(6) := -20106;
   NO_REFLIGHTING            NUMBER(6) := -20107;
   CARRIER_NOT_ACCEPT        NUMBER(6) := -20110;
   NO_ROOM_ON_FLIGHT         NUMBER(6) := -20111;
   DEST_TO_AVOID             NUMBER(6) := -20112;
   UNRESOLVABLE_DUP_PAX      NUMBER(6) := -20114;
   NO_SUITABLE_FLIGHTS       NUMBER(6) := -20115;
   RFLT_ACCESS_DENIED        NUMBER(6) := -20131;
   RFLT_REVALIDATE_CANCELLED NUMBER(6) := -20132;
   RFLT_UNKNOWN_TAG          NUMBER(6)  := -20133;
   INVALID_TAG               NUMBER(6)  := -20134;
   INVALID_CARRIER           NUMBER(6)  := -20135;

   ERR_MSG          VARCHAR2(300);
   ERR_CODE         NUMBER(10);
   DEPAIR           SITE_INFO.AIRPORT_CODE%TYPE;

   -- error response to use if the response format is unknown
   v_unknown_response_format VARCHAR2(30):= 'Unknown response format';

   -- string containing the new line characters
   v_new_line VARCHAR2(2) := chr(13);

   -- create a type to store tokens extracted from a message line
   -- The maximum length of any line is 61 characters(2 character
   -- plus 58 characters for the line content.  Set the size of each
   -- token to 100 characters so we can be sure this is big enough.
   TYPE token_array IS VARRAY(9999) OF VARCHAR2(100);


/****************************************************************************************************
* Procedure    - add_reflight_bag
*
* Description  - This procedure is used to add the bag to the flight selected to re-flight it on.
*                If the RUSH pax does not already exist this is also created.
*                It is called from either the reflight_one_bag procedure or the tia_flight trigger.
*****************************************************************************************************/

   PROCEDURE add_reflight_bag(pUserId        IN     tagtrak_users.user_id%TYPE,
                              pOrigFlightId  IN     flight.flight_no%TYPE,
                              pOrigPaxId     IN     pax.pax_id%TYPE,
                              pBagTag        IN     bag.bag_tag%TYPE,
                              pFlightId      IN     flight.flight_no%TYPE,
                              pAirportCode   IN     site_info.airport_code%TYPE,
                              pSiteId        IN     site_info.site_id%TYPE,
                              pDateTime      IN     DATE,
                              pPaxId            OUT pax.pax_id%TYPE);

/****************************************************************************************************
* Procedure    - bag_history
*
* Description  - This procedure is called by the MWS when a user has entered a 
*                bag tag/flight/passenger that they wish to view the history of. 
*                The result will be a cursor containing the list of flights the bag has been on, in 
*                the order of the scheduled departure time.
*****************************************************************************************************/

   PROCEDURE bag_history     (pBagTag         IN     bag.bag_tag%TYPE,
                              pFlightId       IN     flight.flight_id%TYPE,
                              pPaxId          IN     pax.pax_id%TYPE,
                              pFlightNo          OUT gFlightNoType,
                              pFlightDate        OUT gFlightDateType,
                              pFlightSchTime     OUT gFlightSchTimeType,
                              pBagTagHist        OUT gBagTagType,
                              pPaxInitials       OUT gPaxInitialsType,
                              pPaxSurname        OUT gPaxSurnameType, 
                              pDestination       OUT gDestinationType,
                              pPaxClass          OUT gPaxClassType, 
                              pULD               OUT gULDType,
                              pRLCode            OUT gRLCodeType,
                              pFaultStation      OUT gFaultStationType,
                              pWorldTracerRef    OUT gWorldTracerRefType);

  FUNCTION bag_history_2(p_bag_tag    IN bag.bag_tag%TYPE,
                         p_flight_id  IN flight.flight_id%TYPE,
                         p_pax_id     IN pax.pax_id%TYPE)
  RETURN SYS_REFCURSOR;
  
/****************************************************************************************************
* Procedure    - bag_search
*
* Description  - This procedure is called by the MWS 'Re-flight Flight' screen or the 'ULD Contents' 
*                pop-up screen when the user has double clicked on a colour coded row, indicating that
*                the bag belongs to a passenger that has also missed the flight. 
*                It will look to see if the bag and passenger can be found on a future flight.
*****************************************************************************************************/

   PROCEDURE bag_search      (pUserId       IN     tagtrak_users.user_id%TYPE,
                              pBagTag       IN     bag.bag_tag%TYPE,
                              pOrigFlightId IN     flight.flight_id%TYPE,
                              pPaxInit      IN     pax.pax_initials%TYPE,
                              pPaxSurname   IN     pax.pax_surname%TYPE,
                              pFlightId        OUT gFlightIdType,
                              pFlightNo        OUT gFlightNoType,
                              pFlightDate      OUT gFlightDateType,
                              pFlightSchTime   OUT gFlightSchTimeType,
                              pRetStatus       OUT gReturnStatusType);

  FUNCTION bag_search_2(p_user_id        IN tagtrak_users.user_id%TYPE,
                        p_bag_tag        IN bag.bag_tag%TYPE,
                        p_orig_flight_id IN flight.flight_id%TYPE,
                        p_pax_init       IN pax.pax_initials%TYPE,
                        p_pax_surname    IN pax.pax_surname%TYPE)
  RETURN SYS_REFCURSOR;
  
/****************************************************************************************************
* Procedure    - copy_uld_bags
*
* Description  - This procedure is called from the procedures tu_flight_trig or uld_unloaded. These
*                in turn are called from the triggers tu_flight or tu_ulds respectively.
*                It will copy all data about the ULD/Passengers/Bags into the re-flighting data tables
*                for all bags loaded in the ULD.
*****************************************************************************************************/

   PROCEDURE copy_uld_bags   (pSiteId        IN     reflight_ulds.site_id%TYPE,
                              pFlightId      IN     reflight_ulds.flight_id%TYPE,
                              pULDNo         IN     reflight_ulds.uld_no%TYPE,
                              pULDNoOfBags   IN     reflight_ulds.uld_no_of_bags%TYPE);

/****************************************************************************************************
* Procedure    - get_bag_info
*
* Description  - This procedure is called from the MWS or Scanner when a bag tag has been 
*                entered/selected for reflighting. It will try to find the flight and passenger the 
*                bag is on. If more than one flight is found a list of possible flights are returned.
*                If more than one passenger is found a list of possible passengers are returned. 
*                It will initially be called with the Bag Tag to find, but if multiple flights or 
*                passengers were found and the user has selected the individual ones, it will be 
*                called with the Flight No/Date/Time and Pax details.
*                Once there is a single match the additional information is retrieved and returned.
*****************************************************************************************************/
     
   PROCEDURE get_bag_info    (pUserId          IN     tagtrak_users.user_id%TYPE,
                              pBagTag          IN     bag.bag_tag%TYPE,
                              pFlightNoIn      IN     flight.flight_no%TYPE,
                              pFlightDateIn    IN     flight.flight_date%TYPE,
                              pFlightSchTimeIn IN     flight.flight_sch_time%TYPE,
                              pPaxInitIn       IN     pax.pax_initials%TYPE,
                              pPaxSurnameIn    IN     pax.pax_surname%TYPE,
                              pFlightNo           OUT gFlightNoType,
                              pFlightDate         OUT gFlightDateType,
                              pFlightSchTime      OUT gFlightSchTimeType,
                              pOrigCarrier        OUT gCarrierType,
                              pPaxInit            OUT gPaxInitialsType,
                              pPaxSurname         OUT gPaxSurnameType,
                              pPaxClass           OUT gPaxClassType,
                              pFlightId           OUT gFlightIdType,
                              pDestination        OUT gDestinationType,
                              pPaxId              OUT gPaxIdType,
                              pPrintTagDef        OUT gPrintTagType,
                              pPrintTagOth        OUT gPrintTagType,
                              pWTMsg              OUT gWTMsgType,
                              pRetStatus          OUT gReturnStatusType);

  FUNCTION get_bag_info_2(p_user_id             IN tagtrak_users.user_id%TYPE,
                          p_bag_tag             IN bag.bag_tag%TYPE,
                          p_flight_no_in        IN flight.flight_no%TYPE,
                          p_flight_date_in      IN flight.flight_date%TYPE,
                          p_flight_sch_time_in  IN flight.flight_sch_time%TYPE,
                          p_pax_init_in         IN pax.pax_initials%TYPE,
                          p_pax_surname_in      IN pax.pax_surname%TYPE)
  RETURN SYS_REFCURSOR;
  
/****************************************************************************************************
* Procedure    - reflight_one_bag
*
* Description  - This procedure is called by the MWS and scanner when the bag is to be reflighted.
*                The bag will be copied on to the new flight as a rush bag and the re-flighting data
*                updated to indicate the bag has been reflighted and lastly send the WorldTracer message.
*****************************************************************************************************/

   PROCEDURE reflight_one_bag(pUserId        IN     tagtrak_users.user_id%TYPE,
                              pOrigFlightId  IN     flight.flight_id%TYPE,
                              pPaxId         IN     pax.pax_id%TYPE,
                              pBagTag        IN     bag.bag_tag%TYPE,
                              pFlightId      IN     flight.flight_id%TYPE,
                              pFlightNo      IN     flight.flight_no%TYPE,
                              pReasonForLoss IN     reflight_bag.reason_for_loss%TYPE,
                              pFaultStation  IN     reflight_bag.fault_station%TYPE,
                              pSupInfo1      IN     VARCHAR2,
                              pSupInfo2      IN     VARCHAR2,
                              pWTRef         IN     reflight_bag.worldtracer_ref%TYPE,
                              pTeletypeList  IN     VARCHAR2);

/****************************************************************************************************
* Procedure    - remove_reflight_data
*
* Description  - This procedure is called by the tu_flight trigger when the flight status changes to back from 
*                'PD'. It will remove all reflighting data where the bag has not already been re-flighted.
*                If a bag has already been re-flighted then the pax/flight and if relevant the uld records
*                will remain.
*****************************************************************************************************/

   PROCEDURE remove_reflight_data (pFlightId         IN     flight.flight_id%TYPE);

/****************************************************************************************************
* Procedure    - select_flights
*
* Description  - This procedure is called by the MWS and scanner to obtain a list of possible flights
*                to which the bag could be re-flighted.
*****************************************************************************************************/

   PROCEDURE select_flights  (pUserId        IN     tagtrak_users.user_id%TYPE,
                              pBagTag        IN     bag.bag_tag%TYPE,
                              pPaxId         IN     pax.pax_id%TYPE,
                              pOrigFlightId  IN     flight.flight_id%TYPE,
                              pFlightId         OUT gFlightIdType,
                              pFlightNo         OUT gFlightNoType,
                              pFlightDate       OUT gFlightDateType,
                              pFlightSchTime    OUT gFlightSchTimeType,
                              pFlightDest       OUT gFlightDestType,
                              pReflightSpace    OUT gReflightSpaceType);

  FUNCTION select_flights_2(p_user_id         IN tagtrak_users.user_id%TYPE,
                            p_bag_tag         IN bag.bag_tag%TYPE,
                            p_pax_id          IN pax.pax_id%TYPE,
                            p_orig_flight_id  IN flight.flight_id%TYPE)
  RETURN SYS_REFCURSOR;
  
/****************************************************************************************************
* Procedure    - ti_flight_dests_trig
*
* Description  - This procedure is called by the triiger ti_flight_dests. It will check to see if the
*                flight being created has been used to re-flight bags to, if so create a RUSH passenger
*                on the flight and copy the bag details. If the WorldTracer message is to be sent, 
*                store the message in the table and update the re-flighting data to show the bag has
*                been re-flighted.
*****************************************************************************************************/

   PROCEDURE ti_flight_dests_trig (pFlightId         IN     flight.flight_id%TYPE,
                                   pFlightDest       IN     flight.flight_no%TYPE);

/****************************************************************************************************
* Procedure    - tu_flight_trig
*
* Description  - This procedure is called by the tu_flight trigger when the flight status changes to
*                'PD'. It will determine which bags/ulds have missed the flight and for each one copy
*                the details for the bag/passenger/uld/flight into the re-fighting data tables.
*****************************************************************************************************/

PROCEDURE tu_flight_trig (pSiteId           IN     site_info.site_id%TYPE,
                          pFlightId         IN     flight.flight_id%TYPE,
                          pFlightNo         IN     flight.flight_no%TYPE,
                          pFlightDate       IN     flight.flight_date%TYPE,
                          pFlightSchTime    IN     flight.flight_sch_time%TYPE,
                          pCarrierAlphaCode IN     flight.carrier_alpha_code%TYPE,
                          pDeptime          IN     flight.departure_time%TYPE);

/****************************************************************************************************
* Procedure    - uld_loaded
*
* Description  - This procedure is called from the tu_ulds trigger when the flight status is 'PD' and
*                the ULD have been recorded as being on the aircraft.
*                It will removed all of the bags/passengers/uld details from the re-flighting data 
*                tables for all bags within the uld and for the uls itself.
*****************************************************************************************************/

PROCEDURE uld_loaded      (pSiteId           IN     reflight_ulds.site_id%TYPE,
                           pFlightId         IN     reflight_ulds.flight_id%TYPE,
                           pULDNo            IN     reflight_ulds.uld_no%TYPE);

/****************************************************************************************************
* Procedure    - uld_unloaded 
*
* Description  - This procedure is called from the tu_ulds trigger when the flight status is 'PD' and 
*                the ULD has been recorded as being unloaded from the aircraft.
*                It will copy all the the details about the ULD/passengers/bags to the re-flighting 
*                data tables.
*****************************************************************************************************/

PROCEDURE uld_unloaded    (pSiteId           IN     reflight_ulds.site_id%TYPE,
                           pFlightId         IN     reflight_ulds.flight_id%TYPE,
                           pULDNo            IN     reflight_ulds.uld_no%TYPE,
                           pULDNoOfBags      IN     reflight_ulds.uld_no_of_bags%TYPE);

/****************************************************************************************************
* Procedure    - validate_flight
*
* Description  - This procedure is called from the MWS and the scanner when a the user has manually 
*                entered a flight to which the bag is to be re-flighted.
*                It will check to see if a flight exists in the future that can be used. If so checks
*                are performed to make sure it has room for re-flighted bags and does not go via 
*                destinations to be avoided.If no flight is found the procedure will return success as
*                this is quite possible. The user may have entered a flight that will operate, but one
*                that UltraTrak system does not yet know about. In this case there will be no values
*                returned for the flight date/time/destination or id.
*****************************************************************************************************/

PROCEDURE validate_flight (pUserId       IN     tagtrak_users.user_id%TYPE,
                           pOrigFlightId IN     flight.flight_id%TYPE,
                           pPaxDest      IN     pax.pax_destination%TYPE,
                           pFlightNo     IN     flight.flight_no%TYPE,
                           pFlightDate      OUT flight.flight_date%TYPE,
                           pSchedTime       OUT flight.flight_sch_time%TYPE,
                           pFlightDest      OUT pax.pax_destination%TYPE,
                           pFlightId        OUT flight.flight_id%TYPE);

/****************************************************************************************************
* Procedure    - worldtracer_msg
*
* Description  - This procedure is called from the reflight_bag procedure and will send the FWD message 
*                to the WorldTracer system for the re-flighted bag.
*****************************************************************************************************/

PROCEDURE worldtracer_msg (pSiteId        IN     site_info.site_id%TYPE,
                           pBagTag        IN     bag.bag_tag%TYPE,
                           pBagTag2a      IN     bag.bag_tag2a%TYPE,
                           pBagTag2b      IN     bag.bag_tag2b%TYPE,
                           pBagTag3       IN     bag.bag_tag3%TYPE,
                           pAirportCode   IN     site_info.airport_code%TYPE,
                           pOrigFlightId  IN     flight.flight_id%TYPE,
                           pOrigCarrier   IN     flight.carrier_alpha_code%TYPE,
                           pOrigPaxId     IN     pax.pax_id%TYPE,
                           pFlightId      IN     flight.flight_id%TYPE,
                           pFlightNo      IN     flight.flight_no%TYPE,
                           pFlightDest    IN     flight_dests.flight_destination%TYPE,
                           pPaxId         IN     pax.pax_id%TYPE,
                           pFaultStation  IN     reflight_bag.fault_station%TYPE,
                           pReasonForLoss IN     reflight_bag.reason_for_loss%TYPE,
                           pSupInfo1      IN     VARCHAR2,
                           pSupInfo2      IN     VARCHAR2,
                           pWTRef         IN     reflight_bag.worldtracer_ref%TYPE,
                           pTeletypeList  IN     VARCHAR2);

/****************************************************************************************************
* Procedure    - process_fwd
*
* Description  - Check whether the FWD file was received okay, and set the error response if not.
*
*****************************************************************************************************/

PROCEDURE process_fwd(
   p_wt_response IN wt_transactions.wt_response%TYPE,
   p_response_error OUT wt_transactions.response_error%TYPE);

END reflighting;
/
PROMPT CREATE OR REPLACE PACKAGE BODY reflighting

CREATE OR REPLACE PACKAGE BODY reflighting
AS


/******************************************************************
 * Private Procedure   - get_tag_component
 ******************************************************************/
PROCEDURE get_tag_component( pPaxId      IN  pax.pax_id%TYPE,
                             pBagTag2b   IN  bag.bag_tag2b%TYPE,
                             pBagTag3    IN  bag.bag_tag3%type,
                             pBagTag2a   OUT bag.bag_tag2a%TYPE,
                             pBagTag1a   OUT bag.bag_tag1%TYPE
                           )
IS

BEGIN

   SELECT bag_tag1, bag_tag2a
     INTO pBagTag1a , pBagTag2a
     FROM bag
    where pax_id    = pPaxId
      and bag_tag3  = pBagTag3
      and bag_tag2b = pBagTag2b;
   
EXCEPTION 

  WHEN NO_DATA_FOUND THEN
      pBagTag2a := NULL;
      pBagTag1a := NULL;
  WHEN OTHERS THEN
    RAISE;

END get_tag_component; 

   
         

/******************************************************************
 *  Private Procedure  - log_oracle_error
 *
 *  Description        - Use to log oracle runtime error
 *
 ******************************************************************/
PROCEDURE log_oracle_error(   p_process_name      IN    varchar2
                             ,p_err_msg           IN    varchar2
                             ,p_err_code          IN    number
                          ) 
IS
  
  PRAGMA         AUTONOMOUS_TRANSACTION;

BEGIN

   
   INSERT INTO ORACLE_ERROR (PROCESS_NAME,
                             ERRROR_MSG,
                             ERROR_CODE,
                             ERROR_TIME
                            )
                     VALUES ( p_process_name, 
                              p_err_msg,
                              p_err_code,
                              SYSDATE + get_offset.airport_code(depair) 
                            );
COMMIT;
EXCEPTION
  WHEN OTHERS THEN
     RAISE;
END log_oracle_error;

/**********************************************************************
 *
 * Private Procedure - tokenise_string
 *
 * Description:
 *    Separate a provided string into separate tokens, placing them
 *    in separate elements in an array.
 *    A maximum number of tokens may be specified to allow the last
 *    few tokens to be batched into a single element.
 *
 * Parameters:
 *    p_response: string which we are separating into tokens
 *    p_max_tokens: maximum number of tokens which should be returned
 *    p_delimiter: separator between each token
 *    p_token_array: array to contain separated tokens
 *    p_token_count: number of tokens returned
 *
 * Errors raised:
 *    None
 *
 **********************************************************************/

PROCEDURE tokenise_string(p_response IN wt_transactions.wt_response%TYPE,
   p_max_tokens IN NUMBER,
   p_delimiter IN VARCHAR2,
   p_token_array OUT token_array,
   p_token_count OUT NUMBER) IS

   l_current_token NUMBER(4):=0; -- next token to populate

   -- copy of response used for processing
   l_current_string wt_transactions.wt_response%TYPE;

   -- end of next token
   l_next_delimiter NUMBER(4);
BEGIN
   -- copy the string into a local variable so that it may be manipulated
   l_current_string := LTRIM( RTRIM(p_response));

   -- initialise the array of tokens
   p_token_array := token_array();

   -- loop through the string, identifying delimiters and
   -- extracting each token
   LOOP
      -- increment the token count
      l_current_token :=l_current_token +1;

      -- find the position of the first delimiter
      l_next_delimiter := instr(l_current_string, p_delimiter,1,1);

      -- extend the array creating a place for the next token
      p_token_array.extend(1);

      IF l_next_delimiter >0 AND l_current_token < p_max_tokens THEN
         -- this is not the last token, extract the token we have found
         -- and strip it from the string we are processing.  Strip any
         -- leading spaces.
         p_token_array(l_current_token) := SUBSTR(l_current_string,1,l_next_delimiter -1);
         l_current_string :=
            RTRIM(LTRIM(SUBSTR(l_current_string,l_next_delimiter),p_delimiter),p_delimiter);
      ELSE
         -- this is the last token
         p_token_array(l_current_token) := RTRIM(LTRIM(l_current_string,p_delimiter),p_delimiter);
      END IF;

      -- exit the loop when the are no more delimiters, or when we have found
      -- the maximum number of tokens required
      EXIT WHEN l_next_delimiter <=0 OR l_current_token = p_max_tokens;

   END LOOP;

   -- set the returned token count
   p_token_count :=l_current_token;

END tokenise_string;


/****************************************************************************************************
* Procedure    - add_reflight_bag
*
* Description  - This procedure is used to add the bag to the flight selected to re-flight it on.
*                If the RUSH pax does not already exist this is also created.
*                It is called from either the reflight_one_bag procedure or the tia_flight trigger.
*****************************************************************************************************/

PROCEDURE add_reflight_bag(pUserId        IN     tagtrak_users.user_id%TYPE,
                           pOrigFlightId  IN     flight.flight_no%TYPE,
                           pOrigPaxId     IN     pax.pax_id%TYPE,
                           pBagTag        IN     bag.bag_tag%TYPE,
                           pFlightId      IN     flight.flight_no%TYPE,
                           pAirportCode   IN     site_info.airport_code%TYPE,
                           pSiteId        IN     site_info.site_id%TYPE,
                           pDateTime      IN     DATE,
                           pPaxId            OUT pax.pax_id%TYPE)
AS
   lCount          NUMBER(6);
   lPaxDest        pax.pax_destination%TYPE;
   lSecurity       varchar2(3) := 'N';
   lBagtype        bag.bag_type%TYPE;
   lOrigCarrier    flight.carrier_alpha_code%TYPE;
   lCarrier        flight.carrier_alpha_code%TYPE;
   lUnitName       system_cfg.unit_name%TYPE;
   lSubunitName    system_cfg.subunit_name%TYPE;
   lDcsId          system_cfg.dcsid%TYPE;
   lBagTag1        bag.bag_tag1%TYPE;
   lBagTag2a       bag.bag_tag2a%TYPE;
   lBagTag2b       bag.bag_tag2b%TYPE;
   lBagTag3        bag.bag_tag3%TYPE;
   lBagDest        bag.bag_destination%TYPE;
   lLocation       location.location_code%TYPE;
   lConnectionType bag.connection_type%TYPE := NULL;
   lULDNo          ulds.uld_no%TYPE;
   lTag1           bag.bag_tag1%TYPE;
   lTag2a          bag.bag_tag2a%TYPE;
   lTag2b          bag.bag_tag2b%TYPE;
   lTag3           bag.bag_tag3%TYPE;
   lTagType        NUMBER;
   lTagNoNum       VARCHAR2(10);
   lnewTag         bag.bag_tag%TYPE;

   runtime_stage   number := 0;

BEGIN
   
   

   depair :=  get_offset.get_user_location(pUserId);
 
   

   /** 
      Get the split bag tag
   **/
   split_tags (pBagTag, lTag1, lTag2a, lTag2b, lTag3, lTagNoNum, lTagType);

    IF ( lTagType <= 0) THEN
       raise_application_error(INVALID_TAG, 'Invalid Tag');
    END IF;

   
    
    
   /**
      Get the Carrier code for the original and new flight.
   **/

   SELECT carrier_alpha_code
   INTO lOrigCarrier
   FROM reflight_flight
   WHERE flight_id = pOrigFlightId;
 
      

   SELECT carrier_alpha_code
   INTO lCarrier
   FROM flight
   WHERE flight_id = pFlightId;

   

   /**
      Get the passenger destination 
   **/
   SELECT p.pax_destination
   INTO lPaxDest
   FROM reflight_pax p,
        reflight_bag b
   WHERE p.pax_id             = b.pax_id
   AND   b.flight_id          = pOrigFlightId
   AND   b.pax_id             = pOrigPaxId
   AND   (   (    b.bag_tag2a = lTag2a
              AND b.bag_tag3  = lTag3)
          OR (    b.bag_tag2b = lTag2b
              AND b.bag_tag3  = lTag3));

   

   /**
      Get the bag destination
   **/
   SELECT bag_dest
   INTO lBagDest
   FROM reflight_bag
   WHERE flight_id          = pOrigFlightId
   AND   pax_id             = pOrigPaxId
   AND   (   (    bag_tag2a = lTag2a
              AND bag_tag3  = lTag3)
          OR (    bag_tag2b = lTag2b
              AND bag_tag3  = lTag3));

  

   /***********************************************************************/
   /* This checks to see if the RUSH bag has been flagged for extra       */
   /* security checks or not. The security value could be set to 'U','N'  */
   /* as determined by the reflighting setup configuration screen.        */
   /*                                                                     */
   /* If the authorisation flag is set to Y then the bag does not require */
   /* security clearance, i.e. the security_status is is set to 'U'.      */
   /* If the authorisation flag is set to 'N', then the bag does require  */
   /* to be screened.                                                     */
   /* If no value has been setup in the refligting setup screen, the      */
   /* security status value defualts to 'N',meaning that the bag does need*/
   /* to be screened for security clearance.                              */
   /***********************************************************************/
     
   BEGIN
     SELECT decode(add_rush_auth, 'Y', 'U', add_rush_auth)
     INTO lSecurity
     FROM reflight_accept_from
     WHERE site_id             = pSiteId
     AND   carrier             = lCarrier
     AND   accept_from_carrier = lOrigCarrier;
 
     /***********************************************************************/
     /* If no value has been set up for this carrier in the                 */
     /* reflighting setup screen, then default the security_status for      */
     /* requiring security clearance i.e. screening by setting the value to */
     /* 'N'.                                                                */
     /***********************************************************************/
     IF (lSecurity IS NULL) THEN
         lSecurity := 'N';
     END IF;
      

   EXCEPTION
   WHEN NO_DATA_FOUND 
   THEN
      NULL;
   WHEN OTHERS
   THEN
        runtime_stage := 1;
        RAISE;
   END;
   
     
   /**
      See if there is already a RUSH passenger
   **/
   SELECT COUNT(*)
   INTO lCount
   FROM pax
   WHERE pax_sequence_no = 999
   AND   flight_id       = pFlightId;

    

   IF lCount = 0
   THEN
      /**
         The Rush passenger does not exist so create one.
      **/
      INSERT INTO pax
      (flight_id,
       booked_flight_id,
       pax_initials,
       pax_surname,
       pax_status,
       time_pax_stored,
       no_of_bags,
       no_same_surname,
       pax_destination,
       pax_class,
       pax_sequence_no,
       pax_dcs_sequence_no,
       pax_security_status,
       departure_airport_code,
       authority_to_load_dcs,
       pax_full_inits,
       onward_transfer,
       pax_bags_class)
      VALUES
      (pFlightId,
       pFlightId,
       'RUSH',
       'RUSHRUSH',
       'C',
       pDateTime,
       1,
       1,
       lPaxDest,
       '*',
       999,
       '999',
       lSecurity,
       pAirportCode,
       'Y',
       'RUSH',
       0,
       '*');
   
   ELSE
 
       UPDATE pax SET no_of_bags = no_of_bags + 1
       WHERE flight_id       = pFlightId AND
             pax_sequence_no = 999;

   END IF;

   SELECT pax_id
   INTO pPaxId
   FROM pax
   WHERE flight_id = pFlightId
   AND   pax_sequence_no = 999;

   SELECT unit_name,
          subunit_name,
          dcsid
   INTO lUnitName,
        lSubunitName,
        lDcsId
   FROM system_cfg
   WHERE trim(process_name) = 'DUMMY';

   

   SELECT location_code
   INTO lLocation
   FROM location
   WHERE site_id = pSiteId
   AND rownum = 1;

   /**
      Add the bag
   **/




   IF lSecurity != 'U'
   THEN
      lSecurity := 'SEL';
   END IF;

   get_tag_component(pOrigPaxId,lTag2b,lTag3,lTag2a,lTag1);

   /* Generate the reflighted bag tag */
   lnewTag := build_user_bag_tag( p_t1=> lTag1
                                 ,p_t2a=>lTag2a
                                 ,p_t2b=>lTag2b
                                 ,p_t3=>lTag3
                                 ,p_user_pref=>'N');


   add_bag(     lnewTag, 
                lTag1, 
                lTag2a, 
                lTag2b,
                lTag3, 
                pPaxId,
                'Y',
                lBagDest,
                'L',
                'RUSH', 
                'DUMMY',
                lUnitName,
                lDcsId,
                lSubunitName,
                pBagTag,
                lULDNo, 
                lConnectionType,
                lBagtype,
                lLocation,
                pUserId,
                lSecurity,
		NULL,
                NULL, /* World Tracer Reference */
                0,
                NULL  /* bvis_subheader */
        );

EXCEPTION
WHEN OTHERS
THEN
    IF ( SQLCODE <= -20000  ) THEN
         
           RAISE;   
    ELSE  
           
          err_msg  := SQLERRM;
          err_code := SQLCODE;

          log_oracle_error(  'REFLIGHTING.ADD_REFLIGHT_BAG - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );
          raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');
        
    END IF;        
END add_reflight_bag;

/****************************************************************************************************
* Procedure    - bag_history
*
* Description  - This procedure is called by the MWS when a user has entered a 
*                bag tag/flight/passenger that they wish to view the history of. 
*                The result will be a cursor containing the list of flights the bag has been on, in 
*                the order of the scheduled departure time.
*****************************************************************************************************/

PROCEDURE bag_history     (pBagTag         IN     bag.bag_tag%TYPE,
                           pFlightId       IN     flight.flight_id%TYPE,
                           pPaxId          IN     pax.pax_id%TYPE,
                           pFlightNo          OUT gFlightNoType,
                           pFlightDate        OUT gFlightDateType,
                           pFlightSchTime     OUT gFlightSchTimeType,
                           pBagTagHist        OUT gBagTagType,
                           pPaxInitials       OUT gPaxInitialsType,
                           pPaxSurname        OUT gPaxSurnameType, 
                           pDestination       OUT gDestinationType,
                           pPaxClass          OUT gPaxClassType, 
                           pULD               OUT gULDType,
                           pRLCode            OUT gRLCodeType,
                           pFaultStation      OUT gFaultStationType,
                           pWorldTracerRef    OUT gWorldTracerRefType)
AS

   i               NUMBER(6);
   
   runtime_stage  number := 0;

   CURSOR lcurHistory
   IS
      SELECT flight_order,
             flight_no,
             flight_date,
             flight_sch_time,
             bag_tag, 
             pax_inits, 
             pax_surname,
             pax_dest,
             pax_class,
             uld_no,
             rl,
             fs,
             wtref
      FROM reflight_bag_hist_temp
      ORDER BY flight_order;

   lTag1           bag.bag_tag1%TYPE;
   lTag2a          bag.bag_tag2a%TYPE;
   lTag2b          bag.bag_tag2b%TYPE;
   lTag3           bag.bag_tag3%TYPE;
   lTagType        NUMBER;
   lTagNoNum        VARCHAR2(10);

   /* For the Original Codes */
   Orig_pRLCode         reflight_bag.reason_for_loss%TYPE;   
   Orig_pFaultStation   reflight_bag.fault_station%TYPE; 
   Orig_pWorldTracerRef reflight_bag.worldtracer_ref%TYPE;
   l_countHist          NUMBER := 0;
   
BEGIN
   /** 
      This will select all flights the bag was intended/now on, in the order of the 
      scheduled departure time. The list produced will be the same no matter which flight
      was selected for the query, i.e. if the bag has been on 2 flights and is now booked
      on yet another, no matter of the 1st, 2nd or 3rd flight is selected (input) the same
      list and order of flights will be obtained.
   **/

      depair := multi_site.get_flight_loc(pFlightId);

      split_tags (pBagTag, lTag1, lTag2a, lTag2b, lTag3, lTagNoNum, lTagType);

      IF ( lTagType <= 0) THEN
       raise_application_error(INVALID_TAG, 'Invalid Tag');
      END IF;

      

      INSERT INTO reflight_bag_hist_temp
      (flight_order,
       flight_id,
       flight_no,
       flight_date,
       flight_sch_time,
       bag_tag,
       pax_id,
       pax_inits,
       pax_surname,
       pax_dest,
       pax_class,
       uld_no,
       rl,
       fs,
       wtref)
      SELECT rownum+10,
             f.flight_id,
             f.flight_no,
             f.flight_date,
             f.scheduled_time,
             b.bag_tag, 
             b.pax_id,
             p.pax_initials, 
             p.pax_surname,
             p.pax_destination,
             p.pax_class,
             bg.uld_no,
             b.reason_for_loss,
             b.fault_station,
             b.worldtracer_ref
      FROM reflight_bag    b,
           reflight_pax    p,
           reflight_flight f,
           bag             bg
      WHERE f.flight_id                         = p.flight_id
      AND   p.pax_id                            = b.pax_id
      AND   bg.bag_tag(+)                       = b.bag_tag
      AND   bg.pax_id(+)                        = b.pax_id
      AND   (b.bag_tag, b.pax_id, b.flight_id) IN (SELECT b2.bag_tag, b2.pax_id, b2.flight_id
                                                   FROM reflight_bag b2
                                                   START WITH b2.bag_tag3   = lTag3
                                                   AND        b2.bag_tag2b  = lTag2b
                                                   AND        b2.pax_id     = pPaxId
                                                   AND        b2.flight_id = pFlightId
                                                   CONNECT BY PRIOR b2.reflight_bag_tag   = b2.bag_tag 
                                                   AND        PRIOR b2.reflight_pax_id    = b2.pax_id
                                                   AND        PRIOR b2.reflight_flight_id = b2.flight_id)
      UNION
      SELECT rownum+10,
             f.flight_id,
             f.flight_no,
             f.flight_date,
             f.scheduled_time,
             b.bag_tag, 
             b.pax_id,
             p.pax_initials, 
             p.pax_surname,
             p.pax_destination,
             p.pax_class,
             bg.uld_no,
             b.reason_for_loss,
             b.fault_station,
             b.worldtracer_ref
      FROM live.reflight_bag    b,
           live.reflight_pax    p,
           live.reflight_flight f,
           arch.bag             bg
      WHERE f.flight_id                         = p.flight_id
      AND   p.pax_id                            = b.pax_id
      AND   bg.bag_tag(+)                       = b.bag_tag
      AND   bg.pax_id(+)                        = b.pax_id
      AND   (b.bag_tag, b.pax_id, b.flight_id) IN (SELECT b2.bag_tag, b2.pax_id, b2.flight_id
                                                   FROM reflight_bag b2
                                                   START WITH b2.bag_tag3   = lTag3
                                                   AND        b2.bag_tag2b  = lTag2b
                                                   AND        b2.pax_id     = pPaxId
                                                   AND        b2.flight_id = pFlightId
                                                   CONNECT BY PRIOR b2.reflight_bag_tag   = b2.bag_tag 
                                                   AND        PRIOR b2.reflight_pax_id    = b2.pax_id
                                                   AND        PRIOR b2.reflight_flight_id = b2.flight_id);                                                   

      INSERT INTO reflight_bag_hist_temp
      (flight_order,
       flight_id,
       flight_no,
       flight_date,
       flight_sch_time,
       bag_tag,
       pax_id,
       pax_inits,
       pax_surname,
       pax_dest,
       pax_class,
       uld_no,
       rl,
       fs,
       wtref)
      SELECT rownum ,
             f.flight_id,
             f.flight_no,
             f.flight_date,
             f.scheduled_time,
             b.bag_tag,
             b.pax_id,
             p.pax_initials, 
             p.pax_surname,
             p.pax_destination,
             p.pax_class,
             bg.uld_no,
             b.reason_for_loss,
             b.fault_station,
             b.worldtracer_ref
      FROM reflight_bag    b,
           reflight_pax    p,
           reflight_flight f,
           bag             bg
      WHERE f.flight_id                         = p.flight_id
      AND   p.pax_id                            = b.pax_id
      AND   bg.bag_tag(+)                       = b.bag_tag
      AND   bg.pax_id(+)                        = b.pax_id
      AND   (b.bag_tag, b.pax_id, b.flight_id) IN (SELECT b2.bag_tag, b2.pax_id, b2.flight_id
                                                   FROM reflight_bag b2
                                                   START WITH b2.reflight_bag_tag3   = lTag3
                                                   AND        b2.reflight_bag_tag2b  = lTag2b
                                                   AND        b2.reflight_pax_id    = pPaxId
                                                   AND        b2.reflight_flight_id = pFlightId
                                                   CONNECT BY PRIOR b2.bag_tag   = b2.reflight_bag_tag 
                                                   AND        PRIOR b2.pax_id    = b2.reflight_pax_id
                                                   AND        PRIOR b2.flight_id = b2.reflight_flight_id)
      AND  0 = (SELECT COUNT(*)
                FROM reflight_bag_hist_temp
                WHERE flight_id = f.flight_id 
                AND   pax_id    = b.pax_id 
                AND   bag_tag   = b.bag_tag)
      UNION
      SELECT rownum ,
             f.flight_id,
             f.flight_no,
             f.flight_date,
             f.scheduled_time,
             b.bag_tag,
             b.pax_id,
             p.pax_initials, 
             p.pax_surname,
             p.pax_destination,
             p.pax_class,
             bg.uld_no,
             b.reason_for_loss,
             b.fault_station,
             b.worldtracer_ref
      FROM live.reflight_bag    b,
           live.reflight_pax    p,
           live.reflight_flight f,
           arch.bag             bg
      WHERE f.flight_id                         = p.flight_id
      AND   p.pax_id                            = b.pax_id
      AND   bg.bag_tag(+)                       = b.bag_tag
      AND   bg.pax_id(+)                        = b.pax_id
      AND   (b.bag_tag, b.pax_id, b.flight_id) IN (SELECT b2.bag_tag, b2.pax_id, b2.flight_id
                                                   FROM reflight_bag b2
                                                   START WITH b2.reflight_bag_tag3   = lTag3
                                                   AND        b2.reflight_bag_tag2b  = lTag2b
                                                   AND        b2.reflight_pax_id    = pPaxId
                                                   AND        b2.reflight_flight_id = pFlightId
                                                   CONNECT BY PRIOR b2.bag_tag   = b2.reflight_bag_tag 
                                                   AND        PRIOR b2.pax_id    = b2.reflight_pax_id
                                                   AND        PRIOR b2.flight_id = b2.reflight_flight_id)
      AND  0 = (SELECT COUNT(*)
                FROM reflight_bag_hist_temp
                WHERE flight_id = f.flight_id 
                AND   pax_id    = b.pax_id 
                AND   bag_tag   = b.bag_tag);                

      INSERT INTO reflight_bag_hist_temp
      (flight_order,
       flight_id,
       flight_no,
       flight_date,
       flight_sch_time,
       bag_tag,
       pax_id,
       pax_inits,
       pax_surname,
       pax_dest,
       pax_class,
       uld_no,
       rl,
       fs,
       wtref)
      SELECT rownum+20,
             f.flight_id,
             f.flight_no,
             f.flight_date,
             f.flight_sch_time,
             b.bag_tag, 
             p.pax_id,
             p.pax_initials, 
             p.pax_surname,
             p.pax_destination,
             p.pax_class,
             b.uld_no,
             rb.reason_for_loss,
             rb.fault_station,
             rb.worldtracer_ref
      FROM flight       f,
           pax          p,
           bag          b,
           reflight_bag rb
      WHERE p.flight_id                         = f.flight_id
      AND   b.pax_id                            = p.pax_id
      AND   rb.pax_id(+)                        = b.pax_id
      AND   rb.bag_tag(+)                       = b.bag_tag
      AND   (b.bag_tag, p.pax_id, f.flight_id) IN (SELECT b2.reflight_bag_tag, b2.reflight_pax_id, b2.reflight_flight_id
                                                   FROM reflight_bag b2
                                                   START WITH b2.reflight_bag_tag3   = lTag3
                                                   AND        b2.reflight_bag_tag2b  = lTag2b
                                                   AND        b2.reflight_pax_id    = pPaxId
                                                   AND        b2.reflight_flight_id = pFlightId
                                                   OR        (b2.bag_tag3            = lTag3
                                                   AND        b2.bag_tag2b           = lTag2b
                                                   AND        b2.pax_id             = pPaxId
                                                   AND        b2.flight_id          = pFlightId)
                                                   CONNECT BY PRIOR b2.reflight_bag_tag   = b2.bag_tag 
                                                   AND        PRIOR b2.reflight_pax_id    = b2.pax_id
                                                   AND        PRIOR b2.reflight_flight_id = b2.flight_id)
      AND  0 = (SELECT COUNT(*)
                FROM reflight_bag_hist_temp
                WHERE flight_id = f.flight_id 
                AND   pax_id    = b.pax_id 
                AND   bag_tag   = b.bag_tag)
      UNION
      SELECT rownum+20,
             f.flight_id,
             f.flight_no,
             f.flight_date,
             f.flight_sch_time,
             b.bag_tag, 
             p.pax_id,
             p.pax_initials, 
             p.pax_surname,
             p.pax_destination,
             p.pax_class,
             b.uld_no,
             rb.reason_for_loss,
             rb.fault_station,
             rb.worldtracer_ref
      FROM arch.flight       f,
           arch.pax          p,
           arch.bag          b,
           live.reflight_bag rb
      WHERE p.flight_id                         = f.flight_id
      AND   b.pax_id                            = p.pax_id
      AND   rb.pax_id(+)                        = b.pax_id
      AND   rb.bag_tag(+)                       = b.bag_tag
      AND   (b.bag_tag, p.pax_id, f.flight_id) IN (SELECT b2.reflight_bag_tag, b2.reflight_pax_id, b2.reflight_flight_id
                                                   FROM reflight_bag b2
                                                   START WITH b2.reflight_bag_tag3   = lTag3
                                                   AND        b2.reflight_bag_tag2b  = lTag2b
                                                   AND        b2.reflight_pax_id    = pPaxId
                                                   AND        b2.reflight_flight_id = pFlightId
                                                   OR        (b2.bag_tag3            = lTag3
                                                   AND        b2.bag_tag2b           = lTag2b
                                                   AND        b2.pax_id             = pPaxId
                                                   AND        b2.flight_id          = pFlightId)
                                                   CONNECT BY PRIOR b2.reflight_bag_tag   = b2.bag_tag 
                                                   AND        PRIOR b2.reflight_pax_id    = b2.pax_id
                                                   AND        PRIOR b2.reflight_flight_id = b2.flight_id)
      AND  0 = (SELECT COUNT(*)
                FROM reflight_bag_hist_temp
                WHERE flight_id = f.flight_id 
                AND   pax_id    = b.pax_id 
                AND   bag_tag   = b.bag_tag);

   /* Count the number of records to process */
   SELECT COUNT(*)
   INTO   l_countHist
   FROM   reflight_bag_hist_temp;
   
   i := 1;

   FOR cur IN lcurHistory
   LOOP
      pFlightNo(i)       := cur.flight_no;
      pFlightDate(i)     := cur.flight_date;
      pFlightSchTime(i)  := cur.flight_sch_time;
      pBagTagHist(i)     := cur.bag_tag;
      pPaxInitials(i)    := cur.pax_inits;
      pPaxSurname(i)     := cur.pax_surname;
      pDestination(i)    := cur.pax_dest;
      pPaxClass(i)       := cur.pax_class;
      pULD(i)            := cur.uld_no;
      
       /* Loss Code */
      IF (cur.rl IS NOT NULL) THEN  
      /* if the original has a value save it and copy it to the next
       * record. This is the case when a new value is entered for the
       * reflighted flight.
       */
          pRLCode(i+1)         := cur.rl;
          Orig_pRLCode         := cur.rl;
      ELSIF (cur.rl IS NULL and Orig_pRLCode IS NOT NULL) THEN
      /* if there isn't a value for the reflighted flight, but we
       * have the original value use these. This is when the First
       * Original flight data exists only.
       */
         IF i = l_countHist THEN
            -- Add to the LAST line
            pRLCode(i)   := Orig_pRLCode;
         ELSE
            -- Shift the previously reflighted data onto the reflighted flight
            pRLCode(i+1) := Orig_pRLCode;
         END IF;
      END IF;
    
      /* Fault Station */
      IF (cur.fs IS NOT NULL) THEN
          pFaultStation(i+1)   := cur.fs;
          Orig_pFaultStation   := cur.fs;
      ELSIF (cur.fs IS NULL and Orig_pFaultStation IS NOT NULL) THEN
          IF i = l_countHist THEN
             pFaultStation(i) := Orig_pFaultStation;
          ELSE
             pFaultStation(i+1) := Orig_pFaultStation;
          END IF;
      END IF;
    
      /* Worldtracer */
      IF (cur.wtref IS NOT NULL) THEN
          pWorldTracerRef(i+1) := cur.wtref;
          Orig_pWorldTracerRef := cur.wtref;
      ELSIF (cur.wtref IS NULL AND Orig_pWorldTracerRef IS NOT NULL) THEN
          IF i = l_countHist THEN
             pWorldTracerRef(i) := Orig_pWorldTracerRef;
          ELSE
             pWorldTracerRef(i+1) := Orig_pWorldTracerRef;
          END IF;
      END IF;
    
          

      
      i := i + 1;

   END LOOP;

   --Make sure that all possible indexes values have a value even if it's null
   --to avoid no_data_found errors in bag_history_2.
   FOR j IN 1..l_countHist LOOP
   
     IF NOT pRLCode.exists(j) THEN
     
       pRLCode(j) := NULL;
       
     END IF;
     
     IF NOT pFaultStation.exists(j) THEN
     
       pFaultStation(j) := NULL;
       
     END IF;
     
     IF NOT pWorldTracerRef.exists(j) THEN
     
       pWorldTracerRef(j) := NULL;
       
     END IF;
     
   END LOOP;
   
   DELETE reflight_bag_hist_temp;

EXCEPTION
WHEN OTHERS
THEN
        DELETE reflight_bag_hist_temp;

        IF ( SQLCODE <= -20000  ) THEN
         
           RAISE;
       
        ELSE  
           
          err_msg  := SQLERRM;
          err_code := SQLCODE;

          log_oracle_error(  'REFLIGHTING.BAG_HISTORY - STAGE' || '  '|| runtime_stage
                             ,ERR_MSG
                            , ERR_CODE
                          );

           raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');
       
        END IF;


        raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');
END bag_history;

--------------------------------------------------------------------------------
--------------------------------------------------------------------------------

FUNCTION bag_history_2(p_bag_tag    IN bag.bag_tag%TYPE,
                       p_flight_id  IN flight.flight_id%TYPE,
                       p_pax_id     IN pax.pax_id%TYPE)
RETURN SYS_REFCURSOR IS

  l_flight_no         gflightnotype;
  l_flight_date       gflightdatetype;
  l_flight_schtime    gflightschtimetype;
  l_bag_tag_hist      gbagtagtype;
  l_pax_initials      gpaxinitialstype;
  l_pax_surname       gpaxsurnametype;
  l_destination       gdestinationtype;
  l_pax_class         gpaxclasstype;
  l_uld               guldtype;
  l_rl_code           grlcodetype;
  l_fault_station     gfaultstationtype;
  l_world_tracer_ref  gworldtracerreftype;
  
  l_results_tab      reflight_bag_history_tab := reflight_bag_history_tab();
  l_cursor           SYS_REFCURSOR;
  
BEGIN

  bag_history(p_bag_tag,
              p_flight_id,
              p_pax_id,
              l_flight_no,
              l_flight_date,
              l_flight_schtime,
              l_bag_tag_hist,
              l_pax_initials,
              l_pax_surname,
              l_destination,
              l_pax_class,
              l_uld,
              l_rl_code,
              l_fault_station,
              l_world_tracer_ref);
                           
  FOR n IN nvl(l_flight_no.FIRST,1)..nvl(l_flight_no.LAST,0) LOOP

    BEGIN

      --make room in sql table.
      l_results_tab.extend(1);
      --use constructor to populate.
      l_results_tab(n) := reflight_bag_history_rec(l_flight_no(n),
                                                   l_flight_date(n),
                                                   l_flight_schtime(n),
                                                   l_bag_tag_hist(n),
                                                   l_pax_initials(n),
                                                   l_pax_surname(n),
                                                   l_destination(n),
                                                   l_pax_class(n),
                                                   l_uld(n),
                                                   l_rl_code(n),
                                                   l_fault_station(n),
                                                   l_world_tracer_ref(n));

    EXCEPTION WHEN no_data_found THEN
      NULL; --don't worry about gaps in the sql tables.

    END;

  END LOOP;

  --Build ref cursor from sql table.
  OPEN l_cursor FOR
  SELECT *
  FROM TABLE(CAST(l_results_tab AS reflight_bag_history_tab));

  RETURN l_cursor;

END bag_history_2;

/****************************************************************************************************
* Procedure    - bag_search
*
* Description  - This procedure is called by the MWS 'Re-flight Flight' screen or the 'ULD Contents' 
*                pop-up screen when the user has double clicked on a colour coded row, indicating that
*                the bag belongs to a passenger that has also missed the flight. 
*                It will look to see if the bag and passenger can be found on a future flight.
*****************************************************************************************************/

PROCEDURE bag_search      (pUserId       IN     tagtrak_users.user_id%TYPE,
                           pBagTag       IN     bag.bag_tag%TYPE,
                           pOrigFlightId IN     flight.flight_id%TYPE,
                           pPaxInit      IN     pax.pax_initials%TYPE,
                           pPaxSurname   IN     pax.pax_surname%TYPE,
                           pFlightId        OUT gFlightIdType,
                           pFlightNo        OUT gFlightNoType,
                           pFlightDate      OUT gFlightDateType,
                           pFlightSchTime   OUT gFlightSchTimeType,
                           pRetStatus       OUT gReturnStatusType)

IS
   lSiteId          site_info.site_id%TYPE;
   lAirportCode     site_info.airport_code%TYPE;
   lCount           NUMBER;
   lPaxId           pax.pax_id%TYPE;
   lTag1            bag.bag_tag1%TYPE;
   lTag2a           bag.bag_tag2a%TYPE;
   lTag2b           bag.bag_tag2b%TYPE;
   lTag3            bag.bag_tag3%TYPE;
   lTagType         NUMBER;
   lTagNoNum        VARCHAR2(10);
   runtime_stage    number := 0;
   pOrigPaxId       pax.pax_id%TYPE;
   lnewTag         bag.bag_tag%TYPE;

   CURSOR lcurFlightList (cpSiteId       site_info.site_id%TYPE,
                          cpOrigFlightId flight.flight_id%TYPE,
                          cpPaxInit      pax.pax_initials%TYPE,
                          cpPaxSurname   pax.pax_surname%TYPE,
                          cpTag2a        bag.bag_tag2a%TYPE,
                          cpTag2b        bag.bag_tag2b%TYPE,
                          cpTag3         bag.bag_tag3%TYPE)
   IS
      SELECT f.flight_id,
             f.flight_no,
             f.flight_date,
             f.flight_sch_time,
             p.pax_id
      FROM flight_outbound_v1 f,
           pax    p,
           bag    b
      WHERE to_date(f.flight_date||f.flight_sch_time, 'DDMONHH24MI')  > sysdate
      AND   f.flight_status           != 'PD'
      AND   f.site_id                  = cpSiteId
      AND   f.flight_id                = p.flight_id
      AND   p.flight_id               != cpOrigFlightId
      AND   (   (    p.pax_initials    = cpPaxInit
                 AND p.pax_surname     = cpPaxSurname)
             OR (    p.pax_sequence_no = 999))
      AND   p.pax_id                   = b.pax_id
      AND   ((b.bag_tag2a = cpTag2a AND 
              b.bag_tag3  = cpTag3) OR 
             (b.bag_tag2b = cpTag2b AND 
              b.bag_tag3  = cpTag3))
      ORDER BY f.departure_time;

BEGIN
   pRetStatus(1) := 0;
   depair :=  get_offset.get_user_location(pUserId);

/**********************************************************************************************************
   This procedure will only be called from the MWS screens reflight_flight or Re-flight ULD Contents.
   It is called when a colour codeed row has been double clicked on. These are the bags that although
   were not on the flight were not authorised for that flight. 
   A search is performed of future flights to see if the bag can be found (i.e. the user has checked in
   on a new flight).
**********************************************************************************************************/
 
   /** 
      Get the split bag tag
   **/
   split_tags (pBagTag, lTag1, lTag2a, lTag2b, lTag3, lTagNoNum, lTagType);

   IF ( lTagType <= 0) THEN
       raise_application_error(INVALID_TAG, 'Invalid Tag');
   END IF;

   /**
      Get the site id and airport code for the user.
   **/
   SELECT s.site_id,
          trim(s.airport_code)
   INTO lSiteId,
        lAirportCode
   FROM tagtrak_users tu,
        agents        a,
        site_info     s
   WHERE tu.user_id   = pUserId
   AND   a.agent_code = tu.agent_code
   AND   s.site_id    = a.site_id;

   /**
      Count the number of bags found on future flights.
   **/
   SELECT COUNT(*)
   INTO lCount
   FROM flight_outbound_v1 f,
        pax p,
        bag b
   WHERE to_date(f.flight_date||f.flight_sch_time, 'DDMONHH24MI')  > sysdate
   AND   f.flight_status     != 'PD'
   AND   f.site_id            = lSiteId
   AND   f.flight_id          = p.flight_id
   AND   p.flight_id         != pOrigFlightId
   AND   (   (    p.pax_initials    = pPaxInit
              AND p.pax_surname     = pPaxSurname)
          OR (    p.pax_sequence_no = 999))
   AND   p.pax_id             = b.pax_id
   AND   (   (    b.bag_tag2a = lTag2a
              AND b.bag_tag3  = lTag3)
          OR (    b.bag_tag2b = lTag2b
              AND b.bag_tag3  = lTag3));

   IF lCount > 1
   THEN
      /** 
         Bag has been found one more than one future flight.
         Get the first open flight details
      **/
      OPEN lcurFlightList (lSiteId, 
                           pOrigFlightId,
                           pPaxInit,
                           pPaxSurname,
                           lTag2a,
                           lTag2b,
                           lTag3);
      FETCH lcurFlightList
      INTO pFlightId(1),
           pFlightNo(1),
           pFlightDate(1),
           pFlightSchTime(1),
           lPaxId;

      CLOSE lcurFlightList;

   ELSIF lCount = 0
   THEN
      /**
         The passenger cannot be found on any open later flight, so bag is a Zombie bag.
      **/

     pRetStatus(1) := ZOMBIE_BAG; /* 'Zombie Bag, passenger not on an open flight' */
     RETURN;

   ELSIF lCount = 1
   THEN
   /**
      Bag/Passenger found on one flight. Get the flight details, update the reflight_bag
      table to indicate the bag has been found on a flight and return.
   **/
   BEGIN
      SELECT f.flight_id,
             f.flight_no,
             f.flight_date,
             f.flight_sch_time,
             p.pax_id
      INTO pFlightId(1),
           pFlightNo(1),
           pFlightDate(1),
           pFlightSchTime(1),
           lPaxId
      FROM flight_outbound_v1 f,
           pax    p,
           bag    b
      WHERE to_date(f.flight_date||f.flight_sch_time, 'DDMONHH24MI')  > sysdate
      AND   f.flight_status     != 'PD'
      AND   f.site_id            = lSiteId
      AND   f.flight_id          = p.flight_id
      AND   p.flight_id         != pOrigFlightId
      AND   (   (    p.pax_initials    = pPaxInit
                 AND p.pax_surname     = pPaxSurname)
             OR (    p.pax_sequence_no = 999))
      AND   p.pax_id             = b.pax_id
      AND   (   (    b.bag_tag2a = lTag2a
                 AND b.bag_tag3  = lTag3)
             OR (    b.bag_tag2b = lTag2b
                 AND b.bag_tag3  = lTag3));
   EXCEPTION
   WHEN TOO_MANY_ROWS
   THEN
      pRetStatus(1) := REVAL_BAG_MULTI_PAX; /* 'Reval Bag tag found under multiple passengers' */
      RETURN;
   WHEN OTHERS
   THEN
        runtime_stage := 1;
        RAISE;
   END;

   END IF;

  
   select pax_id 
     INTO pOrigPaxId
    FROM  reflight_pax
   WHERE  site_id      = lSiteId
     AND  flight_id    = pOrigFlightId
     AND  pax_initials = pPaxInit
     AND  pax_surname  = pPaxSurname;
  

  
   get_tag_component(pOrigPaxId,lTag2b,lTag3,lTag2a,lTag1);

   /* Generate the reflighted bag tag */
   lnewTag := build_user_bag_tag( p_t1=> lTag1
                                 ,p_t2a=>lTag2a
                                 ,p_t2b=>lTag2b
                                 ,p_t3=>lTag3
                                 ,p_user_pref=>'N');
   

   UPDATE reflight_bag
   SET reflight_needed      = 'F',
       reflight_flight_id   = pFlightId(1),
       reflight_flight_no   = pFlightNo(1),
       reflight_flight_date = pFlightDate(1),
       reflight_flight_time = pFlightSchTime(1),
       reflight_bag_tag     = lnewTag,
       reflight_bag_tag1    = lTag1,
       reflight_bag_tag2a   = lTag2a,
       reflight_bag_tag2b   = lTag2b,
       reflight_bag_tag3    = lTag3,
       reflight_pax_id      = lPaxId,
       reflight_date_time   = sysdate + get_offset.airport_code(lAirportCode)
   WHERE flight_id          = pOrigFlightId
   AND   (   (    bag_tag2a = lTag2a
              AND bag_tag3  = lTag3)
          OR (    bag_tag2b = lTag2b
              AND bag_tag3  = lTag3))
   AND   pax_id             = (SELECT pax_id
                               FROM reflight_pax
                               WHERE site_id      = lSiteId
                               AND   flight_id    = pOrigFlightId
                               AND   pax_initials = pPaxInit
                               AND   pax_surname  = pPaxSurname);
  
EXCEPTION
WHEN OTHERS
THEN

IF ( SQLCODE <= -20000  ) THEN
         
   RAISE;
      
ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.BAG_SEARCH - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;


END bag_search;

-------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------

FUNCTION bag_search_2(p_user_id        IN tagtrak_users.user_id%TYPE,
                      p_bag_tag        IN bag.bag_tag%TYPE,
                      p_orig_flight_id IN flight.flight_id%TYPE,
                      p_pax_init       IN pax.pax_initials%TYPE,
                      p_pax_surname    IN pax.pax_surname%TYPE)
RETURN SYS_REFCURSOR IS

  l_flight_id        gFlightIdType;
  l_flight_no        gFlightNoType;
  l_flight_date      gFlightDateType;
  l_flight_sch_time  gFlightSchTimeType;
  l_ret_status       gReturnStatusType;

  l_results_tab      reflight_bag_search_tab := reflight_bag_search_tab();
  l_cursor SYS_REFCURSOR;

BEGIN

  bag_search(p_user_id,
             p_bag_tag,
             p_orig_flight_id,
             p_pax_init,
             p_pax_surname,
             l_flight_id,
             l_flight_no,
             l_flight_date,
             l_flight_sch_time,
             l_ret_status);
                           
  FOR n IN nvl(l_flight_id.FIRST,1)..nvl(l_flight_id.LAST,0) LOOP

    BEGIN

      --make room in sql table.
      l_results_tab.extend(1);
      --use constructor to populate.
      l_results_tab(n) := reflight_bag_search_rec(l_flight_id(n),
                                                  l_flight_no(n),
                                                  l_flight_date(n),
                                                  l_flight_sch_time(n),
                                                  l_ret_status(n));

    EXCEPTION WHEN no_data_found THEN
      NULL; --don't worry about gaps in the sql tables.

    END;

  END LOOP;

  --Build ref cursor from sql table.
  OPEN l_cursor FOR
  SELECT *
  FROM TABLE(CAST(l_results_tab AS reflight_bag_search_tab));

  RETURN l_cursor;

END bag_search_2;

/****************************************************************************************************
* Procedure    - copy_uld_bags
*
* Description  - This procedure is called from the procedures tu_flight_trig or uld_unloaded. These
*                in turn are called from the triggers tu_flight or tu_ulds respectively.
*                It will copy all data about the ULD/Passengers/Bags into the re-flighting data tables
*                for all bags loaded in the ULD.
*****************************************************************************************************/

PROCEDURE copy_uld_bags   (pSiteId        IN     reflight_ulds.site_id%TYPE,
                           pFlightId      IN     reflight_ulds.flight_id%TYPE,
                           pULDNo         IN     reflight_ulds.uld_no%TYPE,
                           pULDNoOfBags   IN     reflight_ulds.uld_no_of_bags%TYPE)                         
AS
runtime_stage  number := 0;
BEGIN

   /**
      Copy the missed ULD into the reflight_ulds table
   **/

   INSERT INTO reflight_ulds
   (uld_no,
    flight_id,
    site_id,
    uld_no_of_bags)
   SELECT pULDNo,
          pFlightId,
          pSiteId,
          pULDNoOfBags
   FROM ulds u
   WHERE 0 = (SELECT count(*)
              FROM reflight_ulds ru
              WHERE ru.uld_no = pULDNo
              AND   ru.flight_id = pFlightId)
   AND   flight_id = pFlightId
   AND   uld_no    = pULDNo;

   /**
      Copy all passengers of bags in the ULD to the reflight_pax table
   **/

   INSERT INTO reflight_pax
   (pax_id,
    flight_id,
    site_id,
    pax_initials,
    pax_surname,
    pax_full_inits,
    pax_destination,
    pax_class,
    departure_airport_code)
   SELECT p.pax_id,
          pFlightId,
          pSiteId,
          p.pax_initials,
          p.pax_surname,
          p.pax_full_inits,
          p.pax_destination,
          p.pax_class,
          p.departure_airport_code
   FROM pax           p,
        bag           b
   WHERE p.flight_id     = pFlightId
   AND   p.pax_id        = b.pax_id
   AND   b.loaded_status = 'Y'
   AND   b.uld_flight_id = pFlightId
   AND   b.uld_no        = pULDNo
   AND   0               = (SELECT count(*)
                            FROM reflight_bag
                            WHERE flight_id = pFlightId
                            AND   pax_id    = b.pax_id)
   GROUP BY p.pax_id,
            pFlightId,
            pSiteId,
            p.pax_initials,
            p.pax_surname,
            p.pax_full_inits,
            p.pax_destination,
            p.pax_class,
            p.departure_airport_code;
   /**
      Now copy the bag details to the reflight_bag table.            
   **/
   INSERT INTO reflight_bag
   (bag_tag,
    pax_id,
    flight_id,
    site_id,
    bag_tag1,
    bag_tag2a,
    bag_tag2b,
    bag_tag3,
    bag_dest,
    uld_no,
    uld_flight_id,
    orig_pax_id,
    orig_pax_init,
    orig_pax_surname,
    reflight_needed)
   SELECT b.bag_tag,
          b.pax_id,
          pFlightId,
          pSiteId,
          b.bag_tag1,
          b.bag_tag2a,
          b.bag_tag2b,
          b.bag_tag3,
          b.bag_destination,
          b.uld_no,
          b.uld_flight_id,
          p.pax_id,
          p.pax_initials,
          p.pax_surname,
          decode(p.authority_to_load_dcs, 
                 'Y', decode(p.delete_marker,
                             0, b.authority_to_load_dcs,
                             'N'),
                 'N')
   FROM pax p,
        bag b
   WHERE p.flight_id     = pFlightId
   AND   p.pax_id        = b.pax_id
   AND   b.loaded_status = 'Y'
   AND   b.uld_flight_id = pFlightId
   AND   b.uld_no        = pULDNo
   AND   (b.bag_tag2a, b.bag_tag2b, b.bag_tag3) NOT IN (SELECT bag_tag2a, bag_tag2b, bag_tag3
                                                        FROM reflight_bag
                                                        WHERE flight_id = pFlightId
                                                        AND   site_id   = pSiteId)
   GROUP BY b.bag_tag,
            b.pax_id,
            pFlightId,
            pSiteId,
            b.bag_tag1,
            b.bag_tag2a,
            b.bag_tag2b,
            b.bag_tag3,
            b.bag_destination,
            b.uld_no,
            b.uld_flight_id,
            p.pax_id,
            p.pax_initials,
            p.pax_surname,
            decode(p.authority_to_load_dcs, 
                   'Y', decode(p.delete_marker,
                               0, b.authority_to_load_dcs,
                               'N'),
                   'N');

EXCEPTION
WHEN OTHERS
THEN
IF ( SQLCODE <= -20000  ) THEN
         
   RAISE;
      
ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.COPY_ULD_BAGS - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;

END copy_uld_bags;
      
/****************************************************************************************************
* Procedure    - get_bag_info
*
* Description  - This procedure is called from the MWS or Scanner when a bag tag has been 
*                entered/selected for reflighting. It will try to find the flight and passenger the 
*                bag is on. If more than one flight is found a list of possible flights are returned.
*                If more than one passenger is found a list of possible passengers are returned. 
*                It will initially be called with the Bag Tag to find, but if multiple flights or 
*                passengers were found and the user has selected the individual ones, it will be 
*                called with the Flight No/Date/Time and Pax details.
*                Once there is a single match the additional information is retrieved and returned.
*****************************************************************************************************/

PROCEDURE get_bag_info    (pUserId          IN     tagtrak_users.user_id%TYPE,
                           pBagTag          IN     bag.bag_tag%TYPE,
                           pFlightNoIn      IN     flight.flight_no%TYPE,
                           pFlightDateIn    IN     flight.flight_date%TYPE,
                           pFlightSchTimeIn IN     flight.flight_sch_time%TYPE,
                           pPaxInitIn       IN     pax.pax_initials%TYPE,
                           pPaxSurnameIn    IN     pax.pax_surname%TYPE,
                           pFlightNo           OUT gFlightNoType,
                           pFlightDate         OUT gFlightDateType,
                           pFlightSchTime      OUT gFlightSchTimeType,
                           pOrigCarrier        OUT gCarrierType,
                           pPaxInit            OUT gPaxInitialsType,
                           pPaxSurname         OUT gPaxSurnameType,
                           pPaxClass           OUT gPaxClassType,
                           pFlightId           OUT gFlightIdType,
                           pDestination        OUT gDestinationType,
                           pPaxId              OUT gPaxIdType,
                           pPrintTagDef        OUT gPrintTagType,
                           pPrintTagOth        OUT gPrintTagType,
                           pWTMsg              OUT gWTMsgType,
                           pRetStatus          OUT gReturnStatusType)
AS

   lSiteId          site_info.site_id%TYPE;
   lAirportCode     site_info.airport_code%TYPE;
   lFlightStatus    flight.flight_status%TYPE;
   lReflightNeeded  reflight_bag.reflight_needed%TYPE;
   lCarrier         reflight_flight.carrier_alpha_code%TYPE;
   lCount           NUMBER (6);
   lNextFlightId    flight.flight_id%TYPE;
   lNextFlightNo    flight.flight_no%TYPE;
   lNextFlightDate  flight.flight_date%TYPE;
   lNextSchedTime   flight.flight_sch_time%TYPE;
   lNextPaxId       pax.pax_id%TYPE;
   lNextDestination pax.pax_destination%TYPE;
   n                NUMBER(6);
   
   lTag1            bag.bag_tag1%TYPE;
   lTag2a           bag.bag_tag2a%TYPE;
   lTag2b           bag.bag_tag2b%TYPE;
   lTag3            bag.bag_tag3%TYPE;
   lTagType         NUMBER;
   lTagNoNum        VARCHAR2(10);
   runtime_stage  number := 0;  -- Used for code tracecability
   

   CURSOR lcurMultiFlight (cpSiteId site_info.site_id%TYPE,
                           cptag2a  bag.bag_tag2a%TYPE,
                           cpTag2b  bag.bag_tag2b%TYPE,
                           cpTag3   bag.bag_tag3%TYPE)
   IS
      SELECT f.flight_id,
             f.flight_no,
             f.flight_date,
             f.flight_sch_time
      FROM flight_outbound_v1 f,
           pax    p,
           bag    b
      WHERE f.flight_status   != 'PD'
      AND   f.flight_id        = p.flight_id
      AND   f.site_id          = cpSiteId
      AND   p.pax_id           = b.pax_id
      AND (   (    b.bag_tag2a = cpTag2a 
               AND b.bag_tag3  = cpTag3)
           OR (    b.bag_tag2b = cpTag2b 
               AND b.bag_tag3 = cpTag3))
      UNION
      SELECT rf.flight_id,
             rf.flight_no,
             rf.flight_date,
             rf.scheduled_time
      FROM reflight_flight rf,
           reflight_bag    rb
      WHERE rf.flight_id        = rb.flight_id
      AND   rb.reflight_needed  = 'Y'
      AND (   (    rb.bag_tag2a = cpTag2a 
               AND rb.bag_tag3  = cpTag3)
           OR (    rb.bag_tag2b = cpTag2b 
               AND rb.bag_tag3  = cpTag3));

   CURSOR lcurMultiPax (cpSiteId      site_info.site_id%TYPE,
                        cpAirportCode site_info.airport_code%TYPE,
                        cpFlightId    flight.flight_id%TYPE,
                        cptag2a       bag.bag_tag2a%TYPE,
                        cpTag2b       bag.bag_tag2b%TYPE,
                        cpTag3        bag.bag_tag3%TYPE)
   IS
      SELECT p.pax_id,
             p.pax_initials,
             p.pax_surname,
             p.pax_destination
      FROM pax p,
           bag b
      WHERE p.departure_airport_code = cpAirportCode
      AND   p.flight_id              = cpFlightId
      AND   p.pax_id                 = b.pax_id
      AND (   (    b.bag_tag2a       = cpTag2a 
               AND b.bag_tag3        = cpTag3)
           OR (    b.bag_tag2b       = cpTag2b 
               AND b.bag_tag3 = cpTag3))
      UNION
      SELECT rp.pax_id,
             rp.pax_initials,
             rp.pax_surname,
             rp.pax_destination
      FROM reflight_pax rp,
           reflight_bag rb
      WHERE rp.pax_id           = rb.pax_id
      AND   rp.flight_id        = rb.flight_id
      AND   rb.site_id          = cpSiteId
      AND   rb.flight_id        = cpFlightId
      AND (   (    rb.bag_tag2a = cpTag2a 
               AND rb.bag_tag3  = cpTag3)
           OR (    rb.bag_tag2b = cpTag2b 
               AND rb.bag_tag3  = cpTag3));

BEGIN
   pRetStatus(1) := 0;
   n:= 1;
   depair :=  get_offset.get_user_location(pUserId); 
   
   /*Split the tags to get tag1, tag2a, tag2b, tag3, tagno and tag_type */
   split_tags (pBagTag, lTag1, lTag2a, lTag2b, lTag3, lTagNoNum, lTagType);

   IF ( lTagType <= 0) THEN
       raise_application_error(INVALID_TAG, 'Invalid Tag');
   END IF;
        

  /* IF trim(lTag2a) IS NOT NULL
   THEN
      pOrigCarrier(n) := lTag2a;
   ELSE
      BEGIN

         SELECT alpha_code
         INTO pOrigCarrier(n)
         FROM carrier_lookup
         WHERE bar_code = lTag2b;

      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         raise_application_error(RFLT_ACCESS_DENIED, 'Access Denied' );
      WHEN OTHERS
      THEN

        runtime_stage := 1;
        RAISE;
        
      END;

   END IF; */

   /**
      Get the site id and airport code for the user.
   **/

   SELECT s.site_id,
          trim(s.airport_code)
   INTO lSiteId,
        lAirportCode
   FROM tagtrak_users tu,
        agents        a,
        site_info     s
   WHERE tu.user_id   = pUserId
   AND   a.agent_code = tu.agent_code
   AND   s.site_id    = a.site_id;


   IF trim(pFlightNoIn) IS NOT NULL
   THEN
      /** 
         The user has selected one flight, but we do not have the flight_id. Get the flight_id.
      **/

      pFlightNo(n)      := pFlightNoIn;
      pFlightDate(n)    := pFlightDateIn;
      pFlightSchTime(n) := pFlightSchTimeIn;

      SELECT flight_id, 
             carrier_alpha_code
      INTO pFlightId(n),
           lCarrier
      FROM flight_outbound_v1
      WHERE flight_no       = pFlightNoIn
      AND   flight_date     = pFlightDateIn
      AND   flight_sch_time = pFlightSchTimeIn
      AND   site_id         = lSiteId
      UNION
      SELECT flight_id,
             carrier_alpha_code
      FROM reflight_flight
      WHERE flight_no       = pFlightNoIn
      AND   flight_date     = pFlightDateIn
      AND   scheduled_time  = pFlightSchTimeIn
      AND   site_id         = lSiteId;

   ELSE /** pFlightNo IS NULL **/
      /** 
         First time though, so see if the bag can be found. 
         Look to see if the bag is one a non 'PD'ed flight or in the reflighting data
         where the bag has not already been reflighted.
      **/
      BEGIN

         SELECT f.flight_id,
                f.flight_no,
                f.flight_date,
                f.flight_sch_time,
                f.carrier_alpha_code
         INTO pFlightId(n),
              pFlightNo(n),
              pFlightDate(n),
              pFlightSchTime(n),
              lCarrier
         FROM flight_outbound_v1 f,
              pax    p,
              bag    b
         WHERE f.flight_status   != 'PD'
         AND   f.flight_id        = p.flight_id
         AND   f.site_id          = lSiteId
         AND   p.pax_id           = b.pax_id
         AND (   (    b.bag_tag2a = lTag2a 
                  AND b.bag_tag3  = lTag3)
              OR (    b.bag_tag2b = lTag2b 
                  AND b.bag_tag3  = lTag3))
         UNION
         SELECT rf.flight_id,
                rf.flight_no,
                rf.flight_date,
                rf.scheduled_time,
                rf.carrier_alpha_code
         FROM reflight_flight rf,
              reflight_bag    rb
         WHERE rf.flight_id        = rb.flight_id
         AND   rb.reflight_needed  = 'Y'
         AND (   (    rb.bag_tag2a = lTag2a 
                  AND rb.bag_tag3  = lTag3)
              OR (    rb.bag_tag2b = lTag2b 
                  AND rb.bag_tag3  = lTag3));
 
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         /**
           No bags found, check if this is because the reflighting is not turned on or because
           it is a zombie bag 
         **/
         BEGIN

            SELECT f.flight_no
            INTO pFlightNo(n)
            FROM flight f,
                 pax    p,
                 bag    b
            WHERE f.flight_status   = 'PD'
            AND   f.flight_id        = p.flight_id
            AND   f.site_id          = lSiteId
            AND   p.pax_id           = b.pax_id
            AND (   (    b.bag_tag2a = lTag2a
                     AND b.bag_tag3  = lTag3)
                 OR (    b.bag_tag2b = lTag2b
                     AND b.bag_tag3  = lTag3));

         EXCEPTION
            WHEN OTHERS
            THEN
             /**
               Bag tag not found, return a status of 'ZOMBIE BAG'
             **/
            raise_application_error(ZOMBIE_BAG, 'Zombie Bag, passenger not on an open flight' );

         END;


         SELECT COUNT(*)
         INTO lCount
         FROM carrier_site_config
         WHERE site_id      = lSiteId
         AND   alpha_code   = SUBSTR (pFlightNo(n), 1, 3)
         AND   config_item  = 'REFLIGHTING'
         AND   config_value = 'Y';

 
         IF ( lCount = 0 )
         THEN
            /**
              This flight has been post departed but re-flighting is not turned so return 'RE-FLIGHTING NOT ON'
            **/

            raise_application_error(NO_REFLIGHTING, 'Carrier does not have re-flighting set on' );
         ELSE
            /**
              This flight is post departed and reflighting is turned on yet the bag isn't in the reflighting tables
              so return a status of 'ZOMBIE BAG'.

              Note that in theory this point should never be reached as bags on post departed flights with reflighting
              turned on should always be sent to the reflighting tables however this ELSE is needed to catch instances where
              issues elsewhere in the system have caused this not to be the case.
            **/

            raise_application_error(ZOMBIE_BAG, 'Zombie Bag, passenger not on an open flight' );
         END IF;

      WHEN TOO_MANY_ROWS
      THEN 
         /** 
            The bag tag has been found on multiple flights, so get all of the flight details
            and return them with a status of 'DUPLICATE TAG FLIGHT' 
         **/
         n := 1;
         FOR i IN lcurMultiFlight(lSiteId, lTag2a, lTag2b, lTag3)
         LOOP
            pFlightId(n)      := i.flight_id;
            pFlightNo(n)      := i.flight_no;
            pFlightDate(n)    := i.flight_date;
            pFlightSchTime(n) := i.flight_sch_time;
            n := n + 1;
         END LOOP;            

         pRetStatus(1) := MULTIPLE_TAG_FLIGHT; /* 'Bag tag found on multiple flights' */
         RETURN;
      WHEN OTHERS
      THEN

        runtime_stage := 2;
        RAISE;

     END;

   END IF;



   /**
      Use the flights carrier code to check whether reflighting is turned on
   **/

   pOrigCarrier(n) := SUBSTR (pFlightNo(n), 1, 3);

   SELECT COUNT(*)
   INTO lCount
   FROM carrier_site_config
   WHERE site_id      = lSiteId
   AND   alpha_code   = pOrigCarrier(n)
   AND   config_item  = 'REFLIGHTING'
   AND   config_value = 'Y';

   IF lCount = 0
   THEN
      /**
         Carrier does not have re-flighting on so return 'RE-FLIGHTING NOT ON'
      **/

      raise_application_error(NO_REFLIGHTING, 'Carrier does not have re-flighting set on' );

   END IF;
   /**
      Carrier has re-flighting set on.
   **/

   BEGIN
      SELECT tag_default,
             tag_other,
             send_wt_msg
      INTO pPrintTagDef(n),
           pPrintTagOth(n),
           pWTMsg(n)
      FROM reflight_setup
      WHERE carrier = pOrigCarrier(n)
      AND   site_id = lSiteId;

   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      pPrintTagDef(n) := NULL;
      pPrintTagOth(n) := NULL;
   END;



   /**
      From this point on we have a flight_id.
      Check to see if the user handles the carrier.
   **/

   SELECT count(*)
   INTO lCount
   FROM tagtrak_users     tu,
        contracted_agents ca
   WHERE tu.user_id          = pUserId
   AND ca.agent_code         = tu.agent_code
   AND ca.airline_alpha_code = pOrigCarrier(n);

   IF lCount = 0
   THEN
      /** 
         See if any of the users carriers will accept the bag from the bag_tag carrier.
      **/

      SELECT count(*)
      INTO lCount
      FROM reflight_accept_from
      WHERE accept_from_carrier = pOrigCarrier(n)
      AND   carrier            IN (SELECT c.airline_alpha_code
                                   FROM contracted_agents c,
                                        tagtrak_users     t
                                   WHERE t.user_id     = pUserId
                                   AND   t.agent_code = c.agent_code);

      IF lCount = 0
      THEN
         /**
            The user does not handle the carrier, so return 'ACCESS DENIED'
         **/

         raise_application_error(RFLT_ACCESS_DENIED, 'Access Denied' );

      END IF;

   END IF;

   

   IF trim(pPaxInitIn) IS NOT NULL
   THEN
      /**
         The user has selected one passenger, but we do not have the PaxId, so go and get it
      **/
      pPaxInit(n)    := pPaxInitIn;
      pPaxSurname(n) := pPaxSurnameIn;

      BEGIN
         SELECT p.pax_id,
                p.pax_class,
                p.pax_destination
         INTO pPaxId(n),
              pPaxClass(n),
              pDestination(n)
         FROM pax p,
              bag b
         WHERE p.flight_id        = pFlightId(n)
         AND   p.pax_initials     = pPaxInitIn
         AND   p.pax_surname      = pPaxSurnameIn
         AND   p.pax_id           = b.pax_id
         AND (   (    b.bag_tag2a = lTag2a 
                  AND b.bag_tag3  = lTag3)
              OR (    b.bag_tag2b = lTag2b 
                  AND b.bag_tag3  = lTag3))
         UNION
         SELECT rp.pax_id,
                rp.pax_class,
                rp.pax_destination
         FROM reflight_pax rp,
              reflight_bag rb
         WHERE rb.pax_id           = rp.pax_id
         AND   rp.flight_id        = pFlightId(n)
         AND   rp.pax_initials     = pPaxInitIn
         AND   rp.pax_surname      = pPaxSurnameIn
         AND (   (    rb.bag_tag2a = lTag2a 
                  AND rb.bag_tag3  = lTag3)
              OR (    rb.bag_tag2b = lTag2b 
                  AND rb.bag_tag3  = lTag3));
      EXCEPTION
      WHEN TOO_MANY_ROWS
      THEN
         /**
            There is an unresolvable duplicate for the passenger.
         **/
         raise_application_error(UNRESOLVABLE_DUP_PAX, 'Unresolvable duplicate bag tag' );

      WHEN OTHERS
      THEN
        runtime_stage := 3;
        RAISE;
      END;
   ELSE /** pPaxName IS NULL **/
      /** 
         The passenger is not yet known.
         Find the passenger.
      **/

      BEGIN       
         SELECT p.pax_id,
                p.pax_initials,
                p.pax_surname,
                p.pax_destination,
                p.pax_class
         INTO pPaxId(n),
              pPaxInit(n),
              pPaxSurname(n),
              pDestination(n),
              pPaxClass(n)
         FROM pax p,
              bag b
         WHERE p.departure_airport_code = lAirportCode
         AND   p.flight_id              = pFlightId(n)
         AND   p.pax_id                 = b.pax_id
         AND (   (    b.bag_tag2a       = lTag2a 
                  AND b.bag_tag3        = lTag3)
              OR (    b.bag_tag2b       = lTag2b 
                  AND b.bag_tag3        = lTag3))
         UNION
         SELECT rp.pax_id,
                rp.pax_initials,
                rp.pax_surname,
                rp.pax_destination,
                rp.pax_class
         FROM reflight_pax rp,
              reflight_bag rb
         WHERE rp.pax_id           = rb.pax_id
         AND   rp.flight_id        = rb.flight_id
         AND   rb.site_id          = lSiteId
         AND   rb.flight_id        = pFlightId(n)
         AND (   (    rb.bag_tag2a = lTag2a 
                  AND rb.bag_tag3  = lTag3)
              OR (    rb.bag_tag2b = lTag2b 
                  AND rb.bag_tag3  = lTag3));

      EXCEPTION
      WHEN TOO_MANY_ROWS
      THEN
         /**
            Bag has been found under multiple passengers. Get the passenger information and 
            return 'DUPLICATE TAG PAX'.
         **/

         n := 1;
         FOR i IN lcurMultiPax (lSiteId, lAirportCode, pFlightId(n), lTag2a, lTag2b, lTag3)
         LOOP
            pPaxId(n)       := i.pax_id;
            pPaxInit(n)     := i.pax_initials;
            pPaxSurname(n)  := i.pax_surname;
            pDestination(n) := i.pax_destination;
            n := n + 1;
         END LOOP;

         pRetStatus(1) := MULTIPLE_TAG_PAX; /* 'Bag tag found under multiple passengers' */
         RETURN;
      WHEN OTHERS
      THEN
        runtime_stage := 4;
        RAISE;
      END;
   END IF;

   /** 
      We now have a Flight Id and Pax Id.
      See if it is in the reflighting data and if the bag requires reflighting or not.
   **/

   SELECT count(*)
   INTO lCount
   FROM reflight_bag
   WHERE flight_id          = pFlightId(n)
   AND   pax_id             = pPaxId(n)
   AND (   (    bag_tag2a = lTag2a 
            AND bag_tag3  = lTag3)
        OR (    bag_tag2b = lTag2b 
            AND bag_tag3  = lTag3))
   AND   reflight_needed    = 'Y';

   IF lCount = 0
   THEN
      /**
         Bag does not need re-flighting, but as we have found it under one passenger in the 
         flight tables on a non 'PD'ed flight the user can revalidate the bag to travel on the flight found.
      **/
      pRetStatus(1) := REVALIDATE_BAG;
   END IF;
   
EXCEPTION
WHEN OTHERS
THEN
     
IF ( SQLCODE <= -20000  ) THEN
         
   RAISE;
      
ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.GET_BAG_INFO - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;

END get_bag_info;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

FUNCTION get_bag_info_2(p_user_id             IN tagtrak_users.user_id%TYPE,
                        p_bag_tag             IN bag.bag_tag%TYPE,
                        p_flight_no_in        IN flight.flight_no%TYPE,
                        p_flight_date_in      IN flight.flight_date%TYPE,
                        p_flight_sch_time_in  IN flight.flight_sch_time%TYPE,
                        p_pax_init_in         IN pax.pax_initials%TYPE,
                        p_pax_surname_in      IN pax.pax_surname%TYPE)
RETURN SYS_REFCURSOR IS

  l_flight_no        gFlightNoType;
  l_flight_date      gFlightDateType;
  l_flight_sch_time  gFlightSchTimeType;
  l_orig_carrier     gCarrierType;
  l_pax_init         gPaxInitialsType;
  l_pax_surname      gPaxSurnameType;
  l_pax_class        gPaxClassType;
  l_flight_id        gFlightIdType;
  l_destination      gDestinationType;
  l_pax_id           gPaxIdType;
  l_print_tag_def    gPrintTagType;
  l_print_tag_oth    gPrintTagType;
  l_wt_msg           gWTMsgType;
  l_ret_status       gReturnStatusType;
                           
  l_results_tab      reflight_bag_info_tab := reflight_bag_info_tab();
  l_cursor           SYS_REFCURSOR;
  
BEGIN

  get_bag_info(p_user_id,
               p_bag_tag,
               p_flight_no_in,
               p_flight_date_in,
               p_flight_sch_time_in,
               p_pax_init_in,
               p_pax_surname_in,
               l_flight_no,
               l_flight_date,
               l_flight_sch_time,
               l_orig_carrier,
               l_pax_init,
               l_pax_surname,
               l_pax_class,
               l_flight_id,
               l_destination,
               l_pax_id,
               l_print_tag_def,
               l_print_tag_oth,
               l_wt_msg,
               l_ret_status);

  FOR n IN nvl(l_flight_no.FIRST,1)..nvl(l_flight_no.LAST,0) LOOP

    BEGIN

      --make room in sql table.
      l_results_tab.extend(1);
      --use constructor to populate.
      l_results_tab(n) := reflight_bag_info_rec(l_flight_no(n),
                                                l_flight_date(n),
                                                l_flight_sch_time(n),
                                                l_orig_carrier(n),
                                                l_pax_init(n),
                                                l_pax_surname(n),
                                                l_pax_class(n),
                                                l_flight_id(n),
                                                l_destination(n),
                                                l_pax_id(n),
                                                l_print_tag_def(n),
                                                l_print_tag_oth(n),
                                                l_wt_msg(n),
                                                l_ret_status(n));

    EXCEPTION WHEN no_data_found THEN
      NULL; --don't worry about gaps in the sql tables.

    END;

  END LOOP;

  --Build ref cursor from sql table.
  OPEN l_cursor FOR
  SELECT *
  FROM TABLE(CAST(l_results_tab AS reflight_bag_info_tab));

  RETURN l_cursor;

END get_bag_info_2;

/****************************************************************************************************
* Procedure    - reflight_one_bag
*
* Description  - This procedure is called by the MWS and scanner when the bag is to be reflighted.
*                The bag will be copied on to the new flight as a rush bag and the re-flighting data
*                updated to indicate the bag has been reflighted and lastly send the WorldTracer message.
*****************************************************************************************************/

PROCEDURE reflight_one_bag(pUserId        IN     tagtrak_users.user_id%TYPE,
                           pOrigFlightId  IN     flight.flight_id%TYPE,
                           pPaxId         IN     pax.pax_id%TYPE,
                           pBagTag        IN     bag.bag_tag%TYPE,
                           pFlightId      IN     flight.flight_id%TYPE,
                           pFlightNo      IN     flight.flight_no%TYPE,
                           pReasonForLoss IN     reflight_bag.reason_for_loss%TYPE,
                           pFaultStation  IN     reflight_bag.fault_station%TYPE,
                           pSupInfo1      IN     VARCHAR2,
                           pSupInfo2      IN     VARCHAR2,
                           pWTRef         IN     reflight_bag.worldtracer_ref%TYPE,
                           pTeletypeList  IN     VARCHAR2)
AS
   lAirportCode        site_info.airport_code%TYPE;
   lSiteId             site_info.site_id%TYPE;
   lLoginName          tagtrak_users.loginname%TYPE;
   lDateTime           DATE;
   lOrigCarrier        flight.carrier_alpha_code%TYPE;
   lCarrier            flight.carrier_alpha_code%TYPE;
   lFlightId           flight.flight_id%TYPE;
   lSendWTMsg          reflight_setup.send_wt_msg%TYPE;
   lTeletypeList       VARCHAR2 (160);
   lFlightDest         flight_dests.flight_destination%TYPE;
   lNewPaxId           pax.pax_id%TYPE;
   lReflightFlightDate flight.flight_date%TYPE;
   lReflightFlightTime flight.flight_sch_time%TYPE;

   lTag1             bag.bag_tag1%TYPE;
   lTag2a            bag.bag_tag2a%TYPE;
   lTag2b            bag.bag_tag2b%TYPE;
   lTag3             bag.bag_tag3%TYPE;
   lTagType          NUMBER;
   lTagNoNum         VARCHAR2(10);
   msgid             NUMBER;
   lnewTag         bag.bag_tag%TYPE;
         
   
   
   runtime_stage  number := 0;

   CURSOR lcurTeletypeAdr (cpSiteId   site_info.site_id%TYPE,
                           cpCarrier  flight.carrier_alpha_code%TYPE,
                           cpFlightId flight.flight_id%TYPE)
   IS
      SELECT teletype_address
      FROM reflight_dest_tx 
      WHERE site_id            = cpSiteId
      AND   carrier            = cpCarrier
      AND   dest               = (SELECT reflight_flight_dest
                                  FROM reflight_bag
                                  WHERE reflight_flight_id = cpFlightId
                                  GROUP BY reflight_flight_dest);

   CURSOR lcurFlightDest (cpFlightId flight.flight_id%TYPE)
   IS
      SELECT flight_destination
      FROM flight_dests
      WHERE flight_id = cpFlightId
      AND   dest_order = 1
      ORDER BY dest_source desc;

BEGIN

   

   depair :=  get_offset.get_user_location(pUserId);
   
   

   lFlightId := pFlightId;

   /* Split the tags to get tag1, tag2a, tag2b, tag3, tagno and tag_type */
   split_tags (pBagTag, lTag1, lTag2a, lTag2b, lTag3, lTagNoNum, lTagType);

   IF ( lTagType <= 0) THEN
       raise_application_error(INVALID_TAG, 'Invalid Tag');
   END IF;


   /**
      Get the site_id, users login name and airport code for the flight
   **/
   SELECT s.site_id,
          trim(s.airport_code),
          tu.loginname
   INTO lSiteId,
        lAirportCode,
        lLoginName
   FROM tagtrak_users tu,
        agents        a,
        site_info     s
   WHERE tu.user_id   = pUserId
   AND   a.agent_code = tu.agent_code
   AND   s.site_id    = a.site_id;

   /**
      Set the date/time so the same date/time is used to record the action.
   **/
   lDateTime := sysdate + get_offset.airport_code(lAirportCode);


   

   IF (lFlightId IS NULL)
   OR (lFlightId = 0)
   THEN
      /**
         We have not been sent a flight_id, butt see if the flight exists.
      **/
      BEGIN
         SELECT flight_id 
         INTO lFlightId
         FROM flight_outbound_v1
         WHERE site_id         = lSiteId
         AND   departure_time > sysdate
         AND   trim(flight_no) = trim(pFlightNo)
         AND   flight_status  != 'PD';
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         lFlightId := 0;
      END;
   END IF;
      
   /**
      Check to see if the flight selected for re-flighting exists.
      If we have a Flight_id it does otherwise this would be blank
      If the flight does not exist the bag cannot be put on to it, this will be done
      from the flight trigger when the flight is created. 
   **/
    
  

   IF (lFlightId IS NOT NULL)
   AND (lFlightId <> 0)
   THEN

      /**
         Get the carrier for the original flight
      **/
      SELECT carrier_alpha_code
      INTO lOrigCarrier
      FROM flight
      WHERE flight_id = pOrigFlightId;
    
    

      reflighting.add_reflight_bag(pUserId,
                                   pOrigFlightId,
                                   pPaxId,
                                   pBagTag,
                                   lFlightId,
                                   lAirportCode,
                                   lSiteId,
                                   lDateTime,
                                   lNewPaxId);

      /** 
           Get the flight date/time and destination for the flight
           the bag has been re-flighted to.
      **/

      



      SELECT flight_date,
             flight_sch_time
      INTO lReflightFlightDate,
           lReflightFlightTime
      FROM flight f
      WHERE f.flight_id  = lFlightId;

      OPEN lcurFlightDest (lFlightId);

      FETCH lcurFlightDest
      INTO lFlightDest;

      CLOSE lcurFlightDest;


      

      /** 
           Update the reflight_bag table with the reflighted flight info.
      **/

      get_tag_component(pPaxId,lTag2b,lTag3,lTag2a,lTag1);
   
      /* Generate the reflighted bag tag */
      lnewTag := build_user_bag_tag( p_t1=> lTag1
                                    ,p_t2a=>lTag2a
                                    ,p_t2b=>lTag2b
                                    ,p_t3=>lTag3
                                    ,p_user_pref=>'N');

            

      UPDATE reflight_bag
      SET reflight_needed      = 'A',
          reflight_flight_id   = lFlightId,
          reflight_flight_no   = pFlightNo,
          reflight_flight_date = lReflightFlightDate,
          reflight_flight_time = lReflightFlightTime,
          reflight_flight_dest = lFlightDest,
          reflight_bag_tag     = lnewTag,
          reflight_bag_tag1    = lTag1,
          reflight_bag_tag2a   = lTag2a,
          reflight_bag_tag2b   = lTag2b,
          reflight_bag_tag3    = lTag3,
          reflight_pax_id      = lNewPaxId,
          reflight_login_name  = lLoginName,
          reflight_date_time   = lDateTime,
          reason_for_loss      = pReasonForLoss,
          fault_station        = pFaultStation,
          worldtracer_ref      = pWTRef
      WHERE site_id   = lSiteId
      AND   flight_id = pOrigFlightId
      AND   pax_id    = pPaxId
      AND  (   (    bag_tag2a = lTag2a 
                AND bag_tag3  = lTag3)
            OR (    bag_tag2b = lTag2b 
                AND bag_tag3  = lTag3));

      /** 
         If the carrier has selected to send the WorldTracer FWD message, call the procedure to 
         construct and send the message.
      **/
      BEGIN
         SELECT send_wt_msg
         INTO lSendWTMsg
         FROM reflight_setup
         WHERE site_id = lSiteId
         AND   carrier = lOrigCarrier;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         lSendWTMsg := 'N';
      END;

      IF lSendWTMsg = 'Y'
      THEN

         reflighting.worldtracer_msg (lSiteId,
                                      pBagTag,
                                      lTag2a,
                                      lTag2b,
                                      lTag3,
                                      lAirportCode,
                                      pOrigFlightId,
                                      lOrigCarrier,
                                      pPaxId,
                                      lFlightId,
                                      pFlightNo,
                                      lFlightDest,
                                      pPaxId,
                                      pFaultStation,
                                      pReasonForLoss,
                                      pSupInfo1,
                                      pSupInfo2,
                                      pWTRef,
                                      pTeletypeList);

      END IF;

   ELSE
      /** 
           Update the reflight_bag table with the reflighted flight info.
      **/

      get_tag_component(pPaxId,lTag2b,lTag3,lTag2a,lTag1);
   
      /* Generate the reflighted bag tag */
      lnewTag := build_user_bag_tag( p_t1=> lTag1
                                    ,p_t2a=>lTag2a
                                    ,p_t2b=>lTag2b
                                    ,p_t3=>lTag3
                                    ,p_user_pref=>'N');
 
      UPDATE reflight_bag
      SET reflight_needed     = 'A',
          reflight_flight_no  = pFlightNo,
          reflight_bag_tag    = pBagTag,
          reflight_bag_tag1   = lTag1,
          reflight_bag_tag2a  = lTag2a,
          reflight_bag_tag2b  = lTag2b,
          reflight_bag_tag3   = lTag3,
          reflight_login_name = lLoginName,
          reflight_date_time  = lDateTime,
          reason_for_loss     = pReasonForLoss,
          fault_station       = pFaultStation,
          worldtracer_ref     = pWTRef
      WHERE site_id   = lSiteId
      AND   flight_id = pOrigFlightId
      AND   pax_id    = pPaxId
      AND  (   (    bag_tag2a = lTag2a 
                AND bag_tag3  = lTag3)
            OR (    bag_tag2b = lTag2b 
                AND bag_tag3  = lTag3));
   END IF;

EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
   raise_application_error(UNRESOLVABLE_DUP_PAX, 'Unresolvable duplicate bag tag' );
WHEN OTHERS
THEN
   IF ( SQLCODE = -20027 ) THEN
      raise_application_error(UNRESOLVABLE_DUP_PAX, 'Unresolvable duplicate bag tag' );
   ELSE
        IF ( SQLCODE <= -20000  ) THEN
         
                RAISE;
      
        ELSE  
           
                  err_msg  := SQLERRM;
                  err_code := SQLCODE;

                  log_oracle_error( 'REFLIGHTING.REFLIGHT_ONE_BAG - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

                  raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

       END IF;
   END IF;
END reflight_one_bag;

/****************************************************************************************************
* Procedure    - remove_reflight_data
*
* Description  - This procedure is called by the tu_flight trigger when the flight status changes to back from 
*                'PD'. It will remove all reflighting data where the bag has not already been re-flighted.
*                If a bag has already been re-flighted then the pax/flight and if relevant the uld records
*                will remain.
*****************************************************************************************************/

PROCEDURE remove_reflight_data (pFlightId         IN     flight.flight_id%TYPE)
AS
   lCount         NUMBER(6) := 0;
   runtime_stage  number    := 0;
BEGIN
   BEGIN
      DELETE reflight_flight
      WHERE flight_id = pFlightId
      AND 0 = (SELECT count(*)
               FROM reflight_bag
               WHERE flight_id       = pFlightId
               AND   reflight_needed = 'A');
   EXCEPTION
   WHEN OTHERS
   THEN
      runtime_stage := 1;
      RAISE;
   END;

   SELECT count(*)
   INTO lCount
   FROM reflight_flight
   WHERE flight_id = pFlightId;

   IF lCount > 0
   THEN
      /** 
         There is a flight still, so now remove the bags that have not been reflighted.
      **/

      DELETE reflight_bag
      WHERE flight_id = pFlightId
      AND   reflight_needed != 'A';

      /**
         Now delete the passengers that do not have and reflighted bags for the flight
      **/
      DELETE reflight_pax
      WHERE flight_id = pFlightId
      AND   pax_id NOT IN (SELECT pax_id
                           FROM reflight_bag);

      /**
         Lastly delete the ulds from the reflight_ulds table that do not contain any bags that
         have already been re-flighted.
      **/

      DELETE reflight_ulds
      WHERE flight_id = pFlightId
      AND   uld_no NOT IN (SELECT uld_no
                           FROM reflight_bag
                           WHERE flight_id = pFlightId
                           AND   trim(uld_no) IS NOT NULL);

   END IF;

EXCEPTION
 WHEN oTHERS THEN
  

IF ( SQLCODE <= -20000  ) THEN
         
   RAISE;
      
ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.remove_reflight_data - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;   

END remove_reflight_data;

/****************************************************************************************************
* Procedure    - select_flights
*
* Description  - This procedure is called by the MWS and scanner to obtain a list of possible flights
*                to which the bag could be re-flighted.
*****************************************************************************************************/

PROCEDURE select_flights  (pUserId        IN     tagtrak_users.user_id%TYPE,
                           pBagTag        IN     bag.bag_tag%TYPE,
                           pPaxId         IN     pax.pax_id%TYPE,
                           pOrigFlightId  IN    flight.flight_id%TYPE,
                           pFlightId         OUT gFlightIdType,
                           pFlightNo         OUT gFlightNoType,
                           pFlightDate       OUT gFlightDateType,
                           pFlightSchTime    OUT gFlightSchTimeType,
                           pFlightDest       OUT gFlightDestType,
                           pReflightSpace    OUT gReflightSpaceType)
AS
   lDest             reflight_pax.pax_destination%TYPE;
   lCarrier          reflight_flight.carrier_alpha_code%TYPE;
   lChildCarrier     reflight_flight.carrier_alpha_code%TYPE;
   lSiteId           reflight_flight.site_id%TYPE;
   k                 BINARY_INTEGER;
   
   /* For Carrier to Same Carrier */
   l_sc_MinsInFuture     reflight_setup.mins_in_future%TYPE;
   l_sc_BagsPerFlight    reflight_setup.bags_per_flight%TYPE;
   l_sc_DateTime         DATE;
   
   /* For Bulk Bind */
   TYPE lBagsPerFlight     IS TABLE OF reflight_accept_from.bags_per_flight%TYPE;
   TYPE lMinsInFuture      IS TABLE OF reflight_accept_from.mins_in_future%TYPE;
   TYPE lAcceptFromCarrier IS TABLE OF reflight_accept_from.accept_from_carrier%TYPE;
   TYPE lDateTime          IS TABLE OF DATE;
   TYPE lReflightSpace2    IS TABLE OF NUMBER(6);
   
   t_lBagsPerFlight      lBagsPerFlight;
   t_lMinsInFuture       lMinsInFuture;
   t_lAcceptFromCarrier  lAcceptFromCarrier;
   t_lDateTime           lDateTime;
   t_lReflightSpace      lReflightSpace2;
   
   lFlightOrder      NUMBER(6) := 0;
   lReflightSpace    NUMBER(6);
   l_ra_BagsPerFlight NUMBER(6) := 0;
   lCount            NUMBER(6);
   lFlightDestList   VARCHAR2(20);
   i                 NUMBER(6);

   lTag1             bag.bag_tag1%TYPE;
   lTag2a            bag.bag_tag2a%TYPE;
   lTag2b            bag.bag_tag2b%TYPE;
   lTag3             bag.bag_tag3%TYPE;
   lTagType          NUMBER;
   lTagNoNum         VARCHAR2(10);
   runtime_stage  number := 0;
   lfltno            flight.flight_no%TYPE   := NULL;
   lfltdate          flight.flight_date%TYPE := NULL;



   CURSOR lcurFlights (cpFlightId flight.flight_id%TYPE)
   IS
      SELECT flight_id,
             flight_no,
             flight_date,
             flight_sch_time
      FROM work_flight_select
      WHERE orig_flight_id = cpFlightId
      ORDER BY weighting ASC;

   CURSOR lcurFlightDests (cpFlightId flight.flight_id%TYPE)
   IS
      SELECT flight_destination
      FROM flight_dests
      WHERE flight_id = cpFlightId
      ORDER BY dest_order;

   CURSOR lcurFlightList (cpFlightId flight.flight_id%TYPE)
   IS
      SELECT flight_id,
             flight_no,
             flight_date,
             flight_sch_time,
             flight_dests,
             reflight_space
      FROM reflight_flight_list_temp
      WHERE orig_flight_id = cpFlightId
      ORDER BY flight_order ASC;

BEGIN
   depair :=  get_offset.get_user_location(pUserId);
   /**
      First clear out working tables, just to be sure.
   **/
   BEGIN
      DELETE reflight_flight_list_temp
      WHERE orig_flight_id = pOrigFlightId;
   EXCEPTION
   WHEN OTHERS
   THEN
        runtime_stage := 1;
        RAISE;
   END;

   BEGIN
      DELETE work_flight_select
      WHERE orig_flight_id = pOrigFlightId;
   EXCEPTION
   WHEN OTHERS
   THEN
       runtime_stage := 2;
        RAISE;
   END;

   /* Split the tags to get tag1, tag2a, tag2b, tag3, tagno and tag_type */
   split_tags (pBagTag, lTag1, lTag2a, lTag2b, lTag3, lTagNoNum, lTagType);

   IF ( lTagType <= 0) THEN
       raise_application_error(INVALID_TAG, 'Invalid Tag');
   END IF;

   /**
      Get the passenger destination, bag_tag carrier and site_id
   **/
   SELECT p.pax_destination,
          b.site_id
   INTO lDest,
        lSiteId
   FROM reflight_pax p,
        reflight_bag b
   WHERE p.pax_id           = b.pax_id
   AND   b.flight_id        = pOrigFlightId
   AND   b.pax_id           = pPaxId
   AND (   (    b.bag_tag2a = lTag2a
            AND b.bag_tag3  = lTag3)
        OR (    b.bag_tag2b = lTag2b
            AND b.bag_tag3  = lTag3));

   /* Need to get the carrier of the flight */
   flight_no(pOrigFlightId,lfltno,lfltdate);
   lCarrier := trim(substr(lfltno,1,3));

   /**************************************************/
   /* Get the number of minutes and bags for carrier */
   /* same carrier. Also set the minimum date/time   */
   /* to look for the flights from                   */
   /**************************************************/
   
   SELECT mins_in_future,
          bags_per_flight,
          sysdate + get_offset.airport_code(lSiteId) + mins_in_future/1440 
   INTO   l_sc_MinsInFuture,
          l_sc_BagsPerFlight,
          l_sc_DateTime   
   FROM   reflight_setup
   WHERE  site_id = lSiteId
   AND    carrier = lCarrier;  
    
   
   /***************************************************************************************/
   /* Get the number of minutes in the future to look for flights and while we are here   */
   /* the bags per flight. Also set the minimum date/time criteria to look for the flights*/
   /* from                                                                                */
   /***************************************************************************************/
   
   SELECT mins_in_future,
          bags_per_flight,
          accept_from_carrier,
          sysdate + get_offset.airport_code(lSiteId) + mins_in_future/1440
   BULK COLLECT INTO t_lMinsInFuture,
                     t_lBagsPerFlight,
                     t_lAcceptFromCarrier,
                     t_lDateTime                 
   FROM reflight_accept_from
   WHERE site_id = lSiteId
   AND   carrier = lCarrier;
    
    
   /**************************************************************/
   /* Rule: Select the next direct operational flight to the same*/
   /* destination, using the same carrier i.e. QF->QF look for   */
   /* direct flight on the same carrier                          */
   /**************************************************************/
   BEGIN
      INSERT INTO work_flight_select
      (orig_flight_id,
       flight_id,
       flight_no,
       flight_date,
       flight_sch_time,
       weighting)
      SELECT pOrigFlightId,
             f.flight_id,
             f.flight_no,
             f.flight_date,
             f.flight_sch_time,
             f.departure_time
      FROM flight_outbound_v1 f,
           flight_dests d
      WHERE d.flight_destination = lDest
      AND   f.flight_status   NOT IN ('PD','FF')
      AND   d.dest_order         = 1
      AND   d.flight_id          = f.flight_id
      AND   f.flight_id          != pOrigFlightId
      AND   l_sc_BagsPerFlight   > (SELECT COUNT(*)
                                    FROM reflight_bag
                                    WHERE reflight_flight_id = f.flight_id)
      AND   to_date((f.flight_date||to_char( l_sc_DateTime ,'YYYY')
                   ||f.flight_sch_time),'DDMONYYYYHH24MISS') >  l_sc_DateTime 
      AND   f.carrier_alpha_code = lCarrier
      AND   f.site_id = lSiteId
      GROUP BY pOrigFlightId,
               f.flight_id,
               f.flight_no,
               f.flight_date,
               f.flight_sch_time,
               f.departure_time;

   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   END;
   
   /*******************************************************/
   /*   Look for direct flight on different carrier       */
   /*******************************************************/
   
   IF t_lAcceptFromCarrier.COUNT > 0 THEN
   BEGIN    
     FORALL i in t_lAcceptFromCarrier.FIRST..t_lAcceptFromCarrier.LAST                   
      INSERT INTO work_flight_select
      (orig_flight_id,
       flight_id,
       flight_no,
       flight_date,
       flight_sch_time,
       weighting)
      SELECT pOrigFlightId,
             f.flight_id,
             f.flight_no,
             f.flight_date,
             f.flight_sch_time,
             f.departure_time + 30/1440
      FROM flight_outbound_v1   f,
           flight_dests         d,
           reflight_accept_from r
      WHERE d.flight_destination  = lDest
      AND   d.dest_order          = 1
      AND   d.flight_id           = f.flight_id
      AND   f.flight_status   NOT IN ('PD','FF')
      AND   f.flight_id          != pOrigFlightId
      AND   to_date((f.flight_date||to_char(t_lDateTime(i),'YYYY')
                   ||f.flight_sch_time),'DDMONYYYYHH24MISS') > t_lDateTime(i)
      AND   r.bags_per_flight     > (SELECT COUNT(*)
                                     FROM reflight_bag
                                     WHERE reflight_flight_id = f.flight_id)
      AND   r.site_id             = lSiteId
      AND   r.carrier             = lCarrier
      AND   f.carrier_alpha_code  = t_lAcceptFromCarrier(i) 
      AND   f.flight_id      NOT IN (SELECT flight_id
                                     FROM work_flight_select
                                     WHERE orig_flight_id = pOrigFlightId)
      GROUP BY pOrigFlightId,
               f.flight_id,
               f.flight_no,
               f.flight_date,
               f.flight_sch_time,
               f.departure_time + 30/1440;

   
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN

        runtime_stage := 3;
        RAISE;
   END;
   END IF;

   /**
      See how many flights we have found that depart within 60 mins after the future number of mins.
   **/
   SELECT count(*)
   INTO lCount
   FROM work_flight_select
   WHERE to_date(flight_date||flight_sch_time, 'DDMONHH24MI') < l_sc_DateTime + 61/1440
   AND   orig_flight_id = pOrigFlightId;

   IF lCount < 5
   THEN
      /**
         Not enough flights found, so need to continue looking.
         Look for flight on the same carrier, whose routing will go to the desired destination.
      **/

            
      BEGIN
                          
         INSERT INTO work_flight_select
         (orig_flight_id,
          flight_id,
          flight_no,
          flight_date,
          flight_sch_time,
          weighting)
         SELECT pOrigFlightId,
                f.flight_id,
                f.flight_no,
                f.flight_date,
                f.flight_sch_time,
                f.departure_time + 60/1440
         FROM flight_outbound_v1 f,
              flight_dests       d
         WHERE d.flight_destination  = lDest
         AND   d.flight_id           = f.flight_id
         AND   f.flight_status   NOT IN ('PD','FF')
         AND   d.dest_order         > 1
         AND   0                    = (SELECT count(*)
                                       FROM flight_dests
                                       WHERE dest_order < d.dest_order
                                       AND   flight_id = f.flight_id
                                       AND   flight_destination IN (SELECT avoid_dest
                                                                    FROM reflight_avoid_dest
                                                                    WHERE site_id = lSiteId
                                                                    AND   carrier = lCarrier))
         AND   f.flight_id          != pOrigFlightId
         AND   l_sc_BagsPerFlight   > (SELECT COUNT(*)
                                        FROM reflight_bag
                                        WHERE reflight_flight_id = f.flight_id)
         AND   to_date((f.flight_date||to_char(l_sc_DateTime,'YYYY')
                      ||f.flight_sch_time),'DDMONYYYYHH24MISS') > l_sc_DateTime
         AND   f.carrier_alpha_code  = lCarrier
         AND   f.flight_id      NOT IN (SELECT flight_id
                                        FROM work_flight_select
                                        WHERE orig_flight_id = pOrigFlightId)
         AND   f.site_id = lSiteId
         GROUP BY pOrigFlightId,
                  f.flight_id,
                  f.flight_no,
                  f.flight_date,
                  f.flight_sch_time,
                  f.departure_time + 60/1440;

      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         NULL;
      WHEN OTHERS
      THEN
        runtime_stage := 4;
        RAISE;

      END;
     
      /**
         See how many flights we have found that depart within 90 mins after the future number of mins.
      **/
      SELECT count(*)
      INTO lCount
      FROM work_flight_select
      WHERE to_date(flight_date||flight_sch_time, 'DDMONHH24MI') < l_sc_DateTime + 91/1440
      AND   orig_flight_id = pOrigFlightId;

      IF lCount < 5
      THEN
         /**
            Not enough flights found, so need to continue looking.
            Look for flight on the same carrier, whose routing will go to the desired destination.
         **/
         IF t_lAcceptFromCarrier.COUNT > 0 THEN
         BEGIN
            FORALL i in t_lAcceptFromCarrier.FIRST..t_lAcceptFromCarrier.LAST   
            INSERT INTO work_flight_select
            (orig_flight_id,
             flight_id,
             flight_no,
             flight_date,
             flight_sch_time,
             weighting)
            SELECT pOrigFlightId,
                   f.flight_id,
                   f.flight_no,
                   f.flight_date,
                   f.flight_sch_time,
                   f.departure_time + 90/1440
            FROM flight_outbound_v1   f,
                 flight_dests         d,
                 reflight_accept_from r
            WHERE d.flight_destination  = lDest
            AND   d.flight_id           = f.flight_id
            AND   f.flight_status   NOT IN ('PD','FF')
            AND   d.dest_order          > 1
            AND   0                     = (SELECT count(*)
                                           FROM flight_dests
                                           WHERE dest_order < d.dest_order
                                           AND   flight_id = f.flight_id
                                           AND   flight_destination IN (SELECT avoid_dest
                                                                        FROM reflight_avoid_dest
                                                                        WHERE site_id = lSiteId
                                                                        AND   carrier = lCarrier))
            AND   f.flight_id          != pOrigFlightId
            AND   r.bags_per_flight     > (SELECT COUNT(*)
                                           FROM reflight_bag
                                           WHERE reflight_flight_id = f.flight_id)
            AND   to_date((f.flight_date||to_char(t_lDateTime(i),'YYYY')
                         ||f.flight_sch_time),'DDMONYYYYHH24MISS') > t_lDateTime(i)
            AND   r.site_id             = lSiteId
            AND   r.carrier             = lCarrier
            AND   f.carrier_alpha_code  = t_lAcceptFromCarrier(i)
            AND   f.flight_id      NOT IN (SELECT flight_id
                                           FROM work_flight_select
                                           WHERE orig_flight_id = pOrigFlightId)
            GROUP BY pOrigFlightId,
                     f.flight_id,
                     f.flight_no,
                     f.flight_date,
                     f.flight_sch_time,
                     f.departure_time + 90/1440;

           EXCEPTION
           WHEN NO_DATA_FOUND
           THEN
              NULL;
           WHEN OTHERS
           THEN
                                runtime_stage := 5;
                                RAISE;
                        END;
          END IF;
         /**
            See how many flights we have found that depart within 120 mins after the future number of mins.
         **/
         SELECT count(*)
         INTO lCount
         FROM work_flight_select
         WHERE to_date(flight_date||flight_sch_time, 'DDMONHH24MI') < l_sc_DateTime + 121/1440
         AND   orig_flight_id = pOrigFlightId;

         IF lCount < 5
         THEN
            /**
               Not enough flights found, so need to continue looking.
               Look for flight on the same carrier, who has bags destined to go to the desired destination.
            **/
           
            /* First look at the flights that have has bags loaded */
            BEGIN
           
               INSERT INTO work_flight_select
               (orig_flight_id,
                flight_id,
                flight_no,
                flight_date,
                flight_sch_time,
                weighting)
               SELECT pOrigFlightId,
                      f.flight_id,
                      f.flight_no,
                      f.flight_date,
                      f.flight_sch_time,
                      f.departure_time + 120/1440
               FROM flight_outbound_v1 f,
                    uld_dests          u
               WHERE l_sc_BagsPerFlight  > (SELECT COUNT(*)
                                             FROM reflight_bag
                                             WHERE reflight_flight_id = f.flight_id)
               AND   u.uld_destination    = lDest
               AND   u.flight_id          = f.flight_id
               AND   f.flight_status   NOT IN ('PD','FF')
               AND   0                     = (SELECT count(*)
                                              FROM flight_dests
                                              WHERE flight_id = f.flight_id
                                              AND   flight_destination IN (SELECT avoid_dest
                                                                           FROM reflight_avoid_dest
                                                                           WHERE site_id = lSiteId
                                                                           AND   carrier = lCarrier))
               AND   f.flight_id         != pOrigFlightId
               AND   to_date((f.flight_date||to_char(l_sc_DateTime,'YYYY')
                            ||f.flight_sch_time),'DDMONYYYYHH24MISS') > l_sc_DateTime
               AND   f.carrier_alpha_code = lCarrier
               AND   f.flight_id      NOT IN (SELECT flight_id
                                              FROM work_flight_select
                                              WHERE orig_flight_id = pOrigFlightId)
               AND   f.site_id = lSiteId
               GROUP BY pOrigFlightId,
                        f.flight_id,
                        f.flight_no,
                        f.flight_date,
                        f.flight_sch_time,
                        f.departure_time + 120/1440;

            EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
            WHEN OTHERS
            THEN
                runtime_stage := 6;
                RAISE;
            END;
           

            /* Now look at flights that have onward destinations */
            
            BEGIN
                 
               INSERT INTO work_flight_select
               (orig_flight_id,
                flight_id,
                flight_no,
                flight_date,
                flight_sch_time,
                weighting)
               SELECT pOrigFlightId,
                      f.flight_id,
                      f.flight_no,
                      f.flight_date,
                      f.flight_sch_time,
                      f.departure_time + 120/1440
               FROM onward_flight      o,
                    pax                p,
                    flight_outbound_v1 f
               WHERE o.dest                = ldest
               AND   p.pax_id              = o.pax_id
               AND   0                     = (SELECT count(*)
                                              FROM onward_flight onward_flight,
                                                   pax           pax
                                              WHERE pax.flight_id            = f.flight_id
                                              AND   onward_flight.pax_id     = pax.pax_id
                                              AND   onward_flight.dest_order < o.dest_order
                                              AND   onward_flight.dest      IN (SELECT avoid_dest
                                                                                FROM reflight_avoid_dest
                                                                                WHERE site_id = lSiteId
                                                                                AND   carrier = lCarrier))
               AND   f.flight_id           = p.flight_id
               AND   f.flight_id          != pOrigFlightId
               AND   to_date((f.flight_date||to_char(l_sc_DateTime,'YYYY')
                            ||f.flight_sch_time),'DDMONYYYYHH24MISS') > l_sc_DateTime
               AND   l_sc_BagsPerFlight   > (SELECT COUNT(*)
                                              FROM reflight_bag
                                              WHERE reflight_flight_id = f.flight_id)
               AND   f.carrier_alpha_code  = lCarrier
               AND   f.flight_id      NOT IN (SELECT flight_id
                                              FROM work_flight_select
                                              WHERE orig_flight_id = pOrigFlightId)
               AND   f.site_id = lSiteId
               GROUP BY pOrigFlightId,
                        f.flight_id,
                        f.flight_no,
                        f.flight_date,
                        f.flight_sch_time,
                        f.departure_time + 120/1440;

            EXCEPTION
            WHEN NO_DATA_FOUND
            THEN
               NULL;
            WHEN OTHERS
            THEN
              runtime_stage := 7;
                RAISE;
            END;
          
            /**
               See how many flights we have found that depart within 150 mins after the future number of mins.
            **/
            SELECT count(*)
            INTO lCount
            FROM work_flight_select
            WHERE to_date(flight_date||flight_sch_time, 'DDMONHH24MI') < l_sc_DateTime + 151/1440
            AND   orig_flight_id = pOrigFlightId;

            IF lCount < 5
            THEN
               /**
                  Not enough flights found, so need to continue looking.
                  Look for flight on a different carrier, whose has bags destined to go to the desired destination.
               **/
               IF t_lAcceptFromCarrier.COUNT > 0 THEN
               BEGIN
                  FORALL i in t_lAcceptFromCarrier.FIRST..t_lAcceptFromCarrier.LAST   
                  INSERT INTO work_flight_select
                  (orig_flight_id,
                   flight_id,
                   flight_no,
                   flight_date,
                   flight_sch_time,
                   weighting)
                  SELECT pOrigFlightId,
                         f.flight_id,
                         f.flight_no,
                         f.flight_date,
                         f.flight_sch_time,
                         f.departure_time + 150/1440
                  FROM flight_outbound_v1   f,
                       uld_dests            u,
                       work_flight_select   w,
                       reflight_accept_from r
                  WHERE t_lBagsPerFlight(i)   > (SELECT COUNT(*)
                                                FROM reflight_bag
                                                WHERE reflight_flight_id = f.flight_id)
                  AND   u.uld_destination     = lDest
                  AND   u.flight_id           = f.flight_id
                  AND   0                     = (SELECT count(*)
                                                 FROM flight_dests
                                                 WHERE flight_id = f.flight_id
                                                 AND   flight_destination IN (SELECT avoid_dest
                                                                              FROM reflight_avoid_dest
                                                                              WHERE site_id = lSiteId
                                                                              AND   carrier = lCarrier))
                  AND   f.flight_id          != pOrigFlightId
                  AND   to_date((f.flight_date||to_char(t_lDateTime(i),'YYYY')
                               ||f.flight_sch_time),'DDMONYYYYHH24MISS') > t_lDateTime(i)
                  AND   r.site_id             = lSiteId
                  AND   r.carrier             = lCarrier
                  AND   f.carrier_alpha_code  = t_lAcceptFromCarrier(i)
                  AND   f.flight_id      NOT IN (SELECT flight_id
                                                 FROM work_flight_select
                                                 WHERE orig_flight_id = pOrigFlightId)
                  GROUP BY pOrigFlightId,
                           f.flight_id,
                           f.flight_no,
                           f.flight_date,
                           f.flight_sch_time,
                           f.departure_time + 150/1440;

               EXCEPTION
               WHEN NO_DATA_FOUND
               THEN
                  NULL;
               WHEN OTHERS
               THEN
                        runtime_stage := 8;
                                RAISE;
               END;
               END IF;

               /* Now look at flights that have onward destinations */
               IF t_lAcceptFromCarrier.COUNT > 0 THEN
               BEGIN
                  FORALL i in t_lAcceptFromCarrier.FIRST..t_lAcceptFromCarrier.LAST  
                  INSERT INTO work_flight_select
                  (orig_flight_id,
                   flight_id,
                   flight_no,
                   flight_date,
                   flight_sch_time,
                   weighting)
                  SELECT pOrigFlightId,
                         f.flight_id,
                         f.flight_no,
                         f.flight_date,
                         f.flight_sch_time,
                         f.departure_time + 180/1440
                  FROM onward_flight        o,
                       pax                  p,
                       flight_outbound_v1   f,
                       work_flight_select   w,
                       reflight_accept_from r
                  WHERE o.dest                = ldest
                  AND   p.pax_id              = o.pax_id
                  AND   0                     = (SELECT count(*)
                                                 FROM onward_flight onward_flight,
                                                      pax           pax
                                                 WHERE pax.flight_id            = f.flight_id
                                                 AND   onward_flight.pax_id     = pax.pax_id
                                                 AND   onward_flight.dest_order < o.dest_order
                                                 AND   onward_flight.dest      IN (SELECT avoid_dest
                                                                                   FROM reflight_avoid_dest
                                                                                   WHERE site_id = lSiteId
                                                                                   AND   carrier = lCarrier))
                  AND   f.flight_id           = p.flight_id
                  AND   f.flight_id          != pOrigFlightId
                  AND   to_date((f.flight_date||to_char(t_lDateTime(i),'YYYY')
                               ||f.flight_sch_time),'DDMONYYYYHH24MISS') > t_lDateTime(i)
                  AND   t_lBagsPerFlight(i)    > (SELECT COUNT(*)
                                                 FROM reflight_bag
                                                 WHERE reflight_flight_id = f.flight_id)
                  AND   r.site_id             = lSiteId
                  AND   r.carrier             = lCarrier
                  AND   f.carrier_alpha_code  = t_lAcceptFromCarrier(i)
                  AND   f.flight_id      NOT IN (SELECT flight_id
                                                 FROM work_flight_select
                                                 WHERE orig_flight_id = pOrigFlightId)
                  GROUP BY pOrigFlightId,
                           f.flight_id,
                           f.flight_no,
                           f.flight_date,
                           f.flight_sch_time,
                           f.departure_time + 180/1440;

               EXCEPTION
               WHEN NO_DATA_FOUND
               THEN


                  raise_application_error (NO_SUITABLE_FLIGHTS, 'No suitable flights found to reflight the bag to');


               WHEN OTHERS
               THEN
                runtime_stage := 9;
                RAISE;
               END;
               END IF;
            END IF;
         END IF;
      END IF;
   END IF;

   SELECT count(*)
   INTO lCount
   FROM work_flight_select
   WHERE orig_flight_id = pOrigFlightId;

   IF lCount = 0
   THEN


      raise_application_error (NO_SUITABLE_FLIGHTS, 'No suitable flights found to reflight the bag to');


   END IF;

   
   FOR i IN lcurFlights (pOrigFlightId)
   LOOP
      lFlightOrder := lFlightOrder + 1;
      
       /* Need to get the carrier of the flight to be reflighted to */
       /* We also would need to check this reflighted to carrier */
       /* to see whether it is an acceptfromcarrier              */
      flight_no(i.flight_id,lfltno,lfltdate);
      lChildCarrier := trim(substr(lfltno,1,3));
      
      /* Check for the carrier and get the correct bagperflight value */
      l_ra_BagsPerFlight := NULL;
      
      IF t_lAcceptFromCarrier.COUNT > 0 THEN 
      FOR k in t_lAcceptFromCarrier.FIRST..t_lAcceptFromCarrier.LAST  LOOP
         IF t_lAcceptFromCarrier(k) = lChildCarrier THEN
            l_ra_BagsPerFlight := t_lBagsPerFlight(k);
         END IF;
      END LOOP;
      END IF;
      
      /************************************************************************/
      /* If there are reflighted to carriers avaiable, use ther bagsperflight */
      /* otherwise use the main carrier's bagsperflight to determine the count*/
      /************************************************************************/
      IF t_lAcceptFromCarrier.COUNT > 0 AND l_ra_BagsPerFlight IS NOT NULL THEN
         SELECT l_ra_BagsPerFlight - count(bag_tag)
         INTO lReflightSpace
         FROM reflight_bag rb, reflight_accept_from ra
         WHERE rb.reflight_flight_id = i.flight_id
         AND  rb.site_id = ra.site_id
         AND  ra.carrier = lCarrier
         AND  ra.site_id = lSiteId
         AND  ra.accept_from_carrier = lChildCarrier;
      ELSE
         SELECT l_sc_BagsPerFlight - count(bag_tag)
         INTO lReflightSpace
         FROM reflight_bag
         WHERE reflight_flight_id = i.flight_id;
      END IF;
      
      lCount := 0;

      FOR j IN lcurFlightDests (i.flight_id)
      LOOP
         IF lCount = 1
         THEN
            lFlightDestList := lFlightDestList||','||j.flight_destination;
         ELSE
            lFlightDestList := j.flight_destination;
         END IF;
      END LOOP;

      
      INSERT INTO reflight_flight_list_temp
      (orig_flight_id,
       flight_order,
       flight_id,
       flight_no,
       flight_date,
       flight_sch_time,
       flight_dests,
       reflight_space)
      VALUES
      (pOrigFlightId,
       lFlightOrder,
       i.flight_id,
       i.flight_no,
       i.flight_date,
       i.flight_sch_time,
       lFlightDestList,
       lReflightSpace);
       

      IF lFlightOrder = 4
      THEN
         EXIT;
      END IF;
   END LOOP;

   /* Count for the Other carriers here */
   i := 1;

   FOR cur IN lcurFlightList (pOrigFlightId)
   LOOP
      pFlightId(i)      := cur.flight_id;
      pFlightNo(i)      := cur.flight_no;
      pFlightDate(i)    := cur.flight_date;
      pFlightSchTime(i) := cur.flight_sch_time;
      pFlightDest(i)    := cur.flight_dests;
      pReflightSpace(i) := cur.reflight_space;
      i := i + 1;
   END LOOP;

   BEGIN
      DELETE work_flight_select
      WHERE orig_flight_id = pOrigFlightId;
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN

              runtime_stage := 10;
                RAISE;
   END;

  BEGIN
      DELETE reflight_flight_list_temp
      WHERE orig_flight_id = pOrigFlightId;
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   WHEN OTHERS
   THEN
              runtime_stage := 11;
                RAISE;
   END;
 
EXCEPTION
WHEN NO_DATA_FOUND THEN

   raise_application_error (NO_SUITABLE_FLIGHTS, 'No suitable flights found to reflight the bag to');

   COMMIT;
WHEN OTHERS
THEN
IF ( SQLCODE <= -20000  ) THEN

   RAISE;

ELSE

     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.SELECT_FLIGHTS - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;

END select_flights;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

FUNCTION select_flights_2(p_user_id         IN tagtrak_users.user_id%TYPE,
                          p_bag_tag         IN bag.bag_tag%TYPE,
                          p_pax_id          IN pax.pax_id%TYPE,
                          p_orig_flight_id  IN flight.flight_id%TYPE)
RETURN SYS_REFCURSOR IS

  l_flight_id        gFlightIdType;
  l_flight_no        gFlightNoType;
  l_flight_date      gFlightDateType;
  l_flight_sch_time  gFlightSchTimeType;
  l_flight_dest      gFlightDestType;
  l_reflight_space   gReflightSpaceType;
  
  l_results_tab      reflight_select_flights_tab := reflight_select_flights_tab();
  l_cursor           SYS_REFCURSOR;
  no_flight          EXCEPTION;
  PRAGMA EXCEPTION_INIT(no_flight, -20115);
  
BEGIN

  BEGIN
  
    select_flights(p_User_Id,
                   p_Bag_Tag,
                   p_Pax_Id,
                   p_Orig_Flight_Id,
                   l_flight_id,
                   l_flight_no,
                   l_flight_date,
                   l_flight_sch_time,
                   l_flight_dest,
                   l_reflight_space);
  
  EXCEPTION WHEN no_flight THEN
    --for this error we just want to return no data rather than an error
    --message.
    NULL;
    
  END;
                 
  FOR n IN nvl(l_flight_id.FIRST,1)..nvl(l_flight_id.LAST,0) LOOP

    BEGIN

      --make room in sql table.
      l_results_tab.extend(1);
      --use constructor to populate.
      l_results_tab(n) := reflight_select_flights_rec(l_flight_id(n),
                                                      l_flight_no(n),
                                                      l_flight_date(n),
                                                      l_flight_sch_time(n),
                                                      l_flight_dest(n),
                                                      l_reflight_space(n));

    EXCEPTION WHEN no_data_found THEN
      NULL; --don't worry about gaps in the sql tables.

    END;

  END LOOP;

  --Build ref cursor from sql table.
  OPEN l_cursor FOR
  SELECT *
  FROM TABLE(CAST(l_results_tab AS reflight_select_flights_tab));
  
  RETURN l_cursor;
  
END select_flights_2;

/***********************************************************************************************************/

PROCEDURE ti_flight_dests_trig (pFlightId         IN     flight.flight_id%TYPE,
                                pFlightDest       IN     flight.flight_no%TYPE)
AS
   lAirportCode  airport_configuration.airport_code%TYPE;
   lFlightNo     flight.flight_no%TYPE;
   lSiteId       site_info.site_id%TYPE;
   lOrigCarrier  flight.carrier_alpha_code%TYPE;
   lSendWTMsg    reflight_setup.send_wt_msg%TYPE;
   lNewPaxId     pax.pax_id%TYPE;
   msgid         NUMBER;
   
   runtime_stage  number := 0;

   CURSOR lcurGetReflightBags (cpFlightNo    flight.flight_no%TYPE,
                               cpSiteId      flight.site_id%TYPE)
   IS
      SELECT flight_id,
             pax_id,
             bag_tag,
             bag_tag2a,
             bag_tag2b,
             bag_tag3,
             orig_pax_id,
             fault_station,
             reason_for_loss,
             worldtracer_ref
      FROM reflight_bag
      WHERE site_id             = cpSiteId
      AND   reflight_needed     = 'A'
      AND   reflight_flight_id IS NULL
      AND   reflight_flight_no  = cpFlightNo;
  
BEGIN
   /**
      Get the flight details from the flight table.
   **/    

   SELECT flight_no,
          site_id
   INTO lFlightNo,
        lSiteId
   FROM flight
   WHERE flight_id = pFlightId;

   /** 
      Get the airport code for the current site.
   **/

   SELECT trim(airport_code)
   INTO lAirportCode
   FROM site_info
   WHERE site_id = lSiteId;

   /** 
      Check to see if there are any bags in the REFLIGHT_BAG table that have been authorised
      for re-flighting that do not have the reflight_flight_no equal to that of the current flight, 
      but where the reflight_flight_id is not set (i.e. the flight did not
      exist when the bag was re-flighted, so only the flight_no was stored.
   **/
   FOR i IN lcurGetReflightBags (lFlightNo, lSiteId) 
   LOOP
     
      /**
         Add the bag to the flight, under the RUSH passenger.
      **/

      reflighting.add_reflight_bag(1,
                                   i.flight_id,
                                   i.pax_id,
                                   i.bag_tag,
                                   pFlightId,
                                   lAirportCode,
                                   lSiteId,
                                   sysdate + get_offset.airport_code(lAirportCode),
                                   lNewPaxId);
             
      /**
         Update the entry in the reflight_bag table to record the flight_id of where the bag has
         been re-flighted to.
      **/

      UPDATE reflight_bag
      SET reflight_flight_id = pFlightId,
          reflight_pax_id    = lNewPaxId
      WHERE site_id   = lSiteId
      AND   flight_id = i.flight_id
      AND   pax_id    = i.pax_id
      AND   (   (    bag_tag2a = i.bag_tag2a
                 AND bag_tag3  = i.bag_tag3)
             OR (    bag_tag2b = i.bag_tag2b
                 AND bag_tag3  = i.bag_tag3));

      /**
         Get the carrier code for the original flight.
      **/
      SELECT carrier_alpha_code
      INTO lOrigCarrier
      FROM reflight_flight
      WHERE flight_id = i.flight_id;

      /** 
         See if the carrier has selected to send the WorldTracer FWD message. Set a flag accordingly.
      **/
      BEGIN
         SELECT send_wt_msg
         INTO lSendWTMsg
         FROM reflight_setup
         WHERE site_id   = lSiteId
         AND   carrier   = lOrigCarrier;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         lSendWTMsg := 'N';
      WHEN OTHERS
      THEN
         runtime_stage  := 1;
         RAISE;
      END;

      /**
         If the carrier has selected to send FWD messages, call the funtion to store 
         the message in the DB, for sending.
      **/
      IF lSendWTMsg = 'Y'
      THEN

         reflighting.worldtracer_msg (lSiteId,
                                      i.bag_tag,
                                      i.bag_tag2a,
                                      i.bag_tag2b,
                                      i.bag_tag3,
                                      lAirportCode,
                                      i.flight_id,
                                      lOrigCarrier,
                                      i.orig_pax_id,
                                      pFlightId,
                                      lFlightNo,
                                      pFlightDest,
                                      lNewPaxid,
                                      i.fault_station,
                                      i.reason_for_loss,
                                      '',
                                      '',
                                      i.worldtracer_ref,
                                      '');
         
      END IF;

   END LOOP;

EXCEPTION
WHEN OTHERS
THEN
IF ( SQLCODE <= -20000  ) THEN
         
   RAISE;
      
ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.TI_FLIGHT_DESTS_TRIG - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;
END ti_flight_dests_trig;

/****************************************************************************************************
* Procedure    - tu_flight_trig
*
* Description  - This procedure is called by the tu_flight trigger when the flight status changes to
*                'PD'. It will determine which bags/ulds have missed the flight and for each one copy
*                the details for the bag/passenger/uld/flight into the re-fighting data tables.
*****************************************************************************************************/

PROCEDURE tu_flight_trig (pSiteId           IN     site_info.site_id%TYPE,
                          pFlightId         IN     flight.flight_id%TYPE,
                          pFlightNo         IN     flight.flight_no%TYPE,
                          pFlightDate       IN     flight.flight_date%TYPE,
                          pFlightSchTime    IN     flight.flight_sch_time%TYPE,
                          pCarrierAlphaCode IN     flight.carrier_alpha_code%TYPE,
                          pDeptime          IN     flight.departure_time%TYPE)
AS
   lAutoLoadUlds site_config.db_value%TYPE;
   lCount        NUMBER(6) := 0;
   lCountBag     NUMBER(6) := 0;
   lCountUlds    NUMBER(6) := 0;
   lFlightDest   flight_dests.flight_destination%TYPE;
   lFlightId     flight.flight_id%TYPE;
   runtime_stage  number := 0;
   lBagsLoaded   VARCHAR2(1) := 'N';
   lConfigForPos carrier_site_config.config_value%TYPE;

   CURSOR lcurULDNotLoaded (cpFlightId reflight_ulds.flight_id%TYPE)
   IS
      SELECT uld_no,
             uld_no_of_bags
      FROM ulds
      WHERE uld_load_time IS NULL
      AND   flight_id      = cpFlightId 
      AND   uld_no    NOT IN (SELECT hold_no
                              FROM holds);

   CURSOR lcurOrigPAx ( pFlightId reflight_bag.flight_id%TYPE
                       ,pSiteId   reflight_bag.site_id%TYPE)
   IS 
      SELECT  b2.pax_id
             ,b2.orig_pax_init
             ,b2.orig_pax_surname
             ,b1.bag_tag
      FROM reflight_bag b1,
           reflight_bag b2
      WHERE b2.reflight_bag_tag = b1.bag_tag
      AND   b2.reflight_pax_id  = b1.pax_id
      AND   b1.orig_pax_surname = 'RUSHRUSH'
      AND   b1.flight_id        = pFlightId
      AND   b1.site_id          = pSiteId;


   CURSOR lcurFlightDest (cpFlightId reflight_ulds.flight_id%TYPE)
   IS
      SELECT  flight_destination
      FROM flight_dests
      WHERE flight_id = cpFlightId
      AND   dest_order = 1
      ORDER BY dest_source desc;

BEGIN
   /**
      Check to see if the Carrier of the flight has the re-flighting option set. If not do nothing.
   **/

   SELECT count(*)
   INTO lCount
   FROM flight f,
        carrier_site_config c
   WHERE c.config_item        = 'REFLIGHTING'
   AND   c.config_value       = 'Y'
   AND   c.site_id            = pSiteId
   AND   f.carrier_alpha_code = c.alpha_code
   AND   f.flight_id          = pFlightId;

   IF lCount = 0
   THEN
      RETURN;
   END IF;

   /**
      Check if the auto load ULDs system parameter is set.
   **/
   BEGIN
      SELECT db_value
      INTO lAutoLoadUlds
      FROM site_config
      WHERE site_id = pSiteId
      AND   db_item = 'AUTOLOAD ULDS';
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      lAutoLoadUlds := 'N';
   END;

   IF trim(lAutoLoadUlds) = 'Y'
   THEN
      /**
         Set all of the ULDs as aircraft loaded.
      **/
      UPDATE ulds
      SET pd_marker = 'Y',
          uld_load_time = sysdate + get_offset.airport_code(pSiteId)
      WHERE flight_id   = pFlightId
      AND   uld_load_time IS NULL
      AND   uld_no NOT IN (SELECT hold_no
                           FROM holds);
   ELSE
      /**
         Just set the PD marker for the ULDs
      **/
      UPDATE ulds
      SET pd_marker = 'Y'
      WHERE flight_id   = pFlightId 
      AND   uld_no NOT IN (SELECT hold_no
                           FROM holds);
   END IF;

   /**
      Now look at the bags not loaded on to the flight and copy the data to the
      reflight tables.
      First if there is a bag not loaded for the flight copy the flight details
      to the reflight_flight table.
   **/
   SELECT COUNT(*)
   INTO lCountBag
   FROM pax p,
        bag b
   WHERE p.flight_id     = pFlightId
   AND   p.pax_id        = b.pax_id
   AND   b.loaded_status != 'Y';

   IF lAutoLoadUlds = 'N'
   THEN
      /**
         ULDs have not been automatically loaded on to the aircraft so check
         if any were missed.
      **/         
      SELECT COUNT(*)
      INTO lCountUlds
      FROM ulds
      WHERE uld_load_time IS NULL
      AND   flight_id      = pFlightId 
      AND   uld_no NOT    IN (SELECT hold_no
                              FROM holds);
   END IF;
--------------------------------------------------------------------------------------
   BEGIN
      SELECT 'Y'
        INTO lBagsLoaded
        FROM dual
       WHERE EXISTS (SELECT 'X'
                     FROM pax p,
                          bag b
                    WHERE p.flight_id     = pFlightId
                      AND p.pax_id        = b.pax_id
                      AND b.loaded_status = 'Y');
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      lBagsLoaded := 'N';
   END;      
   /* if no bags on the flight are loaded     */
   /* there is no need to do any reflighting  */
   IF lBagsLoaded = 'N' 
   THEN   
      RETURN; 
   END IF;   
   BEGIN
   SELECT config_value
     INTO lConfigForPos
     FROM flight              f,
          carrier_site_config c
    WHERE c.config_item        = 'ISCONFIGFORPOS'
      AND c.site_id            = pSiteId
      AND f.carrier_alpha_code = c.alpha_code
      AND f.flight_id          = pFlightId
      AND ROWNUM = 1;  
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      lConfigForPos := 'N';
   END; 
---------------------------------------------------------------------------------------   
   /**
      Copy the Flight details into the reflight_flight table if there are bags or ULDs that have
      missed the flight.
   **/
   IF   ( lConfigForPos = 'Y' AND lCountBag + lCountUlds > 0) 
     OR ( lConfigForPos = 'N' AND lCountBag > 0) 
   THEN
      OPEN lcurFlightDest(pFlightId);

      FETCH lcurFlightDest 
      INTO lFlightDest;

      CLOSE lcurFlightDest;
      BEGIN

         INSERT INTO reflight_flight
         (flight_id,
          flight_no,
          flight_date,
          site_id,
          scheduled_time,
          carrier_alpha_code,
          flight_dest,
          departure_time)
         VALUES
         (pFlightId,
          pFlightNo,
          pFlightDate,
          pSiteId,
          pFlightSchTime,
          pCarrierAlphaCode,
          lFlightDest,
          pDeptime);

      EXCEPTION
      WHEN OTHERS
      THEN
         NULL;
      END;

   END IF;

   IF lCountBag > 0
   THEN
      /** 
         For each bag not loaded, go and get the passenger details and copy these into
         the reflight_pax table.
      **/
      BEGIN
         INSERT INTO reflight_pax
         (pax_id,
          flight_id,
          site_id,
          pax_initials,
          pax_surname,
          pax_full_inits,
          pax_destination,
          pax_class,
          departure_airport_code)
         SELECT p.pax_id,
                pFlightId,
                pSiteId,
                p.pax_initials,
                p.pax_surname,
                p.pax_full_inits,
                p.pax_destination,
                p.pax_class,
                p.departure_airport_code
         FROM pax           p,
              bag           b
         WHERE p.flight_id      = pFlightId
         AND   p.pax_id         = b.pax_id
         AND   b.loaded_status != 'Y'
         AND   p.pax_id NOT IN (SELECT pax_id 
                                FROM reflight_pax)
         GROUP BY p.pax_id,
                  pFlightId,
                  pSiteId,
                  p.pax_initials,
                  p.pax_surname,
                  p.pax_full_inits,
                  p.pax_destination,
                  p.pax_class,
                  p.departure_airport_code;
      EXCEPTION
      WHEN OTHERS
      THEN
         runtime_stage := 1;
         RAISE;
      END;
      /**
         Now copy the missed bag details to the reflight_bag table.            
      **/
      BEGIN
         INSERT INTO reflight_bag
         (bag_tag,
          pax_id,
          flight_id,
          site_id,
          bag_tag1,
          bag_tag2a,
          bag_tag2b,
          bag_tag3,
          bag_dest,
          orig_pax_id,
          orig_pax_init,
          orig_pax_surname,
          reflight_needed)
         SELECT b.bag_tag,
                b.pax_id,
                pFlightId,
                pSiteId,
                b.bag_tag1,
                b.bag_tag2a,
                b.bag_tag2b,
                b.bag_tag3,
                b.bag_destination,
                p.pax_id,
                p.pax_initials,
                p.pax_surname,
                decode(p.authority_to_load_dcs, 
                       'Y', decode(p.delete_marker,
                                   0, b.authority_to_load_dcs,
                                   'N'),
                       'N')
         FROM pax           p,
              bag           b
         WHERE p.flight_id      = pFlightId
         AND   p.pax_id         = b.pax_id
         AND   b.loaded_status != 'Y'
         AND   b.delete_marker = 0
         AND   (p.pax_id, b.bag_tag) NOT IN (SELECT pax_id, 
                                                    bag_tag
                                             FROM reflight_bag)
         GROUP BY b.bag_tag,
                   b.pax_id,
                   pFlightId,
                   pSiteId,
                   b.bag_tag1,
                   b.bag_tag2a,
                   b.bag_tag2b,
                   b.bag_tag3,
                   b.bag_destination,
                   p.pax_id,
                   p.pax_initials,
                   p.pax_surname,
                   decode(p.authority_to_load_dcs,
                          'Y', decode(p.delete_marker,
                                      0, b.authority_to_load_dcs,
                                      'N'),
                          'N');
      EXCEPTION
      WHEN OTHERS
      THEN
         runtime_stage := 2;
         RAISE;
      END;

   END IF;
   IF lCountUlds > 0 AND lConfigForPos = 'Y'
   THEN
      /**
         There are some ULDs that have not been loaded on to the aircraft, therefore
         all of the bags in the ULD need to be listed as needing re-flighting.
      **/

      FOR i IN lcurULDNotLoaded (pFlightId)
      LOOP
         reflighting.copy_uld_bags (pSiteId, pFlightId, i.uld_no, i.uld_no_of_bags);
      END LOOP;
   END IF;

   /**
      If the passenger needing reflighting is the RUSH passenger, then go and look
      up the original passenger from the previous reflighting of the bag. 
      If the previous bag reflight cannot be found the original passenger id will be null.
   **/

   FOR i IN lcurOrigPax(pFlightId, pSiteId )
    LOOP
      UPDATE reflight_bag
          SET  orig_pax_id      = i.pax_id
	          ,orig_pax_init    = i.orig_pax_init
	          ,orig_pax_surname = i.orig_pax_surname
      WHERE flight_id        = pFlightId
      AND   site_id          = pSiteId
      AND   orig_pax_surname = 'RUSHRUSH'
      AND   bag_tag          = i.bag_tag;
   END LOOP;


EXCEPTION
WHEN OTHERS
THEN
IF ( SQLCODE <= -20000  ) THEN
         
   RAISE;
      
ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.TU_FLIGHT_TRIG - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;
END tu_flight_trig;

/****************************************************************************************************
* Procedure    - uld_loaded
*
* Description  - This procedure is called from the tu_ulds trigger when the flight status is 'PD' and
*                the ULD have been recorded as being on the aircraft.
*                It will removed all of the bags/passengers/uld details from the re-flighting data 
*                tables for all bags within the uld and for the uls itself.
*****************************************************************************************************/

PROCEDURE uld_loaded      (pSiteId           IN     reflight_ulds.site_id%TYPE,
                           pFlightId         IN     reflight_ulds.flight_id%TYPE,
                           pULDNo            IN     reflight_ulds.uld_no%TYPE)
                          
AS
   lCount NUMBER(6);
   runtime_stage  number := 0;
BEGIN
   /**
      Check to see if the Carrier of the flight has the re-flighting option set. If not do nothing.
   **/

   SELECT count(*)
   INTO lCount
   FROM flight              f,
        carrier_site_config c
   WHERE c.config_item        = 'REFLIGHTING'
   AND   c.config_value       = 'Y'
   AND   c.site_id            = pSiteId
   AND   f.carrier_alpha_code = c.alpha_code
   AND   f.flight_id          = pFlightId;

   IF lCount = 0
   THEN
      RETURN;
   END IF;

   /**
      The ULD has been loaded on to the aircraft.
      Remove all of the bags in the ULD from the reflight_bag table.
   **/

   DELETE reflight_bag
   WHERE site_id       = pSiteId
   AND   uld_flight_id = pFlightId
   AND   uld_no        = pULDNo
   AND   reflight_needed != 'A';

   /**
      Now remove the ULD from the reflight_ULD table.
   **/

   DELETE reflight_ulds
   WHERE  site_id   = pSiteId
   AND    flight_id = pFlightId
   AND    uld_no    = pULDNo
   AND    0         = (SELECT count(*)
                       FROM reflight_bag
                       WHERE uld_no    = pULDNo
                       AND   flight_id = pFlightId);

   /**
      Remove any passengers that no longer have bags for reflighting.
   **/

   DELETE reflight_pax
   WHERE pax_id NOT IN (SELECT pax_id 
                        FROM reflight_bag
                        WHERE site_id   = pSiteId
                        AND   flight_id = pFlightId)
   AND   site_id     = pSiteId
   AND   flight_id   = pFlightId;

   /**
      Remove the flight if there are no passengers/bags for reflighting
   **/

   DELETE reflight_flight
   WHERE flight_id NOT IN (SELECT flight_id
                           FROM reflight_pax
                           WHERE site_id = pSiteId)
   AND   site_id        = pSiteId;
EXCEPTION
WHEN OTHERS
THEN
IF ( SQLCODE <= -20000  ) THEN
         
   RAISE;
      
ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.ULD_LOADED' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;

END uld_loaded;

/****************************************************************************************************
* Procedure    - uld_unloaded 
*
* Description  - This procedure is called from the tu_ulds trigger when the flight status is 'PD' and 
*                the ULD has been recorded as being unloaded from the aircraft.
*                It will copy all the the details about the ULD/passengers/bags to the re-flighting 
*                data tables.
*****************************************************************************************************/

PROCEDURE uld_unloaded    (pSiteId           IN     reflight_ulds.site_id%TYPE,
                           pFlightId         IN     reflight_ulds.flight_id%TYPE,
                           pULDNo            IN     reflight_ulds.uld_no%TYPE,
                           pULDNoOfBags      IN     reflight_ulds.uld_no_of_bags%TYPE)                       
AS
   lCount       NUMBER(6) := 0;
   lFlightDest flight_dests.flight_destination%TYPE;
   runtime_stage  number := 0;

   CURSOR lcurFlightDest (cpFlightId reflight_ulds.flight_id%TYPE)
   IS
      SELECT  flight_destination
      FROM flight_dests
      WHERE flight_id = cpFlightId
      AND   dest_order = 1
      ORDER BY dest_source desc;

BEGIN
   /**
      Check to see if the Carrier of the flight has the re-flighting option set. If not do nothing.
   **/

   SELECT count(*)
   INTO lCount
   FROM flight              f,
        carrier_site_config c
   WHERE c.config_item        = 'REFLIGHTING'
   AND   c.config_value       = 'Y'
   AND   c.site_id            = pSiteId
   AND   f.carrier_alpha_code = c.alpha_code
   AND   f.flight_id          = pFlightId;

   IF lCount = 0
   THEN
      RETURN;
   END IF;

   /** 
      See if the flight already exists in the reflight_flight table.
   **/

   SELECT count(*)
   INTO lCount
   FROM reflight_flight
   WHERE flight_id = pFlightId
   AND   site_id   = pSiteId;

   IF lCount = 0
   THEN
      OPEN lcurFlightDest (pFlightId);

      FETCH lcurFlightDest
      INTO lFlightDest;

      CLOSE lcurFlightDest;

      /**
         Flight does not already exist in the reflight_flight table so copy it from
         the flight table
      **/

      INSERT INTO reflight_flight
      (flight_id,
       flight_no,
       flight_date,
       site_id,
       scheduled_time,
       carrier_alpha_code,
       flight_dest)
      SELECT pFlightId,
             f.flight_no,
             f.flight_date,
             pSiteId,
             f.flight_sch_time,
             f.carrier_alpha_code,
             lFlightDest
      FROM flight       f
      WHERE f.flight_id   = pFlightId
      AND   f.site_id     = pSiteId;
   END IF;
 
   /**
      Now copy the passenger and bag details for all bags loaded in the ULD
   **/

   reflighting.copy_uld_bags (pSiteId, pFlightId, pULDNo, pULDNoOfBags);

   /**
      If the passenger needing reflighting is the RUSH passenger, then go and look
      up the original passenger from the previous reflighting of the bag. 
      If the previous bag reflight cannot be found the original passenger id will be null.
   **/

   UPDATE reflight_bag
   SET (orig_pax_id, orig_pax_init, orig_pax_surname) = (SELECT b2.orig_pax_id,
                                                         b2.orig_pax_init,
                                                         b2.orig_pax_surname
                                                         FROM reflight_bag b1,
                                                              reflight_bag b2
                                                         WHERE b2.reflight_bag_tag = b1.bag_tag
                                                         AND   b2.reflight_pax_id  = b1.orig_pax_id)
   WHERE flight_id     = pFlightId
   AND   site_id       = pSiteId
   AND   orig_pax_init = 'RUSH';

EXCEPTION
 WHEN OTHERS THEN
   IF ( SQLCODE <= -20000  ) THEN
         
     RAISE;
      
   ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.ULD_UNLOADED - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

  END IF;

END uld_unloaded;

/****************************************************************************************************
* Procedure    - validate_flight
*
* Description  - This procedure is called from the MWS and the scanner when a the user has manually 
*                entered a flight to which the bag is to be re-flighted.
*                It will check to see if a flight exists in the future that can be used. If so checks
*                are performed to make sure it has room for re-flighted bags and does not go via 
*                destinations to be avoided.If no flight is found the procedure will return success as
*                this is quite possible. The user may have entered a flight that will operate, but one
*                that UltraTrak system does not yet know about. In this case there will be no values
*                returned for the flight date/time/destination or id.
*****************************************************************************************************/

PROCEDURE validate_flight (pUserId       IN     tagtrak_users.user_id%TYPE,
                           pOrigFlightId IN     flight.flight_id%TYPE,
                           pPaxDest      IN     pax.pax_destination%TYPE,
                           pFlightNo     IN     flight.flight_no%TYPE,
                           pFlightDate      OUT flight.flight_date%TYPE,
                           pSchedTime       OUT flight.flight_sch_time%TYPE,
                           pFlightDest      OUT pax.pax_destination%TYPE,
                           pFlightId        OUT flight.flight_id%TYPE)
AS
   lCount         NUMBER := 0;
   lSiteId        site_info.site_id%TYPE := 0;
   lCarrier       flight.carrier_alpha_code%TYPE := '';
   lOrigCarrier   flight.carrier_alpha_code%TYPE := '';
   lFlightStatus  flight.flight_status%TYPE := '';
   lMinsInFuture  reflight_setup.mins_in_future%TYPE  := 0;
   lBagsPerFlight reflight_setup.bags_per_flight%TYPE := 0;
   lDateTime      DATE;
   i              NUMBER;
   runtime_stage  number := 0;
   
BEGIN
   /** 
      Get the carrier from the flight no passed in. (This is only used if the flight does not yet exist).
   **/

   lCarrier := substr(pFlightNo, 1, 2);   

   /**
      Now get the original flights carrier 
   **/
   SELECT carrier_alpha_code
   INTO lOrigCarrier
   FROM flight
   WHERE flight_id = pOrigFlightId;

   
   /**
      Check to see if the user handles the carrier
   **/
 
   SELECT count(*)
   INTO lCount
   FROM tagtrak_users     tu,
        contracted_agents ca
   WHERE tu.user_id          = pUserId
   AND ca.agent_code         = tu.agent_code
   AND ca.airline_alpha_code = lCarrier;   

   IF lCount = 0
   THEN
      /**
         The user does not handle the carrier, so return 'ACCESS DENIED'
      **/
      
      runtime_stage := 1;
      raise_application_error(RFLT_ACCESS_DENIED, 'Access Denied' );

   END IF;
  
 /**
      Get the Site_id for the user.
   **/
   SELECT s.site_id
   INTO lSiteId
   FROM tagtrak_users tu,
        agents        a,
        site_info     s
   WHERE tu.user_id   = pUserId
   AND   a.agent_code = tu.agent_code
   AND   s.site_id    = a.site_id;
 
  
   /**
      Check to see if the carrier has the reflighting facility
   **/
   SELECT count(*)
   INTO lCount
   FROM carrier_site_config
   WHERE config_item='REFLIGHTING'
   AND   site_id = lSiteId
   AND   alpha_code = lOrigCarrier;
   

   IF lCount = 0
   THEN
   
      /**
         The carrier does not have the reflighting facility.
      **/
      runtime_stage := 2;
      raise_application_error(NO_REFLIGHTING, 'Carrier does not have re-flighting set on' );

   END IF;
   
        
   /** 
      Get the mins in the future for the carrier.
   **/
  BEGIN

   SELECT mins_in_future
   INTO lMinsInFuture
   FROM reflight_setup
   WHERE carrier = lCarrier
   AND   site_id = lSiteId;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
        NULL;
  END;
             

   /**
      Store the time so that all selects are using the same date/time.
   **/

   lDateTime := sysdate + get_offset.airport_code(lSiteId);
  

   /** 
      Get the first flight in the future with this given flight no.
   **/
   BEGIN
      SELECT f.flight_id,
             f.flight_date,
             f.flight_sch_time,
             f.carrier_alpha_code,
             f.flight_status,
             d.flight_destination
      INTO pFlightId,
           pFlightDate,
           pSchedTime,
           lCarrier,
           lFlightStatus,
           pFlightDest
      FROM flight       f,
           flight_dests d
      WHERE d.dest_order = 1
      AND   d.flight_id  = f.flight_id
      AND   f.site_id    = lSiteId
      AND   f.flight_no  = pFlightNo
      AND   flight_status NOT IN ('PD', 'FF')
      AND   ((to_date((f.flight_date||to_char(lDateTime,'YYYY')
                     ||f.flight_sch_time),'DDMONYYYYHH24MISS') - lDateTime)*1440) > 0
      AND   ((to_date((f.flight_date||to_char(lDateTime,'YYYY')
                     ||f.flight_sch_time),'DDMONYYYYHH24MISS') - lDateTime)*1440) > lMinsInFuture
      AND   ROWNUM = 1
      ORDER BY f.departure_time ASC;

      
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
       
      BEGIN

         SELECT f.flight_id,
                f.flight_date,
                f.flight_sch_time,
                f.carrier_alpha_code,
                f.flight_status,
                d.flight_destination
         INTO pFlightId,
              pFlightDate,
              pSchedTime,
              lCarrier,
              lFlightStatus,
              pFlightDest
         FROM flight       f,
              flight_dests d
         WHERE d.dest_order = 1
         AND   d.flight_id  = f.flight_id
         AND   f.site_id    = lSiteId
         AND   f.flight_no  = pFlightNo
         AND   flight_status NOT IN ('PD', 'FF')
         AND   ((to_date((f.flight_date||to_char(lDateTime,'YYYY')
                        ||f.flight_sch_time),'DDMONYYYYHH24MISS') - lDateTime)*1440) > 0
         AND   ((to_date((f.flight_date||to_char(lDateTime,'YYYY')
                        ||f.flight_sch_time),'DDMONYYYYHH24MISS') - lDateTime)*1440) > lMinsInFuture
         AND   ROWNUM = 1
         ORDER BY f.departure_time ASC;
      EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
            
         /**
            If the carriers of the original flight and the chosen flight differ,
            see if the carrier will accept reflighted bags from the original carrier.
         **/
         IF lCarrier != lOrigCarrier
         THEN

            SELECT COUNT(accept_from_carrier)
            INTO lCount
            FROM reflight_accept_from
            WHERE site_id             = lSiteId
            AND   carrier             = lCarrier
            AND   accept_from_carrier = lOrigCarrier;      

            IF lCount = 0
            THEN
                
               /** 
                  Carriers bags not accepted on this flight
               **/ 
                runtime_stage := 3;
                raise_application_error(CARRIER_NOT_ACCEPT, 'Carrier will not accept reflighted bags from current Carrier');
            END IF;
         ELSE
            lCount := -1;
         END IF;
      WHEN OTHERS
      THEN
        
         runtime_stage := 1;
         RAISE;
      END;
   WHEN OTHERS
   THEN
      
      runtime_stage := 2;
         RAISE;
   END;

   /** 
      Retrieve the reflighting configuration values for the original flights carrier.
   **/
IF (lCount > -1)
THEN

   /**
      If the carriers of the original flight and the chosen flight differ,
      see if the carrier will accept reflighted bags from the original carrier.
   **/
   IF lCarrier != lOrigCarrier
   THEN
      SELECT COUNT(accept_from_carrier)
      INTO lCount
      FROM reflight_accept_from
      WHERE site_id             = lSiteId
      AND   carrier             = lCarrier
      AND   accept_from_carrier = lOrigCarrier;  
      
     
      

      IF lCount = 0
      THEN
         /** 
            Carriers bags not accepted on this flight
         **/ 
         runtime_stage := 4;
         raise_application_error(CARRIER_NOT_ACCEPT, 'Carrier will not accept reflighted bags from current Carrier');
      END IF;
   END IF;

   BEGIN 
      SELECT nvl(bags_per_flight, 0),
             nvl(mins_in_future, 0)
      INTO lBagsPerFlight,
           lMinsInFuture
      FROM reflight_setup
      WHERE site_id = lSiteId
      AND   carrier = lCarrier;
      
      
      
   EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
   END;

   IF lBagsPerFlight > 0
   THEN
      /**
         Need to see if the flight has room for reflighted bags
      **/
       
    
      SELECT COUNT(bag_tag)
      INTO lCount
      FROM reflight_bag
      WHERE site_id            = lSiteId
      AND   reflight_flight_id = pFlightId;

      
      
      
      
      IF lCount >= lBagsPerFlight
      THEN
         /**
            Flight has no room
         **/
         runtime_stage := 5;
         raise_application_error(NO_ROOM_ON_FLIGHT, 'Chosen flight has no room for reflighted bag');
      END IF;
   ELSE
        
        
         /**
            Flight has no room
         **/
         runtime_stage := 6;
         raise_application_error(NO_ROOM_ON_FLIGHT, 'Chosen flight has no room for reflighted bag');

   END IF;

   /**
      Now check the flight destination to make sure the bag is not travelling through
      a destination that is to be avoided.
   **/

   IF pFlightDest != pPaxDest
   THEN
      
      SELECT COUNT(*)
      INTO lCount
      FROM reflight_avoid_dest r,
           flight_dests        d
      WHERE r.site_id     = lSiteId
      AND   r.carrier     = lCarrier
      AND   r.avoid_dest != pPaxDest
      AND   d.flight_id   = pFlightId
      AND   r.avoid_dest  = d.flight_destination 
      AND   d.dest_order  < (SELECT dest_order
                             FROM flight_dests
                             WHERE flight_id = pFlightId
                             AND flight_destination = pPaxDest);
      
     

      IF lCount > 0
      THEN
         /**
            Destination is to be avoided.
         **/
         runtime_stage := 7;
         raise_application_error(DEST_TO_AVOID, 'Chosen flight will take bag to a destination that cannot be used for reflighting');
      END IF;
   END IF;

   /** 
      Flight is OK to use. 
   **/
     

ELSE

   /**
      No Suitable Flight found with specified flight number
   **/
   i := NO_SUITABLE_FLIGHTS;
   Raise_Application_Error(NO_SUITABLE_FLIGHTS, 'Failed');
    
END IF;
 
   
EXCEPTION
WHEN OTHERS
THEN
   IF lCount < 0
   THEN
      raise_application_error (i, 'Failed');
   ELSE
      IF ( SQLCODE <= -20000  ) THEN
         
        RAISE;
      
      ELSE  
           
                err_msg  := SQLERRM;
                err_code := SQLCODE;

                log_oracle_error(  'REFLIGHTING.VALIDATE_FLIGHT - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

                raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');
      END IF;
   END IF;
END validate_flight;

/****************************************************************************************************
* Procedure    - worldtracer_msg
*
* Description  - This procedure is called from the reflight_bag procedure and will send the FWD message 
*                to the WorldTracer system for the re-flighted bag.
*****************************************************************************************************/

PROCEDURE worldtracer_msg (pSiteId        IN     site_info.site_id%TYPE,
                           pBagTag        IN     bag.bag_tag%TYPE,
                           pBagTag2a      IN     bag.bag_tag2a%TYPE,
                           pBagTag2b      IN     bag.bag_tag2b%TYPE,
                           pBagTag3       IN     bag.bag_tag3%TYPE,
                           pAirportCode   IN     site_info.airport_code%TYPE,
                           pOrigFlightId  IN     flight.flight_id%TYPE,
                           pOrigCarrier   IN     flight.carrier_alpha_code%TYPE,
                           pOrigPaxId     IN     pax.pax_id%TYPE,
                           pFlightId      IN     flight.flight_id%TYPE,
                           pFlightNo      IN     flight.flight_no%TYPE,
                           pFlightDest    IN     flight_dests.flight_destination%TYPE,
                           pPaxId         IN     pax.pax_id%TYPE,
                           pFaultStation  IN     reflight_bag.fault_station%TYPE,
                           pReasonForLoss IN     reflight_bag.reason_for_loss%TYPE,
                           pSupInfo1      IN     VARCHAR2,
                           pSupInfo2      IN     VARCHAR2,
                           pWTRef         IN     reflight_bag.worldtracer_ref%TYPE,
                           pTeletypeList  IN     VARCHAR2)
AS
   lPaxInit        pax.pax_initials%TYPE;
   lPaxSurname     pax.pax_surname%TYPE;
   lFlightDate     flight.flight_date%TYPE;
   lWTMsg          wt_transactions.wt_request%TYPE;
   lCount          NUMBER(6);
   lOrigFlightNo   flight.flight_no%TYPE;
   lOrigFlightDate flight.flight_date%TYPE;
   lTeletypeList   VARCHAR2(160);
   lOrigCarrier    flight.carrier_alpha_code%TYPE;
   lNewCarrier     flight.carrier_alpha_code%TYPE;
   lAppnRowId      reflight_bag.appn_row_id%TYPE;
   runtime_stage   number := 0;
   lStationAirline wt_config.training_station_airline%TYPE; -- WorldTracer station airline
   lFlightDes      flight_dests.flight_destination%TYPE; -- Flight destination

   lTraining       wt_config.wt_dah_training%TYPE; -- WorldTracer training station indicator
   lTrainingSite   wt_config.training_site%TYPE; -- site to use whilst in training mode
   -- station airline code to use in messages whilst in training mode
   lTrainingStationAirline wt_config.training_station_airline%TYPE;
   -- destinations station to use in forward messages whilst in training mode
   lTrainingDes    wt_config.training_destination%TYPE;

   
   CURSOR lcurTeletypeAdr (cpSiteId   site_info.site_id%TYPE,
                           cpCarrier  flight.carrier_alpha_code%TYPE,
                           cpFlightId flight.flight_id%TYPE)
   IS
      SELECT teletype_address
      FROM reflight_dest_tx
      WHERE site_id            = cpSiteId
      AND   carrier            = cpCarrier
      AND   dest               = (SELECT reflight_flight_dest
                                  FROM reflight_bag
                                  WHERE reflight_flight_id = cpFlightId
                                  GROUP BY reflight_flight_dest);

/*(SELECT fd.flight_destination
                          FROM flight_dests fd
                          WHERE fd.flight_id = cpFlightId
                          AND   fd.dest_order = 1);
*/
BEGIN
   /**
      Get the original passenger initials and surname
   **/
   SELECT orig_pax_init,
          orig_pax_surname,
          appn_row_id
   INTO lPaxInit,
        lPaxSurname,
        lAppnRowId
   FROM reflight_bag
   WHERE flight_id = pOrigFlightId
   AND   (   (    bag_tag2a = pBagTag2a
              AND bag_tag3  = pBagTag3)
          OR (    bag_tag2b = pBagTag2b
              AND bag_tag3  = pBagTag3))
   AND   pax_id    = pOrigPaxid;

   /**
      Get the new flight date and carrier code
   **/
   SELECT flight_date,
          carrier_alpha_code
   INTO lFlightDate,
        lNewCarrier
   FROM flight
   WHERE flight_id = pFlightId;

   /**
      Get the original flight number, date and carrier code
   **/
   SELECT flight_no,
          flight_date,
          carrier_alpha_code
   INTO lOrigFlightNo,
        lOrigFlightDate,
        lOrigCarrier
   FROM reflight_flight
   WHERE flight_id = pOrigFlightId;

   /** 
      See if the teletype address have been supplied.
      If not get them from the configuration setup for the new carrier.
   **/
   lTeletypeList := pTeletypeList;

   IF lTeletypeList IS NULL
   THEN
      lCount := 0;

      FOR i IN lcurTeletypeAdr(pSiteId, lNewCarrier, pFlightId)
      LOOP
         IF lCount = 1
         THEN
            lTeletypeList := lTeletypeList||','||i.teletype_address;
         ELSE
            lTeletypeList := i.teletype_address;
            lCount := 1;
         END IF;
      END LOOP;
   END iF;

   -- Obtain the training configuration
   SELECT wt_dah_training, training_site, training_station_airline, training_destination
      INTO lTraining, lTrainingSite,lTrainingStationAirline,lTrainingDes
      FROM wt_config
      WHERE site=pAirportCode;
   IF NVL(lTraining, 'N')='T' THEN
      -- This is a training site so update the internal copy of the site
      -- to point to the training site. Also set the station airline, destination
      -- and carrier fields to the training values.
      depair          := lTrainingSite;
      lStationAirline := lTrainingStationAirline;
      lFlightDes      := lTrainingDes;
      lOrigCarrier    := SUBSTR(lStationAirline,4);
   ELSE
      -- Use original reflighted station airline and destination
      depair          := pAirportCode;
      lStationAirline := pAirportCode||lOrigCarrier;
      lFlightDes      := pFlightDest;
   END IF;

   /**
      Build the FWD message
   **/

   lWTMsg := 'WM FWD '||lStationAirline||chr(10);
   lWTMsg := lWTMsg||'FO '||REPLACE(pFlightNo,' ','')||'/'||lFlightDate||chr(10);
   lWTMsg := lWTMsg||'TN '||pBagTag||chr(10);
   lWTMsg := lWTMsg||'FD '||REPLACE(lOrigFlightNo,' ','')||'/'||lOrigFlightDate||chr(10);
   lWTMsg := lWTMsg||'NM '||RTRIM(lPaxInit)||'/'||lPaxSurname||chr(10);
   lWTMsg := lWTMsg||'FW '||lFlightDes||lNewCarrier||chr(10);
   lWTMsg := lWTMsg||'FS '||pFaultStation||chr(10);

   IF pReasonForLoss IS NOT NULL
   THEN
      lWTMsg := lWTMsg||'RL '||pReasonForLoss||chr(10);
   END IF;

   IF lTeletypeList IS NOT NULL
   THEN
      lWTMsg := lWTMsg||'TX '||lTeletypeList||chr(10);
   END IF;

   IF pSupInfo1 IS NOT NULL
   THEN
      lWTMsg := lWTMsg||'SI '||pSupInfo1||chr(10);

      IF pSupInfo2 IS NOT NULL
      THEN
         lWTMsg := lWTMsg||' - RC '||pSupInfo2||chr(10);
      END IF;
   END IF;

   lWTMsg := lWTMsg||'AG '||'Login';

   INSERT INTO wt_transactions(site, carrier, bag_appn_row_id, wt_code, wt_request)
   VALUES(depair,lOrigCarrier,lAppnRowId, 'FWD',lWTMsg);

EXCEPTION
WHEN OTHERS
THEN 
        IF ( SQLCODE <= -20000  ) THEN
         
   RAISE;
      
ELSE  
           
     err_msg  := SQLERRM;
     err_code := SQLCODE;

     log_oracle_error(  'REFLIGHTING.WORLDTRACER_MSG - STAGE' || '  '|| runtime_stage
                            , ERR_MSG
                            , ERR_CODE
                          );

    raise_application_error(ORACLE_ERROR, 'APPLICATION ERROR');

END IF;

END worldtracer_msg;

/**********************************************************************
 *
 * PROCEDURE  process_fwd
 *
 * Description:
 *    Check whether the FWD file was received okay, and set the
 *    error response if not.
 *
 * Parameters:
 *    p_wt_response: Response to process from WorldTracer
 *    p_response_error: Error response to return to table
 *
 * Errors raised:
 *    None
 *
 **********************************************************************/

PROCEDURE process_fwd(
   p_wt_response IN wt_transactions.wt_response%TYPE,
   p_response_error OUT wt_transactions.response_error%TYPE) IS

   l_wt_response wt_transactions.wt_response%TYPE; -- WorldTracer response with . changed to newline
   l_expected_tokens NUMBER(1); -- number of tokens expected in line
   l_token_line token_array := token_array(); -- initialised structure to contain the message line
   l_token_count NUMBER(1); -- number of tokens returned
BEGIN
   -- turn full stops into new lines
   l_wt_response := REPLACE(p_wt_response, '.', v_new_line);

   -- expected number of tokens is 4
   l_expected_tokens :=4;

   -- tokenise the response
   tokenise_string(l_wt_response,l_expected_tokens,
      ' ',l_token_line,l_token_count);

   IF l_token_count =l_expected_tokens AND
      INSTR(l_token_line(4), 'OK', 1,1) > 0 THEN
      -- the returned response is okay, don't need to do
      -- anything else
      NULL;
   ELSE
      -- the returned response is not okay
      IF l_token_count<l_expected_tokens THEN
         p_response_error := v_unknown_response_format;
      ELSE
         -- store the error response
         p_response_error :=l_token_line(4);
      END IF;

   END IF;

END  process_fwd;

END reflighting;
/
