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