本文主要是介绍易飞工单用料和实际用料差别分析存储过程，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
工单用料和实际用料差别主要体现在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
这篇关于易飞工单用料和实际用料差别分析存储过程的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
