spool app_modules_oracle-11.5.9.rslt 

set linesize 130;
set pagesize 1000;
set long 50;
set echo off;
set feedback off;
 
col DESC format a30
col VERSION format a8
col Installed format a12
col Updated format a12
col Level format a12

/* ******************************************************* */
/*                                                         */
/* Owner: R.Kupcunas                                       */
/* Date: 01-may-95                                         */
/*                                                         */
/* Query to show the Oracle Application modules installed  */
/*   and the versions of the said modules                  */
/*                                                         */
/* @C:\rkupcuna05_ora\Oracle\SQL\oraapp_modules_oracle_11.5.9.sql */
/*                                                         */
/* Note: usually all modules are installed but only a few  */
/*   are leveraged - some the existance of a module does   */
/*   not necessarly indicate that it is being used         */
/*                                                         */
/* Status:                                                 */
/*  I ..... installed                                      */
/*  N ..... not installed                                  */
/*  S ..... shared                                         */
/*                                                         */
/* ******************************************************* */
    
col segname 			heading 'Name'  		format a8
col segid 			heading 'ID'  		format a5

spool C:\rkupcuna05_ora\Oracle\SQL\oraapp_modules_oracle_11.5.9.rslt
select Distinct
	substr(C.application_short_name,1,8)				segname,
	A.application_id						"ID",
       substr(B.APPLICATION_NAME, 1, 30)        			"Desc",
      /* substr(A.PRODUCT_VERSION, 1, 8)     				"Version", */
       substr(A.patch_level, 1,12)					"Level",
       substr(to_char(A.CREATION_DATE, 'YYYY-MON-DD'), 1, 12)		"Installed",
       substr(to_char(A.LAST_UPDATE_DATE, 'YYYY-MON-DD'), 1, 12) 	"Updated",
       DECODE(status, 'I', 'Installed', 'S', 'Shared', 'Not Installed') "Status"
from 	applsys.fnd_product_installations  	A,
     	applsys.fnd_application_TL            	B,
     	applsys.fnd_application                    C
where 	A.APPLICATION_ID = B.APPLICATION_ID and
	C.APPLICATION_ID = B.APPLICATION_ID
order by 1, 2, 3;
         
set echo on;
set feedback on;
spool off;
set long 80;
commit;










