Related Language Integrity

The following table describes the audit queries and resolutions for this area:

Query Description Resolution

SYSLANG-01

Base language records that are found in the PSRECDEFNLANG table.

Run the following SQL:

DELETE FROM PSRECDEFNLANG
WHERE LANGUAGE_CD =
 (SELECT B.LANGUAGE_CD
  FROM PSOPTIONS B)

SYSLANG-02

Base language fields that are found in the PSDBFIELDLANG table.

Check the value of LANGUAGE_CD on PSOPTIONS; this is the base language. Entries with this language code are found in PSDBFIELDLANG. Base language entries should only be in PSDBFIELD.

After you establish that the base language entries in PSDBFIELD are correct, you delete them from PSDBFIELDLANG as follows:

DELETE FROM PSDBFIELDLANG
WHERE LANGUAGE_CD =
 (SELECT LANGUAGE_CD
  FROM PSOPTIONS)

SYSLANG-03

Foreign language records that are found in PSRECDEFNLANG table without related base records from PSRECDEFN.

Run the following SQL:

DELETE FROM PSRECDEFNLANG
WHERE NOT EXISTS
 (SELECT 'X' FROM PSRECDEFN B
 WHERE PSRECDEFNLANG.RECNAME =
  B.RECNAME) AND
 PSRECDEFNLANG.LANGUAGE_CD <>
 (SELECT C.LANGUAGE_CD
  FROM PSOPTIONS C)

SYSLANG-04

Foreign Language fields that are found in the PSDBFIELDLANG table without related Base Fields from PSDBFIELD.

Run the following SQL:

DELETE FROM PSDBFIELDLANG
WHERE NOT EXISTS
 (SELECT 'X'
  FROM PSDBFIELD B
  WHERE PSDBFIELDLANG.FIELDNAME=
   B.FIELDNAME)
AND
 PSDBFIELDLANG.LANGUAGE_CD <>
 (SELECT LANGUAGE_CD
  FROM PSOPTIONS)

SYSLANG-05

Foreign Language translate fields that are found in the XLATTABLE table without related base language translate fields.

Either delete the offending entries using SQL, or use Application Designer to add the equivalent entries in the base language of the database. To delete the offending entries using your platform query tool, first, perform a SELECT to verify the rows indicated in the report. Then, delete the selected rows.

SELECT A.LANGUAGE_CD, A.FIELDNAME,
A.FIELDVALUE, A.EFFDT
FROM PSXLATITEMLANG A
WHERE NOT EXISTS
  (SELECT 'X'
   FROM PSXLATITEM B
   WHERE A.FIELDNAME = B.FIELDNAME
   AND A.FIELDVALUE = B.FIELDVALUE
   AND A.EFFDT = B.EFFDT);

DELETE FROM PSXLATITEMLANG A
WHERE NOT EXISTS
  (SELECT 'X'
   FROM PSXLATITEM B
   WHERE A.FIELDNAME = B.FIELDNAME
   AND A.FIELDVALUE = B.FIELDVALUE
   AND A.EFFDT = B.EFFDT);

SYSLANG-07

Related Language records which are not valid records.

In Application Designer, delete the specified Related Language Records. Or, use your platform query tool to delete the related language reference for each record that is listed. First, perform a SELECT to verify the rows indicated in the report. Then, update the selected rows.

SELECT A.RECNAME, A.RELLANGRECNAME,
A.OBJECTOWNERID
FROM PSRECDEFN A
WHERE A.RELLANGRECNAME <> ' '
AND A.RELLANGRECNAME NOT IN
    (SELECT B.RECNAME FROM PSRECDEFN B)
ORDER BY A.RECNAME;

UPDATE PSRECDEFN SET RELLANGRECNAME = ' '
WHERE RELLANGRECNAME <> ' '
AND RELLANGRECNAME NOT IN
    (SELECT B.RECNAME FROM PSRECDEFN B);

SYSLANG-08

The displayed Related Language Records are effective-dated but do not have an EFFDT field defined.

In Application Designer, add EFFDT to the specified related language table.

SYSLANG-09

The displayed Related Language records point to another Related Language record.

In Application Designer, delete the related language reference for each record that is listed. Or, use your platform query tool to delete the related language reference for each record that is listed. First, perform a SELECT to verify the rows indicated in the report. Then, update the selected rows.

SELECT A.RECNAME, A.RELLANGRECNAME,
B.RELLANGRECNAME, A.OBJECTOWNERID
FROM PSRECDEFN A, PSRECDEFN B
WHERE A.RELLANGRECNAME <> ' '
AND A.RELLANGRECNAME = B.RECNAME
AND B.RELLANGRECNAME <>' '
ORDER BY A.RECNAME;

UPDATE PSRECDEFN SET RELLANGRECNAME=' '
WHERE RECNAME IN
(SELECT A.RELLANGRECNAME
FROM PSRECDEFN A, PSRECDEFN B
WHERE A.RELLANGRECNAME <> ' '
AND A.RELLANGRECNAME = B.RECNAME
AND B.RELLANGRECNAME <>' ');

SYSLANG-13

Identify related language records that have more than one base record defined.

In Application Designer, remove the related language table link to one of the base records.

SYSLANG-15

The displayed Related Language fields in the PSDBFLDLABLLANG table are orphaned.

Using the platform query tool, first, perform a SELECT to verify the rows indicated in the report. Then, delete the selected rows.

This is sample SQL. Adjust accordingly for your platform:

SELECT A.FIELDNAME, A.LANGUAGE_CD
FROM PSDBFLDLABLLANG A
WHERE NOT EXISTS
 (SELECT 'X'
  FROM PSDBFIELD B
  WHERE A.FIELDNAME =
   B.FIELDNAME)
ORDER BY 1,2

DELETE FROM PSDBFLDLABLLANG A
WHERE NOT EXISTS
 (SELECT 'X'
  FROM PSDBFIELD B
  WHERE A.FIELDNAME =
   B.FIELDNAME)

SYSLANG-16

Invalid language code found in PSOPRDEFN.

Using your SQL query tool, issue a SELECT to verify the rows indicated in the report. Update the affected rows as shown in the following sample SQL.

SELECT OPRID, LANGUAGE_CD
FROM PSOPRDEFN
WHERE OPRID = 'VP1'

UPDATE PSOPRDEFN
SET LANGUAGE_CD = 'ENG'
WHERE OPRID = 'VP1'