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