Thursday, December 5, 2013

Identification and Removal of Unused Custom Functionality in Oracle EBS - Part 1

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.

  FROM all_source

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

1 comment:

  1. Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check