Kintana Script : List Rules for a Request Type

Purpose

This script provides the same information you see on the rules tab and can also pull out the SQL for SQL-based rules.

Note that the SQL may contain carriage returns. The replace of line feeds with spaces prevents problems if you chose to export the result file from Toad as delimited text for import into an Excel file.

Tested

With HP PPM (Project and Portfolio Management) Kintana v8.0.

Usage

Paste the script into SQL Runner

Change the 'and crt.request_type_name' request type name or comment it out.

Omit the comment, spool, and linesize statements unless you're using a PL-SQL editor.
SQL Runner only supports select statements and you'll get a KNTA-10648 error.
If using PL-SQL change 'yyyymmdd' in the output file name to today's date.

-- List Rules for a Request Type
spool c:\request_field_info_yyyymmdd.txt
set linesize 1000

select crt.request_type_name "Request Type Name",
       npr.seq_number  "Rule#",
       npr.enabled_flag "Enabled",
       npr.param_rule_name "Rule Name",
       npr.rule_event_code "Rule Event",
       npr.cascade_rules,
       npr.rule_type_code,
       -- Replace line feeds in sql with spaces
       replace(npr.rule_sql, chr(10),' ')  "Rule SQL"
from KNTA_PARAM_RULES npr,
     KNTA_PARAMETER_SET_CONTEXTS npsc,
     KCRT_REQUEST_TYPES crt
where

    to_char(crt.request_type_id) = npsc.context_value
and npsc.parameter_set_context_id = npr.parameter_set_context_id

  -- limit to a single request type. Comment out to list all request types

    and crt.request_type_name = 'My Request Type Name'

  -- limit to rules using SQL. Comment out to list all rules

    and (npr.rule_sql like '%Select%' or  npr.rule_sql like '%select%' or  npr.rule_sql like '%SELECT%' )

  -- limit to SQL rules containing a specific token

    and npr.rule_sql like '%[MY.TOKEN]%'

order by crt.request_type_name, npr.seq_number

spool off

Request Type Name Seq   Rule Name           Rule Event   Enabled    CASCADE_RULES  RULE_TYPE_CODE  RULE_SQL
Inventory         1     Assign Workflow     ON_CREATION  Y          Y              SIMPLE          Select ...
Inventory         2     Set Security Group  ON_CREATION  Y          Y              SIMPLE
...

To extract the output, click on the [Open as Text] button.

This opens a text window from which you can copy and paste.

Download

KintanaScripts.zip (79 Kb)