MRP的计算与实现

       物料需求计划理论

     物料需求计划理论,也称做基本MRP.其最主要特点是,在传统的基础上引入了时间分段和反映产品结构的物料清单BOM(Bill Of    Materials),较好地解决了库存管理和生产控制中的难题,却按时按量得到所需要的物料.

     1 MRP计算模型

     MRP是对MPS(主生产计划)的展开,由于物料在BOM中所处的层次不同,物料需求的计算处理方式也不同,因此在展开MPS计算过程中,ERP系统引入了物料的"低层码"的概念.

     2 低层码(Low-Level Code)

     概念:物料的低层码是系统分配给物料清单上的每个物品一个从0到N的数字码.在产品结构中,最上层的层级为0,下一层部件的层级码则为1,依此类推.一个物品只能有一个MRP低层码,当一个物品在多个产品中所处的产品结构层次不同或即使处于同一产品结构中但却处于不同产品层次时,则取处在最低层的层级码作为该物品的低层码,也即取数字最大的层级码.

     作用:在展开MPS进行物料需求计算时,计算顺序是从上而下进行的,即从产品为0层次开始计算,按低层码顺序从低层码数字小的物料往低层码数字高的物料进行计算,当计算到该产品的某一层次(如1层),若低层码不同(物料低层码为2),则只计算层次高(低层码数字小)的物料,层级比计算层低(低层码数字大于计算的产品层次)的物料的计算结果(毛需求,净需求量)暂时存储起来,总的需求量可以汇总存储,但不进行MRP需求计算与原材料(或组件)的库存分配,这样可用的库存量优先分配给了处于最低层的物料,保证了时间上最先需求的物料先得到库存分配,避免了晚需求的物品提前下达计划并占用库存.因此低层码是MRP的计算顺序

     3 MRP的存储过程实现     

    基本逻辑

    净需求＝毛需求＋预计出－预计入－期初库存＋安全库存

 

CREATE  PROC [dbo].[SYS_MRP_RUN]
(
  @LONG INT,              --查看期间设定（天）
  @MRP_STDATE DATETIME,   --起始日期
  @MRP_ENDATE DATETIME,   --结束日期
  @SAFE_STOCK  bit=1,     --是否考虑安全库存
  @LEADTIME bit=1,        --是否考虑提前期
  @F_ITEMCODE NVARCHAR(50)='',
  @T_ITEMCODE NVARCHAR(50)='ZZZZZZZZZZZZZZ'--计划物料编码范围
)
AS
IF ISNULL(@T_ITEMCODE,'')=''
   SET @T_ITEMCODE='ZZZZZZZZZZZZZZ'
/*
设计项目：MRP存储过程
作者：陈锦杰
CREATE table SYS_MRP_REPORT                         --需求明细表
(
 ITEMCODE NVARCHAR(50) NOT NULL,                 --物料编码
    UNIT NVARCHAR(50),                              --单位
    LEADDATE NVARCHAR(20),                          --原料需求日期
    KIND NVARCHAR(4) DEFAULT 'B' NOT NULL,          --计划类型
    NEEDQTY NUMERIC(19,5) NOT NULL DEFAULT 0,       --毛需求数量
    QTY NUMERIC(19,5) NOT NULL DEFAULT 0,           --净需求数量
    STORE_QTY NUMERIC(19,5) NOT NULL DEFAULT 0,     --在库库存
 EXPIN NUMERIC(19,5) NOT NULL DEFAULT 0,         --在途数
    COUNT_M NUMERIC(19,5) NOT NULL DEFAULT 0,       --预计可用计算字段
    MEMO NVARCHAR(200)
)
*/
    --清除物料需求计算表
    TRUNCATE TABLE SYS_MRP_REPORT

 --计算物料的底层码
 UPDATE USR_MATRIAL set MTR_LOWNO=0
  UPDATE USR_BOM_D set BOMD_LEVER=1

  DECLARE @I INT,@J INT
  SELECT @I=1
  WHILE (@I<=20)--假设最大BOM层级不超过20
  BEGIN
   UPDATE USR_BOM_D set BOMD_LEVER= b.BOMD_LEVER+1 from USR_BOM_D as a,
    (SELECT * from USR_BOM_D) as b
  WHERE a.BOMD_TPDNO = b.BOMD_MTRNO
   SELECT @I = @I + 1
  END

 UPDATE USR_MATRIAL set MTR_LOWNO=A.MAX_LEVER
 FROM (SELECT BOMD_MTRNO,MAX(BOMD_LEVER) AS MAX_LEVER FROM USR_BOM_D GROUP BY BOMD_MTRNO) A
 WHERE MTR_NO=BOMD_MTRNO

 --销售产品需求
 INSERT SYS_MRP_REPORT(ITEMCODE,UNIT,KIND,LEADDATE,NEEDQTY,QTY)
 SELECT RDR1_ITEMCODE,MTR_UNIT,MTR_PLAN,CONVERT(CHAR(10),T.RDR1_DELIVERYDATE,120),RDR1_OPENQTY,RDR1_OPENQTY     
 FROM USR_MATRIAL T1 JOIN T_ORDR_D T ON T1.MTR_NO=T.RDR1_ITEMCODE 
 JOIN T_ORDR T2 ON T.RDR1_ID=T2.ORDR_ID 
 WHERE MTR_IST<>1 AND T2.ORDR_STATUS='O' AND T2.ORDR_APSTATUS='A' AND RDR1_OPENQTY>0
    AND CONVERT(CHAR(10),T.RDR1_DELIVERYDATE,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
    AND CONVERT(CHAR(10),T.RDR1_DELIVERYDATE,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
    AND RDR1_ITEMCODE>=ISNULL(@F_ITEMCODE,'') AND RDR1_ITEMCODE<=ISNULL(@T_ITEMCODE,'')

 --生产计划需求
 INSERT SYS_MRP_REPORT(ITEMCODE,UNIT,KIND,LEADDATE,NEEDQTY,QTY)
 SELECT SCDDZB_WLH,MTR_UNIT,MTR_PLAN,CONVERT(CHAR(10),SCDD_DQR,120),SCDDZB_YJHSL-SCDDZB_YFHSL,SCDDZB_YJHSL-SCDDZB_YFHSL 
 FROM USR_MATRIAL T1 JOIN USR_SCDDZB T ON T1.MTR_NO=T.SCDDZB_WLH 
 JOIN USR_SCDD T2 ON T.SCDDZB_SCDH=T2.SCDD_ID 
 WHERE T2.SCDD_STATUS=N'O' AND SCDDZB_YJHSL-SCDDZB_YFHSL>0
    AND CONVERT(CHAR(10),SCDD_DQR,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
    AND CONVERT(CHAR(10),SCDD_DQR,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
    AND SCDDZB_WLH>=ISNULL(@F_ITEMCODE,'') AND SCDDZB_WLH<=ISNULL(@T_ITEMCODE,'')

 --外发计划需求
 INSERT SYS_MRP_REPORT(ITEMCODE,UNIT,KIND,LEADDATE,NEEDQTY,QTY)
 SELECT WSCDZB_WLH,MTR_UNIT,MTR_PLAN,CONVERT(CHAR(10),WSCD_DQR,120),WSCDZB_YJHSL-WSCDZB_YFHSL,WSCDZB_YJHSL-WSCDZB_YFHSL     
 FROM USR_MATRIAL T1 JOIN USR_WSCDZB T ON T1.MTR_NO=T.WSCDZB_WLH 
 JOIN USR_WSCDD T2 ON T.WSCDZB_SCDH=T2.WSCD_ID 
 WHERE MTR_IST<>1 AND T2.WSCD_STATUS='O' AND WSCDZB_YJHSL-WSCDZB_YFHSL>0
    AND CONVERT(CHAR(10),WSCD_DQR,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
    AND CONVERT(CHAR(10),WSCD_DQR,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
    AND WSCDZB_WLH>=ISNULL(@F_ITEMCODE,'') AND WSCDZB_WLH<=ISNULL(@T_ITEMCODE,'')

    --合并相同物料相同日期的需求
    SELECT ITEMCODE,UNIT,KIND,LEADDATE,SUM(NEEDQTY) AS NEEDQTY,SUM(QTY) AS QTY INTO #TEMP_MRPI FROM SYS_MRP_REPORT GROUP BY ITEMCODE,UNIT,KIND,LEADDATE
    TRUNCATE TABLE SYS_MRP_REPORT
 INSERT SYS_MRP_REPORT (ITEMCODE,UNIT,KIND,LEADDATE,NEEDQTY,QTY) SELECT ITEMCODE,UNIT,KIND,LEADDATE,NEEDQTY,QTY FROM #TEMP_MRPI
    DROP TABLE #TEMP_MRPI

    --计算需求
 --底层码代表物料的层级,取出并使用最大低层码来循环计算
    DECLARE @LEADDATE NVARCHAR(20),@ITEMCODE NVARCHAR(20),@TEMP_COUNT NUMERIC(19,5)
    SELECT @I=0,@J=MAX(MTR_LOWNO) FROM USR_MATRIAL

    WHILE @I<@J
    BEGIN
             UPDATE SYS_MRP_REPORT SET COUNT_M=ISNULL(A.AMOUNT,0)+ISNULL(B.POQTY,0)+ISNULL(C.PDQTY,0)+ISNULL(D.WFQTY,0)-(CASE WHEN @SAFE_STOCK=1 THEN ISNULL(MTR_LEASTSTOR,0) ELSE 0 END)
             FROM SYS_MRP_REPORT INNER JOIN USR_MATRIAL ON ITEMCODE=MTR_NO AND MTR_LOWNO=@I
                          LEFT JOIN
                          (
                           SELECT ITEMID,sum(AMOUNT) as AMOUNT from T_STOCK
         WHERE STOREHOUS IN(SELECT L_WHSCODE FROM USR_OWHS WHERE L_NMRP=1) and AMOUNT>0
         GROUP BY ITEMID
                          ) A ON A.ITEMID=ITEMCODE
                          LEFT JOIN
                          (
                            SELECT POR1_ITEMCODE,SUM(POR1_UNRECEIVE*POR1_UNITGENE1) AS POQTY
       FROM  T_OPOR_D inner JOIN T_OPOR ON POR1_ID=OPOR_ID
       WHERE  OPOR_DOCCHECK=N'A' AND OPOR_STATUS=N'O'
         AND CONVERT(CHAR(10),POR1_DATE,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
         AND CONVERT(CHAR(10),POR1_DATE,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
       GROUP BY POR1_ITEMCODE
                           ) B ON B.POR1_ITEMCODE=ITEMCODE
                           LEFT JOIN
                           (
         SELECT SCDD_CPBH,SUM(SCDD_YJHSL-SCDD_WCSL) AS PDQTY     
         FROM USR_SCDD
         WHERE SCDD_STATUS='O'
            AND CONVERT(CHAR(10),SCDD_DQR,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
         AND CONVERT(CHAR(10),SCDD_DQR,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
         GROUP BY SCDD_CPBH
       ) C ON C.SCDD_CPBH=ITEMCODE                                            
                           LEFT JOIN     
                           (
                        SELECT WSCD_CPBH,sum(WSCD_YJHSL-WSCD_WCSL) as WFQTY  
                        FROM USR_WSCDD
                              WHERE WSCD_STATUS='O'
                                    AND CONVERT(CHAR(10),WSCD_DQR,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
                                    AND CONVERT(CHAR(10),WSCD_DQR,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
                              GROUP BY WSCD_CPBH
                            ) D ON D.WSCD_CPBH=ITEMCODE   
           
             --按照原料要求到期日期循环计算各个计划期的物料需求
    DECLARE mycur cursor for select DISTINCT LEADDATE,ITEMCODE from SYS_MRP_REPORT
                                                               INNER JOIN USR_MATRIAL ON ITEMCODE=MTR_NO AND MTR_LOWNO=@I
                                                               order by ITEMCODE,LEADDATE
    OPEN mycur
    FETCH next from mycur into @LEADDATE,@ITEMCODE
    WHILE (@@FETCH_STATUS = 0 )
    BEGIN 
   --计算净需求并
     UPDATE SYS_MRP_REPORT SET QTY=QTY-COUNT_M,COUNT_M=COUNT_M-QTY,@TEMP_COUNT=(CASE WHEN COUNT_M-QTY<0 THEN 0 ELSE COUNT_M-QTY END) WHERE ITEMCODE=@ITEMCODE AND LEADDATE=@LEADDATE AND COUNT_M<>0
   --更新预计可用量
               UPDATE SYS_MRP_REPORT set COUNT_M=ISNULL(@TEMP_COUNT,0) WHERE ITEMCODE=@ITEMCODE
                    IF @ITEMCODE='A003R100A12090'
                    select * from SYS_MRP_REPORT where ITEMCODE=@ITEMCODE AND LEADDATE=@LEADDATE
                   
     FETCH next from mycur into @LEADDATE,@ITEMCODE
    END
    CLOSE mycur
    DEALLOCATE mycur

             --按BOM展开新需求  
             --没有就增加                     
             INSERT SYS_MRP_REPORT(ITEMCODE,UNIT,KIND,LEADDATE,NEEDQTY,QTY)
             SELECT BOMD_MTRNO,B.MTR_UNIT,B.MTR_PLAN,CONVERT(CHAR(10),DATEADD(DD,-B.MTR_AHEADDATE,LEADDATE),120),ISNULL(QTY,0)*ISNULL(BOMD_QTY,0),ISNULL(QTY,0)*ISNULL(BOMD_QTY,0)
             FROM SYS_MRP_REPORT INNER JOIN USR_MATRIAL A ON ITEMCODE=A.MTR_NO AND A.MTR_LOWNO=@I AND KIND!='01' AND QTY>0
             INNER JOIN USR_BOM_D ON ITEMCODE=BOMD_TPDNO INNER JOIN USR_MATRIAL B ON BOMD_MTRNO=B.MTR_NO
    WHERE NOT EXISTS(SELECT ITEMCODE FROM SYS_MRP_REPORT WHERE ITEMCODE=BOMD_MTRNO AND LEADDATE=CONVERT(CHAR(10),DATEADD(DD,-B.MTR_AHEADDATE,LEADDATE),120))
             --有记录就累加
             UPDATE SYS_MRP_REPORT SET NEEDQTY=SYS_MRP_REPORT.NEEDQTY+T.NEEDQTY,QTY=SYS_MRP_REPORT.QTY+T.QTY FROM
             (
               SELECT BOMD_MTRNO,B.MTR_UNIT,B.MTR_PLAN,CONVERT(CHAR(10),DATEADD(DD,-B.MTR_AHEADDATE,LEADDATE),120) AS LEADDATE,ISNULL(QTY,0)*ISNULL(BOMD_QTY,0) AS NEEDQTY,ISNULL(QTY,0)*ISNULL(BOMD_QTY,0) AS QTY
               FROM SYS_MRP_REPORT INNER JOIN USR_MATRIAL A ON ITEMCODE=A.MTR_NO AND A.MTR_LOWNO=@I AND KIND!='01' AND QTY>0
               INNER JOIN USR_BOM_D ON ITEMCODE=BOMD_TPDNO INNER JOIN USR_MATRIAL B ON BOMD_MTRNO=B.MTR_NO
      WHERE EXISTS(SELECT ITEMCODE FROM SYS_MRP_REPORT WHERE ITEMCODE=BOMD_MTRNO AND LEADDATE=CONVERT(CHAR(10),DATEADD(DD,-B.MTR_AHEADDATE,LEADDATE),120)) 
             ) T
             WHERE T.BOMD_MTRNO=ITEMCODE AND T.LEADDATE=SYS_MRP_REPORT.LEADDATE

             SET @I=@I+1
    END

--过滤临时组合件
  DELETE SYS_MRP_REPORT FROM USR_MATRIAL WHERE MTR_NO=ITEMCODE and MTR_ISNP=0 and KIND='02'
--过滤虚拟工序物料
  DELETE SYS_MRP_REPORT FROM USR_MATRIAL WHERE MTR_NO=ITEMCODE and MTR_IST=1
--删除没有需求的物料
  DELETE SYS_MRP_REPORT WHERE QTY<=0

--查看期间设定
   DECLARE @DATE DATETIME,@acount INT
   SET @acount=1
   SELECT @DATE=min(LEADDATE) from SYS_MRP_REPORT
   WHILE (@acount>0)
   BEGIN
        UPDATE SYS_MRP_REPORT set LEADDATE=convert(char(10),@DATE,120) where convert(char(10),LEADDATE,120)>=convert(char(10),@DATE,120) and convert(char(10),LEADDATE,120)<convert(char(10),dateadd(DD,@LONG+1,@DATE),120)
        SET @acount=@@rowcount
        SET @DATE=convert(char(10),dateadd(DD,@LONG+1,@DATE),120)
   END
--汇总需求
 SELECT ITEMCODE,UNIT,KIND,CONVERT(CHAR(10),LEADDATE,120) AS LEADDATE,AVG(STORE_QTY) AS STORE_QTY,AVG(EXPIN) AS EXPIN,SUM(NEEDQTY) as NEEDQTY,SUM(QTY) AS QTY INTO #TEMP_MRPII FROM SYS_MRP_REPORT GROUP BY ITEMCODE,UNIT,KIND,CONVERT(CHAR(10),LEADDATE,120)
 TRUNCATE TABLE SYS_MRP_REPORT
 INSERT SYS_MRP_REPORT (ITEMCODE,UNIT,KIND,LEADDATE,STORE_QTY,EXPIN,NEEDQTY,QTY) SELECT ITEMCODE,UNIT,KIND,LEADDATE,STORE_QTY,EXPIN,NEEDQTY,QTY FROM #TEMP_MRPII
    DROP TABLE #TEMP_MRPII

     UPDATE SYS_MRP_REPORT SET STORE_QTY=ISNULL(A.AMOUNT,0),EXPIN=ISNULL(B.POQTY,0)+ISNULL(C.PDQTY,0)+ISNULL(D.WFQTY,0)
     FROM SYS_MRP_REPORT INNER JOIN USR_MATRIAL ON ITEMCODE=MTR_NO
     LEFT JOIN
     (
                           SELECT ITEMID,sum(AMOUNT) as AMOUNT from T_STOCK
         WHERE STOREHOUS IN(SELECT L_WHSCODE FROM USR_OWHS WHERE L_NMRP=1) and AMOUNT>0
         GROUP BY ITEMID
     ) A ON A.ITEMID=ITEMCODE
     LEFT JOIN
     (
                            SELECT POR1_ITEMCODE,SUM(POR1_UNRECEIVE*POR1_UNITGENE1) AS POQTY
       FROM  T_OPOR_D inner JOIN T_OPOR ON POR1_ID=OPOR_ID
       WHERE  OPOR_DOCCHECK=N'A' AND OPOR_STATUS=N'O'
         AND CONVERT(CHAR(10),POR1_DATE,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
         AND CONVERT(CHAR(10),POR1_DATE,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
       GROUP BY POR1_ITEMCODE
     ) B ON B.POR1_ITEMCODE=ITEMCODE
     LEFT JOIN
     (
         SELECT SCDD_CPBH,SUM(SCDD_YJHSL-SCDD_WCSL) AS PDQTY     
         FROM USR_SCDD
         WHERE SCDD_STATUS='O'
            AND CONVERT(CHAR(10),SCDD_DQR,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
         AND CONVERT(CHAR(10),SCDD_DQR,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
         GROUP BY SCDD_CPBH
  ) C ON C.SCDD_CPBH=ITEMCODE                                            
     LEFT JOIN     
     (
                        SELECT WSCD_CPBH,sum(WSCD_YJHSL-WSCD_WCSL) as WFQTY  
                        FROM USR_WSCDD
                              WHERE WSCD_STATUS='O'
                                    AND CONVERT(CHAR(10),WSCD_DQR,120)>=CONVERT(CHAR(10),@MRP_STDATE,120)
                                    AND CONVERT(CHAR(10),WSCD_DQR,120)<=CONVERT(CHAR(10),@MRP_ENDATE,120)
                              GROUP BY WSCD_CPBH
     ) D ON D.WSCD_CPBH=ITEMCODE   

  SELECT * FROM SYS_MRP_REPORT ORDER BY ITEMCODE,LEADDATE

 

 

                                                                                                              作者： 陈锦杰

                                                                                                              2009年11月26日

                                                                                                              Email: kindjie@126.com