Kintana Script : List Workflow Steps eMail Notification Messages

Purpose

A simple script to list the email messages defined on workflow steps.

Tested

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

Usage

Paste the script into Toad or SQL Runner

The KNTA_NOTIFICATIONS table condition_value field maps to
the KWFL_WORKFLOW_STEPS workflow_step_id field for requests and packages.

select ww.workflow_name,
       wws.sort_order "Step#",
       wws.step_name,
       wws.step_type_code,
       nn.visible_event_value "Event",
       nn.description,
       nni.interval_name "Interval",
       nnr.recipient_type_code,
       nn.notification_type_id,
       nnt.notification_type_name,
       nntm.subject,
       nntm.message_name

from KWFL_WORKFLOWS ww,
     KWFL_WORKFLOW_STEPS wws,
     KNTA_notifications nn,
     KNTA_NOTIFICATION_TYPES nnt,
     KNTA_NOTIFICATION_RECIPIENTS nnr,
     KNTA_NOTIFICATION_INTERVALS nni,
     KNTA_NOTIF_TYPE_MESSAGES nntm,
     KNTA_SECURITY_GROUPS nsg

where ww.workflow_id = wws.workflow_id
  and wws.workflow_step_id = nn.condition_value
  and nn.notification_id = nnr.notification_id
  and nn.interval_id = nni.interval_id
  and nn.notif_type_message_id = nntm.notif_type_message_id
  and nn.notification_type_id = nnt.notification_type_id

  -- Limit to Enabled Workflows

  and ww.enabled_flag = 'Y'

  -- Limit to one workflow

  and ww.workflow_name = 'myWorkflow'

  -- Locate notifications with a specific description

  and nn.description like '%Manager Review Notice%'

  -- Limit notifications to a specific type

  and nn.notification_type_id = '2'

  order by ww.workflow_name, wws.step_type_code, wws.sort_order

Download

KintanaScripts.zip (79 Kb)