Understanding Rules

Design and Organize Rule Steps

Determine whether you can use one of the delivered rule templates or if you must create either actions and conditions or SQL objects. Consider which rules or parts of rules to reuse. If you can use part of a rule repeatedly or in many ways, create an action or condition or a SQL object so that you can reuse that part of the rule.

Think about dependencies: what should be checked or processed first. If you want to act on a subset of data or the results of a query, define that data or subquery first. If you want to act only if a certain condition exists, create the statement to determine if the condition is true before inputting the action. If you want to perform multiple actions on a single row, think about processing order. For example, if you must compare daily reported time to daily scheduled hours, sum the daily reported time and scheduled time first. Although the comparison and summation could be a single step, keep each step relatively simple for maintenance and troubleshooting. Specify and order rule steps on the Define Rule Steps page.

Avoid broad or redundant querying or processing. For optimal performance, create conditions that retrieve the fewest possible records and still satisfy your data needs. For example, specifying certain column names in your Select statements is more efficient than selecting all columns. Use the logical operator IN instead of OR, since IN is processed faster than OR.

If you want to create a rule that concerns a certain period of time, define a valid rule period ID for that rule.

Use all the tools available to you. When possible, use PeopleSoft-delivered objects. Create value lists instead of multiple conditions. Several working tables are delivered within rules, but you can create your own working tables. They must be temporary tables.

Translate Business Rules into SQL Rules

Before you create rules in Time and Labor, have a clear idea of how you manage both time and time reporters, and have the business rules in front of you when you begin. Certain terminology in these documents has counterparts in particular SQL conditions and actions. Words such as update, replace, and substitute frequently imply the SQL action update. Words such as where, if, when, and in case of imply dependencies and conditions.

Use Working Tables within Rules

Before Time Administration can execute a rule, it moves the time reporter data needed to run the rule from scheduled and reported time into a data store known as Intermediate Payable Time. Rules are applied to information in the data store rather than to data in scheduled or reported time to protect the integrity of the source data. The Intermediate Payable Time data store comprises these five tables:

• TL_IPT1

• TL_IPT2

• TL_IPT3

• TL_IPT4

• TL_IPT5

In addition to the Intermediate Payable Time tables, you can access a number of other working tables in the system. Most of these working tables start with the prefix TL_WRK.

Rules for Using Working Tables

When you create rules in Time Administration, remember that:

• Time Administration automatically loads much of the data for executing rules into the Intermediate Payable Time table TL_IPT1.

  In the rules, you can move data back and forth between this table and the other Intermediate Payable Time tables. For example, the rule can move time data from TL_IPT1 to TL_IPT2, apply a specific TRC to this time, and then update the original row of data in TL_IPT1 with the new TRC-associated data.

• You must decide when and how to move data from TL_IPT1 to other working tables.

  After the rules process and modify the data in other tables, return any processed data to send to Payable Time back to TL_IPT1, which is the table containing the original, raw data. That is, push the data in its final form—the form in which to the data to Payable Time—back to TL_IPT1. If the data does not exist in TL_IPT1, it will not become part of Payable Time.

• You must always truncate (or clear) working tables before moving data into them.

  This ensures that the data that remains in those tables from previous rules processing does not interfere with or corrupt the new data. To truncate any working table, use the %TruncateTable command, which is included in the list of meta-SQL commands supported by PeopleSoft. However, never truncate or delete the data in TL_IPT1; it contains raw time data to execute the rules. If you delete this data, the rules have nothing to process.

• You can move data from external tables (nonworking tables) into working tables.

  If you do not plan to process or modify this data, but you must verify it (in a condition statement, for example), consider reading the external tables directly rather than moving large quantities of information into the working tables. Moving large quantities of data into TL_IPT1 can affect processing speed.

Example: Translating Business Rules into SQL Rules

This example illustrates how to translate a business rule into a SQL rule, and how the resulting SQL rule moves data from TL_IPT1 to other working tables in the system. Suppose that your business rule states: All hours worked in excess of 40 hours in any one week shall be paid at the rate of 1.5 times the employee's applicable hourly rate.

The first step is to rewrite the business rule in a simpler sentence. If possible, translate business concepts into Time and Labor terms. For example, you recognize that the rate of 1.5 times the normal rate implies a different (overtime) TRC. Rewrite this business rule as: Replace all hours in excess of 40 in a week with TRC OT 1.5.

Next, determine what type of action the system will take. Most business rules require an insert or update and may require subqueries to determine more precisely which rows to affect. This example requires the update action.

Next, consider what conditions exist in the statement. In our example, in excess of 40 hours and in one week are conditions.

To accomplish this rule, you need seven steps. Steps one through two are actions; steps three through seven are SQL objects:

1. Delete TL_IPT2 (truncate working table).

2. Delete TL_IPT3 (truncate working table).

3. Insert affected row (Friday, 10, 46, REG) into TL_IPT2 with hours over threshold and new TRC.

4. Insert rows after affected row (Saturday, 6, 52, REG) into TL_IPT3.

5. Calculate TL_QTY-TL_QTY in work record (TL_IPT2) and update row (Friday, 10, 46, REG becomes Friday, 4, 40, REG) in TL_IPT1.

6. Insert new row (Friday, 6, 46, OT1.5) into TL_IPT1 with new sequence number.

7. Update TL_IPT1 TRC with 'ot1.5' where exists in TL_IPT3.

The shaded rows in these tables represent Intermediate Payable Time created by these rule steps. Changes to the original TL_IPT1 data are in italics:

Data in IPT1

This table lists the data in IPT1:

Data in IPT2

This table lists the data in IPT2:

Data in IPT3

This table lists the data in IPT3:

Data in IPT1 After Processing

This table lists the data in IPT1 after processing:

Using Value Lists within Rules

When you want the condition or action clause of a rule to apply to multiple values—such as multiple TRC codes, multiple shifts, or multiple task groups—create value lists rather than defining multiple conditions.

For example, you have a rule that tells the system to create premium time when a time reporter reports more than 40 hours to time reporting codes Regular, Sick, Vacation, and Holiday. Before you create this rule, you set up a TRC value list and add the TRCs for Regular, Sick, Vacation, and Holiday. When Time Administration executes the rule, it will refer only to the four TRCs in your list. For another example, you want to restrict the punch types that can follow an In punch. You can create a value list that includes only those punch types you deem valid. You can then use Rule Template240 to create a rule that generates an exception when an invalid punch type follows an In punch. Value lists are used within template-built rules.

This table shows each type of list you can create and where the values are defined:

Rule Elements within Rules

To control how and when rules are applied to different groups of time reporters, use rule elements. You can define up to five rule elements to represent any type of data, and then use these elements within the conditions for your rules.

Rule elements are associated with time reporters by:

• Assigning rule elements directly to time reporters on the Create Time Reporter Data page or the Maintain Time Reporter Data page.

• Positively reporting rule elements when entering time.

Example: Defining and Using Rule Elements

You define a rule element called Jobs. You also create these values for Jobs: HOST, CASHIER, ASST MGR, WAITER. Next, you build a rule that creates a premium if a worker positively reports the value ASST MGR for rule element 1. As an alternative, you might build your rule so that the system creates a premium whenever time is reported by a person for whom the value ASST MGR is entered in the Rule Element 1 field on the Create Time Reporter Data page or the Maintain Time Reporter Data page.

Meta-SQL within Rules

Use meta-SQL as an alternative to creating complex SQL commands on your own. Meta-SQL enables you to perform many SQL functions by giving you access to complex SQL operations in a shorthand form. Meta-SQL functions expand to platform-specific SQL statements. Time and Labor uses meta-SQL to interface with different database platforms. A meta-SQL operand begins with a percent (%) sign and returns a scalar value. Almost all meta-SQL constructs that are visible in Time and Labor are PeopleTools meta-SQL constructs that are processed at run time.

Common HR Data in Rules

To use Human Resources or Time and Labor employee data within your rules, check whether the data exists in TL_PROF_WRK before creating multiple joins to tables in Human Resources. TL_PROF_WRK is a temporary table created at the beginning of the Time Administration batch run. It contains employee-related data retrieved from various HCM and Time and Labor source tables, effective as of the current run. Instead of accessing the various source tables to create a rule, you can create a single join to this table to retrieve information relating to all the time reporters you must process.

TL_PROF_WRK contains fields derived from these source tables:

Prioritizing Rules in Rule Programs

Consider rule priority when dependencies exist among the rules in a rule program. For example, you've defined these rules for the organization:

• Default TRC: Set any blank TRC to REG.

• Daily 8: Pay overtime (OT1.5) for hours worked in excess of 8.0 per day.

• Daily 12: Pay double time (OT2.0) for hours in excess of 12 per day.

• Weekly 40: Pay overtime (OT1.5) for hours worked in excess of 40 per week.

In this example, the second, third, and fourth rules evaluate time with a TRC of REG. In addition, they are replace-type rules, for which REG is reduced to the threshold number and a new TRC is created for the excess hours. Based on this information, order your rule program as follows:

1. Default TRC

   Other rules must evaluate TRC REG.

2. Daily 12

   This must execute before the Daily 8 rule because REG is set to 12 if hours are over 12. If the Daily 8 rule runs first, the Daily 12 rule does not generate double time because all records with hours over 12 are set to 8.

3. Daily 8

   Daily 12 reduces hours in excess of 12 to 12.

4. Weekly 40

   Executed last because it needs to capture all the REG hours (including REG hours that are reduced by daily rules and REG hours that are not altered by daily rules).

Time Period Selection for A Rule

When creating rules, carefully analyze the time period to use. For rules that apply to punch time reporters, evaluate your daybreaker and the relationship between the out punch of one day and the in punch of the next day. In many cases, you may need to apply a time period greater than a day for the proper evaluation of a daily rule.

Rules Testing

Always test any rule you create using an interactive SQL tool. Otherwise, the system could generate incorrect results when it processes the rules.