查询一个职责哪些用户具有

--- query ID
select U.user_id user_id,
u.user_name,
u.description,
WLR.ORIG_SYSTEM_ID RESPONSIBILITY_ID ,
(select application_id
from apps.fnd_application
where application_short_name =/* Val between 1st and 2nd separator */
replace(
substr(WAUR.ROLE_NAME,
INSTR(WAUR.ROLE_NAME, '|', 1, 1)+1,
( INSTR(WAUR.ROLE_NAME, '|', 1, 2)
-INSTR(WAUR.ROLE_NAME, '|', 1, 1)-1)
)
,'%col', ':')
) RESPONSIBILITY_APPLICATION_ID,
(select security_group_id
from apps.fnd_security_groups
where security_group_key =/* Val after 3rd separator */
replace(
substr(WAUR.ROLE_NAME,
INSTR(WAUR.ROLE_NAME, '|', 1, 3)+1
)
,'%col', ':')
) SECURITY_GROUP_ID,
WAUR.START_DATE,
WAUR.END_DATE END_DATE,
WAUR.CREATED_BY CREATED_BY,
WAUR.CREATION_DATE CREATION_DATE,
WAUR.LAST_UPDATED_BY LAST_UPDATED_BY,
WAUR.LAST_UPDATE_DATE LAST_UPDATE_DATE,
WAUR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
from apps.fnd_user u, apps.wf_all_user_role_assignments waur,
apps.wf_local_roles wlr,
apps.fnd_responsibility_vl t
where u.user_name = waur.user_name
and waur.role_name = wlr.name
and wlr.orig_system_id=t.RESPONSIBILITY_ID
and waur.END_DATE is null
--and waur.assignment_type = 'INHERITED'
-- and wlr.orig_system = 'FND_RESP'
and t.RESPONSIBILITY_NAME like '&RESP_NAME'
and not waur.role_name like 'FND_RESP|%|ANY';

-- queRY user
select U.user_id user_id,
u.user_name,
u.description,
WLR.ORIG_SYSTEM_ID RESPONSIBILITY_ID ,
(select application_id
from apps.fnd_application
where application_short_name =/* Val between 1st and 2nd separator */
replace(
substr(WAUR.ROLE_NAME,
INSTR(WAUR.ROLE_NAME, '|', 1, 1)+1,
( INSTR(WAUR.ROLE_NAME, '|', 1, 2)
-INSTR(WAUR.ROLE_NAME, '|', 1, 1)-1)
)
,'%col', ':')
) RESPONSIBILITY_APPLICATION_ID,
(select security_group_id
from apps.fnd_security_groups
where security_group_key =/* Val after 3rd separator */
replace(
substr(WAUR.ROLE_NAME,
INSTR(WAUR.ROLE_NAME, '|', 1, 3)+1
)
,'%col', ':')
) SECURITY_GROUP_ID,
WAUR.START_DATE,
WAUR.END_DATE END_DATE,
WAUR.CREATED_BY CREATED_BY,
WAUR.CREATION_DATE CREATION_DATE,
WAUR.LAST_UPDATED_BY LAST_UPDATED_BY,
WAUR.LAST_UPDATE_DATE LAST_UPDATE_DATE,
WAUR.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
from apps.fnd_user u, apps.wf_all_user_role_assignments waur,
apps.wf_local_roles wlr
where u.user_name = waur.user_name
and waur.role_name = wlr.name
and wlr.orig_system_id=78298
--and waur.assignment_type = 'INHERITED'
-- and wlr.orig_system = 'FND_RESP'
and waur.END_DATE is null
and not waur.role_name like 'FND_RESP|%|ANY';