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

7.   PL/SQL Packages, Procedures and Functions

Identification & Verification

The initial list of unused PL/SQL code objects (Packages/Procedures/Functions) can be identified based on the strategy discussed in Identification section above. Also the unused application components (Workflows, Forms and Reports) removed above will give us additional list of PL/SQL code objects that can potentially be removed.

We then need to verify if the PL/SQL code object is not used in any workflow activity, view definition, any other package, oracle form, oracle report or concurrent program executable.

Check if the PL/SQL code object is used in any workflow activity.

SELECT *   FROM wf_activities  WHERE UPPER (function) LIKE UPPER('%<PACKAGE_NAME>%')    AND end_date IS NULL  ORDER BY version

Check if the PL/SQL code object is used in any views, packages, procedures, functions etc.

SELECT ad.name, -- Referencing Object        ad.type, -- Type of referencing object        ad.*   FROM all_dependencies ad  WHERE referenced_name LIKE UPPER('%<PACKAGE_NAME>%')

Check if the PL/SQL code object is used in any concurrent executable

     

SELECT *   FROM fnd_executables  WHERE UPPER (execution_file_name) LIKE UPPER('%<PACKAGE_NAME>%')    AND execution_method_code = 'I' -- ‘I’ for Oracle PLSQL Code

Check if the PL/SQL code object is used in any form

     

Go to $AU_TOP/forms/US grep –ir <PACKAGE_NAME> *.fmb

Check if the PL/SQL code object is used in any report

     

Go to $CUSTOM_TOP/reports/US grep –ir <PACKAGE_NAME> *.rdf

Removal

The package, procedure or function can be removed using the following SQL command.

DROP PACKAGE <PACKAGE_NAME> DROP PROCEDURE <PROCEDURE_NAME> DROP FUNCTION <FUNCTION_NAME>

8.   Database Views

Identification & Verification

The initial list of unused database views can be identified based on the strategy discussed in Identification section above. Also the unused application components removed will give us additional list of views that can potentially be removed.

We then need to verify if the views are not used in any code such as package/procedure/function, oracle form, oracle report, table type value set and flex fields.

Check if the view is used in any other view, package, procedure, function etc.

     

SELECT ad.name, --Referencing Object        ad.type, -- Type of referencing object        ad.*   FROM all_dependencies ad  WHERE referenced_name LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any value set definition

     

SELECT *   FROM fnd_flex_validation_tables  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any descriptive flex field definition

     

SELECT *   FROM fnd_descriptive_flexs_vl  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any key flex field definition

     

SELECT *   FROM fnd_id_flexs  WHERE UPPER(application_table_name) LIKE UPPER('%<VIEW_NAME>%')

Check if the view is used in any form

     

Go to $AU_TOP/forms/US grep –ir <VIEW_NAME> *.fmb

Check if the view is used in any report

     

Go to $CUSTOM_TOP/forms/US grep –ir <VIEW_NAME> *.rdf

Removal

The view can be removed using the following SQL command.

DROP VIEW <VIEW_NAME>

9.   Database Tables

Identification & Verification

The initial list of unused database tables can be identified based on the strategy discussed in Identification section above. Also the unused application components removed will give us additional list of tables that can potentially be removed.

We then need to verify if the tables are not used in any code such as view definition, package/procedure/function, oracle form, oracle report, table type value set and flex field.

Check if the table is used in any views, package, procedure, function etc.

SELECT name,-- Referencing Object        type -- Type of referencing object   FROM all_dependencies  WHERE referenced_name LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any value set definition

     

SELECT *   FROM fnd_flex_validation_tables  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any descriptive flex field definition

     

SELECT *   FROM fnd_descriptive_flexs_vl  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any key flex field definition

     

SELECT *   FROM fnd_id_flexs  WHERE UPPER(application_table_name) LIKE UPPER('%<TABLE_NAME>%')

Check if the table is used in any form

     

Go to $AU_TOP/forms/US grep –ir <TABLE_NAME> *.fmb

Check if the table is used in any report

     

Go to $CUSTOM_TOP/forms/US grep –ir <TABLE_NAME> *.rdf

Check the latest time a DML Commit operation was performed on a table. If the table stores transactional data, this query will give you a good indication of whether the table is used or not.

Note: The following query will not consider SELECTs performed on the table.

     

SELECT SCN_TO_TIMESTAMP(MAX(ora_rowscn))   FROM <TABLE_NAME>

Removal

The table can be removed using the following SQL command. It is also a suggested to take a temporary backup of the table data before dropping the table.

CREATE TABLE <TABLE_NAME_BKUP>          AS (SELECT * FROM <TABLE_NAME>); DROP TABLE <TABLE_NAME>;

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

5.   Oracle Forms

Identification & Verification

The initial list of unused forms can be identified based on the strategy discussed in Identification section above.

Additionally, unused forms can also be identified by checking the latest time when each form was accessed by a user. Forms which were accessed long back are probably not in use any more and are likely candidates for removal.

Note: The following query will give latest access time for a form only if -

a.     The value for the profile ’Sign-On:Audit Level’ is set to ‘FORM’ and

b.     The built in ‘FND_STANDARD.FORM_INFO’ is called in the ‘PRE-FORM’ trigger with appropriate values for the form.

SELECT ff.form_name,        MAX(flrf.start_time) last_accessed_time   FROM fnd_form ff,        fnd_login_resp_forms flrf        WHERE ff.form_id = flrf.form_id (+)    AND UPPER(ff.form_name) LIKE 'XX%'  GROUP BY ff.form_name    ORDER BY ff.form_name

 Check if any other form is calling the identified form.

Go to $AU_TOP/forms/US grep –ir <FORM_NAME> *.fmb

Removal

Following steps need to be carried out to remove the form.

1) Delete the <FORM_NAME>.fmb file from to $AU_TOP/forms/US

rm –f <FORM_NAME>.fmb

2)Delete the <FORM_NAME>.fmx file from to $CUSTOM_TOP/forms/US

rm –f <FORM_NAME>.fmx

3) Remove the Form Function from the menu

4) Delete the Form Function

6.   Oracle Reports

Identification & Verification

The initial list of unused reports can be identified based on the strategy discussed in Identification section above.

Additionally, unused reports can also be identified by checking if the corresponding concurrent programs are disabled.

SELECT fe.execution_file_name,        fcp.concurrent_program_name   FROM fnd_executables fe,        fnd_concurrent_programs fcp  WHERE fe.execution_method_code = 'P'    -- ‘P’ for Oracle reports    AND fe.executable_id = fcp.executable_id    AND fcp.enabled_flag = 'N'    AND fe.execution_file_name LIKE 'XX%' -- Assuming custom reports are   -- named starting with XX

For each unused report the corresponding executable and concurrent program can be identified. The concurrent program can then be verified as follows.

Check the last time when the report concurrent program was ran.

Note: This data might not give correct usage picture as normally the concurrent program run data is frequently purged.

SELECT *   FROM fnd_conc_req_summary_v  WHERE program_short_name LIKE UPPER('%<PROGRAM_SHORT NAME>%')   AND TRUNC (actual_start_date) < TRUNC (SYSDATE-365)

Removal

Following steps need to be carried out to remove the report.

1)Delete the corresponding executable and the concurrent program

BEGIN   FND_PROGRAM.DELETE_EXECUTABLE ('<EXECUTABLE_SHORT_NAME>', '<APPLICATION_NAME>');   FND_PROGRAM.DELETE_PROGRAM ('<PROGRAM_SHORT_NAME>', '<APPLICATION_NAME>'); END;

2)Delete the <REPORT_NAME>.rdf file from to $CUSTOM_TOP/reports/US

rm –f <REPORT_NAME>.rdf

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

1.   Introduction

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. 

2.   Approach

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.

3.   Identification

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.

4.   Oracle Workflows

Identification & Verification

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>%')

Removal

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