Listing existing Maximo/TPAE customizations

During my job assignments it often happens that I'm sent to a customer that already has Maximo in production with some customizations in place. One of the problems in such cases is to quickly get an overall picture of how many customizations have been made and what have been customized.
One approach that I have found useful is to 'diff' the existing environment with a clean one exporting Maximo definitions into text files. The only problem with this approach is that requires a clean installation of the product. If you don't have a clean system to compare you can use a simpler approach.

I have developed a set of database select statements whose output can be put into one or more text files. I export the results of such queries on both production and reference system. Then I use a diff tool to compare the outputs.
Note that this approach can be useful also to detect differences between dev-test-production systems.

Database

The following queries list the objects and attributes defined on the Maximo database. The number, location and type of changes give a very quick idea of the amount of customizations in place. For example many changes on classname fields point out heavy Java customizations.

select
  servicename, objectname, classname, description,
  persistent, entityname, extendsobject, siteorgtype, userdefined, mainobject, internal, eauditenabled, eauditfilter, esigfilter
from maxobject
order by servicename, objectname;

select
  objectname, attributeno, attributename,
  alias, autokeyname, canautonum, classname, columnname,
  complexexpression, defaultvalue, domainid, entityname,
  handlecolumnname, isldowner, ispositive, length, localizable,
  maxtype, mlinuse, mlsupported, mustbe, persistent,
  primarykeycolseq, remarks, required, restricted,
  sameasattribute, sameasobject, scale, searchtype,
  textdirection, title, userdefined, eauditenabled, esigenabled
from maxattribute
order by objectname, attributeno, attributename;

Applications

Applications are stored in the MAXAPPS table.

select app, apptype, custapptype, description,
  maintbname, orderby, originalapp, reportobject, restrictions
from maxapps
order by app;

The problem with this approach is that it does not point out changes in the application definitions. Those changes are stored in MAXPRESENTATION table. However, the applications definitions are stored in XML format into CLOB field so it is hard to export all such fields in a single text file. An approach is to calculate a hash value of such field in order to be able to quickly detect changes. Starting from Oracle 10g a function called ora_hash has been introduced. Here follows the 'enhanced' query.

select
  m.app, m.apptype, m.custapptype, m.description,
  m.maintbname, m.orderby, m.originalapp, m.reportobject, m.restrictions,
  ora_hash(mp.presentation)
from maxapps m
join maxpresentation mp on mp.app=m.app
order by m.app;

Another (maybe simpler) approach could be to export the entire list of applications definition. This can be achieved opening the Application Designer application, listing all the applications (simply press enter) and clicking on 'Export application definitions' button on the toolbar.

Reports

Reports are stored in the REPORT table.

select
  reportfolder, appname, basetablename, reportname, runtype,
  description, scheduleonly, norequestpage, detail,
  toolbarlocation, toolbaricon, toolbarsequence, destinationfolder
from report
order by reportfolder, appname, reportname;

Reports are stored in the REPORTDESIGN table. Here we have the same problem found for Maximo application so we need to use the 'hashing' solution.

select
  r.reportfolder, r.appname, r.basetablename, r.reportname, r.runtype, r.description,
  r.scheduleonly, r.norequestpage, r.detail, r.toolbarlocation,
  r.toolbaricon, r.toolbarsequence, r.destinationfolder,
  ora_hash(rd.design)
from report r
join reportdesign rd on rd.reportname=r.reportname;
order by reportfolder, appname, reportname;

Menus

System and application menus including search definitions are stored in the MAXMENU table.

select 
  menutype, moduleapp, position, subposition, visible, elementtype,
  keyvalue, headerdescription, image, tabdisplay, accesskey, url
from maxmenu
order by menutype, moduleapp, position, subposition;

Final considerations

The described approach has several limitations including the not comprehensive coverage of all the possible customizations that are available within TPAE. Another limitation is the hashing solution that is only described for Oracle databases. Please send me any updates and comments that can improve the quality or coverage of this solution.