ERP – 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;

详细图文：AX2012基于角色的权限设计2-权限结构-V1.0.pdf

本文重点结合Microsoft Dynamics AX 2012的权限体系和标准角色，分析如何对Role, Duty和Privilege进行合理地利用，从而设计出一种既便于初始权限收集，又利于后续维护的权限结构。初始权限收集的主要难点在于如何整理一个表述清晰的权限模板，并通过其与用户取得良好的沟通；后续权限维护的主要难点在于如何快速响应现有岗位的职责范围变动、如何将开发的新功能准确地赋予相应用户。一个合理的权限结构，应该对这两个方面有很好的支撑。

一、 基本权限结构

在AX系统中，权限的设定是基于角色的，下图展示了Role, Duty和Privilege各自包含的内容，及三者的相互关系。

图片见附件。

1. Role（角色）

包含Sub Role, Duty, Privilege和对Table, Form, Server Method的Permission设置。

2. Duty（职责）

只包含Privilege，相当于Privilege的分组。

3. Privilege（特权）

包含MenuItem和对Table, Form, Server Method的Permission设置。

继续阅读“AX2012基于角色的权限设计2-权限结构”

分类：ERP

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

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

问题描述

问题重现

AX2012基于角色的权限设计2-权限结构

AX2012基于角色的权限设计1-权限体系