Oracle Alert Back-End Query

Tables:
ALR_ALERTS -  It stores the alerts definition and the query
ALR_ACTIONS - It stores alert action information, ex. email subject, body and other infromation
ALR_ACTIONS_V -- It is a view based on ALR_ACTIONS

Query:
SELECT alert_name,
       description,
       start_date_active,
       Decode (alert_condition_type, 'P', 'Periodic',
                                     'E', 'Event') ALERT_TYPE,
       frequency_type,
       insert_flag,
       update_flag
FROM   apps.alr_alerts
WHERE  enabled_flag = 'Y'
       AND end_date_active IS NULL
       AND alert_name LIKE 'AA%';

SELECT *
FROM   apps.alr_actions_v
WHERE  subject LIKE 'XX%Notification%'; 

/*Query to Find Active Oracle Alert Information in EBS R12*/
SELECT aa.alert_name,
       aa.start_date_active,
       al.meaning                    "FREQUENCY_TYPE",
       Decode (frequency_type, 'B', days_between_checks,
                               'W', weekly_check_day,
                               'M', monthly_check_day_num,
                               'C', days_between_checks,
                               NULL) "Days",
       aav.to_recipients,
       aav.cc_recipients,
       aav.bcc_recipients,
       aav.subject,
       aav.msg_body
FROM   alr_actions_v aav,
       alr_alerts aa,
       alr_lookups al
WHERE  1 = 1
       AND aav.alert_id = aa.alert_id
       AND aa.enabled_flag = 'Y'
       AND aa.frequency_type = al.lookup_code
       AND al.lookup_type = 'ALERT_FREQUENCY_TYPE'
-- AND UPPER (aa.alert_name) LIKE UPPER ('%XX%')
; 

/*Query to find Submitter Oracle Alert Requests*/
SELECT rq.parent_request_id            "Parent Req. ID",
       rq.request_id                   "Req. ID",
       tl.user_concurrent_program_name "User Concurrent Program Name",
       --rq.description,
       Decode (rq.description, NULL, tl.user_concurrent_program_name,
                               rq.description
                               || ' ('
                               || tl.user_concurrent_program_name
                               || ')') "Program Name",
       rq.actual_start_date            "Start Date",
       rq.actual_completion_date       "Completion Date",
       rq.argument_text                parameter,
       Round(( rq.actual_completion_date - rq.actual_start_date ) * 1440, 2) "Runtime (in Minutes)",
       Round(( ( rq.actual_completion_date - rq.actual_start_date ) * 1440 ) /
             60, 2) "Runtime (in Hrs)"
FROM   applsys.fnd_concurrent_programs_tl tl,
       applsys.fnd_concurrent_requests rq
WHERE  tl.application_id = rq.program_application_id
       AND tl.concurrent_program_id = rq.concurrent_program_id
       AND tl.LANGUAGE = Userenv('LANG')
       AND rq.actual_start_date IS NOT NULL
       AND rq.actual_completion_date IS NOT NULL
       AND rq.status_code = 'C' --Status : Normal 
       --and rq.request_id = 104184867
       AND tl.user_concurrent_program_name IN ( 'Check Periodic Alert' )
--and rq.description = '-----'
ORDER  BY 7 DESC;