/*****************************************************************************
*
* Filename         - $Workfile:   get_onward_flight.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          -
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1998
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   get_onward_flight.sql  $  $Revision:   1.2  $
*
* Revision History
*
* Note that user tagtrak is many users. For these users the user
* name is entered as the last line in the change description.
*
* $Log:   /pvcsarch/db_ora/db/source/live/procedures/get_onward_flight.svl  $
* 
*    Rev 1.2   20 Sep 1999 14:41:12   tagtrak
*  
* 
*    Rev 1.1   26 Feb 1999 08:51:28   tagtrak
*  
*
****************************************************************************/
 
/****************************************************************************

   Procedure to get onward flight itinerary for a given pax.

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

PROMPT CREATE OR REPLACE PROCEDURE get_onward_flight

CREATE OR REPLACE PROCEDURE get_onward_flight
(
  paxid_in          IN     pax.pax_id%TYPE,
  v_onward_flightno    OUT onward_flight_defs.onwd_no_t,
  v_onward_dest        OUT onward_flight_defs.onwd_dest_t,
  v_onward_date        OUT onward_flight_defs.onwd_date_t,
  v_onward_class       OUT onward_flight_defs.onwd_class_t,
  num_found         IN OUT INTEGER,
  array_size        IN     INTEGER
)
IS
   CURSOR c1 IS
      SELECT o.no,
             o.dest,
             o.odate,
             o.class
        FROM onward_flight o
       WHERE o.pax_id = paxid_in
    ORDER BY o.dest_order;

   v_index BINARY_INTEGER := 0;
   i INTEGER; 
BEGIN

   num_found := 0;

   OPEN c1;
   FOR i IN 1..array_size LOOP

      v_index := v_index + 1;

      FETCH c1
       INTO v_onward_flightno(v_index),
            v_onward_dest(v_index),
            v_onward_date(v_index),
            v_onward_class(v_index);

      EXIT WHEN c1%NOTFOUND;

      num_found := num_found + 1; 

   END LOOP;
   CLOSE c1;

END get_onward_flight;
/
