Query to get alert details in oracle apps

Hi Friends, We are going to discuss about the Query to get alert details in oracle apps. We will share the detail sql query which helps to extract the Oracle alerts complete informations in oracle system. Using this sql query we will be able to fetch only the active alerts in oracle apps. If you want to find out the all Active and Inactive alerts then you need to disable the sql query condition 'enabled_flag = 'Y''. We are also sharing the list of important tables related to oracle alerts in oracle apps. If you want to find out the list of alerts created in the system , you can refer this sql for that. Please find below the complete detail about Query to get alert details in oracle apps.

Query to get alert details in oracle apps

3 Important Tables to get alert details in oracle apps

1.alr_actions_v

2.alr_alerts

3.alr_lookups

Detail Query to get alert details in oracle apps

Here below is the detail sql query to get the alert details in oracle apps

SELECT alr.alert_name,

alr.start_date_active "Alert Start Date", al.meaning "Alert Frequency Type",

DECODE (frequency_type,

'B', days_between_checks,

'W', weekly_check_day,

'M', monthly_check_day_num,

'C', days_between_checks,

NULL

) "Days",

alr_act.subject "Alert Subject" ,

alr_act.msg_body "Alert Message Body",

alr_act.to_recipients "Alert To Address",

alr_act.cc_recipients "Alert CC Address",

alr_act.bcc_recipients "Alert BCC Address"

FROM alr_actions_v alr_act,

alr_alerts alr, 

alr_lookups al

WHERE 1 = 1

AND alr_act.alert_id = alr.alert_id

AND alr.enabled_flag = 'Y'

AND alr.frequency_type = al.lookup_code

AND al.lookup_type = 'ALERT_FREQUENCY_TYPE'

Query to get alert details in oracle apps

Query to get alert details in oracle apps