Workflow Issue

Some time back we had a requirement to resolve the timecard workflows that were in error status at PROD. After some analysis we found that someone has recompiled the older version of the workflow at PROD and as the underlying workflow package was referring to some attributes which were missing in the older workflow the process was going in error status. We had a difficult task ahead..

As it was a weekend there were many timecards that were been submitted and went in error status and all these timecards have to be approved in another couple of days by there respective approving authority failing which these timecards would not be considered for accounting.. Initially to resolve the issue the only way we thought would be possible is to recompile the original workflow, cancel all the timecards manually at PROD and inform all the users with error timecards to resubmit again. Informing to all the users of this count was a hectic task.

We did some further investigations in the workflow developer’s guide and found that we can actually abort the existing process and resubmit the timecards with same item key without the need cancel and inform the users to resubmit the timecards. We tried this process initially at DEV instance and found that it really worked as we expected.

In short the process what we followed is as follows:
1. Recompiled the original workflow.
2. Created a temp table to store the important attribute values for the error workflows.
3. Performed the Abort, Purge, Create and Start process using standard oracle workflow packages. (Before Starting the new process we set the workflow attributes from the temp table.). The below is the code which we used to perform all the process mentioned in the step 3:

/************************************************************
1. Below code is used to retrieve the important attribute values from the workflow table for the error timecards and store them in a temp table.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT DISTINCT item_key
FROM wf_item_activity_statuses
WHERE activity_status = 'ERROR'
AND activity_result_code = '#EXCEPTION'
AND item_type = 'PATCARD'
AND error_stack LIKE '%CHECKPERSONCOSTINGS%';
BEGIN
FOR c1_rec IN c1
LOOP
INSERT INTO xx_wf_temp
(SELECT 'N', c1_rec.item_key,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'APPLICATION_ID'
AND item_type = 'PATCARD') application_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'APPROVER_COUNT'
AND item_type = 'PATCARD') approver_count,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'COMMENT_DOC'
AND item_type = 'PATCARD') comment_doc,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_NAME'
AND item_type = 'PATCARD') employee_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EXPENDITURE_ID'
AND item_type = 'PATCARD') expenditure_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'LINE_TABLE'
AND item_type = 'PATCARD') line_table,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_NAME'
AND item_type = 'PATCARD') manager_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'ORG_ID'
AND item_type = 'PATCARD') org_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_NAME'
AND item_type = 'PATCARD') preparer_name,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'RESPONSIBILITY_ID'
AND item_type = 'PATCARD') responsibility_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TIMECARD_DETAILS'
AND item_type = 'PATCARD') timecard_details,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TIMECARD_NUMBER'
AND item_type = 'PATCARD') timecard_number,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'TOTAL_HOURS'
AND item_type = 'PATCARD') total_hours,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'USER_ID'
AND item_type = 'PATCARD') user_id,
(SELECT date_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'WEEK_END_DATE'
AND item_type = 'PATCARD') week_end_date,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_ID'
AND item_type = 'PATCARD') employee_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_ID'
AND item_type = 'PATCARD') manager_id,
(SELECT number_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_ID'
AND item_type = 'PATCARD') preparer_id,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'EMPLOYEE_DISP_NAME'
AND item_type = 'PATCARD') employee_disp_name,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'MANAGER_DISP_NAME'
AND item_type = 'PATCARD') manager_disp_name,
(SELECT text_value
FROM wf_item_attribute_values
WHERE item_key = c1_rec.item_key
AND NAME = 'PREPARER_DISP_NAME'
AND item_type = 'PATCARD') preparer_disp_name
FROM DUAL);

COMMIT;
END LOOP;
END;

/************************************************************
2. Basically to delete the workflows in open status, we need to abort workflow process first so they will be eligible for purge process. The below code is used to perform the abort process on the workflow. Once the code is executed the workflow status would be set to Completed.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'N'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.abortprocess ('PATCARD', --Item Type
cur_rec.item_key, -- Item Key
'PA_TCARD_APPROVAL_PROCESS', -- Parent Process
'CANCEL' -- Process
);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'A'
WHERE status_stg = 'N' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
3. The below code is used to perform the purge process for the error workflow timecards from the oracle workflow base tables.
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'A'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_purge.items ('PATCARD', cur_rec.item_key, SYSDATE, TRUE, TRUE);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'P'
WHERE status_stg = 'A' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
4. The below code is used to create the timecard again in Oracle with the same item key and with the same version of the workflow existing in Oracle now..
************************************************************/

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'P'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.createprocess ('PATCARD',
cur_rec.item_key,
'PA_TCARD_APPROVAL_PROCESS',
NULL,
cur_rec.preparer_name
);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'C'
WHERE status_stg = 'P' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;

/************************************************************
5. In the below code we are setting the attributes from the temp table in which we have stored all the values at Step 1 and would be starting the workflow process.
/************************************************************

DECLARE
CURSOR c1
IS
SELECT *
FROM xx_wf_temp
WHERE status_stg = 'C'
ORDER BY item_key ASC;
BEGIN
FOR cur_rec IN c1
LOOP
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'COMMENT_DOC',
avalue => cur_rec.comment_doc
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_NAME',
avalue => cur_rec.employee_name
);
wf_engine.setitemattrdocument (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'LINE_TABLE',
documentid => cur_rec.line_table
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_NAME',
avalue => cur_rec.manager_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_NAME',
avalue => cur_rec.preparer_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TIMECARD_DETAILS',
avalue => cur_rec.timecard_details
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TIMECARD_NUMBER',
avalue => cur_rec.timecard_number
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_DISP_NAME',
avalue => cur_rec.employee_disp_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_DISP_NAME',
avalue => cur_rec.manager_disp_name
);
wf_engine.setitemattrtext (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_DISP_NAME',
avalue => cur_rec.preparer_disp_name
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'APPLICATION_ID',
avalue => cur_rec.application_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'APPROVER_COUNT',
avalue => cur_rec.approver_count
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EXPENDITURE_ID',
avalue => cur_rec.expenditure_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'ORG_ID',
avalue => cur_rec.org_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'RESPONSIBILITY_ID',
avalue => cur_rec.responsibility_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'TOTAL_HOURS',
avalue => cur_rec.total_hours
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'USER_ID',
avalue => cur_rec.user_id
);
wf_engine.setitemattrdate (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'WEEK_END_DATE',
avalue => cur_rec.week_end_date
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'EMPLOYEE_ID',
avalue => cur_rec.employee_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'MANAGER_ID',
avalue => cur_rec.manager_id
);
wf_engine.setitemattrnumber (itemtype => 'PATCARD',
itemkey => cur_rec.item_key,
aname => 'PREPARER_ID',
avalue => cur_rec.preparer_id
);
wf_engine.startprocess ('PATCARD', cur_rec.item_key);
COMMIT;

UPDATE xx_wf_temp
SET status_stg = 'S'
WHERE status_stg = 'C' AND item_key = cur_rec.item_key;

COMMIT;
END LOOP;
END;