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 = '[email protected]'
  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.