rem HEADER
rem   $Header: wfprotck.sql 26.1 2001/11/05 20:12:06 ctilley ship $
rem NAME
rem   wfprotck.sql - WorkFlow PROTection level CK
rem USAGE
rem   @wfprotck
rem DESCRIPTION
rem   List Item_Type, Lookup_Type, Message and Activity protection level
rem   problems.
rem NOTE
rem   Please use wfprot to fix the problem

rem   dbdrv: none

prompt For product teams except WF and FND,
prompt ... CUSTOM_LEVEL should be 0 or 20
prompt ... PROTECT_LEVEL should be 20 or 1000
prompt

prompt -- checking item_type
select NAME, CUSTOM_LEVEL, PROTECT_LEVEL
  from WF_ITEM_TYPES
 where NAME not like 'WF%'
   and NAME not like 'FND%'
   and (PROTECT_LEVEL not in (20, 1000)
     or CUSTOM_LEVEL  not in (0, 20))
 order by NAME;

prompt -- checking lookup_type
select ITEM_TYPE, LOOKUP_TYPE, CUSTOM_LEVEL, PROTECT_LEVEL
  from WF_LOOKUP_TYPES_TL
 where LOOKUP_TYPE not like 'WF%'
   and LOOKUP_TYPE not like 'FND%'
   and (PROTECT_LEVEL not in (20, 1000)
     or CUSTOM_LEVEL  not in (0, 20))
 order by ITEM_TYPE;

prompt -- checking item attribute
select ITEM_TYPE, NAME, CUSTOM_LEVEL, PROTECT_LEVEL
  from WF_ITEM_ATTRIBUTES
 where ITEM_TYPE not like 'WF%'
   and ITEM_TYPE not like 'FND%'
   and (PROTECT_LEVEL not in (20,1000)
    or  CUSTOM_LEVEL  not in (0,20))
 order by ITEM_TYPE;

prompt -- checking message
select TYPE, NAME, CUSTOM_LEVEL, PROTECT_LEVEL
  from WF_MESSAGES
 where TYPE not like 'WF%'
   and TYPE not like 'FND%'
   and (PROTECT_LEVEL not in (20, 1000)
     or CUSTOM_LEVEL  not in (0, 20))
 order by TYPE;

prompt -- checking activity
select ITEM_TYPE, NAME, CUSTOM_LEVEL, PROTECT_LEVEL
  from WF_ACTIVITIES
 where ITEM_TYPE not like 'WF%'
   and ITEM_TYPE not like 'FND%'
   and (PROTECT_LEVEL not in (20, 1000)
     or CUSTOM_LEVEL  not in (0, 20))
   and END_DATE is null
 order by ITEM_TYPE;

prompt -- all problem item_type

(
select NAME
  from WF_ITEM_TYPES
 where NAME not like 'WF%'
   and NAME not like 'FND%'
   and (PROTECT_LEVEL not in (20, 1000)
     or CUSTOM_LEVEL  not in (0, 20))
)
union
(
select ITEM_TYPE
  from WF_LOOKUP_TYPES_TL
 where LOOKUP_TYPE not like 'WF%'
   and LOOKUP_TYPE not like 'FND%'
   and (PROTECT_LEVEL not in (20, 1000)
     or CUSTOM_LEVEL  not in (0, 20))
)
union
(
select TYPE
  from WF_MESSAGES
 where TYPE not like 'WF%'
   and TYPE not like 'FND%'
   and (PROTECT_LEVEL not in (20, 1000)
     or CUSTOM_LEVEL  not in (0, 20))
)
union
(
select ITEM_TYPE
  from WF_ACTIVITIES
 where ITEM_TYPE not like 'WF%'
   and ITEM_TYPE not like 'FND%'
   and (PROTECT_LEVEL not in (20, 1000)
     or CUSTOM_LEVEL  not in (0, 20))
   and END_DATE is null
);