REM $Header: wfdirouv.sql 26.9 2005/03/03 21:47:28 rwunderl ship $ REM +======================================================================+ REM | Copyright (c) 1998 Oracle Corporation Redwood Shores, California, USA| REM | All rights reserved. | REM +======================================================================+ REM REM NAME REM wfdirv.sql - WorkFlow DIRectory (Native Oracle Users and Roles Views /* ** NOTE ** requires EXPLICIT select from SYS on dba_user,dba_roles,dba_role_privs ** Having dba role is not sufficient. ** table or view does not exist. ** even though you may select and describe it. Error should really read: ** Cannot create a stored object using privileges from a role ** ** NOTE ** make sure the new views maintain unique names ** run admin/sql/wfdirchk.sql */ REM ======================================================================= set verify off define hdr = "$Header: wfdirouv.sql 26.9 2005/03/03 21:47:28 rwunderl ship $" WHENEVER SQLERROR EXIT FAILURE ROLLBACK; /* ** WF_LANGUAGES */ create or replace force view wf_languages ( CODE, DISPLAY_NAME, NLS_LANGUAGE, NLS_TERRITORY, NLS_CODESET, INSTALLED_FLAG ) as select /* &&hdr */ CODE, DISPLAY_NAME, NLS_LANGUAGE, NLS_TERRITORY, NLS_CODESET, INSTALLED_FLAG from WF_LOCAL_LANGUAGES; /* WF_USERS */ REM /* In case of standalone isMLSEnabled returns false */ REM /* Hence the _TL table is never populated . The join on */ REM /* _TL table is as good as querying only from */ REM /* wf_local_roles but keeping the same for scalability */ REM /* any day we support MLS in standalone */ create or replace force view wf_users ( NAME, DISPLAY_NAME, DESCRIPTION, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, START_DATE, STATUS, EXPIRATION_DATE, OWNER_TAG, PARTITION_ID ) as (select /* &&hdr */ username, username, username, NVL(wf_pref.get_pref(d.username,'MAILTYPE'),'MAILHTML'), NVL(wf_pref.get_pref(d.username,'LANGUAGE'),l.nls_language), NVL(wf_pref.get_pref(d.username,'TERRITORY'),l.nls_territory), username, '', 'ORACLE', d.user_id, null, to_number(null), sysdate, 'ACTIVE', to_date(NULL), null, 0 from dba_users d, wf_languages l where l.code=userenv('LANG') union all select name, display_name, description, notification_preference, language, territory, email_address, fax, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, start_date, status, expiration_date, owner_tag, partition_id from WF_LOCAL_ROLES where USER_FLAG = 'Y') / /* WF_ROLES */ REM /* In case of standalone isMLSEnabled returns false */ REM /* Hence the _TL table is never populated . The join on */ REM /* _TL table is as good as querying only from */ REM /* wf_local_roles but keeping the same for scalability */ REM /* any day we support MLS in standalone */ create or replace force view wf_roles ( NAME, DISPLAY_NAME, DESCRIPTION, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, START_DATE, STATUS, EXPIRATION_DATE, OWNER_TAG, PARTITION_ID ) as (select /* &&hdr */ username, username, username, NVL(wf_pref.get_pref(d.username,'MAILTYPE'),'MAILHTML'), NVL(wf_pref.get_pref(d.username,'LANGUAGE'),l.nls_language), NVL(wf_pref.get_pref(d.username,'TERRITORY'),l.nls_territory), username, '', 'ORACLE', d.user_id, null, to_number(null), sysdate, 'ACTIVE', to_date(NULL), null, 0 from dba_users d, wf_languages l where l.code=userenv('LANG') union all select NAME, DISPLAY_NAME, DESCRIPTION, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, START_DATE, STATUS, EXPIRATION_DATE, OWNER_TAG, PARTITION_ID from WF_LOCAL_ROLES union all select role, role, role, 'MAILHTML', l.nls_language, l.nls_territory, '', '', 'ORACLE', 0, null, to_number(null), sysdate, 'ACTIVE', to_date(null) , null, 0 from dba_roles r, wf_languages l where l.code = userenv('LANG') and role like 'WF%') / /* WF_USER_ROLES */ REM /* Additional clauses to check that the wf_user_roles */ REM /* have valid user-role relation based on the active */ REM /* date (sysdate) being between the start and end dates */ create or replace force view WF_USER_ROLES ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, ASSIGNMENT_TYPE, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, PARTITION_ID) as select /* &&hdr */ username, 'ORACLE', d.user_id, username, 'ORACLE', d.user_id, sysdate, to_date(null), null, null, to_number(null), 0 from dba_users d, wf_languages l where l.code=userenv('LANG') union all select USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, ASSIGNMENT_TYPE, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, PARTITION_ID from WF_LOCAL_USER_ROLES union all select u.name, u.orig_system, u.orig_system_id, granted_role, 'ORACLE', 0, sysdate, to_date(null), null, null, to_number(null), 0 from dba_role_privs D, wf_users U where d.grantee = u.name and d.granted_role like 'WF%'; /* WF_DIRECTORY_PARTITIONS_VL */ REM/* REM ** Bug 2868207 REM ** View WF_DIRECTORY_PARTITIONS_VL REM */ create or replace force view WF_DIRECTORY_PARTITIONS_VL ( ROW_ID, PARTITION_ID, ORIG_SYSTEM, DISPLAY_NAME ) as select /* &&hdr */ B.ROWID ROW_ID, B.PARTITION_ID, B.ORIG_SYSTEM, T.DISPLAY_NAME from WF_DIRECTORY_PARTITIONS_TL T, WF_DIRECTORY_PARTITIONS B where B.ORIG_SYSTEM = T.ORIG_SYSTEM and T.LANGUAGE = userenv('LANG') / /* WF_USER_LOV_VL */ create or replace force view wf_user_lov_vl ( NAME, DISPLAY_NAME, DESCRIPTION, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, START_DATE, STATUS, EXPIRATION_DATE, PARTITION_ID ) as ((select WR.NAME, WRT.DISPLAY_NAME, WRT.DESCRIPTION, WR.NOTIFICATION_PREFERENCE, WR.LANGUAGE, WR.TERRITORY, WR.EMAIL_ADDRESS, WR.FAX, WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID, WR.PARENT_ORIG_SYSTEM, WR.PARENT_ORIG_SYSTEM_ID, WR.START_DATE, WR.STATUS, WR.EXPIRATION_DATE, WR.PARTITION_ID from wf_local_roles WR, wf_local_roles_tl WRT WHERE WR.USER_FLAG = 'Y' /*Adhoc Users*/ and nvl(WR.EXPIRATION_DATE, sysdate+1) > sysdate and wr.name = wrt.name and wr.orig_system = wrt.orig_system and wr.orig_system_id = wrt.orig_system_id and wr.partition_id = wrt.partition_id and wrt.language = userenv('LANG')) UNION ALL (select WR.NAME, WR.DISPLAY_NAME, WR.DESCRIPTION, WR.NOTIFICATION_PREFERENCE, WR.LANGUAGE, WR.TERRITORY, WR.EMAIL_ADDRESS, WR.FAX, WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID, WR.PARENT_ORIG_SYSTEM, WR.PARENT_ORIG_SYSTEM_ID, WR.START_DATE, WR.STATUS, WR.EXPIRATION_DATE, WR.PARTITION_ID from wf_local_roles WR WHERE WR.USER_FLAG = 'Y' /*Adhoc Users */ and nvl(WR.EXPIRATION_DATE, sysdate+1) > sysdate and NOT EXISTS (SELECT NULL FROM WF_LOCAL_ROLES_TL WRT WHERE wrt.language = userenv('LANG') AND wrt.name = wr.name AND wrt.orig_system = wr.orig_system AND wrt.orig_system_id = wr.orig_system_id ))) / /* WF_ROLE_LOV_VL */ create or replace force view wf_role_lov_vl ( NAME, DISPLAY_NAME, DESCRIPTION, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, ORIG_SYSTEM, ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, START_DATE, STATUS, EXPIRATION_DATE, PARTITION_ID ) as ((select WR.NAME, WRT.DISPLAY_NAME, WRT.DESCRIPTION, WR.NOTIFICATION_PREFERENCE, WR.LANGUAGE, WR.TERRITORY, WR.EMAIL_ADDRESS, WR.FAX, WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID, WR.PARENT_ORIG_SYSTEM, WR.PARENT_ORIG_SYSTEM_ID, WR.START_DATE, WR.STATUS, WR.EXPIRATION_DATE, WR.PARTITION_ID from wf_local_roles WR, wf_local_roles_tl WRT WHERE nvl(WR.EXPIRATION_DATE, sysdate+1) > sysdate and wr.name = wrt.name and wr.orig_system = wrt.orig_system and wr.orig_system_id = wrt.orig_system_id and wr.partition_id = wrt.partition_id and wrt.language = userenv('LANG')) UNION ALL (select WR.NAME, WR.DISPLAY_NAME, WR.DESCRIPTION, WR.NOTIFICATION_PREFERENCE, WR.LANGUAGE, WR.TERRITORY, WR.EMAIL_ADDRESS, WR.FAX, WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID, WR.PARENT_ORIG_SYSTEM, WR.PARENT_ORIG_SYSTEM_ID, WR.START_DATE, WR.STATUS, WR.EXPIRATION_DATE, WR.PARTITION_ID from wf_local_roles WR WHERE nvl(WR.EXPIRATION_DATE, sysdate+1) > sysdate and NOT EXISTS (SELECT NULL FROM WF_LOCAL_ROLES_TL WRT WHERE wrt.language = userenv('LANG') AND wrt.name = wr.name AND wrt.orig_system = wr.orig_system AND wrt.orig_system_id = wr.orig_system_id ))) / /* WF_ALL_ROLES_VL */ create or replace force view WF_ALL_ROLES_VL ( NAME, DISPLAY_NAME, DESCRIPTION, NOTIFICATION_PREFERENCE, LANGUAGE, TERRITORY, EMAIL_ADDRESS, FAX, ORIG_SYSTEM, ORIG_SYSTEM_ID, START_DATE, STATUS, EXPIRATION_DATE, OWNER_TAG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PARTITION_ID ) as SELECT WR.NAME, nvl(WRT.DISPLAY_NAME, WR.DISPLAY_NAME), nvl(WRT.DESCRIPTION, WR.DESCRIPTION), WR.NOTIFICATION_PREFERENCE, WR.LANGUAGE, WR.TERRITORY, WR.EMAIL_ADDRESS, WR.FAX, WR.ORIG_SYSTEM, WR.ORIG_SYSTEM_ID, WR.START_DATE, WR.STATUS, WR.EXPIRATION_DATE, WR.OWNER_TAG, WR.CREATED_BY, WR.CREATION_DATE, WR.LAST_UPDATED_BY, WR.LAST_UPDATE_DATE, WR.LAST_UPDATE_LOGIN, WR.PARTITION_ID from WF_LOCAL_ROLES wr, WF_LOCAL_ROLES_TL wrt WHERE wr.orig_system = wrt.orig_system (+) and wr.orig_system_id = wrt.orig_system_id (+) and wr.name = wrt.name (+) and wr.partition_id = wrt.partition_id (+) and wrt.language (+) = userenv('LANG') / /* WF_ALL_USER_ROLES */ create or replace force view wf_all_user_roles ( USER_NAME, ROLE_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, PARENT_ORIG_SYSTEM, PARENT_ORIG_SYSTEM_ID, ASSIGNMENT_TYPE, START_DATE, EXPIRATION_DATE, OWNER_TAG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PARTITION_ID ) as select USER_NAME, ROLE_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, ASSIGNMENT_TYPE, START_DATE, EXPIRATION_DATE, OWNER_TAG, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PARTITION_ID from wf_local_user_roles / /* WF_USER_ROLE_ASSIGNMENTS_V */ REM /* View is just for standalone datamodel */ REm /* to be consistent with apps not used */ create or replace force view wf_user_role_assignments_v ( USER_NAME, ROLE_NAME, ASSIGNING_ROLE, START_DATE, END_DATE, ASSIGNMENT_TYPE ) as select USER_NAME, ROLE_NAME, ASSIGNING_ROLE, START_DATE, END_DATE, decode(RELATIONSHIP_ID, -1, 'DIRECT', 'INHERITED') from WF_USER_ROLE_ASSIGNMENTS where ((START_DATE is NULL) or (trunc(sysdate) >= START_DATE)) and ((END_DATE is NULL) or (trunc(sysdate) < END_DATE)) / /* WF_ALL_USER_ROLE_ASSIGNMENTS */ create or replace force view wf_all_user_role_assignments ( USER_NAME, ROLE_NAME, ASSIGNING_ROLE, START_DATE, END_DATE, ASSIGNMENT_TYPE, CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN ) as select USER_NAME, ROLE_NAME, ASSIGNING_ROLE, START_DATE, END_DATE, decode(RELATIONSHIP_ID, -1, 'DIRECT', 'INHERITED'), CREATED_BY, CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN from WF_USER_ROLE_ASSIGNMENTS / commit; exit;