Kintana Script : Locate Step(s) Sending a Specified Notification

Purpose

The converse of figuring out why user's aren't getting email notifications is figuring out why they are getting unexpected or unwanted notifications.

This is a lot less common but it can happen if you set the event conditions that trigger notifications incorrectly.

In that case you'll find this script will save you a lot of time clicking open steps and inspecting their notifications.

Tested

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

Usage

Paste the following script into SQL Runner

Change the workflow name literal to the workflow name you want to list.

Change the subject match string to a distinctive sub-string in the message

Omit the comment unless you're using a PL-SQL editor.
SQL Runner only supports select statements and you'll get a KNTA-10648 error.

-- Locate Step(s) Sending a Specified Notification
-- Workflow step notification emails are triggered
-- by a change in the workflow step and status.
-- For Create and Deliver notification types
-- the KNTA_NOTIFICATION table:
--   Condition_value field maps to the workflow_step.
--   Event_value field maps to the status.

select ww.workflow_name,
       wws.sort_order "Step No", wws.step_name,
       nn.event_value, nn.subject,
       dbms_lob.substr(nn.parent_text, 4000, 1) "Message Text"

from KWFL_WORKFLOWS ww,
     KWFL_WORKFLOW_STEPS wws,
     KNTA_NOTIFICATIONS nn

where wws.workflow_id = ww.workflow_id
  and wws.workflow_step_id = nn.condition_value  /* See Note above */

-- Replace with your workflow name

  and ww.workflow_name = 'Sales Forecast'

-- Replace with your subject search string

   and nn.subject like '%simulation%'

order by wws.sort_order

WORKFLOW_NAME  Step No  STEP_NAME       EVENT_VALUE  SUBJECT           Message Text
-------------  -------  ---------       -----------  ---------         -----------------------
Sales Forecast      23  Review forecast ELIGIBLE     Request           Please login.
                                                     #[REQ.REQUEST_ID] Verify forecast. Enter
                                                     start simulation  non-std simulation seed
Sales Forecast      28  Monte Carlo sim ELIGIBLE     Request
                                                     #[REQ.REQUEST_ID] Please login and set
                                                     needs simulation  simulation spans.
                                                     seed values

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)