• Post category:BI Publisher
  • Post comments:0 Comments
  • Post last modified:January 25, 2022
  • Reading time:2 mins read
You are currently viewing How to extract the list of Alerts from Alert Composer?
How to extract the list of Alerts from Alert Composer?

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.

SQL Query:

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
If you like the content, please follow us on LinkedInFacebook, and Twitter to get updated with the latest content.