Friday, November 29, 2013

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

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect