Event Alert does not fire or ( ALECTC ) Concurrent Request does not start

Event Alert does not fire or Check Event Alert ( ALECTC ) Concurrent Request does not start

CHANGES

Symptoms : Event Alert does not fire or Check Event Alert ( ALECTC ) Concurrent Request does not start
Changes : Doing the setup of an Event Alert.
Cause : The cause can be setup or code related
Solution : 

I) Setup steps to verify

In the Responsibility : Alert Manager
a) Navigate to : System Options.

Tab : Mail Systems => Verify that only Oracle InterOffice is checked as In Use
Tab : Mail Server Options => Verify that the User Name for Send Mail Account and Response Mail Account is set for both to ORACLE

b) Navigate to : System Applications

Verify that the Application you want to setup an Alert for is listed in this form

c) Navigate to : System Installations

Verify that the Application you want to setup an Alert for is listed in this form

d) Navigate to : Alert Define

Main Form => Verify that the enabled flag is activated on the defined Alert
Button : Actions => Verify that there is at least 1 Action defined
Button : Action Details => verify that for Action Type : Message that there is an e-mail defined for the field : To

Button : Action Sets => Verify that there is at least 1 Action Set defined and enabled
Button : Action Sets Details => Tab Members should have at least 1 row defined

Button : Alert Details => Tab : Installations => Verify that the Alert is enabled for all Operating Units you want to use the Alert for.
This setup is very important as it will be included in the Alert Trigger definition.
The Alert database trigger will only fire if the Operating Unit set in the alert definition matches the org_id for your session.
The session org_id depends on the profile options : MO: Operating Unit, MO: Security profile and MO: Default Operating Unit

In the Responsibility : System Administrator

a) Navigate to : Profile System

Query the Profile Options ; MO%
For Release 11i make sure that the Profile Option : MO: Operating Unit has a value
For Release 12 with the new MOAC setup make sure 

1) Profile Option : MO: Operating Unit has a value OR
2) The Profile Options MO: Security profile AND MO: Default Operating Unit have a value

With Workflow used for email:

a) Navigate to : System Options.

Tab : Mail Systems => Verify that only Oracle InterOffice is checked as In Use --> As long as ANY one Name is marked in use, this will work.
Tab : Mail Server Options => Verify that the User Name for Send Mail Account and Response Mail Account is set for both to ORACLE --> This section is ignored now.

II) SQL to verify the setup

a) Verify if the Alert trigger is valid and enabled:

SELECT DBA_TRIGGERS.TABLE_NAME,
               DBA_TRIGGERS.TRIGGER_NAME,
               DBA_TRIGGERS.STATUS, USER_OBJECTS.STATUS,
               DBA_TRIGGERS.TRIGGER_BODY
FROM    USER_OBJECTS, DBA_TRIGGERS
WHERE  DBA_TRIGGERS.TRIGGER_NAME = USER_OBJECTS.OBJECT_NAME AND
                   USER_OBJECTS.OBJECT_TYPE = 'TRIGGER'
   AND        USER_OBJECTS.OBJECT_NAME LIKE 'ALR_%'

=> One can add the first characters of the table you defined the trigger on. 

e.g user_objects.object_name like 'ALR_FND_PROFILE%' , but don't use the full table_name as the trigger name doesn't contain the full table name.

b) Verify that the concurrent request ; 'Check Event Alert' has been started and completed normal.

SELECT REQUEST_ID
             , REQUEST_DATE
             , PHASE_CODE
             , STATUS_CODE
             , ORACLE_ID
             , PROGRAM_APPLICATION_ID
             , CONCURRENT_PROGRAM_ID
FROM APPLSYS.FND_CONCURRENT_REQUESTS
where Concurrent_program_id = (select Concurrent_program_id from fnd_concurrent_programs_tl where
USER_CONCURRENT_PROGRAM_NAME = 'Check Event Alert') order by request_date desc;

Note : PHASE_CODE = C and STATUS_CODE = C means Completed Normal.

Challa.