Post date: Nov 27, 2013 7:40:14 AM
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