Using Related Language Tables

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

Install PeopleSoft-provided translations.

Swap the base language.

Create related language tables.

Create related language views.

Understanding Related Language Tables

This section discusses:

Related language tables overview.

Related language table structure.

How related language tables store transactions.

How related language tables are used.

Related Language Tables Overview

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

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

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

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

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

The current language preference.

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

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

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

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

System display of base and non-base language text

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

Note. PeopleTools automatically handles the storage and
display of data in related language tables and their synchronization with
corresponding entries in the base language tables based on each user’s
language preference. However, PeopleTools cannot automatically translate data!
The PeopleSoft system provides translations of PeopleSoft Pure Internet Architecture
components and user interfaces for most application products into several
languages. Also, translations of several key application data tables, such
as country and currency codes, are provided by the PeopleSoft system. However,
you will need to plan a way to translate application data entered by your
users that needs to be maintained in multiple languages. Some users who are
familiar with several languages may want to translate the data elements they
enter themselves using the Multilingual 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.

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

PSMSGCATDEFN table

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

A related language table must:

Share all key record fields with its base table.

Have an additional key record field, which must be LANGUAGE_CD.

Have language-sensitive fields matching those in the base
table. These are typically DESCR, DESCRSHORT or
DESCRLONG; or LONGNAME or SHORTNAME.

Not contain any fields that are not present on the base
table, except the LANGUAGE_CD key field.

Not contain any non-key, non-language-sensitive fields from
the base table.

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

PSMSGCATLANG table

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

To associate a base table with its related language table:

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

Click the Use tab.

Under Record Relationships, in the Related
Language Record field enter the name of the related language
table.

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

Click OK.

How Related Language Tables Store Translations

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

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

MESSAGE_SET_NBR

MESSAGE_NBR

MESSAGE_TEXT

MSG_SEVERITY

DESCRLONG

1

1

Distributed Object Manager: Help Name=%1 Language=%2

M

A Help Text object is being retrieved from the database. This message
is for your information only. It is not an error or a warning.

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

How Related Language Tables Are Used

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

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

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

Page with translated descriptive text (Message Catalog page)

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

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

Translation of fields in ADDRESS2_SBP

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

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

Installing Oracle-Provided Translations

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

Add translations to an existing database on the same PeopleTools
version.

Add translations to an existing database on a different
PeopleTools version.

Use the upgrade process to copy customizations into a multilingual
database.

Understanding Translation Installation

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

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

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

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

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

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

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

Adding or customizing system data, such as account codes, departments,
and locations, is not considered a PeopleTools object change. If you have
performed only these types of transactions against your existing database, and you
have not modified any objects using Application Designer or PeopleSoft Tree
Manager, then you can use this option.

Add the translations provided on the PeopleSoft Global Multi-Language
CD to an existing database on a different PeopleTools version.

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

Use the upgrade process to copy your customizations into
a multilingual database.

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

Adding Translations to an Existing Database on the Same PeopleTools
Version

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

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

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

Rerun the steps for setting up the batch environment.

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

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

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

See the PeopleTools 8.52 installation guide for your database
platform, “Setting Up the Batch Environment.”

Generate database setup from PeopleSoft Data Mover.

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

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

Run the PeopleSoft Data Mover script.

Because you have already created your database and set up the appropriate
database-specific objects, you must perform only the “Run Data Mover
Import Scripts” task.

Run the VERSION Application Engine program to update your
PeopleTools version numbers.

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

Rerun your database audits.

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

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

Adding Translations to an Existing Database on a Different PeopleTools
Version

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

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

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.

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

Apply this compare project to the target database.

Export application system data.

In Data Mover, export 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.

Run the VERSION Application Engine program.

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

Rerun your database audits.

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

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

Using the Upgrade Process to Copy Customizations into a Multilingual
Database

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

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

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

Rerun the steps for setting up the batch environment.

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

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

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

See the PeopleTools 8.52 installation guide for your database
platform, “Setting Up the Batch Environment.”

Create the new multilingual database.

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

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

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

Copy the customizations into the new multilingual database.

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

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

Create a project with all objects.

This project will be used in the next step to upgrade customizations
and translations into the test or production database.

Load changes in the test database.

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.

Swapping the Base Language

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

Run the swap audit report.

Check space and resources.

Run the SWAP_BASE_LANGUAGE Data Mover command.

Understanding Base Language Swapping

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

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.

Prerequisites

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

A clean audit report to ensure that there are no data integrity
problems.

Sufficient space and resources to run the swapping process.

Running the Swap Audit Report

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

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

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

Audit

Error Description

Resolution

SWAP-1

Related Language Records That are not Valid Records

The records listed are defined as related language records for one or
more base records, but they do not exist in your database.

Do one of the following:

Create the related language record, as appropriate, based
on the keys and translatable fields of the base record.

Open the base language record and remove its association
with the related language record in the Object Properties dialog box.

SWAP-2

The Field LANGUAGE_CD is not a Key in the Following Related
Language Record(s)

A record is defined as a related language record, but it does not have LANGUAGE_CD as
a component of its key. LANGUAGE_CD must be a
key field on every related language record.

Make LANGUAGE_CD into a key field on each
specified related language record.

SWAP-3

The Following Related Language View(s) Have the Wrong Structure
Defined

A related language record must share all of the same keys as its base
language record, plus an additional key field (LANGUAGE_CD). Also, any non-key
fields must also be in the base language record.

Correct the field structure for the specified related language views
to ensure that they conform to this requirement. Then, recreate the views
in the database.

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';

Checking Space and Resources

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

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

Running the SWAP_BASE_LANGUAGE Data Mover Command

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

SWAP_BASE_LANGUAGE ​target_language;

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

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

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

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

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

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

SET BASE_LANGUAGE ​target_language;
SWAP_BASE_LANGUAGE ​recname;

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

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

SET IGNORE_ERRORS;
SWAP_BASE_LANGUAGE ​LANGUAGE_CD;

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

See Also

PeopleTools 8.52 installation guide for your database
platform

Creating Related Language Tables

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

A one-to-one relationship 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:

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

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

Design the related language record.

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

The related language record must:

Have all the same key fields as the base table.

Have an additional key field, which must be LANGUAGE_CD.

Have the language-sensitive fields from the base table.

Not contain any fields that are not present on the base
table except the LANGUAGE_CD key field.

Not contain any non-key, non-language-sensitive fields from
the base table.

Build the related language table.

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

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

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

Associate the related language table with the base table
record.

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

Click OK to close the Record Properties
dialog box.

Save the record.

Creating Related Language Views

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

Understanding Related Language Views

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

Any time you create a view over a table that has a related
language record, you typically also need a related language view.

The related language view consists of, at a minimum, all
the key fields from the base view, a language code, and the language-sensitive
fields.

You associate the base view and the related language view
in the Record Properties dialog box of the base view.

When a user logs on in a non-base language, the PeopleSoft
system retrieves the data with the appropriate language code from the related
language view.

It’s best to use the same naming convention for related
language views as you do for related language records, such as prepending
L or _LANG to the _VW suffix of the view name.

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

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

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

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

Creating Views for One Base Table and One Related Language Table

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

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

Example of one base table, one related language table

As an example, DEPT_TBL contains the following data:

DEPT_ID

DESCR

MGR_ID

110

Finance

01732

120

Engineering

22056

131

Sales - Belgium

08630

132

Sales - Germany

08630

133

Sales - UK

12972

134

Sales - Japan

28553

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

DEPT_ID

LANGUAGE_CD

DESCR

110

FRA

Finances

110

GER

Finanzierung

120

FRA

Technologie

120

GER

Technik

131

FRA

Ventes - Belgique

132

GER

Verkäufe - Deutschland

133

GER

Verkäufe - GB

The following is the SELECT statement for the base view:

SELECT A.DEPT_ID, A.DESCR FROM DEPT_TBL A

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

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

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

The name of the table from which it selects.

The addition of the LANGUAGE_CD column.

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

Creating Views for Two Base Tables and One Related Language Table

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

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

Example of two base tables, one related language table

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

DEPT_ID

DESCR

MGR_ID

110

Finance

01732

120

Engineering

22056

131

Sales - Belgium

08630

132

Sales - Germany

08630

133

Sales - UK

12972

134

Sales - Japan

28553

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

DEPT_ID

LANGUAGE_CD

DESCR

110

FRA

Finances

110

GER

Finanzierung

120

FRA

Technologie

120

GER

Technik

131

FRA

Ventes - Belgique

132

GER

Verkäufe - Deutschland

133

GER

Verkäufe - GB

Finally, EMPLOYEE_TBL contains the following data:

EMPL_ID

LAST_NAME

01732

Jones

08630

Gräff

12972

Smythe

17145

De Bruecker

22056

Agarwal

28553

Katsuhiro

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

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):

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):

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

The name of the table from which it selects.

The addition of the LANGUAGE_CD column.

Creating Views for Two Base Tables and Two Related Language Tables

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

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

Example of two base tables, two related language tables

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

DEPT_ID

DESCR

LOCATION

110

Finance

RWS

120

Engineering

RWS

131

Sales - Belgium

BRU

132

Sales - Germany

MUN

133

Sales - UK

LON

134

Sales - Japan

OSA

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

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

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

A translated department may reference a translated location—for
example, the German for department 132 (Verkäufe – Deutschland)
references the German for location MUN (München).

A translated department may reference an untranslated location—for
example, the French for department 120 (Technologie) does not have a translated
location and therefore references the untranslated location for RWS (Redwood
Shores).

In this scenario, there is a row in DEPT_TBL_LANG, but there is no row
in LOCATION_TBL_LANG. If you were to join DEPT_TBL_LANG with LOCATION_TBL_LANG
in the view, no translation would be retrieved by the related language view
because the location doesn’t have a translation.

A translated location may reference an untranslated department
—for example, the German for location BRU (Brüssel) does not have
a translated department and therefore references the untranslated department
for 131 (Sales - Belgium).

In this scenario, there is a row in LOCATION_TBL_LANG, but there is
no row in DEPT_TBL_LANG. If you were to join LOCATION_TBL_LANG with DEPT_TBL_LANG
in the view, no translation would be retrieved by the related language view
because the department doesn’t have a translation.

An untranslated department may reference an untranslated
location—for example, department 134 (Sales - Japan) and the location
OSA (Osaka) are not translated into either French or German. The related
language view should return no rows.

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

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.

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.