-- This script finds projects that contain an object
set lines 255

select a.projectname, b.PROJECTDESCR,
decode(a.objecttype,0,'Records',
	decode(a.objecttype,1,'Indexes',
	decode(a.objecttype,2,'Fields',
	decode(a.objecttype,3,'Field Format',
	decode(a.objecttype,4,'Translate',
	decode(a.objecttype,5,'Page',
	decode(a.objecttype,6,'Menu',
	decode(a.objecttype,7,'Component',
	decode(a.objecttype,8,'PeopleCode',
	decode(a.objecttype,9,'Menu PeopleCode',
	decode(a.objecttype,10,'Query',
	decode(a.objecttype,11,'Tree Structures',
	decode(a.objecttype,12,'Tree',
	decode(a.objecttype,13,'Access Group',
	decode(a.objecttype,14,'Color',
	decode(a.objecttype,15,'Style',
	decode(a.objecttype,16,'Business Process Map',
	decode(a.objecttype,17,'Business Process',
	decode(a.objecttype,18,'Activity',
	decode(a.objecttype,19,'Role',
	decode(a.objecttype,20,'Process Definition',
	decode(a.objecttype,21,'Process Server',
	decode(a.objecttype,22,'Process Type',
	decode(a.objecttype,23,'Process Job',
	decode(a.objecttype,24,'Process Recurrence',
	decode(a.objecttype,25,'Message',
	decode(a.objecttype,26,'Dimension',
	decode(a.objecttype,27,'Analysis Model',
	decode(a.objecttype,28,'Cube Template', 
	decode(a.objecttype,53,'Permission List',
	decode(a.objecttype,54,'Portal Registry Definition',
	decode(a.objecttype,55,'Portal Registry Structure',
	a.objecttype)))))))))))))))))))))))))))))))) OBJECTTYPE,
RTRIM(a.objectvalue1 || '.' || 
	DECODE(a.OBJECTVALUE2,' ','',a.OBJECTVALUE2) || '.' || 
	DECODE(a.OBJECTVALUE3,' ','',a.OBJECTVALUE3) || '.' ||
	DECODE(a.OBJECTVALUE4,' ','',a.OBJECTVALUE4) || '.','.') OBJECT
from psprojectitem A, psprojectdefn b
where a.projectname = b.projectname and 
	(upper(a.objectvalue1) like upper('&1%') or 
	 upper(a.objectvalue2) like upper('&1%') or 
	 upper(a.objectvalue3) like upper('&1%') or 
	 upper(a.objectvalue4) like upper('&1%') or
	 upper(a.projectname) like upper('&1%'))
/