ERP工单发料 先进先出 自动发料 T-SQL

发料的时候，管理层经常抱怨先到的料没有先用，发料员经常抱怨料号难打，仓库难打，数量难打……如何根据工单需求数量，自动拆解，先到的料先用，又省去发料员大量精力，这便成了一个问题。
以下代码，我想用在其他ERP是同样的原理，特别是后半部分的的循环，有两个退出的条件，一是工单发够料了，一是库存发完了。此段代码生成发料的预备数据，确认之后，直接过账即可。因为多单位问题，相互转换，代码量大大增加，不感兴趣的可以不看。

转载请注明出处，联系我: t39q@163.com
本人热衷于数据库技术及算法的研究，志同道合之士， 欢迎探讨

ALTER PROCEDURE [dbo].[AutoSTK_MTL_MultiUOM] 
@JobID INT,
@MtlSeq INT,
@AssemblySeq INT,
@company NVARCHAR(20),
@userID NVARCHAR(20),
@issueWarehouse NVARCHAR(8),
@FGissueQty DECIMAL(18,6)
AS--拿到工單物料,未發料數量DECLARE @JobMtl NVARCHAR(50) = '';DECLARE @UnissuedQty DECIMAL(22, 8) = 0; --未發料數量DECLARE @UnpostQty DECIMAL(22, 8) = 0;DECLARE @sql NVARCHAR(max);DECLARE @JobIUM NVARCHAR(6)=''; --工單需求數量的單位，不是BaseUOMDECLARE @issueRate DECIMAL(18,6)=1;--發料套數的比率--此處取工單未發料數量 需求數量除以損耗率，就是不帶損耗的數量--多單位處理思路（倉儲批拆解時，全部用統一的單位，即是庫存單位IUM來計算）if @FGissueQty=0beginset @issueRate=1;endelsebeginSELECT @issueRate=CAST(CAST(@FGissueQty AS DECIMAL(18,6))/jh.ProdQty AS DECIMAL(18,6)) FROM erp.JobHead jh WHERE jh.Company=@company AND jh.JobNum=(SELECT MAX(smn.jobNum) FROM dbo.STK_MTL_jobNum smn WHERE smn.AutoID=@JobID);endif @issueRate<0 or @issueRate>1beginreturn;endSELECT@JobMtl = jm.PartNum,@JobIUM=jm.IUM--用庫存單位除以工單需求單位得出單位換算比率，--再乘以已發料數量，得到已發的庫存單位數量 when part's ium is different from uom class id default uom,its wrong,@UnissuedQty=(CASE WHEN u.ConvOperator='*' THEN cast((jm.RequiredQty*u.ConvFactor / (1 + 0 / 100)*@issueRate --include scrap- CAST(jm.IssuedQty*u.ConvFactor AS DECIMAL(22,8))) as decimal(22,8))WHEN u.ConvOperator='/' THEN cast((jm.RequiredQty/u.ConvFactor / (1 + 0 / 100)*@issueRate --include scrap- CAST(jm.IssuedQty/u.ConvFactor AS DECIMAL(22,8))) as decimal(22,8)) END)FROM erp.JobMtl jmLEFT JOIN erp.JobAsmbl jaON jm.Company = ja.CompanyAND jm.JobNum = ja.JobNumAND jm.AssemblySeq = ja.AssemblySeqLEFT JOIN dbo.Part p ON jm.company = p.Company AND jm.PartNum = p.PartNum --找UOMClassIDLEFT JOIN erp.UOMConv u ON p.Company = u.Company AND p.UOMClassID=u.UOMClassID AND jm.ium=u.UOMCode --找轉換係數WHERE jm.MtlSeq = @MtlSeqAND jm.AssemblySeq = @AssemblySeqAND jm.JobNum = (SELECTJobNumFROM dbo.STK_MTL_jobNumWHERE AutoID = @JobIDAND Company = @company)AND jm.Company = @company;--PRINT ' UnissuedQty: '+CAST(@UnissuedQty AS NVARCHAR);--發料單未過賬數量--全部轉換為庫存單位SELECT @UnpostQty=(CASE WHEN u.ConvOperator='*' THEN ISNULL(SUM(ISNULL(smd.TranQty*u.ConvFactor, 0)), 0)WHEN u.ConvOperator='/' THEN ISNULL(SUM(ISNULL(smd.TranQty/u.ConvFactor, 0)), 0) END)FROM dbo.STK_MTL_Detail smdLEFT JOIN dbo.Part p ON smd.company = p.Company AND smd.PartNum = p.PartNum --找UOMClassIDLEFT JOIN erp.UOMConv u ON p.Company = u.Company AND p.UOMClassID=u.UOMClassID AND smd.UM=u.UOMCode --找轉換係數WHERE smd.STK_MTL_jobNum_AutoID = @JobIDAND smd.MtlSeq = @MtlSeqAND (smd.TranNum IS NULL OR LEN(TranNum) = 0)AND smd.AssemblySeq = @AssemblySeqGROUP BY u.ConvOperator;--PRINT ' UnpostQty: '+CAST(@UnpostQty AS NVARCHAR);--需要發料的數量--SET @UnissuedQty=@UnissuedQty-@UnpostQty;--此處更正，@UnpostQty會有其他發料單未過賬的數量--舉例來説，一個工單需求為11，其他發料單已發6，此單只需要發5，--如果@UnissuedQty為5，後面@UnpostQty<@UnissuedQty(6<5)會不進入循環--把庫存從大到小，批號小到大排序SELECTROW_NUMBER() OVER (ORDER BY pb.LotNum ASC, pb.OnhandQty DESC) ID,pb.Company,pb.PartNum,pb.WarehouseCode,pb.BinNum,pb.OnhandQty,pb.LotNum,pb.DimCode INTO #OnhandQtyFROM erp.PartBin pbWHERE 1 = 2;SET @sql = '';--庫存減去未過賬的發料，為OnhandQtySET @sql = N'SELECT ROW_NUMBER() OVER (ORDER BY pb.LotNum asc,pb.OnhandQty DESC) ID,pb.Company, pb.PartNum, pb.WarehouseCode, pb.BinNum,CASE WHEN u.ConvOperator=''*'' THEN isnull(pb.OnhandQty*u.ConvFactor-t4.TranQty,pb.OnhandQty*u.ConvFactor) WHEN u.ConvOperator=''/'' THEN isnull(pb.OnhandQty/u.ConvFactor-t4.TranQty,pb.OnhandQty/u.ConvFactor) END OnhandQty, pb.LotNum, pb.DimCode FROM erp.PartBin pbleft join (SELECTtt1.[PartNum],tt1.[LotNum],tt1.FromwarehouseCode,tt1.FromBinNum,CASE WHEN u.ConvOperator=''*'' THEN ISNULL(SUM(tt1.[TranQty]*u.ConvFactor), 0) WHEN u.ConvOperator=''/'' THEN ISNULL(SUM(tt1.[TranQty]/u.ConvFactor), 0) END TranQty,tt1.UMFROM [STK_MTL_Detail] tt1LEFT JOIN [dbo].[STK_MTL_jobNum] tt2ON tt1.STK_MTL_jobNum_AutoID = tt2.AutoIDAND tt1.Company = tt2.CompanyLEFT JOIN [dbo].[STK_MTL_Header] tt3ON tt2.STK_MTL_AutoID = tt3.AutoIDAND tt2.Company = tt3.CompanyLEFT JOIN Erp.JobMtl tt4ON tt1.MtlSeq = tt4.MtlSeqAND tt1.Company = tt4.CompanyAND tt2.jobNum = tt4.jobNumAND tt2.Company = tt4.CompanyAnd tt1.AssemblySeq=tt4.AssemblySeqLEFT JOIN dbo.Part p ON tt1.company = p.Company AND tt1.PartNum = p.PartNumleft JOIN erp.UOMConv u ON p.Company = u.Company AND p.UOMClassID=u.UOMClassID AND tt1.UM=u.UOMCodeWHERE (tt1.TranNum is NULL OR LEN(tt1.TranNum)=0)AND (tt3.type = 1 OR tt3.type = 3)AND tt1.Company = '''+@company+'''GROUP BY tt1.[PartNum],tt1.[LotNum],tt1.FromwarehouseCode,tt1.FromBinNum,tt1.UM,u.ConvOperator) t4 ON pb.PartNum = t4.PartNumAND pb.LotNum = t4.LotNumAND pb.WarehouseCode=t4.FromwarehouseCodeAND pb.BinNum=t4.FromBinNumleft JOIN dbo.Part p ON pb.Company = p.Company AND pb.PartNum = p.PartNumleft JOIN erp.UOMConv u ON pb.Company = u.Company AND p.UOMClassID = u.UOMClassID AND pb.DimCode=u.UOMCodeWHERE pb.PartNum=''' + @JobMtl + ''' AND pb.Company='''+@company+''' AND (CASE WHEN u.ConvOperator=''*'' THEN isnull(pb.OnhandQty*u.ConvFactor-t4.TranQty,pb.OnhandQty*u.ConvFactor) WHEN u.ConvOperator=''/'' THEN isnull(pb.OnhandQty/u.ConvFactor-t4.TranQty,pb.OnhandQty/u.ConvFactor) END)>0 '--isnull(pb.OnhandQty-t4.TranQty,pb.OnhandQty)>0--不可分割物料从SA0001仓库发料,无论是否指定仓库--DECLARE @RefCategory NVARCHAR(8) = '';--SELECT-- @RefCategory = RefCategory--FROM erp.Part--WHERE PartNum = @JobMtl--AND Company = @company;--IF (@RefCategory = 'MT0001')--BEGIN-- SET @sql = @sql + ' AND pb.WarehouseCode=''SA0001'' ';--END--ELSEIF (LEN(@issueWarehouse) > 0)BEGINSET @sql = @sql + ' AND pb.WarehouseCode=''' + @issueWarehouse + '''';ENDSET @sql = @sql + ' ORDER BY pb.LotNum asc,pb.OnhandQty DESC';--批號升序，庫存數量降序--PRINT @sql;INSERT INTO #OnhandQty EXEC (@sql);--SELECT * FROM #OnhandQty;--PRINT 'UnissuedQty:'+CAST(@UnissuedQty AS NVARCHAR);-- DECLARE @OnhandQty DECIMAL(22,8)=0;-- SELECT @OnhandQty=SUM(OnhandQty) -- FROM erp.PartBin pb WHERE pb.PartNum=@JobMtl AND pb.Company=@company AND pb.OnhandQty>0; --PRINT CAST(@OnhandQty AS NVARCHAR);DECLARE @EveryOnhandQty DECIMAL(22, 8) = 0;--庫存每筆可以發的數量DECLARE @FromWarehouseCode NVARCHAR(8) = '';DECLARE @FromBinNum NVARCHAR(10) = '';DECLARE @ToWarehouseCode NVARCHAR(8) = '';DECLARE @ToBinNum NVARCHAR(10) = '';DECLARE @TranReference NVARCHAR(MAX) = '';DECLARE @PartNum NVARCHAR(50) = '';DECLARE @LotNum NVARCHAR(30) = '';DECLARE @UM NVARCHAR(6) = '';DECLARE @TranQty DECIMAL(22, 8) = 0;DECLARE @ToJobSeqPartNum NVARCHAR(50) = '';--拿到入料倉庫，倉庫跟產綫，產綫跟工序，工序跟物料SELECT@ToWarehouseCode = r.InputWhse,@ToBinNum = r.InputBinNumFROM dbo.STK_MTL_jobNum smnLEFT JOIN erp.JobMtl jmON (smn.company = jm.CompanyAND smn.jobNum = jm.JobNum)LEFT JOIN Erp.JobOpDtl jodON (jm.Company = jod.CompanyAND jm.jobNum = jod.jobNumAND jm.RelatedOperation = jod.OprSeqAND jm.AssemblySeq = jod.AssemblySeq)LEFT JOIN Erp.Resource rON (jod.Company = r.CompanyAND jod.ResourceID = r.ResourceID)WHERE smn.AutoID = @JobIDAND jm.MtlSeq = @MtlSeqAND jm.AssemblySeq = @AssemblySeqAND smn.company = @company;--PRINT 'ToWarehouse :'+cast(@ToWarehouseCode as nvarchar(50))+' '+cast(@ToBinNum as nvarchar(50));DECLARE @i INT = 1,@n INT = 0;SELECT@n = COUNT(*)FROM #OnhandQty oq;--循環按庫存的行，將料發給工單--考慮2種情況--1.庫存夠發--跳出循環的條件：sum(發料單裏面的未過賬發料數量)>=@UnissuedQty未發料數量--2.庫存不夠發--跳出循環條件：已掃描所有發料行--PRINT 'UnpostQty: '+CAST(@UnpostQty AS NVARCHAR);--6--PRINT 'UnissuedQty: '+CAST(@UnissuedQty AS NVARCHAR);--5--PRINT 'i, n: '+CAST(@i AS NVARCHAR)+' '+CAST(@n AS NVARCHAR);--5WHILE (@UnpostQty < @UnissuedQty AND @i <= @n)--@UnpostQty<@UnissuedQty不能是<=，=時多執行一次循環，多發料--@i<=@n需要有=號，循環庫存最大行的發料BEGINSET @EveryOnhandQty = 0;SET @LotNum = '';--取庫存批號/數量等信息SELECT@EveryOnhandQty = OnhandQty --庫存單位的數量,@LotNum = LotNum,@FromWarehouseCode = WarehouseCode,@FromBinNum = BinNum,@LotNum = LotNum,@UM = DimCode --庫存明細的單位FROM #OnhandQtyWHERE ID = @i;--自動拆解多行時，發料數量=工單需求數量-未過賬數量SET @TranQty = @UnissuedQty - @UnpostQty;-- PRINT CAST(@i AS NVARCHAR)+' UnissuedQty: '+CAST(@UnissuedQty AS NVARCHAR);-- PRINT CAST(@i AS NVARCHAR)+' UnpostQty: '+CAST(@UnpostQty AS NVARCHAR);IF (@EveryOnhandQty >= @TranQty) --此行夠發，發料數量是工單的需求數量BEGIN--在發料表插入發料行--PRINT CAST(@EveryOnhandQty AS NVARCHAR)--PRINT 'ToWarehouse'+@ToWarehouseCode+' '+@ToBinNum;--SET @TranQty=@UnissuedQty;--都是以庫存單位計算的數量，要轉換為庫存明細單位數量（OnHandUM）SELECT @TranQty=(CASE WHEN u.ConvOperator='*' THEN  @TranQty/u.ConvFactor --這裡計算應該是反的，因為是庫存單位到其他單位WHEN u.ConvOperator='/' THEN  @TranQty*u.ConvFactor END)from erp.Part p LEFT JOIN erp.UOMConv u ON p.Company = u.Company AND p.UOMClassID = u.UOMClassID  --用UOMClassID去做鏈接，找到轉換係數WHERE  p.Company=@company AND p.PartNum=@JobMtl and u.UOMCode=@UM;INSERT INTO dbo.STK_MTL_Detail (STK_MTL_jobNum_AutoID, MtlSeq, company, FromWarehouseCode, FromBinNum, ToWarehouseCode, ToBinNum, TranReference, PartNum, LotNum, UM, TranQty, ToJobSeqPartNum, TranNum, AssemblySeq,IUM)VALUES (@JobID -- STK_MTL_jobNum_AutoID - bigint, @MtlSeq -- MtlSeq - int, @company -- company - nvarchar(20), @FromWarehouseCode -- FromWarehouseCode - nvarchar(8), @FromBinNum -- FromBinNum - nvarchar(10), @ToWarehouseCode --@ToWarehouseCode -- ToWarehouseCode - nvarchar(8), @ToBinNum --@ToBinNum -- ToBinNum - nvarchar(10), (@userID + '_' + CONVERT(VARCHAR(100), GETDATE(), 20)) -- TranReference - nvarchar(50), @JobMtl -- PartNum - nvarchar(50), @LotNum -- LotNum - nvarchar(30), @UM -- UM - nvarchar(6), @TranQty -- TranQty - decimal(22, 8), @JobMtl -- ToJobSeqPartNum - nvarchar(50), NULL -- TranNum - nvarchar(50), @AssemblySeq,@JobIUM);ENDELSE  --此行不夠發，就直接將此行的數量發給工單BEGIN--都是以庫存單位計算的數量，要轉換為庫存明細單位數量（OnHandUM）SELECT @EveryOnhandQty=(CASE WHEN u.ConvOperator='*' THEN  @EveryOnhandQty/u.ConvFactor --這裡計算應該是反的，因為是庫存單位到其他單位WHEN u.ConvOperator='/' THEN  @EveryOnhandQty*u.ConvFactor END)from erp.Part p LEFT JOIN erp.UOMConv u ON p.Company = u.Company AND p.UOMClassID = u.UOMClassID  --用UOMClassID去做鏈接，找到轉換係數WHERE  p.Company=@company AND p.PartNum=@JobMtl and u.UOMCode=@UM;--PRINT CAST(@EveryOnhandQty AS NVARCHAR)--SET @TranQty=@UnissuedQty;INSERT INTO dbo.STK_MTL_Detail (STK_MTL_jobNum_AutoID, MtlSeq, company, FromWarehouseCode, FromBinNum, ToWarehouseCode, ToBinNum, TranReference, PartNum, LotNum, UM, TranQty, ToJobSeqPartNum, TranNum, AssemblySeq,IUM)VALUES (@JobID -- STK_MTL_jobNum_AutoID - bigint, @MtlSeq -- MtlSeq - int, @company -- company - nvarchar(20), @FromWarehouseCode -- FromWarehouseCode - nvarchar(8), @FromBinNum -- FromBinNum - nvarchar(10), @ToWarehouseCode --@ToWarehouseCode -- ToWarehouseCode - nvarchar(8), @ToBinNum --@ToBinNum -- ToBinNum - nvarchar(10), (@userID + '_' + CONVERT(VARCHAR(100), GETDATE(), 20)) -- TranReference - nvarchar(50), @JobMtl -- PartNum - nvarchar(50), @LotNum -- LotNum - nvarchar(30), @UM -- UM - nvarchar(6), @EveryOnhandQty -- TranQty - decimal(22, 8), @JobMtl -- ToJobSeqPartNum - nvarchar(50), NULL -- TranNum - nvarchar(50), @AssemblySeq,@JobIUM);END--算一下未過賬的發料總數量是多少SELECT @UnpostQty=(CASE WHEN u.ConvOperator='*' THEN ISNULL(SUM(ISNULL(smd.TranQty*u.ConvFactor, 0)), 0)WHEN u.ConvOperator='/' THEN ISNULL(SUM(ISNULL(smd.TranQty/u.ConvFactor, 0)), 0) END)FROM dbo.STK_MTL_Detail smdLEFT JOIN dbo.Part p ON smd.company = p.Company AND smd.PartNum = p.PartNum --找UOMClassIDLEFT JOIN erp.UOMConv u ON p.Company = u.Company AND p.UOMClassID=u.UOMClassID AND smd.UM=u.UOMCode --找轉換係數WHERE smd.STK_MTL_jobNum_AutoID = @JobIDAND smd.MtlSeq = @MtlSeqAND (smd.TranNum IS NULL OR LEN(TranNum) = 0)AND smd.AssemblySeq = @AssemblySeqGROUP BY u.ConvOperator;--PRINT ' UnpostQty: '+CAST(@UnpostQty AS NVARCHAR);--SET @UnpostQty=9999999; --跳出循環的條件SET @i = @i + 1; --下一個庫存的行繼續循環 ENDdrop table #OnhandQty;