--
-- BBAN_IBAN_BIC  (Package Body) 
--
/**
* ONLY FOR BELGIAN BBAN, BIC AND IBAN !!!
*/
CREATE OR REPLACE package body ACIS_COMMUN.bban_iban_bic as
/**
* Function returns '1' if the bban code is validated 
* @param p_bban                String of the code to validate (without '-')
* @return Returns a char '1' TRUE if the code is validated, else '0' is returned
*/
  FUNCTION VALIDATE_BBAN
( p_bban IN VARCHAR2 ) return CHAR as
    v_mod INTEGER;
    v_mod_check INTEGER;
  begin
    select mod(substr(p_bban,1,10), 97) into v_mod from dual;
    v_mod_check := substr(p_bban,11,2);
    if v_mod = v_mod_check then
      return '1';
    else 
      return '0';
    end if;
  end VALIDATE_BBAN;
/**
* Function returns '1' if the iban code is validated 
* @param p_iban                String of the code to validate (without '-')
* @return Returns a char '1' TRUE if the code is validated, else '0' is returned
*/
  FUNCTION VALIDATE_IBAN
( p_iban IN VARCHAR2 ) return CHAR as
  v_mod INTEGER;
  v_mod_iban INTEGER;
  v_mod_check INTEGER;
  v_mod_check_iban INTEGER;
  begin
    select mod(substr(p_iban,5,10), 97) into v_mod from dual;
    v_mod_check := substr(p_iban,15,2);
    if v_mod = v_mod_check then
      v_mod_iban := substr(p_iban,3,2);
      v_mod_check_iban := 98-mod(to_number(v_mod||v_mod||'111400'),97);
      if v_mod_iban = v_mod_check_iban then
        return '1';
      else 
        return '0';
      end if;
    else 
      return '0';
    end if;
  end VALIDATE_IBAN;
/**
* Function returns '1' if the bic code is validated 
* @param p_bic                String of the code to validate (without '-')
* @return Returns a char '1' TRUE if the code is validated, else '0' is returned
*/
  FUNCTION VALIDATE_BIC
( p_bic IN VARCHAR2 ) return CHAR as
  v_bic VARCHAR2(8);
  begin
    /* TODO implementation required */
    begin
      select bic_code into v_bic from bic where bic_code = p_bic;
    exception
      when NO_DATA_FOUND then
        return '0';
      when others then
        raise;
    end;
    return '1';
  end VALIDATE_BIC;
/**
* Function returns the converted IBAN code from the belgian BBAN
* @param p_bban                String of the code to convert (without '-')
* @return Returns the converted code
*/
  FUNCTION BBAN_TO_IBAN
( p_bban IN VARCHAR2 ) return VARCHAR2 as
  v_mod INTEGER;
  v_mod_iban INTEGER;
  begin
    v_mod := substr(p_bban,11,2);
    v_mod_iban := mod(to_number(v_mod||v_mod||'111400'),97);
    return 'BE'||trim(to_char(98-v_mod_iban,'09'))||p_bban;
  end BBAN_TO_IBAN;
/**
* Function returns the converted BBAN code from the belgian IBAN
* @param p_iban                String of the code to convert (without '-')
* @return Returns the converted code
*/
  FUNCTION IBAN_TO_BBAN
( p_iban IN VARCHAR2 ) return VARCHAR2 as
  begin
    return substr(p_iban,5,12);
  end IBAN_TO_BBAN;
/**
* Function returns the extracted BIC code from the belgian BBAN
* @param p_bban                String of the code to extract bic code (without '-')
* @return Returns the extracted code
*/
  FUNCTION BIC_FROM_BBAN
( p_bban IN VARCHAR2 ) return VARCHAR2 as
  v_bic VARCHAR(8);
  begin
    select bic_code 
    into v_bic 
    from bic 
    where bic_id = to_number(substr(p_bban,1,3));
    return v_bic;
  end BIC_FROM_BBAN;
/**
* Function returns the extracted BIC code from the belgian IBAN
* @param p_iban                String of the code to extract bic code (without '-')
* @return Returns the extracted code
*/
  FUNCTION BIC_FROM_IBAN
( p_iban IN VARCHAR2 ) return VARCHAR2 as
  v_bic VARCHAR(8);
  begin
    select bic_code 
    into v_bic 
    from bic 
    where bic_id = to_number(substr(p_iban,5,3));
    return v_bic;
  end BIC_FROM_IBAN;
/**
* Function returns the bank name from the belgian BBAN
* @param p_bban                String of the code to extract bank name (without '-')
* @return Returns the bank name
*/
  FUNCTION NAME_FROM_BBAN
( p_bban IN VARCHAR2 ) return VARCHAR2 as
  v_name VARCHAR(255);
  begin
    select case 
      when bic_name_french is null and bic_name_dutch is not null then bic_name_dutch
      when bic_name_french is null and bic_name_dutch is null and bic_name_german is not null then bic_name_german
      when bic_name_french is null and bic_name_dutch is null and bic_name_german is null then bic_name_english
    end case
    into v_name
    from bic 
    where bic_id = to_number(substr(p_bban,1,3));
    return v_name;
  end NAME_FROM_BBAN;
/**
* Function returns the bank name from the belgian IBAN
* @param p_iban                String of the code to extract bank name (without '-')
* @return Returns the bank name
*/
  FUNCTION NAME_FROM_IBAN
( p_iban IN VARCHAR2 ) return VARCHAR2 as
  v_name VARCHAR(255);
  begin
    select bic_name_french 
    into v_name
    from bic 
    where bic_id = to_number(substr(p_iban,5,3));
    return v_name;
  end NAME_FROM_IBAN;

end bban_iban_bic;
/