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;