/*****************************************************************************
*
* Filename         - $Workfile:   unauthorised_bags.sql   $
*
*                     Name              Date      Version     CA Number
* Author           -  M G C Helliwell 12/09/03      1.0
*
* Project Name     - FALCP.
*
* Source Code
* Identifier       - N/A.
*
* Design Spec
* Section          - FAS/BBTRK/SD/1245.
*
* Contents         - Live Schema Package
*                    to process data in the unauthorised_bag table
*
* Language         - Oracle PL/SQL.
*
* Classification   - Commercial In Confidence
*
*          (c) Ultra Electronics Limited, 2003
*
*****************************************************************************/
PROMPT CREATE OR REPLACE PACKAGE unauthorised_bags

CREATE OR REPLACE
PACKAGE unauthorised_bags AS

FUNCTION ok_to_fly
  (p_flight_id    IN flight.flight_id%TYPE
  ,p_pax_id       IN pax.pax_id%TYPE
  ,p_bag_tag2b    IN bag.bag_tag2b%TYPE
  ,p_bag_tag3     IN bag.bag_tag3%TYPE
  ) RETURN unauthorised_bag.ok_to_fly%TYPE;

FUNCTION count_authorised_bags
  (p_flight_id IN flight.flight_id%TYPE
  ) RETURN PLS_INTEGER;

PROCEDURE add_unauthorised_bag
  (p_bag_tag     IN unauthorised_bag.bag_tag%TYPE
  ,p_bag_tag1    IN unauthorised_bag.bag_tag1%TYPE
  ,p_bag_tag2a   IN unauthorised_bag.bag_tag2a%TYPE
  ,p_bag_tag2b   IN unauthorised_bag.bag_tag2b%TYPE
  ,p_bag_tag3    IN unauthorised_bag.bag_tag3%TYPE
  ,p_flight_id   IN unauthorised_bag.flight_id%TYPE
  ,p_pax_id      IN unauthorised_bag.pax_id%TYPE
  ,p_error_id    IN unauthorised_bag.error_id%TYPE
  ,p_user_id     IN tagtrak_users.user_id%TYPE
  ,p_location    IN unauthorised_bag.location_code%TYPE
  );

PROCEDURE add_unknown_bag
  (p_bag_tag     IN bag.bag_tag%TYPE
  ,p_bag_tag1    IN bag.bag_tag1%TYPE
  ,p_bag_tag2a   IN bag.bag_tag2a%TYPE
  ,p_bag_tag2b   IN bag.bag_tag2b%TYPE
  ,p_bag_tag3    IN bag.bag_tag3%TYPE
  ,p_flight_id   IN flight.flight_id%TYPE
  ,p_pax_id      IN pax.pax_id%TYPE
  ,p_error_id    IN error.error_id%TYPE
  ,p_user_id     IN tagtrak_users.user_id%TYPE
  ,p_bag_group   IN bag_group.group_no%TYPE
  ,p_location    IN unknown_bag.location_code%TYPE
  );

PROCEDURE remove_unauthorised_bag
  (p_pax_id                IN bag.pax_id%TYPE
  ,p_bag_tag2b             IN bag.bag_tag2b%TYPE
  ,p_bag_tag3              IN bag.bag_tag3%TYPE
  );

PROCEDURE maintain_unauthorised_bags
  (p_pax_id                IN bag.pax_id%TYPE
  ,p_bag_tag2b             IN bag.bag_tag2b%TYPE
  ,p_bag_tag3              IN bag.bag_tag3%TYPE
  ,p_authority_to_load_dcs IN bag.authority_to_load_dcs%TYPE
  ,p_delete_marker         IN bag.delete_marker%TYPE
  );

PROCEDURE maintain_unknown_bags
  (p_pax_id                IN bag.pax_id%TYPE
  ,p_bag_tag2b             IN bag.bag_tag2b%TYPE
  ,p_bag_tag3              IN bag.bag_tag3%TYPE
  ,p_authority_to_load_dcs IN bag.authority_to_load_dcs%TYPE
  ,p_delete_marker         IN bag.delete_marker%TYPE
  ,p_group_no              IN bag.group_no%TYPE
  );

PROCEDURE modify_unknown_bag
  (p_bag_tag2b             IN bag.bag_tag2b%TYPE
  ,p_bag_tag3              IN bag.bag_tag3%TYPE
  ,p_old_flight_id         IN flight.flight_id%TYPE
  ,p_new_flight_id         IN flight.flight_id%TYPE
  ,p_pax_inits             IN unknown_bag.pax_full_inits%TYPE
  ,p_pax_surname           IN unknown_bag.pax_surname%TYPE
  ,p_ident_code            IN unknown_bag.bag_ident_code%TYPE
  ,p_scanner_id            IN kit.scanner_id%TYPE
  ,p_success               OUT NUMBER
  );

PROCEDURE MWS_modify_unknown_bag
  ( p_bag_id                 IN unknown_bag.id%TYPE,
    p_flight_id          IN unknown_bag.flight_id%TYPE,
    p_pax_full_inits         IN unknown_bag.pax_full_inits%TYPE,
    p_pax_surname            IN unknown_bag.pax_surname%TYPE,
    p_bag_ident_code         IN unknown_bag.bag_ident_code%TYPE
  );

PROCEDURE MWS_Authorise_To_Load
  (p_user_id  IN  tagtrak_users.user_id%TYPE
  ,p_bag_id   IN  unknown_unauth_bag.id%TYPE
  ,p_bag_type IN  unknown_unauth_bag.unk_unauth%TYPE
  ,p_location IN  bag_trace.location_code%TYPE
  ,p_result   OUT NUMBER
  );

FUNCTION validate_bic
  (p_ident_code IN unknown_bag.bag_ident_code%type
  ) RETURN PLS_INTEGER;

END unauthorised_bags;
/
PROMPT CREATE OR REPLACE PACKAGE BODY unauthorised_bags

CREATE OR REPLACE
PACKAGE BODY unauthorised_bags AS

-- Determine whether this bag is now ok to fly
-- i.e. the bag was previously unauthorised (but ATL DCS now received) OR
--      the bag was previously unknown (but BSM has now been received for it)

FUNCTION ok_to_fly
  (p_flight_id    IN flight.flight_id%TYPE
  ,p_pax_id       IN pax.pax_id%TYPE
  ,p_bag_tag2b    IN bag.bag_tag2b%TYPE
  ,p_bag_tag3     IN bag.bag_tag3%TYPE
  ) RETURN unauthorised_bag.ok_to_fly%TYPE IS
  v_ok_to_fly     unauthorised_bag.ok_to_fly%TYPE;
  BEGIN
    SELECT COUNT(*)
    INTO v_ok_to_fly
    FROM
        (SELECT flight_id, MAX(pax_id), bag_tag2b, bag_tag3
           FROM unauthorised_bag
          WHERE flight_id = p_flight_id
            AND NVL(pax_id,p_pax_id) = p_pax_id
            AND bag_tag2b = p_bag_tag2b
            AND bag_tag3 = p_bag_tag3
            AND ok_to_fly = 1
       GROUP BY flight_id, bag_tag2b, bag_tag3
    UNION
        -- Bags which were unknown (and are not unauthorised)
        SELECT flight_id, MAX(pax_id), bag_tag2b, bag_tag3
          FROM unknown_bag
         WHERE flight_id NOT IN (SELECT flight_id
                         FROM unauthorised_bag
                        WHERE flight_id = p_flight_id
                          AND NVL(pax_id, p_pax_id) = p_pax_id
                          AND bag_tag2b = p_bag_tag2b
                          AND bag_tag3 = p_bag_tag3
                          AND ok_to_fly = 0)
           AND flight_id = p_flight_id
           AND bag_tag2b = p_bag_tag2b
           AND bag_tag3 = p_bag_tag3
           AND ok_to_fly = 1
    GROUP BY flight_id, bag_tag2b, bag_tag3);

    RETURN v_ok_to_fly;
  END ok_to_fly;

-- Get count of authorised bags
-- i.e. total of bags that were previously unauthorised (but ATL DCS now received) AND
--      bags previously unknown (but BSM has now been received for it)

FUNCTION count_authorised_bags
  (p_flight_id IN flight.flight_id%TYPE
  ) RETURN PLS_INTEGER IS
  v_count_authorised_bags      PLS_INTEGER;
  BEGIN
    SELECT COUNT(*)
    INTO v_count_authorised_bags
    FROM
       (SELECT flight_id,MAX(pax_id),bag_tag2b,bag_tag3
          FROM unauthorised_bag
         WHERE ok_to_fly = 1
           AND flight_id = p_flight_id
      GROUP BY flight_id,bag_tag2b,bag_tag3
    UNION
--     bags previously unknown that are not ATL DCS 'N'
       SELECT flight_id,MAX(pax_id),bag_tag2b,bag_tag3
         FROM unknown_bag
        WHERE
              flight_id||pax_id||bag_tag2b||bag_tag3 NOT IN
                       (SELECT flight_id||p.pax_id||bag_tag2b||bag_tag3
                         FROM bag b, pax p
                        WHERE b.authority_to_load_dcs = 'N'
                          AND p.flight_id = p_flight_id
                          AND b.pax_id = p.pax_id)
    AND ok_to_fly = 1
    AND flight_id = p_flight_id
    GROUP BY flight_id,bag_tag2b,bag_tag3
    );

    RETURN v_count_authorised_bags;
  END count_authorised_bags;

PROCEDURE add_unauthorised_bag
  (p_bag_tag     IN unauthorised_bag.bag_tag%TYPE
  ,p_bag_tag1    IN unauthorised_bag.bag_tag1%TYPE
  ,p_bag_tag2a   IN unauthorised_bag.bag_tag2a%TYPE
  ,p_bag_tag2b   IN unauthorised_bag.bag_tag2b%TYPE
  ,p_bag_tag3    IN unauthorised_bag.bag_tag3%TYPE
  ,p_flight_id   IN unauthorised_bag.flight_id%TYPE
  ,p_pax_id      IN unauthorised_bag.pax_id%TYPE
  ,p_error_id    IN unauthorised_bag.error_id%TYPE
  ,p_user_id     IN tagtrak_users.user_id%TYPE
  ,p_location    IN unauthorised_bag.location_code%TYPE
  ) AS
  BEGIN
    INSERT INTO unauthorised_bag
      (id
      ,bag_tag
      ,bag_tag1
      ,bag_tag2a
      ,bag_tag2b
      ,bag_tag3
      ,flight_id
      ,pax_id
      ,error_id
      ,timestamp
      ,user_id
      ,location_code
      ) VALUES
      (unb_id_s.NEXTVAL
      ,p_bag_tag
      ,p_bag_tag1
      ,DECODE(p_bag_tag2a,'-',NULL,p_bag_tag2a)
      ,p_bag_tag2b
      ,p_bag_tag3
      ,p_flight_id
      ,DECODE(p_pax_id,0,NULL,p_pax_id)
      ,p_error_id
      ,SYSDATE + get_offset.airport_code(get_offset.get_user_location(p_user_id))
      ,p_user_id
      ,p_location
      );
  END add_unauthorised_bag;

PROCEDURE add_unknown_bag
  (p_bag_tag     IN bag.bag_tag%TYPE
  ,p_bag_tag1    IN bag.bag_tag1%TYPE
  ,p_bag_tag2a   IN bag.bag_tag2a%TYPE
  ,p_bag_tag2b   IN bag.bag_tag2b%TYPE
  ,p_bag_tag3    IN bag.bag_tag3%TYPE
  ,p_flight_id   IN flight.flight_id%TYPE
  ,p_pax_id      IN pax.pax_id%TYPE
  ,p_error_id    IN error.error_id%TYPE
  ,p_user_id     IN tagtrak_users.user_id%TYPE
  ,p_bag_group   IN bag_group.group_no%TYPE
  ,p_location    IN unknown_bag.location_code%TYPE
  ) AS

  l_bag_tag2a    bag.bag_tag2a%TYPE;
  l_default_used number := 0;
  l_flight_check number := 0;
  l_need_to_insert boolean := TRUE;  -- this is a flag used to indicate whether we already
                                     -- have an unknown bag matching the tag number.

BEGIN

    -- If the unknown bag is being created from the load bag screen then if the
    -- tag number is entered as a bar code number, then the alpha code component
    -- may not be filled in. This will ensure it is always populated.
    IF p_bag_tag2a = '-' THEN
       get_alpha_code(p_bag_tag2b, l_bag_tag2a, l_default_used);
    ELSE
       l_bag_tag2a := p_bag_tag2a;
    END IF;

    -- Are we configured for unknown_bags with flight_id's of 0?
    IF config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y' THEN

       -- Yes, we therefore need to perform a couple of extra checks to find out whether
       -- we have an unknown bag already with a flight ID of 0 and a pax_id of 0.

       SELECT COUNT(*) INTO l_flight_check FROM unknown_bag WHERE
          (bag_tag2a = l_bag_tag2a OR bag_tag2b = p_bag_tag2b) AND
          bag_tag3 = p_bag_tag3 AND
          pax_id = 0 AND
          flight_id = 0;

       IF l_flight_check = 1 THEN

          -- We have found a flight with a flight ID of 0, if we have now been given a
          -- flight ID then we can update it.

          IF p_flight_id != 0 THEN

             -- Go ahead and update the existing UNKNOWN_BAG entry and any existing
             -- tracking logs for that bag.

             UPDATE unknown_bag
                SET flight_id = p_flight_id, pax_id = DECODE(p_pax_id,0,0,p_pax_id)
                WHERE
                   (bag_tag2a = l_bag_tag2a OR bag_tag2b = p_bag_tag2b) AND
                    bag_tag3 = p_bag_tag3 AND
                    pax_id = 0 AND
                    flight_id = 0;

             -- Ensure that any tracking logs already created for this bag are
             -- are associated with this flight

             UPDATE tracking_log
                SET tracking_log.flight_id = p_flight_id,
                    tracking_log.pax_id = p_pax_id
              WHERE tracking_log.pax_id = 0 AND
                   (tracking_log.bag_tag2a = l_bag_tag2a OR tracking_log.bag_tag2b = p_bag_tag2b) AND
                    tracking_log.bag_tag3 = p_bag_tag3 AND
                    tracking_log.flight_id = 0;

          END IF;

          l_need_to_insert := FALSE;

       END IF;

    END IF;

    IF l_need_to_insert = TRUE THEN

       IF config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y' AND p_flight_id = 0 THEN

          -- A flight has not been specified. If any unknown bags exist
          -- which are associated with flights we will not create
          -- another unknown bag

          SELECT COUNT(*) INTO l_flight_check FROM unknown_bag WHERE
             (bag_tag2a = l_bag_tag2a OR bag_tag2b = p_bag_tag2b) AND
              bag_tag3 = p_bag_tag3 AND
              pax_id = 0 AND
              flight_id != 0;
       ELSE

          -- Check whether an unknown bag associated with this flight
          -- already exists

          SELECT COUNT(*) INTO l_flight_check FROM unknown_bag WHERE
             (bag_tag2a = l_bag_tag2a OR bag_tag2b = p_bag_tag2b) AND
              bag_tag3 = p_bag_tag3 AND
              pax_id = 0 AND
              flight_id = p_flight_id;
       END IF;

       IF l_flight_check > 0 THEN

          -- This unknown bag already exists, so no need to create it
          l_need_to_insert := FALSE;

       END IF;

    END IF;

    IF l_need_to_insert = TRUE THEN

       INSERT INTO unknown_bag
         (id
         ,bag_tag
         ,bag_tag1
         ,bag_tag2a
         ,bag_tag2b
         ,bag_tag3
         ,flight_id
         ,pax_id
         ,error_id
         ,timestamp
         ,group_no
         ,user_id
         ,location_code
         ) VALUES
         (unknown_bag_id_s.NEXTVAL
         ,p_bag_tag
         ,p_bag_tag1
         ,l_bag_tag2a
         ,p_bag_tag2b
         ,p_bag_tag3
         ,p_flight_id
         ,DECODE(p_pax_id,0,0,p_pax_id)
         ,p_error_id
         ,SYSDATE + get_offset.airport_code(get_offset.get_user_location(p_user_id))
         ,p_bag_group
         ,p_user_id
         ,p_location
          );

      END IF;

  END add_unknown_bag;


PROCEDURE remove_unauthorised_bag
  (p_pax_id                IN bag.pax_id%TYPE
  ,p_bag_tag2b             IN bag.bag_tag2b%TYPE
  ,p_bag_tag3              IN bag.bag_tag3%TYPE
  ) AS
  BEGIN

    DELETE unauthorised_bag
    WHERE NVL(pax_id,p_pax_id)  = p_pax_id
    AND bag_tag2b = p_bag_tag2b
    AND bag_tag3  = p_bag_tag3
    AND flight_id = (SELECT flight_id
                    FROM pax
                    WHERE pax_id = p_pax_id);

    DELETE unknown_bag
    WHERE NVL(pax_id, p_pax_id)  = p_pax_id
    AND bag_tag2b = p_bag_tag2b
    AND bag_tag3  = p_bag_tag3
    AND flight_id = (SELECT flight_id
                     FROM pax
                     WHERE pax_id = p_pax_id);

  END remove_unauthorised_bag;

PROCEDURE maintain_unauthorised_bags
  (p_pax_id                IN bag.pax_id%TYPE
  ,p_bag_tag2b             IN bag.bag_tag2b%TYPE
  ,p_bag_tag3              IN bag.bag_tag3%TYPE
  ,p_authority_to_load_dcs IN bag.authority_to_load_dcs%TYPE
  ,p_delete_marker         IN bag.delete_marker%TYPE
  ) AS
  BEGIN

   IF (p_delete_marker = 1) THEN

        -- the delete marker was set so we dont want the ok_to_fly flag to indicate that the bag should be loaded.
    	UPDATE unauthorised_bag
    	SET ok_to_fly = 0
    	WHERE NVL(pax_id,p_pax_id)  = p_pax_id
    	AND bag_tag2b = p_bag_tag2b
    	AND bag_tag3  = p_bag_tag3
    	AND flight_id = (SELECT flight_id
                     FROM pax
                     WHERE pax_id = p_pax_id);
   ELSE

        -- the delete marker was not set so continue to update the ok_to_fly flag to indicate that the bag is now known.
        UPDATE unauthorised_bag
        SET ok_to_fly = DECODE(p_authority_to_load_dcs
                              ,'Y', 1
                              ,'N', 0)
        WHERE NVL(pax_id,p_pax_id)  = p_pax_id
        AND bag_tag2b = p_bag_tag2b
        AND bag_tag3  = p_bag_tag3
        AND flight_id = (SELECT flight_id
                        FROM pax
                        WHERE pax_id = p_pax_id);
   END IF;

  END maintain_unauthorised_bags;

PROCEDURE maintain_unknown_bags
  (p_pax_id                IN bag.pax_id%TYPE
  ,p_bag_tag2b             IN bag.bag_tag2b%TYPE
  ,p_bag_tag3              IN bag.bag_tag3%TYPE
  ,p_authority_to_load_dcs IN bag.authority_to_load_dcs%TYPE
  ,p_delete_marker         IN bag.delete_marker%TYPE
  ,p_group_no              IN bag.group_no%TYPE
  ) AS

    l_flight_id            flight.flight_id%TYPE;
    l_flight_id_to_set     flight.flight_id%TYPE;
 BEGIN

   SELECT flight_id INTO l_flight_id FROM pax WHERE pax_id = p_pax_id;

   -- copy the flight ID into the local variable to set it from.
   l_flight_id_to_set := l_flight_id;

   IF config.get_value('ALLOW_BAG_WITH_NOFLT') = 'Y' THEN

      -- If we are allowing unknown bags to have no flight ID then we need to clear the
      -- flight ID as well.
      l_flight_id_to_set := 0;
   END IF;

   -- Check whether the delete marker was set?
   IF (p_delete_marker = 1) THEN

        -- the delete marker was set so we dont want the ok_to_fly flag to indicate that the bag should be loaded.
        -- reset the pax id, flight id and group no
    	UPDATE unknown_bag
    	SET ok_to_fly = 0, pax_id = 0, flight_id = l_flight_id_to_set, group_no = p_group_no
    	WHERE NVL(pax_id,p_pax_id)  = p_pax_id
    	AND bag_tag2b = p_bag_tag2b
    	AND bag_tag3  = p_bag_tag3
    	AND flight_id = l_flight_id;
   ELSE

        -- the delete marker was not set so continue to update the ok_to_fly flag to indicate that the bag is now known.
        -- set the group no to theame as the BAG table
        UPDATE unknown_bag
        SET ok_to_fly = DECODE(p_authority_to_load_dcs
                              ,'Y', 1
                              ,'N', 0),
            group_no  = p_group_no
        WHERE NVL(pax_id,p_pax_id)  = p_pax_id
        AND bag_tag2b = p_bag_tag2b
        AND bag_tag3  = p_bag_tag3
        AND flight_id = l_flight_id;

   END IF;

  END maintain_unknown_bags;


PROCEDURE modify_unknown_bag
  (p_bag_tag2b             IN  bag.bag_tag2b%TYPE
  ,p_bag_tag3              IN  bag.bag_tag3%TYPE
  ,p_old_flight_id         IN  flight.flight_id%TYPE
  ,p_new_flight_id         IN  flight.flight_id%TYPE
  ,p_pax_inits             IN unknown_bag.pax_full_inits%TYPE
  ,p_pax_surname           IN unknown_bag.pax_surname%TYPE
  ,p_ident_code            IN unknown_bag.bag_ident_code%TYPE
  ,p_scanner_id            IN kit.scanner_id%TYPE
  ,p_success               OUT NUMBER
  ) AS
  l_row_count NUMBER;
  l_bic_needed db_config.db_value%type;

  l_site_id site_info.site_id%type;

BEGIN
   l_row_count := 0;

   -- lets get the site_id
   SELECT s.site_id INTO l_site_id FROM site_info s, kit k
	WHERE k.scanner_id = p_scanner_id
	    AND k.airport_code = s.airport_code;

   -- do we want the BIC?
   BEGIN
	SELECT TRIM(db_value) INTO l_bic_needed FROM site_config
	    WHERE db_item = 'USE_BAG_ID_CODES' AND site_id = l_site_id;
   EXCEPTION
	WHEN NO_DATA_FOUND THEN
	    l_bic_needed := 'N';
   END;

   IF l_bic_needed = 'Y' AND p_ident_code IS NULL THEN
       p_success := -20801; -- we need a BIC!
   ELSIF l_bic_needed = 'Y' AND validate_bic(p_ident_code) < 0 
     AND p_ident_code != 'BLANK' THEN
       p_success := -20802; -- BIC is invalid!
   ELSE

       -- Update the pax name if it was set
       IF NVL(p_pax_surname, '?') != '?' THEN
          UPDATE unknown_bag
          SET pax_full_inits = p_pax_inits,
              pax_surname = p_pax_surname
          WHERE bag_tag2b = p_bag_tag2b
            AND bag_tag3  = p_bag_tag3
            AND flight_id = p_old_flight_id;

          l_row_count := SQL%ROWCOUNT;
       END IF;

       -- Update the flight_id if it was set
       IF NVL(p_new_flight_id, 0) != 0 THEN
          UPDATE unknown_bag
          SET flight_id = p_new_flight_id,
              flt_manually_entered = 'Y'
          WHERE bag_tag2b = p_bag_tag2b
            AND bag_tag3  = p_bag_tag3
            AND flight_id = p_old_flight_id;

          l_row_count := SQL%ROWCOUNT;
       END IF;

       -- Update the BIC if it was set
       IF p_ident_code IS NOT NULL AND p_ident_code != 'BLANK' THEN
          UPDATE unknown_bag
          SET bag_ident_code = p_ident_code
          WHERE bag_tag2b = p_bag_tag2b
            AND bag_tag3  = p_bag_tag3
            AND flight_id = p_old_flight_id;

          l_row_count := SQL%ROWCOUNT;
       END IF;

       -- Set p_success flag as appropriate
       IF l_row_count = 0 THEN
          p_success := -502; -- FAIL
       ELSE
          p_success := 0; -- SUCCESS
       END IF;
   END IF;
END modify_unknown_bag;

PROCEDURE MWS_modify_unknown_bag
(
    p_bag_id                 IN unknown_bag.id%TYPE,
    p_flight_id              IN unknown_bag.flight_id%TYPE,
    p_pax_full_inits         IN unknown_bag.pax_full_inits%TYPE,
    p_pax_surname            IN unknown_bag.pax_surname%TYPE,
    p_bag_ident_code         IN unknown_bag.bag_ident_code%TYPE
)
AS

l_db_flight_id unknown_bag.flight_id%TYPE;
l_flight_id unknown_bag.flight_id%TYPE;
l_flt_manually_entered unknown_bag.flt_manually_entered%TYPE;

BEGIN
/*
 * This procedure updates the flight_id, pax_surname and pax_full_inits
 * columns of the UNKNOWN_BAG table. It is only allowed to update the flight_id
 * if the flt_manually_entered flag for the bag is set to 'Y', or if the
 * existing flight_id for the bag is zero.
 */

    l_flight_id := p_flight_id;

    -- Select the existing flight_id and flt_manually_entered flag for the
    -- bag and store them in local variables.
    SELECT flight_id, flt_manually_entered
    INTO l_db_flight_id, l_flt_manually_entered
    FROM unknown_bag
    WHERE id = p_bag_id;

    -- Handle NULL flight ids by setting them to zero.
    IF(l_flight_id IS NULL)
    THEN
        l_flight_id := 0;
    END IF;

    IF(l_flt_manually_entered != 'Y' OR l_flt_manually_entered IS NULL)
    THEN
       IF(l_db_flight_id != 0)
       THEN
           -- flight_id should not be updated - reset the l_flight_id parameter.
           l_flight_id := l_db_flight_id;

       ELSIF (l_flight_id != 0)
       THEN
           -- a non-zero flight_id has been sent - the flight will have been
           -- manually entered.
           l_flt_manually_entered := 'Y';

       END IF;
    END IF;

    UPDATE unknown_bag
    SET flight_id = l_flight_id,
        pax_full_inits = p_pax_full_inits,
        pax_surname = p_pax_surname,
        flt_manually_entered = l_flt_manually_entered,
        bag_ident_code = p_bag_ident_code
    WHERE id = p_bag_id;

END MWS_modify_unknown_bag;

-- FALCP/CA/01979
-- This procedure allows a user to manually authorise a bag, as if the bag
-- had been authorised by a DCS.
--
PROCEDURE MWS_Authorise_To_Load
		  (p_user_id  IN  tagtrak_users.user_id%TYPE
		  ,p_bag_id   IN  unknown_unauth_bag.id%TYPE
		  ,p_bag_type IN  unknown_unauth_bag.unk_unauth%TYPE
		  ,p_location IN  bag_trace.location_code%TYPE
		  ,p_result   OUT NUMBER
		  )
as
    l_unk_unauth	unknown_unauth_bag%rowtype;
    l_status		number := 0;
    l_tag1		bag.bag_tag1%type;
    l_tag2a		bag.bag_tag2a%type;
    l_tag2b		bag.bag_tag2b%type;
    l_tag3		bag.bag_tag3%type;
    l_tagno		bag.bag_tag%type;
    l_tag_type		number;
    l_pax_id		pax.pax_id%TYPE;
    l_process_name	flight.process_name%type;
    l_subunit_name	flight.subunit_name%type;
    l_unit_name		flight.unit_name%type;
    l_dcsid		flight.dcsid%type;
begin

    -- Get data for this unknown bag
    --
    begin
	select * into l_unk_unauth
	    from unknown_unauth_bag
	    where id=p_bag_id and unk_unauth=p_bag_type;

	-- Split the bag tag
	--
	split_tags(l_unk_unauth.bag_tag,
		   l_tag1,
		   l_tag2a,
		   l_tag2b,
		   l_tag3,
		   l_tagno,
		   l_tag_type);

    exception
    	when NO_DATA_FOUND then
	    l_status := 1;
    end;

    -- Handle unknown bags
    --
    if l_status=0 and p_bag_type='K' then
	declare
	    l_unk_bag	unknown_bag%ROWTYPE;
	    l_dest	flight_dests.flight_destination%type;
    	begin

	    -- Fetch the bag from the UNKNOWN_BAG table
	    --
	    begin
		select * into l_unk_bag
		    from unknown_bag
		    where unknown_bag.id = p_bag_id;
	    exception
		when NO_DATA_FOUND then
		    l_status := 1;
	    end;

	    if l_status = 0 then
		l_dest := get_dest(l_unk_unauth.flight_id);

		-- Create a new PAX for this if needed
		--
		if l_unk_bag.pax_id is null or l_unk_bag.pax_id = 0 then
		    declare
			l_seqno	pax.pax_sequence_no%TYPE;
			l_location	site_info.airport_code%TYPE;
			l_onward	onward_flight_defs.onwd_flt_t;
		    begin
			make_sequence_no(l_unk_unauth.flight_id, l_seqno);

			select airport_code into l_location
			    from site_info
			    where site_id =
				(select site_id
				    from flight
				    where flight_id = l_unk_unauth.flight_id);

			-- CA/2064 -- Chhanges to passenger name
			--
			add_pax
			    (fltid		=> l_unk_unauth.flight_id,
			     codeshare_fid	=> null,
			     init		=>
			     		substr(l_unk_bag.pax_full_inits,1,1),
			     surn		=> trim(l_unk_bag.pax_surname),
			     sts		=> 'C',
			     nobags		=> 1,
			     nosurn		=> null,
			     dest		=> l_dest,
			     seat		=> null,
			     class		=> '*',
			     seqno		=> l_seqno,
			     dcsseqno		=> null,
			     secno		=> null,
			     pnr		=> null,
			     secsts		=> null,
			     depair		=> l_location,
			     ifltno		=> null,
			     ifltdate		=> null,
			     ibdg		=> null,
			     atl		=> 'Y',
			     pax_full_inits	=> trim(l_unk_bag.pax_full_inits),
			     onward_transfer	=> null,
			     con_type		=> null,
			     onwd_flt		=> l_onward,
			     freq_flyer		=> null,
			     bag_exception	=> null,
			     embassy		=> null,
			     paxid		=> l_pax_id);
		    end;
		else
		    l_pax_id := l_unk_bag.pax_id;
		end if;

		-- For the new BNS functionality we set the bag to be for the
		-- BAGFLO DCS.
		--
		select process_name, unit_name, subunit_name, dcsid
		    into l_process_name, l_unit_name, l_subunit_name, l_dcsid
			from system_cfg
			    where subunit_name = 'SYM' and process_name = 'DCS';


		-- Create a new BAG for this PAX which is authorised to load by
		-- the MWS.
		--
		-- FALCP/CA/02058 - proc, unit, dcsid and subunit set.
		--
		add_bag(tagno		=> l_unk_unauth.bag_tag,
			tag1		=> l_tag1,
			tag2a		=> l_tag2a,
			tag2b		=> l_tag2b,
			tag3		=> l_tag3,
			pid		=> l_pax_id,
			atldcs		=> 'Y',
			dest		=> l_dest,
			bsi		=> 'L',
			excep		=> null,
			proc		=> l_process_name,
			unit		=> l_unit_name,
			dcsid		=> l_dcsid,
			subunit		=> l_subunit_name,
			dcstagno	=> null,
			uldno		=> null,
			con_type	=> null,
			btype		=> null,
			loc		=> p_location,
			user_id		=> null,
			del_instr	=> null,
			wt_ref		=> null,
			bvis_subhead	=> null,
			auth_source	=> 'MWS');

		-- Update the unknown_bag table
		--
		update unknown_bag
		    set unknown_bag.pax_id = l_pax_id,
			unknown_bag.ok_to_fly = 1
			    where unknown_bag.id = p_bag_id;
	    end if;
    	end;
    end if;

    -- Handle unauthorised bags
    --
    if l_status=0 and p_bag_type='A' then
	declare
	    l_unauth_bag	unauthorised_bag%ROWTYPE;
	    l_bag_data		bag%ROWTYPE;
    	begin

	    -- Fetch the bag from the UNAUTHORISED_BAG table
	    --
	    select * into l_unauth_bag
	    	from unauthorised_bag
		where unauthorised_bag.id = p_bag_id;

	    -- Find the matching bag (there must only be one).
	    --
	    if l_unauth_bag.pax_id is null then
	    	l_status := 1;
	    else
	    	l_pax_id := l_unauth_bag.pax_id;
	    end if;

	    if l_status = 0 then
		declare
		    l_count number;
		begin
		    select count(*) into l_count
		    	from bag
			where bag.bag_tag2b = l_tag2b and
			      bag.bag_tag3 = l_tag3 and
			      bag.pax_id = l_pax_id;

		    if l_count < 1 then
		    	l_status := 1;
		    end if;

		    if l_count > 1 then
		    	l_status := 2;
		    end if;

		exception
		    when NO_DATA_FOUND then
		    	l_status := 1;
		end;
	    end if;

	    -- If status is still zero, we can be sure we have one bag and
	    -- pax that goes with it.  So we can update the database.
	    --
	    if l_status = 0 then
		update unauthorised_bag
		    set unauthorised_bag.ok_to_fly = 1
			    where unauthorised_bag.id = p_bag_id;

	    	update bag
		    set bag.authority_source = 'MWS',
		    	bag.authority_to_load_dcs = 'Y'
			    where bag.bag_tag2b = l_tag2b and
				  bag.bag_tag3 = l_tag3 and
				  bag.pax_id = l_pax_id;
	    end if;
	exception
	    when NO_DATA_FOUND then
	    	l_status := 1;
	end;
    end if;


    -- If we're still OK, log a trace and commit the changes.
    --
    if l_status = 0 then
	declare
	    l_user_id number := p_user_id;
	begin
	    add_bag_trace_record(p_user_id		=> l_user_id,
				 p_location_code	=> p_location,
				 p_error_id		=> 0,
				 p_bagtag		=> l_unk_unauth.bag_tag,
				 p_tag1			=> l_tag1,
				 p_tag2a		=> l_tag2a,
				 p_tag2b		=> l_tag2b,
				 p_tag3			=> l_tag3,
				 p_trace_action		=> 'MWS_ATL',
				 p_scanner_id		=> 0,
				 p_flight_id_scan	=> l_unk_unauth.flight_id,
				 p_paxid		=> l_pax_id);
	    commit;
	end;
    end if;


    -- All done, return the status
    --
    p_result := l_status;

end MWS_Authorise_To_Load;

-- FALCP/CA/3057
-- Validate a supplied Baggage Identification Code (BIC)
-- 7 Character code should be of format CCTTXXX
-- Where CC is a colour code in the BAG_COLOUR_CODES table,
-- TT is a type code in the BAG_TYPE_CODES table, and each
-- X is descriptive element in the BAG_DESCRIPTIVE_ELEMENTS table

-- Returns 0 if code is valid, negative number if invalid
FUNCTION validate_bic
  (p_ident_code IN unknown_bag.bag_ident_code%type
  ) RETURN PLS_INTEGER as

    /* Supplied BIC will be split into these */
    l_colour_code bag_colour_codes.code%type;
    l_type_code NUMBER;
    l_descriptive_element_1 bag_descriptive_elements.code%type;
    l_descriptive_element_2 bag_descriptive_elements.code%type;
    l_descriptive_element_3 bag_descriptive_elements.code%type;
    
    /* These will be used for debugging purposes */ 
    l_temp_colour bag_colour_codes.code%type;
    l_temp_type NUMBER;
    l_temp_element_1 bag_descriptive_elements.code%type;
    l_temp_element_2 bag_descriptive_elements.code%type;
    l_temp_element_3 bag_descriptive_elements.code%type;
    
    l_return PLS_INTEGER;

  BEGIN

  l_return := 0; -- Checks passed

  -- correct length?
  IF (LENGTH(p_ident_code) != 7) THEN
     l_return := -1; -- not 7 chars!
  END IF;

  -- split the BIC
  BEGIN
    l_colour_code := SUBSTR(p_ident_code,1,2);
    l_type_code := SUBSTR(p_ident_code,3,2);
    l_descriptive_element_1 := SUBSTR(p_ident_code,5,1);
    l_descriptive_element_2 := SUBSTR(p_ident_code,6,1);
    l_descriptive_element_3 := SUBSTR(p_ident_code,7,1);
  EXCEPTION
    WHEN OTHERS THEN
      l_return := -2; -- invalid type code (probably not numeric)
  END;

  -- valid colour code?
  BEGIN
    SELECT code INTO l_temp_colour FROM bag_colour_codes
               WHERE code = l_colour_code;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_return := -3; -- invalid colour code
  END;

  -- type code in list?
  BEGIN
    SELECT code INTO l_temp_type FROM bag_type_codes
               WHERE code = l_type_code;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_return := -2; -- invalid type code
  END;

  -- valid descriptive codes?
  BEGIN
    SELECT code INTO l_temp_element_1 FROM bag_descriptive_elements
               WHERE code = l_descriptive_element_1;
    SELECT code INTO l_temp_element_2 FROM bag_descriptive_elements
               WHERE code = l_descriptive_element_2;
    SELECT code INTO l_temp_element_3 FROM bag_descriptive_elements
               WHERE code = l_descriptive_element_3;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_return := -4; -- invalid descriptive code(s)
  END;

  RETURN l_return;

END validate_bic;

END unauthorised_bags;
/
