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.

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

    This example illustrates the fields and controls on the Message Catalog page showing the information used for the Cust_600 Rule Template. You can find definitions for the fields and controls later on this page.

     Message Catalog page showing the information used for the Cust_600 Rule Template

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

    This example illustrates the fields and controls on the Message Catalog page showing the information for the tracked TRC exception. You can find definitions for the fields and controls later on this page.

     Message Catalog page showing the information for the tracked TRC exception

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

    This example illustrates the fields and controls on the Exception Definition page, illustrating the set up for the exception generated by the example temple. You can find definitions for the fields and controls later on this page.

     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.

This example illustrates the fields and controls on the Partial image of the Application Designer screen showing information for the TL_CUST600_PNL page. You can find definitions for the fields and controls later on this page.

 sm_TemplateAppDesignerPage

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.

This example illustrates the fields and controls on the Copy SQL Objects page showing how to copy an existing SQL object to the CUSTOMER600_S01 SQL object. You can find definitions for the fields and controls later on this page.

 sm_TemplateCopySQLObjPage

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.

This example illustrates the fields and controls on the Template Definition page showing the information entered for this example template. You can find definitions for the fields and controls later on this page.

 Create Template Definition Page

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

This example illustrates the fields and controls on the Template steps page showing the three SQL objects used in this example. You can find definitions for the fields and controls later on this page.

 sm_CreateTemplateDefnPage002

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:

This example illustrates the fields and controls on the Template Step Parameters page showing the information for Rule Step 1 of the CUST_600 template. You can find definitions for the fields and controls later on this page.

 sm_CreateTemplateStepParaPage001

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:

This example illustrates the fields and controls on the Template Step Parameters page showing the information for Rule Step 2 of the CUST_600 template. You can find definitions for the fields and controls later on this page.

 sm_CreateTemplateStepParaPage002a

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:

This example illustrates the fields and controls on the Template Step Parameters page showing the information for Rule Step 3 of the CUST_600 template. You can find definitions for the fields and controls later on this page.

 sm_CreateTemplateStepParaPage002c

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:

This example illustrates the fields and controls on the Template Header page showing information based on the CUST_600 Template. You can find definitions for the fields and controls later on this page.

 sm_CreateTemplateBuiltRulesPage001

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

This example illustrates the fields and controls on the TL_CUST600_PNL page as created for the CUST_600 Rule Template. You can find definitions for the fields and controls later on this page.

 sm_CreateTemplateBuiltRulesPage002

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.