Recently, one of our Oracle Siebel Clinical Trial Management System (CTMS) clients noted that the rollups feature, which collates information from lower levels of the data type hierarchy into a single view (e.g., all site records within a single region or country), was not working properly.
Specifically, the fields that indicate the number of subjects enrolled, screened, and completed at a particular site were incorrectly displaying all zeros.
Since these numbers are calculated by workflows, I thought it would be a good idea to share some of the troubleshooting steps for when workflows “go bad.”
The first thing to note is that Siebel Workflow Policies are converted to database triggers. When a workflow policy is triggered, a record is created in the table S_ESCL_REQ.
After the workflow is successfully run, the record is removed from that table. So, as a first step, use SQLPLUS, SQLDeveloper, or another SQL tool to see whether there are records in that table:
Select count(*) from Siebel.s_escl_req;
COUNT(*)
———-
11,598
When I performed this step during my troubleshooting, it returned over 11,000 records, so clearly there were some issues. Let’s analyze the data further. Run this query:
select unique(group_id) from siebel.s_escl_req;
GROUP_ID
—————
0VO2C-1ARSOV
0V-C3EQX
This tells us the Row_id of the Workflow Policy Group.
Troubleshooting Tip: Each workflow policy group needs a Workflow Policy Monitor to be configured in the user interface (UI). If you have records in the S_ESCL_REQ table that contain a workflow policy group that is not being monitored, you can remedy the issue by either deleting those records from the table (assuming it should not be monitored) or adding a monitor for that group.
Next, try running this query (customized for your own data, of course) for each of the workflow policy groups:
select unique(rule_id) from siebel.s_escl_req where group_id=’0VO2C-1ARSOV’;
RULE_ID
—————
0VW-1ARU5X
That RUL_ID is the ROW_ID in the UI of the workflow policy.
Troubleshooting Tip: Are all the workflow policies that are not needed for your Siebel implementation end-dated? Keep in mind, if you change any workflow policies, triggers will need to be re-generated.
If you have records for workflow policies that should not have been active, those records can be deleted from the S_ESCL_REQ table.
Next, since there were so many records in the S_ESCL_REQ, it is possible that some records could have been orphaned. We ran these queries to see if that was the case:
select * from siebel.s_escl_req t1
where
t1.tbl_name = ‘S_AGREE_SITE_LS’
and not exists (select ‘Y’ from siebel.S_AGREE_SITE_LS where t1.bt_row_id = row_id)
– 3 records
select * from siebel.s_escl_req t1
where
t1.tbl_name = ‘S_PTCL_SITE_LS’
and not exists (select ‘Y’ from siebel.S_PTCL_SITE_LS where t1.bt_row_id = row_id)
– 113 rows
select * from siebel.s_escl_req t1
where
t1.tbl_name = ‘S_CL_SUBJ_LS’
and not exists (select ‘Y’ from siebel.S_CL_SUBJ_LS where t1.bt_row_id = row_id)
— 12 rows
select * from siebel.s_escl_req t1
where
t1.tbl_name = ‘S_DOC_AGREE’
and not exists (select ‘Y’ from siebel.S_DOC_AGREE where t1.bt_row_id = row_id)
select * from siebel.s_escl_req t1
where
t1.tbl_name = ‘S_SRC_PAYMENT’
and not exists (select ‘Y’ from siebel.S_SRC_PAYMENT where t1.bt_row_id = row_id)
select * from siebel.s_escl_req t1
where
t1.tbl_name = ‘S_CL_PTCL_LS’
and not exists (select ‘Y’ from siebel.S_CL_PTCL_LS where t1.bt_row_id = row_id)
The records that were identified as being orphaned were safe to delete from the S_ESCL_REQ table.
In this case, we deleted records for:
- Invalid workflow policy groups
- Invalid workflow policies
- Orphaned data
In the end, we were left with about 8,000 records. We restarted all of the workflow components. Over a period of several hours, Siebel was able to clear all of the records, after which all of the rollup numbers displayed correctly in the Siebel UI.
If you are interested in learning more about Perficient’s capabilities, please fill out the “contact us” form in the footer of this page and let us know how we can help.
We will also be hosting a webinar next month, in which we will highlight not only the features and benefits of Siebel IP2016, but the risks involved in upgrading, including a look at computer system validation (CSV) and regulatory compliance issues. A sneak preview of IP2017, based on the insider information we receive as an Oracle Platinum Partner, will also be provided. Feel free to register!
