/*****************************************************************************
*
* Filename         - $Workfile:   flts_bag_count.sql  $
*
*                     Name              Date      Version     CR Number
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - TAGTRAK(Oracle Database)
*
* Source Code
* Identifier       - 0/3/300/8010/112
*
* Design Spec
* Section          - 
*
* Contents         - Live Schema Package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   flts_bag_count.sql  $  $Revision:   1.6  $
*
* 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:   /shared/pvcs/tagtarch/db_ora/db/source/live/procedures/flts_bag_count.sqv  $
* 
*    Rev 1.6   08 Apr 1999 15:22:14   tagtrak
* OR: 13206
* CR:  
* REV:
* Andrew Walker
* (branch build revision copied to trunk.)
* Neil Bishop
* 
*    Rev 1.5.1.0   25 Feb 1999 15:23:52   tagtrak
* OR: 13206
* CR:  
* REV:
* Andrew Walker
* 
*    Rev 1.5   02 Apr 1998 19:34:02   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.4   15 Jan 1998 12:12:10   tagtrak
* OR: OR11101, TN13, TN15
* CR:
* REV:
* Phase 2C to Phase 3 upgrade plus code review actions and resolving some technical isses.
* 
*    Rev 1.3   24 Oct 1997 11:06:38   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE flts_bag_count_pkg

CREATE OR REPLACE PACKAGE flts_bag_count_pkg
AS
   TYPE ret_rec_type IS RECORD
      (flight_no   CHAR(8),
       flight_date CHAR(5),
       dest        CHAR(3),
       cf          NUMBER,
       cj          NUMBER,
       cy          NUMBER,
       cr          NUMBER,
       lf          NUMBER,
       lj          NUMBER,
       ly          NUMBER,
       lr          NUMBER,
       sf          NUMBER,
       sj          NUMBER,
       sy          NUMBER,
       sr          NUMBER);

   TYPE ret_table_type IS
      TABLE OF ret_rec_type
      INDEX BY BINARY_INTEGER;

   PROCEDURE flts_bag_count
      (fid        IN OUT NUMBER, 
       batch_size IN     NUMBER,
       found      IN OUT NUMBER,
       done_fetch    OUT NUMBER,
       table_in      OUT ret_table_type);

END flts_bag_count_pkg;
/


PROMPT CREATE OR REPLACE PACKAGE BODY flts_bag_count_pkg

CREATE OR REPLACE PACKAGE BODY flts_bag_count_pkg
AS
   CURSOR c1 (fid IN NUMBER)
   IS
      SELECT f.flight_no,
             f.flight_date,
             d.flight_destination 
        FROM flight f,
             flight_dests d
       WHERE f.flight_id = fid
         AND f.flight_id = d.flight_id
       ORDER BY flight_destination;

   PROCEDURE flts_bag_count
      (fid        IN OUT NUMBER, 
       batch_size IN     NUMBER,
       found      IN OUT NUMBER,
       done_fetch    OUT NUMBER,
       table_in      OUT ret_table_type)
   IS
   BEGIN
      -- Procedure "flts_bag_count" fetches a batch of 
      -- rows (batch size is determined by the client/caller
      -- of the procedure).  It can be called from other
      -- stored procedures or client application programs.
      -- The procedure opens the cursor if it is not
      -- already open, fetches a batch of rows, and
      -- returns the number of rows actually retrieved. At
      -- end of fetch, the procedure closes the cursor.

      IF (batch_size = 0) THEN
         found      := 0;
         done_fetch := 1;        -- indicate all done
         RETURN;
      END IF;

      IF NOT c1%ISOPEN THEN      -- open the cursor if
         OPEN c1(fid);           -- not already open
      END IF;

      -- Fetch up to "batch_size" rows into PL/SQL table,
      -- tallying rows found as they are retrieved. When all
      -- rows have been fetched, close the cursor and exit
      -- the loop, returning only the last set of rows found.

      done_fetch := 0;    -- set the done flag FALSE
      found      := 0;

      /* loop round the flights */
      FOR i IN 1..batch_size LOOP
         FETCH c1 INTO 
            table_in(i).flight_no, 
            table_in(i).flight_date, 
            table_in(i).dest; 

         IF c1%NOTFOUND THEN   -- if no row was found
            CLOSE c1;
            done_fetch := 1;   -- indicate all done
            EXIT;
         ELSE
            flight_bag_count
               (fid,
                table_in(i).dest, 
                table_in(i).flight_no, 
                table_in(i).cf, 
                table_in(i).cj, 
                table_in(i).cy, 
                table_in(i).cr,
                table_in(i).lf, 
                table_in(i).lj, 
                table_in(i).ly, 
                table_in(i).lr,
                table_in(i).sf, 
                table_in(i).sj, 
                table_in(i).sy, 
                table_in(i).sr);

            found := found + 1;  -- count row

         END IF;

      END LOOP;

   END flts_bag_count;

END flts_bag_count_pkg;
/
