通过ITEM查找 批号和所在库位
作者: moonsoft(http://moonsoft.itpub.net)发表于: 2007.03.16 16:30
分类: 分销
出处: http://moonsoft.itpub.net/post/15182/272450
---------------------------------------------------------------
两种方法 :
mtl_onhand_quantities_detail
mtl_material_transactions
我只要找到批号相关的库位即可.
批次控制是在物料属性那里设置的。
对一个物料要么启用批次要么不启用批次。不可能一部分用批次，一部分不用批次吧
-- on_hand
SELECT loc.subinventory_code sub,
loc.segment1
|| '.'
|| loc.segment2
|| '.'
|| loc.segment3
|| '.'
|| loc.segment4 locator,
q.lot_number--ÅúºÅ, q.*
FROM mtl_onhand_quantities_detail q,
mtl_system_items_b msi,
mtl_item_locations loc
WHERE q.inventory_item_id = msi.inventory_item_id
AND q.locator_id = loc.inventory_location_id
AND msi.segment1 = 'tvsn00'
AND msi.segment2 = '002'
AND msi.segment3 = '002'
--by transaction , 要查询多个记录加减才可以 ,因为只有一笔记录,所以没有加减,当然这是通过统计数量, 但是依然可以查到哪个批号是哪个仓库
--如果transaction_quantity 为负,代表出库,反之则入库
/* Formatted on 2007/03/16 16:20 (Formatter Plus v4.8.7) */
SELECT loc.subinventory_code sub,
loc.segment1
|| '.'
|| loc.segment2
|| '.'
|| loc.segment3
|| '.'
|| loc.segment4 LOCATOR,
mt.transaction_quantity, mt.*
FROM mtl_material_transactions mt,
mtl_system_items_b msi,
mtl_item_locations loc
WHERE mt.inventory_item_id = msi.inventory_item_id
AND mt.locator_id = loc.inventory_location_id
AND msi.segment1 = 'tvsn00'
AND msi.segment2 = '002'
AND msi.segment3 = '002'
