Rule and messages: Example 2

In the following example, the rule prevents users with either of two particular time sheet policies from submitting a time sheet with a request that has been cancelled or closed for more than 30 days. This rule uses the [TMG.TIME_SHEET_ID]token and it restricts which requests users can enter on time sheets.

The restrictTo attribute is used to apply the rule to time sheet policies 20000 and 20001 only. To find the time sheet policy ID number associated with a policy name, see the KTMG_POLICIEStable in the database.

rule>
   <identifier>requests_closed</identifier>
   <title>req.closed.title</title>
   <enabled>true</enabled>
<!-- Restrict this rule to resources associated with particular PPM Time Management policies -->
   <restrictTo>
      <policies>
        <policy id="20000"/>
        <policy id="20001"/></policies>
    </restrictTo>
    <sql><![CDATA[
      SELECT '#' || kr.request_id
       FROM kcrt_requests kr
       WHERE (kr.status_code LIKE 'CLOSED%' OR kr.status_code LIKE 'CANCEL%')
         AND EXISTS (
            SELECT 1
              FROM tm_time_sheets ts,
                   tm_time_sheet_lines tsl,
                   ktmg_periods tp,
                   kwfl_workflow_instances kwi
              WHERE ts.time_sheet_id = [TMG.TIME_SHEET_ID]
               AND ts.time_sheet_id = tsl.time_sheet_id
               AND tsl.work_item_type = 'REQUEST'
               AND tsl.work_item_id = kr.request_id
               AND ts.period_id = tp.period_id
               AND kr.request_id = kwi.instance_source_id
               AND kr.workflow_id = kwi.workflow_id
               AND kwi.instance_source_type_code = 'IR'
               AND TRUNC (tp.end_date) - TRUNC (kwi.completion_date) > 30)
   ]]></sql>
   <violation>req.closed.violation</violation>
   <error>genericError</error>
</rule>

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

req.closed.title=Closed Requests

req.closed.violation=The following requests on this time sheet have been closed or cancelled for more than 30 days: {0} You are no longer permitted to submit additional effort on these requests.

genericError=An error occurred while checking this time sheet rule.

The violation message is written to include specific data returned by the SQL, namely one or more request IDs 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 (request IDs in this example).

Based on the SQL and the properties file, if a user submits a time sheet with effort on any requests that have been cancelled or closed for more than 30 days, a Non-Compliance message is displayed with the following title and text (including two example request numbers):

Closed Requests The following requests on this time sheet have been closed or cancelled for more than 30 days:
1 #12345
1 #23456
You are no longer permitted to submit additional effort on these requests.

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 same generic error message text as in example 1:

Closed Requests An error occurred while checking this time sheet rule.