Enterprise PeopleTools 8.50 PeopleBook: Global Technology

Using Related Language Tables

This chapter provides an overview of Related Language tables and discusses how to:

Install PeopleSoft-provided translations.
Swap the base language.
Create related language tables.
Create related language views.

Understanding Related Language Tables

This section discusses:

Related language tables overview.
Related language table structure.
How related language tables store transactions.
How related language tables are used.

Related Language Tables Overview

PeopleTools can store multiple translations of application data and PeopleTools objects in a single database. Each PeopleSoft database has a single base language. The base language of a new PeopleSoft database upon installation is always English, but can be changed by an administrator. The base language should be the language most commonly used by application users, and is the language in which data is stored in the core PeopleSoft tables known as base language tables.

All PeopleTools objects, such as pages, fields and queries, can be maintained in multiple languages. Descriptions of application data elements, such as departments, locations and job codes, can also be maintained in multiple languages.

The key to maintaining this data in multiple languages is the use of special tables known as related language tables. A related language table stores descriptions and other language-sensitive elements in all languages other than the base language of the database. In this way, while any table in the database can store data in the base language of that database, only tables that have related language tables can maintain the same data in multiple languages simultaneously. For example, it is unlikely that you will want to maintain the descriptions of your general ledger journal lines in multiple languages − the sheer volume of the journal lines in most systems would preclude any effort to maintain translations of their descriptions. The cost of hiring a translator to translate each journal line would be prohibitive, and in most cases only the person entering the journal line, and possibly their supervisor, would be likely to want to view it again. However for frequently used values, such as a chart of accounts, many users across your entire organization would often need to refer to this data, and so you would most likely want to maintain the descriptions of each ChartField entry in each language spoken by your users.

In this case, you would not need a related language table for your Journal Lines table, as you would be maintaining Journal Line descriptions in a single language only, and this would be in the base table, but you would need a related language table for each of your ChartField tables.

When the system displays a language-sensitive field value, it retrieves the text from either the base table or the related language table, depending on the following:

The current language preference.
Whether any translated rows for the field are found in the related language table.

The language preference applies to both the session language and the data language and can be different for each.

Note. In the case of Application Designer, the language preference is determined by the PeopleSoft Configuration Manager language setting.

If the current language preference is the system's base language, the text is retrieved from the base table. If the language preference is a non-base language, then the system looks for a translation of the text in the related language table. If it finds a translation, it displays the translated text; if no translation exists, the system uses the text in the base table. This enables developers to selectively translate portions of the system, while keeping the system fully functional at all times, even if not all rows have been translated. The following flowchart shows this flow of execution:

System display of base and non-base language text

Synchronization of related language tables with their base tables is automatically maintained by PeopleTools. When you translate a language-sensitive field, the system adds a new row to the related language table. When you delete a row from a base table, any child rows in the related language table are deleted. The primary responsibility of the application developer in the language architecture is to define and maintain the related language tables.

Note. PeopleTools automatically handles the storage and display of data in related language tables and their synchronization with corresponding entries in the base language tables based on each user’s language preference. However, PeopleTools cannot automatically translate data! The PeopleSoft system provides translations of PeopleSoft Pure Internet Architecture components and user interfaces for most application products into several languages. Also, translations of several key application data tables, such as country and currency codes, are provided by the PeopleSoft system. However, you will need to plan a way to translate application data entered by your users that needs to be maintained in multiple languages. Some users who are familiar with several languages may want to translate the data elements they enter themselves using the Multilingual Support feature. In other cases, you may want to bring in translators periodically to translate key elements into each language used in your organization. PeopleTools provides an architecture to store these translations, but does not perform “machine translation” or provide other automated linguistic translation technologies.

See Also

Controlling International Preferences

Editing Data in Multiple Languages

Related Language Table Structure

Related language tables store multiple translations of language-sensitive fields from an associated base table. For example, the description (DESCR), short description (DESCRSHORT), and long description (DESCRLONG) fields are commonly language sensitive.

Each table in your database that is not a related language table is, by definition, a base language table. The Message Catalog table, PSMSGCATDEFN, is one example of a base table. Note that it has a DESCRLONG field—which is typically language-sensitive—as well as MESSAGE_TEXT, which is also language-sensitive.

PSMSGCATDEFN table

PeopleSoft has therefore created a new record definition, PSMSGCATLANG, to act as the related language table for PSMSGCATDEFN. Related language records are defined in Application Designer just like any other record definitions. However, as they often contain many of the same fields as their base language table, it is often quicker to create the related language table by cloning the base table. To clone a base table, open the base language table and the use the Save As option on the File menu and enter a name for the new record. Once cloned, any fields not required in the related language record can be deleted from the new record.

A related language table must:

Share all key record fields with its base table.
Have an additional key record field, which must be LANGUAGE_CD.
Have language-sensitive fields matching those in the base table. These are typically DESCR, DESCRSHORT or DESCRLONG; or LONGNAME or SHORTNAME.
Not contain any fields that are not present on the base table, except the LANGUAGE_CD key field.
Not contain any non-key, non-language-sensitive fields from the base table.

The related language table, PSMSGCATLANG, meets all of these requirements. Note that both language-sensitive fields, DESCRLONG and MESSAGE_TEXT, appear in PSMSGCATLANG.

PSMSGCATLANG table

The base table must be explicitly associated with its related language table.

To associate a base table with its related language table:

Open the Record Properties dialog box for the base table record.
Click the Use tab.
Under Record Relationships, in the Related Language Record field enter the name of the related language table.

Note. There is a one-to-one relationship between the base record and the related language record. Also, the record type of the base record and the related language record should be the same.
Click OK.

How Related Language Tables Store Translations

Base tables store language-sensitive descriptions in the database's base language. Related language tables store translations of these descriptions in non-base languages. For each row in the base table, there can be zero rows or one row in the related language table for each non-base language.

For example, the following table shows a row of data from the PSMSGCATDEFN table.

MESSAGE_SET_NBR	MESSAGE_NBR	MESSAGE_TEXT	MSG_SEVERITY	DESCRLONG
1	1	Distributed Object Manager: Help Name=%1 Language=%2	M	A Help Text object is being retrieved from the database. This message is for your information only. It is not an error or a warning.

The following table shows child rows of data from the PSMSGCATLANG table. For each LANGUAGE_CD, there is a row with appropriate translations of the language-sensitive fields, DESCRLONG and MESSAGE_TEXT, from the parent row.

MESSAGE_SET_NBR	MESSAGE_NBR	LANGUAGE_CD	MESSAGE_TEXT	DESCRLONG
1	1	ESP	Gestor de objetos distribuidos: Nombre de ayuda=%1 Idioma=%2	Se está recuperando un objeto texto de ayuda de la base de datos. Este mensaje sólo es informativo. No es un error ni un aviso.
1	1	FRA	Gestionnaire d'objets répartis: Aide=%1 Langue=%2	Un objet de type Aide en ligne est extrait de la base de données. Ce message est affiché seulement à titre d'information. Ce n'est ni un message d'erreur, ni un message d'avertissement.

How Related Language Tables Are Used

Related language tables are the primary means of storing language translations in the PeopleSoft system. They store translations of descriptive text for data, such as a standard list of expense categories, and system objects, such as descriptions of fields and records.

Related language tables that store descriptive text for data are generally associated with edit tables that store lists of values that are used for validation and prompting. When a page field prompts against a table that has an associated related language table, and, if the user’s language preference is a non-base language, the description fields that have been translated into the current language appear in place of the base language descriptive text.

For example, the following Message Catalog page appears when the user’s language preference is Spanish (ESP). The Texto Mensaje (MESSAGE_TEXT) field and the Explicación (DESCRLONG) field present translations from the PSMSGCATLANG related language table.

Page with translated descriptive text (Message Catalog page)

Many PeopleTools object definitions, including fields, records, and components, also have associated related language tables. This makes maintenance of multiple language PeopleTools objects, and therefore the system user interface, relatively straightforward. The system uses a single mechanism for maintaining translated data and for maintaining the system objects themselves. Field translation is particularly powerful because translated field descriptions appear on search pages and on pages where the descriptive text for the field is used as the field label.

In the following example all the fields on the ADDRESS2_SBP subpage are translated into Spanish:

Translation of fields in ADDRESS2_SBP

Because the fields are translated, the translated versions of field labels appear when a user whose language preference is Spanish accesses a page that shows the long name or short name of any of these fields. No additional development work on the part of the developer is required.

Of course, whenever the base labels change, you must also update the translations of the object names.

Translating Application Definitions

Installing Oracle-Provided Translations

This section provides an overview of translation installation and discusses how to:

Add translations to an existing database on the same PeopleTools version.
Add translations to an existing database on a different PeopleTools version.
Use the upgrade process to copy customizations into a multilingual database.

Understanding Translation Installation

In many cases, Oracle provides translations of the user interface and key application data for many PeopleSoft application products. Except for PeopleTools, these translations are shipped on a separate CD for each product line; this CD is known as the “PeopleSoft Global Multi-Language CD.” If you did not receive this CD in your kit, you may not have licensed the appropriate translations from Oracle. Contact customer support to obtain the CD. Translations for PeopleTools itself are shipped on the PeopleTools CD; no Global Multi-Language CD is required for PeopleTools.

Typically, you would install these translations when you first install your database. However, if you did not install the Oracle-provided translations during your initial install, this section describes the steps to follow to add translations that you have licensed from Oracle to an already-existing database.

Note. You should install the PeopleSoft Global Multi-Language CD at the same time that you install the English release.

Note. The size impact on the database of installing translations is difficult to predict exactly, but a general rule of thumb is approximately 5% per language.

To add translations licensed from Oracle, you must determine how you will perform your upgrade. Choose the most appropriate of the following three options:

Add the translations provided on the PeopleSoft Global Multi-Language CD to your existing PeopleSoft database on the same PeopleTools version.

This option is appropriate only if you have not performed any application or PeopleTools maintenance (that is, you have not upgraded, applied patches, or applied maintenance packs), and you have not made any customizations to existing PeopleTools-based objects in your existing database, and your current PeopleTools version matches the version originally available at the time of the GA release of your PeopleSoft application. PeopleTools-based objects include any objects that you maintain using Application Designer or PeopleSoft Tree Manager, such as pages, fields, and menus. If you have added only new PeopleTools objects to your database, without modifying existing definitions, then you can use this option.

Adding or customizing system data, such as account codes, departments, and locations, is not considered a PeopleTools object change. If you have performed only these types of transactions against your existing database, and you have not modified any objects using Application Designer or PeopleSoft Tree Manager, then you can use this option.
Add the translations provided on the PeopleSoft Global Multi-Language CD to an existing database on a different PeopleTools version.

This option is appropriate if you have not made any customizations to existing PeopleTools-based objects, but your PeopleTools version is different from the version that was available at the time of the GA release of your PeopleSoft application. You will need to set up a separate database with the translations, upgrade the database to your current PeopleTools version, and then copy the translations from your new database to your existing PeopleSoft database.
Use the upgrade process to copy your customizations into a multilingual database.

If you have customized the PeopleTools objects in your database, you cannot simply add the translations contained on the PeopleSoft Global Multi-Language CD-ROM to your existing database. Instead, you must create a new database containing all of your licensed translations and use the upgrade process to copy customizations from your existing database to your newly installed database.

Adding Translations to an Existing Database on the Same PeopleTools Version

This procedure is appropriate only if you have not performed any application or PeopleTools maintenance (that is, you have not upgraded, applied patches, or applied maintenance packs), and you have not made any customizations to existing PeopleTools-based objects in your existing database, and your current PeopleTools version matches the version originally available at the time of the GA release of your PeopleSoft application.

To add translations to an existing database on the same PeopleTools version:

Read the section “Understanding Translation Installation” presented previously in this chapter.

See Understanding Translation Installation.
Back up your database.
Load your PeopleSoft Global Multi-Language CD.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Setting Up the Windows File Server.”
Rerun the steps for setting up the batch environment.

If you are running a PeopleSoft Process Scheduler on Microsoft Windows, then you must rerun the steps for setting up the batch environment documented in your PeopleTools installation guide.

Because the PeopleSoft Global Multi-Language CD contains translations for your Crystal Reports and PS/nVision layouts, you must transfer those translations to your Windows-based PeopleSoft Process Scheduler.

If you run only non-Windows-based PeopleSoft Process Schedulers, you do not need to complete this step. This step also does not need to be performed for UNIX batch servers because the only objects that contain translations outside the database are Crystal and PS/nVision reports, both of which are supported on only Windows-based batch servers.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Setting Up the Batch Environment.”
Generate database setup from PeopleSoft Data Mover.

Run PeopleSoft Data Mover in bootstrap mode by logging in with your database’s access ID. From PeopleSoft Data Mover, select File, Database Setup, and follow the wizard step by step to generate the database setup script. Be sure to select “Add New Language” in the “Database Type” selection box in the second step of the wizard. The generated database setup script appears in the PeopleSoft Data Mover input view. Be sure to enter the same database parameters that you entered the first time you ran the database setup program to install your English database; do not select any language that already exists in your database.

If you choose a new base language, then the generated script will include the command to swap the base language.
Run the PeopleSoft Data Mover script.

Because you have already created your database and set up the appropriate database-specific objects, you must perform only the “Run Data Mover Import Scripts” task.
Run the VERSION Application Engine program to update your PeopleTools version numbers.

Run the PeopleTools VERSION Application Engine program against your database. This synchronizes the PeopleTools version numbers, taking the new translations into account.
Rerun your database audits.

Your database should now be complete. To ensure that it is complete, rerun the database audits. Refer to the sections about creating and checking a database in your PeopleTools installation guide for more information.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Creating a Database.”

Adding Translations to an Existing Database on a Different PeopleTools Version

This procedure is appropriate only if you have not made any customizations to existing PeopleTools-based objects, but your PeopleTools version is different from the version that was available at the time of the GA release of your PeopleSoft application. To add translations to an existing database on a different PeopleTools version than the one that was available at the time of the GA release of your PeopleSoft application, you must set up a separate database with the translations, upgrade it to the required PeopleTools version, and then copy the translations from the new database to your existing database.

To add translations to an existing database on a different PeopleTools version:

Read the section “Understanding Translation Installation” presented previously in this chapter.

See Understanding Translation Installation.
Back up your database.
Install a new database.

Install a new database, including the translations you need at the same time, making sure to perform the steps in the task “Updating Database to Latest PeopleTools Release” in your PeopleTools installation guide. The translations need to be loaded prior to running this task. For more information on installing and upgrading PeopleTools translations, including PPLTLSML, PPLTLS84CURML, PPLDELETE, PATCH84xML, PT84xTLSxxx.dms, and MSGTLxxx.dms, see your PeopleTools installation guide.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Updating Database to Latest PeopleTools Release.”
Do a database compare.

Do a database compare of your new database against your existing database to create a project containing all managed object differences between the two databases. Review the database compare report and the generated database compare project to ensure that only language objects are copied, and they don’t overwrite any customizations.
Apply this compare project to the target database.
Export application system data.

In Data Mover, export application system data (non managed objects) from the new database to a .dat file. Import the system data into the target database. For application data, the Define System Data menu lists the system data, and the tables are PS_PM_PS_REC and PSRECDEFN.RELLANGRECNAME. PeopleTools system data was already loaded via the PT84xTLSyyy.dms scripts.
Run the VERSION Application Engine program.

Run the PeopleTools VERSION Application Engine program against your database. This synchronizes the PeopleTools version numbers, taking the new translations into account.
Rerun your database audits.

Your database should now be complete. To ensure that it is complete, rerun the database audits. Refer to the sections about creating and checking a database in your PeopleTools installation guide for more information.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Creating a Database.”

Using the Upgrade Process to Copy Customizations into a Multilingual Database

This section explains how to load translations by using the upgrade process to copy your customizations into a multilingual database. If you have customized the PeopleTools objects in your database, you cannot simply add the translations contained on the PeopleSoft Global Multi-Language CD-ROM to your existing database. Instead, you must create a new database containing all of your licensed translations and use the upgrade process to copy customizations from your existing database to your newly installed database.

To use the upgrade process to copy your customizations into a multilingual database:

Read the section “Understanding Translation Installation” presented previously in this chapter.

See Understanding Translation Installation.
Back up your database.
Load your PeopleSoft Global Multi-Language CD.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Setting Up the Windows File Server.”
Rerun the steps for setting up the batch environment.

If you are running a PeopleSoft Process Scheduler on Microsoft Windows, then you must rerun the steps for setting up the batch environment documented in your PeopleTools installation guide.

Because the PeopleSoft Global Multi-Language CD contains translations for your Crystal Reports and PS/nVision layouts, you must transfer those translations to your Windows-based PeopleSoft Process Scheduler.

If you run only non-Windows-based PeopleSoft Process Schedulers, you do not need to complete this step. This step also does not need to be performed for UNIX batch servers because the only objects that contain translations outside the database are Crystal and PS/nVision reports, both of which are supported on only Windows-based batch servers.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Setting Up the Batch Environment.”
Create the new multilingual database.

Create and load your new PeopleSoft global multi-language database, which contains all of the languages you’ve licensed, and apply patches and bundles to bring it to the same level as your source database. Refer to the section on creating a database in your PeopleTools installation guide for more information.

Keep in mind that you must load the English objects provided by PeopleSoft even if you don’t plan to run your system in English. Follow all of the applicable steps in the documentation about creating a database except the swap base language step. You should not swap the base language of your new database until you have copied your customizations into it.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Creating a Database.”
Copy the customizations into the new multilingual database.

Use the Application Designer upgrade compare process to compare your existing, customized database against the newly installed database in order to identify your customizations. Once you have identified the objects you have changed, use upgrade copy to copy the objects into your new multilingual database.

If you have maintained all of your customizations in Application Designer projects, you can easily use these projects to copy only the objects you have modified over to your new PeopleSoft global multilingual database.
Create a project with all objects.

This project will be used in the next step to upgrade customizations and translations into the test or production database.
Load changes in the test database.
1. Using a test database (a copy of the production database) as a target, copy the project from the previous step to the database, selecting only the new language.
2. Using PeopleSoft Data Mover in user mode, import the language tools .dat files using the data mover script, ppltls84Xxxx.dms.
3. In the PeopleSoft Pure Internet Architecture, sign in in English and ensure that the new language is marked as Enabled in the Maintain Installed Languages pages.
4. Run the VERSION Application Engine program to update your PeopleTools version numbers to synchronize the PeopleTools version numbers, taking the new translations into account.
5. (Optional) Swap your base language. If you have not already swapped your base language and you decide to run your database in a non-English base language, swap the base language of your database.
After testing, repeat the previous steps to load changes in the production database.
Rerun the database audits.

Your database should now be complete. To ensure that it is complete, rerun the database audits, as documented in the PeopleTools installation guide in the chapter on creating a database.

See the Enterprise PeopleTools 8.50 installation guide for your database platform, “Creating a Database.”

See Also

Swapping the Base Language

This section provides an overview of base language swapping, lists prerequisites, and discusses how to:

Run the swap audit report.
Check space and resources.
Run the SWAP_BASE_LANGUAGE Data Mover command.

Understanding Base Language Swapping

As delivered, the PeopleSoft database has a base language of English. The system has English data in its base language tables and, if you have licensed and installed the PeopleSoft-provided translations, non-English data in the related language tables. To choose a language other than English as your base language, you must move all English data to the related language tables (with an appropriate language code) and move all of the data in your new base language to the base tables. This process is called swapping the base language of your database. If you later decide to change base languages again, you must go through the same process.

PeopleSoft Data Mover provides the following command to automate this process:

SWAP_BASE_LANGUAGE target_language;

To swap the base language of your database, you must identify the language to swap in as the new base language. PeopleSoft Data Mover finds all the affected tables and loads the appropriate language data into them.

This process handles all related language tables in the system: PeopleTools tables and application tables. Changes are committed after each table is swapped; a log file records the process of the swap and can help you troubleshoot any errors during the process.

You can check the existing base language code by looking in the PeopleTools Options page. To navigate to the PeopleTools Options page, select PeopleTools, Utilities, Administration, PeopleTools Options. You can't change the Base Language Code setting in this page; it is for display purposes only. To change the base language, use the SWAP_BASE_LANGUAGE PeopleSoft Data Mover command.

Prerequisites

Before swapping the base language of your database, you must have:

A clean audit report to ensure that there are no data integrity problems.
Sufficient space and resources to run the swapping process.

Running the Swap Audit Report

The swap audit report, SWPAUDIT.SQR, identifies data integrity problems that will cause errors during the swap process. This SQR program does not have a run control and, therefore, cannot be run with PeopleSoft Process Scheduler. For information on how to run SQR programs manually in your environment, refer to the section on creating a database in your PeopleTools installation documentation.

The swap audit report might list one or more of the following errors. Refer to the following instructions to correct these errors. Rerun the audit until it reports no errors before continuing with the swap process.

Warning! Errors reported during the swap audit process indicate that the swap base language process will fail unless the errors are corrected.

Audit	Error Description	Resolution
SWAP-1	Related Language Records That are not Valid Records The records listed are defined as related language records for one or more base records, but they do not exist in your database.	Do one of the following: Create the related language record, as appropriate, based on the keys and translatable fields of the base record. Open the base language record and remove its association with the related language record in the Object Properties dialog box.
SWAP-2	The Field LANGUAGE_CD is not a Key in the Following Related Language Record(s) A record is defined as a related language record, but it does not have LANGUAGE_CD as a component of its key. LANGUAGE_CD must be a key field on every related language record.	Make LANGUAGE_CD into a key field on each specified related language record.
SWAP-3	The Following Related Language View(s) Have the Wrong Structure Defined A related language record must share all of the same keys as its base language record, plus an additional key field (LANGUAGE_CD). Also, any non-key fields must also be in the base language record.	Correct the field structure for the specified related language views to ensure that they conform to this requirement. Then, recreate the views in the database.
SWAP-4	The Following Related Language Table(s) Have the Wrong Key Structure Defined A related language record must share all of the same keys as its base language record, plus an additional key (LANGUAGE_CD).	Correct the key structure for the specified related language tables to ensure that they conform to this requirement. Then, alter the tables in the database to match the new key structure.
SWAP-5	The Following Related Language Table(s) Have the Wrong Structure Defined A related language record must share all of the same keys as its base language record, plus an additional key field (LANGUAGE_CD). Also, any non-key fields must also be in the base language record.	Correct the field structure for the specified related language tables to ensure that they conform to this requirement. Then, alter the tables in the database to match the new field structure.
SWAP-6	The Following Related Language Table(s) Have the Orphan Row Defined For each row on the related language record there must be a single row on the base table with matching keys. An orphan row is a row of data on the related language record that does not have a corresponding parent row on the base table. You must delete orphan rows from related language tables.	Use your platform's query tool to select against the related language table using the fields listed in the report. For every row in the report, there is an orphan row in a related language table. Perform a SELECT command first to ensure that you are getting the same row count as the report, then use the DELETE command to delete the selected rows. Here is sample The following provides example code for an Oracle database. In this example, xxx is equal to the language code to which you are swapping. SELECT * FROM PSMSGCATLANG WHERE NOT EXISTS (SELECT 'X' FROM PSMSGCATDEFN A WHERE PSMSGCATLANG.MESSAGE_SET_NBR = A.MESSAGE_SET_NBR AND PSMSGCATLANG. MESSAGE_NBR = A.MESSAGE_NBR) AND LANGUAGE_CD = 'xxx' DELETE FROM PSMSGCATLANG WHERE NOT EXISTS (SELECT 'X' FROM PSMSGCATDEFN A WHERE PSMSGCATLANG.MESSAGE_SET_NBR = A.MESSAGE_SET_NBR AND PSMSGCATLANG. MESSAGE_NBR = A.MESSAGE_NBR) AND LANGUAGE_CD = 'xxx' SELECT * FROM Related_Language_Record A WHERE NOT EXISTS (SELECT 'X' FROM Base_Record B WHERE A.Field_Name = BField_Name ...for each field name listed) AND LANGUAGE_CD = 'xxx'
SWAP-7	The Following Base Records do not Have a Unique Index A base record that has a corresponding related language record must have a unique index—that is, a key field.	Open each base record listed in the report and select a field that you want to make a key field. Then alter the tables in the database to match the new key structure.
SWAP-8	The Following Base Records and the Related Language Records Have Different Record Types There is a mismatch between the record type of the base record definition and its related language record definition.	Create a new related language record definition and assign it (or reassign an existing related language definition) to the base record, making sure that both the base record and related language record definition are the same record type.

Checking Space and Resources

Before running the SWAP_BASE_LANGUAGE command, be sure that your database has sufficient space and resources to perform the swap process. Although the swap base language process commits after each table is successfully swapped, you need sufficient log space, or rollback space in the case of Oracle databases, to hold the contents of the largest table to be swapped.

On a newly installed PeopleSoft database, the PSPNLFIELD table is typically the largest table to be swapped, and this can be used as a benchmark for sizing your log space. Plan on at least 50 to 75 MB log or rollback segment space before running the swap process.

Running the SWAP_BASE_LANGUAGE Data Mover Command

Once you are ready to swap the base language, start PeopleSoft Data Mover in non-bootstrap mode by logging in using a regular PeopleSoft user ID (not the access ID). Once PeopleSoft Data Mover begins, type and run the SWAP_BASE_LANGUAGE command:

SWAP_BASE_LANGUAGE target_language;

PeopleSoft Data Mover provides feedback during the swap process, including the name of the record currently being swapped and the number of records that remain to be swapped.

Even when SWPAUDIT completes with no errors, the swap base language process may fail. Typically, environmental issues, such as lack of database space, log space, or rollback segment space, causes any errors during this stage of the process. If a failure occurs, note the database-specific error message issued and take the appropriate action according to your database platform documentation.

PeopleSoft Data Mover stops when errors are encountered. Once you have corrected the problems that caused the failure in the swap base language process, you can restart the process without having to restore your database or remember where the first error occurred. To restart the swap process, rerun the SWAP_BASE_LANGUAGE command. PeopleSoft Data Mover recognizes the tables whose data has already been swapped and does not attempt to swap the data in those tables again; it will report that zero rows were swapped for those tables.

It is safe to rerun this command as many times as needed, correcting errors between runs, until the log file reports no errors.

In some situations, you may want to swap a specific record. Typically, you would swap a single record only if errors occurred during the swap base language process and you want to verify that the swap will succeed without having to re-swap all the records in the database or trace or troubleshoot the swap process.

To swap a specific table, use the following PeopleSoft Data Mover commands:

SET BASE_LANGUAGE target_language;
SWAP_BASE_LANGUAGE recname;

Note. You should swap individual tables only when there has been an error with system-wide swapping.

If you want PeopleSoft Data Mover to not stop when errors are encountered and continue the swap process, set the following:

SET IGNORE_ERRORS;
SWAP_BASE_LANGUAGE LANGUAGE_CD;

If you chose to ignore errors during the swap, after the swap process is complete carefully review the log file to ensure that no errors occurred. If errors occur, fix the errors and then re-swap each table using the SWAP_BASE_LANGUAGE command in individual table mode for each table that failed.

See Also

Enterprise PeopleTools 8.50 installation for your database platform

Creating Related Language Tables

PeopleSoft applications provide related language tables for most edit tables that you are likely to translate, so in most cases, you won’t have to create your own related language tables. However, if during a customization you create a new table that requires translation, you must create your own related language table.

A one-to-one relationship exists between the base record and the related language record. In addition, both base and related language records must share the same record type. For example, if the base record is a SQL table, then the related language record should be defined as a SQL table.

The following procedure assumes that you know how to design and build records in Application Designer.

Note. If a field is defined as a Long (DESCRLONG) on the related language table, then you must ensure that the table is in a tablespace dedicated to tables containing LONG/IMAGE columns (for example, PSIMAGE tablespace for PeopleTools-owned tables). This is needed to support DB2 UDB for OS/390 and z/OS restrictions on bufferpool sizes.

Note. Records with duplicate order keys should not have related language tables associated to them. If the record requires a related language record you must removed the order key(s). Do not create a related language record with a duplicate order key.

To create a related language table:

Check the structure of the base table record, noting its key fields, and make sure that it has fields that can be language-sensitive, such as DESCR, DESCRSHORT, or DESCRLONG.

If the base table has no fields that are language sensitive, then it does not need a related language record.
Design the related language record.

By convention, the related language record name should reflect its relationship to the base table and end with LANG. For example, if the base table is MY_NEW_TBL, the related language table might be named MY_NEW_LANG.

The related language record must:
- Have all the same key fields as the base table.
- Have an additional key field, which must be LANGUAGE_CD.
- Have the language-sensitive fields from the base table.
- Not contain any fields that are not present on the base table except the LANGUAGE_CD key field.
- Not contain any non-key, non-language-sensitive fields from the base table.
Build the related language table.

An easy way to build the related language table is to copy the base record definition using the Save As option on the File menu. Once you save the record with a new name, you can remove the fields that are not required on the related language table and add the LANGUAGE_CD key. By using this approach, you avoid having to remember the key structure and column names of the base table.

Note. When using Save As to create the related language record, you do not need to also save the PeopleCode that is associated with the base record. PeopleCode programs on related language records are not executed; therefore, they are redundant and may be misleading. For this reason, you should not maintain PeopleCode programs on related language records.

It is also recommended that you remove any search key and alternate search key attributes from the related language table, as these do not need to be translated and can hinder performance.
Associate the related language table with the base table record.

Open the Record Properties dialog box for the base table. On the Use tab, select the related language record in the Related Language Record field.

Click OK to close the Record Properties dialog box.
Save the record.

Creating Related Language Views

This section provides an overview of related language views and discusses how to:

Create views for one base table and one related language table.
Create views for two base tables and one related language table.
Create views for two base tables and two related language tables.

Understanding Related Language Views

Just as records with language-sensitive fields require related language records, views with language-sensitive fields require related language views.

Related language views work the same way as related language records and must adhere to the same rules:

Any time you create a view over a table that has a related language record, you typically also need a related language view.
The related language view consists of, at a minimum, all the key fields from the base view, a language code, and the language-sensitive fields.
You associate the base view and the related language view in the Record Properties dialog box of the base view.
When a user logs on in a non-base language, the PeopleSoft system retrieves the data with the appropriate language code from the related language view.
It’s best to use the same naming convention for related language views as you do for related language records, such as prepending L or _LANG to the _VW suffix of the view name.

Note. The related language record type must match the base record type—that is, either both are SQL tables or both are SQL views.

Related language views have one additional issue: the SELECT statement from the original view must be modified to select any language-sensitive fields from the appropriate related language tables.

The SELECT statements behind the views vary in complexity, depending on how many tables are involved and how many of those tables have related language records.

Note. In the following examples, the changes to the SELECT statement for the related language view have been highlighted in bold.

Creating Views for One Base Table and One Related Language Table

When you have a view that selects data from a single table, the SELECT statement for the related language view is straightforward: select the data from the related language table making sure to also select LANGUAGE_CD.

The following diagram shows DEPT_TBLwith one key column (DEPT_ID), one language-sensitive column (DESCR), and one column that is not language sensitive (MGR_ID). DEPT_TBL has a one-to-many relationship with DEPT_TBL_LANG, a table that includes DEPT_ID, LANGUAGE_CD, and DESCR:

Example of one base table, one related language table

As an example, DEPT_TBL contains the following data:

DEPT_ID	DESCR	MGR_ID
110	Finance	01732
120	Engineering	22056
131	Sales - Belgium	08630
132	Sales - Germany	08630
133	Sales - UK	12972
134	Sales - Japan	28553

The related language table, DEPT_TBL_LANG, contains the following data:

DEPT_ID	LANGUAGE_CD	DESCR
110	FRA	Finances
110	GER	Finanzierung
120	FRA	Technologie
120	GER	Technik
131	FRA	Ventes - Belgique
132	GER	Verkäufe - Deutschland
133	GER	Verkäufe - GB

The following is the SELECT statement for the base view:

SELECT A.DEPT_ID, A.DESCR FROM DEPT_TBL A

The following is the SELECT statement for the related language view:

SELECT B.DEPT_ID, B.DESCR, B.LANGUAGE_CD FROM DEPT_TBL_LANG B

Because this base view is simple and selects only columns that exist both in the base table and in the related language table, the related language view is straightforward. It differs from the base view in only two ways:

The name of the table from which it selects.
The addition of the LANGUAGE_CD column.

If the base view also selected non-key, non-language-sensitive columns from the base table, those columns would need to be selected from the base table in the related language view because non-key, non-language-sensitive fields don’t exist in related language records.

Creating Views for Two Base Tables and One Related Language Table

When you join two tables, one of which has a related language record, the SELECT statement becomes only slightly more complex.

The following diagram shows the addition of EMPLOYEE_TBL to the previous diagram. DEPT_TBL has a many-to-one relationship with EMPLOYEE_TBL, which has one key column (EMPL_ID) and one non-language-sensitive column (LAST_NAME). The same one-to-many relationship as depicted previously exists between DEPT_TBL and the related language record, DEPT_TBL_LANG:

Example of two base tables, one related language table

As an example, DEPT_TBL contains the same data as listed previously:

DEPT_ID	DESCR	MGR_ID
110	Finance	01732
120	Engineering	22056
131	Sales - Belgium	08630
132	Sales - Germany	08630
133	Sales - UK	12972
134	Sales - Japan	28553

The related language table, DEPT_TBL_LANG, also contains the same data:

DEPT_ID	LANGUAGE_CD	DESCR
110	FRA	Finances
110	GER	Finanzierung
120	FRA	Technologie
120	GER	Technik
131	FRA	Ventes - Belgique
132	GER	Verkäufe - Deutschland
133	GER	Verkäufe - GB

Finally, EMPLOYEE_TBL contains the following data:

EMPL_ID	LAST_NAME
01732	Jones
08630	Gräff
12972	Smythe
17145	De Bruecker
22056	Agarwal
28553	Katsuhiro

The following SELECT statement for the base view selects the department ID, description, and the last name of the manager for each department. The department description is language-sensitive, but the employee last name is not.

SELECT A.DEPT_ID, A.DESCR, B.LASTNAME
FROM 
  DEPT_TBL A, 
  EMPLOYEE_TBL B 
WHERE A.MANAGER_ID = B.EMPL_ID

The following SELECT statement is for the related language view:

SELECT A.DEPT_ID, C.DESCR, B.LASTNAME, C.LANGUAGE_CD
FROM 
  DEPT_TBL A, 
  EMPLOYEE_TBL B,
  DEPT_TBL_LANG C
WHERE A. MGR_ID = B.EMPL_ID
  AND A.DEPT_ID = C.DEPT_ID

As an example of a view used in PeopleTools, the following SELECT statement builds the PeopleTools view (PTLT_FEATFLTRVW). The base view selects one key column (PTLT_FEATURE_CODE) and one language-sensitive column (PTLT_FEATURE) from the base table (PTLT_FEATURE) while joining additional data (PTLT_PROJ_NAME) from another base table (PTLT_PROJ_DEFN):

SELECT DISTINCT 
  A.PTLT_PROJ_NAME,
  D.PTLT_FEATURE_CODE,
  D.PTLT_FEATURE 
FROM
  PTLT_PROJ_DEFN A,
  PTLT_PROJ_TASK B,
  PTLT_ASSGN_TASK C,
  PTLT_FEATURE D 
WHERE A.PTLT_PROJ_NAME = B.PTLT_PROJ_NAME 
  AND B.PTLT_TASK_CODE = C.PTLT_TASK_CODE
  AND C.PTLT_FEATURE_CODE = D.PTLT_FEATURE_CODE

The following SELECT statement is for the related language view (PTLT_FEATFL_LVW). In this view, the key field, language-sensitive data, and the language code are selected from the related language table (PTLT_FEAT_LANG):

SELECT DISTINCT
  A.PTLT_PROJ_NAME,
  D.PTLT_FEATURE_CODE,
  D.PTLT_FEATURE,
  D.LANGUAGE_CD 
FROM
  PTLT_PROJ_DEFN A,
  PTLT_PROJ_TASK B,
  PTLT_ASSGN_TASK C,
  PTLT_FEAT_LANG D 
WHERE A.PTLT_PROJ_NAME = B.PTLT_PROJ_NAME
  AND B.PTLT_TASK_CODE = C.PTLT_TASK_CODE
  AND C.PTLT_FEATURE_CODE = D.PTLT_FEATURE_CODE

This related language view differs from the base view in only two ways:

The name of the table from which it selects.
The addition of the LANGUAGE_CD column.

Creating Views for Two Base Tables and Two Related Language Tables

When you join two tables that both have related language records, the SELECT statement becomes complex.

The following diagram shows a new many-to-one relationship of DEPT_TBL to LOCATION_TBL. Unlike the previous examples, DEPT_TBL includes a LOCATION column rather than a MGR_ID column. LOCATION_TBL includes a key column (LOCATION) and a language-sensitive column (DESCR). The same relationship as depicted previously exists between the related language record, DEPT_TBL_LANG, and DEPT_TBL. In addition, there is a one-to-many relationship between LOCATION_TBL and its related language record, LOCATION_TBL_LANG, which includes LOCATION, LANGUAGE_CD, and DESCR:

Example of two base tables, two related language tables

Unlike the previous examples, DEPT_TBL includes a LOCATION column with data rather than a MGR_ID column:

DEPT_ID	DESCR	LOCATION
110	Finance	RWS
120	Engineering	RWS
131	Sales - Belgium	BRU
132	Sales - Germany	MUN
133	Sales - UK	LON
134	Sales - Japan	OSA

The related language table, DEPT_TBL_LANG, contains the same data as previously:

DEPT_ID	LANGUAGE_CD	DESCR
110	FRA	Finances
110	GER	Finanzierung
120	FRA	Technologie
120	GER	Technik
131	FRA	Ventes - Belgique
132	GER	Verkäufe - Deutschland
133	GER	Verkäufe - GB

LOCATION_TBL contains location codes and descriptions:

LOCATION	DESCR
RWS	Redwood Shores
BRU	Brussels
MUN	Munich
LON	London
OSA	Osaka

Finally, LOCATION_TBL_LANG contains translations of the language-sensitive field, DESCR:

LOCATION	LANGUAGE_CD	DESCR
BRU	FRA	Bruxelles
BRU	GER	Brüssel
LON	FRA	Londres
MUN	GER	München

In this example, the base view joins DEPT_TBL with LOCATION_TBL to retrieve the location of the department. The following SELECT statement is for the base view:

SELECT A.DEPT_ID, A.DESCR, B.LOCATION, B.DESCR
FROM 
  DEPT_TBL A, 
  LOCATION_TBL B
WHERE A.LOCATION = B.LOCATION

However, in this example, creating the related language view is difficult because both tables referenced in the base view have related language records. Because the related language architecture does not require that all rows have translations, creating the related language view is not as simple as joining DEPT_TBL_LANG with LOCATION_TBL_LANG.

The related language view needs to take into account the following scenarios:

A translated department may reference a translated location—for example, the German for department 132 (Verkäufe – Deutschland) references the German for location MUN (München).
A translated department may reference an untranslated location—for example, the French for department 120 (Technologie) does not have a translated location and therefore references the untranslated location for RWS (Redwood Shores).

In this scenario, there is a row in DEPT_TBL_LANG, but there is no row in LOCATION_TBL_LANG. If you were to join DEPT_TBL_LANG with LOCATION_TBL_LANG in the view, no translation would be retrieved by the related language view because the location doesn’t have a translation.
A translated location may reference an untranslated department —for example, the German for location BRU (Brüssel) does not have a translated department and therefore references the untranslated department for 131 (Sales - Belgium).

In this scenario, there is a row in LOCATION_TBL_LANG, but there is no row in DEPT_TBL_LANG. If you were to join LOCATION_TBL_LANG with DEPT_TBL_LANG in the view, no translation would be retrieved by the related language view because the department doesn’t have a translation.
An untranslated department may reference an untranslated location—for example, department 134 (Sales - Japan) and the location OSA (Osaka) are not translated into either French or German. The related language view should return no rows.

To address these scenarios, the SQL statement for the related language view must include logic for the first three scenarios—those where translations exist. Following is the SELECT statement for the related language view:

SELECT C.DEPT_ID, C.DESCR, B.LOCATION, B.DESCR, C.LANGUAGE_CD
FROM
  DEPT_TBL A,
  LOCATION_TBL_LANG B,
  DEPT_TBL_LANG C
WHERE A.DEPT_ID = C.DEPT_ID
  AND A.LOCATION = B.LOCATION
  AND C.LANGUAGE_CD = B.LANGUAGE_CD
UNION
SELECT F.DEPT_ID, F.DESCR, E.LOCATION, E.DESCR, F.LANGUAGE_CD
FROM  
  DEPT_TBL D, 
  LOCATION_TBL E,
  DEPT_TBL_LANG F
WHERE 
  F.DEPT_ID = D.DEPT_ID
  AND D.LOCATION = E.LOCATION
  AND NOT EXISTS (SELECT 'X' 
    FROM LOCATION_TBL_LANG H
    WHERE H.LOCATION = E.LOCATION
    AND H.LANGUAGE_CD = F.LANGUAGE_CD)
UNION
SELECT I.DEPT_ID, I.DESCR, J.LOCATION, J.DESCR, J.LANGUAGE_CD
FROM 
  DEPT_TBL I, 
  LOCATION_TBL_LANG J
WHERE
  I.LOCATION = J.LOCATION
  AND NOT EXISTS (SELECT 'X'
    FROM DEPT_TBL_LANG K
    WHERE K.DEPT_ID = I.DEPT_ID

This view is really three separate SQL statements whose output is concatenated using the SQL UNION operator. Each SELECT statement in the view addresses one of the first three scenarios previously described. Let’s examine this view, statement by statement.

SELECT Statement One

The following SELECT statement addresses scenario one. It retrieves the rows for which translations for both the department and the location exist.

SELECT C.DEPT_ID, C.DESCR, B.LOCATION, B.DESCR, C.LANGUAGE_CD
FROM
  DEPT_TBL A,
  LOCATION_TBL_LANG B,
  DEPT_TBL_LANG C
WHERE A.DEPT_ID = C.DEPT_ID
  AND A.LOCATION = B.LOCATION
  AND C.LANGUAGE_CD = B.LANGUAGE_CD

SELECT Statement Two

The following SELECT statement addresses scenario two. It retrieves the rows for which the department translation exists, but the location translation does not exist.

The sub-SELECT statement is required in order to prevent this statement from retrieving records that are returned by statement one.

SELECT F.DEPT_ID, F.DESCR, E.LOCATION, E.DESCR, F.LANGUAGE_CD
FROM  
  DEPT_TBL D,
  LOCATION_TBL E,
  DEPT_TBL_LANG F
WHERE 
  F.DEPT_ID = D.DEPT_ID
  AND D.LOCATION = E.LOCATION
  AND NOT EXISTS (SELECT 'X' 
    FROM LOCATION_TBL_LANG H
    WHERE H.LOCATION = E.LOCATION
    AND H.LANGUAGE_CD = F.LANGUAGE_CD)

SELECT Statement Three

The following SELECT statement addresses scenario three. It retrieves the rows for which translations exist for the location but not the department. Again, the sub-SELECT statement is needed to avoid returning rows that are returned by statement one.

SELECT I.DEPT_ID, I.DESCR, J.LOCATION, J.DESCR, J.LANGUAGE_CD
FROM 
  DEPT_TBL I, 
  LOCATION_TBL_LANG J
WHERE
  I.LOCATION = J.LOCATION
  AND NOT EXISTS (SELECT 'X'
    FROM DEPT_TBL_LANG K
    WHERE K.DEPT_ID = I.DEPT_ID
    AND K.LANGUAGE_CD = J.LANGUAGE_CD)