FUNCTION TRANSLATE (
      P_TABLE IN VARCHAR2, 
      P_COLUMN IN VARCHAR2,
      P_REF_ID IN VARCHAR2,  
      P_LANGUAGE in VARCHAR2 default 'FRE',
      P_COUNTRY in VARCHAR2 default NULL,
      P_ROLE in VARCHAR2 default NULL) 
  RETURN VARCHAR2 AS 
    v_mob_id  number;
    v_return  varchar2(4000);
    v_mla_id  number;
    v_mro_id number := 0;
    v_dco_id number := 0;
  BEGIN

/*
    RForbes      20111013       Original Version

P_TABLE is the TABLE_ALIAS of the object for which you are looking for the translation  (e.g. DPD for position details)
P_COLUMN is the COLUMN_NAME of the object for which you are looking for the translation  (e.g. POSITION_TITLE)
P_REF_ID is the ID on the table identified in P_TABLE that has the record you are looking for the translation
P_LANGUAGE optional is the language you want to translate to (if not provided, FREnch will be used)

*/

-- Find MLA_ID
-- 1. look for language + country combination
-- 2. look for language + NO Country
  if P_COUNTRY is not NULL then
    begin
      select ID into v_DCO_ID
        from p2k_cm_countries
       where country_code = upper(trim(p_country));
    exception
      when others then
        return '*NO COUNTRY '||sqlerrm(sqlcode);
    end;
  end if;
  
  if P_ROLE is not NULL then
    begin
      select ID into v_MRO_ID
        from p2k_am_roles
       where role_name = upper(trim(p_role));
    exception
      when others then
        return '*NO ROLE '||sqlerrm(sqlcode);
    end;
  end if;
  
  begin
    select ID into v_MLA_ID
      from p2k_am_languages
      where LANGUAGE_CODE = upper(trim(p_LANGUAGE))
        and NVL(DCO_ID,0) = v_dco_id
        and NVL(MRO_ID,0) = v_mro_id;
  exception when others then
    begin
      select ID into v_MLA_ID
        from p2k_am_languages
       where LANGUAGE_CODE = upper(trim(p_LANGUAGE))
         and NVL(DCO_ID,0) = v_dco_id;
    exception when others then
      begin
        select ID into v_MLA_ID
          from p2k_am_languages
         where LANGUAGE_CODE = upper(trim(p_LANGUAGE));
      exception when others then   
        return '*NO LANGUAGE '||sqlerrm(sqlcode);
      end;
    end;
  end;

    begin 
      select ID into V_MOB_ID
        from p2k_am_objects
        where OBJECT_CODE = upper(trim(p_TABLE))
          and ATTRIBUTE_CODE = upper(trim(p_column));
      exception when others then
        return NULL;
    end;
  
    begin
      select TRANSLATED_TEXT into V_RETURN
        from p2k_am_translations
       where MOB_ID = v_mob_id
         and REFERENCE_ID = p_ref_id
         and MLA_ID = v_mla_id;
      exception when others then
        return NULL;
    end;
     
  RETURN V_RETURN;
  
END; -- TRANSLATE;