An Easy Way to Mass Progress Workflow Activities

July 7th, 2007

Being that Oracle Order Management is a workflow driven module, there is a tendency for the transactions to get “hung” up on errored or deferred workflow activities that requires the workflow to be retried or progressed.

In most cases there is a corresponding concurrent process that can be executed which progresses transactions that are caught in this state.   For example, if a line tied to the “Line Flow – Generic” workflow is placed on scheduling hold, the “Schedule Orders” concurrent process needs to be executed in order to progress order lines through the “Schedule – Line” activity after the hold has been released.

However, there are some cases where the workflow can get “hung” up on an activity that has no corresponding concurrent process or is in a state where seeded processes such as “Workflow Background” cannot detect the transaction.   In this situation, the only apparent way to progress these transactions (at least in Order Management) is to right-click the transaction, navigate to Workflow, and then select the “Progress Order” option if the workflow is deferred or the “Retry Activities in Error” option if the workflow is in an errored state.   Upon selecting either option a separate pop-window appears showing the workflow activity that needs to be progressed.

This procedure is fine if only a small number of transactions are affected.   However, in situations where there are large volumes this isn’t an effective solution, unless you have a team of people who are dedicated and willing to progress each transaction individually.

Fortunately there is an alternative.   With a little knowledge of the workflow table structure and the workflow engine API’s, you can whip up a quick PL/SQL procedure that can progress a mass number of transactions in a matter of seconds.

During a recent go-live I experienced this exact situation.   The client was utilizing a customized version of the “Line Flow – Generic, with Export Compliance” workflow which contained an activity configured to send each transaction through an XML gateway to an outside processor.   The processor then screens the transactions for export compliance and returns the results via the XML gateway.   Once the

results are received the line is able to progress to “Awaiting Shipping” status or is placed on hold if the transaction violates export compliance.  

However, if for some reason the services hosted by the outside processor temporarily go down or return error responses, it causes the transactions to fail in the “Export Compliance Screening – Line” activity.   Unfortunately, neither the seeded “Export Compliance Screening” concurrent process nor the “Workflow Background” process could progress the transactions so that they are re-screened.   This means that the transactions need to be manually retried.   However, in my case, where several thousand converted sales order transactions were in this errored state, I needed another solution…

I first began looking through the Metalink forums to see if there were other implementors who were caught in this situation.   I ended up finding a good posting that described a programmatic solution utilizing Oracle seeded workflow API to resubmit the transactions – wf_engine.completeactivity().   After a little research I discovered that this was an appropriate API to use.   The next step was to identify the parameters the correct parameters needed to progress my transactions.

Opening a SQL session and performing a “describe” on the wf_engine package shows the parameters needed to call this procedure:

procedure CompleteActivity(itemtype in varchar2,
                                                    itemkey   in varchar2,
                                                    activity in varchar2,
                                                    result     in varchar2)

If you are familiar with workflow terminology and how it works you can pretty much figure out what the required values are for each parameter.   For example, “Itemtype” is the type of workflow object we’re dealing with and is usually visible when looking the activity up through the Workflow Administrator.   So for order headers the parameter would be “OEOH”, and for order lines it would be “OEOL”.   In this situation, since the transactions I’m dealing with are at the line level, I would use “OEOL”.

“Itemkey” is the transaction identifier.   So for order lines it would be the line_id column of the oe_order_lines_all table.   I verified this by performing a Diagnostics > Examine on the order line to obtain the line_id, then making sure that this was the key that appears on the corresponding workflow when viewing the Workflow Status of the line.

The “Activity” parameter is the system name of the activity that needs to be progressed.   This may require some digging in the wf_activities and WF_ITEM_ACTIVITY_STATUSES tables and identifying the exact activity name.   To do this I chose a sample transaction and located the latest status in the WF_ITEM_ACTIVITY_STATUSES table and linked back to the corresponding activity name in wf_activities.   In doing this I identified the activity name to be “EXPORT_COMPLIANCE_ELIGIBLE”.

The “Result” code is what value should result from the completion of the activity.   You also may need to do some digging within the workflow tables to find what the corresponding activity result should be.   In this example, the result code I needed was “OE_EXPORT_COMPLIANCE_RESULTS”.

Now that I’ve identified the parameters, I need to test the procedure call on one of the order line transactions to ensure the approach will work.   To do this, I chose a sample transaction and built a quick PL/SQL which submits this line.

begin   wf_engine.completeactivity(’OEOL’,12345,’EXPORT_COMPLIANCE_ELIGIBLE’, ‘OE_EXPORT_COMPLIANCE_RESULTS’);
commit;
end;

After executing the PL/SQL, I confirmed that the transaction went to an “Awaiting Export Compliance Screening” status, and after a response was received from the outside processor, the transaction then went to “Awaiting Shipping”.  

It worked!   But now the next step was to build a query that will loop through all the transactions so that I can call the procedure for each line that is stuck in the export compliance activity.   So by evaluating at a few sample “stuck” transactions in the oe_order_lines_all table, I was able to identify a query:

select ool.line_id
from oe_order_lines_all ool
where ool.flow_status_code = ‘EXPORT_SCREENING_DATA_ERROR’;

Next, I need to build a PL/SQL that will loop through all the results of this query and call the wf_engine.completeactivity() procedure.   The below script accomplishes this by turning the above query into a cursor:

declare
CURSOR c1
IS
select ool.line_id
from oe_order_lines_all ool
where ool.flow_status_code = ‘EXPORT_SCREENING_DATA_ERROR’;
v_item_key             number;
begin
open c1;
loop
fetch c1 into v_item_key;
EXIT WHEN c1%NOTFOUND;
  wf_engine.completeactivity(’OEOL’,v_item_key,’EXPORT_COMPLIANCE_ELIGIBLE’, ‘OE_EXPORT_COMPLIANCE_RESULTS’);
end loop;
close c1;
commit;
end;

After running the above script in a pre-production instance I confirmed that all the errored transactions were progressed.   This definitely beats manually progressing all of the transactions!

Entry Filed under: Order Management, Technical