How to PURGE EBS R12 Workflow Error WFERROR Notifications
Following Workflow Purge Action Plan is applicable for WFERROR notifications that are associated with parent workflow. This type of Error message generates when something has happened in Parent and then an alert is sent to SYSADMIN.
Figure : Sample ERROR Message
1. Find out total number of WFERROR notifications till sysdate.
FROM wf_items
WHERE item_type = 'WFERROR'
AND end_date is null
Output:-
COUNT(*)
----------
10631
SQL> 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;
Output:-
CHILD CHILD_ PARENT PARENT_S COUNT(*)
-------------- ------ ------------- -------- ----------
WFERROR OPEN APINVAPR OPEN 2
WFERROR OPEN DBLMOA CLOSED 1443
WFERROR OPEN DBLMOA OPEN 207
WFERROR OPEN IGSPE002 OPEN 8704
WFERROR OPEN POAPPRV CLOSED 49
WFERROR OPEN POAPPRV OPEN 7
WFERROR OPEN POWFPOAG OPEN 9
WFERROR OPEN POWFRQAG OPEN 1
WFERROR OPEN REQAPPRV CLOSED 13
WFERROR OPEN REQAPPRV OPEN 130
WFERROR OPEN NOPARENT 66
2. Find out total number of WFERROR notifications generated before 30 days.
FROM wf_items
WHERE item_type = 'WFERROR'
AND end_date is null
AND begin_date <= sysdate-30;
Output:-
COUNT(*)
----------
9994
3. ABORT all the OPEN WFERROR notifications other than last 30 days
CURSOR Ab_wf IS
SELECT item_key
FROM wf_items
WHERE item_type = 'WFERROR'
AND end_date is null
AND begin_date <= sysdate-30;
-- AND begin_date <= sysdate; (If you want to delete all notifications)
BEGIN
FOR i IN Ab_wf LOOP
WF_ENGINE.abortProcess('WFERROR', i.item_key);
END LOOP;
COMMIT;
END;
/
Output:-
SQL> PL/SQL procedure successfully completed.
4. Find out Child Error Status
SQL> 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;
Output:-
CHILD CHILD_ PARENT PARENT_S COUNT(*)
------- ------ -------- -------- ----------
WFERROR OPEN APINVAPR OPEN 2
WFERROR CLOSED DBLMOA CLOSED 975
WFERROR CLOSED DBLMOA OPEN 197
WFERROR OPEN DBLMOA CLOSED 468
WFERROR OPEN DBLMOA OPEN 10
WFERROR CLOSED IGSPE002 OPEN 8704
WFERROR OPEN POAPPRV CLOSED 49
WFERROR OPEN POAPPRV OPEN 7
WFERROR CLOSED POWFPOAG OPEN 5
WFERROR OPEN POWFPOAG OPEN 4
WFERROR CLOSED POWFRQAG OPEN 1
WFERROR CLOSED REQAPPRV CLOSED 7
WFERROR CLOSED REQAPPRV OPEN 105
WFERROR OPEN REQAPPRV CLOSED 8
WFERROR OPEN REQAPPRV OPEN 23
WFERROR OPEN NOPARENT 66
5. Generate script and PURGE WFERROR notifications
From SQL*Plus as APPS user generate instructions using,
SQL> spool purge_wferror.sqlSQL> set heading off
SQL> select 'exec WF_PURGE.ITEMS('''||item_type||''','''||item_key||''',SYSDATE,TRUE,TRUE);'
from wf_items
where item_type = 'WFERROR'
and end_date is not null;
SQL> spool off;
9994 rows selected.
Run previous instructions generated in the spool file connections as apps user
SQL>@purge_wferror.sql;
Output:-
SQL> PL/SQL procedure successfully completed.
6. Check current WFERROR notification status
At this point only last 30 days notifications will be present.
SQL> 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;
Output:-
CHILD CHILD_ PARENT PARENT_S COUNT(*)
------- ------ -------- -------- ----------
WFERROR OPEN APINVAPR OPEN 2
WFERROR OPEN DBLMOA CLOSED 468
WFERROR OPEN DBLMOA OPEN 10
WFERROR OPEN POAPPRV CLOSED 49
WFERROR OPEN POAPPRV OPEN 7
WFERROR OPEN POWFPOAG OPEN 4
WFERROR OPEN REQAPPRV CLOSED 8
WFERROR OPEN REQAPPRV OPEN 23
WFERROR OPEN NOPARENT 66
SQL> SELECT COUNT(*)
FROM wf_items
WHERE item_type = 'WFERROR'
AND end_date is null
Output:-
COUNT(*)
----------
637
So total 10631-637 = 9994 open WFERROR successfully purged.
......ENJOY....
