When you introduce new business processes to your Oracle® Enterprise Resource Planning (ERP) systems, you might experience a serious notification bottleneck at the application server level rather than the mailer level.
This notification congestion is due to the sudden influx of notifications that business users don’t receive in time or that arrive two to three hours late.
This post helps you improve the performance and make notifications appear in real-time. The post-process completion of the ERP business notifications should reach the approver’s inbox instantaneously, and the email approval should also reflect in the system immediately.
This post offers four possible solutions for your notification performance problem. After you complete a solution’s steps, test the performance and see if it improved.
Stop the Workflow Agent listeners and mailers.
Find the tablespace that creates CORRID indexes for queue tables:
sqlplus apps/<apps_pwd> select distinct tablespace_name from dba_indexes,dba_queues where index_name='WF_NOTIFICATION_OUT_N1' and table_name=queue_table and name like 'WF%';
Rebuild the mailer’s queue:
sqlplus apps/<apps_pwd> @$FND_TOP/patch/115/sql/wfntfqup APPS <apps_pwd> APPLSYS
If the queue rebuild takes more than four or five hours (or never completes), run the following query:
select c.item_type child, decode(c.end_date,null,'OPEN','CLOSED') child_status, c.parent_item_type parent, decode(c.parent_item_type,null,'NOPARENT',decode(p.end_date,null,'OPEN','CLOSED')) parent_status, count(*) from wf_items p, wf_items c where p.item_type(+) = c.parent_item_type and p.item_key(+) = c.parent_item_key and c.item_type='WFERROR' group by c.item_type, decode(c.end_date,null,'OPEN','CLOSED'), c.parent_item_type , decode(c.parent_item_type,null,'NOPARENT',decode(p.end_date,null,'OPEN','CLOSED')) order by c.item_type , c.parent_item_type;
If that query returns one or more items, those items in WFERROR status are still open, or their parent Workflow item is still open. The program Purge Obsolete Workflow Runtime Data concurrent request, FNDWFPR, purges only closed items. You can raise an Oracle Service Request (SR) or see How to Purge WFERROR (System: Error) Workflow Items? (Doc ID 804622.1) for help.
Recreate the index on the CORRID column:
sqlplus applsys/<applsys_pwd> CREATE INDEX WF_NOTIFICATION_OUT_N1 ON WF_NOTIFICATION_OUT(CORRID) STORAGE (INITIAL 1M NEXT 1M MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0) TABLESPACE &tbs;
When prompted for the tablespace, enter the tablespace name from Step 2.
Start the Workflow Agent listeners and mailers.
1, change it to
If an index is missing, add it.
The solution has three parts:
HR_MLR, and enter the rest of the values on that page.
After completing the previous parts, run the following query to find out if the HR_MLR New Mailer service is linked to the new HR_MLRMailer:
select fcq.USER_CONCURRENT_QUEUE_NAME Mailer_Service, fcq.ENABLED_FLAG ENABLED, fsc.COMPONENT_NAME Mailer_Name, fsc.STARTUP_MODE, fsc.COMPONENT_STATUS from APPS.FND_CP_SERVICES fcs,fnd_svc_components fsc, APPS.FND_CONCURRENT_QUEUES_VL fcq where fcs.SERVICE_HANDLE = 'FNDCPGSC' and fsc.component_type = 'WF_MAILER' and fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) order by fsc.STARTUP_MODE;
Note: If, after creating a new workflow mailer service, you find genuine emails going into the DISCARD folder, see Workflow Mailer Randomly Moves Response E-mail Notifications to the Discard Folder (Doc ID 333444.1). You probably have not created separate IMAP accounts for each mailer.
This post presented four solutions to Oracle ERP notification performance issues, from rebuilding the queue to creating a new queue. Hopefully, these options solve your problems.
Use the Feedback tab to make any comments or ask questions. You can also click Let’s Talk to start the conversation.