oracle-EBS-PL/sql 库存-查看产品现有量实时状态

关键就是这个通用表，这个表记录当前oracle数据库内的产品的当前的实际库存： mtl_onhand_quantities

只要发生变动，这个表的数据就会变，如果要看历史库存，这张表就无能为力了。

select mp.organization_code org_code, msi.segment1 item_number, msi.description, moq.transaction_quantity quantity			
,gmf_cmcommon.get_cmpt_cost(moq.inventory_item_id, moq.organization_id,moq.date_received , (select cost_type_id from CM_MTHD_MST where cost_mthd_code='PMAC'), null) unit_cost			
,moq.transaction_quantity  * gmf_cmcommon.get_cmpt_cost(moq.inventory_item_id, moq.organization_id, moq.date_received , (select cost_type_id from CM_MTHD_MST where cost_mthd_code='PMAC'), null) total_cost			
,moq.lot_number, moq.subinventory_code, msi1.description subinventory_desc			
,case when moq.locator_id is not null then mil.segment1||'.'||mil.segment2||'.'||mil.segment3||'.'||mil.segment4 else null end locator			
,mil.description locator_description--, moq.lot_epiration_date			
from mtl_onhand_quantities moq, mtl_system_items msi, mtl_parameters mp, mtl_item_locations mil, mtl_secondary_inventories msi1			
where moq.inventory_item_id=msi.inventory_item_id and moq.organization_id=msi.organization_id			
and moq.organization_id=mp.organization_id			
and moq.subinventory_code=msi1.secondary_inventory_name and moq.organization_id=msi1.organization_id			
and moq.locator_id=mil.inventory_location_id(+)			
and mp.organization_code='333'			
--and msi.segment1 = 'ADD211001250003002'
order by mp.organization_code, msi.segment1, moq.subinventory_code