CREATE OR REPLACE PACKAGE BODY carrier IS

PROCEDURE maintain_carrier (p_airline IN flight.carrier_alpha_code%TYPE)
IS

	mach_carrier_desc1  carrier_map.airline_des1%TYPE;
	mach_carrier_desc2  carrier_map.airline_des2%TYPE;
	mach_alpha_code     carrier_lookup.alpha_code%TYPE;

BEGIN

	--Check if we know this carrier.
	IF carrier.is_known( p_airline ) = FALSE THEN
		/*
		 * We don't know the carrier, so add it.
		 * But first, see if we have a handy airline description
		 * for this carrier in the carrier_map table.
		 */
    carrier.match_carrier(p_airline,
                          mach_alpha_code,
                          mach_carrier_desc1,
                          mach_carrier_desc2);

    IF mach_carrier_desc1 IS NULL THEN
			mach_carrier_desc1 := 'Added for FIDS';
    END IF;

    carrier.add_carrier(p_airline,
                        NULL,
                        mach_carrier_desc1,
                        config.get_value( 'DEF_TAG_CREW_BAGS' ),
                        config.get_value( 'DEF_PAX_CREDENTIAL' ),
                        1);

  END IF;

END maintain_carrier;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

FUNCTION is_known (p_airline IN carrier_lookup.alpha_code%TYPE)
RETURN BOOLEAN IS

	l_found  NUMBER;

BEGIN

	SELECT 1
	INTO l_found
	FROM carrier_lookup
	WHERE alpha_code = p_airline;

	RETURN TRUE;

EXCEPTION WHEN no_data_found THEN
  RETURN FALSE;

END is_known;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE match_carrier (p_carrier_in   IN     carrier_lookup.alpha_code%TYPE,
                         p_carrier_out     OUT carrier_lookup.alpha_code%TYPE,
                         p_desc_1          OUT carrier_map.airline_des1%TYPE,
                         p_desc_2          OUT carrier_map.airline_des2%TYPE) IS

  l_found NUMBER := 0;

BEGIN

	/*
	 * Attempt to find a single matching carrier_map record
	 * to provide an alternative airline code along with
	 * descriptions.
	 */

	FOR c IN (SELECT airline_code1,
                   airline_code2,
                   airline_des1,
                   airline_des2
            FROM carrier_map
            WHERE airline_code1 = p_carrier_in
            OR airline_code2 = p_carrier_in) LOOP

    IF ( l_found > 0 ) THEN
      raise_application_error(-20038, 'Conflicting carrier codes in carrier_map');
    END IF;

    IF c.airline_code1 != p_carrier_in THEN
      p_carrier_out := c.airline_code1;
    ELSE
      p_carrier_out := c.airline_code2;
    END IF;

		p_desc_1 := c.airline_des1;
		p_desc_2 := c.airline_des2;

		l_found := l_found + 1;

	END LOOP;

	/*
	 * If no matching carrier was found, return the original
	 * airline code, with descriptions set to NULL.
	 */

	IF ( l_found = 0 ) THEN
		p_carrier_out := p_carrier_in;
		p_desc_1 := NULL;
		p_desc_2 := NULL;
	END IF;

END match_carrier;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE add_carrier (p_alpha_code     IN carrier_lookup.alpha_code%TYPE,
                       p_bar_code       IN carrier_lookup.bar_code%TYPE,
                       p_carrier        IN carrier_lookup.carrier%TYPE,
                       p_tag_crew_bags  IN carrier_lookup.tag_crew_bags%TYPE,
                       p_pax_cred       IN carrier_lookup.pax_cred%TYPE,
                       p_uid            IN tagtrak_users.user_id%type) IS

	l_default_agent   db_config.db_value%TYPE;

BEGIN

	INSERT INTO carrier_lookup (
		alpha_code,
		bar_code,
		carrier,
		tag_crew_bags,
		pax_cred,
		last_user
	) VALUES (
		p_alpha_code,
		p_bar_code,
		nvl(rtrim(p_carrier),'Unknown'),
		nvl(rtrim(p_tag_crew_bags),'Y'),
		nvl(rtrim(p_pax_cred),'SEQUENCE_NO'),
		p_uid
	);

	/* We need to set up the contracted agent for the new airline
	   (otherwise no users would be able to access their flights) */
	l_default_agent := config.get_value('DEF_AGENT_CODE');

	IF l_default_agent IS NOT NULL THEN

    INSERT INTO contracted_agents
    (airline_alpha_code, agent_code, last_user, archive_flag)
    VALUES
    (p_alpha_code, rtrim(l_default_agent), 1, 1);

  END IF;

END add_carrier;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE translate_carrier (p_carrier_in  IN     carrier_lookup.alpha_code%TYPE,
                             p_carrier_out    OUT carrier_lookup.alpha_code%TYPE) IS

  l_found NUMBER := 0;

BEGIN

	/* Attempt to find a single matching carrier_map record to provide an alternative
     airline code. This is for carrier translation so we adopt the following behaviour:
	   If the original code is found in airline_code1, it should be translated to the value in
     airline_code2. In any other case, no translation will be made.*/
	FOR c IN (SELECT airline_code2
            FROM carrier_map
            WHERE airline_code1 = p_carrier_in) LOOP

    IF ( l_found > 0 ) THEN
      raise_application_error(-20038, 'Conflicting carrier codes in carrier_map');
    END IF;

    p_carrier_out := c.airline_code2;

    l_found := l_found + 1;

  END LOOP;

	/*If no matching carrier was found, return the original airline code, with descriptions
    set to NULL. */
	IF ( l_found = 0 ) THEN
    p_carrier_out := p_carrier_in;
  END IF;

END translate_carrier;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
-- Get alpha code from CARRIER_LOOKUP. Will translate from 3 character to 2 character if
--prefered or send back the 3 character code.
FUNCTION preferred_alpha_code (p_3letter_alpha_code three_letter_carrier_codes.three_letter_carrier_code%TYPE)
RETURN CHAR IS

  l_2letter_alpha_code  three_letter_carrier_codes.two_letter_carrier_code%TYPE;
  l_prefer_3letter      three_letter_carrier_codes.prefer_3letter%TYPE;

BEGIN

  -- Try and get the two letter alpha code
  SELECT a.two_letter_carrier_code,
         a.prefer_3letter
  INTO l_2letter_alpha_code,
       l_prefer_3letter
  FROM three_letter_carrier_codes a
  WHERE three_letter_carrier_code = p_3letter_alpha_code;

  IF l_prefer_3letter = 'N' THEN
    RETURN l_2letter_alpha_code; -- Otherwise return the two letter alpha code
  ELSE
    RETURN p_3letter_alpha_code; -- Return the parameter value if the "prefered" flag is set to "N"
  END IF;

EXCEPTION
  WHEN NO_DATA_FOUND THEN -- Return the parameter value if no row is found
    RETURN p_3letter_alpha_code;

END preferred_alpha_code;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE add_carrier_from_mws(p_alpha_code        carrier_lookup.alpha_code%TYPE,
                               p_bar_code          carrier_lookup.bar_code%TYPE,
                               p_carrier           carrier_lookup.carrier%TYPE,
                               p_tag_crew_bags     carrier_lookup.tag_crew_bags%TYPE,
                               p_pax_cred          carrier_lookup.pax_cred%TYPE,
                               p_pax_unique        carrier_lookup.pax_unique%TYPE,
                               p_dcs_boarding      carrier_lookup.dcs_boarding%TYPE,
                               p_board_match_using carrier_lookup.board_match_using%TYPE,
                               p_precision_loading carrier_lookup.precision_loading%TYPE,
                               p_selectee_pax      carrier_lookup.selectee_pax%TYPE,
                               p_handle_dupes      carrier_lookup.handle_dupes%TYPE,
                               p_stdby_pax_unq     carrier_lookup.stdby_pax_unique_key%TYPE,
                               p_chkdin_pax_unq    carrier_lookup.not_chkdin_pax_unique_key%TYPE,
                               p_bpm_groups        carrier_lookup.bpm_groups%TYPE,
                               p_bpm_exceptions    carrier_lookup.bpm_exceptions%TYPE,
                               p_user_id           carrier_lookup.last_user%TYPE) IS

BEGIN

  INSERT INTO carrier_lookup
  (alpha_code,
   bar_code,
   carrier,
   last_user,
   tag_crew_bags,
   pax_cred,
   pax_unique,
   dcs_boarding,
   board_match_using,
   precision_loading,
   selectee_pax,
   handle_dupes,
   stdby_pax_unique_key,
   not_chkdin_pax_unique_key,
   bpm_groups,
   bpm_exceptions)
  VALUES
  (p_alpha_code,
   p_bar_code,
   p_carrier,
   p_user_id,
   nvl(p_tag_crew_bags, config.get_value( 'DEF_TAG_CREW_BAGS' )),
   nvl(p_pax_cred, config.get_value( 'DEF_PAX_CREDENTIAL' )),
   nvl(p_pax_unique, 'N'),
   nvl(p_dcs_boarding, 'Y'),
   nvl(p_board_match_using, 'SEQUENCE_NO'),
   nvl(p_precision_loading, 'N'),
   nvl(p_selectee_pax, 'N'),
   nvl(p_handle_dupes, 'N'),
   nvl(p_stdby_pax_unq, 'UNKNOWN'),
   nvl(p_chkdin_pax_unq, 'UNKNOWN'),
   p_bpm_groups,
   p_bpm_exceptions);

END add_carrier_from_mws;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE update_carrier(p_alpha_code        carrier_lookup.alpha_code%TYPE,
                         p_bar_code          carrier_lookup.bar_code%TYPE,
                         p_carrier           carrier_lookup.carrier%TYPE,
                         p_tag_crew_bags     carrier_lookup.tag_crew_bags%TYPE,
                         p_pax_cred          carrier_lookup.pax_cred%TYPE,
                         p_pax_unique        carrier_lookup.pax_unique%TYPE,
                         p_dcs_boarding      carrier_lookup.dcs_boarding%TYPE,
                         p_board_match_using carrier_lookup.board_match_using%TYPE,
                         p_precision_loading carrier_lookup.precision_loading%TYPE,
                         p_selectee_pax      carrier_lookup.selectee_pax%TYPE,
                         p_handle_dupes      carrier_lookup.handle_dupes%TYPE,
                         p_stdby_pax_unq     carrier_lookup.stdby_pax_unique_key%TYPE,
                         p_chkdin_pax_unq    carrier_lookup.not_chkdin_pax_unique_key%TYPE,
                         p_bpm_groups        carrier_lookup.bpm_groups%TYPE,
                         p_bpm_exceptions    carrier_lookup.bpm_exceptions%TYPE,
                         p_user_id           carrier_lookup.last_user%TYPE) IS
BEGIN

  UPDATE carrier_lookup
  SET bar_code = p_bar_code,
      carrier = p_carrier,
      tag_crew_bags = nvl(p_tag_crew_bags, config.get_value( 'DEF_TAG_CREW_BAGS' )),
      pax_cred = nvl(p_pax_cred, config.get_value( 'DEF_PAX_CREDENTIAL' )),
      pax_unique = nvl(p_pax_unique, 'N'),
      dcs_boarding = nvl(p_dcs_boarding, 'Y'),
      board_match_using = nvl(p_board_match_using, 'SEQUENCE_NO'),
      precision_loading = nvl(p_precision_loading, 'N'),
      selectee_pax = nvl(p_selectee_pax, 'N'),
      handle_dupes = nvl(p_handle_dupes, 'N'),
      stdby_pax_unique_key = nvl(p_stdby_pax_unq, 'UNKNOWN'),
      not_chkdin_pax_unique_key = nvl(p_chkdin_pax_unq, 'UNKNOWN'),
      bpm_groups = p_bpm_groups,
      bpm_exceptions = p_bpm_exceptions,
      last_user = p_user_id
  WHERE alpha_code = p_alpha_code;

END update_carrier;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE delete_carrier(p_alpha_code        carrier_lookup.alpha_code%TYPE) IS
BEGIN

  DELETE three_letter_carrier_codes
  WHERE two_letter_carrier_code = p_alpha_code;

  DELETE carrier_lookup
  WHERE alpha_code = p_alpha_code;

END delete_carrier;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE add_or_update_three_letter(p_alpha_code        three_letter_carrier_codes.two_letter_carrier_code%TYPE,
                                     p_three_letter_code three_letter_carrier_codes.three_letter_carrier_code%TYPE,
                                     p_prefer_3letter    three_letter_carrier_codes.prefer_3letter%TYPE) IS
BEGIN

  INSERT INTO three_letter_carrier_codes
  (three_letter_carrier_code, two_letter_carrier_code, prefer_3letter)
  VALUES
  (p_three_letter_code,       p_alpha_code,            nvl(p_prefer_3letter, 'N'));

EXCEPTION WHEN dup_val_on_index THEN

  UPDATE three_letter_carrier_codes
  SET prefer_3letter = p_prefer_3letter
  WHERE three_letter_carrier_code = p_three_letter_code;

END add_or_update_three_letter;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

PROCEDURE delete_three_letter_code(p_three_letter_code three_letter_carrier_codes.three_letter_carrier_code%TYPE)
IS
BEGIN

  DELETE three_letter_carrier_codes
  WHERE three_letter_carrier_code = p_three_letter_code;
  
END delete_three_letter_code;

----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------

END carrier;
/