通过stock.move.line推算理论在手库存 - xiaohao0576/odoo-doc GitHub Wiki

更新调拨时间

如果是滞后生成的调拨单，stock_move和实际情况不一致，需要先把库存调拨时间调整到计划的时间

stock_picking.SCHEDULED_DATE的时间和销售订单中的date_order时间是一样的，所以调整stock_move的时间就以这个为准

-- 1. 更新 stock_picking 表的 date 和 date_done
UPDATE STOCK_PICKING
SET
	DATE = SCHEDULED_DATE,
	DATE_DONE = SCHEDULED_DATE
WHERE
	SCHEDULED_DATE IS NOT NULL
	AND CREATE_DATE > '2025-01-01'
	AND PICKING_TYPE_ID = 2;

-- 2. 更新 stock_move 表的 date
UPDATE STOCK_MOVE SM
SET
	DATE = SP.SCHEDULED_DATE
FROM
	STOCK_PICKING SP
WHERE
	SM.PICKING_ID = SP.ID
    AND SP.CREATE_DATE > '2025-01-01'
	AND SP.SCHEDULED_DATE IS NOT NULL
	AND SP.PICKING_TYPE_ID = 2;

-- 3. 更新 stock_move_line 表的 date
UPDATE STOCK_MOVE_LINE SML
SET
	DATE = SP.SCHEDULED_DATE
FROM
	STOCK_PICKING SP
WHERE
	SML.PICKING_ID = SP.ID
	AND SP.CREATE_DATE > '2025-01-01'
	AND SP.SCHEDULED_DATE IS NOT NULL
	AND SP.PICKING_TYPE_ID = 2;

-- 4.更新stock_valuation_layer的创建日期
UPDATE stock_valuation_layer svl
SET
	create_date = SM.DATE
FROM
	STOCK_move SM
WHERE
	svl.stock_move_id = sm.id
	AND SM.DATE > '2025-01-01';

SELECT
	PP.ID AS PRODUCT_ID,
	PP.BARCODE AS BARCODE,
	COALESCE(
		SUM(
			CASE
				WHEN SML.LOCATION_DEST_ID = 8 THEN SML.QUANTITY
				WHEN SML.LOCATION_ID = 8 THEN - SML.QUANTITY
				ELSE 0
			END
		),
		0
	) AS QTY_THEORETICAL
FROM
	PRODUCT_PRODUCT PP
	LEFT JOIN STOCK_MOVE_LINE SML ON PP.ID = SML.PRODUCT_ID
	AND SML.STATE = 'done'
	AND (
		SML.LOCATION_ID = 8
		OR SML.LOCATION_DEST_ID = 8
	)
	-- 这里指定截至日期的库存
	AND SML.DATE <= '2025-12-31 17:00:00'
WHERE
	PP.ID > 0  --这里可以指定具体的产品ID
GROUP BY
	PP.ID
ORDER BY
	PP.ID;

使用方法

• 把所有的location_id 和 location_dest_id都更换为需要查询的库存地点
• SML.DATE可以指定库存截至日期， 其实也可以指定开始日期，去查询一段时间的库存净变动
• PP.ID可以过滤出指定的产品

查询区间出入数量和净变动

SELECT
	PP.ID AS PRODUCT_ID,
	PP.BARCODE AS BARCODE,
	COALESCE(
		SUM(
			CASE
				WHEN SML.LOCATION_DEST_ID = 8 THEN SML.QUANTITY
				ELSE 0
			END
		),
		0
	) AS QTY_IN, -- 入库数量
	COALESCE(
		SUM(
			CASE
				WHEN SML.LOCATION_ID = 8 THEN SML.QUANTITY
				ELSE 0
			END
		),
		0
	) AS QTY_OUT, -- 出库数量
	COALESCE(
		SUM(
			CASE
				WHEN SML.LOCATION_DEST_ID = 8 THEN SML.QUANTITY
				WHEN SML.LOCATION_ID = 8 THEN - SML.QUANTITY
				ELSE 0
			END
		),
		0
	) AS QTY_NET_CHANGE -- 净变动
FROM
	PRODUCT_PRODUCT PP
	LEFT JOIN STOCK_MOVE_LINE SML ON PP.ID = SML.PRODUCT_ID
	AND SML.STATE = 'done'
	AND (
		SML.LOCATION_ID = 8
		OR SML.LOCATION_DEST_ID = 8
	)
	AND SML.DATE >= '2024-01-01 00:00:00' -- 开始时间
	AND SML.DATE <= '2025-12-31 23:59:59' -- 截止时间
WHERE
	PP.ID > 0
GROUP BY
	PP.ID
ORDER BY
	PP.ID;