/*****************************************************************************
*
* Filename         - $Workfile:   get_best_uld.sql  $
*
*                     Name  Yomi kogbe    Date 28/05/00   Version     CR Number
*                     Martin Helliwell   30/9/02            1.3         15686
*                     Yomi Kogbe         4/10/02            1.4         15697
* 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:   get_best_uld.sql  $  $Revision:   1.4  $
*
* 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/aisarch/db_ora/db/source/live/procedures/get_best_uld.svl  $
* 
*    Rev 1.4   04 Oct 2002 17:42:38   tagtrak
* OR:
* CR: 15697
* Change ltr package to remove suspect reserved words
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PROCEDURE get_best_uld                                                          

CREATE OR REPLACE PROCEDURE get_best_uld                                                          
( uld       IN   ulds.uld_no%type,  
  fid       IN   flight.flight_id%type,                                         
  bag_dest  IN   bag.bag_destination%type,
  conn_type IN   bag.connection_type%type,                                      
  pax_class IN   pax.pax_class%type,                                            
  pax_type  IN   ulds.uld_type%type,                                            
  on_dest   IN   char,                                                          
  load_type IN   char,                                                          
  on_no     IN   onward_flight.norm_fno%type, -- CA2566
  on_date   IN   onward_flight.odate%type,
  best_uld  OUT  ulds.uld_no%type                                               
)                                                                               
IS                                                                              
   v_uld       ulds.uld_no%type;                                                 
   on_uld      ulds.uld_no%type;                                                 
   grp_uld     ulds.uld_no%type;                                                 
   group_dest  group_iata_dests.group_dest%type;                                 
   onward_uld  boolean    := FALSE;
   init_dest   number := 0;
   got_type    number := 0;
   got_uld     number := 0;
   mapped      connection_configuration.mapping%TYPE; -- CR 15686
   l_num_dests NUMBER := 0;
/* cursor to get uld_no for carrier with precision loading supplied. uld_type and pax class must be checked */
CURSOR c1 is                                                                    
       select u.uld_no uldno,
              u.uld_type a,                                                   
              ud.uld_destination                                               
         from ulds u,                                                           
              uld_dests ud                                                      
        where u.flight_id         = fid                                         
          and u.flight_id         = ud.flight_id                                
          and u.uld_no            = ud.uld_no
          and u.connection_type is null                                   
          and decode(u.uld_type,'M', 'T',u.uld_type)  = pax_type                           
          and instr(ud.uld_class_list,pax_class) > 0                            
          and ud.uld_destination  = bag_dest
          and u.close_flag        = 'N'                -- CA2537
          and u.uld_no           != uld                -- CA2537
          order by  u.uld_type desc, uld_no_of_bags;
                                                                                
/* cursor to get uld_no for carrier without precision loading. pax class must be checked */
CURSOR c2 is                                                                    
       select u.uld_no b,                                                       
              u.uld_type c,                                                     
              ud.uld_destination                                                
         from ulds u,                                                           
              uld_dests ud                                                      
        where u.flight_id         = fid
          and u.connection_type is null                                         
          and u.flight_id         = ud.flight_id                                
          and u.uld_no            = ud.uld_no                                   
          and instr(ud.uld_class_list,pax_class) > 0                            
          and ud.uld_destination   = bag_dest                                   
          and u.close_flag        = 'N'                -- CA2537
          and u.uld_no           != uld                -- CA2537
          order by u.uld_type desc, uld_no_of_bags; 
                                                                                
/* cursor to get uld_no for tag with onward_destination irrespective of carrier loading strategy */
CURSOR c3(init_uldno IN  uld_dests.uld_no%type,
          fltid      IN  flight.flight_id%type) is
    select a.uld_no uld ,
           a.uld_type df,
           b.uld_destination dest
     from  ulds a,
           uld_dests b
     where b.uld_no           = a.uld_no
       and b.flight_id        = a.flight_id
       and b.uld_no           = init_uldno
       and b.flight_id        = fltid
       and ((   b.uld_destination  = on_dest
                         /* CA2566 - onward flight details */
               and ((b.onward_flight_no = TRIM(on_no) AND b.onward_flight_date = on_date)
                   OR b.onward_flight_no IS NULL
                   OR TRIM(b.onward_flight_no) = TRIM(SUBSTR(on_no,1,3)))
               and (instr(b.uld_class_list,pax_class) > 0 OR TRIM(b.uld_class_list) = '*')
       )
       or
       (
               /* CA2566 - ULD without onward dest/class, correct flight details */
               TRIM(b.uld_destination) = '*' AND TRIM(b.uld_class_list) = '*'
               AND( (b.onward_flight_no = TRIM(on_no) AND b.onward_flight_date = on_date)
                OR (TRIM(b.onward_flight_no) = TRIM(SUBSTR(on_no,1,3))))
       ))
       and decode(a.uld_type,'M', 'T',a.uld_type) = pax_type
       and a.connection_type is null
       and a.close_flag        = 'N'                -- CA2537
       and a.uld_no           != uld                -- CA2537
       order by  a.uld_type desc, uld_no_of_bags; 

/* cursor for group dest */                                                     
CURSOR c4 is                                                                    
    select group_dest,
           dest                                                     
      from group_iata_dests                                                     
     where dest = on_dest;                                                      
                                                                                
                                                                                
BEGIN
       
/* CA2866 - Was the ULD generic? */  
   BEGIN
      select count(*) into l_num_dests
        from uld_dests
       where uld_no = uld 
         and flight_id = fid;
   EXCEPTION
       WHEN OTHERS THEN
            RAISE; 
   END;

IF (conn_type is not null) THEN
 DECLARE
   cursor get_connection_type(conn_info IN bag.connection_type%type) is
   select u.uld_no ab
     from ulds u, uld_dests ud
    where u.flight_id       = fid
      and u.connection_type = conn_info
      and u.uld_type = pax_type           /* CR: 15697 */
      and instr(ud.uld_class_list,pax_class) > 0
      and ud.uld_destination  = bag_dest
      and u.uld_no = ud.uld_no
      and u.flight_id = ud.flight_id
    order by uld_no_of_bags;
 BEGIN
    FOR get_connection_type_rec IN get_connection_type(conn_type) LOOP
          got_type := got_type + 1;
          best_uld := get_connection_type_rec.ab;
          EXIT;
    END LOOP;
    
    IF (got_type = 0) THEN
       IF is_connection_valid(conn_type, mapped) THEN
          IF mapped is null THEN
             best_uld := null;
          ELSE
            FOR get_connection_type_rec IN get_connection_type(mapped) LOOP
                got_type := got_type + 1;
                best_uld := get_connection_type_rec.ab;
                EXIT;
            END LOOP;
          END IF;
       ELSE
         IF is_connection_valid('ALL', mapped) THEN
          IF mapped is null THEN
             best_uld := null;
          ELSE
            FOR get_connection_type_rec IN get_connection_type(mapped) LOOP
                got_type := got_type + 1;
                best_uld := get_connection_type_rec.ab;
                EXIT;
            END LOOP;
          END IF; 
         ELSE
            best_uld := null;
         END IF; 
       END IF;
    END IF;         
 EXCEPTION
   WHEN OTHERS THEN
     RAISE;       
 END;

ELSE
                                                                              
 IF (on_dest is not null ) THEN
  
   BEGIN

     select count(uld_no)
       into init_dest
       from uld_dests
      where uld_destination = bag_dest
        and flight_id       = fid;
   END;

   IF init_dest > 0 THEN
   DECLARE
   CURSOR check_initial_dest is
             select uld_no,
                    flight_id
               from uld_dests
              where uld_destination = bag_dest
                and flight_id       = fid;
   BEGIN
   FOR check_initial_dest_rec IN check_initial_dest LOOP 
      FOR c3_REC IN c3(check_initial_dest_rec.uld_no, check_initial_dest_rec.flight_id) LOOP
          got_uld := got_uld + 1;
          on_uld  := c3_REC.uld;                                                  
          onward_uld := TRUE;
          EXIT;
      END LOOP;
   IF got_uld > 1 THEN
   EXIT;
   END IF;
   END LOOP;
   END;
   END IF;                                                                    
   best_uld := on_uld;                                                          
                                                                                
   IF NOT  onward_uld and init_dest > 0 THEN
      got_uld := 0;                                                      
      FOR c4_rec in c4 LOOP
         DECLARE                                                                
         CURSOR get_group_dest_uld is                                                 
                   select  b.uld_no     grp_uldno,
                           b.flight_id  grp_fid
                     from  ulds a,                                              
                           uld_dests b                                          
                    where  b.uld_no           = a.uld_no                               
                      and  b.flight_id        = a.flight_id                            
                      and  b.flight_id        = fid                                    
                      and  b.uld_destination  = c4_rec.group_dest                      
                      and  a.close_flag       = 'N'                -- CA2537
                      and  a.uld_no          != uld                -- CA2537
                      /* CA 2866 - Seg by Dest groups allowed if: */
                      and (l_num_dests = 1 -- Original ULD is Generic, or
                      or   a.uld_type = 'T'); -- New  ULD is Transfer
         BEGIN                                                                  
              FOR get_group_dest_uld_rec IN get_group_dest_uld LOOP
              dbms_output.put_line('2');
              DECLARE
              CURSOR get_uld is
                     select uld_no
                       from uld_dests
                     where  uld_destination = bag_dest
                       and  flight_id       = get_group_dest_uld_rec.grp_fid
                       and  uld_no          = get_group_dest_uld_rec.grp_uldno;
              BEGIN
                     FOR get_uld_rec IN get_uld LOOP
                         got_type := got_type + 1;
                         onward_uld := TRUE;
                         best_uld   := get_uld_rec.uld_no;
                         EXIT;
                     END LOOP;
              END;
              IF (got_type > 0) THEN
              EXIT;
              END IF;                             
              END LOOP;                                                    
         END;
      IF (got_type > 0) THEN
      EXIT;
      END IF;
      END LOOP;                                                                   
   END IF;                                                                      
                                                                                
   IF NOT onward_uld and init_dest > 0 THEN
      got_uld := 0;
      IF load_type = 'Y' THEN                                                   
         FOR c1_rec IN c1 LOOP
             DECLARE
             CURSOR check_dest is
                    select count(uld_destination) cou 
                      from uld_dests
                     where uld_no    = c1_rec.uldno
                       and flight_id = fid;
             BEGIN
                 FOR check_dest_rec IN check_dest LOOP
                     IF check_dest_rec.cou = 1 then
                        best_uld := c1_rec.uldno;
                        got_uld  := got_uld + 1;
                     END IF;
                 IF (got_uld > 0) THEN
                 EXIT;
                 END IF;
                 END LOOP;
             END;
         IF (got_uld > 0) THEN
         EXIT;
         END IF;
         END LOOP;
          
      ELSE                                                                      
         FOR c2_rec in c2 LOOP
             DECLARE
             CURSOR check_dest is
                    select count(uld_destination) cou
                      from uld_dests
                     where uld_no    = c2_rec.b
                       and flight_id = fid;
             BEGIN
                 FOR check_dest_rec IN check_dest LOOP
                     IF check_dest_rec.cou = 1 then
                        best_uld := c2_rec.b;
                        got_uld := got_uld + 1;
                     END IF;
                 IF (got_uld > 0) THEN
                 EXIT;
                 END IF;
                 END LOOP;
             END;
         IF (got_uld > 0) THEN
         EXIT;
         END IF;
         END LOOP;                                                              
      END IF;                                                                   
   END IF;                                                                      

                                                                                
 ELSE                                                                            
                                                                                
   FOR c2_rec IN c2 LOOP                                                        
       IF(c2_rec.c = 'L') or (c2_rec.c = 'M') THEN                                           
          v_uld := c2_rec.b;                                                    
          EXIT;                                                                 
       END IF;                                                                  
   END LOOP;                                                                    
   best_uld := v_uld;                                                           
                                                                                
 END IF; 
END IF;                                                                        
END;
/
show errors                                                                            
