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

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

REM +======================================================================+
REM | Copyright (c) 2000 Oracle Corporation Redwood Shores, California, USA|
REM |                       All rights reserved.                           |
REM +======================================================================+
REM
REM NAME
REM   wfdirhmv.sql  -  WorkFlow DIRectory (Human Resources) Materialized Views
REM ARGS
REM
REM NOTES
REM   we do not restrict wf tables by business group
REM   This allows work flow builders access users/roles
REM   in all business groupd ( review with HR team )
REM
REM MODIFICATION LOG:
REM   01/2002 JWSMITH BUG 2001012 - Changed comments to increase username,
REM           email_address, and roles to varchar2(320), role display_name
REM           to varchar2(360)
REM =======================================================================

SET VERIFY OFF
DEFINE hdr = "$Header: wfdirhmv.sql 26.1 2002/06/27 16:06:03 jwsmith ship $"

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

/* fnd_users linked to per_people_f */
/* Materialized View for PER */
create materialized view WF_PER_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
       USR.USER_NAME,
       PER.FULL_NAME,
       PER.EMAIL_ADDRESS,
       USR.FAX,
       PER.PERSON_ID,
       decode(usr.end_date,
              null, 'ACTIVE',
              'INACTIVE')     as STATUS
   from
       PER_PEOPLE_F PER,
       FND_USER USR
   where
       trunc(SYSDATE) between PER.EFFECTIVE_START_DATE
                          and PER.EFFECTIVE_END_DATE
   and PER.PERSON_ID       = USR.EMPLOYEE_ID
  );

/* fnd_users Not linked to per_people_f  */
/* Materialized View for FND_USR */
create materialized view WF_FND_USR_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
       USR.USER_NAME,
       USR.DESCRIPTION,
       USR.EMAIL_ADDRESS,
       USR.FAX,
       USR.USER_ID,
       decode(USR.END_DATE,
              null, 'ACTIVE',
              'INACTIVE')     as STATUS
   from  FND_USER USR
   where USR.EMPLOYEE_ID is null
  );

/* customer address contacts */
create materialized view WF_CUST_CONT_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
       'CUST_CONT'||':'||cont.contact_point_id as NAME,
       party.person_last_name,
       party.person_last_name||', '||party.person_first_name as DESCRIPTION,
       fndl.nls_language,
       fndt.nls_territory,
       cont.email_address,
       cont.contact_point_id,
       DECODE(cont.status, 'I','INACTIVE', 'ACTIVE') as STATUS
   from
       FND_LANGUAGES FNDL,
       FND_TERRITORIES FNDT ,
       HZ_LOCATIONS LOC,
       HZ_PARTY_SITES SITE ,
       HZ_PARTIES PARTY,
       HZ_CONTACT_POINTS CONT
 where cont.owner_table_name = 'HZ_PARTIES'
   and cont.owner_table_id = party.party_id
   and cont.contact_point_type = 'EMAIL'
   and cont.primary_flag = 'Y'
   and site.party_id = party.party_id
   and site.location_id = loc.location_id
   and site.identifying_address_flag = 'Y'
   and loc.country = fndt.territory_code(+)
   and fndt.nls_territory = fndl.nls_territory(+)
 );

/*
** WF_USERS
*/
create or replace force view WF_USERS
(
    NAME,                     /* varchar2(320)  not null */
    DISPLAY_NAME,             /* varchar2(360)  not null */
    DESCRIPTION,              /* varchar2(240)          */
    NOTIFICATION_PREFERENCE,  /* varchar2(8)   not null */
    LANGUAGE,                 /* varchar2(30)  not null */
    TERRITORY,                /* varchar2(30)  not null */
    EMAIL_ADDRESS,            /* varchar2(320)          */
    FAX,                      /* varchar2(240)          */
    ORIG_SYSTEM,              /* varchar2(8)   not null */
    ORIG_SYSTEM_ID,           /* number        not null */
    STATUS,                   /* varchar2(8)   not null */
    EXPIRATION_DATE           /* date                   */
)
as
/* fnd_users linked to per_people_f */
select  /* &&hdr */
    PER.USER_NAME,
    PER.FULL_NAME,
    PER.FULL_NAME,
    NVL(wf_pref.get_pref(PER.USER_NAME, 'MAILTYPE'), 'MAILHTML'),
    NVL(wf_pref.get_pref(PER.USER_NAME, 'LANGUAGE'), FNDL.NLS_LANGUAGE),
    NVL(wf_pref.get_pref(PER.USER_NAME, 'TERRITORY'), FNDL.NLS_TERRITORY),
    PER.EMAIL_ADDRESS,
    PER.FAX,
    'PER',
    PER.PERSON_ID,
    PER.STATUS,
    to_date(NULL)
from  WF_PER_MV PER,
      FND_LANGUAGES FNDL
where FNDL.INSTALLED_FLAG = 'B'
/* fnd_users not linked to per_people_f */
union
all
select USR.USER_NAME,
       USR.USER_NAME,
       USR.DESCRIPTION,
       NVL(wf_pref.get_pref(USR.USER_NAME,'MAILTYPE'),'MAILHTML'),
       NVL(wf_pref.get_pref(USR.USER_NAME, 'LANGUAGE'), FNDL.NLS_LANGUAGE),
       NVL(wf_pref.get_pref(USR.USER_NAME, 'TERRITORY'), FNDL.NLS_TERRITORY),
       USR.EMAIL_ADDRESS,
       USR.FAX,
       'FND_USR',
       USR.USER_ID,
       USR.STATUS,
       to_date(NULL)
from   FND_LANGUAGES FNDL,
       WF_FND_USR_MV USR
where  FNDL.INSTALLED_FLAG = 'B'
/* customer address contacts */
union
all
select NAME,
       PERSON_LAST_NAME,
       DESCRIPTION,
       'MAILTEXT',
       NLS_LANGUAGE,
       NLS_TERRITORY,
       EMAIL_ADDRESS,
       NULL,
       'CUST_CONT',
       CONTACT_POINT_ID,
       STATUS,
       to_date(NULL)
from   WF_CUST_CONT_MV
/* local users */
union
all
select  name,
        display_name,
        description,
        NVL(wf_pref.get_pref(name, 'MAILTYPE'), notification_preference),
        NVL(wf_pref.get_pref(name, 'LANGUAGE'), language),
        NVL(wf_pref.get_pref(name, 'TERRITORY'), territory),
        email_address,
        fax,
        'WF_LOCAL_USERS',
        0,
        status,
        expiration_date
from    wf_local_users;

/* Materialized Views for WF_ROLES */
create materialized view WF_POS_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
        'POS'||':'||POS.POSITION_ID  as NAME,
        POS.NAME                     as DISPLAY_NAME,
        FNDL.NLS_LANGUAGE,
        FNDT.NLS_TERRITORY,
        POS.POSITION_ID
   from FND_TERRITORIES FNDT,
        FND_LANGUAGES FNDL,
        HR_LOCATIONS HRL,
        PER_POSITIONS POS
  where POS.LOCATION_ID         = HRL.LOCATION_ID(+)
   and  HRL.COUNTRY             = FNDT.TERRITORY_CODE(+)
   and  FNDT.NLS_TERRITORY      = FNDL.NLS_TERRITORY(+)
  );

create materialized view WF_ENG_LIST_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
        'ENG_LIST'||':'||eeal.approval_list_id  as NAME,
        eeal.approval_list_name,
        eeal.description,
        eeal.approval_list_id
   from ENG_ECN_APPROVAL_LISTS eeal
  );

create materialized view WF_GBX_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
        NAME,
        DISPLAY_NAME,
        DESCRIPTION,
        GROUPBOX_ID
   from GHR_GROUPBOXES
  );

/*
** WF_ROLES
*/
create or replace force view WF_ROLES
(
    NAME,                     /* varchar2(320)  not null */
    DISPLAY_NAME,             /* varchar2(360)  not null */
    DESCRIPTION,              /* varchar2(240)          */
    NOTIFICATION_PREFERENCE,  /* varchar2(8)   not null */
    LANGUAGE,                 /* varchar2(30)  not null */
    TERRITORY,                /* varchar2(30)  not null */
    EMAIL_ADDRESS,            /* varchar2(320)          */
    FAX,                      /* varchar2(240)          */
    ORIG_SYSTEM,              /* varchar2(8)   not null */
    ORIG_SYSTEM_ID,           /* number        not null */
    STATUS,                   /* varchar2(8)   not null */
    EXPIRATION_DATE           /* date          not null */
)
as
select /* &&hdr */
      NAME,
      DISPLAY_NAME,
      DISPLAY_NAME,
      'QUERY',
      NLS_LANGUAGE,
      NLS_TERRITORY,
      NULL,
      NULL,
      'POS',
      POSITION_ID,
      'ACTIVE',
      to_date(NULL)
from  WF_POS_MV
union all
select /* &&hdr */
      EEAL.NAME,
      EEAL.APPROVAL_LIST_NAME,
      EEAL.DESCRIPTION,
      'QUERY',
      FNDL.NLS_LANGUAGE,
      FNDL.NLS_TERRITORY,
      NULL,
      NULL,
      'ENG_LIST',
      EEAL.APPROVAL_LIST_ID,
      'ACTIVE',
      to_date(NULL)
from  FND_LANGUAGES FNDL,
      WF_ENG_LIST_MV EEAL
where FNDL.INSTALLED_FLAG = 'B'
union all
select /* &&hdr */
        GBX.NAME,
        GBX.DISPLAY_NAME,
        GBX.DESCRIPTION,
        'QUERY',
        FNDL.NLS_LANGUAGE,
        FNDL.NLS_TERRITORY,
        NULL,
        NULL,
        'GBX',
        GBX.GROUPBOX_ID,
        'ACTIVE',
        to_date(NULL)
from    WF_GBX_MV GBX,
        FND_LANGUAGES FNDL
where   FNDL.INSTALLED_FLAG = 'B'
/* fnd_responsibility - should be always valid */
union all
select  'FND_RESP'||r.application_id||':'||r.responsibility_id,
        R.RESPONSIBILITY_NAME,
        R.DESCRIPTION,
        'QUERY',
        FNDL.NLS_LANGUAGE,
        FNDL.NLS_TERRITORY,
        null,
        null,
        'FND_RESP'||r.application_id,
        R.RESPONSIBILITY_ID,
        'ACTIVE',
        to_date(NULL)
from    FND_LANGUAGES FNDL,
        FND_RESPONSIBILITY_TL R
where   R.LANGUAGE = userenv('LANG')
and     FNDL.INSTALLED_FLAG = 'B'
union all
select /* &&hdr */
       NAME,
       DISPLAY_NAME,
       DESCRIPTION,
       NOTIFICATION_PREFERENCE,
       LANGUAGE,
       TERRITORY,
       EMAIL_ADDRESS,
       FAX,
       ORIG_SYSTEM,
       ORIG_SYSTEM_ID,
       STATUS,
       EXPIRATION_DATE
from   WF_USERS
union all
select  NAME,
        DISPLAY_NAME,
        DESCRIPTION,
        NOTIFICATION_PREFERENCE,
        LANGUAGE,
        TERRITORY,
        EMAIL_ADDRESS,
        FAX,
        'WF_LOCAL_ROLES',
        0,
        STATUS,
        EXPIRATION_DATE
from    WF_LOCAL_ROLES;

/* Materialized Views for WF_USER_ROLES */
create materialized view WF_PER_POS_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
       USR.USER_NAME,
       PER.PERSON_ID,
       'POS'||':'||POS.POSITION_ID as ROLE_NAME,
       POS.POSITION_ID
  from PER_ASSIGNMENTS_F ASS,
       PER_POSITIONS  POS,
       FND_USER USR,
       PER_PEOPLE_F PER
 where ASS.POSITION_ID = POS.POSITION_ID
   and ASS.PERSON_ID   = USR.EMPLOYEE_ID
   and ASS.PERSON_ID   = PER.PERSON_ID
   and trunc(sysdate) between PER.EFFECTIVE_START_DATE
                          and PER.EFFECTIVE_END_DATE
   and trunc(sysdate) between ASS.EFFECTIVE_START_DATE
                          and ASS.EFFECTIVE_END_DATE
   and trunc(sysdate) between USR.START_DATE
                          and nvl(USR.END_DATE, sysdate+1)
   and PER.EMPLOYEE_NUMBER is not null
   and ASS.ASSIGNMENT_TYPE = 'E'
   );

/* Engineering approval roles */
create materialized view WF_PER_ENG_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
        USR.USER_NAME,
        PER.PERSON_ID,
        'ENG_LIST'||':'||EEAL.APPROVAL_LIST_ID as ROLE_NAME,
        EEAL.APPROVAL_LIST_ID
   from FND_USER USR,
        PER_PEOPLE_F PER,
        ENG_ECN_APPROVAL_LISTS EEAL,
        ENG_ECN_APPROVERS EEA
  where EEA.EMPLOYEE_ID         = PER.PERSON_ID
    and PER.PERSON_ID           = USR.EMPLOYEE_ID
    and EEA.APPROVAL_LIST_ID    = EEAL.APPROVAL_LIST_ID
    and trunc(sysdate) between PER.EFFECTIVE_START_DATE
                           and PER.EFFECTIVE_END_DATE
    and trunc(sysdate) between USR.START_DATE
                           and nvl(USR.END_DATE, sysdate+1)
   );

create materialized view WF_PER_GBX_MV
  refresh force
    on demand
  disable query rewrite
  as
  (select  /* &&hdr */
       GBU.USER_NAME,
       U.EMPLOYEE_ID,
       GBX.NAME,
       GBX.GROUPBOX_ID
  from GHR_GROUPBOXES GBX,
       GHR_GROUPBOX_USERS GBU,
       FND_USER U
 where GBU.GROUPBOX_ID   = GBX.GROUPBOX_ID
   and GBU.USER_NAME     = U.USER_NAME
   and U.EMPLOYEE_ID is not null
  );

/*
** WF_USER_ROLES
*/
create or replace force view WF_USER_ROLES
(
    USER_NAME,                /* varchar2(320)  not null */
    USER_ORIG_SYSTEM,         /* varchar2(8)   not null */
    USER_ORIG_SYSTEM_ID,      /* number        not null */
    ROLE_NAME,                /* varchar2(320)  not null */
    ROLE_ORIG_SYSTEM,         /* varchar2(8)   not null */
    ROLE_ORIG_SYSTEM_ID       /* number        not null */
)
as
select  /* &&hdr */
       USER_NAME,
       'PER',
       PERSON_ID,
       ROLE_NAME,
       'POS',
       POSITION_ID
from   WF_PER_POS_MV
/* Engineering approval roles */
union all
select  USER_NAME,
        'PER',
        PERSON_ID,
        ROLE_NAME,
        'ENG_LIST',
        APPROVAL_LIST_ID
from    WF_PER_ENG_MV
union all
/* User responsibility where user Not linked to an employee */
select  distinct U.USER_NAME,
        'FND_USR',
        U.USER_ID,
        'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID||':'||R.RESPONSIBILITY_ID,
        'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID,
        R.RESPONSIBILITY_ID
from    FND_USER U,
        FND_USER_RESP_GROUPS UR,
        FND_RESPONSIBILITY R
where   U.USER_ID = UR.USER_ID
and     UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
and     UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID
and     trunc(sysdate) between UR.START_DATE and NVL(UR.END_DATE, sysdate + 1)
and     trunc(sysdate) between U.START_DATE and NVL(U.END_DATE, sysdate + 1)
and     trunc(sysdate) between R.START_DATE and NVL(R.END_DATE, sysdate + 1)
and     U.EMPLOYEE_ID is null
union all
select  distinct U.USER_NAME,
        'PER',
        U.EMPLOYEE_ID,
        'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID||':'||R.RESPONSIBILITY_ID,
        'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID,
        R.RESPONSIBILITY_ID
from    FND_USER U,
        FND_USER_RESP_GROUPS UR,
        FND_RESPONSIBILITY R
where   U.USER_ID = UR.USER_ID
and     UR.RESPONSIBILITY_ID = R.RESPONSIBILITY_ID
and     UR.RESPONSIBILITY_APPLICATION_ID = R.APPLICATION_ID
and     trunc(sysdate) between UR.START_DATE and NVL(UR.END_DATE, sysdate + 1)
and     trunc(sysdate) between U.START_DATE and NVL(U.END_DATE, sysdate + 1)
and     trunc(sysdate) between R.START_DATE and NVL(R.END_DATE, sysdate + 1)
and     U.EMPLOYEE_ID is not null
union all
/* Every group box user must be an employee exist in FND_USER */
select USER_NAME,
       'PER',
       EMPLOYEE_ID,
       NAME,
       'GBX',
       GROUPBOX_ID
from   WF_PER_GBX_MV
union all
select USER_NAME,
       USER_ORIG_SYSTEM,
       USER_ORIG_SYSTEM_ID,
       ROLE_NAME,
       ROLE_ORIG_SYSTEM,
       ROLE_ORIG_SYSTEM_ID
from   WF_LOCAL_USER_ROLES
/* every user has a role_name which is the same name as their user_name */
union all
select NAME,
       ORIG_SYSTEM,
       ORIG_SYSTEM_ID,
       NAME,
       ORIG_SYSTEM,
       ORIG_SYSTEM_ID
from   WF_USERS;

commit;
exit;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de