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
