EBS – MIS Note – 管理信息系统笔记

近日根据eTRM梳理了EBS配置文件涉及的各种表，做了一个可以按照配置文件选项名称、地点、应用、职责、服务器、组织、用户查询各级别上配置文件选项设置的sql脚本，希望对大家有所帮助。

with
  PROFILE_OPTION_LEVEL as ( --定义配置文件 [层级] 子查询
  select 10001 Level_Id, 'Site' Level_Name from dual union
  select 10002 Level_Id, 'Application' Level_Name from dual union
  select 10003 Level_Id, 'Responsibility' Level_Name from dual union
  select 10004 Level_Id, 'User' Level_Name from dual union
  select 10005 Level_Id, 'Server' Level_Name from dual union
  select 10006 Level_Id, 'Organization' Level_Name from dual),

  PROFILE_OPTION_LEVEL_VALUE as ( --定义配置文件 [层级值] 子查询
  select '10002' as Level_Value_Type, --应用
      Application_Id as Level_Value_Id,
      Application_Name as Level_Value_Name,
      0 as Level_Value_Application_Id from FND_APPLICATION_TL where Language='ZHS' union
  select '10003' as Level_Value_Type, --职责
      Responsibility_Id as Level_Value_Id,
      Responsibility_Name as Level_Value_Name,
      Application_Id as Level_Value_Application_Id from FND_RESPONSIBILITY_TL where Language='ZHS' union
  select '10004' as Level_Value_Type, --用户
      User_Id as Level_Value_Id,
      User_Name as Level_Value_Name,
      0 as Level_Value_Application_Id from FND_USER union
  select '10005' as Level_Value_Type, --服务器
      Node_Id as Level_Value_Id,
      Node_Name as Level_Value_Name,
      0 as Level_Value_Application_Id from FND_NODES union
  select '10006' as Level_Value_Type, --组织
      Organization_Id as Level_Value_Id,
      Name as Level_Value_Name,
      0 as Level_Value_Application_Id from HR_OPERATING_UNITS )

select po.Profile_Option_Id,po.Profile_Option_Name,po.User_Changeable_Flag,po.User_Update_Allowed_Flag,
       pot.User_Profile_Option_Name,pot.Description,
       pol.Level_Id,pol.Level_Name,
       pov.Profile_Option_Value, --配置文件选项值设定
       --pov.Level_Value,pov.Level_Value_Application_Id,
       polv.Level_Value_Name
  from FND_PROFILE_OPTIONS po, --User profile options
       FND_PROFILE_OPTIONS_TL pot, --Translations for FND_PROFILE_OPTIONS
       FND_PROFILE_OPTION_VALUES pov, --Values of user profile options defined at different profile levels
       PROFILE_OPTION_LEVEL pol,
       PROFILE_OPTION_LEVEL_VALUE polv
  where po.Profile_Option_Name = pot.Profile_Option_Name and pot.Language = 'ZHS'
    and po.Application_Id = pov.Application_Id and po.Profile_Option_Id = pov.Profile_Option_Id
    and pov.Level_Id = pol.Level_Id
    and (pov.Level_Id = polv.Level_Value_Type(+)
      and NVL(pov.Level_Value,0) = polv.Level_Value_Id(+)
      and NVL(pov.Level_Value_Application_Id,0) = polv.Level_Value_Application_Id(+))
    
    --按 [配置文件选项名称] 查询
    --and pot.User_Profile_Option_Name = 'MO: Operating Unit'
    --and pov.Profile_Option_Value = (select Organization_Id from Hr_Organization_Units_v where Name = 'Vision China')
    
    --按 [地点] 查询
    --and pol.Level_Name = 'Site'
    
    --按 [应用] 查询
    --and pol.Level_Name = 'Application'
    --and polv.Level_Value_Name = N'订单分录'
    
    --按 [职责] 查询
    --and pol.Level_Name = 'Responsibility'
    --and polv.Level_Value_Name = 'Receivables, Vision China'
    
    --按 [服务器] 查询
    --and pol.Level_Name = 'Server'
    --and polv.Level_Value_Name = 'EBS'
    
    --按 [组织] 查询
    --and pol.Level_Name = 'Organization'
    --and polv.Level_Value_Name = 'Vision Operations'
    
    --按 [用户] 查询
    and pol.Level_Name = 'User'
    and polv.Level_Value_Name = 'SYSADMIN'

  order by po.Profile_Option_Id,pol.Level_Id,pov.Level_Value_Application_Id,pov.Level_Value;

with

PROFILE_OPTION_LEVEL as ( --定义配置文件 [层级] 子查询

select 10001 Level_Id, 'Site' Level_Name from dual union

select 10002 Level_Id, 'Application' Level_Name from dual union

select 10003 Level_Id, 'Responsibility' Level_Name from dual union

select 10004 Level_Id, 'User' Level_Name from dual union

select 10005 Level_Id, 'Server' Level_Name from dual union

select 10006 Level_Id, 'Organization' Level_Name from dual),

PROFILE_OPTION_LEVEL_VALUE as ( --定义配置文件 [层级值] 子查询

select '10002' as Level_Value_Type, --应用

Application_Id as Level_Value_Id,

Application_Name as Level_Value_Name,

0 as Level_Value_Application_Id from FND_APPLICATION_TL where Language='ZHS' union

select '10003' as Level_Value_Type, --职责

Responsibility_Id as Level_Value_Id,

Responsibility_Name as Level_Value_Name,

Application_Id as Level_Value_Application_Id from FND_RESPONSIBILITY_TL where Language='ZHS' union

select '10004' as Level_Value_Type, --用户

User_Id as Level_Value_Id,

User_Name as Level_Value_Name,

0 as Level_Value_Application_Id from FND_USER union

select '10005' as Level_Value_Type, --服务器

Node_Id as Level_Value_Id,

Node_Name as Level_Value_Name,

0 as Level_Value_Application_Id from FND_NODES union

select '10006' as Level_Value_Type, --组织

Organization_Id as Level_Value_Id,

Name as Level_Value_Name,

0 as Level_Value_Application_Id from HR_OPERATING_UNITS )

select po.Profile_Option_Id,po.Profile_Option_Name,po.User_Changeable_Flag,po.User_Update_Allowed_Flag,

pot.User_Profile_Option_Name,pot.Description,

pol.Level_Id,pol.Level_Name,

pov.Profile_Option_Value, --配置文件选项值设定

--pov.Level_Value,pov.Level_Value_Application_Id,

polv.Level_Value_Name

from FND_PROFILE_OPTIONS po, --User profile options

FND_PROFILE_OPTIONS_TL pot, --Translations for FND_PROFILE_OPTIONS

FND_PROFILE_OPTION_VALUES pov, --Values of user profile options defined at different profile levels

PROFILE_OPTION_LEVEL pol,

PROFILE_OPTION_LEVEL_VALUE polv

where po.Profile_Option_Name = pot.Profile_Option_Name and pot.Language = 'ZHS'

and po.Application_Id = pov.Application_Id and po.Profile_Option_Id = pov.Profile_Option_Id

and pov.Level_Id = pol.Level_Id

and (pov.Level_Id = polv.Level_Value_Type(+)

and NVL(pov.Level_Value,0) = polv.Level_Value_Id(+)

and NVL(pov.Level_Value_Application_Id,0) = polv.Level_Value_Application_Id(+))

--按 [配置文件选项名称] 查询

--and pot.User_Profile_Option_Name = 'MO: Operating Unit'

--and pov.Profile_Option_Value = (select Organization_Id from Hr_Organization_Units_v where Name = 'Vision China')

--按 [地点] 查询

--and pol.Level_Name = 'Site'

--按 [应用] 查询

--and pol.Level_Name = 'Application'

--and polv.Level_Value_Name = N'订单分录'

--按 [职责] 查询

--and pol.Level_Name = 'Responsibility'

--and polv.Level_Value_Name = 'Receivables, Vision China'

--按 [服务器] 查询

--and pol.Level_Name = 'Server'

--and polv.Level_Value_Name = 'EBS'

--按 [组织] 查询

--and pol.Level_Name = 'Organization'

--and polv.Level_Value_Name = 'Vision Operations'

--按 [用户] 查询

and pol.Level_Name = 'User'

and polv.Level_Value_Name = 'SYSADMIN'

order by po.Profile_Option_Id,pol.Level_Id,pov.Level_Value_Application_Id,pov.Level_Value;

标签：EBS

查询EBS配置文件选项设置的sql脚本

EBS创建会计科目提示“单据序号类别无效”

问题描述

问题重现