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

-- Limit to after a specific date

 -- and nntr.creation_date >= to_date('06-30-2005 12:00:00 AM', 'mm-dd-yyyy hh:mi:ss AM')

-- Limit to before 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 one or more request ID's

 -- and nntp.parent_key_id in ( 123456, 123457 )

-- Limit by specifying part of addressee email

 -- and nntr.email_address like '%dave%'

-- Limit by specifying distinctive sub-string in the message

 and nntp.subject like '%cancelled%'

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