欠料表之ERP供应与需求拆解（SQL存储过程）

Opal ·

更新时间:2024-11-13

· 780 次阅读

这是一张欠料表，给生产计划用来催采购单用的，需求由早到晚，供应由早到晚（库存在最前）来满足需求，难点在于一笔供应不能满足需求的时候，需要对需求进行多行的拆解，而且拆解多少行还是不定的，用在Epicor系统，数据库是SQL2016，我认为用在其他系统亦是一样的道理。
转载请注明出处，联系我: t39q@163.com
本人热衷于数据库技术及算法的研究，志同道合之士，欢迎探讨

採購單號在後面，截圖不到。
在这里插入图片描述

Create proc [dbo].[proc_UnderStockQuery2]
@company nvarchar(20),
@PartNumIn NVARCHAR(50)
as
--欠料表
WITH r
AS(
SELECT pd.Company,pd.PartNum,ISNULL(pd.DueDate,'2099-12-31') DueDate,pd.RequirementFlag Require
  ,IIF(pd.RequirementFlag=1,pd.Quantity*(-1), pd.Quantity)  Quantity,pd.JobNum
  ,pd.PONum,pd.POLine,pd.PORelNum
  ,SourceFile,cast(pr.DueDate as nvarchar(50)) PODueDate
  FROM erp.PartDtl pd
  left join erp.PORel pr on pd.Company=pr.Company AND pd.PONum=pr.PONum and pd.POLine=pr.POLine and pd.PORelNum=pr.PORelNum  
  left join erp.Part p on pd.company=p.company and pd.PartNum=p.PartNum
WHERE pd.Company=@company and SourceFile not in ('OR') 
  and p.TypeCode='P' AND pd.PartNum=@PartNumIn
    UNION ALL
  SELECT pb.Company,pb.PartNum,'1900-01-01',0,sum(pb.OnhandQty) Quantity,'',0,0,0,'ST',''
  FROM erp.PartBin pb 
  left join erp.Part p on pb.company=p.company and pb.PartNum=p.PartNum
  WHERE pb.Company=@company and p.TypeCode='P' AND pb.PartNum=@PartNumIn
  GROUP BY pb.Company,pb.PartNum
--    UNION ALL
--  SELECT rd.Company,rd.PartNum,'1950-01-01',0,SUM(rd.OurQty) Quantity,'',0,0,0,'IQC',''
--    FROM erp.RcvDtl rd
--  WHERE rd.Company=@company AND rd.InspectionPending=1 AND rd.PartNum=@PartNumIn
--  GROUP BY rd.Company,rd.PartNum
    UNION ALL
  SELECT rd.Company,rd.PartNum,rd.ArrivedDate,0,rd.OurQty,'',rd.PONum, rd.POLine, rd.PORelNum,'IQC',''
    FROM erp.RcvDtl rd
  WHERE rd.Company=@company AND rd.InspectionPending=1 AND rd.PartNum=@PartNumIn
),
r2
AS
  (
  SELECT ROW_NUMBER() OVER(ORDER BY r.DueDate) RowID,r.*
    ,isnull(cast(jh.StartDate as nvarchar(50)),'') JobStartDate
	,isnull(cast(jh.DueDate as nvarchar(50)),'') JobDueDate,jh.PartNum JobPartNum FROM r
  left JOIN erp.JobHead jh ON r.Company = jh.Company AND r.JobNum = jh.JobNum
  )
  select * into #r31 from r2; 
--取出需求
SELECT #r31.*
,sum(Quantity) OVER (PARTITION BY PartNum ORDER BY RowID) Banlance
,sum(Quantity) OVER (PARTITION BY PartNum ORDER BY RowID) Banlance2
,sum(Quantity) OVER (PARTITION BY PartNum ORDER BY RowID) Banlance3
,isnull(v.VendorID,'') VendorID,isnull(v.Name,'') [Name]
into #r3
FROM #r31
left JOIN erp.POHeader p ON #r31.Company = p.Company AND #r31.PONum = p.PONum
LEFT JOIN erp.Vendor v ON p.Company = v.Company AND p.VendorNum = v.VendorNum
where #r31.Require=1
 ORDER BY RowID;
--取出供應
select * into #Supply from #r31 where Require=0 order by PartNum,DueDate asc;
--SELECT * FROM #Supply;
select ROW_NUMBER() over (order by t0.PartNum,DueDate) ID
,ROW_NUMBER() over (Partition by t0.PartNum order by t0.PartNum,DueDate) PartSeq
,t0.Company,t0.PartNum,DueDate,Require,Quantity
,Banlance,Banlance2,Banlance3,SourceFile
,JobNum,isnull(JobPartNum,'') JobPartNum,JobStartDate,JobDueDate
,PONum
,POLine,PORelNum,isnull(PODueDate,'') PODueDate
  ,VendorID
  ,Name
--,isnull(PO编号_1000,'') PONum_1000, isnull(PO行_1000,'') POLine_1000
--,isnull(t1.供应商ID_1000,'') VendorID_1000,isnull(t1.供应商名称_1000,'') Name_1000
,pp.PersonID
into #Require
 from #r3 t0
left join erp.partplant pp on t0.company=pp.company and t0.PartNum=pp.PartNum
-- left join #InternalCompanyPO t1 on t0.PONum=t1.[PO编号_2000] and t0.POLine=t1.[PO行_2000]
where t0.PartNum in(select PartNum from #r3 where Banlance<0)
order by t0.PartNum,DueDate;
alter table #Require add SupplyQty decimal(22,8);
--select * from #Require;
SELECT DISTINCT PartNum INTO #Require2 FROM #Require;
SELECT ROW_NUMBER() OVER (ORDER BY PartNum) ID,PartNum 
  INTO #Require3 FROM #Require2;
--select * from #Require3 order by ID;
declare @n int=0,@n2 int=0,@n3 INT=0,@i int=1,@i2 INT=1,@i3 INT=1
,@MinNegtiveLine INT=0,@MinNegtiveLine2 int=0,@debug INT=0;
declare @ID int=1,@ID2 INT;
select @n=count(*) from #Require3;
declare @PartNum nvarchar(50);
declare @SupplyQty decimal(22,8),@SumSupply decimal(22,8)
   ,@ReqQty decimal(22,8),@Banlance decimal(22,8)=0,@Banlance2 decimal(22,8)=0;
DECLARE @PONum INT,@POLine INT,@PORelNum INT;
DECLARE @SourceFile NVARCHAR(50);
SELECT PartSeq,Quantity,Banlance,Banlance Banlance2 into #Banlance  FROM #Require WHERE 1=2;
SELECT ROW_NUMBER() OVER (ORDER BY s.RowID) ID,* INTO #Supply2 FROM #Supply s WHERE 1=2;
SELECT * INTO #Require4 FROM #Require WHERE 1=2;
SELECT * INTO #Require5 FROM #Require WHERE 1=2;
while(@i<=@n)
begin
     SET @SumSupply=0;
     SELECT @PartNum=r.PartNum FROM #Require3 r WHERE r.ID=@i;
     TRUNCATE TABLE #Supply2;
     insert INTO #Supply2 SELECT ROW_NUMBER() OVER (ORDER BY s.RowID) ID,* 
     FROM #Supply s where s.PartNum=@PartNum;
     SELECT @n2=COUNT(*) FROM #Supply2 s;
     SET @i2=1;
     TRUNCATE TABLE #Require4;
     INSERT INTO #Require4 SELECT * FROM #Require WHERE PartNum=@PartNum;
     --SELECT * FROM #Require4;
     SELECT @n3=COUNT(*) FROM #Require4 r;
     SET @ID=1;
     SET @i3=1
     WHILE @i30
          BEGIN
             SELECT @id2=max(ID)+1 FROM #Require5;
          END
          ELSE
          BEGIN
          	 set @id2=1;
          END
     	    INSERT INTO #Require5 
            SELECT @id2,PartSeq
              ,Company,PartNum,DueDate,Require,Quantity,Banlance,Banlance2
           ,Banlance3,SourceFile,JobNum,JobPartNum,JobStartDate,JobDueDate,PONum
           ,POLine,PORelNum,PODueDate,VendorID,Name,PersonID,SupplyQty 
           FROM #Require4 WHERE ID=@i3;
          --拿到這一筆的需求
          SELECT @ReqQty=abs(Quantity) FROM #Require5 WHERE ID=@ID;
          --PRINT '@ReqQty 1-1:'+CAST(@ReqQty AS NVARCHAR(50));
          --找到第一筆供應
          SET @i2=1;
          WHILE @i2@ReqQty 
              BEGIN  
              	   --直接更新供應量
                   IF @ID=1 
                   begin
                       UPDATE #Require5 SET SupplyQty=ABS(@ReqQty),Banlance=Banlance+@SupplyQty
                        ,PONum=@PONum,POLine=@POLine,PORelNum=@PORelNum,SourceFile=@SourceFile 
                       WHERE ID=@ID;
                       SELECT @Banlance=r.Banlance FROM #Require5 r WHERE ID=@ID; 
                       SET @ID=@ID+1;
                   end
                   ELSE
                   BEGIN
                       UPDATE #Require5 SET SupplyQty=ABS(@ReqQty),Banlance=ABS(@SupplyQty)-@ReqQty
                        ,PONum=@PONum,POLine=@POLine,PORelNum=@PORelNum,SourceFile=@SourceFile 
                       WHERE ID=@ID;
                       SELECT @Banlance=r.Banlance FROM #Require5 r WHERE ID=@ID;
                       SET @ID=@ID+1;
                   END
                   --將#Supply2的供應減掉
                   UPDATE #Supply2 SET Quantity=Quantity-@ReqQty WHERE ID=@i2;
                   --PRINT N'@Banlance1 :'+cast(@Banlance AS NVARCHAR(MAX));
                   BREAK;
              END
              ELSE--一筆供應，不滿足需求的時候，需要把需求成拆成多行
              BEGIN
                  --原來的行，需求改為供應數量
                  --PRINT N'@Banlance1-1 :'+cast(@Banlance AS NVARCHAR(MAX));
                  IF @Banlance>=0 
                  BEGIN
                     UPDATE #Require5 SET Banlance=@Banlance-@ReqQty,SupplyQty=ABS(@SupplyQty)
                      ,PONum=@PONum,POLine=@POLine,PORelNum=@PORelNum,SourceFile=@SourceFile
                     WHERE ID=@ID;
                     UPDATE #Supply2 SET Quantity=Quantity-@SupplyQty WHERE ID=@i2;
                  end
                  --插入的另一行，工單需求是@ReqQty-@SupplyQty
                  --拆多少行要以這一行的結餘為正數為退出條件
                  --PRINT N'@ID 2-2：'+cast(@ID AS NVARCHAR(MAX));
                  SELECT @Banlance2=Banlance FROM #Require5 WHERE ID=@ID; 
                  --PRINT N'@Banlance2-2：'+cast(@Banlance2 AS NVARCHAR(MAX));
                  WHILE @Banlance20) is NOT null
                   begin
                     SELECT @SupplyQty=s.Quantity,@PONum=PONum,@POLine=POLine,@PORelNum=PORelNum
                      ,@SourceFile=SourceFile
                      FROM #Supply2 s 
                     WHERE ID=(SELECT min(ID) FROM #Supply2 WHERE Quantity>0);
                   end
                   ELSE
                   BEGIN
                      BREAK;
                   END
                    IF @SupplyQty0);
                      --更新Balnace2位新增行的Balnace
                      set @Banlance2=@SupplyQty+@Banlance2;
                      SET @ReqQty=@SupplyQty+@Banlance2;
                      SET @ID=@ID+1;
                      --SELECT * FROM #Supply2;
                    end
                    ELSE--最前行夠分
                    BEGIN
                       INSERT INTO #Require5
                      SELECT @ID,PartSeq,Company,PartNum,DueDate,Require
                      ,@Banlance2,@SupplyQty+@Banlance2,Banlance2,Banlance3,@SourceFile
                      ,JobNum,JobPartNum,JobStartDate,JobDueDate,@PONum
                      ,@POLine,@PORelNum,PODueDate,VendorID,Name,PersonID,ABS(@Banlance2)
                      FROM #Require4 WHERE ID=@i3;
                      --更新供應表的對應行，減去當前的供應（@Banlance2為負數，所以是加號）
                      UPDATE #Supply2 SET Quantity=Quantity+@Banlance2
                      WHERE ID=(SELECT min(ID) FROM #Supply2 WHERE Quantity>0);
                      --SELECT * FROM #Supply2;
                      --更新Banlance為當前
                      set @Banlance2=@SupplyQty+@Banlance2;--大於0退出拆行的循環
                      SET @ReqQty=@SupplyQty+@Banlance2;
                      SET @ID=@ID+1;
                    end
                    set @ReqQty=ABS(@ReqQty);
                    SELECT @Banlance=r.Banlance FROM #Require5 r WHERE ID=@ID;
                    --SET @Banlance2=9999; --測試時用
                  END
                  BREAK;--上面的if完了有break，分完了，就跳出來，不要再往下走了，又浪費我半小時
              end
              SET @i2=@i2+1;
          END    
          SET @i3=@i3+1;
     END
     SET @i=@i+1;
end
--SELECT * FROM #Supply2;
SELECT * FROM #Require5 r;
--PRINT N'@debug:'+cast(@debug AS NVARCHAR(MAX));
DROP TABLE #Require;
DROP TABLE #Require2;
DROP TABLE #Require3;
DROP TABLE #Require4;
DROP TABLE #Require5;
DROP TABLE #Supply;
DROP TABLE #Supply2;
DROP TABLE #r3;
DROP TABLE #r31;
DROP TABLE #Banlance;
--exec [proc_UnderStockQuery2] '2000','CN0-H2P200L70EP0-AX';

作者：qq_34677276

SQL erp sql存储过程

1024 个赞