Using Related Language Tables

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

Click to jump to parent topicUnderstanding Related Language Tables

This section discusses:

Click to jump to top of pageClick to jump to parent topicRelated 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 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 Data Entry 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

Click to jump to top of pageClick to jump to parent topicRelated 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:

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:

  1. Open the Record Properties dialog box for the base table record.

  2. Click the Use tab.

  3. 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.

  4. Click OK.

Click to jump to top of pageClick to jump to parent topicHow 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.

Click to jump to top of pageClick to jump to parent topicHow 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.

See Also

Working With Language-Sensitive Application Data

Translating Application Definitions

Click to jump to parent topicInstalling Oracle-Provided Translations

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

Click to jump to top of pageClick to jump to parent topicUnderstanding 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:

Click to jump to top of pageClick to jump to parent topicAdding 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:

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

    See Understanding Translation Installation.

  2. Back up your database.

  3. Load your PeopleSoft Global Multi-Language CD.

    See the PeopleTools 8.52 installation guide for your database platform, “Setting Up the Windows File Server.”

  4. 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 PeopleTools 8.52 installation guide for your database platform, “Setting Up the Batch Environment.”

  5. 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.

  6. 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.

  7. 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.

  8. 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 PeopleTools 8.52 installation guide for your database platform, “Creating a Database.”

Click to jump to top of pageClick to jump to parent topicAdding 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:

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

    See Understanding Translation Installation.

  2. Back up your database.

  3. 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 PeopleTools 8.52 installation guide for your database platform, “Updating Database to Latest PeopleTools Release.”

  4. 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.

  5. Apply this compare project to the target database.

  6. Export application system data.

    In Data Mover, export translated application system data (non managed objects) from the source 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 involved in determining the system data table names are PS_PM_PS_REC and PSRECDEFN.RELLANGRECNAME. PeopleTools system data was already loaded via the PT84xTLSyyy.dms scripts.PS_PM_PS_REC and PS_PM_PS_REC_WHERE define which application data are considered to be system data. These two tables contain base table names, whereas you want to export your data from the related language tables.Determine the related language table name from PSRECDEFN.RELLANGRECNAME.

    For example, one table returned by SELECT * FROM PS_PM_PS_REC is the base table name COUNTRY_TBL.

    To find the related language table for COUNTRY_TBL, run this select statement:

    SELECT RELLANGRECNAME FROM PSRECDEFN WHERE RECNAME = 'COUNTRY_TBL';

    The related language table is COUNTRYTBL_LANG

    In Data Mover, export the translated data from the related language table for the languages being added:

    EXPORT COUNTRYTBL_LANG WHERE LANGUAGE_CD IN('ESP');

    Then in Data Mover, import the translated system data into the target database

    IMPORT COUNTRYTBL_LANG;

    This process is for GA transactions only. Any patched translations need to be applied individually.

  7. 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.

  8. 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 PeopleTools 8.52 installation guide for your database platform, “Creating a Database.”

Click to jump to top of pageClick to jump to parent topicUsing 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:

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

    See Understanding Translation Installation.

  2. Back up your database.

  3. Load your PeopleSoft Global Multi-Language CD.

    See the PeopleTools 8.52 installation guide for your database platform, “Setting Up the Windows File Server.”

  4. 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 PeopleTools 8.52 installation guide for your database platform, “Setting Up the Batch Environment.”

  5. 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 PeopleTools 8.52 installation guide for your database platform, “Creating a Database.”

  6. 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.

  7. 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.

  8. 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.

  9. After testing, repeat the previous steps to load changes in the production database.

  10. 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 PeopleTools 8.52 installation guide for your database platform, “Creating a Database.”

See Also

Swapping the Base Language

Click to jump to parent topicSwapping the Base Language

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

Click to jump to top of pageClick to jump to parent topicUnderstanding 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.

There is a slight performance increase for the base language over the non-base languages, but there is extra administrative over head required when maintaining a swapped base environment. Take both of these into account if you are deciding whether to swap the base language.

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.

Click to jump to top of pageClick to jump to parent topicPrerequisites

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

Click to jump to top of pageClick to jump to parent topicRunning 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.

Important! In PeopleTools 8.52, SWPAUDIT will report an error for the PSADSDEFNSRC_VW record and its related language record, PSADSDFNLNG_VW, but this related language table will always be empty. In PeopleTools 8.52, the error can be safely ignored.

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.

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.

SWAP-9

The Following Translated BI Publisher Template Files Are Orphans

An orphaned translated BI Publisher template file is a file that does not have a corresponding base template file. For each translated BI Publisher template file, there must be a base BI Publisher template file with matching keys. You must delete the row for each orphaned translated BI Publisher template file.

Use your platform’s query tool to select against the tables storing translated BI Publisher template file using the fields listed in the report. 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.

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 PSXPTMPLTRINFO A WHERE NOT EXISTS (SELECT 1 FROM PSXPTMPLFILEDEF B WHERE A.TMPLDEFN_ID = B.TMPLDEFN_ID AND A.EFFDT = B.EFFDT) AND A.TMPLLANGCD = 'xxx';

You must delete the orphan rows from PSFILEDEFN and PSFILEDATA before you delete the orphan rows from PSXPTMPLTRINFO.

DELETE FROM PSFILEDEFN WHERE FILEID IN (SELECT XLIFF_FILEID FROM PSXPTMPLTRINFO A WHERE NOT EXISTS (SELECT 1 FROM PSXPTMPLFILEDEF B WHERE A.TMPLDEFN_ID = B.TMPLDEFN_ID AND A.EFFDT = B.EFFDT) AND TMPLLANGCD='xxx'); DELETE FROM PSFILEDATA WHERE FILEID IN (SELECT XLIFF_FILEID FROM PSXPTMPLTRINFO A WHERE NOT EXISTS (SELECT 1 FROM PSXPTMPLFILEDEF B WHERE A.TMPLDEFN_ID = B.TMPLDEFN_ID AND A.EFFDT = B.EFFDT) AND TMPLLANGCD='xxx'); DELETE FROM PSXPTMPLTRINFO WHERE NOT EXISTS (SELECT 1 FROM PSXPTMPLFILEDEF B WHERE PSXPTMPLTRINFO.TMPLDEFN_ID = B.TMPLDEFN_ID AND PSXPTMPLTRINFO.EFFDT = B.EFFDT) AND TMPLLANGCD='xxx';

Click to jump to top of pageClick to jump to parent topicChecking 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.

Click to jump to top of pageClick to jump to parent topicRunning 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

PeopleTools 8.52 installation guide for your database platform

Click to jump to parent topicCreating 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 is recommended between the base record and the related language record. In addition, both base and related language records are recommended to 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), 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).

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 remove the order keys. Do not create a related language record with a duplicate order key.

To create a related language table:

  1. 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.

  2. 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:

  3. 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.

  4. 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.

  5. Save the record.

Click to jump to parent topicCreating Related Language Views

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

Click to jump to top of pageClick to jump to parent topicUnderstanding 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:

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.

Click to jump to top of pageClick to jump to parent topicCreating 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:

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.

Click to jump to top of pageClick to jump to parent topicCreating 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:

Click to jump to top of pageClick to jump to parent topicCreating 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:

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)