分类目录归档：EBS_INV_SQL

EBS_INV_SQL、系统层级、经验闲谈

Oracle EBS 查询当前form lov对应的SQL

留下评论

1、登录到目标form界面，获取当前会话 Session SID
help->Aboat Oracle Applications

2、选择目标lov

3、通过以下SQL命令获取目标SQL

SELECT Sql_Text, s.Prev_Sql_Addr, s.Sql_Address
FROM V$session s, V$sqltext_With_Newlines t
WHERE s.Prev_Sql_Addr = t.Address(+)
AND s.Sid = 195;

将获取出来的sql从行号大到小整合到一起即可。

Select Organization_Id,
Organization_Code,
Organization_Name,
‘Organization’
From Org_Organization_Definitions
Order By Upper(Organization_Code)

 

EBS_INV_SQL

获取item onhand sql

留下评论

Select Moq.Organization_Id,
Mp.Organization_Code Org,
Msib.Segment1,
Msib.Description,
Msib.Inventory_Item_Id,
Msib.Item_Type,
Mln.Age,
Moq.Lot_Number,
Moq.Subinventory_Code Subinv,
Sum(Moq.Primary_Transaction_Quantity) Qty,
Mil.Segment1 || ‘-‘ || Mil.Segment2 || ‘-‘ || Mil.Segment3 || ‘-‘ || Mil.Segment4 Locator,
Flv.Meaning Item_Type_Code,
Xxcat_Get_Material_Vendor(Moq.Lot_Number, Msib.Inventory_Item_Id) Vendor_Name
From Mtl_Onhand_Quantities_Detail Moq,
Mtl_System_Items_b Msib,
Mtl_Parameters Mp,
Mtl_Item_Locations Mil,
Mtl_Lot_Numbers Mln,
Fnd_Lookup_Values_Vl Flv
Where 1 = 1
And Moq.Inventory_Item_Id = Msib.Inventory_Item_Id
And Moq.Organization_Id = Msib.Organization_Id
And Mp.Organization_Id = Msib.Organization_Id
And Moq.Subinventory_Code = Mil.Subinventory_Code(+)
And Moq.Locator_Id = Mil.Inventory_Location_Id(+)
And Mln.Inventory_Item_Id = Msib.Inventory_Item_Id
And Mln.Organization_Id = Msib.Organization_Id
And Mln.Lot_Number = Moq.Lot_Number
And Moq.Lot_Number Is Not Null
And Flv.Lookup_Type = ‘ITEM_TYPE’
And Flv.Lookup_Code = Msib.Item_Type
And Mp.Organization_Code = ‘CAT’
Group By Moq.Organization_Id,
Mp.Organization_Code,
Msib.Segment1,
Msib.Description,
Msib.Inventory_Item_Id,
Msib.Item_Type,
Mln.Age,
Moq.Lot_Number,
Moq.Subinventory_Code,
Moq.Inventory_Item_Id,
Mil.Segment1 || ‘-‘ || Mil.Segment2 || ‘-‘ || Mil.Segment3 || ‘-‘ || Mil.Segment4,
Flv.Meaning
Union
Select Moq.Organization_Id,
Mp.Organization_Code Org,
Msib.Segment1,
Msib.Description,
Msib.Inventory_Item_Id,
Msib.Item_Type,
Null Age,
Null Lot_Number,
Moq.Subinventory_Code Subinv,
Sum(Moq.Primary_Transaction_Quantity) Qty,
Null Locator,
Flv.Meaning Item_Type_Code,
Xxcat_Get_Material_Vendor(Null, Msib.Inventory_Item_Id) Vendor_Name
From Mtl_Onhand_Quantities_Detail Moq,
Mtl_System_Items_b Msib,
Mtl_Parameters Mp,
Mtl_Secondary_Inventories Msi,
Fnd_Lookup_Values_Vl Flv
Where Moq.Inventory_Item_Id = Msib.Inventory_Item_Id
And Moq.Organization_Id = Msib.Organization_Id
And Mp.Organization_Id = Msib.Organization_Id
And Msi.Secondary_Inventory_Name = Moq.Subinventory_Code
And Msi.Organization_Id = Mp.Organization_Id
And Moq.Lot_Number Is Null
And Flv.Lookup_Type = ‘ITEM_TYPE’
And Flv.Lookup_Code = Msib.Item_Type
And Mp.Organization_Code = ‘CAT’
Group By Moq.Organization_Id,
Mp.Organization_Code,
Msib.Segment1,
Msib.Description,
Msib.Inventory_Item_Id,
Msib.Item_Type,
Moq.Subinventory_Code,
Flv.Meaning,
Msi.Location_Id;