Thursday, December 21, 2017

EBS Workflow WFERROR Notifications Purge

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. 


SQL> SELECT COUNT(*)
           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. 


If you don't want to keep 30 days WFERROR notifications then find out total number using below query.


SQL> SELECT count(*)
           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


SQL> DECLARE
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.sql
SQL> 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....