Aprenda PL/SQL

Comparar Profiles de responsabilidades R12

Perguntas relacionadas a questões técnicas do Oracle EBS. Criação de Concorrentes, Value Sets, Alerts, Forms Personalizations, Configurações, etc
  

Mensagemem Qua, 11 Jan 2017 1:56 pm

Prezados,

Alguém tem algum método ou query que permita comparar as profiles definidas para 2 responsabilidades na R12?

Obrigado
archive

Mensagemem Qua, 11 Jan 2017 1:56 pm

Veja se ajuda essa query , é da 11.5.10 ...

Código: Selecionar todos
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);
archive

Mensagemem Qua, 11 Jan 2017 1:56 pm

Obrigado, Funcionou bem na R12 também!
archive

Mensagemem Qua, 11 Jan 2017 1:57 pm

Segue query:
Código: Selecionar todos
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
archive



Voltar para EBS Técnico

Quem está online

Usuários navegando neste fórum: Nenhum usuário registrado e 2 visitantes