其实这个需求还是比较简单的, 只是查询物料的库存量和成本信息, 具体sql如下。 问题在于mtl_material_transactions、mtl_transaction_accounts这两张表中的数据量超大, 导致查询需要执行很长时间。
如果可以使用mtl_onhand_quantities_detail替代mtl_material_transactions, 执行效率应该会有提升。 另外, 如果不使用mtl_transaction_accounts, 物料成本信息又该如何获取呢?
SELECT mmt1.organization_id,
mmt1.subinventory_code,
mmt1.inventory_item_id,
SUM(mmt1.primary_quantity),
mt1.cost1,
SUM(mmt1.primary_quantity) * mt1.cost1
FROM inv.mtl_material_transactions mmt1,
mtl_system_items msi,
(SELECT mt.organization_id,
mt.inventory_item_id,
decode(SUM(mt.qty),0,0,SUM(mt.amt) / SUM(mt.qty)) cost1
FROM (SELECT mmt.organization_id,
mmt.inventory_item_id,
mmt.primary_quantity qty,
0 amt
FROM inv.mtl_material_transactions mmt
WHERE mmt.costed_flag IS NULL
AND mmt.transaction_type_id <> 80
UNION ALL
SELECT mta.organization_id,
mta.inventory_item_id,
0,
mta.base_transaction_value
FROM inv.mtl_transaction_accounts mta
WHERE mta.accounting_line_type = 1
AND mta.base_transaction_value <> 0) mt
GROUP BY mt.organization_id,
mt.inventory_item_id) mt1
WHERE mmt1.organization_id = mt1.organization_id
AND mmt1.inventory_item_id = mt1.inventory_item_id
AND mmt1.transaction_type_id <> 80
AND mmt1.costed_flag IS NULL
AND mmt1.organization_id = msi.organization_id(+)
AND mmt1.inventory_item_id = msi.inventory_item_id(+)
AND mmt1.subinventory_code IS NOT NULL
GROUP BY mmt1.organization_id,
mmt1.subinventory_code,
mmt1.inventory_item_id,
mt1.cost1
HAVING SUM(mmt1.primary_quantity) <> 0;