Query to list out the Schemas and the status of the products in Oracle Applications

 

SELECT   fou.oracle_id                                   "Oracle Id",

         fou.oracle_username                             "Schema Name",

         fa.application_short_name                       "Application Short Name",

         ft.application_name                             "Application Name",

         fa.product_code                                 "Product Code",

         fpi.product_version                             "Product Version",

         DECODE (fpi.status,

                 'I', 'Installed',

                 'N', 'Not Installed',

                 'S', 'Shared Install')                  "Installation Status",

         NVL (fpi.patch_level, '-- Not Available --')    "Patchset",

         TO_CHAR (fpi.last_update_date, 'dd-Mon-RRRR')   "Update Date"

    FROM fnd_oracle_userid fou,

         fnd_application fa,

         fnd_product_installations fpi,

         dba_users du,

         fnd_application_tl ft

   WHERE fpi.application_id = fa.application_id(+)

     AND fpi.oracle_id(+) = fou.oracle_id

     AND du.username(+) = fou.oracle_username

     AND ft.language(+) = 'US'

     AND ft.application_id(+) = fa.application_id

ORDER BY 2