Rebuild Workflow Queue table if the workflow Queue table corrupted or email not sent
First, Verify or set the 'WF: Workflow Mailer Framework Web Agent' profile value to physical host name and port number, DO NOT enter the Load Balancer URL
Example
WF: Workflow Mailer Framework Web Agent=http://erpdev01.blogspot.com:8008
Step 1: shutdown workflow services
Step 2: Execute below scripts(you may get an error that 'queue does not exist' or 'table does not exist', ' does not exist' and you can ignore that part)
sqlplus apps/****
update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status in ('MAIL','INVALID') and Status In ('OPEN', 'CANCELED') and begin_date < sysdate-30;
sqlplus apps/**** @$FND_TOP/sql/wfevqcln.sql WF_DEFERRED oracle.apps.wf.notification.%
sqlplus apps/**** @$FND_TOP/sql/wfevqcln.sql WF_DEFERRED oracle.apps.wf.notification.% 0
sqlplus apps/****
drop table APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK;
create table APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK as select * from APPLSYS.AQ$WF_NOTIFICATION_OUT where CORR_ID like 'APPS:ALR%' and msg_state in ('READY','WAIT');
exec dbms_aqadm.stop_queue(queue_name => 'APPLSYS.WF_NOTIFICATION_OUT', wait => FALSE);
tip: you may get an error that 'queue does not exist' and you can ignore that part
exec dbms_aqadm.drop_queue_table( queue_table => 'APPLSYS.WF_NOTIFICATION_OUT', force => TRUE);
tip: you may get an error that 'queue does not exist' and you can ignore that part
sqlplus apps/**** @$FND_TOP/patch/115/sql/wfjmsqc2.sql applsys ****
sqlplus apps/**** @$FND_TOP/patch/115/sql/wfmqsubc2.sql APPLSYS ****
sqlplus apps/**** @$FND_TOP/patch/115/sql/wfntfqup APPS **** APPLSYS
Create alert_restore.sql file with below sql content and execute with apps user
set serveroutput on size 100000;
declare
x_out_queue varchar2(80) := 'APPLSYS.WF_NOTIFICATION_OUT';
x_enqueue_options dbms_aq.enqueue_options_t;
x_message_properties dbms_aq.message_properties_t;
x_msgid RAW(16);
i number := 0;
p_event SYS.AQ$_JMS_TEXT_MESSAGE;
cursor msg is
select * from APPLSYS.AQ$WF_NOTIFICATION_OUT_BAK
where msg_state in ('READY','WAIT');
begin
for m1 in msg loop
x_out_queue := 'APPLSYS.'||m1.queue;
x_message_properties.correlation := m1.corr_id;
--x_message_properties.original_msgid := m1.msg_id;
x_message_properties.priority := m1.msg_priority;
x_message_properties.recipient_list(1) := sys.aq$_agent(m1.consumer_name,
null,
0);
if (m1.msg_state = 'WAIT') and (m1.delay > sysdate) then
x_message_properties.delay := ((sysdate - m1.delay)*86400);
else
x_message_properties.delay := 0;
end if;
--dbms_output.put_line('msg.msgid = ' || m1.msg_id);
DBMS_AQ.ENQUEUE(
queue_name => x_out_queue,
enqueue_options => x_enqueue_options,
message_properties => x_message_properties,
payload => m1.user_data,
msgid => x_msgid); /* OUT*/
--dbms_output.put_line('x_msgid = ' || x_msgid);
if i = 20 then
i := 0;
commit;
else
i := i + 1;
end if;
end loop;
commit;
exception
when others then
dbms_output.put_line('sqlerrm = ' || sqlerrm);
end;
/
select tablespace_name, index_name from dba_indexes where index_name like 'WF_%_N1';
sqlplus apps/**** @$FND_TOP/patch/115/sql/wfqidxc APPLSYS **** APPS_TS_QUEUES
sqlplus applsys/**** @$FND_TOP/patch/115/sql/wfqidxc2 APPLSYS ***** APPS_TS_QUEUES
sqlplus apps/**** @$FND_TOP/patch/115/sql/wfhistc APPLSYS
step 3: start the workflow services