Back in April, there was a post on OTN relating to whether you could specify the date when aborting a Workflow process using the standard WF_ENGINE.AbortProcess API. The reason seemed to be that the user only wanted to purge data according to a specific date (e.g. up to the end of 2010).
As I said in my response, it doesn’t make sense to try to convince the system that you did something on a different date. If you set it to BEGIN_DATE+10, what happens to the child activities which occurred on BEGIN_DATE + 11? You would have an end date that was before the begin date, which doesn’t do much for any auditing and reporting that you want to do.
If the requirement is actually to abort and purge processes which started before a certain date, then the code below should help:
BEGIN FOR i IN ( SELECT item_type , item_key FROM wf_items WHERE 1 = 1 ) LOOP WF_ENGINE.AbortProcess ( i.item_type, i.item_key ); END LOOP; FOR j IN ( SELECT item_type , item_key FROM wf_items WHERE TRUNC(begin_date) <= TRUNC(TO_DATE('31-DEC-2010' ,'DD-MON-YYYY')) ) LOOP WF_PURGE.items ( j.item_type, j.item_key ); END LOOP END;
Modify the implicit cursor in the i loop to find the records that you want to abort, modify the cursor in j loop to include any other criteria.
The reason that you have to do them in loops is to ensure that the processes have all been aborted before trying to purge them - the process (and any child processes it spawns) must be complete before it can be purged. Likewise, when purging child flows, the parent must have completed before it can be purged (something I suggested as a feature many years back :))