Queries uteis para o Oracle Ebs

LISTA RESPONSABILIDADES INFORMANDO O USUÁRIO

--Profile por Usuário

SELECT A.PROFILE_OPTION_NAME,A.USER_PROFILE_OPTION_NAME, C.PROFILE_OPTION_VALUE,D.USER_NAME

FROM

 FND_PROFILE_OPTIONS_TL A,

 FND_PROFILE_OPTIONS B,

 FND_PROFILE_OPTION_VALUES C,

 FND_USER D

WHERE /*A.PROFILE_OPTION_NAME = 'ICX_NUMERIC_CHARACTERS'

--      A.USER_PROFILE_OPTION_NAME = 'ICX: Caracteres numéricos'  --> Nome da profile

  AND */A.LANGUAGE = 'PTB'

  AND A.PROFILE_OPTION_NAME = B.PROFILE_OPTION_NAME

  AND B.PROFILE_OPTION_ID = C.PROFILE_OPTION_ID

  AND B.APPLICATION_ID = C.APPLICATION_ID 

  AND C.PROFILE_OPTION_VALUE IS NOT NULL

  AND C.LEVEL_ID = 10004 

  AND C.LEVEL_VALUE = D.USER_ID

  AND D.USER_NAME LIKE '%PACHECO%' --> Nome do usuario

      ;

--Profile por responsabilidade

SELECT A.PROFILE_OPTION_NAME,A.USER_PROFILE_OPTION_NAME, C.PROFILE_OPTION_VALUE, 

REPLACE(C.PROFILE_OPTION_VALUE,'/data01/','/dados/db/'), D.RESPONSIBILITY_NAME,

C.PROFILE_OPTION_ID, C.APPLICATION_ID, C.LEVEL_ID, C.LEVEL_VALUE 

FROM

 FND_PROFILE_OPTIONS_TL A,

 FND_PROFILE_OPTIONS B,

 FND_PROFILE_OPTION_VALUES C,

 FND_RESPONSIBILITY_TL D

WHERE --A.PROFILE_OPTION_NAME = 'BS_AP_DIR'

      /*A.USER_PROFILE_OPTION_NAME = 'BSAP: Diretorio' --> Nome da profile

  AND*/ A.LANGUAGE = 'PTB'

  AND A.PROFILE_OPTION_NAME = B.PROFILE_OPTION_NAME

  AND B.PROFILE_OPTION_ID = C.PROFILE_OPTION_ID

  AND B.APPLICATION_ID = C.APPLICATION_ID 

  AND C.PROFILE_OPTION_VALUE IS NOT NULL

  AND C.LEVEL_ID = 10003 

  AND C.LEVEL_VALUE = D.RESPONSIBILITY_ID

  AND D.RESPONSIBILITY_NAME LIKE 'ABFPP PA%' --> Responsabilidade

  AND D.LANGUAGE ='PTB'

--  AND C.PROFILE_OPTION_VALUE NOT LIKE '/dados/db/%'    --> Valor da profile

--Valor de uma profile

select PROFILE_OPTION_NAME, PROFILE_OPTION_VALUE

from fnd_profile_options a

, fnd_profile_option_values b

where a.APPLICATION_ID = b.APPLICATION_ID

and a.PROFILE_OPTION_ID = b.PROFILE_OPTION_ID

and a.PROFILE_OPTION_NAME in ( 'APPS_FRAMEWORK_AGENT', 'WF_MAIL_WEB_AGENT') ? Nome das profiles

and b.level_value = 0; 

--Nome de uma profile

SELECT A.PROFILE_OPTION_NAME, A.USER_PROFILE_OPTION_NAME USER_PROFILE_PORT, B.USER_PROFILE_OPTION_NAME USER_PROFILE_INGLES

FROM FND_PROFILE_OPTIONS_TL A, FND_PROFILE_OPTIONS_TL B

WHERE A.USER_PROFILE_OPTION_NAME = 'Nome do Local' --> Portugues

--B.USER_PROFILE_OPTION_NAME = 'Site Name' --> Ingles

AND A.PROFILE_OPTION_NAME = B.PROFILE_OPTION_NAME

AND A.LANGUAGE='PTB'

AND B.LANGUAGE='US'

--------------------------------------

/*********************************************************
*PURPOSE: To find out profile option Values              *
*AUTHOR: Shailender Thallam                              *
**********************************************************/
SELECT DISTINCT POT.PROFILE_OPTION_NAME "PROFILE_CODE" 
  , POT.USER_PROFILE_OPTION_NAME "PROFILE_NAME" 
       , DECODE (a.profile_option_value
             , '1', '1 (may be "Yes")'
             , '2', '2 (may be "No")'
             , a.profile_option_value
              ) "PF_VALUE"
     , DECODE (a.level_id
             , 10001, 'Site'
             , 10002, 'Application'
             , 10003, 'Responsibility'
             , 10004, 'User'
             , 10005, 'Server'
             , 10006, 'Organization'
			 , a.level_id
              ) "LEVEL_IDENTIFIER"
     , DECODE (a.level_id
             , 10002, e.application_name
             , 10003, c.responsibility_name
             , 10004, D.USER_NAME
             , 10005, F.HOST || '.' || F.DOMAIN
             , 10006, g.name
             , '-'
              ) "LEVEL_NAME"
 
FROM fnd_application_tl e ,
  fnd_user d ,
  fnd_responsibility_tl c ,
  fnd_profile_option_values a ,
  fnd_profile_options b ,
  fnd_profile_options_tl pot ,
  fnd_nodes f ,
  hr_all_organization_units g
WHERE 1=1
AND UPPER(pot.USER_PROFILE_OPTION_NAME) LIKE UPPER('MO: Default Operating Unit')
AND pot.profile_option_name = b.profile_option_name
AND b.application_id        = a.application_id(+)
AND b.profile_option_id     = a.profile_option_id(+)
AND a.level_value           = c.responsibility_id(+)
AND a.level_value           = d.user_id(+)
AND a.level_value           = e.application_id(+)
AND a.level_value           = f.node_id(+)
AND a.level_value           = g.organization_id(+)
AND pot.language            ='US'
ORDER BY PROFILE_NAME ,
  LEVEL_IDENTIFIER ,
  LEVEL_NAME ,
  PF_VALUE
  ;

 

-Profile por responsabilidade

SELECT A.PROFILE_OPTION_NAME,A.USER_PROFILE_OPTION_NAME, C.PROFILE_OPTION_VALUE, 

REPLACE(C.PROFILE_OPTION_VALUE,'/data01/','/dados/db/'), D.RESPONSIBILITY_NAME,

C.PROFILE_OPTION_ID, C.APPLICATION_ID, C.LEVEL_ID, C.LEVEL_VALUE 

FROM

 FND_PROFILE_OPTIONS_TL A,

 FND_PROFILE_OPTIONS B,

 FND_PROFILE_OPTION_VALUES C,

 FND_RESPONSIBILITY_TL D

WHERE --A.PROFILE_OPTION_NAME = 'BS_AP_DIR'

      /*A.USER_PROFILE_OPTION_NAME = 'BSAP: Diretorio' --> Nome da profile

  AND*/ A.LANGUAGE = 'PTB'

  AND A.PROFILE_OPTION_NAME = B.PROFILE_OPTION_NAME

  AND B.PROFILE_OPTION_ID = C.PROFILE_OPTION_ID

  AND B.APPLICATION_ID = C.APPLICATION_ID 

  AND C.PROFILE_OPTION_VALUE IS NOT NULL

  AND C.LEVEL_ID = 10003 

  AND C.LEVEL_VALUE = D.RESPONSIBILITY_ID

  AND D.RESPONSIBILITY_NAME LIKE 'ABFPP PA%' --> Responsabilidade

  AND D.LANGUAGE ='PTB'

--  AND C.PROFILE_OPTION_VALUE NOT LIKE '/dados/db/%'    --> Valor da profile