本文介绍EBS系统中的核心表结构，包括采购、库存、销售、制造等模块的对象说明，提供了常用的SQL查询语句，如执行中的请求信息、定时任务详情、锁表信息等。
目录
Fnd_Concurrent_Worker_Requests
DBA_DEPENDENCIES:数据库对象依赖表 oracle 表-DBA_DEPENDENCIES_biqidaoer的专栏-优快云博客_all_dependencies oracle
PO
对象说明
po_headers_archive_all
PO归档表
po_lines_archive_all
PO归档表
po_line_locations_archive_all
PO归档表
INV
对象说明
Mtl_Material_Transactions
物料事务处理存储表
EBS 表 Mtl_Material_Transactions说明_biqidaoer的专栏-优快云博客
SO
挑库阶段会生成mtl_material_transactions
transaction_source_id=mtl_sales_orders.sales_order_id
trx_source_line_id=oe_order_lines_all.line_id
记录“销售订单挑库”阶段物料的存放位置发生变化的信息
对象说明
Oe_Order_Headers_All
订单头表
主键：header_id
外键：
Line_Type_Id = Oe_Transaction_Types_Tl.Transaction_Type_Id
Oe_Order_Lines_All
订单行表
主键：header_id
外键：
header=Oe_Order_Headers_All.header_id
Line_Type_Id = Oe_Transaction_Types_Tl.Transaction_Type_Id
Oe_Transaction_Types_Tl
订单头、行的类型\视图\增加多语言逻辑
mtl_sales_orders
记录订单编号的信息表
外键：
segment1=oe_order_headers_all.order_number
sales_order_id=mtl_material_transaction.transaction_source_id
wsh_delivery_details
记录订单的物料的发运明细信息，
等级阶段状态为R（Ready to release）
发货阶段为 S（Released to Warehouse）
挑库阶段状态为Y（Staged）。如果启用了序列号，记录会按单个序列号拆分
外键：
source_header_id=oe_order_headers_all.header_id
source_line_id=oe_order_lines_all.line_id
wsh_delivery_assignments
连接wsh_delivery_details和wsh_new_deliveries的信息表，
发放阶段可以连接wsh_delivery_details
外键：
delivery_detail_id=wsh_delivery_details.delivery_detail_id
wsh_new_deliveries
记录订单的交货信息表，
发放阶段为OP（Delivery is Open, has not been shipped）
外键：
source_header_id=oe_order_headers_all.header_id
wsh_picking_batches
记录订单的发放的信息表
外键：
order_header_id=oe_order_headers_all.header_id
发放
wsh_delivery_details
该表的记录在此阶段状态为S（Released to Warehouse）
wsh_new_deliveries
记录订单的交货信息表，此阶段为OP（Delivery is Open, has not been shipped）
外键：
source_header_id=oe_order_headers_all.header_id
wsh_delivery_assignments
连接wsh_delivery_details和wsh_new_deliveries的信息表
此阶段连接wsh_new_deliveries
外键：
delivery_id=wsh_new_deliveries.delivery_id
wsh_picking_batches
记录订单的发放的信息表
外键：
order_header_id=oe_order_headers_all.header_id
WIP
对象说明
Mfg_Lookups
Lookup_Type='WIP_SUPPLY'：为wip的供应类型,
对应Wip_Discrete_Jobs.Wip_Supply_Type
Lookup_Type='WIP_JOB_STATUS'：为wip的状态,
对应Wip_Discrete_Jobs.Status_Type
Lookup_Type='WIP_DISCRETE_JOB'：为wip的工单类型,
关联表：Wip_Discrete_Jobs
Bom_Bill_Of_Materials
bom头表
Bom_Inventory_Components_v
bom行表视图
Bom_Substitute_Components_v
bom替代料表
常用sql
bom头信息
SELECT Bom.Bill_Sequence_Id
,Bom.Alternate_Bom_Designator
,Msib_Bom.Item_Type Ass_Item_Type
FROM Apps.Bom_Bill_Of_Materials Bom
,Mtl_System_Items_b Msib_Bom
WHERE 1 = 1
AND Bom.Organization_Id = Msib_Bom.Organization_Id
AND Bom.Assembly_Item_Id = Msib_Bom.Inventory_Item_Id
AND Bom.Assembly_Item_Id = Pn_Alt_Bom_Item
AND Bom.Organization_Id = Pn_Parent_Organization_Id
AND Bom.Alternate_Bom_Designator IS NOT NULL;bom组件信息
SELECT Bic.Component_Item_Id
,Bic.Component_Sequence_Id
,Bom.Organization_Id
,Bom.Assembly_Item_Id
,Nvl(Bom.Alternate_Bom_Designator, Gv_Null_Char) Alternate_Bom_Designator
,Bic.Item_Num
,Bic.Operation_Seq_Num
,Bic.Component_Quantity
,Bic.Primary_Uom_Code
,Bic.Wip_Supply_Type
,Bic.Component_Yield_Factor
,Bic.Disable_Date
,Msib_Bic.Item_Type Com_Item_Type
,Msib_Bic.Inventory_Item_Status_Code Com_Item_Status
,Msib_Bom.Item_Type Ass_Item_Type
,Bic.Attribute4
FROM Apps.Bom_Bill_Of_Materials Bom
,Apps.Bom_Inventory_Components_v Bic
,Mtl_System_Items_b Msib_Bic
,Mtl_System_Items_b Msib_Bom
WHERE 1 = 1
AND Bom.Organization_Id = Msib_Bom.Organization_Id
AND Bom.Assembly_Item_Id = Msib_Bom.Inventory_Item_Id
AND Bom.Organization_Id = Msib_Bic.Organization_Id
AND Bic.Component_Item_Id = Msib_Bic.Inventory_Item_Id
AND Bom.Bill_Sequence_Id = Bic.Bill_Sequence_Id
AND Bom.Organization_Id = Pn_Parent_Organization_Id
AND Nvl(Bom.Alternate_Bom_Designator, Gv_Null_Char) = Pv_Parent_Alt_Name
AND Bom.Assembly_Item_Id = Pn_Parent_Item_Id
AND SYSDATE BETWEEN Nvl(Bic.Effectivity_Date, SYSDATE - 1) AND
Nvl(Bic.Disable_Date, SYSDATE + 1)
ORDER BY Bic.Item_Num;bom替代料
SELECT t.Substitute_Component_Id
,t.Substitute_Item_Quantity
,t.Primary_Uom_Code
,Msib.Inventory_Item_Status_Code Com_Item_Status
,Msib.Item_Type
,Msib_Bom.Item_Type Ass_Item_Type
,Bom.Assembly_Item_Id
,Bom.Organization_Id
,Nvl(Bom.Alternate_Bom_Designator, Gv_Null_Char) Alternate_Bom_Designator
,Bic.Wip_Supply_Type
,Bic.Disable_Date
,Msib.Segment1 Item_Num
FROM Apps.Bom_Substitute_Components_v t
,Mtl_System_Items_b Msib
,Apps.Bom_Bill_Of_Materials Bom
,Apps.Bom_Inventory_Components_v Bic
,Mtl_System_Items_b Msib_Bom
WHERE 1 = 1
AND Bom.Organization_Id = Msib_Bom.Organization_Id
AND Bom.Assembly_Item_Id = Msib_Bom.Inventory_Item_Id
AND Bom.Bill_Sequence_Id = Bic.Bill_Sequence_Id
AND Bic.Component_Sequence_Id = t.Component_Sequence_Id
AND Msib.Organization_Id = Pn_Organization_Id
AND t.Substitute_Component_Id = Msib.Inventory_Item_Id
AND Bom.Organization_Id = Pn_Organization_Id
AND t.Component_Sequence_Id = Pn_Component_Sequence_Id;请求
对象说明
Fnd_Concurrent_Worker_Requests
运行的请求信息
Fnd_Concurrent_Queues_Tl
请求管理器信息
主键
Concurrent_Queue_Id
外键
Concurrent_Queue_Id=Fnd_Concurrent_Worker_Requests.Concurrent_Queue_Id
Fnd_Concurrent_Programs
记录用户申请请求信息，对应多语言表Fnd_Concurrent_Programs_Tl
主键
Concurrent_Program_Id
相关对象
Fnd_Concurrent_Programs_Vl：申请的请求“程序”信息
Fnd_Executables
用户注册请求的“可执行”的基本信息，对应多语言表Fnd_Executables_Tl
主键
Executable_Id、Application_Id,与Fnd_Concurrent_Programs关联
相关对象
Fnd_Executables_Vl：与多语言表组成的视图
Fnd_Application_Vl：应用相关信息表
Fnd_Executables_Form_v：form界面“可执行”的视图，由Fnd_Executables_Vl和Fnd_Application_Vl组成
常用sql
执行的请求信息查询
SELECT t.User_Concurrent_Program_Name Request_Name --请求名
,Decode(t.Status_Code, 'R', '运行中', 'Q', '待定', 'I', '无效') Request_Status --状态
,t.Status_Code Request_Status_Code
,To_Char(t.Request_Date, 'yyyy-mm-dd hh24:mi:ss') Request_Date --提交日期
,To_Char(Decode(Sign(t.Requested_Start_Date - t.Request_Date)
,-1
,t.Request_Date
,t.Requested_Start_Date)
,'yyyy-mm-dd hh24:mi:ss') Start_Date --执行日期
,Round((SYSDATE - Decode(Sign(t.Requested_Start_Date - t.Request_Date)
,-1
,t.Request_Date
,t.Requested_Start_Date)) * 24 * 60
,2) Execute_Time --"执行时长分钟"
,t.Requested_By
,Fu.User_Name Requested_By_Name --请求人
,(SELECT p.User_Concurrent_Program_Name
FROM Fnd_Concurrent_Worker_Requests p
WHERE p.Request_Id = t.Parent_Request_Id) Parent_Request --父请求
,(SELECT p.Status_Code
FROM Fnd_Concurrent_Worker_Requests p
WHERE p.Request_Id = t.Parent_Request_Id) Parent_Request_Status --父状态
,t.Concurrent_Program_Name Request_Code --请求简称
,Fcqt.User_Concurrent_Queue_Name Request_Queues_Name
,t.Parent_Request_Id Parent_Request_Id
,t.Argument_Text --请求参数
,t.Request_Id Request_Id
,t.Phase_Code
,t.Description
,t.Concurrent_Program_Id
,t.Program_Application_Id
,t.Concurrent_Queue_Id
,t.Queue_Application_Id
,/*ses.SID */1session_id
,/*ses.SERIAL#*/1 serial_id
,/*c.VALUE / 60 / 100*/1 cpu_p
FROM Fnd_Concurrent_Worker_Requests t
,Fnd_User Fu
,Fnd_Concurrent_Queues_Tl Fcqt
/*,V$session Ses
,V$process Pro
,V$sesstat c*/
WHERE 1 = 1
/* AND c.Statistic# = 12
AND c.Sid = ses.SID
AND Ses.Paddr = Pro.Addr
AND Pro.Spid = t.Oracle_Process_Id*/
AND Fcqt.Language = 'ZHS'
AND Fcqt.Concurrent_Queue_Id = t.Concurrent_Queue_Id
AND t.Requested_By = Fu.User_Id
AND (t.Phase_Code = 'P' OR t.Phase_Code = 'R')
AND t.Hold_Flag != 'Y'
AND t.Requested_Start_Date <= SYSDATE
AND ('' IS NULL OR ('' = 'B' AND t.Phase_Code = 'R' AND t.Status_Code IN ('I', 'Q')))系统在执行的定时请求查询
SELECT *
FROM (SELECT a.*
,COUNT(1) Over(PARTITION BY a.Concurrent_Program_Name ORDER BY a.Concurrent_Program_Name) Cut
,COUNT(1) Over(PARTITION BY a.Concurrent_Program_Name, a.Argument_Text ORDER BY a.Concurrent_Program_Name) Art_Cut
FROM (SELECT r.Request_Id
,p.User_Concurrent_Program_Name || CASE
WHEN p.Concurrent_Program_Name = 'FNDRSSUB' THEN
(SELECT ' - ' || s.User_Request_Set_Name
FROM Fnd_Request_Sets_Tl s
WHERE s.Application_Id = r.Argument1
AND s.Request_Set_Id = r.Argument2
AND s.Language = 'US')
WHEN p.Concurrent_Program_Name = 'ALECDC' THEN
(SELECT ' - ' || a.Alert_Name
FROM Apps.Alr_Alerts a
WHERE a.Application_Id = r.Argument1
AND a.Alert_Id = r.Argument2)
END Concurrent_Program_Name
,CASE
WHEN p.Concurrent_Program_Name != 'FNDRSSUB' AND
p.Concurrent_Program_Name != 'ALECDC' THEN
r.Argument_Text
END Argument_Text
,r.Requested_Start_Date Next_Run
,r.Hold_Flag On_Hold
,Decode(c.Class_Type
,'P'
,'Periodic'
,'S'
,'On Specific Days'
,'X'
,'Advanced'
,c.Class_Type) Schedule_Type
,CASE
WHEN c.Class_Type = 'P' THEN
Substr(c.Class_Info, 1, Instr(c.Class_Info, ':') - 1) ||
Decode(Substr(c.Class_Info, Instr(c.Class_Info, ':', 1, 1) + 1, 1)
,'N'
,' 分钟'
,'M'
,' 月'
,'H'
,' 小时'
,'D'
,' 天') || ' 一次'
END Frequency
,CASE
WHEN c.Class_Type = 'P' THEN
'Repeat every ' || Substr(c.Class_Info, 1, Instr(c.Class_Info, ':') - 1) ||
Decode(Substr(c.Class_Info, Instr(c.Class_Info, ':', 1, 1) + 1, 1)
,'N'
,' minutes'
,'M'
,' months'
,'H'
,' hours'
,'D'
,' days') ||
Decode(Substr(c.Class_Info, Instr(c.Class_Info, ':', 1, 2) + 1, 1)
,'S'
,' from the start of the prior run'
,'C'
,' from the completion of the prior run')
WHEN c.Class_Type = 'S' THEN
Nvl2(Dates.Dates, 'Dates: ' || Dates.Dates || '. ', NULL) ||
Decode(Substr(c.Class_Info, 32, 1), '1', 'Last day of month ') ||
Decode(Sign(To_Number(Substr(c.Class_Info, 33)))
,'1'
,'Days of week: ' || Decode(Substr(c.Class_Info, 33, 1), '1', 'Su ') ||
Decode(Substr(c.Class_Info, 34, 1), '1', 'Mo ') ||
Decode(Substr(c.Class_Info, 35, 1), '1', 'Tu ') ||
Decode(Substr(c.Class_Info, 36, 1), '1', 'We ') ||
Decode(Substr(c.Class_Info, 37, 1), '1', 'Th ') ||
Decode(Substr(c.Class_Info, 38, 1), '1', 'Fr ') ||
Decode(Substr(c.Class_Info, 39, 1), '1', 'Sa '))
END Schedule
,c.Date1 Start_Date
,c.Date2 End_Date
,c.Class_Info
,p.Concurrent_Program_Name Concurrent_Program_Name1
,p.User_Concurrent_Program_Name User_Concurrent_Program_Name1
,Operation_User.Op_User_Name
--,p.*
FROM Fnd_Concurrent_Requests r
,Fnd_Conc_Release_Classes c
--,Fnd_Concurrent_Programs_Tl p
,Fnd_Concurrent_Programs_Vl p
,(SELECT Release_Class_Id
,Substr(MAX(Sys_Connect_By_Path(s, ' ')), 2) Dates
FROM (SELECT Release_Class_Id
,Rank() Over(PARTITION BY Release_Class_Id ORDER BY s) a
,s
FROM (SELECT c.Class_Info
,l
,c.Release_Class_Id
,Decode(Substr(c.Class_Info, l, 1), '1', To_Char(l)) s
FROM (SELECT LEVEL l
FROM Dual
CONNECT BY LEVEL <= 31)
,Fnd_Conc_Release_Classes c
WHERE c.Class_Type = 'S')
WHERE s IS NOT NULL)
CONNECT BY PRIOR (a || Release_Class_Id) = (a - 1) || Release_Class_Id
START WITH a = 1
GROUP BY Release_Class_Id) Dates
,(SELECT Xdfv.Request_Code
,Xdfv.Op_User_Name
FROM Apps.Xxcus_Dev_Function_v Xdfv
WHERE Xdfv.Function_Type = 'REQUEST') Operation_User
WHERE 1 = 1
AND p.Concurrent_Program_Name = Operation_User.Request_Code(+)
AND r.Phase_Code = 'P'
AND c.Application_Id = r.Release_Class_App_Id
AND c.Release_Class_Id = r.Release_Class_Id
AND Nvl(c.Date2, SYSDATE + 1) > SYSDATE
AND c.Class_Type IS NOT NULL
AND p.Concurrent_Program_Id = r.Concurrent_Program_Id
AND p.Application_Id = r.Program_Application_Id
--AND p.Language = Userenv('LANG')
--AND p.Language = 'US'
AND Dates.Release_Class_Id(+) = r.Release_Class_Id
AND r.Hold_Flag = 'N'
/*and p.CONCURRENT_PROGRAM_NAME = 'FNDRSSUB'
AND p.User_Concurrent_Program_Name = 'XXPLM Item/BOM/ECO Import Set (报表集(1))'
AND (p.User_Concurrent_Program_Name = 'Report Set' OR
p.User_Concurrent_Program_Name = 'Check Periodic Alert')*/
ORDER BY p.User_Concurrent_Program_Name
,r.Argument_Text
,On_Hold
,Next_Run) a) x
ORDER BY x.Cut DESC
,x.Concurrent_Program_Name
,Argument_Text
DBA
对象说明
Dba_Hist_Sqlstat：AWR
oracle sql执行信息表，可以挖掘oracle sql的执行信息
参考：Oracle AWR中常用到的几个SQL语句_biqidaoer的专栏-优快云博客
Dba_Hist_Snapshot：
oracle 执行信息表的时间表，与Dba_Hist_Sqlstat关联可以按照时间查找
Dba_Hist_Sqltext：sql历史
oracle sql的历史信息表
V$sqltext：sql执行信息表
sql执行信息表
V$session：会话表
V$process：进程表
DBA_DEPENDENCIES:数据库对象依赖表 oracle 表-DBA_DEPENDENCIES_biqidaoer的专栏-优快云博客_all_dependencies oracle
常用profiles
EBS 获取profiles的值_biqidaoer的专栏-优快云博客
常用sql
执行中的请求对应的sql
SELECT Sql_Text.Sql_Text
,Fcr.Request_Id
,Sql_Text.Piece
,Sql_Text.Sql_Id
FROM V$sqltext Sql_Text
,V$session Ses
,V$process Pro
,Fnd_Concurrent_Requests Fcr
WHERE Sql_Text.Sql_Id = Ses.Sql_Id
AND Ses.Paddr = Pro.Addr
AND Pro.Spid = Fcr.Oracle_Process_Id;ERP锁表信息
SELECT --Se1.Seconds_In_Wait AS 被阻塞的等待时间秒
Round((Se1.Seconds_In_Wait / 60), 2) Wait_Mi -- 等待分
--,Se1.Inst_Id AS 被阻塞的会话节点
--,Se2.Inst_Id AS 罪魁祸首节点
,Ob.Object_Name Lock_Table -- 被锁表
,Se1.Sid Obstruct_Sid --被阻会话
,Se1.Serial# Obstruct_Serial --被阻序号
,Se1.Client_Identifier Obstruct_User --被阻用户
,Se2.Client_Identifier Criminal_User --主阻用户
,Se2.Sid Criminal_Sid -- 主阻会话
,Se2.Serial# Criminal_Serial --主阻序号
,Se1.Program Obstruct_Program --被阻程序
,Se1.Module Obstruct_Module --被阻模块
,Decode(Se1.Action
,'Concurrent Request'
,'报表-' || (SELECT Fcpt.User_Concurrent_Program_Name
FROM Fnd_Concurrent_Programs Fcp
,Fnd_Concurrent_Programs_Tl Fcpt
WHERE 1 = 1
AND Fcpt.Language = 'ZHS'
AND Fcp.Concurrent_Program_Id = Fcpt.Concurrent_Program_Id
AND Fcp.Concurrent_Program_Name = Se1.Module)
,Decode(Sign(Instr(Se2.Module, 'frm'))
,1
,'功能-' || (SELECT Fffv.User_Function_Name
FROM Fnd_Form_Functions_Vl Fffv
WHERE Fffv.Function_Name =
(SELECT Substr(Se2.Module, Instr(Se2.Module, ':', -1) + 1, 100)
FROM Dual))
,'')
,'') Obstruct_Function --被阻功能
,Se2.Program Criminal_Program --主阻程序
,Se2.Module Criminal_Module --主阻模块
,Decode(Se2.Action
,'Concurrent Request'
,'报表-' || (SELECT Fcpt.User_Concurrent_Program_Name
FROM Fnd_Concurrent_Programs Fcp
,Fnd_Concurrent_Programs_Tl Fcpt
WHERE 1 = 1
AND Fcpt.Language = 'ZHS'
AND Fcp.Concurrent_Program_Id = Fcpt.Concurrent_Program_Id
AND Fcp.Concurrent_Program_Name = Se2.Module)
,Decode(Sign(Instr(Se2.Module, 'frm'))
,1
,'功能-' || (SELECT Fffv.User_Function_Name
FROM Fnd_Form_Functions_Vl Fffv
WHERE Fffv.Function_Name =
(SELECT Substr(Se2.Module, Instr(Se2.Module, ':', -1) + 1, 100)
FROM Dual))
,'')
,'') Criminal_Function --主阻功能
,Se2.Machine Criminal_Machine
,Se2.Status Criminal_Status
FROM Gv$session Se1
, --被阻塞的会话
Gv$session Se2
, --罪魁祸首
Dba_Objects Ob
, --表
Gv$locked_Object Lck --锁
WHERE Se1.Blocking_Session_Status = 'VALID'
AND Se1.Blocking_Instance > 0
AND Se1.Blocking_Session > 0
AND Se1.Blocking_Session = Se2.Sid
AND Se1.Sid = Lck.Session_Id
AND Ob.Object_Id = Lck.Object_Id
/*ORDER BY Se1.Seconds_In_Wait
,Se2.Serial#
,Se1.Serial#*/
;
扫一扫
1575
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
