Kintana Script : Locate Workflow Steps with Individual User Name Notifictions

Purpose

Naming users explicitly on workflow step notification tabs is not good practice.

It's better to use WSS: security groups.

This script will locate workflow steps with explicit user name notifications.

Tested

With HP PPM (Project and Portfolio Management) Kintana v6.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 No", wws.step_name,
       nnr.recipient_type_code,
       nnr.user_id,
       nu.first_name || ' ' || nu.last_name,
       nnr.security_group_id,
       nnr.email_address,
       nnr.token,
       nnr.notification_recipient_id
from KWFL_WORKFLOWS ww,
     KWFL_WORKFLOW_STEPS wws,
     KNTA_NOTIFICATIONS nn,
     KNTA_NOTIFICATION_RECIPIENTS nnr,
     KNTA_USERS nu
where  wws.workflow_id = ww.workflow_id
   and wws.workflow_step_id = nn.condition_value
   and nn.notification_id = nnr.notification_id
   -- Limit to one workflow
   -- and ww.workflow_name = 'My Workflow'
   -- Codes : USER, USER_ID, SECURITY_GROUP, EMAIL_ADDRESS
   and nnr.recipient_type_code = 'USER'
   and nu.user_id = nnr.user_id
   --   and ( nnr.user_id != 1 OR nnr.email_address like '%@%' )

Download

KintanaScripts.zip (64 Kb)