REM dbdrv: none REM +======================================================================+ REM | Copyright (c) 1995 Oracle Corporation Redwood Shores, California, USA| REM | All rights reserved. | REM +======================================================================+ REM REM NAME REM wfdirhrv.sql - WorkFlow DIRectory (Human Resources) 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 - Change comments to increase REM usernames, email_addresses, and roles to varchar2(320), REM role display_name to varchar2(360) REM REM 07/2002 CTILLEY BUGS 2423719 and 2452266 - changed decode to determine REM if a user is ACTIVE to account for future end_dates. Changed REM HZ_PARTY select to default the territory based on lang code REM ======================================================================= WHENEVER SQLERROR EXIT FAILURE ROLLBACK; DEFINE hdr = "$Header: wfdirhrv.sql 26.35 2005/03/03 21:46:44 rwunderl ship $" SET VERIFY OFF REM /* WF_LANGUAGES */ create or replace force view wf_languages ( CODE, DISPLAY_NAME, NLS_LANGUAGE, NLS_TERRITORY, NLS_CODESET, INSTALLED_FLAG ) as select L.LANGUAGE_CODE CODE, LTL.DESCRIPTION DISPLAY_NAME, L.NLS_LANGUAGE NLS_LANGUAGE, L.NLS_TERRITORY NLS_TERRITORY, L.NLS_CODESET NLS_CODESET, decode(INSTALLED_FLAG, 'B', 'Y', 'I', 'Y', 'N') INSTALLED_FLAG from FND_LANGUAGES L, FND_LANGUAGES_TL LTL where LTL.LANGUAGE_CODE = L.LANGUAGE_CODE and LTL.LANGUAGE = userenv('LANG'); REM /* WF_USERS */ 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 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 (WR.PARTITION_ID = 1 /* FND_USR and PER */ or (WR.PARTITION_ID IN (0,9) and WR.USER_FLAG = 'Y')) /*Adhoc Users and HZ_PARTY Persons*/ 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_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, OWNER_TAG, 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.OWNER_TAG, WR.PARTITION_ID from wf_local_roles WR, wf_local_roles_tl WRT WHERE (WR.PARTITION_ID = 1 /* FND_USR and PER */ or (WR.PARTITION_ID IN (0,9) and WR.USER_FLAG = 'Y')) /*Adhoc Users and HZ_PARTY Persons*/ 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.OWNER_TAG, WR.PARTITION_ID from wf_local_roles WR WHERE (WR.PARTITION_ID = 1 /* FND_USR and PER */ or (WR.PARTITION_ID IN (0,9) and WR.USER_FLAG = 'Y')) /*Adhoc Users and HZ_PARTY Persons*/ 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_ROLES */ 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 WR.NAME, nvl(WRT.DISPLAY_NAME, WR.DISPLAY_NAME), nvl(WRT.DESCRIPTION, WR.DISPLAY_NAME), 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 WR.partition_id <> 3 /*Excluding PER_ROLE */ 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_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, OWNER_TAG, 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.OWNER_TAG, WR.PARTITION_ID from wf_local_roles WR, wf_local_roles_tl WRT WHERE wr.partition_id <> 3 /*Excluding PER_ROLE */ 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.OWNER_TAG, WR.PARTITION_ID from wf_local_roles WR WHERE wr.partition_id <> 3 /*Excluding PER_ROLE */ 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 ))); REM /* 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.DISPLAY_NAME), 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.partition_id <> 3 /*Excluding PER_ROLE */ 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'); REM /* WF_USER_ROLES */ 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 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) >= trunc(wur.start_date))) and ((wur.expiration_date is NULL) or (trunc(sysdate) < trunc(wur.expiration_date))) and ((wur.user_start_date is NULL) or (trunc(sysdate) >= trunc(wur.user_start_date))) and ((wur.user_end_date is NULL) or (trunc(sysdate) < trunc(wur.user_end_date))) and ((wur.role_start_date is NULL) or (trunc(sysdate) >= trunc(wur.role_start_date))) and ((wur.role_end_date is NULL) or (trunc(sysdate) < trunc(wur.role_end_date)))); REM /* 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; REM /* WF_USER_ROLE_ASSIGNMENTS_V */ create or replace force view wf_user_role_assignments_v ( USER_NAME, ROLE_NAME, ASSIGNING_ROLE, START_DATE, END_DATE, ASSIGNMENT_TYPE ) as select WURA.USER_NAME, WURA.ROLE_NAME, WURA.ASSIGNING_ROLE, WURA.START_DATE, WURA.END_DATE, decode(WURA.RELATIONSHIP_ID, -1, 'DIRECT', 'INHERITED') from WF_USER_ROLE_ASSIGNMENTS wura where ((wura.start_date is NULL) or (trunc(sysdate) >= trunc(wura.start_date))) and ((wura.end_date is NULL) or (trunc(sysdate) < trunc(wura.end_date))) and ((wura.user_start_date is NULL) or (trunc(sysdate) >= trunc(wura.user_start_date))) and ((wura.user_end_date is NULL) or (trunc(sysdate) < trunc(wura.user_end_date))) and ((wura.role_start_date is NULL) or (trunc(sysdate) >= trunc(wura.role_start_date))) and ((wura.role_end_date is NULL) or (trunc(sysdate) < trunc(wura.role_end_date))) and ((wura.assigning_role_start_date is NULL) or (trunc(sysdate) >= trunc(wura.assigning_role_start_date))) and ((wura.assigning_role_end_date is NULL) or (trunc(sysdate) < trunc(wura.assigning_role_end_date))); REM /* 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; REM DL Note: REM PER have notification preference 'QUERY' REM wf_directory.getroleinfo() will be smart enough to resolve PER to REM underlying FND_USR (not trivia) REM PER is always 'ACTIVE' as we only show those are in effective date range. REM STATUS used to be determined by fnd_user enddate. REM /* WF_FND_USR_ROLES (Not to be confused with USER_ROLES.) */ create or replace force view WF_FND_USR_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select USR.USER_NAME, PER.DISPLAY_NAME, PER.DESCRIPTION, NVL(wf_pref.get_pref(USR.USER_NAME,'MAILTYPE'),'MAILHTML'), wf_pref.get_pref(USR.USER_NAME, 'LANGUAGE'), wf_pref.get_pref(USR.USER_NAME, 'TERRITORY'), PER.EMAIL_ADDRESS, USR.FAX, 'PER', PER.ORIG_SYSTEM_ID, USR.START_DATE, decode(substr(to_char(nvl(usr.end_date,sysdate+1)-sysdate),1,1), '-', 'INACTIVE', 'ACTIVE'), USR.END_DATE, NULL, 'Y', 1 from WF_LOCAL_ROLES PARTITION (PER_ROLE) PER, FND_USER USR where SYSDATE between nvl(PER.START_DATE, sysdate) and nvl(PER.EXPIRATION_DATE, sysdate+1) and PER.ORIG_SYSTEM_ID = USR.EMPLOYEE_ID union all select USR.USER_NAME, USR.USER_NAME, USR.DESCRIPTION, NVL(wf_pref.get_pref(USR.USER_NAME,'MAILTYPE'),'MAILHTML'), wf_pref.get_pref(USR.USER_NAME, 'LANGUAGE'), wf_pref.get_pref(USR.USER_NAME, 'TERRITORY'), USR.EMAIL_ADDRESS, USR.FAX, 'FND_USR', USR.USER_ID, USR.START_DATE, decode(substr(to_char(nvl(usr.end_date,sysdate+1)-sysdate),1,1), '-', 'INACTIVE', 'ACTIVE'), USR.END_DATE, NULL, 'Y', 1 from FND_USER USR where USR.EMPLOYEE_ID is null / REM /* WF_PER_ROLE_ROLES */ create or replace force view WF_PER_ROLE_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select /*+ index_ffs(PER,PER_PEOPLE_F_PK) */ 'PER_ROLE:'||PER.PERSON_ID, PER.FULL_NAME, PER.FULL_NAME, 'MAILHTML', NULL, NULL, PER.EMAIL_ADDRESS, NULL, 'PER_ROLE', PER.PERSON_ID, PER.EFFECTIVE_START_DATE, 'ACTIVE', PER.EFFECTIVE_END_DATE, NULL, 'N', 3 from PER_ALL_PEOPLE_F PER where trunc(SYSDATE) between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE / REM /* WF_HZ_PARTY_ROLES */ create or replace force view WF_HZ_PARTY_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select /*+ ordered use_hash(inline,pl,l1) */ 'HZ_PARTY:'||to_char(inline.per_id) , nvl(inline.subj_name, inline.per_name) , inline.per_name , 'MAILTEXT' , l1.nls_language , l1.nls_territory , inline.email_address , null , 'HZ_PARTY' , inline.per_id , to_date(NULL) , DECODE(inline.status,'A','ACTIVE','INACTIVE') , TO_DATE(NULL) , NULL , 'Y' , 9 from ( select /*+ no_merge ordered use_hash(p,pr,oc,subj) index_ffs(oc,HZ_ORG_CONTACTS_N3) */ nvl2(pr.party_id, subj.party_id, p.party_id) party, p.party_type party_type, pr.party_id rel_party_id, p.party_id per_id, p.party_name per_name, subj.party_name subj_name, p.email_address email_address, p.status from hz_parties p, hz_party_relationships pr, hz_org_contacts oc , hz_parties subj where p.party_type in ('PERSON','PARTY_RELATIONSHIP') and p.status IN ('A', 'I') and pr.party_id(+) = p.party_id and pr.status(+) = 'A' and pr.subject_id = subj.party_id(+) and pr.party_relationship_id = oc.party_relationship_id(+) and (p.party_type = 'PERSON' OR oc.party_relationship_id IS NOT NULL) ) inline, hz_person_language pl, fnd_languages l1 where inline.party = pl.party_id(+) and pl.primary_language_indicator(+) = 'Y' and pl.status(+) = 'A' and pl.language_name = l1.language_code(+) / REM /* WF_POS_ROLES */ create or replace force view WF_POS_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select 'POS'||':'||POS.POSITION_ID, POS.NAME, POS.NAME, 'QUERY', FNDL.NLS_LANGUAGE, FNDT.NLS_TERRITORY, NULL, NULL, 'POS', POS.POSITION_ID, to_date(NULL), 'ACTIVE', to_date(NULL), NULL, 'N', 4 from FND_TERRITORIES FNDT, FND_LANGUAGES FNDL, HR_LOCATIONS_ALL HRL, PER_ALL_POSITIONS POS where POS.LOCATION_ID = HRL.LOCATION_ID(+) and HRL.COUNTRY = FNDT.TERRITORY_CODE(+) and FNDT.NLS_TERRITORY = FNDL.NLS_TERRITORY(+) / REM /* WF_ENG_LIST_ROLES */ create or replace force view WF_ENG_LIST_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as SELECT 'ENG_LIST'||':'||eeal.approval_list_id, eeal.approval_list_name, eeal.description, 'QUERY', NULL, NULL, NULL, NULL, 'ENG_LIST', eeal.approval_list_id, to_date(NULL), 'ACTIVE', to_date(NULL), NULL, 'N', 7 FROM eng_ecn_approval_lists eeal / REM /* WF_FND_RESP_ROLES */ create or replace force view WF_FND_RESP_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select 'FND_RESP'||r.application_id||':'||r.responsibility_id, RTL.RESPONSIBILITY_NAME, RTL.DESCRIPTION, 'QUERY', NULL, NULL, null, null, 'FND_RESP'||r.application_id, R.RESPONSIBILITY_ID, r.START_DATE, decode(substr(to_char(nvl(r.end_date,sysdate+1)-sysdate),1,1), '-', 'INACTIVE', 'ACTIVE'), r.END_DATE, null, 'N', 2 from FND_RESPONSIBILITY R, FND_RESPONSIBILITY_TL RTL, FND_LANGUAGES L where L.INSTALLED_FLAG = 'B' and RTL.LANGUAGE = L.LANGUAGE_CODE and R.APPLICATION_ID = RTL.APPLICATION_ID and R.RESPONSIBILITY_ID = RTL.RESPONSIBILITY_ID / /* WF_FND_RESP_ROLES_TL */ create or replace force view WF_FND_RESP_ROLES_TL ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) LANGUAGE, -- varchar2(30) not null SOURCE_LANG, -- varchar2(30) not null ORIG_SYSTEM, -- varchar2(8) not null ORIG_SYSTEM_ID -- number not null ) as select 'FND_RESP'||rtl.application_id||':'||rtl.responsibility_id, RTL.RESPONSIBILITY_NAME, RTL.DESCRIPTION, RTL.LANGUAGE, RTL.SOURCE_LANG, 'FND_RESP'||rtl.application_id, RTL.RESPONSIBILITY_ID from FND_RESPONSIBILITY_TL RTL, FND_LANGUAGES L where L.INSTALLED_FLAG = 'I' and RTL.LANGUAGE = L.LANGUAGE_CODE / REM /* WF_GBX_ROLES */ create or replace force view WF_GBX_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select GBX.NAME, GBX.DISPLAY_NAME, GBX.DESCRIPTION, 'QUERY', NULL, NULL, NULL, NULL, 'GBX', GBX.GROUPBOX_ID, to_date(NULL), 'ACTIVE', to_date(NULL), NULL, 'N', 10 from GHR_GROUPBOXES GBX / REM /* WF_AMV_CHN_ROLES */ create or replace force view WF_AMV_CHN_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select 'AMV_CHN'||':'||CHB.CHANNEL_ID, CHT.CHANNEL_NAME, CHT.CHANNEL_NAME, 'QUERY', FNDL.NLS_LANGUAGE, FNDL.NLS_TERRITORY, NULL, NULL, 'AMV_CHN', CHB.CHANNEL_ID, to_date(NULL), CHB.status, to_date(null), NULL, 'N', 6 from FND_LANGUAGES FNDL, AMV_C_CHANNELS_B CHB, AMV_C_CHANNELS_TL CHT where CHB.CHANNEL_ID = CHT.CHANNEL_ID and CHT.LANGUAGE = FNDL.LANGUAGE_CODE and FNDL.LANGUAGE_CODE = userenv('lang') / REM /* WF_AMV_APPR_ROLES */ create or replace force view WF_AMV_APPR_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select 'AMV_APPR', 'AMV', 'AMV', 'QUERY', FNDL.NLS_LANGUAGE, FNDL.NLS_TERRITORY, NULL, NULL, 'AMV_APPR', 520, to_date(NULL), 'ACTIVE', to_date(null), NULL, 'N', 5 from FND_LANGUAGES FNDL where FNDL.INSTALLED_FLAG = 'B' / REM /* WF_HZ_GROUP_ROLES */ create or replace force view WF_HZ_GROUP_ROLES ( NAME, -- varchar2(320) not null DISPLAY_NAME, -- varchar2(360) not null DESCRIPTION, -- varchar2(1000) 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 START_DATE, -- date STATUS, -- varchar2(8) not null EXPIRATION_DATE, -- date not null SECURITY_GROUP_ID, -- varchar2(32) USER_FLAG, -- varchar2(1) PARTITION_ID -- number ) as select 'HZ_GROUP:'||to_char(p.party_id) , p.party_name , p.mission_statement , NVL(cp.email_format, 'MAILTEXT') , l.nls_language , l.nls_territory , p.email_address , null , 'HZ_GROUP' , p.party_id , TO_DATE(NULL) , DECODE(p.status,'A','ACTIVE','INACTIVE') , TO_DATE(NULL) , NULL , 'N' , 8 from hz_parties p , hz_person_language pl , hz_contact_points cp , fnd_languages l where p.party_type = 'GROUP' and p.status IN ('A', 'I') and cp.owner_table_name(+) = 'HZ_PARTIES' and p.party_id = cp.owner_table_id(+) and cp.contact_point_type(+) = 'EMAIL' and cp.primary_flag(+) = 'Y' and cp.status(+) = 'A' and p.party_id = pl.party_id(+) and pl.primary_language_indicator(+) = 'Y' and pl.status(+) = 'A' and pl.language_name = l.language_code(+) / REM DL Note: REM Additional FND_USR/PER relationship here. REM Need to check all the select statements against WF_USER_ROLES to include REM or exclude this. REM Took out PER/FND_RESP relationship. REM It should be covered by FND_USR/FND_RESP relationship. REM Replaced all 'PER' user with 'PER:XXXX' REM /* WF_FND_USR_UR */ create or replace force view WF_FND_USR_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) as select USR.USER_NAME, 'PER', PER.ORIG_SYSTEM_ID, USR.USER_NAME, 'PER', PER.ORIG_SYSTEM_ID, USR.START_DATE, USR.END_DATE, NULL, 1 from FND_USER USR, WF_LOCAL_ROLES PARTITION (PER_ROLE) PER where USR.EMPLOYEE_ID = PER.ORIG_SYSTEM_ID and trunc(sysdate) between nvl(PER.START_DATE, trunc(sysdate)) and nvl(PER.EXPIRATION_DATE, trunc(sysdate+1)) union all select usr.user_name, 'FND_USR', usr.user_id, usr.user_name, 'FND_USR', usr.user_id, usr.start_date, usr.end_date, NULL, 1 from FND_USER USR where usr.employee_id is null / REM /* WF_POS_UR */ create or replace force view WF_POS_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) as select USR.USER_NAME, 'PER', PER.PERSON_ID, 'POS'||':'||POS.POSITION_ID, 'POS', POS.POSITION_ID, to_date(NULL), to_date(NULL), NULL, 4 from PER_ALL_ASSIGNMENTS_F ASS, PER_ALL_POSITIONS POS, FND_USER USR, PER_ALL_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' / CREATE OR REPLACE VIEW WF_ENG_LIST_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) AS SELECT USR.USER_NAME, 'PER', PER.PERSON_ID, 'ENG_LIST'||':'||EEAL.APPROVAL_LIST_ID, 'ENG_LIST', EEAL.APPROVAL_LIST_ID, to_date(NULL), to_date(NULL), NULL, 7 FROM FND_USER USR, PER_ALL_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) AND trunc(sysdate) <= NVL(EEA.DISABLE_DATE,SYSDATE+1) / REM /* WF_FND_RESP_UR */ create or replace force view WF_FND_RESP_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) AS select distinct U.USER_NAME, decode(u.employee_id,NULL,'FND_USR','PER'), decode(u.employee_id,NULL,U.USER_ID,u.employee_id), 'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID||':'||R.RESPONSIBILITY_ID, 'FND_RESP'||UR.RESPONSIBILITY_APPLICATION_ID, R.RESPONSIBILITY_ID, to_date(NULL), to_date(NULL), NULL, 2 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) / REM /* WF_GBX_UR */ create or replace force view WF_GBX_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) AS select GBU.USER_NAME, 'PER', U.EMPLOYEE_ID, GBX.NAME, 'GBX', GBX.GROUPBOX_ID, to_date(NULL), to_date(NULL), NULL, 10 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 / REM /* WF_AMV_CHN_UR */ create or replace force view WF_AMV_CHN_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) AS select USR.USER_NAME, 'PER', PER.PERSON_ID, 'AMV_CHN'||':'||CHB.CHANNEL_ID, 'AMV_CHN', CHB.CHANNEL_ID, to_date(NULL), to_date(NULL), NULL, 6 from AMV_C_CHANNELS_B CHB, AMV_U_ACCESS CUA, JTF_RS_RESOURCE_EXTNS JRE, PER_ALL_PEOPLE_F PER, FND_USER USR where CUA.ACCESS_TO_TABLE_CODE = 'CHANNEL' and CUA.ACCESS_TO_TABLE_RECORD_ID = CHB.CHANNEL_ID and CUA.USER_OR_GROUP_TYPE = 'USER' and CUA.EFFECTIVE_START_DATE <= sysdate and NVL(CUA.EXPIRATION_DATE,sysdate) >= sysdate and CUA.CHL_APPROVER_FLAG = 'T' and CUA.USER_OR_GROUP_ID = JRE.RESOURCE_ID and JRE.CATEGORY = 'EMPLOYEE' and JRE.SOURCE_ID = PER.PERSON_ID and trunc(SYSDATE) between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE and PER.PERSON_ID = USR.EMPLOYEE_ID / REM /* WF_AMV_APPR_UR */ create or replace force view WF_AMV_APPR_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) AS select USR.USER_NAME, 'PER', PER.PERSON_ID, 'AMV_APPR', 'AMV_APPR', 520, to_date(NULL), to_date(NULL), NULL, 5 from JTF_RS_ROLES_B JRB, JTF_RS_ROLE_RELATIONS JRR, JTF_RS_RESOURCE_EXTNS JRE, PER_ALL_PEOPLE_F PER, FND_USER USR where JRB.ROLE_TYPE_CODE = 'MES' and JRB.ROLE_CODE = 'MES_APPROVE' and JRB.ROLE_ID = JRR.ROLE_ID and JRR.ROLE_RESOURCE_TYPE = 'RS_INDIVIDUAL' and JRR.ROLE_RESOURCE_ID = JRE.RESOURCE_ID and JRE.CATEGORY = 'EMPLOYEE' and JRE.SOURCE_ID = PER.PERSON_ID and trunc(SYSDATE) between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE and PER.PERSON_ID = USR.EMPLOYEE_ID union select USR.USER_NAME, 'PER', PER.PERSON_ID, 'AMV_APPR', 'AMV_APPR', 520, to_date(NULL), to_date(NULL), NULL, 5 from JTF_RS_ROLES_B JRB, JTF_RS_ROLE_RELATIONS JRR, JTF_RS_GROUP_USAGES JGU, JTF_RS_GROUPS_B JGB, JTF_RS_GROUP_MEMBERS JGM, JTF_RS_RESOURCE_EXTNS JRE, PER_ALL_PEOPLE_F PER, FND_USER USR where JRB.ROLE_TYPE_CODE = 'MES' and JRB.ROLE_CODE = 'MES_APPROVE' and JRB.ROLE_ID = JRR.ROLE_ID and JRR.ROLE_RESOURCE_TYPE = 'RS_GROUP' and JRR.ROLE_RESOURCE_ID = JGB.GROUP_ID and NVL(JRR.DELETE_FLAG,'N') <> 'Y' and JGU.USAGE = 'MES_GROUP' and JGU.GROUP_ID = JGB.GROUP_ID and JGU.GROUP_ID = JRR.ROLE_RESOURCE_ID and JGB.GROUP_ID = JGM.GROUP_ID and NVL(JGM.DELETE_FLAG,'N') <> 'Y' and JGM.RESOURCE_ID = JRE.RESOURCE_ID and JRE.CATEGORY = 'EMPLOYEE' and JRE.SOURCE_ID = PER.PERSON_ID and trunc(SYSDATE) between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE and PER.PERSON_ID = USR.EMPLOYEE_ID / REM /* WF_HZ_GROUP_UR */ create or replace force view WF_HZ_GROUP_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) as select 'HZ_PARTY:'||to_char(sp.party_id) USER_NAME , 'HZ_PARTY' USER_ORIG_SYSTEM , sp.party_id USER_ORIG_SYSTEM_ID , 'HZ_GROUP:'||to_char(op.party_id) ROLE_NAME , 'HZ_GROUP' ROLE_ORIG_SYSTEM , op.party_id ROLE_ORIG_SYSTEM_ID , pr.start_date , pr.end_date , NULL , 8 from hz_party_relationships pr , hz_parties sp , hz_parties op where pr.status = 'A' and sp.party_id = pr.subject_id and sp.party_type = 'PERSON' and op.party_id = pr.object_id and op.party_type = 'GROUP' / /* WF_HZ_PARTY_UR */ create or replace force view WF_HZ_PARTY_UR ( USER_NAME, USER_ORIG_SYSTEM, USER_ORIG_SYSTEM_ID, ROLE_NAME, ROLE_ORIG_SYSTEM, ROLE_ORIG_SYSTEM_ID, START_DATE, EXPIRATION_DATE, SECURITY_GROUP_ID, PARTITION_ID ) as select NAME USER_NAME , ORIG_SYSTEM USER_ORIG_SYSTEM , ORIG_SYSTEM_ID USER_ORIG_SYSTEM_ID , NAME ROLE_NAME , ORIG_SYSTEM ROLE_ORIG_SYSTEM , ORIG_SYSTEM_ID ROLE_ORIG_SYSTEM_ID , TO_DATE(NULL) , TO_DATE(NULL) , NULL , 9 from WF_LOCAL_ROLES PARTITION (HZ_PARTY) / 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') / commit; exit;