This appendix provides an overview of Referential Integrity (RI).
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.
Target Table (Main Page) |
Prompt Table (Table Assigned to Main Page) |
Workgroup |
Rule Program ID TRC Program Compensatory Time Off Plan |
Time Reporting Code |
Unit of Measure NA Earnings Code Global Payroll Take Code Global Payroll Earnings Code |
TRC Program |
Time Reporting Code |
Comp Time Plan |
Time Reporting Code |
Taskgroup |
Task Profile |
TCD Setup |
TCD Type TRC Program Taskgroup |
TL Empl Data (Create Time Reporter Data or Maintain Time Reporter Data) |
Workgroup Taskgroup TCD Group Rule Elements 1-5 |
Schedule |
Shift |
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:
Source Table (Prompt Table) Table Assigned to the Main Page |
Target Table (Main Page) |
Compensatory Time Off Plan |
Workgroup Comp Plan Enrollment |
Time Reporting Code |
TRC Program Compensatory Time Off Plan |
TRC Program |
Workgroup TCD Setup |
Rule Program |
Workgroup |
Workgroup |
Create/Maintain Time Reporter Data |
TCD Type |
TCD Setup |
TCD Setup |
TCD Group |
TCD Restriction Profile |
TCD Group |
TCD Group |
Create/Maintain Time Reporter Data |
Taskgroup |
Create/Maintain Time Reporter Data TCD Setup |
Task Profile |
Taskgroup |
Taskgroup Profile |
Taskgroup |
Rule Elements 1-5 |
Create/Maintain Time Reporter Data |
Job |
Create/Maintain Time Reporter Data |
Shift |
Schedule Definition |
Workday |
Schedule Definition |
Create/Maintain Time Reporter Data |
Comp Plan Enrollment |
Referential Integrity Triggers
When you change an effective-dated setup object, these events occur:
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.
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.
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.
Time Administration continues processing.
The process runs rules, runs Time Administration Validation, and creates payable time based on the updated earliest change date.
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.
Workgroup |
TRC Program ID |
Effective Date |
Workgroup A |
TRC Program A |
January 1, 1980 |
Workgroup A |
TRC Program B |
January 1, 1990 |
Workgroup A |
TRC Program A |
January 1, 1995 (future time can be reported up to this next effective date) |
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 Integration PeopleBook
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
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.
Page |
Condition |
TRC |
You cannot:
|
TRC Program |
You cannot:
|
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.
Page |
Prompts on Page |
TRC |
Unit of Measure, NA Earnings Code, GP Take Code, GP Earns Code |
TRC Program |
TRC |
Nested Effective Dates
You cannot change the effective date of the prompt table to be greater than the effective date of the target table.
Prompt Table |
Target Table |
TRC |
TRC Program, Compensatory Time Off Plan |
TRC Program |
Workgroup, TCD Setup |
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.
This table lists the pages and conditions when the system will not allow the inactivation or deletion of effective-dated rows.
Page |
Condition |
Taskgroup |
You cannot:
|
Task profile |
You cannot:
|
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.
Page |
Prompts on Page |
Taskgroup |
Task Profile |
You cannot change the effective date of the prompt table to be greater than the effective date of the target table.
Prompt Table |
Target Table |
Taskgroup |
Create/Maintain Time Reporter Data TCD Setup |
Task Profile |
Taskgroup |
Taskgroup Profile |
Taskgroup |
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:
Page |
Prompt on Page |
System Action |
Validation ID |
Trigger TA? |
Create/Maintain Time Reporter Data |
Workgroup |
Update |
TLX00440 |
Y |
|
Taskgroup |
Update |
TLX00070 |
Y |
|
|
|
TLX00090 |
|
|
|
|
TLX00100 |
|
|
Task Profile |
Update |
TLX00070 |
Y |
|
Rule Element 1-5 |
Update |
None |
Y |
Comp Plan Enrollment |
Compensatory Time Off Plan |
Add/Del/Update |
TLX00001 |
Y |
Time Reporting Code |
TRC Type − Changed to Hours |
Update |
TLX00490 |
Y |
|
TRC Type − Changed to Amount |
Update |
TLX00500 |
Y |
|
TRC Type − Changed to Units |
Update |
TLX00510 |
Y |
|
Maximum Quantity |
Update |
TLX00450 |
Y |
|
Minimum Quantity |
Update |
TLX00450 |
Y |
|
Flat Amount |
Update |
TLX00460 |
Y |
|
Rate |
Update |
TLX00470 |
Y |
|
Effect on Comp/Leave |
Update when indicator is changed from None to Comp Earned or Comp Taken |
TLX00001 |
Y |
|
|
Update when indicator is changed from None to Leave Taken |
TLX0010 |
Y |
TRC Program |
Time Reporting Code |
Add/Delete |
TLX00440 |
Y |
Workgroup |
TRC Program ID |
Update |
TLX00440 |
Y |
|
Rule Program ID |
Update |
None |
Y |
|
Comp Time Plan |
Update |
TLX00001 |
Y |
|
Holiday Schedule |
Update |
None |
Y |
|
Schedule ID |
Update |
None |
Y |
|
Needs Approval |
Update |
None |
Y |
|
Day Breaker |
Update |
None |
Y |
|
Day Breaker Range |
Update |
None |
Y |
|
Day Breaker Options |
Update |
None |
Y |
|
Round Punches Before Rules |
Update |
None |
Y |
|
Rounding Intervals (Mins) |
Update |
None |
Y |
|
Rounding Marker (Mins) |
Update |
None |
Y |
|
Round Duration After Rules |
Update |
None |
Y |
|
Rounding Options (Day, None, Segment) |
Update |
None |
Y |
Task Profile |
Task Profile ID |
Add/Update |
TLX00060 |
Y |
Taskgroup |
Taskgroup |
Add |
TLX00040 |
Y |
|
Task Template |
Update |
TLX00090 |
Y |
|
|
|
TLX00100 |
|
|
Default Task Profile ID |
Update |
None |
Y |
Taskgroup (Profile) |
Task Profile ID |
Inactivate |
TLX00070 |
Y |
|
|
|
TLX00080 |
|
|
|
Add |
TLX00070 |
Y |
|
|
|
TLX00080 |
|
Build Time Period Calendar |
Time Period ID |
Update |
None |
Y |
Rule Program |
Priority |
Update |
None |
Y |
|
Rule ID |
Update |
None |
Y |
Compensatory Time Off Plan |
Expiration Period |
Update |
TLX00001 |
Y |
|
Number |
Update |
TLX00001 |
Y |
|
Maximum Negative Hours Allowed |
Update |
TLX00001 |
Y |
|
Maximum Positive Hours Allowed |
Update |
TLX00001 |
Y |
Compensatory Time Off Plan (TRC) |
Time Reporting Code |
Add/Delete |
TLX00001 |
Y |
Assign Work Schedule |
Schedule ID |
Update |
None |
Y |
Override Scheduled Workday |
Shift |
Add/Update |
None |
Y |
Job Data |
All Fields |
Add/Update |
TLX00830* |
Y |
Department |
Use Actuals Distribution |
Update |
TLX00830** |
Y |
Leave Plan Table |
Maximum Negative Hours Allowed |
Update |
TLX00010 |
Y |
|
Accrued Balance Goes Negative |
Update |
TLX00010 |
Y |
Leave Plans (USA) |
Benefit Plan |
Add/Update |
TLX00010 |
Y |
|
Plan Type |
Update |
TLX00010 |
Y |
|
Coverage Election |
UpdateECD |
TLX00010 |
Y |
*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.
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.
The following table lists the pages and conditions when the system will not allow the inactivation or deletion of effective-dated rows.
Page |
Condition |
TCD Group |
You cannot inactivate a TCD group that is associated with a time reporter. You cannot delete an effective-dated row if the deletion invalidates the association to a time reporter. |
TCD Setup |
You cannot inactivate a TCD if the TCD is attached to a TCD group. You cannot delete an effective-dated row if it invalidates the association to a TCD group. |
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.
Page |
Prompts on Page |
TCD Setup |
TCD Type TRC Program Taskgroup |
You cannot change the effective date of the prompt table to be greater than the effective date of the target table.
Prompt Table |
Target Table |
TCD Type |
TCD Setup |
TCD Setup |
TCD Group |
TCD Restriction Profile |
TCD Group |
TCD Group |
Maintain TR Data |