CREATE OR REPLACE PACKAGE carrier
IS

/******************************************************************************
  PROCEDURE maintain_carrier
 
  Description:
    
    Checks to see if the carrier is known using is_known function.
    If it isn't then it uses mtach_carrier to try and find a description before
    creating the carrier using add_carrier.
    
  Parameters:
    
    p_airline  - The airline alpha code
    
******************************************************************************/
PROCEDURE maintain_carrier (p_airline IN flight.carrier_alpha_code%TYPE);

/******************************************************************************
  FUNCTION is_known
 
  Description:
    
    Returns TRUE if the carrier exists in the carrier_lookup table.
    Otherwise returns FALSE.
    
  Parameters:
    
    p_airline  - The airline alpha code
    
******************************************************************************/
FUNCTION is_known (p_airline IN carrier_lookup.alpha_code%TYPE)
RETURN BOOLEAN;

/******************************************************************************
  PROCEDURE add_carrier
 
  Description:
    
    Creates a carrier in the carrier_lookup table adds a record to contracted_agents
    For that carrier using the default agent defined in db_config.
    
  Parameters:
    
    p_alpha_code     - alpha code of carrier
    p_bar_code       - bar code of carrier
    p_carrier        - description of carrier
    p_tag_crew_bags  - flag to indicate whether or not crew bags are tagged
    p_pax_cred       - pax credentials used.
    p_uid            - used id of user creating the record.
    
******************************************************************************/
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);

/******************************************************************************
  PROCEDURE match_carrier
 
  Description:
    
    Attempts to find a single match in carrier_map for the passed in carrier.
    
  Parameters:
    
    p_carrier_in     - alpha code of carrier
  
  Out Parameters:
    p_carrier_out    - corresponding carrier code from carrier_map
    p_desc_1         - description 1 from carrier_map
    p_desc_2         - description 2 from carrier_map
    
  Errors raised:
    -20038 if more than one match found.
******************************************************************************/
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);

/******************************************************************************
  PROCEDURE translate_carrier
 
  Description:
    
    Attempts to find a single match in carrier_map for the passed in carrier
    matching against carrier1 only (match_carrier above matches against
    carrier1 and carrier2).
    
  Parameters:
    
    p_carrier_in     - alpha code of carrier
  
  Out Parameters:
    p_carrier_out    - corresponding carrier code from carrier_map
    
  Errors raised:
    -20038 if more than one match found.
******************************************************************************/
PROCEDURE translate_carrier (p_carrier_in  IN     carrier_lookup.alpha_code%TYPE,
                             p_carrier_out    OUT carrier_lookup.alpha_code%TYPE);


/******************************************************************************
  FUNCTION preferred_alpha_code
 
  Description:
    
    Returns the passed in three_letter_alpha_code from three_letter_carrier_codes
    if three_letter_carrier_codes.prefer_three_kteer is set to Y, otherwise
    returns the corresponding two letter code.
    
  Parameters:
    
    p_3letter_alpha_code - three letter code for carrier.
  
******************************************************************************/
FUNCTION preferred_alpha_code (p_3letter_alpha_code three_letter_carrier_codes.three_letter_carrier_code%TYPE)
RETURN CHAR;

/******************************************************************************
  PROCEDURE update_carrier
 
  Description:
    
    Used by MWS to update a carrier_lookup record for the passed in alpha_code.
    
  Parameters:
    
    These correspond directly to columns on carrier_lookup. Please see the comments
    on the table for definitions.
  
******************************************************************************/
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);

/******************************************************************************
  PROCEDURE delete_carrier
 
  Description:
    
    Deletes a carrier_lookup record and its associated three_letter_carrier_codes
    record(s).
    
  Parameters:
    
    p_alpha_code     - alpha code of carrier
  
******************************************************************************/
PROCEDURE delete_carrier(p_alpha_code        carrier_lookup.alpha_code%TYPE);

/******************************************************************************
  PROCEDURE add_carrier_from_mws
 
  Description:
    
    Used by MWS to create a carrier_lookup record for the passed in alpha_code.
    
  Parameters:
    
    These correspond directly to columns on carrier_lookup. Please see the comments
    on the table for definitions.
  
******************************************************************************/
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);

/******************************************************************************
  PROCEDURE add_or_update_three_letter
 
  Description:
    
    Creates a three_letter_carrier_codes record with the passed in parameters.
    If there is a three_letter_carrier_codes for the given three_letter_code
    then it updates the value of prefer_3letter for that record.
    
  Parameters:
    
    p_alpha_code         - alpha code of carrier
    p_three_letter_code  - three letter code for carrier.
    p_prefer_3letter     - flag to indicate if the two letter or three letter code
                           should be used.
******************************************************************************/
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);
                                     
/******************************************************************************
  PROCEDURE delete_three_letter_code
 
  Description:
    
    Deletes the specified three_letter_carrier_codes record.
    
  Parameters:
    
    p_three_letter_code  - three letter code for carrier.
******************************************************************************/
PROCEDURE delete_three_letter_code(p_three_letter_code three_letter_carrier_codes.three_letter_carrier_code%TYPE);

END carrier;
/