Ensuring Data Integrity

This chapter provides an overview of data integrity tools and discusses how to:

Click to jump to parent topicUnderstanding Data Integrity Tools

PeopleSoft provides several tools to ensure the integrity of the data that is stored in the PeopleSoft system, such as SQL Alter , SYSAUDIT, and DDDAUDIT. You may want to use these tools during upgrades and system customizations, to verify the PeopleSoft system and check how it compares to the actual SQL objects.

It is good practice to run and read the audit reports, which include SYSAUDIT, SYSAUD01, DDDAUDIT and ALTER audit, after making changes such as:

Running the audits helps you to make sure that the tables are internally and externally synchronized.

Furthermore, it is recommended that you schedule regular maintenance runs of these audits, for example weekly, enabling you to discover and resolve any data integrity issues in a timely manner.

Click to jump to parent topicRunning SQL Alter

The primary purpose of the Application Designer SQL Alter function is to bring SQL tables into accordance with PeopleTools record definitions. You can run SQL Alter in an audit-only mode that alerts you to discrepancies between record definitions and SQL tables, but that doesn't actually perform an alter.

To audit tables or views:

  1. In Application Designer, choose the records that you want to audit.

    You have the option of auditing the active record definition, the selected records in the project workspace, or all the records that are in the current project.

  2. Select the Build menu and select the appropriate option for the records that you want to audit.

    If you're auditing an open record definition, choose Build, Current Object. If you select one or more records in the project workspace, you can select Build, Selected Objects. If you want to audit all records in the current project, select Build, Project.

    The Build Scope shows a list of all the records that are affected, or audited in the case.

  3. Select Alter tables as the Build Option and select Build script file as the Build Execute option.

  4. Click Settings and choose the Alter tab in the Build Settings dialog.

  5. In the Alter Any group box, select the situations for which you want an Alter performed.

  6. Select the Scripts tab.

    You use the Scripts tab to specify the output for the build scripts in one file, in two files, where the file is generated, and so on.

  7. Select Write Alter comments to script.

    Performing alters with this option enabled adds comments to the SQL script about what fields are being manipulated.

  8. Choose the other script file options.

  9. Click OKto close the Build Settings dialog and return to the Build dialog.

  10. Press Buildon the Build dialog.

Click to jump to top of pageClick to jump to parent topicUnderstanding Table and Column Audits

The SELECT statements that are produced by auditing with SQL Alter deal with inconsistencies between PeopleTools tables and SQL in the definition of tables or columns. A SQL table is equivalent to a record in Application Designer, and a column is equivalent to a field.

To fix problems that are found in the system tables and columns, you need to know how PeopleSoft field types correspond to SQL data types:

Application Designer Field Type

SQL Data Type

SQL Description

Character

CHAR

Alphanumeric; fixed length.

Long character

LONGVAR

Alphanumeric; variable length.

Date

DATE

Dates; stored as fixed length; displayed in various formats.

Number or signed number

SMALLINT

Numeric; integers only (no decimals); 1 to 4 digits (and 5 digits if RawBinary).

Number or signed number

INTEGER

Numeric; integers only (no decimals); 5 to 9 digits (and 10 digits if RawBinary).

Number or signed number

DECIMAL

Numeric; either (1) 10 or more digits or (2) contains decimal positions.

Note. In Application Designer, if a field is specified as required, or if a field is numeric and does not have a format of Phone, SSN (social security number), or SIN, you need to initialize the starting value of the column and specify the NOT NULL attribute in SQL.

Click to jump to parent topicRunning DDDAUDIT

This section discusses DDDAUDIT queries.

The Database Audit Report (DDDAUDIT) finds inconsistencies between PeopleTools record and index definitions and the database objects. This audit consists of queries that check tables, views, indexes, and triggers.

DDDAUDIT.SQR. is located in PS_HOME\sqr.

When you run DDDAUDIT.SQR, its results are written to a file called DDDAUDIT.LIS in the \TEMP folder. After running DDDAUDIT, view the .LIS file by using any text editor.

Click to jump to top of pageClick to jump to parent topicDDDAUDIT Queries

The following table lists the names of each query that DDDAUDIT performs on the PeopleSoft system, what it means if rows are returned, and how to resolve the inconsistency.

Note. The query names in this table are arranged alphabetically, and are not necessarily in the order in which they appear in DDDAUDIT.LIS:

Query

If Rows are Returned?

Resolution

INDEX-1

Indexes are defined in Application Designer and not found in the database.

Use Application Designer to create the index.

INDEX-2

Indexes are defined in the database and not found in Application Designer.

If the index is valid, use Application Designer to define the index.

Otherwise, drop the index.

INDEX-3

Uniqueness or the number of keys in the Index Definition do not match between Application Designer and the database.

See INDEX-1.

TABLE-1

SQL table names are defined in the Data Designer that are not blank and not the same as the record name.

Use Application Designer to enter the record name as the Non-Standard SQL Table Name.

TABLE-2

SQL tables are defined in the Data Designer and not found in the database.

If you want to delete the record definition, use Application Designer (select File, Delete).

Otherwise, to create the SQL table, use Application Designer. This command also creates the appropriate indexes for keys, duplicate order keys, alternate keys, and list items.

TABLE-3

SQL tables are defined in the database and not found in the Data Designer.

SYSINDEXES and SYSTABLES can be ignored in these results.

For Informix: PSALTERLONG can also be ignored.

If the table is not valid, drop it.

Otherwise, define a new record in Application Designer.

TABLE-4

Tablespace is not defined for the SQL table in Application Designer.

If you're using or migrating to a relational database management system that uses table spaces, you should use Application Designer to assign table spaces to these tables.

TABLE-5

Table contains more than 500 fields.

Use Application Designer to adjust the number of fields on the table, as needed.

VIEWS-1

Views are defined in the Data Designer and not found in the database.

If you want to delete the view definition, use Application Designer (select File, Delete).

Otherwise, to create the SQL view, use Application Designer.

VIEWS-2

Views are defined in the database and not found in the Data Designer.

If the view is not valid, Drop it.

Otherwise, define a new view in Application Designer.

TRIGGER-1

Trigger defined in the Application Designer and not found in the database.

Delete the definition if it is not needed.

Otherwise, use Application Designer to create the trigger in the database.

Click to jump to parent topicRunning SYSAUDIT

This section provides an overview of how to run SYSAUDIT and discusses audits for:

Click to jump to top of pageClick to jump to parent topicUnderstanding How to Run SYSAUDIT

The System Audit (SYSAUDIT) identifies orphaned PeopleSoft objects and other inconsistencies within the system. An example of an orphaned object is a module of PeopleCode that exists, but which does not relate to any other objects in the system.

Select PeopleTools, Utilities, Audit, Perform System Audit. Select the appropriate check boxes to run the audits that you want.

Audit AE Integrity

Audits PeopleSoft Application Engine program definitions and components.

Audit Clear List Integrity

Audits the SYSCLRLIST* component.

Audit EDI Manager Integrity

Audits the EC* component for EDI Manager.

Audit Field Integrity

Audits the DBFLD* component for Application Designer fields.

Audit Integration Broker

Runs a collection of audits to on the Integration Broker configuration.

Audit Menu Integrity

Audits the MENU* component for Application Designer menus.

Audit Optimization Integrity

Audits the definitions for Optimization Engine.

Audit Page Integrity

Audits the PNL* component for Application Designer pages.

Audit PeopleCode Integrity

Audits the PCM* and PRG* components for PeopleCode programs.

Audit Query Integrity

Audits the QRY* component for PeopleSoft Query.

Audit Record Integrity

Audits the REC* and VIEWT* components for Application Designer records.

Audit Related Lang Integrity

Audits Related Language Integrity. Query the *LANG component.

Audit Security Integrity

Audits the AUTH*, OPRDF* components for PeopleTools Security.

Audit SQL Integrity

Audits the referential integrity of the tables supporting SQL objects in the db component.

Audit Tree Integrity

Audits the TREE* component.

Audit Translates Integrity

Audits the XLAT* component.

Audit PSLOCK Version Integrity

Audits the VERSN* component.

Audit BI Publisher Integrity

Audits the referential integrity of the tables of the definitions that are associated with BI Publisher.

Audit Style Sheet Integrity

Audits the referential integrity of the tables of the definitions that are associated with style sheets.

Audit Feeds Integrity

Audits the referential integrity of the tables of the definitions that are associated with feeds.

Audit Connected Query Integrity

Audits the referential integrity of the tables of the definitions that are associated with connected query.

Audit Search Integrity

Audits the referential integrity of the tables and definitions associated with the PeopleSoft Search Framework and PeopleSoft Search.

Audit Rel. Content Integrity

Audits the referential integrity of the tables and definitions associated with the Related Content.

To run SYSAUDIT:

  1. Select PeopleTools, Utilities, Audit, Perform System Audit.

  2. When prompted, enter a new run control ID and click OK.

  3. Select the desired Integrity Audit options.

  4. Click Run.

  5. Select the appropriate settings on the Process Scheduler Request page, and click .OK.

Accessing SYSAUDIT Output

When you run SYSAUDIT, you can specify the type and format of the output on the Process Scheduler Request page, as you can with any Process Scheduler request. By default, the results are written to the configured report repository as an Adobe Acrobat PDF file called SYSAUDIT_runctrl_ID.pdf, where runctrl_ID is the run control ID you specified for the audit.

The tables in the following sections list the names of each of the audit queries that SYSAUDIT performs on the PeopleSoft system, what it means if rows are returned, and how to resolve the discrepancies that the audit report uncovers.

Note. The query names in these tables are arranged alphabetically, and are not necessarily in the order in which they appear in the output.

See Also

Scheduling Process Requests

Click to jump to top of pageClick to jump to parent topicApplication Engine Integrity

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

Query

Description

Resolution

AE-01

This audit lists the AE programs without any sections.

If the affected program is delivered by PeopleSoft and is not modified, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

Otherwise, use the Application Engine designer to either create valid sections for the program or remove the program. It is not possible to recover the missing sections.

AE-02

This audit lists the AE sections without AE programs.

If the affected program is delivered by PeopleSoft and is not modified, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

If the affected program is a customization, it is not possible to recover the missing program. Restore it from a backup if needed.

Run SysAECleanUp.dms to remove any orphans remaining after you have followed the steps above.

AE-03

This audit lists the AE state records without AE programs.

If the affected record is delivered by PeopleSoft, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

Otherwise, ignore the warnings or restore the program from a backup. It is not possible to recover the missing program.

AE-04

This audit lists the AE state records without record definitions.

If the affected record is delivered by PeopleSoft, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

Otherwise, use Application Designer to remove invalid records from the program definition or create record definitions.

AE-05

This audit lists the AE section details without base section definitions.

If the affected program is delivered by PeopleSoft and is not modified, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

Otherwise, ignore the warnings or restore the program from a backup. It is not possible to recover the missing sections.

AE-06

This audit lists the AE steps without sections.

If the affected program is delivered by PeopleSoft and is not modified, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

If the affected program is a customization, it is not possible to recover the missing program. Restore it from a backup if needed.

Run SysAECleanUp.dms to remove any orphans remaining after you follow the steps above.

AE-07

This audit lists the AE Call Section actions referring to nonexistent sections

If the affected program is delivered by PeopleSoft and is not modified, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

Otherwise, use the Application Engine either to open the program containing the Call Section and change it to call the correct section, or create the required section.

AE-08

This audit lists the AE Log Message actions without an AE step.

If the affected record is delivered by PeopleSoft, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

If the affected program is a customization, it is not possible to recover the missing program; restore it from a backup if needed.

Run SysAECleanUp.dms to remove any orphans remaining after you follow the steps above.

AE-09

This audit lists the AE actions without an AE step.

If the affected record was delivered by PeopleSoft, contact the GCS.

If the affected program was converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

If the affected program is a customization, it is not possible to recover the missing program; restore it from a backup if needed.

Run SysAECleanUp.dms to remove any orphans remaining after you follow the steps above.

AE-10

This audit lists the AE temp tables that are attached to invalid AE programs.

If the affected temp table was delivered by PeopleSoft, contact the GCS.

If the affected program is converted as part of an upgrade, this may be a symptom that the conversion failed. Contact the GCS.

Otherwise, ignore the warnings or restore the program from a backup. It is not possible to recover the missing programs.

AE-11

This audit lists the orphaned AE PeopleCode.

Because of platform issues and Structured Query Report (SQR), this check may not be included in the audit report. Run SysAECleanUp.dms to clean up these orphans.

AE-12

This audit lists the orphaned AE SQL objects.

Because of platform issues and SQR, this check may not be included in the audit report. Run SysAECleanUp.dms to clean up these orphans.

AE-13

This audit verifies that PS_AEONLINEINST contains the correct number of rows.

Run ps_aeonlineinst.dms. If you do not have the Data Mover script, contact the GCS.

AE-14

This audit verifies that PS_AEINSTANCENBR contains the correct number of rows.

Run ps_aeinstancenbr.dms. If you do not have the Data Mover script, contact the GCS.

AE-15

This audit verifies that PS_AELOCKMGR contains a row .

Resolution may vary based on implementation. Contact the GCS.

Note. Locate the Data Mover scripts in the PS_HOME\scripts directory unless otherwise noted.

Click to jump to top of pageClick to jump to parent topicClear List Integrity

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

Query

Description

Resolution

SYSCLRLIST-01

Entries in PSACTIVITYDEL and PSACTIVITYDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-02

Entries in PSAEAPPLDEL and PSAEAPPLDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-05

Entries in PSCOLORDEL and PSCOLORDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-06

Entries in PSFMTDEL and PSFMTDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-07

Entries in PSHOLIDAYDEL and PSHOLIDAYDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-09

Entries in PSIMPDEL and PSIMPDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-10

Entries in PSMENUDEL and PSMENUDEFN are not mutually exclusive

Run the VERSION Application Engine program.

SYSCLRLIST-11

Entries in PSPCMPROGDEL and PSPCMPROG are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-12

Entries in PSPNLDEL and PSPNLDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-13

Entries in PSPNLGRPDEL and PSPNLGRPDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-14

Entries in PSPRCSRUNCDEL and PSPRCSRUNCNTL are not mutually exclusive

Run the VERSION Application Engine program.

SYSCLRLIST-15

Entries in PSPROJECTDEL and PSPROJECTDEFN are not mutually exclusive

Run the VERSION Application Engine program.

SYSCLRLIST-16

Entries in PSQRYDEL and PSQRYDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-17

Entries in PSRECDEL and PSRECDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-18

Entries in PSRECURDEL and PS_PRCSRECUR are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-19

Entries in PSSTYLEDEL and PSSTYLEDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-20

Entries in PSTOOLBARDEL and PSTOOLBARDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-21

Entries in PSTREEBRADEL and PSTREEBRANCH are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-22

Entries in PSTREEDEL and PSTREEDEFN are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-23

Entries in PSTREESTRDEL and PSTREESTRCT are not mutually exclusive.

Run the VERSION Application Engine program.

SYSCLRLIST-24

Entries in XLATTABLEDEL and XLATTABLE are not mutually exclusive.

Run the VERSION Application Engine program.

Click to jump to top of pageClick to jump to parent topicConnected Query Integrity

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

Query

Description

Resolution

SysConqrs-01

Identifies Connected Query definitions in the PSCONQRSMAP table that do not exist in the PSCONQRSDEFN table.

Delete the invalid definition from the PSCONQRSMAP table and the PSCONQRSFLDREL table (if it exists).

SysConqrs-02

Identifies Connected Query definitions in the PSCONQRSDEFN table that do not exist in the PSCONQRSMAP table.

Delete the invalid definition using Connected Query Manager.

SysConqrs-03

Identifies Connected Query definitions in the PSCONQRSFLDREL table that do not exist in the PSCONQRSMAP table.

Delete the invalid definition using Connected Query Manager or delete invalid definition directly from the PSCONQRSFLDREL table and the PSCONQRSDEFN table (if it exists).

SysConqrs-04

Identifies Connected Query definitions being used in the PSCONQRSRUNCNTR table that do not exist in the PSCONQRSDEFN table.

Delete the invalid definition from the PSCONQRSRUNCNTR table and the PSCONQRSRUNPRM table (if it exists).

SysConqrs-05

Identifies Connected Query definitions being used in the PSCONQRSRUNPRM table that do not exist in the PSCONQRSRUNCNTR table.

Delete the invalid definition from the PSCONQRSRUNPRM table.

SysConqrs-06

Verifies query definition usage according to the following rules:

  • Query definitions being used in PSCONQRSMAP table should exist in PSQRYDEFN table.

  • Only public query definitions should be used for public Connected Query definitions.

  • Private query definitions can be used for private Connected Query definitions only if they have the same owner.

If the query referenced in a Connected Query definition does not exist, delete it or open it in the Connected Query Manager and correct query selection and file mapping.

If a public Connected Query uses a private query, delete it or open it in the Connected Query Manager and correct query selection and file mapping.

You can also save an invalid Connected Query as a private query (with the same owner) with a new name and delete the original invalid public query. After deleting the original Connected Query, save the new private Connected Query using the original name.

SysConqrs-07

Verifies Connected Query structure (PSCONQRSMAP table), ensuring:

  • No duplicate parents exist for child queries.

  • No duplicate combinations of parent and child queries exist for a Connected Query.

Delete the invalid definition using Connected Query Manager.

SysConqrs-08

Verifies Connected Query structure (PSCONQRSMAP table), ensuring a parent(root) query is defined for all Connected Query definitions.

Delete the invalid definition using Connected Query Manager.

SysConqrs-09

Verifies Connected Query structure (PSCONQRSMAP table), checking if any parent query exists as a child query.

Delete the invalid definition using Connected Query Manager.

SysConqrs-10

Verifies Connected Query structure (PSCONQRSMAP table), ensuring that the number of child queries should be equal to the maximum value of the SEQNUM field.

Open the Connected Query and re-map member query fields.

If the Connected Query can't be opened, it should be deleted using Connected Query Manager.

Click to jump to top of pageClick to jump to parent topicEDI Manager Integrity

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

Query

Description

Resolution

ECINMPFL-1

Inbound work records that are not found in the PSRECDEFN table.

Either modify the inbound map definition to not use the Inbound Row ID Work Record (ECINMAPFILE), or create the Work Record Definition.

ECINMPFL-2

Inbound work record EC Map ID is not found in the PS_ECMAPDEFN table.

Create an entry in the map definition table (ECMAPDEFN).

ECINMPFD-1

Inbound work record fields are not found with valid EC Map ID and EC File Row ID combination from the PS_ECINMAPFILE table

Either remove the invalid map ID from the Inbound Work Record (ECINMAPFLD), or create an Inbound Row ID Work Record entry.

ECINMPFD-2

Inbound work record fields from PS_ECINMAPFLD are not found in PSRECFIELD

Either remove the invalid entry in the inbound work record or create the record/field definition.

ECINMPRC-1

Target inbound records are not found in the PSRECDEFN table

Either modify the inbound map definition to not use the Inbound Row ID Target Record (ECINMAPREC), or create the Work Record Definition.

ECINMPRC-2

Target inbound EC Map ID is not found in the PS_ECMAPDEFN table

Either remove the invalid map ID from the Inbound Row ID Target Record or create an entry in the Map Definition table.

ECINMPRF-1

EC Map ID/EC File Row ID combination is not found in PS_ECINMAPREC for the target inbound record field in PS_ECINMAPRECFLD

Remove the invalid map ID from the Inbound Target Record.

ECINMPRF-2

A Field for a Record in PS_ECINMAPRECFLD was not found in PSRECFIELD

Create the appropriate definitions in PSRECFIELD or remove the invalid map ID from the Inbound Target Record.

ECINMPRF-4

A related record in PS_ECINMAPRECFLD is not found in PSRECDEFN

Either create the record definition or remove the reference to the related record in the Inbound Target Record.

ECINMPRF-5

An EC Related Record in PS_ECINMAPRECFLD does not have a valid EC Related Row ID from PS_ECINMAPREC

Either remove the reference to the related record from the Inbound Target Record or create an appropriate entry in the Inbound Row ID Target Record.

ECINMPRF-6

A related field in PS_ECINMAPRECFLD is not found in PSRECFIELD

Either remove or correct the reference to the related field record from the Inbound Target Record or create the correct definition in PSRECFIELD.

ECOTMPRC-1

Target outbound records are not found in the PSRECDEFN table

Either modify the outbound map definition to not use the Outbound Target Record, or create the record definition.

ECOTMPRC-2

Outbound work record EC Map ID is not found in the PS_ECMAPDEFN table

Create an entry in the map definition table.

ECOTMPRC-3

Parent records from the outbound work record are not found in the PSRECDEFN table

Remove the reference to the parent record or create a record definition for the parent.

ECOTMPRC-4

File records from the outbound work record are not found in the PSRECDEFN table

Create a record definition for the file record.

ECOTMPFD-1

Outbound work record fields are not found with a valid EC Map ID and EC File Row ID combination from the PS_ECOUTMAPREC table

Either remove the entry from the Outbound Work Record (ECOUTMAPFLD) or create an entry in the Outbound Target Record (ECOUTMAPREC).

ECOTMPFD-2

Outbound work record fields from PS_ECOUTMAPFLD are not found in PSRECFIELD

Create the appropriate definitions in PSRECFIELD or remove the invalid map ID from the Outbound Work Record.

SYSECMGR-1

Inbound work record field does not exist in the type definitions in PSDBFIELD

Select, PeopleTools, EDI Manager, Setup Trading Partners, Remove EDI Manager Objects. In the Delete EC Map field, delete all maps except the following:

  • AUDIT

  • BCWCB

  • CL_APP_V4

  • CL_CHNG_V4

  • CL_EFT_V4

  • CL_ORIG_V4

  • OUAC-LAW-A

  • OUAC-LAW-U

  • OUAC-PT

  • OUAC-TEA-A

  • OUAC-TEA-U

  • OUAC_UAS_A

  • OUAC_UAS_U

  • TS130_MAP

  • TS189_MAP

Click to jump to top of pageClick to jump to parent topicFeeds Integrity

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

Query

Description

Resolution

FEED-01

Feed referencing a nonexistent feed data type.

Remove the feed referencing the nonexistent feed data type from the feed definition table.

FEED-02

Feed data type Integration Broker operations table referencing a nonexistent feed data type.

Remove the Integration Broker operations referencing the nonexistent feed data type from the feed data type Integration Broker operations table.

Run the following SQL:

DELETE FROM PSFP_DTYPE_IBSO WHERE PTFP_DATATYPE_ID='<DATATYPEID>'

FEED-03

Default feed attributes table referencing a nonexistent feed data type.

Remove the default feed attributes referencing the nonexistent feed data type from the default feed attributes table.

Run the following SQL:

DELETE FROM PSFP_DTYPE_ATTR WHERE PTFP_DATATYPE_ID='<DATATYPEID>'

FEED-04

Feed attributes table referencing nonexistent feed.

Remove the feed attributes referencing the nonexistent feed from the feed attributes table.

Run the following SQL:

DELETE FROM PSFP_ATTRS WHERE PTFP_FEED_ID='<FEEDID>'

FEED-05

Feed data source settings table referencing a nonexistent feed.

Remove the data source settings referencing the nonexistent feed from the feed data source settings table.

Run the following SQL:

DELETE FROM PSFP_SETTINGS WHERE PTFP_FEED_ID='<FEEDID>'

FEED-06

Feed data source parameters table referencing a nonexistent feed.

Remove the data source parameters referencing the nonexistent feed from the feed data source parameters table.

Run the following SQL:

DELETE FROM PSFP_PARMS WHERE PTFP_FEED_ID='<FEEDID>'

FEED-07

Feed security table referencing a nonexistent feed.

Remove the feed security referencing the nonexistent feed from the feed security table.

Run the following SQL:

DELETE FROM PSFP_SECURITY WHERE PTFP_FEED_ID='<FEEDID>'

FEED-08

User specified data source parameter table referencing a nonexistent feed.

Remove the user specified data source parameters referencing the nonexistent feed from the user specified data source parameter table.

Run the following SQL:

DELETE FROM PSFP_PVALS WHERE PTFP_FEED_ID='<FEEDID>'

FEED-09

Admin personalization table referencing a nonexistent feed.

Remove admin personalization data referencing the nonexistent feed from the admin personalization table.

Run the following SQL:

DELETE FROM PSFP_ADMN_PREF WHERE PTFP_FEED_ID='<FEEDID>'

FEED-10

User personalization table referencing a nonexistent feed.

Remove user personalization data referencing the nonexistent feed from the user personalization table

Run the following SQL:

DELETE FROM PSFP_USER_PREF WHERE PTFP_FEED_ID='<FEEDID>'

FEED-11

 

Remove the invalid category association in the feed. Run the following SQL, depending on your database platform.

Oracle:

UPDATE PSFP_FEED SET PTFP_CATEGORY_ID = ' ' WHERE PTFP_FEED_ID='<FEEDID>'

Non-Oracle:

UPDATE PSFP_FEED SET PTFP_CATEGORY_ID = '' WHERE PTFP_FEED_ID='<FEEDID>'

Note. There is a space within the quotes for an Oracle database, and no space within quotes for a non-Oracle database.

GENERICFEED-01

Integration Broker Generic Feed referencing a nonexistent Integration Broker service operation.

Remove the Integration Broker Generic Feed that references a nonexistent Integration Broker service operation.

WORKLISTFEED-01

Worklist feed referencing a nonexistent activity.

Remove the worklist feed that references a nonexistent activity.

WORKLISTFEED-02

Worklist feed referencing a nonexistent business process.

Remove the worklist feed referencing a nonexistent business process.

WORKLISTFEED-03

Worklist feed referencing a nonexistent event.

Remove the worklist feed referencing a nonexistent event.

WORKLISTFEED-04

Worklist feed referencing a nonexistent worklist name.

Remove the worklist feed referencing a nonexistent worklist name.

WORKLISTFEED-05

Worklist feed referencing a nonexistent "From" user.

Remove the worklist feed referencing a nonexistent user.

QUERYFEED-01

Query feed definition referencing a nonexistent query.

Remove the query feed referencing the nonexistent query.

QUERYFEED-02

Query feed parameter definition referencing a nonexistent query bind.

Remove the query feed parameter referencing a nonexistent query bind.

Run the following SQL:

DELETE FROM PSFP_PARMS WHERE PTFP_FEED_ID='<FEEDID>' AND PTFT_FIELD_NAME = ‘<FIELDNAME>’

QUERYFEED-03

Query feed entry element maps to a template that referencing a nonexistent query result column.

Remove query feed entry template referencing a nonexistent query result column.

Run the following SQL:

DELETE FROM PSFP_ATTRS WHERE PTFP_FEED_ID='<FEEDID>' AND PTFT_FIELD_NAME = ‘<QRYFLDNAME>’

To remove a feed shown in these audits:

Run the following SQL:

DELETE FROM PSFP_ADMN_PREF WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_USER_PREF WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_ATTRS WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_ATTRS_LANG WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_FEED WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_FEED_LANG WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_PARMS WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_PARMS_LANG WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_PUB_SITES WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_PVALS WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_PVALS_LANG WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_SECURITY WHERE PTFP_FEED_ID IN ('<FEEDID>'); DELETE FROM PSFP_SETTINGS WHERE PTFP_FEED_ID IN ('<FEEDID>');

Click to jump to top of pageClick to jump to parent topicField Integrity

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

Query

Description

Resolution

FIELD-3

This query lists invalid default fields.

Modify the default value in record field properties.

FIELD-4

This query lists fields that are used in record definitions but do not exist in PSDBFIELD.

Define the field in Application Designer.

FIELD-5

This query lists fields that have multiple default field labels in PSDBFLDLABL.

Open the field, select the default label, and save.

FIELD-06

This query lists deleted fields that have orphaned field labels in PSDBFLDLABL.

Run this SQL:

DELETE FROM PSDBFLDLABL WHERE FIELDNAME NOT IN (SELECT FIELDNAME FROM PSDBFIELD)

Click to jump to top of pageClick to jump to parent topicIntegration Broker Integrity

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

Query

Description

Resolution

IBRK-01

Message parts referencing a message/version that does not exist.

Most likely caused by moving a container message in a project and not including all the part messages. Always make sure that when moving a container message in a project, every part message is also included if it doesn’t already exist in the target database.

IBRK-02

Rowset based messages referencing records that do not exist.

Most likely caused by moving a message in a project and not including records referenced in the message. Always make sure that when moving a rowset-based message in a project, every record that is not in the target database is also included in the project.

IBRK-03

Message parts must reference message/versions that are defined as part messages.

Most likely caused by moving a container message in a project and not including records referenced in the message. By definition, only part messages can be found in a container message. Always make sure that when moving a container message in a project, every part message contained is also in the project, unless the part message is already in the target database defined as a part message.

IBRK-04

Service operations need at least one version, the default.

Service operations cannot exist without a service operation version. These are separate managed objects and can therefore be added individually into a project. However, care must be taken when moving service operations in projects to always include the default version if it doesn’t already exist in the target database.

IBRK-05

Service references service operation(s) that do not exist

This audit reports any services that contain service operations that don't exist. Typically, this is caused when moving a service from one database to another by including the service in the project but not including all related service operations.

Care must be taken when moving services to always include the associated service operations in the project, unless the same service already exists in the target database.

IBRK-06

Service operation versions must have a valid Service operation

Service operations and service operation versions are separately managed objects. When moving service operation versions in a project, be sure to include the service operation with the same name unless the service operation already exists in the target database.

IBRK-07

Handlers must have a valid service operation.

Service operation handlers and service operations are separately managed objects. When moving handlers, be sure to include the related service operation unless the operation already exists in the target database.

IBRK-08

Routings must reference a valid service operation version.

Note. Routing IB_ADMIN_ROUTING is excluded because it is a dummy routing used during upgrade to 8.48.

Service operation routings and service operation versions are separately managed objects. When moving routings, be sure to include the related service operation version unless the operation version already exists in the target database.

IBRK-09

Routings must reference valid nodes

Service operation routings and nodes are separately managed objects. When moving routings, be sure to include any related Node that doesn’t exist in the target database.

IBRK-10

Routings must reference valid service operation handlers

Service operation routings and service operation Handlers are separately managed objects. When moving routings, be sure to include the related service operation Handlers that appear in the routing component unless the Handlers already exist in the target database.

IBRK-11

Routing parameters must reference valid transform application engine programs

Service operation routings reference application engine transform programs. When moving routings, be sure to include any referenced Application Engine programs unless the programs that appear in the routing parameter component already exist in the target database.

IBRK-12

Routing parameters must reference valid message/version combinations.

Service operation routings reference messages. When moving routings, be sure to include any referenced messages unless the messages that appear in the routing parameter component already exist in the target database.

IBRK-13

Service operation versions need to reference valid messages

Service operation versions reference messages. When moving versions in a project, be sure to include any referenced messages unless the messages that appear in the service operation or service operation versions component already exist in the target database.

IBRK-14

Service operation versions need to reference valid queues

Service operation versions reference Integration Broker queues. When moving versions in a project, be sure to include any referenced queues unless the queues that appear in the service operation or service operation versions component already exist in the target database.

IBRK-15

Service operation versions need to reference valid transform programs

Service operation versions reference application engine transform programs. When moving versions in a project, be sure to include any referenced application engine programs unless the programs that appear in the service operation versions component already exist in the target database.

IBRK-16

Service operation versions with validation turned on require each -- message to have a schema defined

Service operations versions with validation turned on (see the Service Operations component), require all referenced messages to have schemas defined. When moving service operation versions that have validation turned on, include all referenced messages unless the referenced messages already exist in the target database with valid schemas. Also note that when moving messages in projects the related schemas are not brought along. These need to be moved via Data Mover scripts. (PSIBMSGSCHEMA_IMP.DMS and PSIBMSGSCHEMA_EXP.DMS)

IBRK-17

Component interface handlers should reference valid component interfaces.

Service operation handlers that are of type CI reference component interfaces. When moving service operation handlers, be sure to also include any referenced component interfaces in the project unless the CIs already exist in the target database.

IBRK-18

Application class handlers should reference valid application packages

Service operation handlers that are of type Application Class reference application classes. When moving service operation Handlers, be sure to also include any referenced application classes in the project unless they already exist in the target database. Care must be taken to make sure that the referenced application class PeopleCode is also included in the project.

IBRK-19

Part messages need to have a schema defined.

When moving messages in projects, the related schemas are not brought along. The schemas need to be moved using Data Mover scripts. (see PSIBMSGSCHEMA_IMP.DMS and PSIBMSGSCHEMA_EXP.DMS). For part messages, having a schema is mandatory.

IBRK-20

Container messages need to have a schema defined.

When moving messages in projects, the related schemas are not brought along. These need to be moved using Data Mover scripts. (see PSIBMSGSCHEMA_IMP.DMS and PSIBMSGSCHEMA_EXP.DMS). For container messages, having a schema is mandatory.

IBRK-21

Operations with duplicate routings.

This audit reports service operations with multiple active routings where the sender and receiver node are identical. There should never be multiple active routings where the sender and receiver node are identical for a service operation.

Delete or inactivate one of the offending routings for each service operation.

IBRK-22

Operations with duplicate ANY routings

This audit reports service operations with multiple active routings where the sender node is set to ANY. There should never be multiple active routings where the sender node is set to ANY for a service operation.

Delete or inactivate one of the offending routings for each service operation.

You can change the status of one of the duplicate routings as follows:

UPDATE PSIBRTNGDEFN SET EFF_STATUS = 'I' WHERE ROUTINGDEFNNAME = <NAME> AND EFFDT = <EFFDT>

To delete a duplicate routing, select PeopleTools, Integration Broker, System Utilities, Service Administration. On the Routings tab, find the routing to remove and remove it. Alternatively, you can run the following set of SQL statements:

DELETE FROM PSIBRTNGDEFN WHERE ROUTINGDEFNNAME = <NAME> AND EFFDT = <EFFDT> DELETE FROM PSIBRTNGSUBDEFN WHERE ROUTINGDEFNNAME = <NAME> AND EFFDT = <EFFDT> DELETE FROM PSRTNGDFNPARM WHERE ROUTINGDEFNNAME = <NAME> AND EFFDT = <EFFDT> DELETE FROM PSRTNGDFNCONPRP WHERE ROUTINGDEFNNAME = <NAME> AND EFFDT = <EFFDT>

IBRK-23

Operation with no Service Relationship

There should never be operations referencing services that don't exist. Typically, this is caused when importing a project containing a service operation that belongs to a service that does not exist in the target database.

Always make sure that the services that a service operation belongs to exist in the target database or are included in the import project.

All service operations must belong to at least one service.

IBRK-24

DMS Handler referencing invalid message

This audit identifies handlers of type Data Mover with a missing message. Certain Data Mover handlers require an associated message. When moving Data Mover handlers in a project, all related messages should also be included.

To resolve issues, locate the message(s) in the database that was used to create the handler. Import that message into the target database (where you ran SYSAUDIT).

If you can't locate the message, recreate the handler in the source database and then be sure to include both the handler and the related message in the project, and import the project.

Click to jump to top of pageClick to jump to parent topicMenu Integrity

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

Query

Description

Resolution

MENU-01

A row in the MenuItem table has no corresponding row in the MenuDefinition table.

Issue the following SQL:

DELETE FROM PSMENUITEM WHERE MENUNAME = 'x';

MENU-02

A component-type menu item specifies no component.

Use the Menu Designer to change each of these menu items to reference an existing component.

MENU-03

A menu item has a specified component, but that component has no corresponding row in the ComponentDefinition table.

Use the Menu Designer to change each of these menu items to reference an existing component.

MENU-04

A PeopleCode-type menu item has a specified enabling component, but that component is not specified for any component-type menu item within the same menu. (Such menu items never get enabled at runtime.)

Use the Menu Designer to change each of these menu items to reference a component that is associated with a component-type menu item within the same menu.

MENU-05

A menu has no rows in the MenuItem table.

Use the Menu Designer to add any appropriate menu items to each of these menus.

Click to jump to top of pageClick to jump to parent topicOptimization Integrity

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

Query

Description

Resolution

OPTZN-01

Problem type records that do not have matching record definitions.

Execute the following SQL:

DELETE FROM PSOPTREC WHERE RECNAME = ​'recordname'; DELETE FROM PSOPTFIELD WHERE RECNAME ='recordname';

OPTZN-02

Optimization delete records that do not have matching definitions.

In Application Designer, open the base record definition properties. Clear the optimization delete record name, and perform an alter.

OPTZN-03

Optimization base record has fields that delete record does not.

Using Application Designer, delete the optimization delete record definition, drop the table, and recreate it by cloning the base record. Run Build. You may need to recreate triggers on the base record on some platforms where deferred processing is not done.

OPTZN-04

Optimization delete record has fields that base record does not.

Using Application Designer, delete the optimization delete record definition, drop the table and recreate it by cloning the base record. Run Build. You may need to recreate triggers on the base record on some platforms where deferred processing is not done.

OPTZN-06

Optimization base record defn has the trigger flag set but has no delete record name, or vice versa.

Using Application Designer, open the record definition properties, make sure that the optimization delete record name is set, and save. Build the record with the create triggers check box set to create optimization triggers.

OPTZN-07

Optimization records that need to have trigger flag set and do not.

Using Application Designer, open the record definition properties, make sure that the optimization delete record name is set, and save. Build the record with the create triggers check box set to create optimization triggers.

OPTZN-08

Optimization records that have trigger flag set but are not marked readable in any problem type.

Using Application Designer, open the record definition properties, clear the optimization delete record name and alter the record to drop optimization triggers as they are no longer needed but affect performance.

OPTZN-10

Optimization Tools table PSOPTSYNC does not have an entry for the listed opt records, that are marked READABLE in PSOPTREC.

Open the problem type definition in Application Designer, make sure that the readable flags are set correctly for each readable record, and save the problem type definition.

OPTZN-11

Optimization Tools table PSOPTSYNC does not have an entry with PROBINST = $ALL$ and is marked as NON SCENARIO_MANAGED and READABLE in PSOPTREC.

Using Problem Type Designer, make sure that the readable flags are set correctly for each readable record. Make sure that the scenario_managed flags are set correctly. Save the problem type definition.

OPTZN-12

Optimization Tools table PSOPTSYNC has extra entries for the listed record names that are not there in PSOPTREC.

Submit the following SQL to remove extra entries in PSOPTSYNC table.

DELETE FROM PSOPTSYNC WHERE RECNAME NOT IN (SELECT RECNAME FROM PSOPTREC)

OPTZN-13

The following record names in Optimization Tools table PSOPTREC do not have at least one field listed in the PSOPTFIELD table.

Open the problem type definition in Application Designer. Make sure that for every record in the problem type definition at least one field is selected to be loaded in the problem instance.

OPTZN-14

For the following transaction parameter of type RECARRAY, the default value contains an invalid record name.

Open the problem type definition in Application Designer. Inspect the offending transaction parameter and make sure that the default value contains a valid record name.

OPTZN-15

PSOPTSOLVERCODE table is empty for the listed problem types.

You may ignore this if none of the problem types need a third-party solver. Otherwise, populate the PSOPTSOLVERCODE table with the third-party solver license key. Select PeopleTools, Utilities, Optimization, Solver Licenses.

OPTZN-16

PSOPTSOLVERCODE table has a null licence key for the listed problem types.

You may ignore this if the plugin does not need a third-party solver. Otherwise populate the PSOPTSOLVERCODE table with the third-party solver licence key. Select PeopleTools, Utilities, Optimization, Solver Licenses.

OPTZN-17

This query identifies readable base records in an analytic type that don't have an optimization delete record specified.

In Application Designer, either specify a delete record for the analytic type record, or clear the Readable check box for the analytic type record.

OPTZN-18

This query identifies base records in an analytic type that have an optimization delete record specified, but aren't readable.

In Application Designer, either select the Readable check box for the analytic type record, or don't specify an optimization delete record for the analytic type record.

OPTZN-19

This query identifies base records and their associated delete records in an analytic type that don't have all fields in the same order.

In Application Designer, change the field order of one of the records to match the field order of the other record.

OPTZN-21

This query identifies fields in main records used in an analytic model that aren't selected in the analytic type associated with that model.

In Application Designer, either specify an appropriate record in the analytic model, or select the appropriate corresponding fields in the analytic type definition.

OPTZN-22

This query identifies fields in aggregate records used in an analytic model that aren't selected in the analytic type associated with the model.

In Application Designer, either specify an appropriate record in the analytic model, or select the appropriate corresponding fields in the analytic type definition.

Click to jump to top of pageClick to jump to parent topicPage Integrity

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

Query

Description

Resolution

PAGE-01

Page definition's page field count is not equal to the count of its page fields in the PageField table, and there is at least one row in the PageField table for that page.

Enter the following SQL:

SELECT COUNT(*) FROM PSPNLFIELD WHERE PNLNAME = 'x'; UPDATE PSPNLDEFN SET FIELDCOUNT = count WHERE PNLNAME = 'x';

PAGE-02

Page definition's page field count is not equal to zero, but there are no rows in the PageField table for that page definition.

Enter the following SQL:

UPDATE PSPNLDEFN SET FIELDCOUNT = 0 WHERE PNLNAME = 'x';

PAGE-03

A subpage contains itself as a page field.

Use the Page Designer to change each of these page fields to reference a different subpage.

PAGE-04

A row in the PageField has no corresponding row in the PageDefinition table.

Issue the following SQL:

DELETE FROM PSPNLFIELD WHERE PNLNAME = 'x';

PAGE-05

A subpage-type page field has no corresponding row in the Page Definition table for its specified subpage.

Use the Page Designer to change each of these page fields to reference an existing subpage.

PAGE-06

A page field's specified record/field has no corresponding row in the RecordField table.

Use the Page Designer to change each of these page fields to reference an existing record/field.

PAGE-07

A row in the ComponentItem table has no corresponding row in the ComponentDefinition table.

Issue the following SQL:

DELETE FROM PSPNLGROUP WHERE PNLGRPNAME = 'x';

PAGE-08

A component item's specified page has no corresponding row in the PageDefinition table.

Use Application Designer to replace each of these component items with one that references an existing page.

PAGE-09

A component's specified access detail page has no corresponding row in the PageDefinition table.

Use Application Designer to change each of these components to reference an access detail page that exists.

PAGE -10

A component's specified search record has no corresponding row in the RecordDefinition table.

Use Application Designer to change each of these components to reference a search record that exists.

PAGE-11

A component's specified add search record has no corresponding row in the RecordDefinition table.

Use Application Designer to change each of these components to reference an add search record that exists.

PAGE-12

There is a discrepancy between MAXPNLFLDID on the page definition table (PSPNLDEFN) and PNLFLDID on the page field table (PSPNLFIELD).

Use your SQL Editor to set PSPNLDEFN.MAXPNLFLDID equal to the highest PSPNLFIELD.PNLFLDID value for the page.

Click to jump to top of pageClick to jump to parent topicPeopleCode Integrity

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

Query

Description

Resolution

PEOPLECODE-1

The PeopleCode Name table contains a program name that does not exist in PcmProgram table

Run the following SQL:

DELETE FROM PSPCMNAME WHERE NOT EXISTS (SELECT 'X'FROM PSPCMPROG B WHERE B.OBJECTID1 = PSPCMNAME.OBJECTID1 AND B.OBJECTVALUE1 = PSPCMNAME.OBJECTVALUE1 AND B.OBJECTID2 = PSPCMNAME.OBJECTID2 AND B.OBJECTVALUE2 = PSPCMNAME.OBJECTVALUE2 AND B.OBJECTID3 = PSPCMNAME.OBJECTID3 AND B.OBJECTVALUE3 = PSPCMNAME.OBJECTVALUE3 AND B.OBJECTID4 = PSPCMNAME.OBJECTID4 AND B.OBJECTVALUE4 = PSPCMNAME.OBJECTVALUE4 AND B.OBJECTID5 = PSPCMNAME.OBJECTID5 AND B.OBJECTVALUE5 = PSPCMNAME.OBJECTVALUE5 AND B.OBJECTID6 = PSPCMNAME.OBJECTID6 AND B.OBJECTVALUE6 = PSPCMNAME.OBJECTVALUE6)

PEOPLECODE-2

The PeopleCode Program table contains a program name that does not exist in the PcmName table.

Run the following SQL:

DELETE FROM PSPCMPROG WHERE NAMECOUNT <> 0 AND NOT EXISTS (SELECT 'X'FROM PSPCMNAME B WHERE PSPCMPROG.OBJECTID1 = B.OBJECTID1 AND PSPCMPROG.OBJECTVALUE1 = B.OBJECTVALUE1 AND PSPCMPROG.OBJECTID2 = B.OBJECTID2 AND PSPCMPROG.OBJECTVALUE2 = B.OBJECTVALUE2 AND PSPCMPROG.OBJECTID3 = B.OBJECTID3 AND PSPCMPROG.OBJECTVALUE3 = B.OBJECTVALUE3 AND PSPCMPROG.OBJECTID4 = B.OBJECTID4 AND PSPCMPROG.OBJECTVALUE4 = B.OBJECTVALUE4 AND PSPCMPROG.OBJECTID5 = B.OBJECTID5 AND PSPCMPROG.OBJECTVALUE5 = B.OBJECTVALUE5 AND PSPCMPROG.OBJECTID6 = B.OBJECTID6 AND PSPCMPROG.OBJECTVALUE6 = B.OBJECTVALUE6)

PEOPLECODE-3

The PeopleCode Program table name count does not match the record count in PcmName table.

Run the following SQL:

UPDATE PSPCMPROG SET NAMECOUNT = (SELECT COUNT(*) FROM PSPCMNAME C WHERE C.OBJECTID1 = PSPCMPROG.OBJECTID1 AND C.OBJECTVALUE1 = PSPCMPROG.OBJECTVALUE1 AND C.OBJECTID2 = PSPCMPROG.OBJECTID2 AND C.OBJECTVALUE2 = PSPCMPROG.OBJECTVALUE2 AND C.OBJECTID3 = PSPCMPROG.OBJECTID3 AND C.OBJECTVALUE3 = PSPCMPROG.OBJECTVALUE3 AND C.OBJECTID4 = PSPCMPROG.OBJECTID4 AND C.OBJECTVALUE4 = PSPCMPROG.OBJECTVALUE4 AND C.OBJECTID5 = PSPCMPROG.OBJECTID5 AND C.OBJECTVALUE5 = PSPCMPROG.OBJECTVALUE5 AND C.OBJECTID6 = PSPCMPROG.OBJECTID6 AND C.OBJECTVALUE6 = PSPCMPROG.OBJECTVALUE6)

PEOPLECODE-4

PeopleCode contains invalid FILELAYOUT References.

Open the PeopleCode program in Application Designer and correct the invalid reference.

PEOPLECODE-5

PeopleCode reference to an invalid record or field.

Open the PeopleCode program in Application Designer and correct the invalid reference.

PEOPLECODE-6

PeopleCode reference to an invalid field.

Open the PeopleCode program in Application Designer and correct the invalid field name.

PEOPLECODE-7

There is orphaned Application Package PeopleCode.

Run the following SQL:

DELETE FROM PSPCMPROG WHERE OBJECTID1 = 104 AND OBJECTID2 = 107 AND OBJECTVALUE2 NOT IN (SELECT APPCLASSID FROM PSAPPCLASSDEFN P WHERE P.PACKAGEROOT = OBJECTVALUE1 AND P.APPCLASSID = OBJECTVALUE2)

PEOPLECODE-8

There is orphaned Application Package PeopleCode.

Run the following SQL:

DELETE FROM PSPCMPROG WHERE OBJECTID1 = 104 AND OBJECTID2 = 105 AND OBJECTID3 = 107 AND OBJECTVALUE3 NOT IN (SELECT APPCLASSID FROM PSAPPCLASSDEFN P WHERE P.PACKAGEROOT = OBJECTVALUE1 AND P.QUALIFYPATH = OBJECTVALUE2 AND P.APPCLASSID = OBJECTVALUE3)

PEOPLECODE-9

There is orphaned Application Package PeopleCode.

For Microsoft SQL Server, run the following SQL:

DELETE FROM PSPCMPROG WHERE OBJECTID1 = 104 AND OBJECTID2 = 105 AND OBJECTID3 = 106 AND OBJECTID4 = 107 AND OBJECTVALUE4 NOT IN (SELECT APPCLASSID FROM PSAPPCLASSDEFN F WHERE F.PACKAGEROOT = OBJECTVALUE1 AND F.QUALIFYPATH = RTRIM(OBJECTVALUE2)+ ':'+ RTRIM(OBJECTVALUE3) AND F.APPCLASSID = OBJECTVALUE4)

For Informix, run the following SQL:

DELETE FROM PSPCMPROG WHERE OBJECTID1 = 104 AND OBJECTID2 = 105 AND OBJECTID3 = 106 AND OBJECTID4 = 107 AND OBJECTVALUE4 NOT IN (SELECT APPCLASSID FROM PSAPPCLASSDEFN F WHERE F.PACKAGEROOT = OBJECTVALUE1 AND QUALIFYPATH = TRIM(P.OBJECTVALUE2) || ':' || TRIM(P.OBJECTVALUE3) AND F.APPCLASSID = OBJECTVALUE4)

For all other DB platforms

DELETE FROM PSPCMPROG WHERE OBJECTID1 = 104 AND OBJECTID2 = 105 AND OBJECTID3 = 106 AND OBJECTID4 = 107 AND OBJECTVALUE4 NOT IN (SELECT APPCLASSID FROM PSAPPCLASSDEFN F WHERE F.PACKAGEROOT = OBJECTVALUE1 AND QUALIFYPATH = RTRIM(P.OBJECTVALUE2) || ':' || RTRIM(P.OBJECTVALUE3) AND F.APPCLASSID = OBJECTVALUE4)

Click to jump to top of pageClick to jump to parent topicProcess Scheduler

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

Query

Description

Resolution

PRCSSCHED-01

SQR-Related Process Definitions (PS_PRCSDEFN) that override the PARMLIST field from the Process Type Definition (PS_PRCSTYPEDEFN).

For the listed processes, select Process Scheduler, Processes, Override Options. Remove the value that is assigned to the Parameter List field.

Note. The PRRSCHED-01 query is intended to be a warning. If the override of the parameter list that is specified in the process type definition is intentional, then the above action can be bypassed.

PRCSSCHED-03

Process Definitions (PS_PRCSDEFN), where the OUTDESTTYPE should be set to NONE.

For the listed processes, select Process Scheduler, Processes Destination. In the Output Destination Options group, set the Type option to (None).

PRCSSCHED-04

Process Definitions, where the API AWARE should be set to true.

For the listed processes, select Process Scheduler, Processes, Process Definition. Select the check box that reads API Aware.

If API Aware is not marked, this process gets an incorrect run status when it's viewed from Process Monitor. For additional information, please refer to the Process Scheduler PeopleBook.

PRCSSCHED-05

Process Definitions, where process type is not found in the Process Type Definition .

This occurs when a Process Definition is copied from another PeopleSoft database by using project upgrade. However, the Process Type definition that is associated with this Process Definition is not copied into the database. Review the project upgrade that is used to create the Process Definition. Create another project upgrade to copy Process Type definition from the database where the Process Definition originated.

PRCSSCHED-06

Process Job Item (PS_PRCSJOBITEM), where Process Type is listed as a job item, but is not found in the Process Definition (PS_PRCSDEFN).

This occurs when a PSJob is copied from another PeopleSoft database by using a project upgrade. However, the Process Definition for one or more job items in the PSJob is not copied from the database. Review the project upgrade that is used to create the PSJob. Create another project upgrade to copy the Process Definitions that are identified in this report from the database where the PSJob originated.

PRCSSCHED-07

Server Class List (PS_SERVERCLASS), where Process Type is not found in the Process Type Definition (PS_PRCSTYPEDEFN).

This occurs when a Server Definition is copied from another database by using a project upgrade. However, a process type in the Server Class list is not found in the Process Type Definition. Create another project upgrade to copy the Process Type definition from the database where the Server Definition is created.

PRCSSCHED-08

Process Definitions, where the process category is invalid

For the listed processes, select Process Scheduler, Processes, Process Definition. Correct the Process Category.

PRCSSCHED-09

Job Definitions, where the process category is invalid.

For the listed jobs, select Process Scheduler, Jobs, Job Definition. Correct the Process Category.

PRCSSCHED-10

Process Definitions, where the process category is missing.

For the listed processes, select Process Scheduler, Processes, Process Definition. Specify a Process Category.

PRCSSCHED-11

Job Definitions, where the process category is missing.

For the listed jobs, select Process Scheduler, Jobs, Job Definition. Specify a Process Category.

PRCSSCHED-12

Server Categories, where a category defined for a server does not exist in process category definition.

For the listed servers, select Process Scheduler, Servers, Server Definition. Remove the invalid Process Category.

PRCSSCHED-13

Server Categories, where a server is missing a process category definition.

For the listed servers, select Process Scheduler, Servers, Server Definition.

A warning message appears when you open the page, and the missing Process Category is added to the server when the page is saved.

PRCSSCHED-14

Process Scheduler Queue, where a queued/pending request specifies a category that does not exist in process category definition.

Run the following SQL:

DELETE FROM PSPRCSQUE S WHERE S.RUNSTATUS IN ('5', '16') AND S.SERVERNAMERQST <> '' AND S.PRCSCATEGORY NOT IN (SELECT PRCSCATEGORY FROM PS_SERVERCATEGORY WHERE SERVERNAME = S.SERVERNAMERQST AND MAXCONCURRENT > 0)

PRCSSCHED-15

Process Definitions, where a process specifies an invalid destination folder.

For the listed processes, select Process Scheduler, Processes, Destination. Correct the Destination Folder or blank it out.

PRCSSCHED-16

Process Definitions, where a process definition specifies a recovery process that does not exist.

For the listed processes, select Process Scheduler, Processes, Process Definition Options. Correct the recovery process or blank it out.

PRCSSCHED-17

Job Definitions, where a job definition specifies a recovery process that does not exist.

For the listed jobs, select Process Scheduler, Jobs, Job Definition Options. Correct the recovery process or blank it out.

PRCSSCHED-18

There are queued processes in tables used by Process Scheduler (PSPRCSPARMS and PSPRCSRQST) containing a DBNAME different than the current database name.

This situation can occur when a database has been renamed.

To resolve the database name, shut down the Process Scheduler server(s), and run the MGRPRCSTBL Application Engine program from the command line.

See Using the Command Line to Invoke Application Engine Programs.

PRCSSCHED-19

Process definitions, where a process definition specifies a run time parameter record/field that does not exist.

For the listed processes, select PeopleTools, Process Scheduler, Processes, Runtime Parameters, and perform one of the following:

  • select the correct runtime parameter record/field.

  • remove the incorrect parameters.

Click to jump to top of pageClick to jump to parent topicQuery Integrity

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

Query

Description

Resolution

QUERY-01

Query Definition Select count does not match the record count that is in the Query Select table. The query definition is corrupt.

Run the following SQL:

DELETE FROM PSQRYDEFN WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYSELECT WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYRECORD WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYFIELD WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYCRITERIA WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYEXPR WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYBIND WHERE OPRID = 'X' AND QRYNAME = 'Y'

QUERY-02

Query Definition Expression count does not match the record count in the Query Expression table.

Run the following SQL:

UPDATE PSQRYDEFN SET EXPCOUNT = (SELECT COUNT(*) FROM PSQRYEXPR C WHERE OPRID = ‘X’ AND QRYNAME = ‘Y’) WHERE OPRID = ‘X’ AND QRYNAME = ‘Y’

QUERY-03

Query Definition Bind count does not match the record count in the Query Bind table

Run the following SQL:

UPDATE PSQRYDEFN SET BNDCOUNT = (SELECT COUNT(*) FROM PSQRYBIND WHERE OPRID = ‘X’ AND QRYNAME = ‘Y’) WHERE OPRID = ‘X’ AND QRYNAME = ‘Y’

QUERY-04

Query Definition Record name does not exist in the Record Definition table.

See resolution for QUERY-07.

QUERY-05

Query Definition Record JoinRecord name does not exist in the Query Record table

See resolution for QUERY-01.

QUERY-06

Query Definition Record JoinField name does not exist in the Query Field table.

See resolution for QUERY-01.

QUERY-07

Query Field Record Name does not exist in Record Definition Table

To salvage the query, you must use Application Designer to re-create the record definition.

Having re-created the record, run Query and open the offending query. Remove or repair the affected areas and save the query.

Or, if the query is not important, you can delete the entire query definition by using the resolution for QRY-01.

QUERY-08

Query Definition Field name does not exist in the Field Definition table

If the record on which this field appears is deleted, you have seen errors for every referenced field that belongs to the deleted record. If this is the case, see the resolution for QUERY-1.

If this is not the case, some fields that the query depends on are either deleted or renamed. Run Query and open the offending query. Query automatically repairs itself and updates the query definition in the database.

QUERY-09

Query Selection Record count does not match the record count in Query Record table.

See resolution for QUERY-01.

QUERY-10

Query Selection Field count does not match the record count in Query Field table.

See resolution for QUERY-01.

QUERY-11

Query Selection Criteria count does not match the record count in Query Criteria table.

See resolution for QUERY-01.

QUERY-11A

Query Selection Criteria having count does not match the record count in Query Criteria table.

See resolution for QUERY-01.

QUERY-12

Query Selection Parent select number does not exist in Query Select table.

See resolution for QUERY-01.

QUERY-13

Query Criteria Selection-Left does not exist in the Query Selection table.

Run Query and delete the corrupted criteria entry. If you can't open the query, run the following SQL to delete the corrupted criteria entry:

DELETE FROM PSQRYCRITERIA WHERE OPRID = 'X'AND QRYNAME ='Y' AND CRTNUM = count

QUERY-14

Query Criteria Selection-Right1 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-15

Query Criteria Selection-Right2 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-16

Query Criteria Field-Left does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-17

Query Criteria Field-Right1 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-18

Query Criteria Field-Right2 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-19

Query Criteria Expression-Right1 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-20

Query Criteria Expression-Right2 does not exist in the Query Selection table.

See resolution for QUERY-13.

QUERY-22

This audit identifies queries that were created without PUBLIC access.

This is normal; the audit insures that PeopleSoft does not deliver nonpublic queries as part of its standard delivered products.

QUERY-23

This audit identifies queries that reference non-existent database records. The query definitions are corrupt.

This is an internal PeopleSoft audit. Call GCS for resolution.

Run the following SQL:

DELETE FROM PSQRYDEFN WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYSELECT WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYRECORD WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYFIELD WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYCRITERIA WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYEXPR WHERE OPRID = 'X' AND QRYNAME = 'Y' DELETE FROM PSQRYBIND WHERE OPRID = 'X' AND QRYNAME = 'Y'

QUERY-24

This audit identifies queries that were created with the name UNTITLED.

Queries should not be saved as UNTITLED. Either rename or delete these queries.

QUERY-25

This audit identifies queries that were created with blank query names.

You must either rename or delete these queries.

QUERY-26

This audit identifies queries that contain unions but select an unequal number of fields.

Ensure that every select statement in the query has an equal number of fields selected. These fields must also match in display type and length.

QUERY-27

This audit identifies the queries that reference query translate fields that have been incorrectly modified to a non-translate type. When run in Microsoft Windows Query Manager, queries that reference these fields might return an inaccurate zero result set because the query metadata is corrupt.

Run the following SQL:

UPDATE PSQRYFIELD SET XLATTYPE = 1 WHERE FIELDNAME IN ​(<list of fieldnames from the audit>)

Click to jump to top of pageClick to jump to parent topicRecord Integrity

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

Query

Description

Resolution

RECORD-1

Record Definition Field count does not match the number of records in Record Field table.

Run the following SQL:

SELECT COUNT(*) FROM PSRECFIELD WHERE RECNAME = 'X'; UPDATE PSRECDEFN SET FIELDCOUNT = COUNT WHERE RECNAME = 'X';

RECORD-2

Record Definition Fields do not exist in Record Field table.

Run the following SQL:

UPDATE PSRECDEFN SET FIELDCOUNT = 0 WHERE RECNAME = 'X';

Or

DELETE FROM PSRECDEFN WHERE RECNAME = 'X';

RECORD-3

Record Definition Parent Record does not exist in Record Definition table.

Use Application Designer to open the definition.

Select File, Object Properties, Use and specify a valid parent record.

RECORD-4

Record Definition SubRecord does not exist in Record Definition table.

Use Application Designer to open the definition.

Select File, Object Properties, Type and specify a valid subrecord.

RECORD-5

Record Definition Query Security Record does not exist in Record Definition table

Use Application Designer to open the definition.

Select File, Object Properties, Use and specify a valid query security record.

RECORD-6

Record Field definitions contain record names that do not exist in the Record Definition table.

Run the following SQL:

DELETE FROM PSRECFIELD WHERE RECNAME = 'X'

RECORD-7

DBField records do not exist for the following RecField table Fields.

Use Application Designer to open the definition and fix the invalid fields.

RECORD-8

Record definitions do not exist for the following RecField table SubRecords.

Use Application Designer to open the definition and fix the invalid fields.

RECORD-9

Invalid record definitions in record group definitions.

Run the following SQL:

DELETE FROM PS_REC_GROUP_REC WHERE RECNAME NOT IN (SELECT DISTINCT RECNAME FROM PSRECDEFN)

RECORD-11

Record definitions that contain more than two Long character field types.

Note. The use of LONG data types can introduce performance overhead for multiple database platforms.

For DB2 zOS, the use of multiple LONG fields in a single record definition is highly discouraged. The use of the LONG data type for DB2 zOS creates situations in which the maximum row length is determined by the page size, less the sum of the lengths of the columns that precede the LONG column in the row. This means that the length becomes variable as the size of the LONG changes. This can lead to performance overhead, and in some cases, LONG data that no longer fits the row. Judicious use of the LONG data type for DB2 zOS is required, and placing multiple LONGs in a single record definition is discouraged.

For the Oracle platform, the judicious use of multiple LOB data types in a single record definition is recommended. The use of multiple LONG fields in a single record definition is supported. Multiple LONG support is made possible with the adoption of the CLOB (Character Large Object) and BLOB (Binary Large Object) Oracle LOB data types. While technically possible, you should exercise caution when using multiple LOBs in a single record definition because of potential performance overhead which might occur based on the placement and actual size of the LOB.

RECORD-12

Record definitions with a blank or null record name value.

Run the following SQL:

DELETE FROM PSRECDEFN WHERE RECNAME = ''

RECORD-13

Temp records that specify a non-standard SQL table name.

Run the following SQL:

UPDATE PSRECDEFN SET SQLTABLENAME = ' ' WHERE RECTYPE = 7 AND SQLTABLENAME <> ' '

RECORD-14

The Field Number field in the RecordField table has an invalid value.

If the reported record was delivered by PeopleSoft or generated as part of an upgrade, contact the GCS. Run the following SQL to determine which fields need to be updated:

SELECT FIELDCOUNT FROM PSRECDEFN WHERE RECNAME='X'; SELECT FIELDNUM FROM PSRECFIELD WHERE RECNAME='X' ORDER BY FIELDNUM;

Note. The PSRECFIELD.FIELDNUM values should be numbered sequentially 1 through PSRECDEFN.FIELDCOUNT. If any value is skipped then renumber the FIELDNUM values accordingly.

Click to jump to top of pageClick to jump to parent topicRelated 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'

Click to jump to top of pageClick to jump to parent topicSecurity Integrity

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

Query

Description

Resolution

SEC-1

Authorized Signon Operator does not exist in the Class Definition table. Incomplete permission list: Orphan signon times:

(Verifies the existence of permission lists owning signon times.)

Delete the extra signon times. If this is a permission list that should exist, recreate it through PeopleTools Security.

DELETE FROM PSAUTHSIGNON WHERE CLASSID='x'

SEC-2

Incomplete permission list: Orphan page permissions:

(Verifies the existence of permission lists owning page permissions.)

Delete the extra page permissions. If this is a permission list that should exist, recreate it through PeopleTools Security.

DELETE FROM PSAUTHITEM WHERE CLASSID='x'

SEC-3

Incomplete permission list: Orphan process groups:

(Verifies existence of permission lists owning process groups.)

Delete the extra process group authorizations. If this is a permission list that should exist, recreate it through PeopleTools Security.

DELETE FROM PSAUTHPRCS WHERE CLASSID='x'

SEC-4

Incomplete permission list: Orphan process profiles:

(Verifies existence of permission lists owning process profiles.)

Delete the extra process profiles. If this is a permission list that should exist, recreate it through PeopleTools Security.

DELETE FROM PSPRCSPRFL WHERE CLASSID='x'

SEC-5

Permission list references a nonexistent process group:

(Verifies the existence of process groups.)

Delete the extraneous process groups. If this group should exist, recreate it.

DELETE FROM PSAUTHPRCS WHERE CLASSID='x' AND PRCSGRP = 'y'

SEC-6

User profile references a role that does not exist:

Open the user profile in PeopleTools Security and remove the reference to the Role that does not exist.

SEC-7

Role references a permission list that does not exist:

Open the Role in PeopleTools Security and remove the reference to the Permission List that does not exist.

SEC-8

Role references a user that does not exist in the PSOPRDEFN table.

Remove the user from the PSROLEUSER table.

SEC-9

Permission list references a role that does not exist in the PSROLEDEFN table.

Remove the role from the PSROLECLASS table.

SEC-17

Primary Permission List specified for user does not exist in the Permission List Definition table: (Verifies the existence of Permission Lists Assigned to User Profiles Primary Permission List.)

If this is a Permission List that should exist, recreate it through PeopleTools Security. Otherwise, Delete the Primary Permission List value for the User Profiles via the following SQL:

UPDATE PSOPRDEFN A SET A.OPRCLASS = '' WHERE A.OPRCLASS <> '' AND NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A.OPRCLASS)

SEC-18

User named in a User-Role relationship does not exist in the User Definition table: (Verifies the existence of Users owning Roles.)

If this is a User that should exist, recreate it through PeopleTools Security. Otherwise, Delete the User from the Role.

DELETE FROM PSROLEUSER WHERE NOT EXISTS (SELECT 'X' FROM PSOPRDEFN B WHERE B.OPRID = A.ROLEUSER)

SEC-19

User named in a User-Role relationship does not exist in the User Definition table: (Verifies the existence of Users owning Roles.)

If this is a Role that should exist, recreate it through PeopleTools Security. Otherwise, Delete the Role from the User.

DELETE FROM PSROLEUSER WHERE NOT EXISTS (SELECT 'X' FROM PSROLEDEFN B WHERE B.ROLENAME = A.ROLENAME)

SEC-20

Role named in a Role-Permission List relationship does not exist in the Role Definition table: (Verifies existence of Roles owning Permission Lists.)

If this is a Role that should exist, recreate it through PeopleTools Security. Otherwise, Delete the Role From the Permission List.

DELETE FROM PSROLECLASS WHERE NOT EXISTS (SELECT 'X' FROM PSROLEDEFN B WHERE B.ROLENAME = A.ROLENAME)

SEC-21

Permission List named in a Role-Permission List relationship does not exist in the Permission List Definition table: (Verifies the existence of process groups.)

If this is a Permission List that should exist, recreate it through PeopleTools Security. Otherwise, Delete the Permission List from the Role.

DELETE FROM PSROLECLASS WHERE NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A.CLASSID)

SEC-24

Row Level Security Permission List specified for user does not exist in the Permission List Definition table:

(Verifies the existence of Permission Lists Assigned to User Profiles Row Security Permission List.)

If this is a permission list that should exist, recreate it through PeopleTools Security. Otherwise, Delete the Row Security Permission List value for the User Profiles via the following SQL:

UPDATE PSOPRDEFN A SET A.ROWSECCLASS = '' WHERE A.ROWSECCLASS <> '' AND NOT EXISTS(SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A.ROWSECCLASS)

SEC-25

Process Profile Permission List specified for user does not exist in the Permission List Definition table: (Verifies the existence of Permission Lists Assigned to User Profiles Process Profile Permission List.)

If this is a permission list that should exist, recreate it through PeopleTools Security. Otherwise, Delete the Process Profile Permission List value for the User Profiles via the following SQL:

UPDATE PSOPRDEFN A SET A.PRCSPRFCLS = '' WHERE A.PRCSPRFCLS <> '' AND NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A.PRCSPRFCLS)

SEC-26

Navigator Homepage Permission List specified for user does not exist in the Permission List Definition table: (Verifies the existence of Permission Lists Assigned to User Profiles Navigator Homepage Permission List.)

If this is a permission list that should exist, recreate it through PeopleTools Security. Otherwise, Delete the Navigator Homepage Permission List value for the User Profiles via the following SQL:

UPDATE PSOPRDEFN A SET A.DEFAULTNAVHP = '' WHERE A.DEFAULTNAVHP = '' AND NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A. DEFAULTNAVHP)

SEC-27

Access Profile specified for user does not exist in the Access Profile table:

If this is an Access Profile that should exist, recreate it through Application Designer/Data Mover and or your DBA. Otherwise, manually change the value to a valid Access Profile or blank via Security. It is not advised to blank out the Access Profile unless you intend to disable the User Profile. If so, you can delete the Access Profile value for the User Profiles via the following SQL:

DELETE FROM PS_SCRTY_SRCHGRP A WHERE NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A.CLASSID)

SEC-28

Invalid entries in the PSAUTHITEM table.

(Continues in next row)

Run the following SQL:

DELETE FROM PSAUTHITEM WHERE (PSAUTHITEM.MENUNAME NOT LIKE 'WEBLIB_%'AND PSAUTHITEM.MENUNAME NOT IN ('CLIENTPROCESS', 'DATA_MOVER', 'IMPORT_MANAGER', 'OBJECT_SECURITY', 'QUERY', 'PERFMONPPMI') AND PSAUTHITEM.MENUNAME NOT LIKE ('APPLICATION_DESIGNER%') AND PSAUTHITEM.MENUNAME <> 'REN' AND NOT EXISTS (SELECT 'X' FROM PSMENUITEM MI WHERE PSAUTHITEM.MENUNAME = MI.MENUNAME AND PSAUTHITEM.BARNAME = MI.BARNAME AND PSAUTHITEM.BARITEMNAME = MI.ITEMNAME AND (MI.ITEMTYPE IN (0, 1, 2, 3,4, 6, 7, 8, 10, 11) OR (MI.ITEMTYPE = 5 AND EXISTS (SELECT 'X' FROM PSPNLGRPDEFN GD, PSPNLGROUP GI WHERE MI.PNLGRPNAME = GD.PNLGRPNAME AND MI.MARKET = GD.MARKET AND GD.PNLGRPNAME = GI.PNLGRPNAME AND GD.MARKET = GI.MARKET AND PSAUTHITEM.PNLITEMNAME = GI.ITEMNAME))

SEC-28

(Continued)

OR (MI.ITEMTYPE = 9 AND EXISTS (SELECT 'X' FROM PSPCMNAME PCN, PSPCMPROG PCP WHERE PCN.OBJECTID1 = 3 AND PCN.OBJECTVALUE1 = MI.MENUNAME AND PCN.OBJECTID2 = 4 AND PCN.OBJECTVALUE2 = MI.BARNAME AND PCN.OBJECTID3 = 5 AND PCN.OBJECTVALUE3 = MI.ITEMNAME AND PCN.OBJECTID4 = 12 AND PCN.OBJECTVALUE4 = 'ItemSelected' AND PCN.OBJECTID1 = PCP.OBJECTID1 AND PCN.OBJECTVALUE1 = PCP.OBJECTVALUE1 AND PCN.OBJECTID2 = PCP.OBJECTID2 AND PCN.OBJECTVALUE2 = PCP.OBJECTVALUE2 AND PCN.OBJECTID3 = PCP.OBJECTID3 AND PCN.OBJECTVALUE3 = PCP.OBJECTVALUE3 AND PCN.OBJECTID4 = PCP.OBJECTID4 AND PCN.OBJECTVALUE4 = PCP.OBJECTVALUE4)) OR (MI.ITEMTYPE = 12 AND EXISTS (SELECT 'X' FROM PSXFERITEM XI WHERE MI.MENUNAME = XI.MENUNAME AND MI.ITEMNAME = XI.ITEMNAME)))))

SEC-28

(Continued)

OR (PSAUTHITEM.MENUNAME LIKE 'WEBLIB_%' AND NOT EXISTS (SELECT 'X' FROM PSPCMPROG PCP WHERE PCP.OBJECTID1 = 1 AND PCP.OBJECTVALUE1 = PSAUTHITEM.MENUNAME AND PCP.OBJECTID2 = 2 AND PCP.OBJECTVALUE2 = PSAUTHITEM.BARNAME))OR (PSAUTHITEM.MENUNAME IN ('CLIENTPROCESS', 'DATA_MOVER', 'IMPORT_MANAGER', 'OBJECT_SECURITY', 'QUERY', 'PERFMONPPMI') AND (PSAUTHITEM.BARNAME <> ' ' OR PSAUTHITEM.BARITEMNAME <> ' ' OR PSAUTHITEM.PNLITEMNAME <> ' ')) OR (PSAUTHITEM.MENUNAME LIKE ('APPLICATION_DESIGNER%') AND ((PSAUTHITEM.BARNAME <> ' ' AND PSAUTHITEM.BARNAME NOT IN (SELECT OBJNAME FROM PS_APP_DES_OBJECTS WHERE PSAUTHITEM.BARNAME = OBJNAME)) OR PSAUTHITEM.BARITEMNAME <> ' ' OR PSAUTHITEM.PNLITEMNAME <> ' ')) OR PSAUTHITEM.MENUNAME = 'REN' AND ((PSAUTHITEM.BARNAME <> ' ' AND PSAUTHITEM.BARNAME NOT IN (SELECT OBJNAME FROM PS_APP_DES_OBJECTS WHERE PSAUTHITEM.BARNAME = OBJNAME)) OR PSAUTHITEM.BARITEMNAME <> ' 'OR PSAUTHITEM.PNLITEMNAME <> ' ' )

SEC-29

The displayed PSPRSMPERM rows contain invalid PORTAL_PERMTYPE values.

Run the following SQL:

DELETE FROM PSPRSMPERM WHERE PORTAL_PERMTYPE = ' ' AND EXISTS (SELECT 'X' FROM PSPRSMPERM PP2 WHERE PSPRSMPERM.PORTAL_NAME = PP2.PORTAL_NAME AND PSPRSMPERM.PORTAL_REFTYPE = PP2.PORTAL_REFTYPE AND PSPRSMPERM.PORTAL_OBJNAME = PP2.PORTAL_OBJNAME AND PSPRSMPERM.PORTAL_PERMNAME = PP2.PORTAL_PERMNAME AND PP2.PORTAL_PERMTYPE <> ' '); UPDATE PSPRSMPERM SET PORTAL_PERMTYPE = 'P' WHERE PORTAL_PERMTYPE = ' ' AND EXISTS (SELECT 'X' FROM PSCLASSDEFN WHERE CLASSID = PSPRSMPERM.PORTAL_PERMNAME);

SEC-30

Missing users in the PS_ROLEXLATOPR table.

Every User that is defined in the PSOPRDEFN table should have a corresponding Role User entry in the PS_ROLEXLATOPR table.

SEC-31

Verify that the user definition table PSOPRDEFN has an entry corresponding to each user assigned to a role.

The role users returned by the audit do not have corresponding user IDs in the PSOPRDEFN table. That is, the user ID’s don’t exist. These role users should be removed from the PS_ROLEXLATOPR table.

Run the following SQL:

DELETE FROM PS_ROLEXLATOPR A WHERE NOT EXISTS (SELECT 'X' FROM PSOPRDEFN B WHERE B.OPRID = A.OPRID)

SEC-32

Verify that no inactive roles exist in the PSROLEDEFN table.

The roles returned by the audit need to be fixed by either deleting them or making them active.

To remove a role, use the following SQL:

DELETE FROM PSROLEDEFN WHERE ROLESTATUS <> 'A'

Or, use the Delete Roles page. Select PeopleTools, Security, Permissions & Roles, Delete Roles.

To activate inactive roles, use the following SQL:

UPDATE PSROLEDEFN SET ROLESTATUS = 'A' WHERE ROLESTATUS <>'A'

SEC-34

Incomplete permission list: orphan service operation.

Use the Integration Broker interface to open the service operation listed in the audit. On the Web Service Access page, remove any invalid permission lists.

Or, submit the following SQL:

DELETE FROM PSAUTHWS A WHERE NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A.CLASSID)

SEC-35

Incomplete Permission List: Orphan Search Groups: (Verifies the existence of Permission Lists owning Search Groups.)

If this is a Permission List that should exist, recreate it through PeopleTools Security. Otherwise, Delete the Permission List value for a Search Group via the following SQL:

DELETE FROM PS_SCRTY_SRCHGRP A WHERE NOT EXISTS (SELECT 'X' FROM PSCLASSDEFN B WHERE B.CLASSID = A.CLASSID)

SEC-36

Permission List references a non-existent Search Group: (Verifies the existence of Search Groups Assigned to Permission Lists.)

Use the Integration Broker interface to open the service operation listed in the audit. On the Web Service Access page, remove any invalid permission lists. Or, submit the following SQL:

DELETE FROM PS_SCRTY_SRCHGRP A WHERE NOT EXISTS (SELECT 'X' FROM PSPTSF_SRCCAT B WHERE B.PTSF_ISGBLSRCH = 'Y' AND B.PTSF_SRCCAT_NAME = A.PTSF_SRCHGRP_NAME)

Click to jump to top of pageClick to jump to parent topicSQL Integrity

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

Query

Description

Resolution

SQL-01

SQL text without a base definition.

Run the following SQL:

DELETE FROM PSSQLTEXTDEFN WHERE SQLID NOT IN (SELECT DISTINCT SQLID FROM PSSQLDEFN)

SQL-02

SQL definitions without SQL text.

Run the following SQL:

DELETE FROM PSSQLDEFN WHERE SQLID NOT IN (SELECT DISTINCT SQLID FROM PSSQLTEXTDEFN)

SQL-03

SQL descriptions without a base definition.

Run the following SQL:

DELETE FROM PSSQLDESCR WHERE SQLID NOT IN (SELECT DISTINCT SQLID FROM PSSQLDEFN)

SQL-04

SQL descriptions without associated SQL text.

Run the following SQL:

DELETE FROM PSSQLDESCR WHERE SQLID NOT IN (SELECT DISTINCT SQLID FROM PSSQLTEXTDEFN)

SQL-05

AE SQL without SQL definitions.

This reveals Application Engine SQL Actions that do not contain any SQL code within them.

Open the Application Engine program and complete the entry of the SQL before attempting to run the program.

If the empty SQL actions are delivered by PeopleSoft, open an incident with the GCS to report the corrupt AE program.

SQL-06

AE SQL that's not referenced.

This reveals an Application Engine SQL object that is not being referenced by an AE program. This indicates that the AE program is deleted but the associated SQL is not. The orphaned SQL does not cause issues other than consuming disk space.

If the orphaned SQL is delivered by PeopleSoft, open an incident with GCS to make sure that it is not a symptom of a larger problem, such as a corrupted AE application.

SQL-07

Record Views/Dynamic Views without SQL definitions.

Complete the entry of the record view or dynamic view before attempting to build or create the view. Each record should be opened, and the SQL should be entered as required.

SQL-08

View SQL that are not referenced by record or dynamic views.

Run the following SQL:

DELETE FROM PSSQLDEFN WHERE SQLTYPE = 2 AND SQLID NOT IN (SELECT DISTINCT RECNAME FROM PSRECDEFN WHERE RECTYPE = 5 OR RECTYPE = 1) DELETE FROM PSSQLDESCR WHERE SQLTYPE = 2 AND SQLID NOT IN (SELECT DISTINCT RECNAME FROM PSRECDEFN WHERE RECTYPE = 5 OR RECTYPE = 1) DELETE FROM PSSQLTEXTDEFN WHERE SQLTYPE = 2 AND SQLID NOT IN (SELECT DISTINCT RECNAME FROM PSRECDEFN WHERE RECTYPE = 5 OR RECTYPE = 1)

Click to jump to top of pageClick to jump to parent topicStyle Sheet Integrity

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

Query

Description

Resolution

STYLESHEET-1

Orphaned free form style sheet data.

Issue the following SQL to remove the orphaned rows:

DELETE * FROM PSCONTDEFN WHERE CONTTYPE = 9 AND CONTNAME = %1; DELETE * FROM PSCONTENT WHERE CONTTYPE = 9 AND CONTNAME = %1; DELETE * FROM PSCONTDEFNLANG WHERE CONTTYPE = 9 AND CONTNAME = %1; DELETE * FROM PSCONTENTLANG WHERE CONTTYPE = 9 AND CONTNAME = %1;

STYLESHEET-2

Orphaned free form style sheet definitions.

Use Application Designer to delete the free form style sheet(s).

STYLESHEET-3

Parent style sheet not found.

Open the affected style sheet(s) in Application Designer. Remove the reference to the nonexistent parent in the Parent Style Sheet dropdown on the Style Sheet Properties dialog box.

STYLESHEET-04

Sub Style Sheet not found.

Use Application Designer to delete references to any missing sub style sheets.

Click to jump to top of pageClick to jump to parent topicTree Integrity

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

Query

Description

Resolution

TREE-01

Tree Structure table contains Level Record name that does not exist in Record Definition table

Use Tree Manager to open the structure and fix the invalid fields.

TREE-02

Tree Structure table contains Level Page name that does not exist in Page Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-03

Tree Structure table contains Node Record name that does not exist in Record Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-04

Tree Structure table contains Node Field name that does not exist in RecordField table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-05

Tree Structure table contains Node Page name that does not exist in Page Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-06

Tree Structure table contains Detail Record name that does not exist in Record Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-07

Tree Structure table contains Detail Record name that does not exist in Record Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-08

Tree Structure table contains Detail Page name that does not exist in Page Definition table.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-09

Tree Structure table contains Summary Tree that does not exist in Tree Level table.

See the following section on Notes for TREE-09.

TREE-10

Tree Structure table contains Level Menu-Menu Bar combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-11

Tree Structure table contains Node Menu-Menu Bar combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-12

Tree Structure table contains Detail Menu-Menu Bar combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-13

Tree Structure table contains Level Menu-Page combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-14

Tree Structure table contains Node Menu-Page combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-15

Tree Structure table contains Detail Menu-Page combination that does not exist.

Use Tree Manager to open the structure and fix the invalid fields.

TREE-16

Tree Definition Level count does not match the record count in Tree Level table.

Set the Count in the Tree Definition table to reflect the actual number of records that are in the PSTREELEVEL table for this tree branch. Note that a problem may occur if some levels are missing and there are still nodes referencing them. In this case, the nodes do not open the tree correctly. The third SELECT checks for the previous situation. If this is the problem, run PSTED, and define the missing levels, save the tree, and then close and reopen it.

SELECT COUNT(*) FROM PSTREELEVEL WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt'; UPDATE PSTREEDEFN SET LEVEL_COUNT = $count WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt';

TREE-17

Tree Definition Node count does not match the record count in Tree Node table.

Set the count in the Tree Definition table to reflect the actual number of the records that are in the PSTREENODE table for this tree branch.

SELECT COUNT(*) FROM PSTREENODE WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' AND TREE_BRANCH = 'tree_branch_name'; UPDATE PSTREEDEFN SET NODE_COUNT = $count WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' AND TREE_BRANCH = 'tree_branch_name';

   

For branched trees, use following SQL for each branch in the tree.

Note. For trees with branches, the UPDATE also uses a different TABLE NAME and there are two UPDATE statements.

SELECT COUNT(*) FROM PSTREENODE WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' AND TREE_BRANCH = 'tree_branch_name'; UPDATE PSTREEBRANCH SET NODE_COUNT = $count WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' AND TREE_BRANCH = 'tree_branch_name' UPDATE PSTREEDEFN SET NODE_COUNT = 0, WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' AND TREE_BRANCH = 'tree_branch_name'

   

For trees without branches, do not include the "TREE_BRANCH=" lines. For example:

SELECT COUNT(*) FROM PSTREENODE WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' SELECT COUNT(*) FROM PSTREENODE WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' UPDATE PSTREEDEFN SET NODE_COUNT = 0, WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt'

TREE-18

Tree Definition Leaf count does not match the record count in Tree Leaf table.

Set the Count in the Tree Definition table to reflect the actual number of records that are in the PSTREELEAF table for this branch.

SELECT COUNT (*) FROM PSTREELEAF WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' AND TREE_BRANCH = 'tree_branch_name'; UPDATE PSTREEDEFN SET LEAF_COUNT = $count WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' AND TREE_BRANCH = 'tree_branch_name';

TREE-19

Tree Definition contains Structure ID that does not exist in Tree Structure table

Use Tree Manager to create the structure that you desire by using the name that is reported in this audit.

TREE-20

Tree Definition contains Query Access Group structure with undefined levels and leaves.

Query trees should have no leaves and no levels. This audit finds exceptions to that case in the definition counts.

UPDATE PSTREEDEFN SET LEVEL_COUNT = 0, LEAF_COUNT = 0 WHERE TREE_STRCT_ID = 'ACCESS_GROUP' AND (LEVEL_COUNT <> 0 OR LEAF_COUNT <> 0);

TREE-21

Tree Selector Control contains Tree name that is not defined in Tree Definition table.

This audit flags records in the PSTREESELCTL tables for records that don't have a corresponding record in the PSTREEDEFN table.

DELETE FROM PSTREESELCTL A WHERE NOT EXISTS (SELECT 'X' FROM PSTREEDEFN B WHERE B.SETID = A.SETID AND B.TREE_NAME = A.TREE_NAME AND B.EFFDT = A.EFFDT)

TREE-22

Tree Definition Level count does not match level use.

See the section titled “Notes for TREE-22” below.

TREE-23

Tree Level does not exist in Tree Definition table.

Tree Level records in the PSTREELEVEL table exist for trees that don't exist in the PSTREEDEFN table.

DELETE FROM PSTREELEVEL A WHERE NOT EXISTS (SELECT 'X' FROM PSTREEDEFN B WHERE B.SETID = A.SETID AND B.TREE_NAME = A.TREE_ NAME AND B.EFFDT = A.EFFDT)

TREE-24

Tree Node does not exist in Tree Definition table.

Tree Node records in the PSTREENODE table exist for trees that don't exist in the PSTREEDEFN table.

DELETE FROM PSTREENODE A WHERE NOT EXISTS (SELECT 'X' FROM PSTREEDEFN B WHERE B.SETID = A.SETID AND B.TREE_NAME = A.TREE_NAME AND B.EFFDT = A.EFFDT)

TREE-25

Tree Leaf does not exist in Tree Definition table.

Tree Leaf records in the PSTREELEAF table exist for trees that don't exist in the PSTREEDEFN table.

DELETE FROM PSTREELEAF A WHERE NOT EXISTS (SELECT 'X' FROM PSTREEDEFN B WHERE B.SETID = A.SETID AND B.TREE_NAME = A.TREE_NAME AND B.EFFDT = A.EFFDT)

TREE-26

Tree Leaf ranges are not valid in Tree Definition table.

Finds records in the PSTREELEAF table where RANGE_FROM is less than RANGE_TO.

Use Tree Manager to open the tree and correct the invalid range values.

TREE-27

Tree Leaf does not have parent Tree Node in Tree Definition table.

Run this SQL:

DELETE FROM PSTREELEAF A WHERE NOT EXISTS (SELECT 'X' FROM PSTREENODE B WHERE B.SETID = A.SETID AND B.TREE_NAME = A.TREE_NAME AND B.EFFDT = A.EFFDT AND B.TREE_NODE_NUM = A.TREE_NODE_NUM)

TREE-28

Tree Branch does not exist in Tree Branch table.

Refer to the "Tree Audit and Repair Utilities" chapter in the Tree Manager PeopleBook and run the Unbranch Tree Repair Utility so that all branches are removed from the tree.

TREE-29

Tree Branch does not exist in Tree Branch table.

Refer to the "Tree Audit and Repair Utilities" chapter in the Tree Manager PeopleBook and run the Unbranch Tree Repair Utility so that all branches are removed from the tree.

TREE-30

Tree Branch Node count does not match the record count in Tree Node table.

See Resolution for Tree-29.

TREE-31

Tree Branch Leaf count does not match the record count in Tree Leaf table

See Resolution for Tree-29.

TREE-32

Tree Node Num, Node Num End, or Level Num is invalid in Tree Branch table.

See Resolution for Tree-29.

TREE-33

Identify all orphan access group definitions as well as invalid access group definitions in the access group security.

Open Query Access Group Tree in Query Access Group Manager and update the identified Access Group so that a record is created in the Access Group Table.

TREE-34

Tree Definition Node Count Does Not Equal 0 for a Branched Tree.

See Resolution for Tree-29.

TREE-35

Tree Definition Leaf Count Does Not Equal 0 for a Branched Tree.

See Resolution for Tree-29.

Notes for TREE-09

Lists any Summary Tree Structures that reference a level number that is on a Detail Tree that does not exist in the Tree Level table. Since a Summary Tree is a tree that is built off of the nodes from an existing Detail Tree at a given level, the level that is specified on the Summary Tree Structure must exist in the detail tree's PSTREELEVEL table. In this case, the Summary Tree is not usable from nVision or other reporting tools.

The situation could occur from several possible causes :

To correct this :

  1. First determine if Detail Tree exists and is in a valid state. This can be done by checking the name of the Detail Tree on the Summary Tree's Structure record; check the Summary Tree tab on the Tree Structure record for the Summary Tree. Note the tree name, setID, and level number.

  2. If Detail Tree exists, check to see if the level number that is defined on the Summary Tree Structure (step 1) exists.

  3. To correct the situation, either add missing level to detail tree or update Summary Tree Structure to refer to a valid detail tree and level number.

Notes for TREE-22

This audit flags the Level Use type with the Level Count for conflicts. When the Level Use is N, there should be no levels defined, and when it is not N, levels should be defined. A problem in this audit may also report problems in the TREE-16 audit.

When the Level Use is N and the Level Count is 0 and TREE-16 does not indicate an error on the same tree, run the following SQL:

UPDATE PSTREEDEFN SET USE_LEVELS = 'S' WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt'

When the Level Use is S and the Level Count is 0 and TREE-16 does not indicate an error on the same tree, run the following SQL (after checking the resolution on TREE-16 to clean up any level records):

UPDATE PSTREEDEFN SET LEVEL_COUNT = 0 WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt'

When the Level Use is not N and the Level Count is 0 and TREE-16 does not indicate an error on the same tree, run the following SQL (after checking the resolution on TREE-16 to clean up any level records):

UPDATE PSTREEDEFN SET USE_LEVELS = 'N' WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' UPDATE PSTREENODE SET TREE_LEVEL_NUM = 0 WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt'

When TREE-23 indicates an error on the same Tree with the Level Count on the PSTREEDEFN = number of PSTREELEVEL records (when the PSTREELEVEL has no levels for this tree, count is 0 ), run the following SQL:

SELECT COUNT(*) FROM PSTREELEVEL WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' UPDATE PSTREEDEFN SET LEVEL_COUNT = 0 WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' UPDATE PSTREEDEFN SET USE_LEVELS = 'N' WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt' UPDATE PSTREENODE SET TREE_LEVEL_NUM = 0 WHERE TREE_NAME = 'tree_name' AND SETID = 'setid' AND EFFDT = 'effdt'

Click to jump to top of pageClick to jump to parent topicTranslate Integrity

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

Query

Description

Resolution

XLATT-1

Translate table Field does not exist in database Field.

Create the field by using Application Designer.

XLATT-3

Translate fields do not have associated translate values defined.

Edit translate field and enter translate value.

Click to jump to top of pageClick to jump to parent topicPSLOCK Version Integrity

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

Query

Description

Resolution

Manager-XXX

Where XXX is the associated three-letter code of the object type.

Version Check of listed table against PSVERSION.

Run the VERSION Application Engine program.

Click to jump to top of pageClick to jump to parent topicBI Publisher Integrity

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

Query

Description

Resolution

BIP-01

Query Data Source does not exist in Query Definition table.

Remove the data source and all report definitions using the data source.

BIP-02

Data Source Definition used by a Report Definition but does not exist in Data Source Definition table.

If this error is observed after the upgrade copy process, try copying the data source object from source database. If this is not the case, remove the report definition.

BIP-03

Template Definition used by a Report Definition but does not exist in Template Definition table.

If this error is observed after the upgrade copy process, try copying the missing template definition from the source database. If this is not the case, remove the report definition.

BIP-04

Template Definition not associated with any Report Definition.

Remove the template object.

BIP-05

Template Definition associated with more than one Report Definition.

Delete all the report definitions using the template object and recreate them again from the user interface.

BIP-06

Sub-Template Definition associated with any Report Definition.

Delete all the report definitions using the template object and recreate them again from the user inferface.

BIP-07

Template File does not exist in file table.

If this error is observed after the upgrade copy process, try copying the missing file definition from the source database. If this is not the case, the template definition should be deleted and recreated.

BIP-08

PDF Map File does not exist in file table.

If this error is observed after the upgrade copy process, try copying the missing file definition from the source database. If this is not the case, the template definition should be deleted and recreated.

BIP-09

XLIFF File does not exist in file table.

If this error is observed after the upgrade copy process, try copying the missing file definition from the source database. If this is not the case, the template definition should be deleted and recreated.

BIP-10

File definition not used by template file definition.

Run Application Engine program PSXPCLEAN to delete orphan file definitions.

BIP-11

File data not referenced by file definition.

Run Application Engine program PSXPCLEAN to delete orphan file definitions.

BIP-12

File definitions not referenced by file data.

Run Application Engine program PSXPCLEAN to delete orphan file definitions.

Click to jump to top of pageClick to jump to parent topicPivot Grid Integrity

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

Query

Description

Resolution

PIVOT-01

Pivot Grid Model referencing a nonexistent data type.

This should generally not happen and is an upgrade issue. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script..

PIVOT-02

Pivot Grid definitions not having a valid Pivot Grid Model.

This indicates a partially saved, unusable model. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-03

Pivot Grid Definitions not having a default view.

This indicates a partially saved, unusable model. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-04

Pivot Grid Definitions not having default grid options.

This indicates a partially saved, unusable model. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-05

Pivot Grid Definitions not having default chart options.

This indicates a partially saved, unusable model. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-06

Pivot Grid Model based on a nonexistent PSQuery.

This can happen if the PSQuery associated with the Pivot Grid Model is deleted. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-07

PSQuery field name in PSQRYFIELD table does not match field defined in PSPGMODEL table.

This can happen if the field in PSQuery is modified. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-08

PSQuery field headings in PSQRYFIELD table do not match column name defined in PSPGMODEL table.

This can happen if the field in PSQuery is modified. In such cases, using the Pivot Grid Wizard re-select all the relevant columns and save the Model again.

PIVOT-09

PSQuery prompt name in PSPGQRYPROMPT table for Pivot Grid does not match the bind field defined in the PSQRYBIND table.

This can happen if the PSQuery prompts are modified. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-10

PSQuery bind name in PSQRYBIND does not match prompt defined in PSPGQRYPROMPT table for Pivot Grid.

This can happen if the PSQuery prompts are modified. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-11

PSQuery bind variable heading in PSQRYBIND does not match prompt name defined in PSPGQRYPROMPT table for Pivot Grid.

This can happen if the PSQuery prompts are modified. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

PIVOT-12

PSQuery bind variable prompt records in PSQRYBIND table do not match prompt record defined in PSPGQRYPROMPT table for Pivot Grid.

This can happen if the PSQuery prompts are modified. In such cases, mark the Model as Invalid by unchecking the “Valid” flag for the Model. Then delete the model using the Delete_All_InvalidModels.dms script.

Click to jump to top of pageClick to jump to parent topicSearch Integrity

The following table describes the audit queries and resolutions for the PeopleSoft Search Framework:

Query

Description

Resolution

PTSF-01

Search definition mapped in a search category is missing.

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

Also check, People Tools, Search Framework, Designer, Search Definition

PTSF-02

There is no search category created for a search definition.

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

Also check, PeopleTools, Search Framework, Designer, Search Categories

PTSF-03

Search category does not contain a search definition with the same name as search category

This report is provided for informational purposes. It is not necessary for a category to have a definition with the same name. However the reverse case is a mandate. A search category can contain any number of search definitions which are deployed by its own categories.

PTSF-04

Source name in search definition is not available in Query or Connected Query definitions.

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

Also check Reporting Tools, Query, Query Manager

PTSF-05

Delete query mapped to a search definition is missing in query definition

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

Also check Reporting Tools, Query, Query Manager

PTSF-06

Pre/Post processing AE mapped to a search definition is missing in Application Engine library

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

PTSF-07

Security Application Class specified in a search definition is missing in Application Class library.

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

PTSF-08

Indexed field in a search definition does not exist in the query fields list

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

PTSF-09

Attribute selected in a search definition is missing in the search attribute list

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

PTSF-10

Selected attribute display name does not exists in the field label definition

If the definitions are delivered by Oracle and not modified, contact GCS.

If the definitions are customized or the issue appears after a project migration or upgrade, please verify the related definitions are included in the project.

PTSF-11

Attribute in the search attribute list is not referenced by any search definition

Use your SQL Editor to delete the unused attributes from record PSPTSF_ATTRS.

Click to jump to top of pageClick to jump to parent topicRelated Content Integrity

The following table describes the audit queries and resolutions for the PeopleSoft Related Content:

Query

Description

Resolution

RELCONTENT-01

This audit verifies that the PSVERSION table contains the entries for the Related Content managed objects.

Run the VERSION Application Engine program to reset the PSVERSION and PSLOCK entry.

RELCONTENT-02

This audit verifies that the PSLOCK table contains the entries for the Related Content managed objects.

Run the VERSION Application Engine program to reset the PSVERSION and PSLOCK entry.

RELCONTENT-03

This audit lists all the missing service definitions required by the service configuration.

Use the Application Designer to import the missing Related Content Definitions into the target database.

RELCONTENT-04

This audit lists all the missing menu entries required by service configuration.

Sign on to PeopleTools using the browser, and make a change on the Manage Service Configuration page add missing menu metadata.

RELCONTENT-05

This audit lists all mapped fields that are not part of any service configuration.

Delete orphaned mapped fields with this SQL:

DELETE FROM PSPTCS_MAPFLDS A WHERE A.PTCS_SERVICEID NOT IN (SELECT B.PTCS_SERVICEID FROM PSPTCSSRVCONF B)

RELCONTENT-06

This audit lists all the root folders that are not part of any menu.

Delete orphan folders:

DELETE FROM PSPTCS_MNUFLDRS A WHERE A.PTCS_FOLDERID = A.PTCS_PRNT_FOLDERID AND NOT EXISTS (SELECT B.PTCS_FOLDERID FROM PSPTCS_MNULINKS B WHERE A.PORTAL_NAME = B.PORTAL_NAME AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME AND A.PTCS_MENUID = B.PTCS_MENUID AND A.PTCS_FOLDERID = B.PTCS_FOLDERID)

RELCONTENT-07

This audit lists all the menus with no folders.

Delete empty menus using this SQL:

DELETE FROM PSPTCS_MENU_TBL A WHERE NOT EXISTS (SELECT 'X' FROM PSPTCS_MNUFLDRS B WHERE A.PORTAL_NAME = B.PORTAL_NAME AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME AND A.PTCS_MENUID = B.PTCS_MENUID)

RELCONTENT-08

This audit lists all the missing application packages of service definitions.

Re-import the missing application packages using Application Designer.

RELCONTENT-09

This audit lists all the orphaned links.

Delete the orphaned links using this SQL:

DELETE FROM PSPTCS_MNULINKS A WHERE A.PTCS_FOLDERID NOT IN (SELECT B.PTCS_FOLDERID FROM PSPTCS_MNUFLDRS B WHERE A.PORTAL_NAME = B.PORTAL_NAME AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME AND A.PTCS_MENUID = B.PTCS_MENUID)

RELCONTENT-10

This audit lists all the orphaned folders.

Delete folders using this SQL:

DELETE FROM PSPTCS_MNUFLDRS A WHERE A.PTCS_MENUID NOT IN (SELECT B.PTCS_MENUID FROM PSPTCS_MENU_TBL B WHERE A.PORTAL_NAME = B.PORTAL_NAME AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME)

RELCONTENT-11

This audit lists all menu entries pointing to nonexistent service configurations.

Delete menu entries, using this SQL:

DELETE FROM PSPTCS_MNULINKS A WHERE NOT EXISTS (SELECT 'X' FROM PSPTCSSRVCONF B WHERE A.PORTAL_NAME = B.PORTAL_NAME AND A.PORTAL_OBJNAME = B.PORTAL_OBJNAME AND A.PTCS_SERVICEID = B.PTCS_SERVICEID AND A.PTCS_INSTANCEID = B.PTCS_INSTANCEID)

RELCONTENT-12

This audit lists missing application package PeopleCode for service definitions.

Use the Application Designer to re-import the missing application packages.

Click to jump to parent topicRunning SYSAUD01

This section provides an overview of how to run SYSAUD01.

Click to jump to top of pageClick to jump to parent topicUnderstanding SYSAUD01

SYSAUD01 is an SQR report used for system auditing that is a counterpart to the SYSAUDIT SQR report. SYSAUD01 contains additional sets of audits related to these PeopleTools technologies:

The resolutions for the audits for these technologies can be found within the SYSAUDIT resolutions documentation.

See Also

Feeds Integrity

Pivot Grid Integrity

Related Content Integrity

Search Integrity

Click to jump to top of pageClick to jump to parent topicRunning SYSAUD01

To run SYSAUD01:

  1. Select PeopleTools, Process Scheduler, System Process Requests.

  2. Select the appropriate run control ID from the System Process Requests page to run the SYSAUD01.SQR.

  3. Click Run.

  4. On the Process Scheduler Request page, select the SYSAUD01.SQR, and click OK.