/*****************************************************************************
*
* Filename        - $Workfile:   flight_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 Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 1997
*
*****************************************************************************/
/****************************************************************************
* @(#)$Workfile:   flight_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/flight_bag_count.sqv  $
* 
*    Rev 1.6   08 Apr 1999 15:21:52   tagtrak
* OR:
* CR: 13677
* REV:
* Changes as a result of System Testing.
* 
* Neil Bishop.
* (branch build revision copied to trunk.)
* Neil Bishop
* 
*    Rev 1.5.1.1   05 Mar 1999 11:36:36   tagtrak
* OR:
* CR: 13677
* REV:
* Changes as a result of System Testing.
* 
* Neil Bishop.
* 
*    Rev 1.5   02 Apr 1998 19:33:06   tagtrak
* OR:
* CR:
* REV:
* Initial Issue
* See label for concession
* 
*    Rev 1.4   15 Jan 1998 12:11:52   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:04:32   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE flight_bag_count

CREATE OR REPLACE PROCEDURE flight_bag_count
(
   fid   IN OUT NUMBER,
   dest  IN     CHAR,
   fltno IN     CHAR,
   cf    IN OUT NUMBER,
   cj    IN OUT NUMBER,
   cy    IN OUT NUMBER,
   cr    IN OUT NUMBER,
   lf    IN OUT NUMBER,
   lj    IN OUT NUMBER,
   ly    IN OUT NUMBER,
   lr    IN OUT NUMBER,
   sf    IN OUT NUMBER,
   sj    IN OUT NUMBER,
   sy    IN OUT NUMBER,
   sr    IN OUT NUMBER
)
AS 
   last_uldno  CHAR(10);
   is_shipside CHAR;

	 /*
		* Added check to the following cursor - authority_to_load_dcs = 'Y'
		* Neil Bishop.  04/03/99.
		*/

   CURSOR c1 IS
      SELECT p.pax_id,
             p.pax_class,
             p.pax_status,
             b.loaded_status,
             b.uld_no,
             b.delete_marker
        FROM pax p,
             bag b
       WHERE p.pax_id                = b.pax_id
         AND p.flight_id             = fid
				 AND b.authority_to_load_dcs = 'Y'
       ORDER BY b.uld_no;

	 /*
		* Added check to the following cursor - authority_to_load_dcs = 'Y'
		* Neil Bishop.  04/03/99.
		*/

   CURSOR c_dest IS
      SELECT p.pax_id,
             p.pax_class,
             p.pax_status,
             b.loaded_status,
             b.uld_no,
             b.delete_marker
        FROM pax p,
             bag b
       WHERE p.pax_id                = b.pax_id
         AND p.flight_id             = fid
         AND b.bag_destination       = dest
				 AND b.authority_to_load_dcs = 'Y'
    ORDER BY b.uld_no;

BEGIN

   cf         := 0;   /* checked,  F */
   cj         := 0;   /* checked,  J */
   cy         := 0;   /* checked,  Y */
   cr         := 0;   /* checked,  RUSH */
   lf         := 0;   /* loaded,  F */
   lj         := 0;   /* loaded,  J */
   ly         := 0;   /* loaded,  Y */
   lr         := 0;   /* loaded,  RUSH */
   sf         := 0;   /* shipside,  F */
   sj         := 0;   /* shipside,  J */
   sy         := 0;   /* shipside,  Y */
   sr         := 0;   /* shipside,  RUSH */

   last_uldno := '-';

   /* loop round the bags for this flight */
   IF (dest = '-') THEN

      FOR c1_rec in c1 LOOP

         IF (c1_rec.loaded_status = 'Y') THEN

            IF (c1_rec.uld_no != last_uldno) THEN
               /* check if this uld is ship-side if we don't know it already */
               is_uld_shipside(c1_rec.uld_no, fltno, fid, is_shipside);
               last_uldno := c1_rec.uld_no;
            END IF;

         END IF;

         IF (c1_rec.pax_class IN ('F','P','A')) THEN

            IF (c1_rec.delete_marker = 0 AND c1_rec.pax_status IN ('C','B')) THEN
               cf := cf + 1;
            END IF;

            IF (c1_rec.loaded_status = 'Y') THEN
               lf := lf + 1;
               IF (is_shipside = 'Y') THEN
                  sf := sf + 1;
               END IF;
            END IF;

         ELSIF (c1_rec.pax_class IN ('J','C','D','Z')) THEN

            IF (c1_rec.delete_marker = 0 AND c1_rec.pax_status IN ('C','B')) THEN
               cj := cj + 1;
            END IF;

            IF (c1_rec.loaded_status = 'Y') THEN

               lj := lj + 1;

               IF (is_shipside = 'Y') THEN
                  sj := sj + 1;
               END IF;

            END IF;

         ELSIF (c1_rec.pax_class IN ('Y','S','W','B','H','Q',
                                     'M','T','K','L','V','G')) THEN

            IF (c1_rec.delete_marker = 0 AND c1_rec.pax_status IN ('C','B')) THEN
               cy := cy + 1;
            END IF;

            IF (c1_rec.loaded_status = 'Y') THEN
               ly := ly + 1;

               IF (is_shipside = 'Y') THEN
                  sy := sy + 1;
               END IF;

            END IF;

         ELSIF (c1_rec.pax_class = '*') THEN

            IF (c1_rec.delete_marker = 0 AND c1_rec.pax_status IN ('C','B')) THEN
               cr := cr + 1;
            END IF;

            IF (c1_rec.loaded_status = 'Y') THEN

               lr := lr + 1;
               IF (is_shipside = 'Y') THEN
                  sr := sr + 1;
               END IF;

            END IF;

         END IF;

      END LOOP; /* end of for */

   ELSE /* we have a dest */

      FOR c_dest_rec in c_dest LOOP

         IF (c_dest_rec.loaded_status = 'Y') THEN

            IF (c_dest_rec.uld_no != last_uldno) THEN
               /* check if this uld is ship-side if we don't know it already */
               is_uld_shipside(c_dest_rec.uld_no, fltno, fid, is_shipside);
               last_uldno := c_dest_rec.uld_no;
            END IF;

         END IF;

         IF (c_dest_rec.pax_class IN ('F','P','A')) THEN

            IF (c_dest_rec.delete_marker = 0 AND
                c_dest_rec.pax_status IN ('C','B')) THEN
               cf := cf + 1;
            END IF;

            IF (c_dest_rec.loaded_status = 'Y') THEN
               lf := lf + 1;
               IF (is_shipside = 'Y') THEN
                  sf := sf + 1;
               END IF;
            END IF;

         ELSIF (c_dest_rec.pax_class IN ('J','C','D','Z')) THEN

            IF (c_dest_rec.delete_marker = 0 AND
                c_dest_rec.pax_status IN ('C','B')) THEN
               cj := cj + 1;
            END IF;

            IF (c_dest_rec.loaded_status = 'Y') THEN
               lj := lj + 1;

               IF (is_shipside = 'Y') THEN
                  sj := sj + 1;
               END IF;

            END IF;

         ELSIF (c_dest_rec.pax_class IN ('Y','S','W','B','H','Q',
                                         'M','T','K','L','V','G')) THEN

            IF (c_dest_rec.delete_marker = 0 AND
                c_dest_rec.pax_status IN ('C','B')) THEN
               cy := cy + 1;
            END IF;

            IF (c_dest_rec.loaded_status = 'Y') THEN
               ly := ly + 1;

               IF (is_shipside = 'Y') THEN
                  sy := sy + 1;
               END IF;

            END IF; 

         ELSIF (c_dest_rec.pax_class = '*') THEN

            IF (c_dest_rec.delete_marker = 0 AND
                c_dest_rec.pax_status IN ('C','B')) THEN
               cr := cr + 1;
            END IF;

            IF (c_dest_rec.loaded_status = 'Y') THEN
               lr := lr + 1;
               IF (is_shipside = 'Y') THEN
                  sr := sr + 1;
               END IF;
            END IF; 

         END IF;

      END LOOP; /* end of for */

   END IF; /* end of dest */

END flight_bag_count;
/
