Puriging a Complete Item Process and It's Child Process

If there is a situation to close the old itemtype and its child processes and all the itemkeys for that Workflow process, there could be 2 ways to do it:


Firstly using the WF_PURGE.TOTAL:

DECLARE
CURSOR c1 IS
SELECT /*+ FIRST_ROWS */ WI.ITEM_TYPE, WI.ITEM_KEY
FROM WF_ITEMS WI
WHERE end_date is not null and BEGIN_DATE < TO_DATE('30-SEP-2007','DD-MON-YYYY')
AND EXISTS
(SELECT NULL
FROM WF_ITEM_TYPES WIT
WHERE WI.END_DATE+nvl(WIT.PERSISTENCE_DAYS,0) <= SYSDATE
AND WI.ITEM_TYPE = WIT.NAME
AND WIT.PERSISTENCE_TYPE = 'TEMP');
v_count PLS_INTEGER := 0;
BEGIN
DBMS_OUTPUT.PUT_LINE('START DATE'||sysdate);
FOR i IN c1 LOOP
WF_PURGE.Total ( itemtype => i.item_type
, itemkey => i.item_key );
IF v_count = 50 THEN
v_count := 0;
COMMIT;
ELSE
v_count := v_count + 1;
END IF;

Second Method:

DECLARE
   t_result   VARCHAR2 (300);
BEGIN
   FOR c
      IN (SELECT item_type, item_key
            FROM wf_item_activity_statuses_v
           WHERE     activity_type_code = 'PROCESS'
                 AND activity_status_code != 'COMPLETE')
   LOOP
      wf_engine.abortprocess (c.item_type, c.item_key, t_result);
   END LOOP;

   COMMIT;
END;

Even this sometimes returns 0 records

Then just execute the following sql :


Run the WFRMITT.sql script which deletes all definitions for an Item