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

------------------------------------------------------------------

select nntp.parent_key_id "RequestID",
       nntr.creation_date,
       nntp.notification_sent_flag,
       nntr.email_address,
       nntp.subject
from KNTA_NOTIF_TXN_RECIPIENTS nntr,
     KNTA_NOTIF_TXN_PARENTS nntp,
     KNTA_NOTIF_TXN_DETAILS nntd
where nntr.notif_txn_detail_id = nntd.notif_txn_detail_id
  and nntd.notif_txn_parent_id = nntp.notif_txn_parent_id
-- Limit by date range
-- and nntr.creation_date >= to_date('01-01-2007 01:00:00 AM', 'mm-dd-yyyy hh:mi:ss AM')
-- and nntr.creation_date <= to_date('12-31-2007 12:59:59 PM', 'mm-dd-yyyy hh:mi:ss AM')
-- Limit to one or more request Id's
-- and nntp.parent_key_id in ( 123456, 123457 )
-- Limit by distinctive sub-string in the email subject
--and nntp.subject like '% Approved for Release%'
and ( nntr.email_address like '%_EMAIL%')
order by nntr.creation_date desc
-- ,nntp.parent_key_id
-- ,nntp.subject

Download

KintanaScripts.zip (58 Kb)