Prezados,
Alguém tem algum método ou query que permita comparar as profiles definidas para 2 responsabilidades na R12?
Obrigado
Comparar Profiles de responsabilidades R12
Veja se ajuda essa query , é da 11.5.10 ...
SELECT
UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name,
SUBSTR (r.responsibility_name, 1, 60) responsiblity,
SUBSTR (a.application_name, 1, 50) application,
r.responsibility_id,
fpov.profile_option_value SOB ,
a.APPLICATION_ID,
fpo.profile_option_name
FROM
apps.fnd_user u,
apps.fnd_user_resp_groups g,
apps.fnd_application_tl a,
apps.fnd_responsibility_tl r,
apps.fnd_profile_options fpo,
apps.fnd_profile_option_values fpov
WHERE g.user_id(+) = u.user_id
AND g.responsibility_application_id = a.application_id
AND a.application_id = r.application_id
AND g.responsibility_id = r.responsibility_id
--AND u.user_id = (select user_id from apps.fnd_user where user_name='&USERNAME')
and fpov.profile_option_id = fpo.profile_option_id
and fpov.level_value = r.responsibility_id
AND fpo.profile_option_name = '&profile_name'
ORDER BY
SUBSTR (user_name, 1, 30),
SUBSTR (a.application_name, 1, 50),
SUBSTR (r.responsibility_name, 1, 60);
Obrigado, Funcionou bem na R12 também!
Segue query:
SELECT USER_PROFILE_OPTION_NAME
, MAX(RESPONSABILIDADE_1) RESPONSABILIDADE_1
, MAX(RESPONSABILIDADE_2) RESPONSABILIDADE_2
FROM (
SELECT E.USER_PROFILE_OPTION_NAME
, A.PROFILE_OPTION_VALUE RESPONSABILIDADE_1
, NULL RESPONSABILIDADE_2
FROM FND_PROFILE_OPTION_VALUES A
, FND_PROFILE_OPTIONS B
, FND_RESPONSIBILITY_TL C
, FND_APPLICATION D
, FND_PROFILE_OPTIONS_TL E
WHERE A.APPLICATION_ID = B.APPLICATION_ID
AND A.PROFILE_OPTION_ID = B.PROFILE_OPTION_ID
AND A.LEVEL_ID = 10003
AND A.LEVEL_VALUE_APPLICATION_ID = C.APPLICATION_ID
AND A.LEVEL_VALUE = C.RESPONSIBILITY_ID
AND C.LANGUAGE = 'PTB'
AND D.APPLICATION_ID = C.APPLICATION_ID
AND B.PROFILE_OPTION_NAME = E.PROFILE_OPTION_NAME
AND E.LANGUAGE = 'PTB'
AND C.RESPONSIBILITY_NAME LIKE '0101001_SUPRIMENTOS_ALMOXARIFE%' -- RESPONSABILIDADE_1
UNION ALL
SELECT E.USER_PROFILE_OPTION_NAME
, NULL
, A.PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTION_VALUES A
, FND_PROFILE_OPTIONS B
, FND_RESPONSIBILITY_TL C
, FND_APPLICATION D
, FND_PROFILE_OPTIONS_TL E
WHERE A.APPLICATION_ID = B.APPLICATION_ID
AND A.PROFILE_OPTION_ID = B.PROFILE_OPTION_ID
AND A.LEVEL_ID = 10003
AND A.LEVEL_VALUE_APPLICATION_ID = C.APPLICATION_ID
AND A.LEVEL_VALUE = C.RESPONSIBILITY_ID
AND C.LANGUAGE = 'PTB'
AND D.APPLICATION_ID = C.APPLICATION_ID
AND B.PROFILE_OPTION_NAME = E.PROFILE_OPTION_NAME
AND E.LANGUAGE = 'PTB'
AND C.RESPONSIBILITY_NAME LIKE '0102002_SUPRIMENTOS_ALMOXARIFE_REQUISITANTE_ESCRIT CNO DS SP') -- RESPONSABILIDADE_2
GROUP BY USER_PROFILE_OPTION_NAME
ORDER BY 2
-
- Informação
-
Quem está online
Usuários navegando neste fórum: Nenhum usuário registrado e 13 visitantes