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

本示例可从SQL Server数据库查得物料在最新采购日期的最小采购价格，即如果同一物料存在多条采购记录，首先取采购日期最大的，如果同一采购日期仍然存在多条记录，取采购价格最低的。（通过调整Order By也可实现其他需求，例如取最早日期或最高采购价格的记录）

--创建临时表，插入测试数据
if Object_id('Tempdb..#temp1') is not null drop table #temp1
create table #temp1(ItemNumber varchar(10),PurchDate date,PurchPrice decimal(10,2))
insert into #temp1(ItemNumber,PurchDate,PurchPrice)
select 'Item01', '2016-1-8',3.33 union all
select 'Item01', '2016-5-8',2.22 union all
select 'Item01', '2016-3-8',1.11 union all
select 'Item02', '2016-3-9',4.44 union all
select 'Item02', '2016-5-9',5.55 union all
select 'Item02', '2016-1-9',6.66 union all
select 'Item03', '2016-1-7',9.99 union all
select 'Item03', '2016-3-7',8.88 union all
select 'Item03', '2016-3-7',7.77

--查询所有信息
select * from #temp1 order by ItemNumber asc,PurchDate desc,PurchPrice asc

--使用row_number() over partition by 查询物料的最新采购价格，如果同一日期有多个价格取最小值
select *
from (
    select ItemNumber,PurchDate,PurchPrice,
    row_number() over(partition by ItemNumber order by ItemNumber asc,PurchDate desc,PurchPrice asc) row_num
    from #temp1) t1
where t1.row_num=1

--创建临时表，插入测试数据

if Object_id('Tempdb..#temp1') is not null drop table #temp1

create table #temp1(ItemNumber varchar(10),PurchDate date,PurchPrice decimal(10,2))

insert into #temp1(ItemNumber,PurchDate,PurchPrice)

select 'Item01', '2016-1-8',3.33 union all

select 'Item01', '2016-5-8',2.22 union all

select 'Item01', '2016-3-8',1.11 union all

select 'Item02', '2016-3-9',4.44 union all

select 'Item02', '2016-5-9',5.55 union all

select 'Item02', '2016-1-9',6.66 union all

select 'Item03', '2016-1-7',9.99 union all

select 'Item03', '2016-3-7',8.88 union all

select 'Item03', '2016-3-7',7.77

--查询所有信息

select * from #temp1 order by ItemNumber asc,PurchDate desc,PurchPrice asc

--使用row_number() over partition by 查询物料的最新采购价格，如果同一日期有多个价格取最小值

select *

from (

select ItemNumber,PurchDate,PurchPrice,

row_number() over(partition by ItemNumber order by ItemNumber asc,PurchDate desc,PurchPrice asc) row_num

from #temp1) t1

where t1.row_num=1

分类：Database

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

使用SQL查询物料最新采购价格的示例