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 PeopleSoft 9.2 Application Installation for your database platform.

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.

If the key structure is not valid between the base and RLR table, subsequent SWAP checks may fail. So, you may need to correct the key structure before rerunning the SQR to check the other SWAP checks.

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

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

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.

Run the PTIACLEANLNG application engine program to clean orphan rows or 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';