/*****************************************************************************
*
* Filename         - $Workfile:   check_connect_info.sql  $
*
*                     Name              Date      Version     CR Number
*                     Yomi Kogbe       1/10/2       1.1         15697
* Author           - See revision history below.
* Last Update      - See revision history below.
*
* Project Name     - ULTRATRAK(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, 2002
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PROCEDURE check_connect_info  

CREATE OR REPLACE
PROCEDURE check_connect_info  
(
 fid        IN flight.flight_id%type, 
 bag_type   IN bag.connection_type%type,
 uld_type   IN ulds.connection_type%type,
 uld_no     IN ulds.uld_no%type,
 got_onward IN NUMBER,
 tpax_class IN pax.pax_class%type,
 bag_dest   IN bag.bag_destination%type,
 retval    OUT number 
)IS
  is_hold   number := 0;
  got_type  number := 0;
  mapped    char(6);
  pax_type  ulds.uld_type%type;
  l_carrier carrier_site_config.alpha_code%TYPE;
  l_site_id site_info.site_id%TYPE;

BEGIN
    retval  := 0;
    is_hold := 0;

    /* Determine type for pax  CR: 15697  */
      
    IF(bag_type is null and got_onward = 0) THEN
       pax_type := 'L';
    ELSIF(bag_type is not null) THEN
       pax_type := 'T';
    ELSIF(got_onward >0) THEN 
       pax_type := 'T';
    END IF;

    /* Retrieve flight's carrier and site id */
    SELECT SUBSTR(flight_no, 1, 3), site_id
      INTO l_carrier, l_site_id
      FROM flight
     WHERE flight_id = fid;

    select count(hold_no) 
      into is_hold
      from holds
     where hold_no = uld_no;

    
    IF (is_hold > 0) THEN 
       retval   := 1;   
    ELSIF(bag_type is null and uld_type is null) THEN 
       retval   := 2;      
    ELSIF(bag_type is not null and uld_type is null) THEN 
         select count(u.uld_no) 
           into got_type
           from ulds u, uld_dests ud
          where u.flight_id       = fid
            and u.connection_type = bag_type
            and u.uld_type = pax_type 
            and instr(ud.uld_class_list, tpax_class) > 0
            and ud.uld_destination  = bag_dest
            and u.uld_no = ud.uld_no
            and u.flight_id = ud.flight_id;
           
        IF (got_type > 0) THEN 
           retval := -1067; 
        ELSE
           IF is_connection_valid(bag_type, mapped) THEN
              IF mapped is null THEN 
                 retval := -1071;
              ELSE
                 select count(u.uld_no)  
                    into got_type
                    from ulds u , uld_dests ud
                    where u.flight_id         = fid
                    and   u.connection_type   = mapped
                    and u.uld_type = pax_type
                    and instr(ud.uld_class_list, tpax_class) > 0
                    and ud.uld_destination  = bag_dest
                    and u.uld_no = ud.uld_no
                    and u.flight_id = ud.flight_id;
                
                   IF (got_type > 0) THEN
                    retval := -1067;
                   ELSE
                    retval := -1071; 
                   END IF;
              END IF;
           ELSE
             IF is_connection_valid('ALL', mapped) THEN  
                 IF mapped is null THEN  
                    retval := -1071;  
                 ELSE                  
                       select count(u.uld_no)        
                         into got_type
                         from ulds u, uld_dests ud
                        where u.flight_id       = fid
                          and u.connection_type = mapped
                          and u.uld_type = pax_type
                          and instr(ud.uld_class_list, tpax_class) > 0
                          and ud.uld_destination  = bag_dest
                          and u.uld_no = ud.uld_no
                          and u.flight_id = ud.flight_id;

                       IF (got_type > 0) THEN
                          retval := -1067;
                       ELSE
                          retval := -1071;
                       END IF;
                 END IF;
             ELSE
                 retval := -1071;
             END IF;
           END IF;
        END IF;
    ELSIF(bag_type is null and uld_type is not null) THEN
       IF got_onward > 0 THEN 
          -- CA02488 - Is strict connecting terminal segregation enabled?
          IF rtrim(inbound_flight.get_carrier_site_config(l_site_id, l_carrier,
                'STRICT_CON_TERM_SEG')) = 'Y' THEN
             retval := -1069;  
          ELSE
             retval := 3;
          END IF;
       ELSE
          retval := -1070;  
       END IF;
    ELSIF(bag_type = uld_type) THEN                   
       retval   := 4;
    ELSIF(bag_type != uld_type) THEN 
      
         select count(u.uld_no)  
           into got_type
           from ulds u, uld_dests ud
          where u.flight_id       = fid
            and u.connection_type = bag_type
            and u.uld_type = pax_type
            and instr(ud.uld_class_list, tpax_class) > 0
            and ud.uld_destination  = bag_dest
            and u.uld_no = ud.uld_no
            and u.flight_id = ud.flight_id;

       IF (got_type > 0) THEN  
          retval := -1067;  
       ELSE
           IF is_connection_valid(bag_type, mapped) THEN 
              IF mapped is null then  
                 retval := -1072;
              ELSE                   
                 IF mapped = uld_type THEN  
                    retval := 6;
                 ELSE                      
                    select count(u.uld_no)   
                    into got_type
                    from ulds u , uld_dests ud   
                    where u.flight_id       = fid
                    and u.connection_type   = mapped
                    and u.uld_type = pax_type
                    and instr(ud.uld_class_list, tpax_class) > 0
                    and ud.uld_destination  = bag_dest
                    and u.uld_no = ud.uld_no
                    and u.flight_id = ud.flight_id;
 
                   IF (got_type > 0) THEN
                    retval := -1067; 
                   ELSE
                    retval := -1072; 
                   END IF;
                 END IF;
              END IF;
           ELSE                      
              IF is_connection_valid('ALL', mapped) THEN  
                 IF mapped is null THEN  
                    retval := -1072;
                 ELSE                    
                    IF mapped = uld_type THEN    
                       retval := 6;
                    ELSE
                       select count(u.uld_no)       
                         into got_type
                         from ulds u , uld_dests ud
                        where u.flight_id       = fid
                          and u.connection_type = mapped
                          and u.uld_type = pax_type
                          and instr(ud.uld_class_list, tpax_class) > 0
                          and ud.uld_destination  = bag_dest
                          and u.uld_no = ud.uld_no
                          and u.flight_id = ud.flight_id;
                  
                       IF (got_type > 0) then
                          retval := -1067;
                       ELSE
                          retval := -1072;
                       END IF;
                    END IF;
                 END IF;
              ELSE
                 retval := -1072;
              END IF;
           END IF;
       END IF;
    END IF;
     
EXCEPTION
   when others then
   retval := -502;
   RAISE;
END check_connect_info;
/
