How to Detect Data Corruption and Purge More Eligible OEOH/OEOL Workflow Items for Order Management Workflow
By gaurav.verma on Mar 17, 2007
The Possibility of Order Management Customizations causing huge non-purgable Workflow data volume in 11i Apps
Often customers have to customize the seeded Order management workflow to tailor it to their business needs. It is not un-common to open service requests with Oracle Support to diagnose huge order management workflow data volume in WF_ITEM_ACTIVITY_STATUSES and WF_ITEM_ACTIVITY_STATUSES_H tables.
If not enough focus is invested in maintaining and developing customizations, it can easily lead to difficult to diagnose problems.
It may get to a point where one may ask - Why does not the data purge automatically? Does the purge even work?
Interestingly, following non-standard customization practices for Oracle Order management workflow and having a custom mechanism of retrying ERRORed workflow activities can and will lead to data corruption between Order management and OEOH/OEOL workflow items. A recently published Note 402144.1 -- FAQ: Best Practices For Custom Order Entry Workflow Design will help understand the caveats and common pitfalls.
Data corruption may be defined as a situation wherein order management status is out of sync with OEOH or OEOL workflow item status. This leads to a lot of non-purgeable workflow data, confusion in issue recognization, lengthy and effort taking data fix TARs and Bugs, follow-up etc.
More Options.. Additional Big Picture Data Mining Whitepaper
How would you like to see the big picture of Order Management and corresponding Workflow information? In a nutshell, if we do not know what we are looking for, it can also be an un-necessary waste of everyone's time and energy.
The recently published white paper (Note 405275.1) presents a high level data mining approach to Oracle community for identifying and debugging Oracle Management Workflow data corruption issues in addition to the BDE (Bugs, Diagnostics and Escalations committee) debugging scripts from Note:183643.1.
What is the New Approach in this Whitepaper ?
Through a data mining approach, it is attempted to provide some new tables and data analysis scripts (which may be further extended). This white paper essentially helps identify the nature of data volume which is not getting purged and evaluate the additional hidden purge potential of OEOH/OEOL workflow items.
This summarized tool can be very useful for Support Analysts, Users and Development personnel to find out the extent of issue at hand or the damage done due to customizations in a particular release. Extended reports can be developed at will to dig deeper or look at data from a different view.
They can be used as a supplement to the standard Workflow debugging and troubleshooting scripts available on metalink.
A quick preview...
An example is the following report which shows the extra hidden potential in OM workflow data which CAN get purged out of WF_ITEM_ACTIVITY_STATUSES and WF_ITEM_ACTIVITY_STATUSES_H:
Through reports and extra aggregate tables, the user gains a much clearer high level integrated view of Order management and Workflow, and has a better understanding of the nature of data volume which is not getting purged.
This approach should be followed for exercising a focused analysis of the order management workflow data volume.
This whitepaper, does not, however, give the tools for fixing the data corruption.
Innovate and Reap the Benefits..
More reports can be designed and explored to suit the user's criteria. They would only limited by imagination and cretivity.
A good example would be a WHAT-IF report .E.g. How much OEOL Workflow volume is out there waiting to be purged, IF we completed processing for Order lines in AWAITING_SHIPPING or AWAITING_RETURN status for Orders which more than 3 months old and are 90-95% closed as per Order Management status?
Additional Readings Suggested..
It is strongly suggested to read a recently published Note 402144.1 -- FAQ: Best Practices For Custom Order Entry Workflow Design to understand the caveats and common pitfalls while undertaking workflow customizations.
Additionally, please check Note 398822.1 regularly for knowing about the latest and greatest data fix patches (and preventive patches, if identified) for different OM data corruption scenarios.