Rule and messages: Example 3

In the following example, the rule prevents any resource from submitting time sheets with effort on tasks from any project that starts with the word "Operation" but the rule allows their managers to submit such time sheets. This rule uses both the [TMG.TIME_SHEET_ID] token and the [TMG.CURRENT_USER_ID] token and it restricts which tasks certain users can have on time sheets.

<rule>
  <identifier>tasks_restricted</identifier>
  <title>tasks.restricted.title</title>
  <enabled>true</enabled>
  <sql><![CDATA[
   SELECT ti.name
     FROM wp_task_info ti, wp_tasks t
    WHERE ti.task_info_id = t.task_info_id
     AND EXISTS (
      SELECT 1
       FROM tm_time_sheets ts,
            knta_users ku,
            tm_time_sheet_lines tsl,
            pm_projects prj,
            pm_work_plans wp
      WHERE ts.time_sheet_id = [TMG.TIME_SHEET_ID]
        AND ts.resource_id = ku.user_id
        AND ku.manager_user_id <> [TMG.CURRENT_USER_ID]
        AND ts.time_sheet_id = tsl.time_sheet_id
        AND tsl.work_item_type = 'TASK'
        AND tsl.work_item_id = t.task_id
        AND t.work_plan_id = wp.work_plan_id
        AND wp.project_id = prj.project_id
        AND prj.project_name LIKE 'Operation%')
      ORDER BY ti.name
  ]]></sql>
  <violation>tasks.restricted.violation</violation>  
  <error>tasks.restricted.error</error>
</rule>

Assume this rule uses the following key-value pairs in the properties file:

tasks.restricted.title=Restricted Tasks

tasks.restricted.violation=You are not permitted to submit effort on the following tasks: {0}.

tasks.restricted.error=An error occurred while checking this time sheet rule. Please contact your PPM Center administrator.

The violation message is written to include specific data returned by the SQL, namely one or more tasks in place of {0} in the violation message text. Note that the outermost SELECT statement in the SQL is written such that the returned strings shown in the violation message clearly identify the particular items of interest (task names in this example).

The runtime error message is unique, not generic.

Based on the SQL and the properties file, if a user submits a time sheet with effort on tasks from any project that starts with the word "Operation," a Non-Compliance message is displayed with the following title and text (including two example task names):

Restricted Tasks You are not permitted to submit effort on the following tasks:
1 Analysis
1 Design6

Based on the SQL and the properties file, if a runtime error occurs during execution of the SQL, the error message that appears consists of the message title specified in the properties file, followed by the unique error message text for this rule:

Restricted Tasks An error occurred while checking this time sheet rule. Please contact your PPM Center administrator.

As with any rule, the SQL could be changed for variations of interest. For example, you could display the project names as well as the subordinate task names, or you could allow delegates as well as managers to log time against projects that begin with "Operation."