More date arithmetic

I came across this today. I have been having quite a ball these last weeks with dates, periods and mainly overlapping periods in two tables. Therefore I am currently rather interested in date handling. This is definitely one for the Tips & Tricks book I seem to be gathering here. Nice post.

OWB Process Flows that keep saying they are running

Now and then an OWB started Processflow has trouble finishing. Or more likely I have created an incomplete Processflow. Most of the time this leads to an error when deploying the processflow again:

RPE-02062: ItemType LAAD_CMS cannot be dropped as it has running Processes. You must first abort all the running processes using the Oracle Workflow Monitor

To find out if there are any such processflows I use the following query:

SELECT item_type, item_key
FROM wf_item_activity_statuses_v
WHERE activity_type_code= 'PROCESS'
AND activity_status_code != 'COMPLETE';

And you can tell OWF the processflow is not running anymore with:

DECLARE
t_result VARCHAR2(300);
BEGIN
wf_engine.abortprocess (‘itemtype’,’itemkey’,t_result);
COMMIT;
END;

Where Itemtype and ItemKey must be copied from the results of the query.

There are probably more sophisticated methods to handle these situations (probably from OWF-savvy people), but it gives me a handle to know what is going on and what I can do about it.