Appendix: Referential Integrity

This appendix provides an overview of Referential Integrity (RI).

Background Processing

Time and Labor provides several batch processes to ensure that data is error-free when information changes online or when the system performs batch processing through Time Administration. Two of these processes are Referential Integrity (RI) and Time Validation. The system uses default processing to determine the correct information for processing time.

When you make a change to one object in the system, Referential Integrity process ensures that the change does not invalidate data in related objects.

RI starts working after you select the Referential Integrity check box on the Time and Labor Installation page.

The Referential Integrity process:

• Determines affected reported and payable time and time reporters.

  It checks for retroactive changes to effective-dated setup data or employee-related data that could invalidate related objects or values in the system. The system uses an RI router that determines the effect of changes to reported or payable time for affected time reporters. Only online updates trigger RI processing.

• Validates time during the Time Administration process.

• Creates or resolves exceptions during the Time Administration process.

  When processing data through Time and Labor, exceptions are created or resolved automatically, and payable time is recalculated as necessary.

• Updates TR status records to reevaluate payable time.

  When processing data through Time and Labor, RI evaluates changes and reevaluates affected time.

• Disallows the inactivation or deletion of certain records or the change of effective-dated rows.

  The system displays error messages when you try to inactivate, delete, or change an effective-dated row that invalidates the association with another table.

The Referential Integrity process checks for discrepancies and validates time for:

• Time collection device data

• Time collection groups

• Time reporter data

• TRCs

• Time reporting code programs

• Workgroups

• Taskgroups

• Task profiles

• Rule programs

• Shifts, Schedules, and schedule assignments

• Time period calendars

• Comp time plans

• Time reporter association to comp time plans

• DeptID change on the Job page

• The Department table

• Leave plans

• The Leave Plan table

The Referential Integrity process checks data when any reported or payable time exists on or after the effective-dated setup change.

Execute Edit Errors

When you try to change the effective date of a setup page to a date that is greater than a prompt value on that page, you receive an error message. For example, you receive an error message if you attempt to change the effective date of the workgroup to be greater than the effective date of the TRC Program. Workgroup is the target table and the TRC Program is the prompt table.

Message number 13500,103 in the message catalog states:

"Edit Error for field %1. The Value in the column specified is not a valid value. This error may have been caused by a change to the Effective Date or to some other field which controls the list of valid values for the field in question. This error can be corrected by selecting a valid value for this field from the Prompt list, or by correcting the changes which have caused this error. Another option is to cancel the changes and start over."

The field that will be filled in the %1 is the prompt field that is negatively affected by the effective-dated change on the target table. Changing the effective date in a target table to a date greater than that in the prompt table generates an error.

This table lists the relationships between target and prompt tables.

Nested Effective-Dated Tables

The RI process acts upon effective dates throughout the application. It does not enable effective-dated changes to objects that are nested in other effective-dated objects when the change invalidates the association between the two. It prevents this from occurring in these ways:

• When the nested object effective date is greater than the host object effective date, you receive an error message.

  For example, the effective date of the workgroup affects the Maintain Time Reporter Data table; the minimum effective date of the workgroup cannot be later than the effective date of the Maintain Time Reporter Data page. Also, the TRC Program effective date cannot be later than the effective date of the workgroup, TCD group, and so on.

• The system does not enable you to delete or inactivate a row that is used by another object.

• If you try to inactivate or delete an effective-dated row that uses another effective-dated object, the system verifies that the date does not invalidate the association with the nested effective-dated table.

  For example, if a workgroup is associated with a time reporter and you try to inactivate that workgroup, you will get a message that states that you cannot inactivate this workgroup because time reporters are participating in it.

See message number 13500,104 in the Message Catalog. The message states:

"Effective Date change invalidates related setup data; date must be on or before %1."

The %1 represents the effective date that needs to be maintained for the integrity of the nested object.

This table lists the relationships among nested objects; the source table cannot have an effective date greater than the target table:

Referential Integrity Triggers

When you change an effective-dated setup object, these events occur:

1. The system determines whether or not that change affects any reported or payable time.

   If a change affects reported or payable time, the system writes a row in a control record to identify the setup change and any validations to be run as a result.

2. The Referential Integrity process triggers the Time Validation process.

   The Time Validation process checks for any reported or payable time that is negatively affected by the setup change and triggers exceptions for invalid elements. If validation is unnecessary, the Referential Integrity process triggers Time Administration to reprocess payable time that is affected by the setup change.

3. When Time Administration runs, it initiates a separate RI review process.

   The RI process determines whether any rows exist in the control record to identify setup changes. If rows exist in the control record, the RI process determines which time reporters are affected by each change in the record. The affected time reporters will:

   • Change their Time Administration status so that they are selected for Time Administration processing.

   • Set their earliest change date to the first instance of reported or payable time that is equal to, or greater than, the effective date of the setup table change.

   • Run the Time Validation process against reported time that is equal to or greater than the new earliest change date.

4. Time Administration continues processing.

   The process runs rules, runs Time Administration Validation, and creates payable time based on the updated earliest change date.

Example

Today's date is February 28, 1990. You decide to change the TRC Program on Workgroup A for the effective date of January 1, 1990 from TRC Program A to TRC Program B.

All TRCs reported between the effective date of January 1, 1990 and January 1,1995 need review. If the TRCs reported during this period do not exist in TRC Program B, the system generates exceptions on the invalid time. Change the TRCs associated with that time or adjust the TRCs in TRC Program B to resolve these exceptions.

Note. For the RI process to correctly trigger changes to JOB Data, the DEPT_SYNC message must be active on the ENTERPRISE_SETUP queue, which is delivered with PeopleSoft Integration Broker.

See Also

PeopleSoft Enterprise Integration PeopleBook

Time Administration and Referential Integrity

Time Administration does the following:

• Provides the tools to create, maintain, and apply the organization's compensation, task, and exception rules to reported and scheduled time.

• Validates instances of reported time and generates payable time.

• Creates exceptions.

• Uses the RI process to check for setup changes that may affect processed time.

• Verifies that changes, either system generated or user generated changes, do not negatively affect the system or create errors related to reported or payable time.

• Processes validations before creating payable time.

  These include compensatory time, leave, TRC, task, and time reporter status validations. This ensures that the payable time is valid.

If you do not include the delivered validation definitions in your validation set, validations against reported time are not included in the Submit Time process.

Note. Time Administration calculates payable time for only those time reporters defined in the run control for Time Administration. The RI process updates the earliest change dates for time reporters defined in the run control. However, any other affected time reporters excluded from the current Time Administration run are reprocessed in the next Time Administration run with these time reporters.

This flow chart shows the high-level view of the RI process and Time Administration process:

Referential Integrity Process and Time Administration

See Also

Creating Rules in Time Administration

Understanding the Batch Process in Time Administration

Time Reporting Codes and Referential Integrity

When you make a change to one area of the application, the RI process makes sure the changes do not adversely affect another area of the application. It checks for retroactive changes to effective-dated setup data or employee related data that could invalidate related objects or values in the system.

Prevention of Inactivation or Deletion of Effective-Dated Rows

This table lists the pages and conditions when the system will not allow the inactivation or deletion of effective-dated rows.

Execute Edit Error Checking

You cannot change the minimum effective date on a page to be less than the minimum effective date of a field that acts as a prompt on this page.

Nested Effective Dates

You cannot change the effective date of the prompt table to be greater than the effective date of the target table.

Task Reporting and Referential Integrity

When you make a change to one area of Time and Labor, the RI process makes sure the changes do not adversely affect another area of the application. It checks for retroactive changes to effective-dated setup data or employee related data that could invalidate related objects or values in the system.

Prevention of Inactivation or Deletion of Effective-Dated Rows

This table lists the pages and conditions when the system will not allow the inactivation or deletion of effective-dated rows.

Execute Edit Error Checking

You cannot change the minimum effective date on a page to be less than the minimum effective date of a field that acts as a prompt on this page.

Nested Effective Dates

You cannot change the effective date of the prompt table to be greater than the effective date of the target table.

Time Administration and Referential Integrity Triggers

This section provides a table listing the situations in which Referential Integrity is initiated and is processed through Time Administration.

This table lists the RI triggers:

*Any effective dated change to JOB data for an employee causes the ECD for the employee to be moved back to the earliest instance of reported or payable time that is on or after the effective dated change to JOB data. When the Time Administration process is run a new row in the RI Control table validates this time for this employee, and processes validation TLX00830, which checks that the Commitment Accounting flag is in consistent with the taskgroup in reported and payable time, and the DEPTID on JOB data for the employee. The update to the RI Control table for the JOB data change is made possible through the use of the Workforce Sync message.

**Any change to a department in the Department table triggers the Department Sync message to insert a row into the RI Control table to validate that no employee that is associated with the department has a change in the commitment accounting flag that is inconsistent with the commitment accounting flag of the taskgroup used in reported or payable time for that employee, as of the effective date of the change or greater.

Understanding the TCD Interface and Referential Integrity

When you change one area of the application, referential integrity ensures that changes do not adversely affect another area of the application. The system checks for retroactive changes to effective-dated setup data or time-reporter-related data that could invalidate related objects or values.

The following sections describe referential integrity actions when certain changes, inactivations, or deletions are made during TCD processing.

Prevention of Inactivation or Deletion of Effective-Dated Rows

The following table lists the pages and conditions when the system will not allow the inactivation or deletion of effective-dated rows.

Execute Edit Error Checking

You cannot change the minimum effective date on a page to be less than the minimum effective date of a field that acts as a prompt on the page.

Nested Effective Dates

You cannot change the effective date of the prompt table to be greater than the effective date of the target table.