Creating Rule Templates

This chapter provides an overview of the rule template creation process, lists prerequisites and discusses how to create rule templates.

Click to jump to parent topicUnderstanding Rule Template Creation

Time Administration in the Time and Labor application is a rules engine that executes the rules you specify to process reported and scheduled time. The system enables you to build custom, or non-template-based rules using SQL. However, many users prefer to create rules using templates that contain the basic logic, underlying structures and necessary SQL objects for a rule. Templates enable users that are not experts in SQL to create a variety of rules for the Time Administration program to execute when processing reported and scheduled time.

Rule templates define the set of SQL statements that are required to enact a particular type of rule. The template is very similar to a rule, except that the SQL objects contain pre-defined variables where values can be inserted. The template also defines and controls the user interface where users who may not have extensive rule-building skills can input the functional criteria that make up the rule. The template continues to list the rule steps and the SQL objects upon which the parameters entered by the user are applied to generate the executable code specific to each rule. A template also defines how the information, or parameters, from the user interface fit into the template’s explanation and the template’s executable steps, or SQL objects.

SQL objects that are subject to customization have placeholders, specifically %RuleTemplate(), that are replaced with information specific to the rule in order to create the executable Application Engine (AE) section and its SQL steps.

Click to jump to top of pageClick to jump to parent topicExample: Creating a New Rule Template

The following steps illustrate the creation of a new template that tracks a TRC and the number of days reported.

Creating Message Catalog Entries

Creating or selecting a message catalog entry as a template for the rule explanation is a prerequisite for creating a Rule Template. For this template, two entries are added to the Time and Labor Time Admin message set using the Message Catalog page. The messages in this example are numbered 5000 and 5001 to place the messages into the customer range of messages.

  1. Create a message for the rule description

  2. Create a second message for the exception that rules using this template can generate.

    In addition to the message for the generated exception, you also need to create the Time and Labor exception on the Exception Definition page.

Creating a New Presentation Page

Creating or selecting a page used to present the detail parameters that are specific to a rule from the Template-Built Rules component is a prerequisite for creating a new rule template. For this example, create a new page called TL_CUST_600_PNL using the Application Designer to present the detail parameters that are specific to the new rules created by the template.

Creating New SQL Objects

Another prerequisite for most rule templates is an SQL object or an action. For this example, create two new SQL objects: CUST600_S01 and CUST600_S02, using the Copy SQL Objects process. In this case, you make the modifications to the final version of the SQLs directly in the Rule Template definition. So it does not matter which SQL object you use as source for creating your copies.

Creating a New Rule Template

Using the Template Definition page, create a new template called CUST_600. When completing the information in the fields, in the Period Filter field, indicate that the period filter is not applicable by selecting TL_PERIOD_NA_VW. Also be sure to indicate the TL_CUST600_PNL detail page that you created earlier.

Then, on the Template Steps page, enter the SQL objects you created earlier, as well as the TMPL000_S01E SQL object.

Click the Edit SQL button for each SQL object and enter your planned syntax for each object.

Once you have defined the syntax for each object, specify the template step parameters for each SQL object by clicking the Parameters link for each object.

For Rule Step 1, the parameter you need to fill is the Exception type, field EXCEPTION_ID or record DERIVED_TL_TMPL.

Note. If your template calls for fields that are not present in the application, you should create a new derived record to contain those fields.

Access the Template Step Parameters page for Rule Step 1:

For Rule Step 2, both of the fields are list values. Do not specify a field name, but use the List Information tab to provide the list name.

Fill the parameters in order, according to the SQL presented. In this example, the first parameter is a TRC, so the first TRC listed is the DERIVED_TL_TMPL field TRC, which is delivered on second page of the rule. The second field is a PUNCH_TYPE list, so the parameter is TL_TMPLT_RULE, type "Punch List".

Access the Template Step Parameters page for Rule Step 2:

For Rule Step 3, the DERIVED_TL_TMPL field name and field format are completed as you did for Rule Step 1. The TL_TMPLT_RULE record uses the DAYNUM field, which has a Numeric field format. Access the Template Step Parameters page for Rule Step 3:

Note. You will not be able to save the template until you fill out the complete number of parameters for each step.

Creating a Template Built Rule Using the Sample Template

After you successfully save the CUST_600 rule template, you can create new template-built rules using the Template Header page:

The Tl_CUST600_PNL appears as an additional tabbed page in the Template-Built Rules component:

Based on the configuration specified in the sample pages shown above, the content of the newly-created rule is:

--- AE STEP: 1 --- SQL Object ID: TMPL000_S01E --- %Select(TL_EXC_RULE_AET.DESCR) SELECT A.DESCR FROM PS_TL_EXCEPT_DEFN A WHERE A.EXCEPTION_ID = 'CUST5001' AND A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_TL_EXCEPT_DEFN A1 WHERE A1.EXCEPTION_ID = A.EXCEPTION_ID AND A1.EFFDT <= %Bind(TL_TA_MAIN_AET.TL_SYSTEM_DT)) ----------------------------------------------------------------------------- --- AE STEP: 2 --- SQL Object ID: CUST600_S01 --- %InsertSelect(TL_WRK01 ,TL_IPT1 A , PROCESS_INSTANCE = %PROCESSINSTANCE, SEQ_NBR = MAX(A.SEQ_NBR)) FROM %TABLE(TL_IPT1) A WHERE A.PROCESS_INSTANCE = %PROCESSINSTANCE AND A.TRC IN ('K0MCK', 'KUSIK', 'MAMSK', 'SCK') AND A.PUNCH_TYPE IN ('0', '1', '4', '5') GROUP BY A.PROCESS_INSTANCE, A.EMPLID, A.DUR ------------------------------------------------------------------------------ --- AE STEP: 3 --- SQL Object ID: CUST600_S02 --- %InsertSelect(TL_EXCEPT_WRK ,TL_WRK01 A , ACTION_DTTM = %CurrentDateTimeIn , VALID_INST_ID = 0 , ALLOW_IND = 'Y' , DUR = MIN(A.DUR) , EMPL_RCD = 0 , END_DT = MAX(A.DUR) , EXCEPTION_ID = 'CUST5001' , EXCEPTION_SOURCE = 'TA' , EXCEPTION_STATUS = 'U' , MSG_DATA1 = %NUMTOCHAR(A.TL_QUANTITY) , SEQ_NBR = MIN(A.SEQ_NBR) , START_DT = MAX(A.DUR) , UPDATE_FLAG ='N') FROM %TABLE(TL_WRK01) A WHERE A.PROCESS_INSTANCE = %PROCESSINSTANCE GROUP BY A.PROCESS_INSTANCE, A.EMPLID HAVING 3 >= (SELECT COUNT(*) FROM %TABLE(TL_WRK01) B WHERE B.PROCESS_INSTANCE = A.PROCESS_INSTANCE AND B.EMPLID = A.EMPLID)

Note. Notice the value entered for the EXCEPTION_ID in steps one and three, and the values for the TRC IN in step two.

Click to jump to parent topicPrerequisites for Creating Rule Templates

Before you can create a rule template, you must create or select the following rule template components:

Creating or Selecting Rule Objects

Most working steps in a template require a SQL object or an action. For any SQL object you create, designate a name, several descriptions, and the type of object you are building.

Template-serving objects are typically named for the template they support, for example TMPL030_U10.

See Creating SQL Objects.

Creating or Selecting a Presentation Page

This is the page used to present the detail parameters that are specific to a rule from the Template-Built Rules component. Oracle delivered templates use a number of different pages on the TL_TA_RULE_TMPLT component that may cater to the parameters required for your new template. However, rather than adding pages to the Oracle delivered and maintained component, you may prefer to use a generic user interface. Oracle provides the TL_TEMPLATE_GEN and TL_TEMPLATE_GRID, pages for those users that may not want to modify the delivered component object. Selecting either TL_TEMPLATE_GEN or TL_TEMPLATE_GRID as a presentation page means that you do not need to create a new page.

See PeopleTools 8.52: PeopleSoft Application Designer Developers Guide PeopleBook, Creating Page Definitions

Creating or Selecting an Explanation Message

This is the text that is displayed as the explanation for the rule, and is updated and stored along with each rule created based on this template. Select or create the message catalog entry as a template for the rule explanation. The message catalog entries for Oracle delivered templates all reside in the 13507 message set.

Note. The messages typically contain placeholders where rule-specific parameters are entered for each new rule. Each placeholder starts with a placeholder number, (n), and contains an uninterrupted text string that is intended to guide the user in creating the new rule. A space, period, comma, or semi-colon are all recognized as characters that define the end of the placeholder text.

See PeopleTools 8.52: System and Server Administration PeopleBook, Using PeopleTools Utilities

Creating or Selecting a Derived Record

Oracle delivered templates use the record DERIVED_TL_TMPL, which contains fields that are suited for most templates. You should create your own record and place all parameters linked to a given template on the same record.

Click to jump to parent topicCreating Rule Templates

Time and Labor provides a component for creating and maintaining template definitions. The component enables validation of the data entered and provides full isolation of any template-specific PeopleCode from PeopleSoft delivered updates.

Click to jump to top of pageClick to jump to parent topicPages Used to Create Rule Templates

Page Name

Definition Name

Navigation

Usage

Template Definition

TL_TMPLT_DEFN

Set Up HRMS, System Administration, Utilities, Build Time and Labor Rules, Rule Template Definition, Template Definition

Create and review the contents of rule templates.

Template Steps

TL_TMPLT_STEPS

Set Up HRMS, System Administration, Utilities, Build Time and Labor Rules, Rule Template Definition, Template Steps

Create the template rule steps, which define the logic of any rules created by a template.

Rules Built From Template

TL_TMPLTRULE_REF

Click the List of Rules link on the Template Definition page.

Lists the rules that are created from the template based on a view against the TL_TMPLT_RULE table.

Template Text Parameters

TL_TMPLT_TEXT_SP

Click the Text Parameters link on the Template Definition page.

Specify the parameters for each placeholder in the pre-defined message that the system generates for rules when you specify a rule explanation message.

Template Step Parameters

TL_TMPLT_PARM_SP

Click the Parameters link in the Text Parameters column on the Template Steps page.

Lists the name of the record holding the parameter in the rule component interface and displays the template SQL defined for the step with the %RuleTemplate() place-holders where the parameters fit.

Click to jump to top of pageClick to jump to parent topicDefining a New Rule Template

Access the Template Definition page (Set Up HRMS, System Administration, Utilities, Build Time and Labor Rules, Rule Template Definition, Template Definition).

List of Rules

Click to access the Rules Built From Template page in order to view a list of the rules that have been created from this template.

Template Type

Select an option to filter the search results returned when the user accesses the template built rules component.

Core Component

Select to mark this template as a core component. The Time and Labor application makes any core components read-only when they are saved.

Note. To understand the effects of different time selections and period filters, review the existing templates and rules and observe how the Template Header page behaves for templates that use the different settings.

Header Page Controls

Time Selection

Select the punch types available when creating a rule, and whether a selection is required. The values are:

  • Controlled by Code

  • In Punch Only

  • Not Applicable (Disable)

  • Out Punch Only

  • Require Punch, Except Out

  • Required, No Elapsed

  • Required, No Out Punch

  • Required, No Restriction

Select Controlled By Code when the list of enabled, defaulted, and required punch types does not fit within any of the other field values. When you select this option you must provide explicit logic in an application class to display and validate the appropriate combination of selectors.

Profile Selection

Select the Task Profile Assignment options for any rules created by this template. This selection only applies to rules that add new Intermediate Payable Time (IPT) and needs to fill in the task information on the new row and has no impact on rules that generate exceptions or alter content on existing rows.

Select Not Applicable for Template to disable the Task Profile Assignment during the creation of rules.

The values are: Enable Selection, Not Applicable for Template, and Use Previous Row Tasks.

Period Filter

Enter the calendar table name to specify the calendars that the user can select in the Period ID field on the Template Header page. For example, you generally would not want to enable a user to enter a weekly calendar for a daily threshold rule. For templates where the calendar is not applicable, enter the TL_PERIOD_NA_VW table.

Detail Page Controls

Detail Page

Specify the page to use for entering and displaying parameters. Two generic pages, TL_TEMPLATE_GEN and TL_TEMPLATE_GRID, support custom template development and provide more options if you do not want to modify the delivered component object.

Message Set (Optional)

Enter the Message Set Number for the translatable text that the system uses to generate explanations for rules created by this template. Use with the Message Numbers field.

List Type (Optional)

Select the list of values, if any, from which the user selects. Templates normally use only one List field.

Message Number (Optional)

Enter the Message Number for the translatable text that the system uses to generate explanations for rules created by this template. Use the Template Text Parameters page to specify the parameters for each placeholder in the pre-defined message. Use with the Message Set field.

Text Parameters

Click to access the Template Text Parameters page. Use the Template Text Parameters page to specify the parameters for each placeholder in the pre-defined message specified by the Message Set and Message Number fields

Action

Specify whether the default setting for rules created by this template is to replace the incoming reported time or add an extra IPT entry. The values are: Create as Replacement, Create in Addition, and Not Applicable.

Template Controlling Code

The fields in this section enable you to create separate application classes that isolate your custom code from the delivered code. This means that future Time and Labor upgrades will not overwrite your custom templates.

Use Dedicated App Class (Use Dedicated Application Class)

Select to enable you to enter values in the Root PAckage ID and Package Name fields.

Root Package ID

Enter the name of the application package for this template. All delivered templates use the TL_RULETEMPLATES Root Package ID.

Package Name

For custom packages the Package Name is optional and the application class can be placed under the root package. The package name must be identical to the template ID. All delivered templates use the TL_RULETEMPLATES Root Package ID with a default Package Name of PSFT.

See Also

PeopleTools 8.52: PeopleCode Developer's Guide PeopleBook, Creating Application Packages and Classes

Click to jump to top of pageClick to jump to parent topicDefining Template Rule Steps

Define template rule steps in the following manner:

  1. Number the steps in execution order.

  2. Indicate the Action type.

  3. Indicate the Statement Type and the related object. Depending upon the selected Action type, you need to indicate at least one of the following:

  4. Review and edit the SQL object or action using the Edit SQL button for each rule step.

    Note. The interface automatically detects the number of parameters required by the rule object and updates the template step.

  5. Specify the parameter mapping for each statement that requires parameters using the link in the Parameters column.

Access the Template Steps page ( Set Up HRMS, System Administration, Utilities, Build Time and Labor Rules, Rule Template Definition, Template Steps).

AE Action

 

Select the type of step based on basic Application Engine technology. If you select Do Select, Do When, or Do While you must also add a SQL statement in the same rule step. SQL-Standard and SQL-Quit If No Rows are both single action steps. Selecting SQL-Quit If No Rows produces a SQL step with attributes that skip the remaining steps in the rule section unless this statement produced or returned any rows.

The values are: SQL-Standard, Do Select, Do When, Do While, and SQL-Quit If No Rows

Statement Type

Select the type of SQL statement. You must also indicate the table to update or truncate, or the SQL object or action. A typical rule truncates, or deletes the content of its work tables, and then defines the actual rule logic by copying relevant information into its work tables. There may be intermediate steps that update the statistics on these tables for better performance.

The values are: Action, SQL Object, Truncate Work Table, Update Statistics.

Record (Table) Name and SQL Object ID

Depending upon the selected Statement Type value, you must indicate either a table or SQL object. You must indicate a table to update or truncate when you select Truncate Work Table or Update Statistics in the Statement Type field. You must indicate an SQL object or action when you select Action or SQL Object in the Statement Type field.

Edit SQL

Click to access the SQL Object page to edit the SQL for the specified rule step.

Parameters

Click to access the Template Step Parameters page.

Click to jump to top of pageClick to jump to parent topicIdentifying Template Text Parameters

When you specify a rule explanation message, you must indicate the correct parameter for each placeholder in the template message. Use the Template Text Parameters page to indicate the parameters for each placeholder.

Access the Template Text Parameters page (Click the Text Parameters link on the Template Definition page).

Sequence Number

The system automatically assigns the value of this field, and links the specified parameters in each row with each placeholder in the template message. In the Description field, each placeholder is numbered, based on this sequence number.

Record Name and Field Name

Enter the source of the parameter for each placeholder in the template message

List Type

Select the type of list. Use this field when the specified field is not a single value item, but a List ID for multiple values that must be expanded from the value list definition.

Description

Displays the message text from the selected Message Catalog entry. Use the text in this field as a guide for defining the necessary parameters on this page.

Note. Enter a single TRC using the TRC field, or a list of TRCs using the LIST_ID field. When entering a list of TRCs, enter TRC in the Field Name and the List Type for that parameter.

Click to jump to top of pageClick to jump to parent topicDefine Template Step Parameters

The Template Step Parameters page enables you to map parameters within a step while viewing the template SQL. Access the Template Step Parameters page (click the Parameters link in the Text Parameters column on the Template Steps page).

Required Field

Select to indicate that the parameter must have a value. Deselect this field to allow a zero numeric value or an empty character field for this parameter.

Field Format

Select the format of the field, based on the definition of the selected field. The selected value in this field deals with the formatting of the parameter into the SQL statement.

Select Character without Quotes to allow a character value that does not have quote wrappers. For example, in the statement “ ...AND TRC = ‘KUREG’…” KUREG has quote wrappers that identify the character value for the time reporter code. However, when you have logical or mathematical operators, such as >, or <, you do not want the character value surrounded by quote wrappers.

The values are: Character, Character without Quotes, Date, Date and Time, Numeric, Signed Numeric, and Time.