Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\owb\wf\sql\wfdircsv.sql

REM $Header: wfdircsv.sql 26.9 2005/03/03 21:45:37 rwunderl ship $
REM +======================================================================+
REM | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM
REM NAME
REM     wfdirv.sql  -  WorkFlow DIRectory service (CuStom Views)
REM NOTES
REM
REM =======================================================================

set verify off
define hdr = "$Header: wfdircsv.sql 26.9 2005/03/03 21:45:37 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 */ 
        WR.NAME,
        NVL(WRT.DISPLAY_NAME, WR.DISPLAY_NAME),
        NVL(WRT.DESCRIPTION, WR.DESCRIPTION),
        NVL(wf_pref.get_pref(WR.NAME,'MAILTYPE'),WR.NOTIFICATION_PREFERENCE),
        NVL(wf_pref.get_pref(WR.NAME,'LANGUAGE'),WR.LANGUAGE),
        NVL(wf_pref.get_pref(WR.NAME,'TERRITORY'),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.OWNER_TAG,
        WR.PARTITION_ID
from    WF_LOCAL_ROLES WR , WF_LOCAL_ROLES_TL wrt
where   WR.USER_FLAG = 'Y'
and   nvl(WR.EXPIRATION_DATE, sysdate+1) > sysdate
and   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_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 */
    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.PARENT_ORIG_SYSTEM,
    WR.PARENT_ORIG_SYSTEM_ID,
    WR.START_DATE,
    WR.STATUS,
    WR.EXPIRATION_DATE,
    WR.OWNER_TAG,
    WR.PARTITION_ID
   from  WF_LOCAL_ROLES wr, WF_LOCAL_ROLES_TL wrt
   WHERE nvl(WR.EXPIRATION_DATE, sysdate+1) > sysdate
   and   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_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,
                                             ROLE_NAME,
                                             USER_ORIG_SYSTEM,
                                             USER_ORIG_SYSTEM_ID,
                                             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 */
  WUR.USER_NAME,
  WUR.ROLE_NAME,
  WUR.USER_ORIG_SYSTEM,
  WUR.USER_ORIG_SYSTEM_ID,
  WUR.ROLE_ORIG_SYSTEM,
  WUR.ROLE_ORIG_SYSTEM_ID,
  WUR.START_DATE,
  WUR.EXPIRATION_DATE,
  WUR.ASSIGNMENT_TYPE,
  WUR.PARENT_ORIG_SYSTEM,
  WUR.PARENT_ORIG_SYSTEM_ID,
  WUR.PARTITION_ID
  from wf_local_user_roles wur
  where (((wur.start_date is NULL) or (trunc(sysdate) >= wur.start_date))
  and    ((wur.expiration_date is NULL) or 
          (trunc(sysdate) < wur.expiration_date)))
/

/* 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;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de