Query to find the Schemas and the status of the products in Oracle Applications.

 SELECT fou.oracle_id

      ,fou.oracle_username ora_schema_name

  ,fa.application_short_name 

  ,ft.application_name

  ,fa.product_code

  ,fpi.product_version

  ,DECODE (fpi.status, 'I', 'Installed', 'N', 'Not Installed', 'S', 'Shared Install') installation_status

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

  ,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(+) = USERENV('LANG')

   AND ft.application_id(+) = fa.application_id 

ORDER BY fou.oracle_username