rem HEADER
rem   $Header: wfdirchk.sql 26.3 2003/02/19 01:01:44 rwunderl ship $
rem NAME
rem   wfdirchk.sql - WorkFlow Directoy Servoices Data Model Check
rem USAGE
rem   sqlplus <usr>/<passwd>@db @wfdirchk.sql
rem DESCRIPTION
rem   check directory servives data model for all known problems
rem dbdrv: none
SET VERIFY OFF
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE ROLLBACK;
set pages 66 lines 132

col user_name for a30 Head "User Name"
col role_name for a30 Head "Role Name"
col num for 9999999 Head Count
col name for a30

col display_name format a30
col notification_preference format a30
col ORIG_SYSTEM_ID format 999999999 heading "Orig S-ID"


prompt ------------------------- Roles (Includes users) ------------------------

prompt
prompt -- WF_ROLES: Invalid names
select NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID
from   WF_LOCAL_ROLES
where  translate(NAME, '#/', '###') like '%#%'
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_LOCAL_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: Duplicated Roles
select NAME, DISPLAY_NAME, ORIG_SYSTEM, ORIG_SYSTEM_ID
from   WF_LOCAL_ROLES r1
where  1 < ( select count(*) from wf_local_roles r2 where r1.name = r2.name
             and r1.orig_system = r2.orig_system 
             and r1.orig_system_id = r2.orig_system_id)
order by name
/

prompt
prompt -- WF_ROLES: Multiple WF ROLES linked to same row in original system
REM don't select duplicates as these are printed above
select orig_system,orig_system_id,name,display_name
from   wf_local_roles wfr1
where  (wfr1.orig_system <> 'WF_LOCAL_ROLES'
and     wfr1.orig_system_id <> 0)
and exists ( select 'row found' from wf_local_roles wfr2
	     where  (wfr2.orig_system <> 'WF_LOCAL_ROLES'
        and    wfr2.orig_system_id <> 0)
        and   wfr2.orig_system_id = wfr1.orig_system_id
	     and    wfr2.orig_system    = wfr1.orig_system
             and    wfr2.name          <> wfr1.name)
order by orig_system,orig_system_id
/

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

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


col USER_ORIG_SYSTEM    format a10 heading "User Sys"
col USER_ORIG_SYSTEM_ID format 99999999999  heading "U.System ID"
col ROLE_ORIG_SYSTEM    format a10 heading "Role Sys"
col ROLE_ORIG_SYSTEM_ID format 99999999999 heading "R.System ID"


prompt
prompt -- WF_USER_ROLES Invalid (User / Role) Foreign Key
select USER_NAME,user_orig_system,user_orig_system_id, ROLE_NAME,role_orig_system,role_orig_system_id
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)
order by user_name,role_name
/

prompt
prompt -- WF_USER_ROLES: Missing user role
prompt --    Every User must Participate in their 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)
/

col display_name format a30
prompt
prompt -- WF_USER_ROLES - Duplicate Rows
prompt
REM don't join to wf_user or wf_roles  incase of duplicates in join 
REM which would make the results of the select confusing.
REM don't select user_name=role_name as this will pick up duplicate users 
REM listed above
select user_name,role_name, user_orig_system, user_orig_system_id, 
       role_orig_system, role_orig_system_id
from   wf_user_roles 
where  user_name <> role_name
and    user_orig_system <> role_orig_system
and    user_orig_system_id <> role_orig_system_id
group by user_name,role_name, user_orig_system, role_orig_system,
         user_orig_system_id, role_orig_system_id
having count(*) > 1
/

prompt
prompt --Number of roles that qualify for purging (Make sure you run purge
prompt --regularly!
prompt
select count(*)
from wf_local_roles
where expiration_date <= sysdate
/

prompt
prompt --Number of userroles that qualify for purging (Make sure you run purge
prompt --regularly!
prompt
select count(*)
from wf_local_user_roles
where expiration_date <= sysdate
/
exit;