Kintana Script : Email Pending for DR, Subject, User eMail

Purpose

If a user complains that s(he) is not getting emails, this query will let you locate pending emails in the KNTA_NOTIFICATION_RECIPIENTS table.

You can locate them by request number, subject line, or the user's email address.

Tested

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

Usage

Paste the script into Toad or SQL Runner

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

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