物料领用控制方案

方案流程图

Alt text

效果提示

Alt text

物料领用控制方案

• 通过利用库存转储申请单，作为备料计划清单
• 限制库存发货单的物料领用总数不能超过备料计划清单数量
• 可建立多个备料计划清单，可设置不同期间的备料计划清单，根据库存发货单日期自动获取对应期间备料计划清单

自定义字段

• 表头字段：领用部门 U_LYBM
• 表头字段：单据类型 U_DJLX
• 行表字段：领用部门 U_LYBM

单据控制代码

----库存发货单：限制发货单不能超过备料清单
 IF @object_type='60'
 begin
 if @transaction_type='A'  OR @transaction_type = 'U'
	begin
DECLARE @ITEM nvarchar(20)
DECLARE @QTY NUMERIC(19, 6)

SELECT   top 1  @QTY =TA.未领数量, @ITEM = T1.ItemCode
FROM OIGE T0
	INNER JOIN IGE1 T1 ON T0.[DocEntry] = T1.[DocEntry]
	INNER JOIN (
	----取值领料数量
		SELECT T0.[DocDate], T0.[DocDueDate], T1.[ItemCode], T1.[U_LYBM], T1.Quantity
			, T1.Quantity - ISNULL(SUM(Ts.L), 0) AS '未领数量'
		FROM OWTQ T0
			INNER JOIN WTQ1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			INNER JOIN (
				SELECT T0.[U_LYBM], T0.[DocDate], T1.[ItemCode], T1.[Quantity] AS 'L'
				FROM OIGE T0
					INNER JOIN IGE1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			) TS
			ON Ts.[U_LYBM] = T1.[U_LYBM]
				AND Ts.[ItemCode] = T1.[ItemCode]
				AND TS.[DocDate] >= T0.[DocDate]
				AND TS.[DocDate] <= T0.[DocDueDate]
		WHERE T0.[U_DJLX] = '1'
		GROUP BY T0.[DocDate], T0.[DocDueDate], T1.[ItemCode], T1.[Quantity], T1.[U_LYBM]
	) TA
	ON TA.ItemCode = T1.ItemCode
		AND TA.U_LYBM = T0.U_LYBM
WHERE t0.DocEntry = @list_of_cols_val_tab_del
	AND T0.DocDate BETWEEN TA.DocDate AND TA.DocDueDate
	ORDER BY  ta.未领数量 
IF @QTY < 0
			begin
			select @error=1
			select @error_message=N'领用不能超过计划清单,超领物料为： '+@ITEM +N'   超出： '+cast(-@QTY as nvarchar(20))

 
		end
	end
 end

查询领料状态报表

SELECT T0.[DocNum],T1.[U_LYBM], T0.[DocDate] as'开始日期', T0.[DocDueDate] as'结束日期', T1.[ItemCode], T1.Quantity as'计划数量'
			,SUM(Ts.L) as'已领数量', T1.Quantity - ISNULL(SUM(Ts.L), 0) AS '未领数量'
		FROM OWTQ T0
			INNER JOIN WTQ1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			LEFT JOIN (
				SELECT T0.[U_LYBM], T0.[DocDate], T1.[ItemCode], T1.[Quantity] AS 'L'
				FROM OIGE T0
					INNER JOIN IGE1 T1 ON T0.[DocEntry] = T1.[DocEntry]
			) TS
			ON Ts.[U_LYBM] = T1.[U_LYBM]
				AND Ts.[ItemCode] = T1.[ItemCode]
				AND TS.[DocDate] >= T0.[DocDate]
				AND TS.[DocDate] <= T0.[DocDueDate]
		WHERE T0.[U_DJLX] = '1'
		GROUP BY T0.[DocNum],T1.[LineNum],T0.[DocDate], T0.[DocDueDate], T1.[ItemCode], T1.[Quantity], T1.[U_LYBM]
ORDER BY T0.[DocNum] DESC,T1.[LineNum]