MRP计算过程——SQL_荃景

http://blog.sina.com.cn/u/1859456197

首页博文目录关于我

个人资料

微博

加好友发纸条

写留言加关注

博客等级：
博客积分：

博客访问：
关注人气：
获赠金笔：0支
赠出金笔：0支
荣誉徽章：

正文字体大小：大中小

MRP计算过程——SQL

(2009-02-12 09:39:11)

标签：

杂谈

分类： MSN搬家

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

ALTER PROC proc_mrp
@planNo varchar(50),
@isEnd   int output
AS
BEGIN
DECLARE @productNo varchar(50)
DECLARE @month_plan varchar(1000)
DECLARE @sql varchar(3000)
DECLARE @i   bigint
--开始计算
set @isEnd=0
--存放周生产计划的临时表
CREATE TABLE #(
    planNo varchar(50),
    productNo varchar(100),
    planNum bigint,
    ymw varchar(50)
)
--存放半成品库存与预计库存信息的临时表
CREATE TABLE #t_semistock (
    productNo varchar(100),
    stockNum float,
    preStockNum float
)
   --存放原料库存与预计库存信息的临时表
CREATE TABLE #t_materialstock (
    materialNo varchar(100),
    stockNum float,
    preStockNum float
)

--计算每个BOM展开需求的临时表，每次都要重置
DECLARE @t_Level TABLE (
        id           bigint,
        pid       bigint,          --父节点ID
        bomNo        varchar(100),
materialNo   varchar(100),
QTY          float, --取自@bomMingxi
        usage        float, --用量
        grossDemand float, --毛需求
        grossDemand2 float, --去掉半成品库存后的毛需求
        demandWeek   varchar(20),
        planNo       varchar(20),
        level        int,
sid          varchar(1000) --排序字段,通过这个来排序,可以体现出树形的层次
)

--保存毛需求的表
DECLARE @t_grossDemand TABLE (
        id           bigint,
        pid          bigint,
        bomNo        varchar(100),
materialNo   varchar(100),
QTY          float, --取自@bomMingxi
        usage        float, --用量
        grossDemand float, --毛需求
        grossDemand2 float, --去掉半成品库存后的毛需求
        demandWeek   varchar(20),
        planNo       varchar(20),
        level        int,
sid          varchar(1000) --排序字段,通过这个来排序,可以体现出树形的层次
)
--MPS分解到周
DECLARE mps_cursor CURSOR FOR

--取出计划记录，将各月的计划合并
SELECT productNo,month1_plan + '|' + month2_plan + '|' + month3_plan + '|'
         +month4_plan+'|'+month5_plan+'|'+month6_plan
FROM tb_MPSMX WHERE planNo=@planNo
OPEN mps_cursor

--循环每一条记录，将计划分拆到周，插入到新的表中
FETCH next FROM mps_cursor INTO @productNo, @month_plan

WHILE @@FETCH_STATUS=0
BEGIN
    SET @sql='insert # select '''+ @planNo + ''',''' + @productNo+''',' + replace(@month_plan,'|',' union select '''+@planNo+''','''+@productNo+''',')
    --PRINT @sql
    exec(@sql)

    IF @@ERROR<>0
        BEGIN
    PRINT 'An error occurred .'+' productNo:'+@productNo
        END

        FETCH next FROM mps_cursor INTO @productNo, @month_plan
        IF @@ERROR<>0
        BEGIN
    PRINT 'An error occurred .'+' productNo:'+@productNo
        END
END
--select * from # ORDER BY productno,ymw
CLOSE mps_cursor
DEALLOCATE mps_cursor

DECLARE @planNum float     --需求数量
DECLARE @demandWeek varchar(20) --需求周
DECLARE @bomID AS bigint
--用于BOM展开的游标变量
DECLARE @id           bigint
DECLARE @pid      bigint
DECLARE @materialNo   varchar(100)
DECLARE @QTY          float --取自@bomMingxi
DECLARE @usage        float --用量
DECLARE @level        bigint
DECLARE @sid          varchar(1000) --排序字段,通过这个来排序,可以体现出树形的层次
----半成品库存数量变量
DECLARE @semistockNum     float
----原料库存数量变量
DECLARE @materialstockNum     float
----用于更新预计库存的需求量
DECLARE @demand float
DECLARE @preStockNum float --预计库存数
--
insert #t_semistock
SELECT semistockpro_product_no,semistockpro_num,semistockpro_num--后一项库存数量将作为预计库存更新
FROM tb_stocksemiproduct

PRINT '开始统计原料库存...'

insert #t_materialstock
SELECT stockinfo_materiel_name,stockinfo_nownum,stockinfo_nownum
FROM tb_stockinfo

PRINT '开始统计采购在单在途数量...'

PRINT '采购在单在途数量统计完毕！'

PRINT 'MRP计算数据基础统计完毕！'
   --*/
PRINT '开始需求计算...'
--SELECT productNo,planNum,ymw FROM #

--判断游标是否存在
if cursor_status('global','weekmps_cursor') <>-3
begin
close weekmps_cursor
deallocate weekmps_cursor
end

DECLARE weekmps_cursor CURSOR FOR
SELECT productNo,planNum,ymw FROM # ORDER BY productno,ymw
open weekmps_cursor
----用游标将周需求从#表中依次取出
FETCH next FROM weekmps_cursor INTO @productNO,@planNum,@demandWeek
PRINT '正在展开的BOM:'+ @productNO
WHILE @@fetch_status=0
BEGIN
      --FETCH next FROM weekmps_cursor INTO @productNO,@planNum,@demandWeek
      --PRINT '正在展开的BOM:'+ @productNO
      ----清空临时需求表
      SELECT @i=count(*) FROM @t_Level
      --print @i
      IF @i>0
      BEGIN
          PRINT '@t_Level表非空，正在清空...'
          DELETE FROM @t_Level
          SELECT @i=count(*) FROM @t_Level
          IF @i=0
             PRINT '@t_Level表已清空！'
          else
             PRINT '@t_Level清空出错！'
      END
      ----取出周需求后，按层次展开相应BOM，并将之保存在临时表中
      declare bom_cursor cursor for
      SELECT * FROM bom_expanding (@productNO)
      --select * from #bom_tmp
      open bom_cursor
      fetch next from bom_cursor into @id,@pid,@materialNo,@QTY,@usage,@level,@sid
      --print 'material:'+@materialNo
      ----将产品计划数量作为第一项需求写入
      ----注意：要乘以产品损耗率
      insert @t_Level
      select @id,@pid,@productNO,@materialNo,1,1,@planNum,@planNum,@demandWeek,@planNo,@level,@sid

      --PRINT '查询临时表'
      --select * from @t_level
      while @@fetch_status=0
      begin
         fetch next from bom_cursor into @id,@pid,@materialNo,@QTY,@usage,@level,@sid
         --print 'material:'+@materialNo
         ----查找库存
         SET @semistockNum=0
         SET @materialstockNum=0
         ----半成品库存
         select @semistockNum=prestockNum from #t_semistock where productNo=@materialNo
         select @materialstockNum=prestockNum from #t_materialstock where materialNo=@materialNo
         ----计算需求,从@t_Level表中查找当前物料的父项信息，计算后存入表@t_Level


         ----定义出消耗数量变量与需求数量变量
         DECLARE @grossDemand2 float --减去半成品数量之后的需求量
         DECLARE @wastage float      --消耗数量

         ----其父层次需求*当前层用量=当前层毛需求量当前层毛需求量-当前层半成品数量=当前层净需求量

         SELECT @wastage=@usage * grossDemand2 FROM @t_Level WHERE id=@pid

         IF @wastage-@semistockNum < 0         ----如果消耗量小于库存数量，则需求为0
            SET @grossDemand2=0
         ELSE
            SET @grossDemand2=@wastage-@semistockNum

         INSERT @t_Level
         SELECT @id,@pid,@productNO,@materialNo,@QTY,@usage,
                @usage*grossDemand,                    --未减任何库存
                @grossDemand2,                         --减（预计）库存
                --@QTY*@usage*(grossDemand2-@semistockNum)-@materialstockNum, --再减原料预计库存（注意：是预计库存）
                @demandWeek,
                @planNo,
                @level,@sid
         FROM @t_Level WHERE id =@pid

         ----更新预计库存（预计库存包括库存、在线数量、在单数量、在途数量）
         ----SET @demand=0
         ----SELECT @demand=grossDemand2 from @t_Level where id=@id

         SELECT @preStockNum=prestockNum FROM #t_semistock where productNo=@materialNo

         ----若预计库存量大于消耗量
         IF @preStockNum-@wastage<=0
            UPDATE #t_semistock set prestockNum=0 from #t_semistock where productNo=@materialNo
         ELSE----否则
            UPDATE #t_semistock set prestockNum=@preStockNum-@wastage from #t_semistock where productNo=@materialNo
      END

      SELECT * FROM #t_semistock
      close bom_cursor
      deallocate bom_cursor
      FETCH next FROM weekmps_cursor INTO @productNO,@planNum,@demandWeek
      ----将临时需求数据写入需求表
      SELECT * FROM @t_Level
      INSERT @t_grossDemand SELECT * FROM @t_Level
   END
      --SET nocount OFF
   CLOSE weekmps_cursor
   DEALLOCATE weekmps_cursor
   --DROP TABLE #

   --确保中间需求表为空
   DECLARE @recordCount int
   SELECT @recordCount =COUNT(*) FROM tb_mrpDemand
   IF @recordCount>0 DELETE FROM tb_mrpDemand

   INSERT tb_mrpDemand
   SELECT id,pid,bomNo,materialNo,QTY,usage,
          grossdemand,grossdemand2,demandWeek,planNo,level,sid
   FROM @t_grossDemand ORDER BY materialNo,demandweek

   --计算完毕
   set @isEnd=1
   --SELECT * FROM #t_semistock
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

阅读┊ 收藏 ┊ 喜欢 ▼ ┊打印┊举报/Report

前一篇：Word 使用技巧

后一篇：内部连接端口

新浪BLOG意见反馈留言板　欢迎批评指正