BIP – Find list of message sent from Oracle Alert

ByMandeep Gupta

BIP – Find list of message sent from Oracle Alert

Alert composer a very powerful and useful tool to configure and sent notifications to the users. However, it is very important for business to know the list of receipients for each message. I have tried to built a query to get these details.

SELECT hat.name alert_name
      ,harr.sent_to
      ,harr.sent_on
 FROM HRC_ALERTS_TL hat
     ,HRC_ALERT_RUNS har
     ,HRC_ALERT_RUN_MESSAGES harm
     ,HRC_ALERT_RUN_RECIPIENTS harr
WHERE hat.name = 'Learning Assignment Alert'
  AND hat.alert_id = har.alert_id
  AND hat.language = 'US'
  AND har.created_by = 'abc@demo.com'
  AND TRUNC(har.creation_date) = TRUNC(SYSDATE)
  AND har.run_id = harm.run_id
  AND harm.run_id = harr.run_id
  AND harm.run_message_id = harr.run_message_id
  ORDER BY harr.sent_to

From alert history, one can find the emails triggered but it doesn’t give an option to export this. This is where the query is helpful.

About the author

Mandeep Gupta administrator