Creating Related Language Views

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

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

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

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

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

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

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

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.

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_TBL with 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:

This diagram illustrates views of one base table, one related language table

As an example, DEPT_TBL contains the following data:

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

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.

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:

This diagram illustrates views of two base tables, one related language table

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

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

Finally, EMPLOYEE_TBL contains the following data:

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

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

The following SELECT statement is for the related language view:

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

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

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

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

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

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

• The name of the table from which it selects.

• The addition of the LANGUAGE_CD column.

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:

This diagram illustrates views 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:

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

LOCATION_TBL contains location codes and descriptions:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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