Creating Rule Templates

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

Understanding 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.

Example: 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.

Prerequisites for Creating Rule Templates

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

• Rule Objects.

• A presentation page.

• An explanation message.

• A derived record, or parameter holder.

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.

Creating 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.

Pages Used to Create Rule Templates

Defining 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

Detail Page Controls

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.

See Also

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

Defining 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:

   • The table to update or truncate.

   • The SQL object or action to run.

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.

Identifying 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.

Define 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.