Kintana Script : Unresolved Email Recipient Tokens

Purpose

Emails will not be sent out and will pile up in the KNTA_NOTIFICATION_RECIPIENTS table if the recipient's email address tokens are not resolved properly.

The symptom of this are entries that look like 'ASSIGNED_TO_EMAIL' instead of a resolved value such as 'harry@houdini.com'

Tested

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

Usage

Paste the script into Toad or SQL Runner

For Demand and Deliver email notifications
these field associations exist between these tables:
KNTA_NOTIFICATIONS         KWFL_WORKFLOW_STEPS
condition_value       -->  workflow__step_id
event_value           -->  status
  

select ww.workflow_name,
       wws.sort_order "Step#", wws.step_name,
       nn.visible_event_value, nn.description,
       nnr.email_address, nnr.token, nnr.recipient_type_code
from KWFL_WORKFLOWS ww,
     KWFL_WORKFLOW_STEPS wws,
     KNTA_NOTIFICATIONS nn,
     KNTA_NOTIFICATION_RECIPIENTS nnr
where ww.workflow_id = wws.workflow_id
  and nn.condition_value = wws.workflow_step_id
  and nn.notification_id = nnr.notification_id
  and ww.enabled_flag = 'Y'
  and nn.enabled_flag = 'Y'
  and wws.enabled_flag = 'Y'
  and ( nnr.token like '%_EMAIL%' or
        nnr.email_address like '%_EMAIL%'
      )
  order by nnr.email_address, ww.workflow_name,
           wws.sort_order, nnr.token, ww.workflow_name asc

WORKFLOW_NAME     Step#  STEP_NAME           VISIBLE_EVENT_VALUE  DESCRIPTION      EMAIL_ADDRESS TOKEN                  RECIPIENT_TYPE_CODE
Inventory Restock     3  Pending SKU Coding  Need More Info                                      [REQ.CONTACT_EMAIL]    EMAIL_ADDRESS
Inventory Restock     4  Bin Assignment      On Hold              Advise Requestor               [REQ.CREATED_BY_EMAIL] EMAIL_ADDRESS
Inventory Restock    16  Repair or Restock   On Hold                                             [REQ.P.CONTACT_EMAIL]  EMAIL_ADDRESS
...

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 (64 Kb)