REM     ORACLE - Licence Compliance Group
REM              Licence Management Services
REM
REM     Change History
REM     Date:        Revision:       Author:          Description:
REM     27-DEC-2006  1.1             esiytiu.us		Added Distinct due to customer 
REM 									requests, removed To_Char on date comparison because this results
REM									to character comparisons and not Date comparisons, 
REM									moved patch date comparison as sub-query
REM     28-MAR-2011  1.2             lpopa		Added formatting for column 'object_type' and extented column 'object_name' to 30 
REM     03-AUG-2011  1.3             lpopa		Extented column 'owner' to 30 
REM     03-AUG-2011  1.3             lpopa		Added server info part


CLEAR SCREEN

PROMPT *******************************************************************************
PROMPT * THIS SCRIPT CHECKS FOR DDL CHANGES IN THE DATABASE                          *
PROMPT * LOOK FOR THE LOG FILE (apps_ddl.log) IN THE $ORACLE_HOME/BIN DIRECTORY      *
PROMPT * THIS QUERY RUNS ON ORACLE 8 AND HIGHER DATABASES                            *
PROMPT *                                                                             *
PROMPT * Connect as SYS to run this script                                           *
PROMPT *******************************************************************************
PROMPT

set pagesize 5000
set heading off
set time off
set timi off
set verify off

spool apps_ddl.log

COL EBS_version format a15
COL HOST_NAME     format a40 wrap
COL Instance_name format a15

prompt  
prompt
prompt ========= Apps DDL 1.3 ==========
prompt =================================
prompt ***E-Business Suite Version***

col C0 new_val EBS_version
SELECT release_name C0 FROM apps.fnd_product_groups;

prompt  
prompt
prompt =================================
prompt ***Instance name and Host name***

-- Get host_name and instance_name
col C1 new_val INSTANCE_NAME
col C2 new_val HOST_NAME
-- Oracle7
--SELECT name    C1 FROM v$database;
--SELECT min(machine) C2 FROM v$session WHERE type = 'BACKGROUND';
-- Oracle8 and higher
SELECT instance_name C1, host_name C2 FROM v$instance;

prompt  
prompt
prompt ==============
prompt ***Database Version

select BANNER FROM V$VERSION;

set heading on
set linesize 125
col owner format a30
col object_name format a30
col object_type format a20
col "DDL TIME" format a17
col "CREATE DATE" format a20

SELECT DISTINCT        
	db.owner,
        db.object_name,
        db.object_type,                 
        TO_CHAR(db.created,'DD-MON-YYYY HH24:MI') "CREATE DATE", 
        TO_CHAR(db.last_ddl_time,'DD-MON-YYYY HH24:MI')"DDL TIME"
FROM    
        dba_objects db                 
WHERE   
        db.created < last_ddl_time
AND     db.created NOT IN (Select DISTINCT p.creation_date from APPLSYS.AD_APPLIED_PATCHES p)
AND     db.GENERATED='N' 
AND     db.object_type in ( 'PACKAGE','PACKAGE BODY','PROCEDURE','TABLE',
                        'CLUSTER','VIEW','FUNCTION','DATABASE LINK','SEQUENCE',
                        'TABLE','TABLE PARTITION','TRIGGER','TYPE','TYPE BODY')
ORDER BY object_type;

set verify on
spool off

