rem HEADER
rem   $Header: wfstdchk.sql 26.5 2003/03/11 12:10:13 hgandiko ship $
rem NAME
rem   wfstdchk.sql - WorkFlow Standard Data Model Check
rem USAGE
rem   sqlplus <usr>/<passwd>@db @wfstdchk.sql
rem DESCRIPTION
rem   check data model for all known problems
rem
rem   dbdrv: none
rem
set verify off
whenever sqlerror exit failure rollback;
whenever oserror exit failure rollback;
set pagesize 9999
set linesize 180
rem
col NAME         for a30 head "Name"
col ITEM_TYPE    for a8  head "ItemType"
col TYPE         for a8  head "Type"
col FORMAT       for a24 head "Format"
col LANG         for a4  head "Lang"
col STATUS       for a10 head "Status"
col CALLBACK     for a60 head "Callback"
col VALUE        for a30 head "Value"
col DISPLAY_NAME for a40 head "DisplayName"
col function     for a30 head "Function"
col Message      for a30 head "Message"
col CONFNAME     for a30 head "ConflictName"
col VER          for 9999 head "Version"
col CONFVER      for 9999 head "Version"
spool wfcheck.txt

alter session set NLS_DATE_FORMAT= "DD-MON-YYYY:HH24:MI:SS"
/
set head off
select 'Generated '||sysdate from dual;
set head on

rem
rem CORE
rem
prompt ------------------------------ Workflow Definition: Errors ------------------------------

prompt
prompt ------------------------------ Item Types ------------------------------

prompt
prompt -- WF_ITEM_TYPES: Invalid Names
prompt -- Name contains '#:/'
select NAME ITEM_TYPE
from   WF_ITEM_TYPES
where  upper(NAME) <> NAME
or     translate(NAME, ':/', '###') like '%#%'
order by 1
/

prompt
prompt -- WF_ITEM_TYPES: Invalid Callback/Selector Function
prompt --   Incorrect prototype
select NAME ITEM_TYPE, WF_SELECTOR CALLBACK
from   WF_ITEM_TYPES
where  WF_SELECTOR is not null
and    5 <>
    (select count(ARGUMENT_NAME) from ALL_ARGUMENTS
     where  PACKAGE_NAME = substr(WF_SELECTOR, 1, instr(WF_SELECTOR,'.')-1)
     and    OBJECT_NAME  = substr(WF_SELECTOR, instr(WF_SELECTOR, '.')+1))
order by 1
/

prompt
prompt -- WF_ITEN_TYPES: Missing TL data
select ITT.NAME ITEM_TYPE, LANG.CODE LANG
from   WF_ITEM_TYPES ITT, WF_LANGUAGES LANG
where  LANG.INSTALLED_FLAG = 'Y'
and    not exists
    (select null from WF_ITEM_TYPES_TL
     where  NAME = ITT.NAME and LANGUAGE = LANG.CODE)
order by 1
/

prompt ------------------------------ Item Attributes ------------------------------

prompt 
prompt -- WF_ITEM_ATTRIBUTES: Invalid Names
prompt -- NAME contains '#:/'
select ITEM_TYPE, NAME
from   WF_ITEM_ATTRIBUTES
where  upper(NAME) <> NAME
or     translate(NAME, '#:/', '###') like '%#%'
order by 1, 2
/

prompt
prompt -- WF_ITEM_ATTRIBUTES: Invalid Validation
prompt --   Type NOT in ('FORM', 'URL', 'VARCHAR2', 'NUMBER', 'DATE', 'LOOKUP', 'DOCUMENT', 'ROLE')
select ITEM_TYPE, NAME, TYPE, FORMAT
from   WF_ITEM_ATTRIBUTES
where  TYPE not in ('FORM', 'URL', 'VARCHAR2', 'NUMBER', 'DATE', 'LOOKUP', 'DOCUMENT', 'ROLE')
/

prompt
prompt -- WF_ITEM_ATTRIBUTES: Invalid Validation
prompt --   Type = 'LOOKUP' and LOOKUP_TYPE does not exist
select ITEM_TYPE, NAME, TYPE, FORMAT
from   WF_ITEM_ATTRIBUTES
where  TYPE = 'LOOKUP'
and    not exists ( select null from WF_LOOKUP_TYPES
                    where LOOKUP_TYPE = FORMAT )
order by 1, 2
/

prompt 
prompt -- WF_ITEM_ATTRIBUTES: Invalid Constant Default Value
prompt --   TYPE = LOOKUP and DEFAULT does not exist
select ITEM_TYPE, NAME, TYPE, TEXT_DEFAULT VALUE
from   WF_ITEM_ATTRIBUTES
where  TYPE = 'LOOKUP' and TEXT_DEFAULT is not null
and    not exists ( select null from WF_LOOKUPS
                    where  LOOKUP_TYPE = FORMAT and LOOKUP_CODE = TEXT_DEFAULT
		   )
/

prompt 
prompt -- WF_ITEM_ATTRIBUTES: Missing TL data
select ITA.ITEM_TYPE ITEM_TYPE, ITA.NAME NAME, LANG.CODE CODE
from   WF_ITEM_ATTRIBUTES ITA, WF_LANGUAGES LANG
where  LANG.INSTALLED_FLAG = 'Y'
and    not exists
    (select null from WF_ITEM_ATTRIBUTES_TL
     where  ITEM_TYPE = ITA.ITEM_TYPE 
     and    NAME = ITA.NAME 
     and    LANGUAGE = LANG.CODE)
order by 1, 2
/

prompt ------------------------------ Lookup Types ------------------------------

prompt
prompt -- WF_LOOKUP_TYPES: Invalid names
prompt -- NAME contains '#:/'
select ITEM_TYPE, LOOKUP_TYPE NAME
from   WF_LOOKUP_TYPES
where  upper(LOOKUP_TYPE) <> LOOKUP_TYPE
or     translate(LOOKUP_TYPE, '#:', '###') like '%#%'
order by 1, 2
/

prompt
prompt -- WF_LOOKUP_TYPES: Missing TL data
select LUT.ITEM_TYPE ITEM_TYPE, LUT.LOOKUP_TYPE NAME, LANG.CODE LANG
from   WF_LOOKUP_TYPES LUT, WF_LANGUAGES LANG
where  LANG.INSTALLED_FLAG = 'Y'
and    not exists
    (select null from WF_LOOKUP_TYPES_TL
     where  LOOKUP_TYPE = LUT.LOOKUP_TYPE and LANGUAGE = LANG.CODE)
order by 1, 2
/

prompt ------------------------------ Lookup Codes ------------------------------

prompt -- WF_LOOKUPS: Invalid Names
prompt -- NAME contains '#:/'
select LOOKUP_TYPE, LOOKUP_CODE
from   WF_LOOKUPS
where  upper(LOOKUP_CODE) <> LOOKUP_CODE
or     translate(LOOKUP_CODE, ':/', '::') like '%:%'
order by 1, 2
/

prompt 
prompt --  WF_LOOKUPS Missing TL data
select ITT.NAME ITEM_TYPE, LANG.CODE LANG
from   WF_ITEM_TYPES ITT, WF_LANGUAGES LANG
where  LANG.INSTALLED_FLAG = 'Y'
and    not exists
    (select null from WF_ITEM_TYPES_TL
     where  NAME = ITT.NAME and LANGUAGE = LANG.CODE)
order by 1, 2
/

prompt ------------------------------ Users ------------------------------

prompt
prompt -- WF_USERS: Invalid Names
prompt -- NAME contains '#:/'
rem    or translate(NAME, '#/', '###') like '%#%' Bug 2779747
select NAME
from   WF_USERS
where  upper(NAME) <> NAME
or     lengthb(NAME) > 30
order by 1
/

prompt
prompt -- WF_USERS: Invalid Compound Names
prompt --  NAME != <orig_system>:<orig_system_id>
select NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID
from   WF_USERS
where  NAME like '%:%' 
and    (ORIG_SYSTEM <> substr(NAME, 1, instr(NAME, ':')-1) or
	to_char(ORIG_SYSTEM_ID) <> substr(NAME, instr(NAME, ':')+1))
order by 1
/

prompt
prompt -- WF_USERS: Duplicate Names
select NAME,count(*) Num
from   WF_USERS U
group  by name
having count(*) >1
/

prompt
prompt -- WF_USERS: Missing Display Names
select NAME
from   WF_USERS
where  DISPLAY_NAME is null
order by 1
/

prompt
prompt -- WF_USERS: Invalid Notification Preference
prompt --   Preference not in  ('MAILTEXT', 'MAILHTML', 'SUMMARY', 'QUERY')
prompt --   Preference in   ('MAILTEXT', 'MAILHTML', 'SUMMARY') and EMAIL_ADDRESS is null
select NAME, NOTIFICATION_PREFERENCE
from   WF_USERS
where  NOTIFICATION_PREFERENCE not in 
	   ('MAILTEXT', 'MAILHTML', 'SUMMARY', 'QUERY')
or     NOTIFICATION_PREFERENCE in
	   ('MAILTEXT', 'MAILHTML', 'SUMMARY') and EMAIL_ADDRESS is null
order by 1
/

prompt
prompt -- WF_USERS: Invalid status
prompt --   STATUS not in ('ACTIVE', 'TMPLEAVE', 'EXTLEAVE', 'INACTIVE')
select NAME, STATUS
from   WF_USERS
where  STATUS not in ('ACTIVE', 'TMPLEAVE', 'EXTLEAVE', 'INACTIVE')
order by 1
/


prompt ------------------------------ Roles ------------------------------

prompt
prompt -- WF_ROLES: Misssing Roles
prompt --  Rows in WF_USERS that do not have a corresponding row in WF_ROLES
select name from wf_users 
MINUS 
select name from wf_roles
/

prompt
prompt -- WF_ROLES: Invalid names
prompt -- NAME contains '#/'
rem or     translate(NAME, '#/', '###') like '%#%' Bug 2779747
select NAME
from   WF_ROLES
where  upper(NAME) <> NAME
or     lengthb(NAME) > 30
order by 1
/

prompt
prompt -- WF_ROLES: Invalid compound names
prompt --  NAME <> <orig_system>:<orig_system_id>
select NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID
from   WF_ROLES
where  NAME like '%:%' 
and    (ORIG_SYSTEM <> substr(NAME, 1, instr(NAME, ':')-1) or
	to_char(ORIG_SYSTEM_ID) <> substr(NAME, instr(NAME, ':')+1))
order by 1
/

prompt
prompt -- WF_ROLES: Duplicate names
select NAME,count(*) Num
from   WF_ROLES U
group by name
having count(*) > 1
/

prompt
prompt WF_ROLES: Missing display names
select NAME
from   WF_ROLES
where  DISPLAY_NAME is null
order by 1
/

prompt
prompt -- WF_ROLES: Invalid Notification Preference
prompt --   Preference not in  ('MAILTEXT', 'MAILHTML', 'SUMMARY', 'QUERY')
prompt --   Preference in   ('MAILTEXT', 'MAILHTML', 'SUMMARY') and EMAIL_ADDRESS is null
select NAME, NOTIFICATION_PREFERENCE
from   WF_ROLES
where  NOTIFICATION_PREFERENCE not in 
	   ('MAILTEXT', 'MAILHTML', 'SUMMARY', 'QUERY')
or     NOTIFICATION_PREFERENCE in
	   ('MAILTEXT', 'MAILHTML', 'SUMMARY') and EMAIL_ADDRESS is null
order by 1
/

prompt ------------------------------ User Roles ------------------------------

prompt
prompt -- WF_USER_ROLES Invalid User / Role Foreign Key
select USER_NAME, ROLE_NAME
from   WF_USER_ROLES
where  not exists
    (select null from WF_USERS 
     where  USER_NAME = NAME
     and    USER_ORIG_SYSTEM = ORIG_SYSTEM
     and    USER_ORIG_SYSTEM_ID = ORIG_SYSTEM_ID)
or   not exists
    (select null from WF_ROLES
     where  ROLE_NAME = NAME
     and    ROLE_ORIG_SYSTEM = ORIG_SYSTEM
     and    ROLE_ORIG_SYSTEM_ID = ORIG_SYSTEM_ID)
/

prompt
prompt -- WF_USER_ROLES: Missing user role
prompt --    Every User must Participate in his/her own role
select NAME
from   WF_USERS U
where  not exists
    (select null from WF_USER_ROLES
     where  U.NAME = USER_NAME
     and    U.ORIG_SYSTEM = USER_ORIG_SYSTEM
     and    U.ORIG_SYSTEM_ID = USER_ORIG_SYSTEM_ID
     and    U.NAME = ROLE_NAME
     and    U.ORIG_SYSTEM = ROLE_ORIG_SYSTEM
     and    U.ORIG_SYSTEM_ID = ROLE_ORIG_SYSTEM_ID)
/


prompt ------------------------------ Message ------------------------------

prompt
prompt -- WF_MESSAGES: Invalid names 
select TYPE ITEM_TYPE, NAME 
from   WF_MESSAGES 
where  upper(NAME) <> NAME
or     translate(NAME, '#:/', '###') like '%#%'
order by 1, 2
/

prompt 
prompt -- WF_MESSAGES: Missing TL data
select MSG.TYPE ITEM_TYPE, MSG.NAME NAME, LANG.CODE LANG
from   WF_MESSAGES MSG, WF_LANGUAGES LANG
where  LANG.INSTALLED_FLAG = 'Y'
and    not exists
    (select null from WF_MESSAGES_TL
     where  TYPE = MSG.TYPE 
     and    NAME = MSG.NAME 
     and    LANGUAGE = LANG.CODE)
order by 1, 2
/

prompt ------------------------------ Message Attributes ------------------------------

prompt
prompt -- WF_MESSAGE_ATTRIBUTES: Invalid names
prompt -- NAME contains '#:/'
select MESSAGE_TYPE ITEM_TYPE, MESSAGE_NAME, NAME
from   WF_MESSAGE_ATTRIBUTES
where  upper(NAME) <> NAME
or     translate(NAME, '#:/', '###') like '%#%'
order by 1, 2
/

prompt
prompt -- WF_MESSAGE_ATTRIBUTES: Invalid sub type
select MESSAGE_TYPE ITEM_TYPE, MESSAGE_NAME, NAME, SUBTYPE
from   WF_MESSAGE_ATTRIBUTES
where  SUBTYPE not in ('SEND', 'RESPOND')
order by 1, 2
/

prompt
prompt -- WF_MESSAGE_ATTRIBUTES: Invalid value type
select MESSAGE_TYPE ITEM_TYPE, MESSAGE_NAME, NAME, VALUE_TYPE
from   WF_MESSAGE_ATTRIBUTES
where  VALUE_TYPE not in ('CONSTANT', 'ITEMATTR')
order by 1, 2
/

prompt
prompt -- WF_MESSAGE_ATTRIBUTES: Invalid validation
prompt --  TYPE not in ('FORM', 'URL', 'VARCHAR2', 'NUMBER', 'DATE', 'LOOKUP', 'DOCUMENT', 'ROLE') 
prompt --  OR
prompt --  TYPE in ( 'ITEMATTR')
select MESSAGE_TYPE ITEM_TYPE, MESSAGE_NAME, NAME, TYPE, FORMAT
from   WF_MESSAGE_ATTRIBUTES
where  TYPE not in ('FORM', 'URL', 'VARCHAR2', 'NUMBER', 'DATE', 'LOOKUP', 'DOCUMENT', 'ROLE') 
or     TYPE in ( 'ITEMATTR' )
order by 1,2,3,4
/

prompt
prompt -- WF_MESSAGE_ATTRIBUTES: Invalid validation
prompt --   TYPE = 'LOOKUP' and LOOKUP_TYPE does not exist 
select MESSAGE_TYPE ITEM_TYPE, MESSAGE_NAME, NAME, TYPE, FORMAT
from   WF_MESSAGE_ATTRIBUTES
WHERE  TYPE = 'LOOKUP' and not exists (select null from WF_LOOKUP_TYPES
            			       where LOOKUP_TYPE = FORMAT
				      )
order by 1, 2,3,4
/

prompt
prompt -- WF_MESSAGE_ATTRIBUTES: Invalid default value
prompt --  VALUE_TYPE = 'CONSTANT' and TYPE = 'LOOKUP' and Lookup_code does not exist
select MESSAGE_TYPE ITEM_TYPE, MESSAGE_NAME, NAME,VALUE_TYPE, TYPE, TEXT_DEFAULT VALUE
from   WF_MESSAGE_ATTRIBUTES MSA
where  VALUE_TYPE = 'CONSTANT' and TYPE = 'LOOKUP' and 
        TEXT_DEFAULT is not null and not exists (select null from WF_LOOKUPS
          					 where  LOOKUP_TYPE = MSA.FORMAT 
						 and LOOKUP_CODE = MSA.TEXT_DEFAULT)
order by 1,2,3,4
/


prompt
prompt -- WF_MESSAGE_ATTRIBUTES: Invalid default value
prompt --   Message Attribute References an Item Attribute that does not exist
prompt --   or is not of the same type
select MESSAGE_TYPE ITEM_TYPE, MESSAGE_NAME, NAME,VALUE_TYPE, TYPE, TEXT_DEFAULT VALUE
from   WF_MESSAGE_ATTRIBUTES MSA
where  VALUE_TYPE = 'ITEMATTR' 
and    TYPE in ('DATE', 'NUMBER','URL','DOCUMENT','ROLE') 
and    TEXT_DEFAULT is not null 
and not exists (select null from WF_ITEM_ATTRIBUTES
    	        where  ITEM_TYPE = MSA.MESSAGE_TYPE
	  	and    NAME = MSA.TEXT_DEFAULT
	  	and    TYPE = MSA.TYPE)
order by 1, 2
/

prompt
prompt -- WF_MESSAGE_ATTRIBUTES: Invalid default value for Text Attr Types
prompt --   Message Attribute References an Item Attribute that does not exist
rem Text attributes can take a default value of any item attribute type
select MESSAGE_TYPE ITEM_TYPE, MESSAGE_NAME, NAME,VALUE_TYPE, TYPE, TEXT_DEFAULT VALUE
from   WF_MESSAGE_ATTRIBUTES MSA
where  VALUE_TYPE = 'ITEMATTR' 
and    TYPE = 'VARCHAR2'
and    TEXT_DEFAULT is not null 
and not exists (select null from WF_ITEM_ATTRIBUTES
    	        where  ITEM_TYPE = MSA.MESSAGE_TYPE
	  	and    NAME = MSA.TEXT_DEFAULT)
order by 1, 2
/

prompt
prompt -- WF_MESSAGES: Missing TL data

select MSA.MESSAGE_TYPE ITEM_TYPE, MSA.MESSAGE_NAME,
       MSA.NAME NAME, LANG.CODE CODE
from   WF_MESSAGE_ATTRIBUTES MSA, WF_LANGUAGES LANG
where  LANG.INSTALLED_FLAG = 'Y'
and    not exists
    (select null from WF_MESSAGE_ATTRIBUTES_TL
     where  MESSAGE_TYPE = MSA.MESSAGE_TYPE 
     and    MESSAGE_NAME = MSA.MESSAGE_NAME
     and    NAME = MSA.NAME 
     and    LANGUAGE = LANG.CODE)
order by 1, 2
/

prompt ------------------------------ Activities ------------------------------

prompt
prompt -- WF_ACTIVITIES: Invalid Names
prompt -- NAME contains '#:/'
select ITEM_TYPE, NAME
from   WF_ACTIVITIES
where  upper(NAME) <> NAME
and    end_date is null
or     translate(NAME, '#:/', '###') like '%#%'
order by 1,2
/

prompt
prompt -- WF_ACTIVITIES: Missing TL data
select A.ITEM_TYPE,A.NAME ACTIVITY, LANG.CODE LANG
from   WF_ACTIVITIES A, WF_LANGUAGES LANG
where  LANG.INSTALLED_FLAG = 'Y'
and    not exists
    (select null from WF_ACTIVITIES_TL
     where  NAME = A.NAME and LANGUAGE = LANG.CODE)
order by 1,2
/

prompt
prompt -- WF_ACTIVITIES: Invalid Validation - 
prompt --   type not in ( 'FUNCTION','NOTICE','PROCESS','FOLDER')
prompt --   OR
prompt --   ( type = 'FUNCTION' and function is null )
prompt --   OR
prompt --   ( type = 'NOTICE' and message is null )
select item_type,name Activity,type,function,message,result_type
from   wf_activities
where  end_date is null
and    ( type not in ( 'FUNCTION','NOTICE','PROCESS','FOLDER')
       or( type = 'FUNCTION' and function is null )
       or( type = 'NOTICE' and message is null )
       )
/

prompt
prompt -- WF_ACTIVITIES: Invalid Function
prompt --   Incorrect prototype
select ITEM_TYPE, NAME, FUNCTION 
from   WF_ACTIVITIES
where  end_date is null
and    FUNCTION is not null
and    5 <>
    (select count(ARGUMENT_NAME) from ALL_ARGUMENTS
     where  PACKAGE_NAME = substr(FUNCTION, 1, instr(FUNCTION,'.')-1)
     and    OBJECT_NAME  = substr(FUNCTION, instr(FUNCTION, '.')+1))
order by 1, 2
/

prompt
prompt -- WF_ACTIVITIES: Invalid Validation
prompt --  Result Type does not exist in WF_LOOKUP_TYPES
select item_type,name,type,function,message,result_type
from   wf_activities
where  end_date is null
and    ( result_type <> '*' and not exists ( select null from wf_lookup_types where lookup_type = result_type ) )
order by 1,2,3
/

prompt
prompt -- WF_ACTIVITIES_TL: Display Name Conflicts
prompt -- Activities of an item type within the same active date range that
prompt -- have identical display names.

select OLDTL.DISPLAY_NAME, OLDTL.LANGUAGE,
       NEW.ITEM_TYPE, NEW.NAME NAME, NEW.VERSION VER,
       OLD.NAME CONFNAME, OLD.VERSION CONFVER
  from WF_ACTIVITIES NEW, WF_ACTIVITIES OLD,
       WF_ACTIVITIES_TL NEWTL, WF_ACTIVITIES_TL OLDTL
 where NEW.ITEM_TYPE = NEWTL.ITEM_TYPE
   and NEW.NAME = NEWTL.NAME
   and NEW.VERSION = NEWTL.VERSION
   and OLD.ITEM_TYPE = OLDTL.ITEM_TYPE
   and OLD.NAME = OLDTL.NAME
   and OLD.VERSION = OLDTL.VERSION
   and NEW.BEGIN_DATE < nvl(OLD.END_DATE, NEW.BEGIN_DATE+1)
   and nvl(NEW.END_DATE, OLD.BEGIN_DATE+1) > OLD.BEGIN_DATE
   and OLDTL.DISPLAY_NAME = NEWTL.DISPLAY_NAME
   and OLD.ITEM_TYPE = NEW.ITEM_TYPE
   and OLDTL.LANGUAGE = NEWTL.LANGUAGE
   and OLDTL.ROWID <> NEWTL.ROWID
 order by 1,2,3,4,5
/

prompt ------------------------------ Activity Attributes ------------------------------

prompt -- WF_ACTIVITY_ATTRIBUTES: Invalid Names
prompt -- NAME contains '#:/'
select Activity_ITEM_TYPE, activity_name 
from   WF_ACTIVITY_ATTRIBUTES
where  (upper(NAME) <> NAME
or     translate(NAME, '#:/', '###') like '%#%')
and    (NAME <> '#PRIORITY' and NAME <> '#TIMEOUT')
order by 1, 2
/
prompt -- WF_ACTIVITIE_ATTRIBUTES: Invalid Validation
prompt --  TYPE not in ('FORM', 'URL', 'VARCHAR2', 'NUMBER','DATE', 'LOOKUP', 'DOCUMENT', 'ROLE','ITEMATTR')
select ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, NAME, TYPE, FORMAT
from   WF_ACTIVITY_ATTRIBUTES
where  TYPE not in ('FORM', 'URL', 'VARCHAR2', 'NUMBER','DATE', 'LOOKUP', 'DOCUMENT', 'ROLE','ITEMATTR')
/

prompt -- WF_ACTIVITIE_ATTRIBUTES: Invalid Validation
prompt --  TYPE = LOOKUP and Lookup Type does not exist
select ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, NAME, TYPE, FORMAT LOOKUP_TYPE
from   WF_ACTIVITY_ATTRIBUTES
where  TYPE = 'LOOKUP'
and    not exists ( select null from WF_LOOKUP_TYPES
                     where LOOKUP_TYPE = FORMAT )
order by 1, 2
/

prompt
prompt -- WF_ACTIVITY_ATTRIBUTES: Invalid constant default value
prompt --   TYPE = 'LOOKUP and Lookup code does not exist
select ACTIVITY_ITEM_TYPE, ACTIVITY_NAME,NAME, TYPE, TEXT_DEFAULT VALUE
from   WF_ACTIVITY_ATTRIBUTES
where  TYPE = 'LOOKUP' and TEXT_DEFAULT is not null
and    not exists (select null from WF_LOOKUPS
                     where  LOOKUP_TYPE = FORMAT and LOOKUP_CODE = TEXT_DEFAULT)
/

prompt
prompt -- WF_ACTIVIY_ATTRIBUTES:Missing TL data
select ITA.activity_ITEM_TYPE,ita.activity_name, ITA.NAME NAME, LANG.CODE CODE
from   WF_ACTIVITY_ATTRIBUTES ITA, WF_LANGUAGES LANG
where  LANG.INSTALLED_FLAG = 'Y'
and    not exists
    (select null from WF_ACTIVITY_ATTRIBUTES_TL
     where  activity_ITEM_TYPE = ITA.activity_ITEM_TYPE
     and    activity_name      =ita.activity_name 
     and    NAME = ITA.NAME 
     and    LANGUAGE = LANG.CODE)
order by 1, 2
/
spool off
exit