Good
systems are not built they grow! They grow over time with addition of new
functionality and features. At the same time many functionalities and features in
a system lose their relevance over time and are no longer used. The software
components behind these unused features manifest themselves as “technical debt”
within the system.
For
financial debts the hidden cost is called “interest”. For technical debt,
interest takes the form of increased maintenance costs due to impact on system
performance, tests, documentation etc. If one repays the financial debt, the
interest costs reduce. Similarly by removing the technical debt the maintenance
costs reduce and system performance improves!
This
document discusses how technical debt within an Oracle EBS based system can be
removed.
At
a high level the approach is to identify unused components, verify that they
are indeed not used by performing dependency analysis and finally perform steps
to remove the components.
Separate
sections in the document list the details of the above approach for different
Oracle EBS components such as Workflows, Forms, Reports, PL/SQL Code Objects
(Packages, Procedures and Functions), Views and Tables. To implicitly avoid
dependency related issues we start by removing the application components (Workflows,
Forms and Reports) followed by code objects (PL/SQL Code Objects and Views) and
finally the data objects (Tables).
The
document does not describe approaches to remove applications components built
using Oracle Application Framework (OAF), Discoverer and XML/BI Publisher. We
believe that the reader can devise the approach for removing these components
using similar principles described for other components in this document.
The
first and foremost step to identify unused components is to talk to different
stakeholders of the system such as system owners, architects, developers and system
support team to get a list of unused functionality and features. The software
components behind the identified functionality and features can then be
identified. Members of the application development and support team may also be
able to provide you with the list of unused software components.
This
activity will provide the first working list of unused software components. This
list is then further refined based on individual identification strategies
described in following sections.
The
initial list of unused workflows can be identified based on the strategy
discussed in Identification section above.
Additionally,
unused workflows can be identified by checking the latest time when each workflow
item and the processes within it were ran. Workflow item types and corresponding
processes which were ran long back are probably not in use any more and are
likely candidates for removal.
Note:
This query might not give correct usage picture as normally the workflow
runtime data is frequently purged.
|
SELECT wpa.process_item_type,
wpa.process_name,
MAX (wias.begin_date) latest_run_time
FROM wf_process_activities wpa,
wf_item_activity_statuses wias
WHERE wpa.instance_id =
wias.process_activity (+)
AND wpa.process_item_type LIKE 'XX%'
AND wpa.process_name <> 'ROOT'
GROUP
BY wpa.process_item_type, wpa.process_name
ORDER
BY wpa.process_item_type, wpa.process_name
|
The
identified workflows can then be verified by checking if the workflow is being
referred in any program.
|
SELECT
*
FROM all_source
WHERE UPPER (text) LIKE UPPER('%<ITEM_TYPE_NAME>%')
|
Workflow
data is of two types, design time and run time. The Design time data is the
actual workflow definition, initially created as WFT file and uploaded to
workflow design time tables in the database. Every execution of the workflow process
also generates data which is stored in the workflow runtime tables.
Even
if a workflow is no longer being used, the workflow runtime data of the past
executions might be required to comply with statutory requirements or customer
needs. Removal of the workflow is therefore not a straightforward decision.
Careful consideration is therefore required to determine whether it is okay to
remove the workflow runtime data or both (runtime and design time).
Note: It is not possible to remove only design
time data.
To
remove workflow runtime data a seeded concurrent program “Purge Obsolete
Workflow Runtime Data” is available. This program takes ‘Item Type’ as a
parameter. Another important parameter is ‘Age’; this is the minimum age of
data to purge in days.
To
remove both, workflow design time and runtime data, a seeded script “Wfrmitt.sql”
has been provided by Oracle. The script is available in $FND_TOP/sql folder,
the same can be copied to a suitable working folder. Run the following command
to execute the script, the script then asks for item name that needs to be
removed.
|
sqlplus <user/pwd> @wfrmitt
|
No comments:
Post a Comment