/*****************************************************************************
*
* Filename         - $Workfile:   pax_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:   pax_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/pax_bag_count.sqv  $
* 
*    Rev 1.6   08 Apr 1999 15:23:32   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:44   tagtrak
* OR:
* CR: 13677
* REV:
* Changes as a result of System Testing.
* 
* Neil Bishop.
* 
*    Rev 1.5   02 Apr 1998 19:01:12   tagtrak
*  
* 
*    Rev 1.4   15 Jan 1998 12:13:54   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:18:56   tagtrak
* Added PVCS header information
*
****************************************************************************/


PROMPT CREATE OR REPLACE PROCEDURE pax_bag_count

CREATE OR REPLACE PROCEDURE pax_bag_count
(
   fid        INTEGER,
   dest       CHAR, 
   p_btf  OUT INTEGER,
   p_btj  OUT INTEGER,
   p_bty  OUT INTEGER,
   p_blf  OUT INTEGER,
   p_blj  OUT INTEGER,
   p_bly  OUT INTEGER,
   p_ptf  OUT INTEGER,
   p_ptj  OUT INTEGER,
   p_pty  OUT INTEGER,
   p_plf  OUT INTEGER,
   p_plj  OUT INTEGER,
   p_ply  OUT INTEGER,
   p_bt_classless OUT INTEGER,
   p_bl_classless OUT INTEGER,
   p_pt_classless OUT INTEGER,
   p_pl_classless OUT INTEGER,
   p_btot OUT INTEGER,
   p_ptot OUT INTEGER
)
AS 
   pid      INTEGER;
   last_pid INTEGER;

   pcls     CHAR;
   bsi      CHAR;
   lsts     CHAR;

   CURSOR get_pax IS
      SELECT pax_id pid,
             pax_class pcls,
             pax_status,
             NVL(b.accompanied_bags,'Y') include_pax_in_count,
             NVL2(b.bag_excep_id
                  ,DECODE( pax.pax_sequence_no
                          ,b.bag_excep_pax_seq
                          ,b.accompanied_bags --system generated pax
                          ,'Y'
                         )
                  ,'Y' --no matching exception_type exists
                 )
        FROM pax, bag_exception_types b
       WHERE flight_id      = fid
         AND pax_status IN ('C','B')
         AND delete_marker != 1
         AND pax.exception_type_id = b.bag_excep_id(+);/*join on primary key to avoid duplicates*/

	 /*
		* Changed the cursor below to include authority_to_load_dcs = 'Y'.
		* Neil Bishop.  04/03/99.
		*/
   CURSOR get_all_bags IS
      SELECT loaded_status lsts,
             bag_source_indicator bsi
        FROM bag
       WHERE pax_id                = pid
         AND delete_marker        != 1
				 AND authority_to_load_dcs = 'Y';

	 /*
		* Changed the cursor below to include authority_to_load_dcs = 'Y'.
		* Neil Bishop.  04/03/99.
		*/
   CURSOR get_bags IS
      SELECT loaded_status lsts,
             bag_source_indicator bsi
        FROM bag
       WHERE pax_id                = pid 
         AND bag_destination       = dest
         AND delete_marker        != 1
				 AND authority_to_load_dcs = 'Y';

   btf INTEGER;
   btj INTEGER;
   bty INTEGER;
   blf INTEGER;
   blj INTEGER;
   bly INTEGER;
   ptf INTEGER;
   ptj INTEGER;
   pty INTEGER;
   plf INTEGER;
   plj INTEGER;
   ply INTEGER;
   btot INTEGER;
   ptot INTEGER;
   bt_classless INTEGER;
   bl_classless INTEGER;
   pt_classless INTEGER;
   pl_classless INTEGER;

BEGIN

   btf := 0;         /* bag, transfer, F */
   btj := 0;         /* bag, transfer, J */
   bty := 0;         /* bag, transfer, Y */
   blf := 0;         /* bag, local, F */
   blj := 0;         /* bag, local, J */
   bly := 0;         /* bag, local, Y */
   ptf := 0;         /* pax, transfer, F */
   ptj := 0;         /* pax, transfer, J */
   pty := 0;         /* pax, transfer, Y */
   plf := 0;         /* pax, local, F */
   plj := 0;         /* pax, local, J */
   ply := 0;         /* pax, local, Y */
   bt_classless := 0; /* bag, transfer, classless */
   bl_classless := 0; /* bag, local, classless */
   pt_classless := 0; /* pax, transfer, classless */
   pl_classless := 0; /* pax, local, classless */

   last_pid := 0;

   /* loop round the pax for this flight */
   FOR pax_record IN get_pax LOOP

      pid  := pax_record.pid;
      pcls := pax_record.pcls;

      IF (dest = '-') THEN

         /* loop round his bags */
         FOR bag_record IN get_all_bags LOOP

            lsts := bag_record.lsts;
            bsi  := bag_record.bsi;

            IF (bsi = 'T') THEN

               IF (pcls IN ('F','P','A')) THEN

                  btf := btf + 1;
                  IF (pid != last_pid) THEN
                     ptf      := ptf + 1;
                     last_pid := pid;
                  END IF;

               END IF;

               IF (pcls IN ('J','C','D','Z')) THEN

                  btj := btj + 1;
                  IF (pid != last_pid) THEN
                     ptj      := ptj + 1;
                     last_pid := pid;
                  END IF;

               END IF;

               IF (pcls IN ('Y','S','W','B','H','Q',
                            'M','T','K','L','V','G')) THEN

                  bty := bty + 1;
                  IF (pid != last_pid) THEN
                      pty      := pty + 1;
                      last_pid := pid;
                  END IF;     

               END IF;

               IF (pcls = '*') THEN
                  bt_classless := bt_classless + 1;
                  IF (pid != last_pid) AND pax_record.include_pax_in_count = 'Y' THEN
                      pt_classless := pt_classless + 1;
                      last_pid := pid;
                  END IF;     
               END IF;

            END IF;

            /* it's a local bag */
            IF (bsi = 'L') THEN

               IF (pcls IN ('F','P','A')) THEN

                  blf := blf + 1;
                  IF (pid != last_pid) THEN
                     plf      := plf + 1;
                     last_pid := pid;
                  END IF;

               END IF;

               IF (pcls IN ('J','C','D','Z')) THEN

                  blj := blj + 1;
                  IF (pid != last_pid) THEN
                     plj      := plj + 1;
                     last_pid := pid;
                  END IF;

               END IF;

               IF (pcls IN ('Y','S','W','B','H','Q',
                            'M','T','K','L','V','G')) THEN

                  bly := bly + 1;
                  IF (pid != last_pid) THEN
                     ply      := ply + 1;
                     last_pid := pid;
                  END IF;    

               END IF;

               IF (pcls = '*') THEN
                  bl_classless := bl_classless + 1;
                  IF (pid != last_pid) AND pax_record.include_pax_in_count = 'Y' THEN
                      pl_classless := pl_classless + 1;
                      last_pid := pid;
                  END IF;    
               END IF;

            END IF;

         END LOOP;

      ELSE /* destination specified, loop round his bags */

         FOR bag_record IN get_bags LOOP

            lsts := bag_record.lsts;
            bsi  := bag_record.bsi;

            IF (bsi = 'T') THEN

               IF (pcls IN ('F','P','A')) THEN

                  btf := btf + 1;
                  IF (pid != last_pid) THEN
                     ptf      := ptf + 1;
                     last_pid := pid;
                  END IF;

               END IF;

               IF (pcls IN ('J', 'C', 'D', 'Z')) THEN

                  btj := btj + 1;
                  IF (pid != last_pid) THEN
                     ptj      := ptj + 1;
                     last_pid := pid;
                  END IF;

               END IF;

               IF (pcls IN ('Y', 'S', 'W', 'B', 'H', 'Q',
                            'M', 'T', 'K', 'L', 'V', 'G')) THEN

                  bty := bty + 1;
                  IF (pid != last_pid) THEN
                     pty      := pty + 1;
                     last_pid := pid;
                  END IF;     

               END IF;

               IF (pcls = '*') THEN
                  bt_classless := bt_classless + 1;
                  IF (pid != last_pid) AND pax_record.include_pax_in_count = 'Y' THEN
                      pt_classless := pt_classless + 1;
                      last_pid := pid;
                  END IF;    
               END IF;

            END IF;

            /* it's a local bag */
            IF (bsi = 'L') THEN

               IF (pcls IN ('F', 'P', 'A')) THEN

                  blf := blf + 1;
                  IF (pid != last_pid) THEN
                     plf      := plf + 1;
                     last_pid := pid;
                  END IF;

               END IF;

               IF (pcls IN ('J', 'C', 'D', 'Z')) THEN

                  blj := blj + 1;
                  IF (pid != last_pid) THEN
                     plj      := plj + 1;
                     last_pid := pid;
                  END IF;

               END IF;

               IF (pcls IN ('Y', 'S', 'W', 'B', 'H', 'Q',
                            'M', 'T', 'K', 'L', 'V', 'G')) THEN

                  bly := bly + 1;
                  IF (pid != last_pid) THEN
                     ply      := ply + 1;
                     last_pid := pid;
                  END IF;    

               END IF;

               IF (pcls = '*') THEN
                  bl_classless := bl_classless + 1;
                  IF (pid != last_pid) AND pax_record.include_pax_in_count = 'Y' THEN
                      pl_classless := pl_classless + 1;
                      last_pid := pid;
                  END IF;    
               END IF;

            END IF;

         END LOOP;

      END IF;

      last_pid := pid;

   END LOOP;

   btot := btf + blf + btj + blj + bty + bly + bl_classless + bt_classless;
   ptot := ptf + plf + ptj + plj + pty + ply + pl_classless + pt_classless;

   p_btf  := btf;
   p_btj  := btj;
   p_bty  := bty;
   p_blf  := blf;
   p_blj  := blj;
   p_bly  := bly;
   p_ptf  := ptf;
   p_ptj  := ptj;
   p_pty  := pty;
   p_plf  := plf;
   p_plj  := plj;
   p_ply  := ply;
   p_btot := btot;
   p_ptot := ptot;
   p_bt_classless := bt_classless;
   p_bl_classless := bl_classless;
   p_pt_classless := pt_classless;
   p_pl_classless := pl_classless;

END pax_bag_count;
/
