/*****************************************************************************
*
* Filename         - $Workfile:   baggage_segregation_strategies.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       -
*
* Design Spec
* Section          - FAS/DUBRS/SD/1304
*
* Contents         - Live Schema Stored Procedure
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2004
*
*****************************************************************************/

PROMPT CREATE OR REPLACE PACKAGE baggage_segregation_strategies

CREATE OR REPLACE PACKAGE baggage_segregation_strategies AS
/******************************************************************************
*
* Program Unit          - Package Procedure
*
* Program Name          - baggage_segregation_strategies
*
* Project Name          - UltraTrak Database
*
* Description           - Baggage Segregation Rules
*
* Language              - Oracle PL/SQL
*
* Contents  - segregate                     - Main segregation call
*           - segregate_by_dest             - Segregation by destination
*           - segregate_by_carrier          - Segregation by carrier eg 'BA' or
*                                             'BA 1234'
*           - segregate_by_onwd_flight      - Segregation by onward flight
*           - segregate_by_onwd_flight_dest - Segregate by onward flight and
*                                             destination.
*           - have_better_uld               - Determine if other ULD better
*                                             matches segregation rules.
*
* (c) Ultra Electronics Limited, 2004
*
*******************************************************************************/
  /* Main segregation procedure */
  PROCEDURE segregate (p_pax_id              IN pax.pax_id%TYPE
                      ,p_uld_no              IN ulds.uld_no%TYPE
                      ,p_tbag_destination    IN bag.bag_destination%TYPE
                      ,p_tpax_bags_class     IN pax.pax_bags_class%TYPE
                      ,p_first_onward_dest   IN onward_flight.dest%TYPE
                      ,p_t2flight_id         IN flight.flight_id%TYPE
                      ,p_precision_loading   IN carrier_lookup.precision_loading%TYPE
                      ,p_uld_type            IN ulds.uld_type%TYPE
                      ,p_got_onward_class    OUT NUMBER      -- All Segregation Rules
                      ,p_got_onward_dest     OUT NUMBER      -- All Segregation Rules
                      ,p_got_better_uld      OUT NUMBER      -- Segregate by Destination
                      ,p_got_grp_dest        OUT NUMBER      -- Segregate by Destination
                      ,p_got_type            OUT NUMBER
                      ,p_got_local_through   OUT NUMBER
                      ,p_onward_flight_ok    OUT NUMBER
                      ,p_onward_carrier_ok   OUT NUMBER);
                      
 /* To Segregate by Local Transfers, aids BMM processing */                   
 FUNCTION segregate_by_local_through( p_pax_id    IN pax.pax_id%TYPE
                                     ,p_uld_no    IN uld_dests.uld_no%TYPE
                                     ,p_flight_id IN flight.flight_id%TYPE)
                                      RETURN NUMBER;
                                      
 /* To Segregate by Local Throughs, aids BMM processing */                                     
 FUNCTION segregate_by_local_onward( p_pax_id    IN pax.pax_id%TYPE
                                    ,p_uld_no    IN uld_dests.uld_no%TYPE
                                    ,p_flight_id IN flight.flight_id%TYPE)
                                     RETURN NUMBER;                                     
END;
/
PROMPT CREATE OR REPLACE PACKAGE BODY baggage_segregation_strategies

CREATE OR REPLACE PACKAGE BODY baggage_segregation_strategies AS

  /* Local Constants */
  DEBUG           VARCHAR2(1)                    := 'Y';
  VALID           VARCHAR2(1)                    := 'Y';
  l_package_name  ORACLE_ERROR.PROCESS_NAME%TYPE := 'BAGGAGE_SEGREGATION_STRATEGIES.';
  l_error_msg     ORACLE_ERROR.ERRROR_MSG%TYPE   := NULL;
  l_error_code    ORACLE_ERROR.ERROR_CODE%TYPE   := NULL;
  l_error_time    ORACLE_ERROR.ERROR_TIME%TYPE   := NULL;


/******************************************************************************
* Program Unit          - Function                                            *
*                                                                             *
* Subprogram Name       - segregate_by_carrier                                *
*                                                                             *
* Description           - This returns a match if the user has populated any  *
*                         of the onward flight fields with the carrier codes  *
*                         (without flight no). The onward carrier from the .O *
*                         field of the bag's BSM ( ONWARD_FLIGHT table) is    *
*                         checked for a match. At least one is needed to match*
*                         to allow the bag to be loaded.                      *
*                                                                             *
* Parameters (IN)       - p_pax_id                                            *
*                         p_uld_no                                            *
*                         p_flight_id                                         *
*            (OUT)        Count of the number of matches                      *
*                                                                             *
*******************************************************************************/
FUNCTION segregate_by_carrier( p_pax_id    IN pax.pax_id%TYPE
                              ,p_uld_no    IN uld_dests.uld_no%TYPE
                              ,p_flight_id IN flight.flight_id%TYPE)
                              RETURN NUMBER IS
 l_count NUMBER := 0;

BEGIN
    SELECT
           Count(*)
    INTO
           l_count
    FROM
           pax p
          ,flight f
          ,uld_dests ud
          ,onward_flight o
    WHERE
          o.pax_id              = p_pax_id
    AND   p.pax_id              = o.pax_id
    AND   ud.uld_no             = p_uld_no
    AND   p.flight_id           = p_flight_id
    AND   p.flight_id           = f.flight_id
    AND   o.dest_order          = 1
    AND   (ud.uld_destination   = '*'
    OR     ud.uld_destination   = o.dest)
    AND   ud.onward_flight_date IS NULL
    AND   RTrim(ud.onward_flight_no) = RTrim(SubStr(normalise_flight_no(o.no),1,3))
    AND   (InStr(ud.uld_class_list, o.class) > 0
    OR    ud.uld_class_list     = '*');

    RETURN l_count;
END; -- segregate_by_carrier

/******************************************************************************
* Program Unit          - Function                                            *
*                                                                             *
* Subprogram Name       - segregate_by_onwd_flight                            *
*                                                                             *
* Description           - This returns a match if the user has populated any  *
*                         of the onward flight fields. This would allow a     *
*                         match to be done on the full onward flight number as*
*                         found on the .O line of the bag's BSM i.e.          *
*                         (ONWARD_FLIGHT.no and ONWARD_FLIGHT.odate)          *
*                         with one of the flight numbers on the ULD card.     *
*                         At least one is needed to match to allow the bag    *
*                         to be loaded.                                       *
*                                                                             *
* Parameters (IN)       - p_pax_id                                            *
*                         p_uld_no                                            *
*                         p_flight_id                                         *
*            (OUT)        Count of the number of matches                      *
*                                                                             *
*******************************************************************************/
FUNCTION segregate_by_onwd_flight( p_pax_id    IN pax.pax_id%TYPE
                                  ,p_uld_no    IN uld_dests.uld_no%TYPE
                                  ,p_flight_id IN flight.flight_id%TYPE)
                                  RETURN NUMBER IS
l_count NUMBER := 0;

BEGIN
    SELECT
          Count(*)
    INTO
          l_count
    FROM
          pax p
         ,flight f
         ,uld_dests ud
         ,onward_flight o
    WHERE
          o.pax_id                   = p_pax_id
    AND   p.pax_id                   = o.pax_id
    AND   ud.uld_no                  = p_uld_no
    AND   p.flight_id                = p_flight_id
    AND   p.flight_id                = f.flight_id
    AND   o.dest_order               = 1
    AND   RTrim(ud.onward_flight_no) = RTrim(normalise_flight_no(o.no))
    AND   ud.onward_flight_date      = o.odate
    AND  (ud.uld_destination         = '*'
    OR    ud.uld_destination         = o.dest)
    AND   (InStr(ud.uld_class_list, o.class) > 0
    OR    ud.uld_class_list     = '*');


    RETURN l_count;
END; -- segregate_by_onwd_flight

/******************************************************************************
* Program Unit          - Function                                            *
*                                                                             *
* Subprogram Name       - segregate_by_onwd_flight_dest                       *
*                                                                       `     *
* Description           - This returns a match if the user has populated any  *
*                         of the onward flight fields and onwards destination *
*                         fields.                                             *
*                                                                             *
* Parameters (IN)       - p_pax_id                                            *
*                         p_uld_no                                            *
*                         p_flight_id                                         *
*            (OUT)        Count of the number of matches                      *
*                                                                             *
*******************************************************************************/
FUNCTION segregate_by_onwd_flight_dest( p_pax_id    IN pax.pax_id%TYPE
                                       ,p_uld_no    IN uld_dests.uld_no%TYPE
                                       ,p_flight_id IN flight.flight_id%TYPE)
                                       RETURN NUMBER IS
l_count NUMBER := 0;

BEGIN

    SELECT
          Count(*)
    INTO
          l_count
    FROM
           pax p
          ,flight f
          ,uld_dests ud
          ,onward_flight o
    WHERE
          o.pax_id               = p_pax_id
    AND   p.pax_id               = o.pax_id
    AND   ud.uld_no              = p_uld_no
    AND   p.flight_id            = p_flight_id
    AND   p.flight_id            = f.flight_id
    AND   o.dest_order           = 1
    AND   (ud.uld_destination    = o.dest
    OR    ud.uld_destination     = '*')
    AND   RTrim(ud.onward_flight_no) = RTrim(normalise_flight_no(o.no))
    AND   ud.onward_flight_date = o.odate
    AND   (InStr(ud.uld_class_list, o.class) > 0
    OR    ud.uld_class_list     = '*');


    RETURN l_count;
END; -- segregate_by_onwd_flight_dest

/******************************************************************************
* Program Unit          - Function                                            *
*                                                                             *
* Subprogram Name       - have_better_uld                                     *
*                                                                       `     *
* Description           - We need to check whether there is a 'better' uld    *
*                         with destination and class matching the bag         *
*                         destination and class and that this is the same ULD *
*                         as one which will also match the onward destination *
*                         and class.                                          *
*                                                                             *
* Parameters (IN)       - p_flight_id                                         *
*                         p_bag_destination                                   *
*                         p_first_onward_dest                                 *
*                         p_pax_bags_class                                    *
*            (OUT)        Count of the number of 'better ULDs'                *
*                                                                             *
*******************************************************************************/
FUNCTION have_better_uld (  p_flight_id         IN flight.flight_id%TYPE
                          , p_bag_destination   IN uld_dests.uld_destination%TYPE
                          , p_first_onward_dest IN uld_dests.uld_destination%TYPE
                          , p_pax_bags_class    IN pax.pax_class%TYPE
                          , p_uld_num           IN ulds.uld_no%TYPE
                          , p_pax_id            IN pax.pax_id%TYPE
                         )
RETURN NUMBER
IS

l_got_better_uld NUMBER := 0;
l_ignore_uld_onward_dests VARCHAR2(1);
l_onward_flight flight.flight_no%TYPE;
l_onward_carrier VARCHAR2(3);
l_onward_date onward_flight.odate%TYPE;
l_num_dests NUMBER := 0;
l_uld_type ulds.uld_type%TYPE;

BEGIN

   /* CA2566 */
   BEGIN
       select o.norm_fno, RTRIM(SUBSTR(o.norm_fno,1,3)), o.odate
         into l_onward_flight, l_onward_carrier, l_onward_date
         from onward_flight o
        where o.pax_id = p_pax_id and o.dest_order = 1;
   EXCEPTION
       WHEN NO_DATA_FOUND THEN
            l_onward_flight := NULL;
            l_onward_carrier := NULL;
   END; 

   /* CA2866 - Is the ULD generic? */  
   BEGIN
      select count(*) into l_num_dests
        from uld_dests
       where uld_no = p_uld_num 
         and flight_id = p_flight_id;
   EXCEPTION
       WHEN OTHERS THEN
            RAISE; 
   END;
   
   /* What type is the ULD? */
   BEGIN
      select uld_type into l_uld_type
        from ulds
       where uld_no = p_uld_num 
         and flight_id = p_flight_id;
   EXCEPTION
       WHEN OTHERS THEN
            RAISE; 
   END;

   /* Is this check enabled?.
     The setting at carrier + site level overrides the setting at site level.
     If not configured at either site or carrier + site level then check is enabled by default
   */
   BEGIN
      SELECT config_value
        INTO l_ignore_uld_onward_dests
        FROM carrier_site_config csc, flight f
       WHERE f.flight_id = p_flight_id
         AND f.carrier_alpha_code = csc.alpha_code
         AND f.site_id = csc.site_id
         AND csc.config_item = 'IGNORE_ULD_ONWRD_DST';
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         BEGIN
            SELECT db_value
              INTO l_ignore_uld_onward_dests
              FROM site_config sc, flight f
             WHERE f.flight_id = p_flight_id
               AND f.site_id = sc.site_id
               AND sc.db_item = 'IGNORE_ULD_ONWRD_DST';
         EXCEPTION
            WHEN NO_DATA_FOUND THEN
               l_ignore_uld_onward_dests := 'N';
         END;
   END;

   IF l_ignore_uld_onward_dests = 'Y' THEN
      l_got_better_uld := 0;
   ELSE

    /* We need to check whether there is a 'better' uld with destination and class *
     * matching the bag destination and class and that this is the same ULD        *
     * as one which will also match the onward destination and class               */

   IF l_num_dests = 1 OR l_uld_type = 'M' THEN -- uld is generic or sort, check group dests
       SELECT
                count(*)
         INTO
                l_got_better_uld
         FROM
                ulds ud, uld_dests ud1
         WHERE
                ud1.uld_no          = ud.uld_no
         AND
                ud1.flight_id       = ud.flight_id
         AND
                ud.close_flag       = 'N'
         AND
                ud1.flight_id       = p_flight_id
         AND
                ud.uld_no          != p_uld_num
         AND
                (ud1.uld_destination = p_bag_destination
                OR ud1.uld_destination in (
                                             SELECT group_dest
                                               FROM group_iata_dests
                                              WHERE dest = p_bag_destination
                                        /* CA2537 - Don't ignore onward destination groups */
                   )
                )

         AND
                (ud1.uld_class_list = '*' OR INSTR(ud1.uld_class_list, p_pax_bags_class) > 0)
         AND
                ud1.uld_no IN
                              (
                                SELECT
                                       ud2.uld_no
                                FROM
                                       uld_dests ud2
                                WHERE
                                       ud2.flight_id       = p_flight_id
                                AND
                                (
                                  ( ud2.ULD_DESTINATION = p_first_onward_dest
                                    OR ud2.ULD_DESTINATION IN
                                    (
                                      SELECT group_dest
                                        FROM group_iata_dests
                                       WHERE dest = p_first_onward_dest 
						     /* CA2866 */
                                         AND ((SELECT uld_type -- New ULD is transfer
                                                 FROM ulds
                                                WHERE uld_no = ud2.uld_no 
                                                  AND flight_id = p_flight_id) = 'T'
                                          OR l_num_dests = 1) -- Orig. ULD is Generic
                                    )
                                  )
                                  AND
                                    ( ud2.uld_class_list = '*' OR INSTR(ud2.uld_class_list, p_pax_bags_class) > 0 )
                                  AND /* CA2566 */
                                  (
                                    (
                                      ud2.onward_flight_no = TRIM(l_onward_flight)
                                      AND ud2.onward_flight_date = l_onward_date
                                    )
                                    OR ud2.onward_flight_no IS NULL
                                    OR ud2.onward_flight_no = l_onward_carrier
                                  )
                                )
                                OR
                                (
                                  TRIM(ud2.ULD_DESTINATION) = '*'
                                  AND TRIM(ud2.uld_class_list) = '*'
                                  AND
                                  (
                                    (
                                      ud2.onward_flight_no = TRIM(l_onward_flight)
                                      AND  ud2.onward_flight_date = l_onward_date
                                    )
                                    OR
                                      ud2.onward_flight_no = l_onward_carrier
                                  )

                                )
                              );
  ELSE -- uld is not generic, don't check group dests
      SELECT
                count(*)
         INTO
                l_got_better_uld
         FROM
                ulds ud, uld_dests ud1
         WHERE
                ud1.uld_no          = ud.uld_no
         AND
                ud1.flight_id       = ud.flight_id
         AND
                ud.close_flag       = 'N'
         AND
                ud1.flight_id       = p_flight_id
         AND
                ud.uld_no          != p_uld_num
         AND
                ud1.uld_destination = p_bag_destination
         AND
                (ud1.uld_class_list = '*' OR INSTR(ud1.uld_class_list, p_pax_bags_class) > 0)
         AND
                ud1.uld_no IN
                              (
                                SELECT
                                       ud2.uld_no
                                FROM
                                       uld_dests ud2
                                WHERE
                                       ud2.flight_id       = p_flight_id
                                AND
                                (
                                      ud2.ULD_DESTINATION = p_first_onward_dest
                                  AND
                                    ( ud2.uld_class_list = '*' OR INSTR(ud2.uld_class_list, p_pax_bags_class) > 0 )
                                  AND /* CA2566 */
                                  (
                                    (
                                      ud2.onward_flight_no = TRIM(l_onward_flight)
                                      AND ud2.onward_flight_date = l_onward_date
                                    )
                                    OR ud2.onward_flight_no IS NULL
                                    OR ud2.onward_flight_no = l_onward_carrier
                                  )
                                )
                                OR
                                (
                                  TRIM(ud2.ULD_DESTINATION) = '*'
                                  AND TRIM(ud2.uld_class_list) = '*'
                                  AND
                                  (
                                    (
                                      ud2.onward_flight_no = TRIM(l_onward_flight)
                                      AND  ud2.onward_flight_date = l_onward_date
                                    )
                                    OR
                                      ud2.onward_flight_no = l_onward_carrier
                                  )

                                )
                              );
  END IF; -- uld generic? sort?  
  END IF; -- ignore_uld_onward_dests = N

  RETURN l_got_better_uld;

END; -- have_better_uld
/******************************************************************************
* Program Unit          - Procedure                                           *
*                                                                             *
* Subprogram Name       - segregate_by_dest                                   *
*                                                                       `     *
* Description           - This returns a match if the user has populated any  *
*                         of the onward flight fields and onwards destination *
*                         fields.                                             *
*                                                                             *
* Parameters (IN)       - p_uld_no            - uld                           *
*                         p_tbag_destination  - bag destination               *
*                         p_tpax_bags_class   - class of bag                  *
*                         p_first_onward_dest - first onward destination      *
*                                                                             *
*            (OUT)        p_got_onward_class  - onward_class match            *
*                         p_got_onward_dest   - onward_dest match             *
*                         p_got_better_uld    - better uld match              *
*                         p_got_grp_dest      - group dest match              *                                                                             *
*******************************************************************************/
PROCEDURE segregate_by_dest (p_uld_no            IN ulds.uld_no%TYPE
                            ,p_tbag_destination  IN bag.bag_destination%TYPE
                            ,p_tpax_bags_class   IN pax.pax_bags_class%TYPE
                            ,p_first_onward_dest IN onward_flight.dest%TYPE
                            ,p_t2flight_id       IN flight.flight_id%TYPE
                            ,p_pax_id            IN pax.pax_id %TYPE
                            ,p_got_onward_class OUT NUMBER
                            ,p_got_onward_dest  OUT NUMBER
                            ,p_got_better_uld   OUT NUMBER
                            ,p_got_grp_dest     OUT NUMBER)  AS

    /* ULD_DESTS values */
    l_got_onward_class    NUMBER := 0;
    l_got_onward_dest     NUMBER := 0;
    l_got_better_uld      NUMBER := 0;
    l_got_grp_dest        NUMBER := 0;
    l_dest_count          NUMBER := 0;

    l_uld_destination     uld_dests.uld_destination%TYPE := NULL;
    l_uld_class_list      VARCHAR2(10)                   := NULL;
    l_uld_grps_dest       group_iata_dests.dest%TYPE     := NULL;

    /* Main uld_dests cursor */
    CURSOR uld_details ( c_uldno             IN CHAR
                        ,c_t2flight_id       IN NUMBER) IS
		SELECT
			ud.uld_destination,
			rtrim(ud.uld_class_list),
			nvl(g.dest,'-')
		FROM
			ulds u, uld_dests  ud,group_iata_dests g, carrier_lookup c, flight f
		WHERE
          u.uld_no                = c_uldno
		AND   u.flight_id             = c_t2flight_id
		AND   u.uld_no                = ud.uld_no
		AND   u.flight_id             = ud.flight_id
		AND   ud.uld_destination      = g.group_dest(+)
    AND   u.flight_id             = f.flight_id
		AND   substr(f.flight_no,1,3) = c.alpha_code
    AND   ud.onward_flight_no     IS NULL
    AND   ud.onward_flight_date   IS NULL;

BEGIN
    /* Some debug information */
    IF ( DEBUG = VALID ) THEN
        dbms_output.put_line( '==========================================');
        dbms_output.put_line('Some Debug Information - IN parameters');
        dbms_output.put_line( '==========================================');
        dbms_output.put_line('p_uld_no           :'||p_uld_no);
        dbms_output.put_line('p_t2flgiht_id      :'||p_t2flight_id);
        dbms_output.put_line('p_tbag_destination :'||p_tbag_destination);
        dbms_output.put_line('p_first_onward_dest:'||p_first_onward_dest);
        dbms_output.put_line( '==========================================');
    END IF;


    OPEN  uld_details ( p_uld_no
                       ,p_t2flight_id );
    LOOP
          FETCH  uld_details
          INTO   l_uld_destination
                ,l_uld_class_list
                ,l_uld_grps_dest;
          EXIT WHEN uld_details%NOTFOUND;

          /* Some debug information */
          IF ( DEBUG = VALID ) THEN
             dbms_output.put_line( '==========================================');
             dbms_output.put_line('BEFORE: Onward flight dest details');
             dbms_output.put_line( '==========================================');
             dbms_output.put_line(' l_uld_destination   :'|| l_uld_destination  );
			       dbms_output.put_line(' l_uld_class_list    :'|| l_uld_class_list   );
             dbms_output.put_line(' l_uld_grps_dest     :'|| l_uld_grps_dest    );
             dbms_output.put_line( '==========================================');
          END IF;

          /*  consider the first onward dest for a bag the uld dest*/
          IF (l_uld_destination = p_first_onward_dest ) THEN
             l_got_onward_dest := 1;
             is_class_ok ( p_tpax_bags_class , l_uld_class_list, l_got_onward_class);

             /* Some debug information */
              IF ( DEBUG = VALID ) THEN
                 dbms_output.put_line('checking the uld_destination with onward_dest');
                 dbms_output.put_line('l_uld_destination   :'||l_uld_destination);
                 dbms_output.put_line('p_first_onward_dest :'||p_first_onward_dest);
                 dbms_output.put_line('p_tpax_bags_class   :'||p_tpax_bags_class);
                 dbms_output.put_line('l_uld_class_list    :'||l_uld_class_list);
                 dbms_output.put_line('l_got_onward_class  :'||l_got_onward_class);
              END IF;
          END IF;

          IF (l_uld_grps_dest = p_first_onward_dest ) THEN
              l_got_grp_dest := 1;
              is_class_ok ( p_tpax_bags_class , l_uld_class_list, l_got_onward_class);

              /* Some debug information */
              IF ( DEBUG = VALID ) THEN
                 dbms_output.put_line('checking the uld_grps_dest with onward_dest');
                 dbms_output.put_line('l_uld_grps_dest     :'||l_uld_grps_dest);
                 dbms_output.put_line('p_first_onward_dest :'||p_first_onward_dest);
                 dbms_output.put_line('p_tpax_bags_class   :'||p_tpax_bags_class);
                 dbms_output.put_line('l_uld_class_list    :'||l_uld_class_list);
                 dbms_output.put_line('l_got_onward_class  :'||l_got_onward_class);
              END IF;

              /*  If the uld has an OK group dest  */
              /*  there may be a better uld for the bag_dest */

              IF (l_got_onward_class = 1) THEN
	     /* is there a better uld for this bag_dest */

                  l_got_better_uld := have_better_uld ( p_flight_id         => p_t2flight_id
                                                      , p_bag_destination   => p_tbag_destination
                                                      , p_first_onward_dest => p_first_onward_dest
                                                      , p_pax_bags_class    => p_tpax_bags_class
                                                      , p_uld_num           => p_uld_no /* CA2537 */
                                                      , p_pax_id            => p_pax_id
                                                      );
              END IF;
          END IF;

          SELECT COUNT(uld_destination)
          INTO  l_dest_count
          FROM  uld_dests
          WHERE flight_id = p_t2flight_id
          AND   uld_no = p_uld_no;

          IF l_dest_count = 1 THEN

              is_class_ok ( p_tpax_bags_class , l_uld_class_list, l_got_onward_class);

              IF (l_got_onward_class = 1) THEN

                  l_got_better_uld := have_better_uld ( p_flight_id         => p_t2flight_id
                                                      , p_bag_destination   => p_tbag_destination
                                                      , p_first_onward_dest => p_first_onward_dest
                                                      , p_pax_bags_class    => p_tpax_bags_class
                                                      , p_uld_num           => p_uld_no /* CA2537 */
                                                      , p_pax_id            => p_pax_id
                                                      );

              END IF;
          END IF;


        /* Some debug information */
        IF ( DEBUG = VALID ) THEN
           dbms_output.put_line( '==========================================');
           dbms_output.put_line('AFTER: Onward flight desc details');
           dbms_output.put_line( '==========================================');
           dbms_output.put_line(' l_uld_destination   :'|| l_uld_destination  );
           dbms_output.put_line(' l_got_onward_dest   :'|| l_got_onward_dest  );
           dbms_output.put_line(' l_got_onward_class  :'|| l_got_onward_class  );
			     dbms_output.put_line(' l_uld_class_list    :'|| l_uld_class_list   );
           dbms_output.put_line(' l_got_grp_dest      :'|| l_got_grp_dest     );
           dbms_output.put_line(' l_got_better_uld    :'|| l_got_better_uld   );
           dbms_output.put_line( '==========================================');
        END IF;
    END LOOP;

    /* Assign Out Parameters */
    p_got_onward_class := l_got_onward_class;
    p_got_onward_dest  := l_got_onward_dest;
    p_got_better_uld   := l_got_better_uld;
    p_got_grp_dest     := l_got_grp_dest;
END;

/******************************************************************************
* Program Unit          - Function                                            *
*                                                                             *
* Subprogram Name       - segregate_by_local_through                          *
*                                                                       `     *
* Description           - This returns a match if the segregate by local      *
*                         through having ALL matching onward flight details   *
*                                                                             *
* Parameters (IN)       - p_pax_id                                            *
*                         p_uld_no                                            *
*                         p_flight_id                                         *
*            (OUT)        Count of the number of matches                      *
*                                                                             *
*******************************************************************************/
FUNCTION segregate_by_local_through( p_pax_id    IN pax.pax_id%TYPE
                                    ,p_uld_no    IN uld_dests.uld_no%TYPE
                                    ,p_flight_id IN flight.flight_id%TYPE)
                                    RETURN NUMBER IS
l_count NUMBER := 0;

BEGIN
    SELECT 
          Count(*)
    INTO  
          l_count
    FROM
           pax p
          ,flight f
          ,uld_dests ud
          ,onward_flight o
    WHERE
          o.pax_id               = p_pax_id
    AND   p.pax_id               = o.pax_id
    AND   ud.uld_no              = p_uld_no
    AND   p.flight_id            = p_flight_id
    AND   p.flight_id            = f.flight_id
    AND   (ud.uld_destination    = o.dest
    OR    ud.uld_destination     = '*')
    AND   (InStr(ud.uld_class_list, o.class) > 0
    OR    ud.uld_class_list     = '*')
    AND   flight_no = normalise_flight_no(o.no)
    AND   NOT exists (SELECT 1
                      FROM  onward_flight o2
                      WHERE f.flight_no <> normalise_flight_no(o2.no)
                      AND   p.pax_id = o2.pax_id
                      AND   o2.pax_id = p_pax_id);    
    
    RETURN l_count;
END; -- segregate_by_local_through

/******************************************************************************
* Program Unit          - Function                                            *
*                                                                             *
* Subprogram Name       - segregate_by_local_onward                           *
*                                                                       `     *
* Description           - This returns a match if the segregate by local      *
*                         onward flight exists, i.e. having onward flight     *
*                         which does not match the outbound flight.           *
*                                                                             *
* Parameters (IN)       - p_pax_id                                            *
*                         p_uld_no                                            *
*                         p_flight_id                                         *
*            (OUT)        Count of the number of matches                      *
*                                                                             *
*******************************************************************************/
FUNCTION segregate_by_local_onward ( p_pax_id    IN pax.pax_id%TYPE 
                                    ,p_uld_no    IN uld_dests.uld_no%TYPE
                                    ,p_flight_id IN flight.flight_id%TYPE)
                                    RETURN NUMBER IS
l_count NUMBER := 0;

BEGIN

    SELECT 
          Count(*)
    INTO  
          l_count
    FROM
           pax p
          ,flight f
          ,uld_dests ud
          ,onward_flight o
    WHERE
          o.pax_id               = p_pax_id
    AND   p.pax_id               = o.pax_id
    AND   ud.uld_no              = p_uld_no
    AND   p.flight_id            = p_flight_id
    AND   p.flight_id            = f.flight_id
    AND   (ud.uld_destination    = o.dest
    OR    ud.uld_destination     = '*')
    AND   (InStr(ud.uld_class_list, o.class) > 0
    OR    ud.uld_class_list     = '*')
    AND   ( ( flight_no = normalise_flight_no(o.no)
    AND      EXISTS ( SELECT 1
                      FROM  onward_flight o2
                      WHERE f.flight_no <> normalise_flight_no(o2.no)
                      AND   p.pax_id = o2.pax_id
                      AND   o2.pax_id = p_pax_id))        
    OR   flight_no <> normalise_flight_no(o.no));
    
    RETURN l_count;
END; -- segregate_by_local_onward

/******************************************************************************
* Program Unit          - Procedure                                           *
*                                                                             *
* Subprogram Name       - segregate                                           *
*                                                                       `     *
* Description           - This procedure encapsulates all the segregation     *
*                         rules for dealing with onward destinations and      *
*                         onward flights. It sets the out parameters = 1 if   *
*                         it finds a match and 0 if it doesn't.               *
*                         This procedure is called from check_bag_for_load.   *
*                                                                             *
* Parameters (IN)       - p_pax_id            - pax                           *
*                         p_uld_no            - uld                           *
*                         p_tbag_destination  - bag destination               *
*                         p_tpax_bags_class   - class of bag                  *
*                         p_first_onward_dest - first onward destination      *
*                         p_t2flight_id       - flight                        *
*                         p_precision_loading - precision loading             *
*                         p_uld_type          - uld type                      *
*                                                                             *
*                                                                             *
*            (OUT)        p_got_onward_class  - onward_class match            *
*                         p_got_onward_dest   - onward_dest match             *
*                         p_got_better_uld    - better uld match              *
*                         p_got_grp_dest      - group dest match              *
*                         p_got_type          - uld/bag type match            *
*                         p_got_local_through - ?                             *
*                         p_onward_flight_ok  - onward flight ok (info only)  *
*                         p_onward_carrier_ok - onward carrier ok (info only) *
*******************************************************************************/
PROCEDURE segregate (p_pax_id              IN pax.pax_id%TYPE
                    ,p_uld_no              IN ulds.uld_no%TYPE
                    ,p_tbag_destination    IN bag.bag_destination%TYPE
                    ,p_tpax_bags_class     IN pax.pax_bags_class%TYPE
                    ,p_first_onward_dest   IN onward_flight.dest%TYPE
                    ,p_t2flight_id         IN flight.flight_id%TYPE
                    ,p_precision_loading   IN carrier_lookup.precision_loading%TYPE
                    ,p_uld_type            IN ulds.uld_type%TYPE
                    ,p_got_onward_class    OUT NUMBER         -- All Segregation Rules
                    ,p_got_onward_dest     OUT NUMBER         -- All Segregation Rules
                    ,p_got_better_uld      OUT NUMBER         -- Segregate by Destination
                    ,p_got_grp_dest        OUT NUMBER         -- Segregate by Destination
                    ,p_got_type            OUT NUMBER
                    ,p_got_local_through   OUT NUMBER
                    ,p_onward_flight_ok    OUT NUMBER
                    ,p_onward_carrier_ok   OUT NUMBER) AS

  /* Error logging information*/
  l_function_name           ORACLE_ERROR.PROCESS_NAME%TYPE := 'SEGREGATE';

  /* Onward indicators */
  l_onwd_carrier_ok         NUMBER := 0;
  l_onwd_flight_ok          NUMBER := 0;
  l_onwd_flight_dest_ok     NUMBER := 0;
  l_local_through_ok        NUMBER := 0;
  l_local_onward_ok         NUMBER := 0;


  l_got_onward_class        NUMBER := 0;
  l_got_onward_dest         NUMBER := 0;
  l_got_better_uld          NUMBER := 0;
  l_got_grp_dest            NUMBER := 0;
  l_got_type                NUMBER := 0;
  l_got_local_through       NUMBER := 0;
  is_hold                   NUMBER;

BEGIN

   BEGIN
    select
	    count(hold_no)
    into
 	    is_hold
    from
 	    holds
    where
 	    hold_no = p_uld_no;
    exception
	    when NO_DATA_FOUND then
	    is_hold := 0; /* this will trip the following check. */
    END;

 	/* if we are not doing priority loading ignore thos check */                                
  IF (p_precision_loading = 'N' OR ((p_first_onward_dest <> '-' AND p_uld_type = 'T')
                                OR   p_uld_type IN ('L','M'))) THEN 
      /*transfer bags not allowed in local ULDs if precision loading is enabled*/
      IF p_precision_loading = 'N' OR NOT ( p_uld_type = 'L' AND p_first_onward_dest <>  '-') THEN
			l_got_type := 1;
      END IF;
      /*****************************************************************************************
      * Check each the segregation rule ( segregation by destination )for a match.             *
      * If no match is found then call the other segregation rules to find a match.            *
      *****************************************************************************************/
      IF (p_uld_type <> 'L') THEN
          segregate_by_dest ( p_uld_no            => p_uld_no
                             ,p_tbag_destination  => p_tbag_destination
                             ,p_tpax_bags_class   => p_tpax_bags_class
                             ,p_first_onward_dest => p_first_onward_dest
                             ,p_t2flight_id       => p_t2flight_id
                             ,p_pax_id            => p_pax_id
                             ,p_got_onward_class  => l_got_onward_class  -- Onward Class match
                             ,p_got_onward_dest   => l_got_onward_dest   -- Onward Destination match
                             ,p_got_better_uld    => l_got_better_uld    -- Better ULD match
                             ,p_got_grp_dest      => l_got_grp_dest);    -- Group Destination match
     /*****************************************************************************************
      * Check each segregation rule for a match. If no match is found this is then dealt with *
      * from within check_bag_for_load procedure and the bag would not be loaded on to the ULD*
      *****************************************************************************************/
      
         IF (l_got_onward_class = 0 AND  l_got_onward_dest = 0) THEN

            /* Segregate by Carrier */
            l_onwd_carrier_ok     :=  segregate_by_carrier( p_pax_id    => p_pax_id
                                                           ,p_uld_no    => p_uld_no
                                                           ,p_flight_id => p_t2flight_id);

            /* Segregate by Onward Flight */
            l_onwd_flight_ok      :=  segregate_by_onwd_flight( p_pax_id     => p_pax_id
                                                               ,p_uld_no     => p_uld_no
                                                               ,p_flight_id  => p_t2flight_id);

            /* Segregate by Omward Flight and Destination */
            l_onwd_flight_dest_ok := segregate_by_onwd_flight_dest( p_pax_id    => p_pax_id
                                                                   ,p_uld_no    => p_uld_no
                                                                   ,p_flight_id => p_t2flight_id);
         END IF; -- no match, hence check other segregation rules

      ELSIF (p_uld_type = 'L') THEN
      /****************************************************************************************/
      /* Segregate by Local Transfer                                                          */
      /****************************************************************************************/
      
         l_local_through_ok := segregate_by_local_through( p_pax_id    => p_pax_id
                                                          ,p_uld_no    => p_uld_no
                                                          ,p_flight_id => p_t2flight_id);                                                               

         l_local_onward_ok := segregate_by_local_onward( p_pax_id    => p_pax_id
                                                        ,p_uld_no    => p_uld_no
                                                        ,p_flight_id => p_t2flight_id);                                                               
                                                               
      END IF;
     
      /********************************************************/
      /* Set the onward_class and onward_dest if match exists */
      /* Set the Onward Flight flags and transfer flags       */
      /* depending on whether it is an Onward Flight or       */
      /* transfer/through flight                              */
      /********************************************************/
    
      IF ( l_onwd_carrier_ok     > 0 OR
           l_onwd_flight_ok      > 0 OR
           l_onwd_flight_dest_ok > 0)  THEN
         /* Set the onward_class and onward_dest indicators to */
         /* say at least one has matched */
         l_got_onward_class := 1;
         l_got_onward_dest  := 1;
       
      ELSIF ((l_local_through_ok > 0 OR l_local_onward_ok > 0) AND p_uld_type = 'L') THEN    
             IF (l_local_through_ok > 0) THEN
                 /* Need to reset the values for local_through bags */
                 l_got_local_through := 1;
                 l_got_onward_class  := 1; -- implicit onward_class
                 l_got_onward_dest   := 1; -- implicit onward_dest 
                 l_got_grp_dest := 0;             -- transfer is not a group dest
                 l_got_type := 1;
             ELSIF (l_local_onward_ok > 0) THEN   -- there is a match which doesn't meet our criteria
                   l_got_type := 0;               -- therefore wrong uld_type
             END IF;
      END IF;

  ELSIF is_hold > 0  THEN
      /* check to see whether this is a hold and if is ignore type etc */
      l_got_type := 1;
      l_got_onward_class :=1;
      l_got_onward_dest  := 1;
  END IF;

  Dbms_Output.put_line('baggage_segregation_strategies.segregate----');
  Dbms_Output.put_line('l_onwd_carrier_ok     :'||l_onwd_carrier_ok);
  Dbms_Output.put_line('l_onwd_flight_ok      :'||l_onwd_flight_ok);
  Dbms_Output.put_line('l_onwd_flight_dest_ok :'||l_onwd_flight_dest_ok);
  Dbms_Output.put_line('l_got_onward_class    :'||l_got_onward_class);
  Dbms_Output.put_line('l_got_onward_dest     :'||l_got_onward_dest);
  Dbms_Output.put_line('l_local_through_ok    :'||l_local_through_ok);
  Dbms_Output.put_line('l_local_onward_ok     :'||l_local_onward_ok);
  Dbms_Output.put_line('baggage_segregation_strategies.segregate----');

  
  /* Assign any variables need */
  p_got_type           := l_got_type;
  p_got_onward_class   := l_got_onward_class;
  p_got_onward_dest    := l_got_onward_dest;
  p_got_better_uld     := l_got_better_uld;
  p_got_grp_dest       := l_got_grp_dest;
  p_got_local_through  := l_got_local_through;
  p_onward_flight_ok   := l_onwd_flight_ok;
  p_onward_carrier_ok  := l_onwd_carrier_ok;
  
  
  EXCEPTION
    WHEN OTHERS THEN
      /* General catch all error, inserts into Oracle_error table */
      l_error_msg  := SQLERRM;
      l_error_code := SQLCODE;
      l_error_time := SYSDATE;
      INSERT INTO oracle_error  ( process_name
                                 ,errror_msg
                                 ,error_code
                                 ,error_time)
                          VALUES( l_package_name||l_function_name
                                 ,l_error_msg
                                 ,l_error_code
                                 ,l_error_time);
      IF ( DEBUG = VALID ) THEN
         dbms_output.put_line('------------------------------------');
         dbms_output.put_line('When others error: '||l_error_code||'-'||l_error_msg);
      END IF;
  END; --segregate
END;
/
