REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM <SQLDIR_GRP>APPS_INST</SQLDIR_GRP>
REM $Header: adutconf.sql 110.2.1100.2 98/02/04 09:50:42 hwood ship $
REM +======================================================================+
REM | Copyright (c) 1996 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM NAME
REM   adutconf.sql
REM DESCRIPTION
REM   <SQLDIR_TXT>
REM   Utility script to display configuration of Applications
REM   </SQLDIR_TXT>
REM NOTES
REM   In 10.6 and higher connect to APPS schema
REM +======================================================================+

spool adutconf.lst

set verify off
set pages 9999
set lines 79

col application_id 	format 99990 	heading "Appid"
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 "Prod"
col argument1		format a15	heading "Arguments"
col basepath 		format a8 	heading "Basepath"
col index_tablespace	format a12	heading "Index TS"
col install_group_num	format 90	heading "IGN"
col language_code	format a4	heading "Code"
col module_short_name	format a8 	heading "Module"
col module_version	format a8 	heading "Version"
col oracle_username	format a12	heading "Schema"
col product_group_id	format 990	heading "ID"
col product_group_name	format a25	heading "Product Group Name"
col product_group_type	format a10	heading "Type"
col product_version	format a8	heading "Version"
col release_name	format a12	heading "Release"
col sizing_factor	format 9990	heading Size
col tablespace		format a12	heading "Main TS"
col temporary_tablespace format a8	heading "Temp TS"

prompt --> Start of Application Information Gathering
prompt
prompt --> Determining information about this Product Group

select product_group_id, product_group_name, release_name, 
	product_group_type, argument1
  from fnd_product_groups;

prompt --> Multi-Org installed?

set head off
select decode(multi_org_flag,'N','No','Y','Yes','Null') from fnd_product_groups;
set head on

prompt --> Selecting Operating Units
select ORGANIZATION_ID, NAME from hr_operating_units
order by ORGANIZATION_ID;

prompt --> Multi-Lingual installed?

set head off
select decode(multi_lingual_flag,'N','No','Y','Yes','Dont Know') from fnd_product_groups;
set head on

prompt --> Multi-Currency installed?

set head off
select decode(multi_currency_flag,'N','No','Y','Yes','Dont Know') from fnd_product_groups;
set head on

prompt --> Determining Licensed, Shared and Other products

select decode(a.APPLICATION_short_name,
	'SQLAP','AP','SQLGL','GL','OFA','FA',
	a.APPLICATION_short_name) apps,
	o.ORACLE_username, fpi.status, fpi.install_group_num,
	fpi.product_version, fpi.sizing_factor, 
	fpi.tablespace, fpi.index_tablespace, fpi.temporary_tablespace
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
/

prompt --> Determining Installed modules

select decode(a.APPLICATION_short_name,
	'SQLAP','AP','SQLGL','GL','OFA','FA',
	a.APPLICATION_short_name) apps
	, o.ORACLE_username
	, fmi.module_short_name
	, fmi.module_version
	, fmi.db_status
from fnd_oracle_userid o, fnd_application a, fnd_module_installations fmi
where fmi.application_id = a.application_id(+)
  and fmi.oracle_id = o.oracle_id(+)
order by 1,2,3
/

prompt --> Determining registered applications

select application_id, application_short_name, basepath
from fnd_application
order by application_id
/

prompt --> Determining registered ORACLE schemas

select oracle_id, oracle_username, install_group_num, read_only_flag
from fnd_oracle_userid
order by 1
/

prompt --> Determining base language and other installed languages

select decode(installed_flag,'I','Installed','B','Base','Unknown') 
	installed_flag,
	language_code, nls_language from fnd_languages
where installed_flag in ('I','B')
order by installed_flag
/

prompt
prompt --> End of Application Information Gathering
spool off