APEX boilerplate translation

APEX provides a mechanism for translating applications into other languages:

Applications can be translated from a primary language into other languages. Each translation results in the creation of a new translated application. Each translation requires a mapping which identifies the target language as well as the translated application ID. Translated applications cannot be edited directly in the Application Builder.
Once the translation mappings are established the translatable text within the application is seeded into a translation repository. This repository can then be exported to an XLIFF for translation.
Once the XLIFF file is populated with the translations, one file per language, the XLIFF file is uploaded back into the translation repository. The final step is to publish each translated application from the translation repository.
A translated application will require synchronization when the primary application has been modified since the translated version was last published. Even modifications to application logic will require synchronization. To synchronize, seed and publish the translated application.

I have never used this method, but I have worked on a number of APEX applications that can be translated into other languages by other means.  Essentially this is a matter of "soft-coding" all boilerplate such as region titles and item labels - holding them as data in tables and selecting the appropriate values at run time.

For item labels there is a neat solution involving APEX shortcuts.  First we define a table to hold the item labels in all the languages we support something like this:

APP_ID	ITEM_NAME	LANGUAGE	LABEL_TEXT
101	P1_EMPNO	ENG	Employee identifier
101	P1_EMPNO	ES	Identificación del empleado
101	P1_EMPNO	FR	Identifiant des employés

Now we need a function to get the label text for an item in the current language, something like:

function label_text (p_app_id number,
                     p_item_name varchar2,
                     p_language varchar2)

Then we can create an APEX label template with "Before label" HTML like this:

<label for="#CURRENT_ITEM_NAME#">"LABEL_TEXT"

"LABEL_TEXT" is a reference to an APEX shortcut which we can now define using PL/SQL function body:

return translate_pkg.label_text(:APP_ALIAS,'#CURRENT_ITEM_NAME#',:AI_LANGUAGE)

(AI_LANGUAGE is an application item defining the user's preferred language.) All we now need to do is use the new label template on all our page items and leave the item's label attribute blank.

It would be nice if we could do something similar for region titles, but unfortunately APEX shortcuts are not currently supported in region titles. Instead we have to make do with creating a hidden item e.g. P1_EMP_REGION_TITLE that we set to the desired title, and then set the region title to &P1_EMP_REGION_TITLE. That still leaves the matter of translating error messages and of course the actual data, but I won't go into that in this post.