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 refers either to the PeopleSoft Pure Internet Architecture sign-in language or, in the case of PeopleSoft Application Designer, to the language preference as 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

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, description (DESCR) and short description (DESCRSHORT) 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 Action Reason table, ACTN_REASON_TBL, is one example of a base table. Note that it has DESCR and DESCRSHORT fields which would typically be language-sensitive. All other fields in the table are translate value or prompt table codes you can edit and are therefore not language sensitive. In the following example, the developer has decided that it is likely that each user would need to see the description of the Action Reason in their own language, and so a related language table is needed to store translations of DESCR and DESCRSHORT.

ACT_REASON_TBL record

The developer has therefore created a new record definition, ACTN_RSN_LANG, to act as the related language table for ACTN_REASON_TBL. Related Language records are defined in PeopleSoft 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 on the related language record can be deleted from the new record.

A related language table must:

The related language table, ACTN_RSN_LANG, meets all of these requirements:

ACT_RSN_LANG record

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.

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

Action

Action_Reason

Effdt

Descr

DescrLong

PAY

PRO

01/01/98

Promotion

Promotion

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

Action

Action_Reason

Effdt

Language_Cd

Descr

DescrLong

PAY

PRO

01/01/98

ESP

Promoción

Promoción

PAY

PRO

01/01/98

FRA

Promotion

Promotion

PAY

PRO

01/01/98

GER

Beförderung

Beförderung

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 page appears when the user’s language preference is Spanish (ESP). The Country field (País) prompts against the Country table (COUNTRY_TBL). The translated description (Francia) comes from the Country table's related language table (COUNTRYTBL_LANG).

Page with translated descriptive text

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 PeopleSoft-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, PeopleSoft provides translations of the user interface and key application data for many of our application products. Except for PeopleTools, these translations are shipped on a separate CD-ROM for each product line; this CD-ROM is known as the "Global Multi-Language CD-ROM." If you did not receive this CD-ROM in your kit, you may not have licensed the appropriate translations from PeopleSoft. Contact Customer Care to obtain the CD. Translations for PeopleTools itself are shipped on the PeopleTools CD; no Global Multi-Languge CD-ROM is required for PeopleTools.

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

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

To add translations licensed from PeopleSoft, you must determine how you will perform your upgrade. Choose the most appropriate option:

Click to jump to top of pageClick to jump to parent topicAdding Translations to Existing Databases

This section explains how to load translations by adding them to an existing database. This method of loading translations may not be available to you, depending on the customizations you have performed on your existing database.

Prerequisites for Adding Translations to Existing Databases

Before you add translations to an existing database:

Adding Translations to an Existing Database

To add translations to an existing database:

  1. Load your PeopleSoft Global Multi-Language CD-ROM.

    See Enterprise PeopleTools 8.49 Installation for your database platform, "Setting Up the File Server."

  2. Rerun the steps for setting up the batch environment.

    If you are running a Microsoft Windows NT or Microsoft Windows 2000 process scheduler (PSNT), then you must rerun the steps for "Setting Up the Batch Environment" documented in the Enterprise PeopleTools 8.49 installation guide for your database platform.

    Because the PeopleSoft Global Multi-Language CD-ROM contains translations for your Crystal Reports and PS/nVision layouts, you must transfer those translations to your Microsoft Windows NT or Microsoft Windows 2000 process scheduler.

    If you run only non-Microsoft Windows NT or Microsoft Windows 2000 process scheduler programs, 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 don't contain translations in the database are Crystal and PS/nVision reports, both of which are supported on only Microsoft Windows NT and Microsoft Windows 2000 batch servers.

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

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

  5. Copy the PPLTLSCURML PeopleSoft Application Designer project. (No English or COMMON)

    This project loads onto your system the current definitions and PeopleTools objects. When you run the project, deselect English language and COMMON objects. Choose only the language that you need.

  6. Copy the PPLDELETE PeopleSoft Application Designer project.

    This project removes definitions and objects that are not applicable to the current release.

  7. Copy the PATCH84xML PeopleSoft Application Designer project. (No English or COMMON).

    When you run the project, deselect English language and COMMON objects. Choose only the language that you need.

  8. Run the PT84xTLSxxx data mover script in User mode.

    The PT84xTLSxxx data mover script loads system data for the release in the target language. It also copies sample data onto your system, such as time zone sample data, currency sample data, and so on.

    Note that the xxx in the data mover script name denotes the language code. For example, PT84xTLSFRA.DMS is the French version of the data mover script.

  9. Run the MSGTLxxx data mover script in User mode.

    The MSGTLxxx data mover script loads message catalog entries onto the system in the target language.

    Note that the xxx in the data mover script name denotes the language code. For example, MSGTLFRA.DMS is the French version of the data mover script.

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

  11. Rerun your database audits.

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

See Enterprise PeopleTools 8.49 Installation for your database platform.

Click to jump to top of pageClick to jump to parent topicUpgrading Customizations into Multilingual Databases

This section explains how to load translations by upgrading your customizations into a multilingual database.

Prerequisites for Upgrading Customizations into Multilingual Databases

Before you upgrade customizations into multilingual databases:

Upgrading Customizations into a Multilingual Database

To upgrade your customizations into a multilingual database:

  1. Follow steps one and two in the previous section entitled Adding Translations to an Existing Database.

  2. 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 installation and administration documentation 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.

  3. Copy the customizations into the new multilingual database.

    Use the PeopleSoft Application Designer Upgrade Compare process to compare your customized database against a newly installed database in order to identify your changes. Once you have identified the objects you have changed, using the Compare process, use Upgrade Copy to copy the objects into your new Multi-Language database.

    If you have maintained all of your customizations in PeopleSoft Application Designer projects, you can easily use these projects to copy only the objects you have modified over to your new PeopleSoft Global Multi-Language database.

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

  5. 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, log in as 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.

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

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

See Also

Swapping the Base Language

Enterprise PeopleTools 8.49 Installation for your database platform

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.

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 Options page. To navigate to the Options page, select PeopleTools, Utilities, Use, 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 SQR process SWPAUDIT.SQR ensures that there are no data integrity problems that will cause errors during the swap process.

This SQR does not have a run control and, therefore, cannot be run with PeopleSoft Process Scheduler. For information on how to run SQRs manually in your environment, refer to the section on creating a database in your PeopleTools installation documentation.

SWPAUDIT.SQR may report one or more of the following errors. Refer to the following instructions to correct these errors before continuing with the swap process, and rerun the audit until it reports no errors.

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

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

To fix this problem, do one of the following:

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

To fix this problem, make LANGUAGE_CD into a key field on the related language record.

(SWAP-3) The Following Related Language View(s) Have the Wrong Key Structure Defined

The keys of a view used as a related language record by one or more records do not conform to the requirements for PeopleSoft related language records. The related language record must share all of the same keys as its base language record, plus an additional key (LANGUAGE_CD).

To fix this problem, correct the key structure to ensure that it conforms to this requirement. Then recreate the view in the database.

(SWAP-4) The Following Related Language Table(s) Have the Wrong Key Structure Defined

The keys of a table used as a related language record by one or more records do not conform to the requirements for PeopleSoft related language records. The related language record must share all of the same keys as its base language record, plus an additional key (LANGUAGE_CD).

To fix this problem, correct the key structure to ensure that it conforms to this requirement. Then alter the table in the database to match the new key structure.

(SWAP-5) The Following Related Language Table(s) Have the Wrong Structure Defined

The fields of a table used as a related language record by one or more records do not conform to the requirements for PeopleSoft related language records. The related language record must share all of the same fields as its base language record, plus an additional key (LANGUAGE_CD).

To fix this problem, correct the field structure to ensure that it conforms to this requirement. Then alter the table 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.

To fix this problem, use your platform's query tools to select against the related language table, using the fields listed in the report, where the values do not match a row on the base table. For every row on the report there is an orphan row on the 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 SQL code for a Microsoft SQL database. 

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> = B.<Field Name> ...
  for each field name listed)
AND LANGUAGE_CD = 'xxx'

Where 'xxx' is equal to the language code to which you are swapping.

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

To fix this problem, use PeopleSoft Application Designer to open the base record listed in the report and select a field that you want to make a key field. Right mouse click on the field and select Record Field Properties. In the dialog that pops up, select the Key check box and save the record. Then click on the Build bar item, select Current Definition, click Key, select Execute SQL Now, and click the Build button.

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

Enterprise PeopleTools 8.49 Installation 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 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 PeopleSoft 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:

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

    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. This is necessary because the view is created at the database-level outside of PeopleTools and cannot recognize when one or more of the tables it selects from has a related language table. If you create a view over a table that has a related language record and don’t create the corresponding related language view, any data selected from your view will appear only in the base language of the database.

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

Note. The related language record must match the base record type, either both tables or both 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.

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 language-sensitive view is straightforward: select the data from the related language table, making sure to also select LANGUAGE_CD.

This example shows a view on the CORP_SHIRT_TBL, a table keyed by SHIRT and with one language-sensitive field (DESCR):

Example of one base table, one related language table

Let’s assume you must create a view that selects only the rows where the SHIRT key field begins with the letter P.

Following is the SELECT statement for the base view:

SELECT SHIRT, DESCR FROM PS_CORP_SHIRT_TBL WHERE SHIRT LIKE 'P%'

Following is the select statement for the language-sensitive view:

SELECT SHIRT, DESCR, LANGUAGE_CD FROM PS_CORP_SHIRT_LANG WHERE SHIRT LIKE 'P%'

Because this 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, there would be no need to select those columns in the related language view because non-language-sensitive fields don’t need to be included 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.

This example shows a view that joins DEPT_TBL, which has a related language record, with PERSONAL_DATA, which does not:

Example of two base tables, one related language table

The following SELECT statement is for the base view that selects the department ID and description and the name of the manager for each department: 

SELECT A.DEPT_ID, A.DESCR, B.NAME
FROM PS_DEPT_TBL A, 
 PS_PERSONAL_DATA B 
WHERE A.MANAGER = B.EMPLID

The following SELECT statement is for the language-sensitive view:

SELECT A.DEPT_ID, A.DESCR, C.NAME, A.LANGUAGE_CD
FROM PS_DEPT_TBL_LANG A,
  PS_DEPT_TBL B,
  PS_PERSONAL_DATA C
WHERE A.DEPT_ID = B.DEPT_ID
AND B.MANAGER = C.EMPLID

In this case, the related language view can't get all the information from the related language table. This is because the MANAGER field, which is used to get the manager's name from PS_PERSONAL_DATA, is not language-sensitive and is therefore not part of the related language table.

To retrieve the MANAGER field, PS_DEPT_TBL_LANG needs to be joined to PS_DEPT_TBL.

Because the PERSONAL_DATA record does not have a related language table, no special logic is required for fetching the manager’s name because it is the same in all languages.

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.

This example shows a view that joins DEPT_TBL with ACCOUNT_TBL:

Example of two base tables, two related language tables

In this example, the base view joins DEPT_TBL with ACCOUNT_TBL to retrieve the description of the account. The following SELECT statement is for the base view: 

SELECT A.DEPT_ID, A.DESCR, B.ACCOUNT, B.DESCR
FROM PS_DEPT_TBL A, 
  PS_ACCOUNT_TBL B
WHERE A.ACCOUNT = B.ACCOUNT

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 ACCOUNT_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 language-sensitive view: 

SELECT A.DEPT_ID, A.DESCR, C.ACCOUNT, C.DESCR, A.LANGUAGE_CD
FROM PS_DEPT_TBL_LANG A, 
  PS_DEPT_TBL B,
  PS_ACCOUNT_TBL_LANG C
WHERE A.DEPT_ID = B.DEPT_ID
  AND B.ACCOUNT = C.ACCOUNT
  AND A.LANGUAGE_CD = C.LANGUAGE_CD
UNION
SELECT E.DEPT_ID, E.DESCR, G.ACCOUNT, G.DESCR, E.LANGUAGE_CD
FROM PS_DEPT_TBL_LANG E, 
  PS_DEPT_TBL F, 
  PS_ACCOUNT_TBL G
WHERE E.DEPT_ID = F.DEPT_ID
  AND F.ACCOUNT = G.ACCOUNT
  AND NOT EXISTS (SELECT ‘X’ 
    FROM PS_ACCOUNT_TBL_LANG H
    WHERE H.ACCOUNT = G.ACCOUNT
    AND H.LANGUAGE_CD = E.LANGUAGE_CD)
UNION
SELECT I.DEPT_ID, I,DESCR, J.ACCOUNT, J.DESCR, J.LANGUAGE_CD
FROM PS_DEPT_TBL I, 
  PS_ACCOUNT_TBL_LANG J
WHERE I.ACCOUNT = J.ACCOUNT
  AND NOT EXISTS (SELECT ‘X’
    FROM PS_DEPT_TBL_LANG K
    WHERE K.DEPT_ID = I.DEPT_ID
    AND K.LANGUAGE_CD = J.LANGUAGE_CD)

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.

Statement One

The following statement addresses scenario one. It retrieves the rows where translations for both the department and the account exist. 

SELECT A.DEPT_ID, A.DESCR, C.ACCOUNT, C.DESCR, A.LANGUAGE_CD
FROM PS_DEPT_TBL_LANG A, 
  PS_DEPT_TBL B,
  PS_ACCOUNT_TBL_LANG C
WHERE A.DEPT_ID = B.DEPT_ID
  AND B.ACCOUNT = C.ACCOUNT
  AND A.LANGUAGE_CD = C.LANGUAGE_CD

Statement Two

The following statement addresses scenario two; it addresses instances where the department translation exists, but the account translation does not exist.

The sub-SELECT statement is required in order to prevent this statement from retrieving records that have already been selected by the statements that address scenario one, where a translated account may exist. 

SELECT E.DEPT_ID, E.DESCR, G.ACCOUNT, G.DESCR, E.LANGUAGE_CD
FROM PS_DEPT_TBL_LANG E, 
  PS_DEPT_TBL F, 
  PS_ACCOUNT_TBL G
WHERE E.DEPT_ID = F.DEPT_ID
  AND F.ACCOUNT = G.ACCOUNT
  AND NOT EXISTS (SELECT ‘X’ 
    FROM PS_ACCOUNT_TBL_LANG H
    WHERE H.ACCOUNT = G.ACCOUNT
    AND H.LANGUAGE_CD = E.LANGUAGE_CD)

Statement Three

The following SELECT statement addresses scenario three, where translations exist for the account but not the department. Again, the sub-SELECT statement is needed to avoid returning rows that have already been selected by the other SELECT statements in the SQL statement, where a translated department exists. 

SELECT I.DEPT_ID, I,DESCR, J.ACCOUNT, J.DESCR, J.LANGUAGE_CD
FROM PS_DEPT_TBL I, 
  PS_ACCOUNT_TBL_LANG J
WHERE I.ACCOUNT = J.ACCOUNT
  AND NOT EXISTS (SELECT ‘X’
    FROM PS_DEPT_TBL_LANG K
    WHERE K.DEPT_ID = I.DEPT_ID
    AND K.LANGUAGE_CD = J.LANGUAGE_CD)