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>;