Kintana Script : Workflow Step Email Notifications

Purpose

This is something of a swiss-army knife and should be adapted to your requirements.

Basically it replicates what you would see by opening a workflow step's 'Notifications' tab and then expanding each line.

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,
       nn.visible_event_value "Event",
       nn.description,
       nni.interval_name "Interval",
       nn.enabled_flag "Enabled",
       nnr.email_address,
       nnr.distribution_type_code,
       nnr.token "Recipient Token",
       nnr.recipient_type_code,
       -- nnr.notification_recipient_id,
       -- nnr.user_id
       nu.username "Recipient Username",
       nu.full_name "Recipient Fullname",
       --nnr.security_group_id "Recipient SG Id",
       nsg.security_group_name "Recipient SG Name",
       --nn.subject,
       --dbms_lob.substr(nn.parent_text, 4000, 1)  "Message Text"

from KWFL_WORKFLOWS ww,
     KWFL_WORKFLOW_STEPS wws,
     KNTA_NOTIFICATIONS nn,
     KNTA_NOTIFICATION_RECIPIENTS nnr,
     KNTA_NOTIFICATION_INTERVALS nni,
     KNTA_USERS nu,
     KNTA_SECURITY_GROUPS nsg

where wws.workflow_id = ww.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.enabled_flag='Y'
  and nnr.token is not NULL
  and nnr.user_id  = nu.user_id (+)
  and nnr.security_group_id = nsg.security_group_id (+)

-- Limit to Enabled Workflows

  and ww.enabled_flag = 'Y'

-- Limit to one workflow

-- and ww.workflow_name = 'My Workflow'

-- Limit By Distribution Type Codes : TO CC BCC

-- and nnr.distribution_type_code = 'TO'

-- Limit By Recipient Type Code : USER  USER_ID  SECURITY_GROUP  EMAIL_ADDRESS

--and nnr.recipient_type_code = 'USER'

-- Limit to one user

-- and nnr.recipient_type_code = 'USER'
-- and nu.last_name = 'Houdini'

-- Limit by email address

--and nnr.recipient_type_code = 'EMAIL_ADDRESS'
--and nnr.email_address like 'Harry.Houdini@%'

-- Locate notifications with a specific sub-string in the Subject

--and nn.subject like '%simulation%'

-- Locate notifications with a specific sub string in the message text

  and to_char( dbms_lob.substr(nn.parent_text, 4000, 1) ) like '%Notified Users:%'

order by ww.workflow_name, nnr.distribution_type_code, wws.sort_order

Download

KintanaScripts.zip (79 Kb)