/*****************************************************************************
*
* Filename         - $Workfile:   pax_match.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: pax_match package
*
* Language         - Oracle SQL*Plus script
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2006
*
*****************************************************************************/

-- Notes
--
-- To add additional columns to the Pax Matching process, the following changes
-- are required:
--
-- 1) Add a new field to the appropriate database type definition to store the
--    the field data
-- 2) Add the new column to the GET_PAX_VALUES procedure to extract the value
--    for the named column from the structure
-- 3) Amend the LOAD_INBOUND_PAX and LOAD_OUTBOUND_PAX to retrieve the new column
--    from the relevant table via the cursors.
-- 4) Add the column to the hard-coded table in INITIALISE_COLUMNS
--
-- It is not possible to define DATABASE TYPE fields based upon table column
-- definitions using the %TYPE specifier.  This means that all fields in the
-- database types are hard-coded to be the same type / size as the underlying
-- tables.  Any changes to table column types must be reflected in this
-- package.
--

-- Database Types
CREATE OR REPLACE TYPE pm_column_record IS OBJECT
(
  table_name     VARCHAR2(30),
  column_name    VARCHAR2(30),
  field_type     VARCHAR2(1)
);
/
CREATE OR REPLACE TYPE pm_onward_flight_record IS OBJECT
(
  on_flight_no   CHAR(8),
  on_flight_date CHAR(5),
  on_flight_dest CHAR(3),
  on_dest_order  NUMBER(2),
  on_class       CHAR(1)
);
/
CREATE OR REPLACE TYPE pm_bag_record IS OBJECT
(
  bag_tag        CHAR(10),
  bag_tag2b      CHAR(3),
  bag_tag3       NUMBER(10),
  atl_dcs        CHAR(1),
  destination    CHAR(3),
  bag_except     CHAR(4),
  bsi            CHAR(1),
  src_location   CHAR(3),
  bag_seq_no     NUMBER(6),
  dcs_bag_tag    CHAR(10),
  bag_type       VARCHAR2(59)
);
/
CREATE OR REPLACE TYPE pm_rule_record IS OBJECT
(
  next_rule_linked       CHAR(1),
  table_name             VARCHAR2(30),
  column_name            VARCHAR2(30),
  test_if                VARCHAR2(6),
  fatal                  CHAR(1),
  limit                  CHAR(1),
  lower_limit            VARCHAR2(100),
  upper_limit            VARCHAR2(100),
  operator               CHAR(1),
  return_on_positive     NUMBER(10),
  return_on_negative     NUMBER(10)
);
/
CREATE OR REPLACE TYPE pm_column_list IS TABLE OF pm_column_record;
/
CREATE OR REPLACE TYPE pm_onward_flight_list IS TABLE OF pm_onward_flight_record;
/
CREATE OR REPLACE TYPE pm_bag_list IS TABLE OF pm_bag_record;
/
CREATE OR REPLACE TYPE pm_rule_list IS TABLE OF pm_rule_record;
/
CREATE OR REPLACE TYPE pm_pax_record IS OBJECT
(
  pax_initials           CHAR(4),
  pax_surname            CHAR(27),
  no_same_surname        NUMBER(6),
  destination            CHAR(3),
  seat_no                CHAR(3),
  class                  CHAR(1),
  pax_sequence_no        NUMBER(10),
  pax_security_no        CHAR(3),
  pax_name_record        CHAR(6),
  dep_airport_code       CHAR(3),
  inbound_flight_no      CHAR(8),
  inbound_date           CHAR(5),
  inbound_boarding_point CHAR(3),
  pax_full_inits         CHAR(60),
  pax_dcs_seq_no         CHAR(3),
  freq_flyer             VARCHAR2(60),
  on_flight_list         pm_onward_flight_list,
  bag_list               pm_bag_list
);
/

-- Package
CREATE OR REPLACE PACKAGE pax_match IS

  -- Exceptions
  invalid_column   EXCEPTION;
  invalid_table    EXCEPTION;

  -- Types
  TYPE t_pax_list IS TABLE OF NUMBER;
  TYPE t_string_list IS TABLE OF VARCHAR2(4000);

  -- Variables
  g_columns        pm_column_list;
  g_matched        t_pax_list;
  g_matched_metric t_pax_list;
  g_algorithm      pax_match_algorithms.algorithm_name%TYPE;
  g_rules          pm_rule_list;

  -- Procedures
  PROCEDURE initialise_columns;
  PROCEDURE load_pax_structure
  (
    p_structure    IN OUT pm_pax_record,
    p_flight_id    IN     flight.flight_id%TYPE,
    p_pax_id       IN     pax.pax_id%TYPE
  );
  PROCEDURE load_inbound_pax
  (
    p_structure    IN OUT pm_pax_record,
    p_pax_id       IN     inbound_pax.inbound_pax_id%TYPE
  );
  PROCEDURE load_outbound_pax
  (
    p_structure    IN OUT pm_pax_record,
    p_pax_id       IN     pax.pax_id%TYPE
  );
  PROCEDURE initialise_algorithm
  (
    algorithm_name IN     pax_match_algorithms.algorithm_name%TYPE
  );
  PROCEDURE get_pax_values
  (
    p_pax          IN     pm_pax_record,
    p_table_name   IN     pax_match_rules.table_name%TYPE,
    p_column_name  IN     pax_match_rules.column_name%TYPE,
    P_values       IN OUT t_string_list
  );

  -- Functions
  FUNCTION validate_column
  (
    p_table_name   IN     pax_match_rules.table_name%TYPE,
    p_column_name  IN     pax_match_rules.column_name%TYPE
  ) RETURN         BOOLEAN;
  FUNCTION convert_to_string
  (
    value          IN     NUMBER
  ) RETURN         VARCHAR2;
  FUNCTION convert_date_to_string
  (
    value          IN     DATE
  ) RETURN         VARCHAR2;
  FUNCTION convert_time_to_string
  (
    value          IN     DATE
  ) RETURN         VARCHAR2;
  FUNCTION perform_match
  (
    p_algorithm_name IN     pax_match_algorithms.algorithm_name%TYPE,
    p_pax_structure  IN     pm_pax_record,
    p_flight_id      IN     flight.flight_id%TYPE
  ) RETURN         pax.pax_id%TYPE;
  FUNCTION compare_pax
  (
    source_pax     IN     pm_pax_record,
    check_pax      IN     pm_pax_record
  ) RETURN         NUMBER;
  FUNCTION check_limits
  (
    p_field_type   IN     VARCHAR2,
    p_lower_limit  IN     pax_match_rules.lower_limit%TYPE,
    p_upper_limit  IN     pax_match_rules.upper_limit%TYPE,
    p_values       IN     t_string_list
  ) RETURN         BOOLEAN;
  FUNCTION compare_string
  (
    p_operator     IN     pax_match_rules.operator%TYPE,
    p_source       IN     t_string_list,
    p_check        IN     t_string_list
  ) RETURN BOOLEAN;
  FUNCTION get_column_type
  (
    p_table_name   IN     pax_match_rules.table_name%TYPE,
    p_column_name  IN     pax_match_rules.column_name%TYPE
  ) RETURN         VARCHAR2;


END pax_match;
/


CREATE OR REPLACE PACKAGE BODY pax_match IS


  ------------------------------------------------------------------
  -- initialise_columns
  --
  PROCEDURE initialise_columns IS

  BEGIN

    -- If g_columns is atomically null, then
    -- create and load the (hard-coded) column list

    IF (g_columns IS NULL) THEN

      -- Create the table
      g_columns := pm_column_list();

      -- Hard-Coded Column List
      g_columns.EXTEND(32);
      g_columns(1)  := pm_column_record('PAX', 'PAX_INITIALS', 'S');
      g_columns(2)  := pm_column_record('PAX', 'PAX_SURNAME', 'S');
      g_columns(3)  := pm_column_record('PAX', 'NO_SAME_SURNAME', 'N');
      g_columns(4)  := pm_column_record('PAX', 'PAX_DESTINATION', 'S');
      g_columns(5)  := pm_column_record('PAX', 'PAX_SEAT_NO', 'S');
      g_columns(6)  := pm_column_record('PAX', 'PAX_CLASS', 'S');
      g_columns(7)  := pm_column_record('PAX', 'PAX_SEQUENCE_NO', 'N');
      g_columns(8)  := pm_column_record('PAX', 'PAX_SECURITY_NO', 'S');
      g_columns(9)  := pm_column_record('PAX', 'PAX_NAME_RECORD', 'S');
      g_columns(10) := pm_column_record('PAX', 'DEPARTURE_AIRPORT_CODE', 'S');
      g_columns(11) := pm_column_record('PAX', 'INBOUND_FLIGHT_NO', 'S');
      g_columns(12) := pm_column_record('PAX', 'INBOUND_DATE', 'S');
      g_columns(13) := pm_column_record('PAX', 'INBOUND_BOARDING_POINT', 'S');
      g_columns(14) := pm_column_record('PAX', 'PAX_FULL_INITS', 'S');
      g_columns(15) := pm_column_record('PAX', 'PAX_DCS_SEQUENCE_NO', 'S');
      g_columns(16) := pm_column_record('PAX', 'FREQ_FLYER', 'S');
      g_columns(17) := pm_column_record('BAG', 'BAG_TAG', 'S');
      g_columns(18) := pm_column_record('BAG', 'BAG_TAG2B', 'S');
      g_columns(19) := pm_column_record('BAG', 'BAG_TAG3', 'N');
      g_columns(20) := pm_column_record('BAG', 'AUTHORITY_TO_LOAD_DCS', 'S');
      g_columns(21) := pm_column_record('BAG', 'BAG_DESTINATION', 'S');
      g_columns(22) := pm_column_record('BAG', 'BAG_EXCEPTION', 'S');
      g_columns(23) := pm_column_record('BAG', 'BAG_SOURCE_INDICATOR', 'S');
      g_columns(24) := pm_column_record('BAG', 'BAG_SOURCE_LOCATION', 'S');
      g_columns(25) := pm_column_record('BAG', 'BAG_SEQUENCE_NO', 'N');
      g_columns(26) := pm_column_record('BAG', 'DCS_BAG_TAG', 'S');
      g_columns(27) := pm_column_record('BAG', 'BAG_TYPE', 'S');
      g_columns(28) := pm_column_record('ONWARD_FLIGHT', 'NO', 'S');
      g_columns(29) := pm_column_record('ONWARD_FLIGHT', 'ODATE', 'S');
      g_columns(30) := pm_column_record('ONWARD_FLIGHT', 'DEST', 'S');
      g_columns(31) := pm_column_record('ONWARD_FLIGHT', 'DEST_ORDER', 'N');
      g_columns(32) := pm_column_record('ONWARD_FLIGHT', 'CLASS', 'S');

    END IF;

  END initialise_columns;


  ------------------------------------------------------------------
  -- validate_column
  --
  FUNCTION validate_column
  (
    p_table_name   IN     pax_match_rules.table_name%TYPE,
    p_column_name  IN     pax_match_rules.column_name%TYPE
  ) RETURN BOOLEAN IS

    l_num          NUMBER;
    l_found        BOOLEAN;

  BEGIN

    -- Make sure columns are defined
    initialise_columns;

    -- Check each column until all checked or column found
    l_num   := 1;
    l_found := FALSE;
    WHILE (NOT l_found AND l_num <= g_columns.COUNT) LOOP

      -- Check for match
      IF (g_columns(l_num).table_name = p_table_name AND g_columns(l_num).column_name = p_column_name) THEN

        -- Found it
        l_found := TRUE;

      ELSE

        -- Not this one
        l_num := l_num + 1;

      END IF;

    END LOOP;

    RETURN l_found;

  END validate_column;

  ------------------------------------------------------------------
  -- load_pax_structure
  --
  PROCEDURE load_pax_structure
  (
    p_structure    IN OUT pm_pax_record,
    p_flight_id    IN     flight.flight_id%TYPE,
    p_pax_id       IN     pax.pax_id%TYPE
  ) IS

    l_direction    flight.flight_type%TYPE;

  BEGIN

    -- Reset the structure
    p_structure := pm_pax_record(NULL, NULL, NULL, NULL, NULL,
                                 NULL, NULL, NULL, NULL, NULL,
                                 NULL, NULL, NULL, NULL, NULL,
                                 NULL, pm_onward_flight_list(), pm_bag_list());

    -- Determine the flight direction
    SELECT flight_type
    INTO   l_direction
    FROM   flight
    WHERE  flight_id = p_flight_id;

    -- Load pax from appropriate tables
    IF (l_direction = 'I') THEN

      -- Inbound
      load_inbound_pax(p_structure, p_pax_id);

    ELSE

      -- Outbound
      load_outbound_pax(p_structure, p_pax_id);

    END IF;

  END load_pax_structure;

  ------------------------------------------------------------------
  -- load_inbound_pax
  --
  PROCEDURE load_inbound_pax
  (
    p_structure    IN OUT pm_pax_record,
    p_pax_id       IN     inbound_pax.inbound_pax_id%TYPE
  ) IS

    CURSOR c_inbound_bags (p_pax_id NUMBER) IS
           SELECT   pm_bag_record(bag_tag,
                    bag_tag2b,
                    bag_tag3,
                    authority_to_load_dcs,
                    bag_destination,
                    bag_exception,
                    bag_source_indicator,
                    bag_source_location,
                    bag_sequence_no,
                    dcs_bag_tag,
                    bag_type)
           FROM     inbound_bag_v1
           WHERE    inbound_pax_id = p_pax_id
           ORDER BY bag_tag2b ASC,
                    bag_tag3 ASC;

    CURSOR c_inbound_onward_flights (p_pax_id NUMBER) IS
           SELECT   pm_onward_flight_record(onward_flight_no,
                    onward_flight_date,
                    onward_destination,
                    destination_sequence,
                    onward_class)
           FROM     inbound_pax_onward_dests
           WHERE    inbound_pax_id = p_pax_id
           ORDER BY destination_sequence ASC;

  BEGIN

    -- Load the Pax Information
    SELECT ip.pax_initials,
           ip.pax_surname,
           ip.no_same_surname,
           ip.pax_destination,
           ip.pax_seat_no,
           ip.pax_class,
           ip.pax_sequence_no,
           ip.pax_security_no,
           ip.pax_name_record,
           ip.departure_airport_code,
           f.flight_no,
           f.flight_date,
           si.airport_code,
           ip.pax_full_inits,
           ip.pax_dcs_sequence_no,
           ip.freq_flyer
    INTO   p_structure.pax_initials,
           p_structure.pax_surname,
           p_structure.no_same_surname,
           p_structure.destination,
           p_structure.seat_no,
           p_structure.class,
           p_structure.pax_sequence_no,
           p_structure.pax_security_no,
           p_structure.pax_name_record,
           p_structure.dep_airport_code,
           p_structure.inbound_flight_no,
           p_structure.inbound_date,
           p_structure.inbound_boarding_point,
           p_structure.pax_full_inits,
           p_structure.pax_dcs_seq_no,
           p_structure.freq_flyer
    FROM   inbound_pax_v1 ip,
           flight f,
           site_info si
    WHERE  ip.inbound_pax_id = p_pax_id
      AND  f.flight_id = ip.inbound_flight_id
      AND  si.site_id = inbound_flight_utils.get_inbound_orig_site(f.flight_id);

    -- Load the Bag Information
    OPEN c_inbound_bags(p_pax_id);
    FETCH c_inbound_bags BULK COLLECT INTO p_structure.bag_list;
    CLOSE c_inbound_bags;

    -- Load the Onward Flight Information
    OPEN c_inbound_onward_flights(p_pax_id);
    FETCH c_inbound_onward_flights BULK COLLECT INTO p_structure.on_flight_list;
    CLOSE c_inbound_onward_flights;

  END load_inbound_pax;

  ------------------------------------------------------------------
  -- load_outbound_pax
  --
  PROCEDURE load_outbound_pax
  (
    p_structure    IN OUT pm_pax_record,
    p_pax_id       IN     pax.pax_id%TYPE
  ) IS

    CURSOR c_bags (p_pax_id NUMBER) IS
           SELECT   pm_bag_record(bag_tag,
                    bag_tag2b,
                    bag_tag3,
                    authority_to_load_dcs,
                    bag_destination,
                    bag_exception,
                    bag_source_indicator,
                    bag_source_location,
                    bag_sequence_no,
                    dcs_bag_tag,
                    bag_type)
           FROM     bag
           WHERE    pax_id = p_pax_id
           ORDER BY bag_tag2b ASC,
                    bag_tag3 ASC;

    CURSOR c_onward_flights (p_pax_id NUMBER) IS
           (
             SELECT pm_onward_flight_record(f.flight_no,
                    f.flight_date,
                    fd.flight_destination,
                    fd.dest_order,
                    p.pax_class)
             FROM   flight f,
                    pax p,
                    flight_dests fd
             WHERE  f.flight_id = p.flight_id
               AND  p.pax_id = p_pax_id
               AND  f.flight_id = fd.flight_id
               AND  fd.dest_order = 1
           UNION ALL
             SELECT pm_onward_flight_record(norm_fno,
                    odate,
                    dest,
                    dest_order + 1,
                    class)
             FROM   onward_flight
             WHERE  pax_id = p_pax_id
           );

  BEGIN

    -- Load the Pax Information
    SELECT pax_initials,
           pax_surname,
           no_same_surname,
           pax_destination,
           pax_seat_no,
           pax_class,
           pax_sequence_no,
           pax_security_no,
           pax_name_record,
           departure_airport_code,
           inbound_flight_no,
           inbound_date,
           inbound_boarding_point,
           pax_full_inits,
           pax_dcs_sequence_no,
           freq_flyer
    INTO   p_structure.pax_initials,
           p_structure.pax_surname,
           p_structure.no_same_surname,
           p_structure.destination,
           p_structure.seat_no,
           p_structure.class,
           p_structure.pax_sequence_no,
           p_structure.pax_security_no,
           p_structure.pax_name_record,
           p_structure.dep_airport_code,
           p_structure.inbound_flight_no,
           p_structure.inbound_date,
           p_structure.inbound_boarding_point,
           p_structure.pax_full_inits,
           p_structure.pax_dcs_seq_no,
           p_structure.freq_flyer
    FROM   pax
    WHERE  pax_id = p_pax_id;

    -- Load the Bag Information
    OPEN c_bags(p_pax_id);
    FETCH c_bags BULK COLLECT INTO p_structure.bag_list;
    CLOSE c_bags;

    -- Load the Onward Flight Information
    OPEN c_onward_flights(p_pax_id);
    FETCH c_onward_flights BULK COLLECT INTO p_structure.on_flight_list;
    CLOSE c_onward_flights;

  END load_outbound_pax;

  ------------------------------------------------------------------
  -- initialise_algorithm
  --
  PROCEDURE initialise_algorithm
  (
    algorithm_name IN     pax_match_algorithms.algorithm_name%TYPE
  ) IS

    CURSOR c_algorithm (alg_name VARCHAR2) IS
           SELECT   pm_rule_record(a.next_rule_linked,
                    r.table_name,
                    r.column_name,
                    r.test_if,
                    r.fatal,
                    r.limit,
                    r.lower_limit,
                    r.upper_limit,
                    r.operator,
                    r.return_on_positive,
                    r.return_on_negative)
           FROM     pax_match_algorithms a,
                    pax_match_rules r
           WHERE    TRIM(a.algorithm_name) = TRIM(alg_name)
             AND    a.rule_id = r.rule_id
           ORDER BY a.sequence_no ASC;

  BEGIN

    -- Check that the columns are configured
    initialise_columns;

    -- Is the correct algorithm already loaded?
    IF (g_algorithm <> algorithm_name OR g_algorithm IS NULL OR g_rules.COUNT = 0) THEN

      -- Load the new algorithm
      g_algorithm := '?';
      g_rules := pm_rule_list();

      OPEN c_algorithm(algorithm_name);
      FETCH c_algorithm BULK COLLECT INTO g_rules;
      CLOSE c_algorithm;

      g_algorithm := algorithm_name;

    END IF;

  END initialise_algorithm;

  ------------------------------------------------------------------
  -- convert_to_string
  --
  FUNCTION convert_to_string
  (
    value          IN     NUMBER
  ) RETURN         VARCHAR2 IS

  BEGIN

    RETURN TRIM(TO_CHAR(value, '99999999999999999999'));

  END convert_to_string;

  ------------------------------------------------------------------
  -- convert_date_to_string
  --
  FUNCTION convert_date_to_string
  (
    value          IN     DATE
  ) RETURN         VARCHAR2 IS

  BEGIN

    RETURN TRIM(TO_CHAR(value, 'YYYYMMDD'));

  END convert_date_to_string;

  ------------------------------------------------------------------
  -- convert_time_to_string
  --
  FUNCTION convert_time_to_string
  (
    value          IN     DATE
  ) RETURN         VARCHAR2 IS

  BEGIN

    RETURN TRIM(TO_CHAR(value, 'YYYYMMDD HH24:MI:SS'));

  END convert_time_to_string;

  ------------------------------------------------------------------
  -- perform_match
  --
  FUNCTION perform_match
  (
    p_algorithm_name IN     pax_match_algorithms.algorithm_name%TYPE,
    p_pax_structure  IN     pm_pax_record,
    p_flight_id      IN     flight.flight_id%TYPE
  ) RETURN         pax.pax_id%TYPE IS

    l_flight_type    flight.flight_type%TYPE;
    l_check_pax      pm_pax_record;
    l_result         NUMBER;
    l_pax_id         NUMBER;
    l_pax_list       t_pax_list;
    i                NUMBER;
    l_highest        NUMBER;
    l_best_match     NUMBER;

  BEGIN

    -- Initialise the algorithm
    initialise_algorithm(p_algorithm_name);

    -- Clear the table
    g_matched := t_pax_list();
    g_matched_metric := t_pax_list();
    l_pax_list := NULL;

    -- Determine the flight type of the requested flight id
    BEGIN

      SELECT flight_type
      INTO   l_flight_type
      FROM   flight
      WHERE  flight_id = p_flight_id;

    EXCEPTION

      WHEN OTHERS THEN

        RETURN -1;

    END;

    -- Set a cursor variable for the appropriate flight type
    IF (l_flight_type = 'O') THEN

      SELECT            pax_id
      BULK COLLECT INTO l_pax_list
      FROM              pax
      WHERE             flight_id = p_flight_id;

    ELSE

      SELECT            inbound_pax_id
      BULK COLLECT INTO l_pax_list
      FROM              inbound_pax
      WHERE             inbound_flight_id = p_flight_id
        AND             result_pax_status IN ('CREATED','AMENDED','UNPROCESSED');

    END IF;

    -- Begin the loop
    FOR i IN 1 .. l_pax_list.COUNT LOOP

      -- Load the pax into the check pax structure
      load_pax_structure(l_check_pax, p_flight_id, l_pax_list(i));

      -- Perform a single pax match
      l_result := compare_pax(p_pax_structure, l_check_pax);

      -- Is this a good result?
      IF (l_result > 0) THEN

        -- Add to matched list
        g_matched.EXTEND;
        g_matched_metric.EXTEND;
        g_matched(g_matched.COUNT) := l_pax_list(i);
        g_matched_metric(g_matched.COUNT) := l_result;

      END IF;

    END LOOP;

    -- How many matches did we find?
    IF (g_matched.COUNT = 1) THEN

      -- This is perfect - a single match
      RETURN g_matched(1);

    ELSIF (g_matched.COUNT = 0) THEN

      -- No match found
      RETURN 0;

    ELSE

      -- We must have found more than 1 match
      -- Return the first match with the highest metric
      l_highest := 0;
      l_best_match := 0;
      FOR i IN 1 .. g_matched.COUNT LOOP

        IF (g_matched_metric(i) > l_highest) THEN

          l_highest := g_matched_metric(i);
          l_best_match := i;

        END IF;

      END LOOP;

      RETURN g_matched(l_best_match);

    END IF;

  END perform_match;

  ------------------------------------------------------------------
  -- compare_pax
  --
  FUNCTION compare_pax
  (
    source_pax     IN     pm_pax_record,
    check_pax      IN     pm_pax_record
  ) RETURN         NUMBER IS

    l_rule_ignored BOOLEAN;
    l_test_source  t_string_list;
    l_test_check   t_string_list;
    l_equal        BOOLEAN;
    l_result       NUMBER;
    i              NUMBER;
    j              NUMBER;
    l_source_pax_values t_string_list;
    l_check_pax_values  t_string_list;
    l_source_null  BOOLEAN;
    l_check_null   BOOLEAN;
    l_field_type   VARCHAR2(1);

  BEGIN

    -- Initialise
    l_rule_ignored := FALSE;
    l_test_source := t_string_list();
    l_test_check := t_string_list();
    l_result := 0;

    -- Loop for each rule in sequence
    FOR i IN 1 .. g_rules.COUNT LOOP

      -- Check whether linked rule and ignore rule set
      IF (l_rule_ignored = FALSE) THEN

        -- Get the source pax values for this rule column
        get_pax_values(source_pax, g_rules(i).table_name, g_rules(i).column_name, l_source_pax_values);

        -- Get the check pax values for this rule column
        get_pax_values(check_pax, g_rules(i).table_name, g_rules(i).column_name, l_check_pax_values);

        -- Check whether NULL condition is met
        l_source_null := FALSE;
        IF (l_source_pax_values.COUNT = 0 OR (l_source_pax_values.COUNT = 1 AND l_source_pax_values(1) IS NULL)) THEN

          l_source_null := TRUE;

        END IF;
        l_check_null := FALSE;
        IF (l_check_pax_values.COUNT = 0 OR (l_check_pax_values.COUNT = 1 AND l_check_pax_values(1) IS NULL)) THEN

          l_check_null := TRUE;

        END IF;
        IF ( (g_rules(i).test_if = 'EITHER' AND (l_source_null = TRUE AND l_check_null = TRUE))
          OR (g_rules(i).test_if = 'BOTH' AND (l_source_null = TRUE OR l_check_null = TRUE)) ) THEN

          -- NULL Check Failed
          l_rule_ignored := TRUE;

        END IF;

        -- Are limits defined?
        IF (g_rules(i).limit = 'Y' AND l_rule_ignored = FALSE) THEN

          -- Check limits
          l_field_type := get_column_type(g_rules(i).table_name, g_rules(i).column_name);
          IF (check_limits(l_field_type, g_rules(i).lower_limit, g_rules(i).upper_limit, l_source_pax_values) = FALSE
           OR check_limits(l_field_type, g_rules(i).lower_limit, g_rules(i).upper_limit, l_check_pax_values) = FALSE) THEN

            -- Failed Limit checks
            l_rule_ignored := TRUE;

          END IF;

        END IF;

        IF (l_rule_ignored = FALSE) THEN

          -- Add values to test strings
          IF (l_test_source.COUNT = 0 AND l_source_pax_values.COUNT > 0) THEN

            l_test_source.EXTEND(l_source_pax_values.COUNT);

          END IF;
          IF (l_test_check.COUNT = 0 AND l_check_pax_values.COUNT > 0) THEN

            l_test_check.EXTEND(l_check_pax_values.COUNT);

          END IF;

          FOR j IN 1 .. l_source_pax_values.COUNT LOOP

            l_test_source(j) := l_test_source(j) || '***' || l_source_pax_values(j);

          END LOOP;

          FOR j IN 1 .. l_check_pax_values.COUNT LOOP

            l_test_check(j) := l_test_check(j) || '***' || l_check_pax_values(j);

          END LOOP;

          -- Don't perform next step for linked rules
          IF (g_rules(i).next_rule_linked = 'N') THEN

            -- Compare test strings
            l_equal := compare_string(g_rules(i).operator, l_test_source, l_test_check);

            IF (l_equal = FALSE AND g_rules(i).fatal = 'Y') THEN

              RETURN g_rules(i).return_on_negative;

            END IF;

            IF (l_equal = FALSE) THEN

              l_result := l_result + g_rules(i).return_on_negative;

            ELSE

              l_result := l_result + g_rules(i).return_on_positive;

            END IF;

          END IF;

        END IF;

      END IF;

      -- Check whether to ignore next rule
      IF (g_rules(i).next_rule_linked = 'N') THEN

        -- Reset values
        l_test_source := t_string_list();
        l_test_check := t_string_list();
        l_rule_ignored := FALSE;

      END IF;

    END LOOP;

    RETURN l_result;

  END compare_pax;

  ------------------------------------------------------------------
  -- get_pax_values
  --
  PROCEDURE get_pax_values
  (
    p_pax          IN     pm_pax_record,
    p_table_name   IN     pax_match_rules.table_name%TYPE,
    p_column_name  IN     pax_match_rules.column_name%TYPE,
    p_values       IN OUT t_string_list
  ) IS

    i              NUMBER;
    l_full_bag_tag db_config.db_value%TYPE;

  BEGIN

    -- Initialise
    p_values := t_string_list();

    -- Determine which table
    IF (p_table_name = 'PAX') THEN

      p_values.EXTEND(1);

      -- Determine which column of the PAX table
      IF (p_column_name = 'PAX_INITIALS') THEN

        p_values(1) := p_pax.pax_initials;

      ELSIF (p_column_name = 'PAX_SURNAME') THEN

        p_values(1) := p_pax.pax_surname;

      ELSIF (p_column_name = 'NO_SAME_SURNAME') THEN

        p_values(1) := convert_to_string(p_pax.no_same_surname);

      ELSIF (p_column_name = 'PAX_DESTINATION') THEN

        p_values(1) := p_pax.destination;

      ELSIF (p_column_name = 'PAX_SEAT_NO') THEN

        p_values(1) := p_pax.seat_no;

      ELSIF (p_column_name = 'PAX_CLASS') THEN

        p_values(1) := p_pax.class;

      ELSIF (p_column_name = 'PAX_SEQUENCE_NO') THEN

        p_values(1) := convert_to_string(p_pax.pax_sequence_no);

      ELSIF (p_column_name = 'PAX_SECURITY_NO') THEN

        p_values(1) := p_pax.pax_security_no;

      ELSIF (p_column_name = 'PAX_NAME_RECORD') THEN

        p_values(1) := p_pax.pax_name_record;

      ELSIF (p_column_name = 'DEPARTURE_AIRPORT_CODE') THEN

        p_values(1) := p_pax.dep_airport_code;

      ELSIF (p_column_name = 'INBOUND_FLIGHT_NO') THEN

        p_values(1) := p_pax.inbound_flight_no;

      ELSIF (p_column_name = 'INBOUND_DATE') THEN

        p_values(1) := p_pax.inbound_date;

      ELSIF (p_column_name = 'INBOUND_BOARDING_POINT') THEN

        p_values(1) := p_pax.inbound_boarding_point;

      ELSIF (p_column_name = 'PAX_FULL_INITS') THEN

        p_values(1) := p_pax.pax_full_inits;

      ELSIF (p_column_name = 'PAX_DCS_SEQUENCE_NO') THEN

        p_values(1) := p_pax.pax_dcs_seq_no;

      ELSIF (p_column_name = 'FREQ_FLYER') THEN

        p_values(1) := p_pax.freq_flyer;

      ELSE

        RAISE invalid_column;

      END IF;

    ELSIF (p_table_name = 'BAG') THEN

      p_values.EXTEND(p_pax.bag_list.COUNT);

      l_full_bag_tag := config.get_value('COMPARE_FULL_TAG');

      FOR i IN 1 .. p_pax.bag_list.COUNT LOOP

        -- Determine which column of the BAG table
        IF (p_column_name = 'BAG_TAG') THEN

          -- Check whether full tag check required
          IF (l_full_bag_tag = 'Y') THEN

            p_values(i) := p_pax.bag_list(i).bag_tag;

          ELSE

            p_values(i) := SUBSTR(p_pax.bag_list(i).bag_tag, 2);

          END IF;

        ELSIF (p_column_name = 'BAG_TAG2B') THEN

          p_values(i) := p_pax.bag_list(i).bag_tag2b;

        ELSIF (p_column_name = 'BAG_TAG3') THEN

          p_values(i) := convert_to_string(p_pax.bag_list(i).bag_tag3);

        ELSIF (p_column_name = 'AUTHORITY_TO_LOAD_DCS') THEN

          p_values(i) := p_pax.bag_list(i).atl_dcs;

        ELSIF (p_column_name = 'BAG_DESTINATION') THEN

          p_values(i) := p_pax.bag_list(i).destination;

        ELSIF (p_column_name = 'BAG_EXCEPTION') THEN

          p_values(i) := p_pax.bag_list(i).bag_except;

        ELSIF (p_column_name = 'BAG_SOURCE_INDICATOR') THEN

          p_values(i) := p_pax.bag_list(i).bsi;

        ELSIF (p_column_name = 'BAG_SOURCE_LOCATION') THEN

          p_values(i) := p_pax.bag_list(i).src_location;

        ELSIF (p_column_name = 'BAG_SEQUENCE_NO') THEN

          p_values(i) := convert_to_string(p_pax.bag_list(i).bag_seq_no);

        ELSIF (p_column_name = 'DCS_BAG_TAG') THEN

          -- Check whether full tag check required
          IF (l_full_bag_tag = 'Y') THEN

            p_values(i) := p_pax.bag_list(i).dcs_bag_tag;

          ELSE

            p_values(i) := SUBSTR(p_pax.bag_list(i).dcs_bag_tag, 2);

          END IF;

        ELSIF (p_column_name = 'BAG_TYPE') THEN

          p_values(i) := p_pax.bag_list(i).bag_type;

        ELSE

          RAISE invalid_column;

        END IF;

      END LOOP;

    ELSIF (p_table_name = 'ONWARD_FLIGHT') THEN

      p_values.EXTEND(p_pax.on_flight_list.COUNT);

      FOR i IN 1 .. p_pax.on_flight_list.COUNT LOOP

        -- Determine which column of the ONWARD_FLIGHT table
        IF (p_column_name = 'NO') THEN

          p_values(i) := p_pax.on_flight_list(i).on_flight_no;

        ELSIF (p_column_name = 'ODATE') THEN

          p_values(i) := p_pax.on_flight_list(i).on_flight_date;

        ELSIF (p_column_name = 'DEST') THEN

          p_values(i) := p_pax.on_flight_list(i).on_flight_dest;

        ELSIF (p_column_name = 'DEST_ORDER') THEN

          p_values(i) := convert_to_string(p_pax.on_flight_list(i).on_dest_order);

        ELSIF (p_column_name = 'CLASS') THEN

          p_values(i) := p_pax.on_flight_list(i).on_class;

        ELSE

          RAISE invalid_column;

        END IF;

      END LOOP;

    ELSE

      RAISE invalid_table;

    END IF;

  END get_pax_values;

  ------------------------------------------------------------------
  -- check_limits
  --
  FUNCTION check_limits
  (
    p_field_type   IN     VARCHAR2,
    p_lower_limit  IN     pax_match_rules.lower_limit%TYPE,
    p_upper_limit  IN     pax_match_rules.upper_limit%TYPE,
    p_values       IN     t_string_list
  ) RETURN         BOOLEAN IS

    l_result       BOOLEAN := TRUE;
    i              NUMBER;

  BEGIN

    FOR i IN 1 .. p_values.COUNT LOOP

      IF (p_field_type = 'N') THEN

        IF (TO_NUMBER(p_values(i)) NOT BETWEEN TO_NUMBER(p_lower_limit) AND TO_NUMBER(p_upper_limit)
            AND TO_NUMBER(p_values(i)) IS NOT NULL) THEN

          l_result := FALSE;

        END IF;

      ELSIF (p_field_type = 'D') THEN

        IF (TO_DATE(p_values(i), 'YYYYMMDD') NOT BETWEEN TO_DATE(p_lower_limit, 'YYYYMMDD') AND TO_DATE(p_upper_limit, 'YYYYMMDD')
            AND TO_DATE(p_values(i), 'YYYYMMDD') IS NOT NULL) THEN

          l_result := FALSE;

        END IF;

      ELSIF (p_field_type = 'T') THEN

        IF (TO_DATE(p_values(i), 'YYYYMMDD HH24:MI:SS') NOT BETWEEN TO_DATE(p_lower_limit, 'YYYYMMDD HH24:MI:SS') AND TO_DATE(p_upper_limit, 'YYYYMMDD HH24:MI:SS')
            AND TO_DATE(p_values(i), 'YYYYMMDD HH24:MI:SS') IS NOT NULL) THEN

          l_result := FALSE;

        END IF;

      ELSE

        IF (p_values(i) NOT BETWEEN p_lower_limit AND p_upper_limit AND p_values(i) IS NOT NULL) THEN

          l_result := FALSE;

        END IF;

      END IF;

    END LOOP;

    RETURN l_result;

  END check_limits;

  ------------------------------------------------------------------
  -- compare_string
  --
  FUNCTION compare_string
  (
    p_operator     IN     pax_match_rules.operator%TYPE,
    p_source       IN     t_string_list,
    p_check        IN     t_string_list
  ) RETURN BOOLEAN IS

    l_result       BOOLEAN := FALSE;
    i              NUMBER;
    l_source_num   NUMBER;
    l_check_num    NUMBER;

  BEGIN

    IF (p_operator = 'E') THEN

      -- EQUALITY
      -- Both sets contain exactly one row and they are equal

      IF ( (p_source.COUNT = 1 AND p_check.COUNT = 1 AND p_source(1) = p_check(1))
        OR (p_source.COUNT = 0 AND p_check.COUNT = 0) ) THEN

        l_result := TRUE;

      END IF;

    ELSIF (p_operator = 'I') THEN

      -- IDENTICAL
      -- Both sets contains exactly the same number of rows and each corresponding row is equal

      IF (p_source.COUNT = p_check.COUNT) THEN

        l_result := TRUE;

        FOR i IN 1 .. p_source.COUNT LOOP

          IF (p_source(i) <> p_check(i)) THEN

            l_result := FALSE;

          END IF;

        END LOOP;

      END IF;

    ELSIF (p_operator = 'C') THEN

      -- CONTAINED IN
      -- Every row in the check set exists in the source set

      IF (p_source.COUNT >= 1 AND p_check.COUNT >= 1) THEN

        l_source_num := 1;
        l_check_num := 1;

        WHILE (l_source_num <= p_source.COUNT AND l_check_num <= p_check.COUNT) LOOP

          IF (p_source(l_source_num) <> p_check(l_check_num)) THEN

            l_source_num := l_source_num + 1;

          ELSE

            l_check_num := l_check_num + 1;

          END IF;

        END LOOP;

        IF (l_check_num > p_check.COUNT) THEN

          l_result := TRUE;

        END IF;

      ELSIF (p_source.COUNT >= p_check.COUNT) THEN

        l_result := TRUE;

      END IF;

    ELSIF (p_operator = 'B') THEN

      -- CONTAINED BY
      -- Every row in the source set exists in the check set

      IF (p_source.COUNT >= 1 AND p_check.COUNT >= 1) THEN

        l_source_num := 1;
        l_check_num := 1;

        WHILE (l_check_num <= p_check.COUNT AND l_source_num <= p_source.COUNT) LOOP

          IF (p_check(l_check_num) <> p_source(l_source_num)) THEN

            l_check_num := l_check_num + 1;

          ELSE

            l_source_num := l_source_num + 1;

          END IF;

        END LOOP;

        IF (l_source_num > p_source.COUNT) THEN

          l_result := TRUE;

        END IF;

      ELSIF (p_check.COUNT >= p_source.COUNT) THEN

        l_result := TRUE;

      END IF;

    ELSIF (p_operator = 'O') THEN

      -- ONE SET CONTAINS THE OTHER
      -- One set is completely contained by the other - i.e. either operator B or operator C is true

      IF (compare_string('C', p_source, p_check) = TRUE OR compare_string('B', p_source, p_check) = TRUE) THEN

        l_result := TRUE;

      END IF;

    ELSIF (p_operator = 'S') THEN

      -- SINGLE ROW EXISTS
      -- At least one row is common to both source and check sets

      IF (p_source.COUNT > 0 AND p_check.COUNT > 0) THEN

        l_source_num := 1;
        l_check_num := 1;

        WHILE (l_source_num <= p_source.COUNT AND l_check_num <= p_check.COUNT AND l_result = FALSE) LOOP

          IF (p_source(l_source_num) = p_check(l_check_num)) THEN

            l_result := TRUE;

          END IF;

          l_check_num := l_check_num + 1;
          IF (l_check_num > p_check.COUNT) THEN

            IF (l_source_num < p_source.COUNT) THEN

              l_source_num := l_source_num + 1;
              l_check_num := 1;

            END IF;

          END IF;

        END LOOP;

      END IF;

    END IF;

    RETURN l_result;

  END compare_string;

  ------------------------------------------------------------------
  -- get_column_type
  --
  FUNCTION get_column_type
  (
    p_table_name   IN     pax_match_rules.table_name%TYPE,
    p_column_name  IN     pax_match_rules.column_name%TYPE
  ) RETURN         VARCHAR2 IS

    l_found        NUMBER;
    i              NUMBER;

  BEGIN

    i := 1;
    l_found := 0;

    WHILE (i <= g_columns.COUNT AND l_found = 0) LOOP

      IF (g_columns(i).table_name = p_table_name AND g_columns(i).column_name = p_column_name) THEN

        l_found := i;

      END IF;

      i := i + 1;

    END LOOP;

    IF (l_found > 0) THEN

      RETURN g_columns(l_found).field_type;

    ELSE

      RETURN 'S';

    END IF;

  END get_column_type;

END pax_match;
/

