/*****************************************************************************
*
* Filename         - $Workfile:   flight_loading_progress.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_loading_progress.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_loading_progress.sqv  $
* 
*    Rev 1.6   08 Apr 1999 15:22:00   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:38   tagtrak
* OR:
* CR: 13677
* REV:
* Changes as a result of System Testing.
* 
* Neil Bishop.
* 
*    Rev 1.5   02 Apr 1998 19:00:38   tagtrak
*  
* 
*    Rev 1.4   15 Jan 1998 12:12:00   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:05:24   tagtrak
* Added PVCS header information
*
****************************************************************************/

PROMPT create or replace procedure flight_loading_progress

create or replace procedure flight_loading_progress
(
   fid   in  integer,
   dest  in  char,
   ctf   out integer,
   ctj   out integer,
   cty   out integer,
   clf   out integer,
   clj   out integer,
   cly   out integer,
   ltf   out integer,
   ltj   out integer,
   lty   out integer,
   llf   out integer,
   llj   out integer,
   lly   out integer,
   stf   out integer,
   stj   out integer,
   sty   out integer,
   slf   out integer,
   slj   out integer,
   sly   out integer,
   crush out integer,
   lrush out integer,
   srush out integer
)
as 
   pid        integer;
   ctf_copy   integer;
   ctj_copy   integer;
   cty_copy   integer;
   clf_copy   integer;
   clj_copy   integer;
   cly_copy   integer;
   crush_copy integer;
   ltf_copy   integer;
   ltj_copy   integer;
   lty_copy   integer;
   llf_copy   integer;
   llj_copy   integer;
   lly_copy   integer;
   lrush_copy integer;

   stf_copy integer;
   stj_copy integer;
   sty_copy integer;
   slf_copy integer;
   slj_copy integer;
   sly_copy integer;
   srush_copy integer;

   pcls       char;
   pstatus    char;
   lsts       char;
   bsi        char;

   cursor get_pax is
      select
         pax_id     pid,
         pax_class  pcls,
         pax_status pstatus
      from
         pax
      where
         flight_id = fid and
         delete_marker != 1;

	 /*
		* Changed the cursor below to check for authority_to_load_dcs = 'Y'.
		* Neil Bishop.  04/03/99.
		*/
   cursor get_bags_all 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 check for authority_to_load_dcs = 'Y'.
		* Neil Bishop.  04/03/99.
		*/
   cursor get_bags_dest 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';
begin

   ctf_copy   := 0; /* checked, transfer, F */
   ctj_copy   := 0; /* checked, transfer, J */
   cty_copy   := 0; /* checked, transfer, Y */
   clf_copy   := 0; /* checked, local, F */
   clj_copy   := 0; /* checked, local, J */
   cly_copy   := 0; /* checked, local, Y */
   crush_copy := 0; /* checked rush */
   ltf_copy   := 0; /* loaded, transfer, F */
   ltj_copy   := 0; /* loaded, transfer, J */
   lty_copy   := 0; /* loaded, transfer, Y */
   llf_copy   := 0; /* loaded, local, F */
   llj_copy   := 0; /* loaded, local, J */
   lly_copy   := 0; /* loaded, local, Y */
   lrush_copy := 0; /* loaded rush */
   stf_copy   := 0;
   stj_copy   := 0;
   sty_copy   := 0;
   slf_copy   := 0;
   slj_copy   := 0;
   sly_copy   := 0;
   srush_copy := 0;

   /* loop round the pax for this flight */
   for pax_record in get_pax loop
      pid  := pax_record.pid;
      pcls := pax_record.pcls;
      pstatus := pax_record.pstatus;

      if (dest = '-') then

         for bag_record in get_bags_all loop /* loop round all bags */

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

            if (bsi = 'T') then /* if it's a transfer bag */

               if (pcls IN ('F','P','A')) then
            
                  if (pstatus in ('C','B')) then
                     ctf_copy := ctf_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        stf_copy := stf_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then /* loaded */
                     ltf_copy := ltf_copy + 1;
                  end if;

               end if;

               if (pcls = 'J' or pcls = 'C' or pcls = 'D' or pcls = 'Z') then

                  if (pstatus in ('C','B')) then
                     ctj_copy := ctj_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        stj_copy := stj_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     ltj_copy := ltj_copy + 1; /* loaded */
                  end if;

               end if;

               if (pcls = 'Y' or pcls = 'S' or pcls = 'W' or pcls = 'B' or
                   pcls = 'H' or pcls = 'Q' or pcls = 'M' or pcls = 'T' or 
                   pcls = 'K' or pcls = 'L' or pcls = 'V' or pcls = 'G') then

                  if (pstatus in ('C','B')) then
                     cty_copy := cty_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        sty_copy := sty_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     lty_copy := lty_copy + 1; /* loaded */
                  end if;
               end if;

               if (pcls = '*') then

                  if (pstatus in ('C','B')) then
                     crush_copy := crush_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        srush_copy := srush_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     lrush_copy := lrush_copy + 1; /* loaded */
                  end if;

               end if;
            end if;

            if (bsi = 'L') then /* it's a local bag */
               if (pcls = 'F' or pcls = 'P' or pcls = 'A') then

                  if (pstatus in ('C','B')) then
                     clf_copy := clf_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        slf_copy := slf_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     llf_copy := llf_copy + 1; /* loaded */
                  end if;

               end if;

               if (pcls = 'J' or pcls = 'C' or pcls = 'D' or pcls = 'Z') then

                  if (pstatus in ('C','B')) then
                     clj_copy := clj_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        slj_copy := slj_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     llj_copy := llj_copy + 1; /* loaded */
                  end if;
               end if;

               if (pcls = 'Y' or pcls = 'S' or pcls = 'W' or pcls = 'B' or 
                   pcls = 'H' or pcls = 'Q' or pcls = 'M' or pcls = 'T' or 
                   pcls = 'K' or pcls = 'L' or pcls = 'V' or pcls = 'G') then

                  if (pstatus in ('C','B')) then
                     cly_copy := cly_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        sly_copy := sly_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     lly_copy := lly_copy + 1; /* loaded */
                  end if;

               end if;

               if (pcls = '*') then

                  if (pstatus in ('C','B')) then
                     crush_copy := crush_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        srush_copy := srush_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     lrush_copy := lrush_copy + 1; /* loaded */
                  end if;

               end if;
            end if;
         end loop;
      else
         for bag_record in get_bags_dest loop /* loop round his bags */
            lsts := bag_record.lsts;
            bsi  := bag_record.bsi;

            if (bsi = 'T') then /* if it's a transfer bag */
               if (pcls = 'F' or pcls = 'P' or pcls = 'A') then

                  if (pstatus in ('C','B')) then
                     ctf_copy := ctf_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        stf_copy := stf_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     ltf_copy := ltf_copy + 1; /* loaded */
                  end if;

               end if;

               if (pcls = 'J' or pcls = 'C' or pcls = 'D' or pcls = 'Z') then

                  if (pstatus in ('C','B')) then
                     ctj_copy := ctj_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        stj_copy := stj_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     ltj_copy := ltj_copy + 1; /* loaded */
                  end if;

               end if;

               if (pcls = 'Y' or pcls = 'S' or pcls = 'W' or pcls = 'B' or 
                   pcls = 'H' or pcls = 'Q' or pcls = 'M' or pcls = 'T' or 
                   pcls = 'K' or pcls = 'L' or pcls = 'V' or pcls = 'G') then

                  if (pstatus in ('C','B')) then
                     cty_copy := cty_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        sty_copy := sty_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     lty_copy := lty_copy + 1; /* loaded */
                  end if;

               end if;

               if (pcls = '*') then

                  if (pstatus in ('C','B')) then

                     crush_copy := crush_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        srush_copy := srush_copy + 1; /* short */
                     end if;

                  end if;

                  if (lsts = 'Y') then
                     lrush_copy := lrush_copy + 1; /* loaded */
                  end if;

               end if;
            end if;

            if (bsi = 'L') then /* it's a local bag */
               if (pcls = 'F' or pcls = 'P' or pcls = 'A') then

                  if (pstatus in ('C','B')) then 
                     clf_copy := clf_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        slf_copy := slf_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     llf_copy := llf_copy + 1; /* loaded */
                  end if;
               end if;

               if (pcls = 'J' or pcls = 'C' or pcls = 'D' or pcls = 'Z') then

                  if (pstatus in ('C','B')) then
                     clj_copy := clj_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        slj_copy := slj_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     llj_copy := llj_copy + 1; /* loaded */
                  end if;
               end if;

               if (pcls = 'Y' or pcls = 'S' or pcls = 'W' or pcls = 'B' or 
                     pcls = 'H' or pcls = 'Q' or pcls = 'M' or pcls = 'T' or 
                     pcls = 'K' or pcls = 'L' or pcls = 'V' or pcls = 'G') then

                  if (pstatus in ('C','B')) then
                     cly_copy := cly_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        sly_copy := sly_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     lly_copy := lly_copy + 1; /* loaded */
                  end if;
               end if;

               if (pcls = '*') then

                  if (pstatus in ('C','B')) then
                     crush_copy := crush_copy + 1; /* checked */
                     if (lsts != 'Y') then
                        srush_copy := srush_copy + 1; /* short */
                     end if;
                  end if;

                  if (lsts = 'Y') then
                     lrush_copy := lrush_copy + 1; /* loaded */
                  end if;
               end if;
            end if;
         end loop;
      end if;
   end loop;

   ctf   := ctf_copy;
   ctj   := ctj_copy;
   cty   := cty_copy;
   clf   := clf_copy;
   clj   := clj_copy;
   cly   := cly_copy;
   ltf   := ltf_copy;
   ltj   := ltj_copy;
   lty   := lty_copy;
   llf   := llf_copy;
   llj   := llj_copy;
   lly   := lly_copy;
   crush := crush_copy;
   lrush := lrush_copy;

   stf   := stf_copy;
   stj   := stj_copy;
   sty   := sty_copy;
   slf   := slf_copy;
   slj   := slj_copy;
   sly   := sly_copy;
   srush := srush_copy;

end flight_loading_progress;
/
