Kintana Script : User Not Getting email Notifications

Purpose

If a user complains he isn't getting email notifications this script will locate the messages she's been sent.

You may find this faster and more flexible than the standard 'Notification History Report' which
only allows you to limit the output to an entire day

The NOTIFICATION_SENT_FLAG output column indicates a positive acknowlegment from the email server that it
accepted the message from the Kintana package.

Tested

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

Usage

Paste the following script into SQL Runner

You can filter the output by date range, request id, email address, or sub-string in the message.

I suggest you start with a date filter add limits from there on.

Omit the comment unless you're using a PL-SQL editor.
SQL Runner only supports select statements and you'll get a KNTA-10648 error.

-- List messages sent to a specific user email address or with a specified string in the subject.

 select nntp.parent_key_id "RequestID", nntr.email_address,
        nntr.creation_date, nntp.notification_sent_flag,
        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
 -- and nntr.creation_date >= to_date('06-30-2005 12:00:00 AM', 'mm-dd-yyyy hh:mi:ss AM') -- Limit to after a specific date
 -- and nntr.creation_date <= to_date('06-30-2005 12:59:59 PM', 'mm-dd-yyyy hh:mi:ss AM') -- Limit to before a specific date
 -- and nntp.parent_key_id in ( 123456, 123457 )   -- Limit to one or more request ID's
 -- and nntr.email_address like '%dave%'          -- Limit by specifying part of addressee email
 and nntp.subject like '%cancelled%'               -- Limit by specifying distinctive sub-string in the message
 order by nntr.creation_date desc

 RequestID EMAIL_ADDRESS         CREATION_DATE             NOTIFICATION_SENT_FLAG SUBJECT
 123456    dave_goodall@ezds.com 6/15/2005 18:53:36.000 AM Y                      QWICGRAM: Request #123456 has been cancelled.
 

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