Product Installation Status, Version and Patch Level Details

col application_id format 99990 heading "ID"
col application_name format a40 heading "Name"
col application_prefix format a6 heading "Prefix"
col application_short_name format a10 heading "Short name"
col apps format a8 heading "Product"
col install_group_num format 90 heading "Inst Grp"
col installed_flag format a9 heading "Type"
col language_code format a4 heading "Code"
col module_short_name format a8 heading "Module"
col module_version format a8 heading "Version"
col product_group_id format 990 heading "ID"
col product_group_name format a28 heading "Product Group Name"
col product_group_type format a10 heading "Type"
col product_version format a8 heading "Version"
col argument1 format a20 heading "Arguments"
col release_name format a12 heading "Release"
col updated format a11 heading "Updated"
col patchset format a20 heading "Patchset Level"
col status format a14 heading "Appl Status"

prompt --> Product Installation Status, Version ,Patch Level and Update date
select decode(nvl(a.APPLICATION_short_name,'Not Found'), 'SQLAP','AP','SQLGL','GL','OFA','FA',
'Not Found','id '||to_char(fpi.application_id), a.APPLICATION_short_name) apps,
decode(fpi.status,'I','Installed','S','Shared','N','Inactive',fpi.status) status,fpi.product_version,
nvl(fpi.patch_level,'-- Not Available --') Patchset,to_char(fpi.last_update_date,'dd-Mon-RRRR') "Update Date"
from fnd_oracle_userid o, fnd_application a, fnd_product_installations fpi
where fpi.application_id = a.application_id(+)
and fpi.oracle_id = o.oracle_id(+)
order by 1,2