In this article, we will look into a query that can get the list of all alerts on Alert Composer by module.
Many times, during the implementation of a module, we need to get the list of all alerts, for us to review them with the client and use the ones which are needed.
The below query will extract all alerts by module along with the filter criteria, recipients, body, delivery type, etc.
select HRALVL.ALERT_TYPE
,HRALVL.ENABLED
,HRALVL.ALERT_CODE
,HRALVL.NAME As Alert_Name
,HRALVL.DESCRIPTION
,HRALVL.FILTER_EXPRESSIONS
,HRALVL.LAST_UPDATED_BY
,HRALVL.LAST_UPDATE_DATE
--,HRALVL.RESOURCE_APPLICATION
--,HRALVL.RESOURCE_PATH
--,HRALVL.RUN_EXPRESSION
--,HRALVL.FILTER_EXPRESSIONS
,HRALTEMVL.STORE_MESSAGES
,HRALTEMVL.NAME as TEMPLATE_Name
,HRALTEMVL.BODY as temp_body
,HRALTEMVL.SUBJECT
,HRALTEMVL.DEFAULT_LANGUAGE
,HEALRE.DELIVER_TYPE as Recipint_delivey_type
,HEALRE.CHANNEL_TYPE as Recipient_Channel_type
,HEALRE.VALUE_EXPRESSION
,fat.user_module_name
--,HRALKEE.SENT_TO
--,HRALKEE.EXPIRES_ON
--,HRALKEE.KEEP_COUNT
from HRC_ALERTS_VL HRALVL,
HRC_ALERT_TEMPLATES_VL HRALTEMVL,
HRC_ALERT_RECIPIENTS HEALRE,
Fnd_appl_taxonomy_vl fat
where 1=1
--and HRALVL.ALERT_CODE Like 'IRC%'
and HRALVL.ENABLED = 'Y'
and hralvl.module_id = fat.module_id
and HRALTEMVL.ALERT_ID = HRALVL.ALERT_ID
and HEALRE.TEMPLATE_ID=HRALTEMVL.TEMPLATE_ID
and ((COALESCE(null, :MODULE) is null) OR (fat.user_module_name IN (:MODULE)))
AND HRALVL.ALERT_CODE like '%' || :ALERT_CODE || '%'
order by fat.user_module_name,HRALVL.ALERT_CODE
