About this blog

As with my website, my blogs espouse the philosophy “not all performance problems are technical” and that in most stable environments user behaviour / usage patterns are typically the source of most correctable performance problems. So if you are into bits, bytes, locks, latches etc this is not the place for you, but if you are after ways to genuinely enhance performance and work with your systems and customers to achieve better, more predictable performance, read on.

Friday, August 13, 2010

What OEBS Application modules are installed?

The following genreates a list of the OEBS Application modules you have installed including their version and patch levels...

SELECT fa.application_id appn_id,
       decode(fa.application_short_name,
       'SQLAP', 'AP', 'SQLGL', 'GL', fa.application_short_name ) appn_short_name,
       substr(fat.application_name,1,50)
              decode(sign(length(fat.application_name) - 50), 1, '...') Application,
       fa.basepath Base_path,
       fl.meaning install_status,
       nvl(fpi.product_version, 'Not Available') product_version,
       nvl(fpi.patch_level, 'Not Available') patch_level,
       to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI') last_update_date,
       nvl(fu.user_name, '* Install *') Updated_by
  FROM applsys.fnd_application fa,
       applsys.fnd_application_tl fat,
       applsys.fnd_user fu,
       applsys.fnd_product_installations fpi,
       apps.fnd_lookups fl
 WHERE fa.application_id = fat.application_id
   and fat.language(+) = userenv('LANG')
   and fa.application_id = fpi.application_id
   and fpi.last_updated_by = fu.user_id(+)
   and fpi.status = fl.lookup_code
   and fl.lookup_type = 'FND_PRODUCT_STATUS'
UNION ALL
SELECT fa.application_id,
       fa.application_short_name,
       substr(fat.application_name,1,50)
       decode(sign(length(fat.application_name) - 50), 1, '...'),
       fa.basepath,
      'Not Available',
      'Not Available',
      'Not Available',
      to_char(fa.last_update_date, 'DD-Mon-YY (Dy) HH24:MI'),
      nvl(fu.user_name, '* Install *')
 FROM applsys.fnd_application fa,
      applsys.fnd_application_tl fat,
      applsys.fnd_user fu
WHERE fa.application_id = fat.application_id
  and fat.language(+) = userenv('LANG')
  and fa.last_updated_by = fu.user_id(+)
  and fa.application_id not in
      ( SELECT fpi.application_id
         FROM applsys.fnd_product_installations fpi )
ORDER by 2;

Example output:


Note: For readability I have converted the SQLAP to AP and SQLGL to GL – this looks so much better for users...

Hope this is useful....

No comments:

Post a Comment