易飞工单用料和实际用料差别分析存储过程

本文链接：https://blog.youkuaiyun.com/sadleaflzh/article/details/80013375

本文介绍了一种通过SQL脚本实现的工单用料分析方法，旨在比较工单的实际用料与BOM（物料清单）用料之间的差异。通过对特定时间段内的工单进行筛选，并针对未在BOM中列出的物料进行详细记录，进而分析这些差异的原因。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

工单用料和实际用料差别主要体现在BOM不发料有点多，而BOM表里没有做成直接材料，另外就是工单变更，那就什么可能也会发生，协助分析工单用料分析和工单用料分析表差不多吧，但应该简洁快
ALTER PROC [dbo].[MOCYL] (@STARTTIME VARCHAR(8),@ENDTIME VARCHAR(8))
AS
BEGIN
DECLARE @TA001 VARCHAR(8)
DECLARE @TA002 VARCHAR(20)
DECLARE @TMPPH VARCHAR(60)
DECLARE @TMPDW VARCHAR(8)
DECLARE @YL NUMERIC(18,8)
if exists(select * from tempdb..sysobjects where id=object_id('tempdb..##tempfy'))
drop table ##tempyl
create table ##tempyl
(
TTA001 varchar(8),
TTA002 VARCHAR(20),
PH VARCHAR(20),
--PM VARCHAR(60),
--GG VARCHAR(100),
DW VARCHAR(8),
SJYL varchar(200),
BZYL varchar(200)

)
DECLARE GDDHLB CURSOR FOR

SELECT TA001,TA002 FROM MOCTA
WHERE TA003>=@STARTTIME AND TA003<=@ENDTIME AND TA001 LIKE '51%' AND TA011='Y' AND TA013='Y' AND TA030='1'
OPEN GDDHLB
FETCH NEXT FROM GDDHLB INTO @TA001,@TA002
WHILE @@FETCH_STATUS=0
BEGIN
INSERT INTO ##tempyl
SELECT TB001,TB002,TB003,TB007,TB005,0 FROM MOCTB
WHERE TB001=@TA001 AND TB002=@TA002 AND TB003 NOT IN (SELECT MD003 FROM BOMMD WHERE MD001 IN(SELECT TA006 FROM MOCTA WHERE TA001=@TA001 AND TA002=@TA002))

DECLARE TMPYLL CURSOR FOR SELECT MD003,MB004,CONVERT (NUMERIC(18,8),CEILING(MD006/MD007*(1+MD008)*TA015)) AS YL FROM BOMMD
LEFT JOIN INVMB ON MD003=MB001
LEFT JOIN MOCTA ON TA006=MD001
WHERE TA001=@TA001 AND TA002=@TA002 AND MD017='1'
--WHERE TA001=@TA001 AND TA002=@TA002
OPEN TMPYLL
FETCH NEXT FROM TMPYLL INTO @TMPPH,@TMPDW,@YL
WHILE @@FETCH_STATUS=0
BEGIN
FETCH NEXT FROM TMPYLL INTO @TMPPH,@TMPDW,@YL
INSERT INTO ##tempyl
SELECT TB001,TB002,TB003,TB007,TB005,@YL
--SELECT TB001,TB002,TB003,TB005,TB007
FROM MOCTB WHERE TB001=@TA001 AND TB002=@TA002 AND TB003=@TMPPH AND TB007=@TMPDW
AND TB005<>@YL
END
CLOSE TMPYLL
DEALLOCATE TMPYLL
FETCH NEXT FROM GDDHLB INTO @TA001,@TA002
END
CLOSE GDDHLB
DEALLOCATE GDDHLB
SELECT * FROM ##tempyl
END