USER x Responsibility X Application no E-Business Suite R12
Olá pessoal !
Um camarada precisou listar todos os usuários e responsabilidades no E-Business Suite R12, felizmente eu possuía um script adequado a essa tarefa que além dessas informações, também trazia as aplicações.
Para lista de um único usuário, utilize o script abaixo:
SELECT UNIQUE
a.user_id user_id,
a.user_name user_name,
d.responsibility_key responsibility,
c.application_name application
FROM fnd_user a,
fnd_user_resp_groups b,
fnd_application_tl c,
fnd_responsibility d
WHERE b.user_id(+) = a.user_id
AND b.responsibility_application_id = c.application_id
AND c.application_id = d.application_id
AND b.responsibility_id = d.responsibility_id
AND a.user_name LIKE UPPER('&p_user')
AND b.end_date IS NULL
ORDER BY a.user_id,
c.application_name,
d.responsibility_key
Caso a idéia seja trazer TODOS os usuários, apenas retire a condição de pesquisa por usuário:
SELECT UNIQUE
a.user_id user_id,
a.user_name user_name,
d.responsibility_key responsibility,
c.application_name application
FROM fnd_user a,
fnd_user_resp_groups b,
fnd_application_tl c,
fnd_responsibility d
WHERE b.user_id(+) = a.user_id
AND b.responsibility_application_id = c.application_id
AND c.application_id = d.application_id
AND b.responsibility_id = d.responsibility_id
AND b.end_date IS NULL
ORDER BY a.user_id,
c.application_name,
d.responsibility_key
Espero que esse script seja de alguma valia a vocês. Fica a dica ! 🙂
Um abraço