怎么根據BOM表求產品裝配數量(200分)

  • 主题发起人 主题发起人 xjw10
  • 开始时间 开始时间
X

xjw10

Unregistered / Unconfirmed
GUEST, unregistred user!
各位大哥:<br> &nbsp; &nbsp;這是我第二次提這個問題了,今天已經給了200分給提出一點思路的一位老兄。<br> &nbsp; &nbsp;我現在要根據BOM表以及BOM中產品的各下級現有數量求出能組裝出多少個產品。但想來想去沒有方法,哪位大哥知道的話請給個思路吧。如果有源碼的話當然是源碼好了。<br>下面是要查詢出來的結果:(就是后面的配套數量不知怎么求)<br>産品配套分析(顯示最小配套數) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>查詢結果 &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<br>層次 &nbsp;元件編號 &nbsp;名稱 &nbsp;規格 &nbsp;單位 &nbsp;單件用量 &nbsp;當前庫存 &nbsp;庫存單位 &nbsp;配套數量
 
建议找本有关MRPII或ERP的书看看吧
 
To:<br> &nbsp; 我有看過類似的書,但上面都沒說怎么求的!
 
笨一点的方法,首先求出每种元件最多能配出的产品数,再排序一下,取第一条记录的产品数,<br>也就是最少配比数<br>SELECT TOP 1 元件/单件用量 as 最小配套數 &nbsp;from 库存表 inner join bom表<br>on &nbsp;库存表.元件= bom表.元件
 
[:D]兄弟,是你给我加分的吧?<br>我告诉你,记得给我加分<br>其实思路就是上次我说的那样<br>功能:BOM分解:根据定单数量自动分组成品计算需要的外协与原料的数量<br>功能:BOM分解:计算所有物料的所需要的产品数量,并返回合计值<br>功能:BOM分解:计算单个物料的所需要的产品数量,并返回其值<br>功能:BOM分解:计算主物料的所需要的所有子物料 :其中用了一个递归执行存储过程
 
首先:我要謝謝給我回帖的各位老兄!我認為“勇者”給的方法可行性比較強,我先試一下。
 
奇怪,我不是给了你解答了?<br>怎么没有见着??
 
To:easykoala<br> &nbsp;我沒見過呢!麻煩你再給一次行不?
 
其实不难:<br>1、遍历BOM表,根据现有的物料求出可以组装每一部件的数量,<br>2、再从其中计算出最小者便是你所要的!
 
BOM單出欠料表,要根據BOM清單及千台用量來核算,包括了解每種零件的庫存量及生產此種零件的原材料量.同時根據不同的訂單核算每張已接訂單的生產狀況,包括零件部門生產耗用原材料及零件入倉數,總裝部門訂單的零件領用數,就這樣加加減減後庫存量就屬於新訂單的可用量了.本人也覺得挺麻煩,不知道有沒有高人有好的解決方案.學習中.......
 
忘了採購未回的這筆數量及不良品退貨的數量,等待高人指點.
 
这个问题我以前没有做过,也没有接触过,但看到你的问题,有一点建议: &nbsp;<br>在存储过程中,使用游标,以及临时表。<br>首先建一个临时表(層次 &nbsp;元件編號 &nbsp;名稱 &nbsp;規格 &nbsp;單位 &nbsp;單件用量 &nbsp;當前庫存 &nbsp;庫存單位 &nbsp;配套數量),然后查询出(層次 &nbsp;元件編號 &nbsp;名稱 &nbsp;規格 &nbsp;單位 &nbsp;單件用量 &nbsp;當前庫存 &nbsp;庫存單位 )放到游标中,依次读取游标中的数据,根据‘元件編號’以及BOOM中的元器件相互的关系,计算出‘配套數量’在放到临时表中,最后在存储过程尾部查询出临时表的记录就OK了!
 
给你一段例子吧:(写不好,不要见笑)<br>CREATE procedure kcStatistic<br> &nbsp; &nbsp;@d1 varchar(100),<br> &nbsp; &nbsp;@d2 varchar(100),<br> &nbsp; &nbsp;@clid varchar(20)<br>as<br>Create table #tmp(a1 varchar(20),a2 varchar(20),a3 varchar(20),a4 numeric,a5 numeric,a6 numeric,a7 numeric,a8 numeric,a9 numeric,a10 numeric,a11 numeric,a12 numeric,a13 int IDENTITY (1, 1) NOT NULL , )<br><br>Declare Cr_tmp cursor For <br> &nbsp;select clid from consumblelist<br>Open Cr_tmp <br>Fetch Next From Cr_tmp into @clid <br>while (@@fetch_status = 0)<br>begin<br> &nbsp; &nbsp;<br> &nbsp; &nbsp; &nbsp;Insert into #tmp<br> &nbsp; &nbsp; &nbsp;select a.clid as 商品编码,d.clname as 商品名称,e.cksname as 种类,0,0,e.kcnum as 本期份数,e.upperlimit as 最高库存,e.lowerlimit as 最低库存,a.num+c.num as 出货数,isnull(((a.num+c.num)*e.trueprice),0) as 出货成本,isnull((d.fnum1+b.allprice),0) as 出货额,(isnull((d.fnum1+b.allprice),0)-isnull(((a.num+c.num)*e.trueprice),0)) as 毛利润 from &nbsp;<br>(select clid,clname,isnull(sum(fnum1),0)as fnum1 &nbsp;from OutgoodTableback where checkid=1 and (createdate between @d1 and &nbsp;@d2) &nbsp;group by clid,clname ) as d,<br>(select clid,isnull(sum(num),0)as num &nbsp;from OutgoodTableback where checkid=1 and (createdate between @d1 and &nbsp;@d2) group by clid)as a,<br><br>(select a.clid as clid,a.clname,isnull(sum(a.allprice*case cudconsumekind when '消费' then 1 when '退货' then -1 else 0 end ),0) as allprice &nbsp;from compartmentusedetail as a,compartmentuse as b &nbsp;where &nbsp;a.cuid=b.cuid and a.ifprint=1 and b.cuifnot&lt;&gt;2 and b.cuifnot&lt;&gt;3<br>and (cuddate between @d1 and &nbsp;@d2) &nbsp;group by a.clid,a.clname )as b,<br><br>(select a.clid as clid,isnull(sum(a.num*case cudconsumekind when '消费' then 1 when '赠送' then 1 when '配送' then 1 when '退货' then -1 else 0 end ),0) as num &nbsp;from compartmentusedetail as a,compartmentuse as b &nbsp;where a.cuid=b.cuid and a.ifprint=1 and b.cuifnot&lt;&gt;2 and b.cuifnot&lt;&gt;3<br>and (cuddate between @d1 and &nbsp;@d2) group by a.clid) as c,<br>consumblelist as e<br> <br>where a.clid=@clid and a.clid=b.clid and b.clid=c.clid and a.clid=d.clid and a.clid=e.clid --order by a.clid <br>--------------------------------------------------------------------------<br>union<br>select d.clid as 商品编码,d.clname as 商品名称,e.cksname as 种类,0,0,e.kcnum as 本期份数,e.upperlimit as 最高库存,e.lowerlimit as 最低库存,d.num as 出货数,isnull((d.num*e.trueprice),0) as 出货成本,isnull(d.fnum1,0) as 出货额,(isnull(d.fnum1,0)-isnull((d.num*e.trueprice),0)) as 毛利润 from &nbsp;<br>(select clid,clname,isnull(sum(fnum1),0)as fnum1,isnull(sum(num),0)as num &nbsp;from OutgoodTableback where checkid=1 and (createdate between @d1 and &nbsp;@d2) &nbsp;group by clid,clname ) as d <br>,consumblelist as e<br>where d.clid=@clid and d.clid=e.clid and d.clid &nbsp;not in (select a.clid as clid &nbsp;from compartmentusedetail as a,compartmentuse as b &nbsp;where &nbsp;a.cuid=b.cuid and a.ifprint=1 and b.cuifnot&lt;&gt;2 and b.cuifnot&lt;&gt;3<br>and (cuddate between @d1 and &nbsp;@d2) &nbsp;group by a.clid ) <br><br>------------------------------------------------------------------------------------------------<br>union<br>select b.clid as 商品编码,b.clname as 商品名称,e.cksname as 种类,0,0,e.kcnum as 本期份数,e.upperlimit as 最高库存,e.lowerlimit as 最低库存,b.num as 出货数,isnull((b.num*e.trueprice),0) as 出货成本,isnull(b.allprice,0) as 出货额,(isnull(b.allprice,0)-isnull((b.num*e.trueprice),0)) as 毛利润 from &nbsp;<br><br>(select a.clid as clid,a.clname,isnull(sum(a.allprice*case cudconsumekind when '消费' then 1 when '退货' then -1 else 0 end ),0) as allprice<br>,isnull(sum(a.num*case cudconsumekind when '消费' then 1 when '赠送' then 1 when '配送' then 1 when '退货' then -1 else 0 end ),0) as num &nbsp;from compartmentusedetail as a,compartmentuse as b &nbsp;where &nbsp;a.cuid=b.cuid and a.ifprint=1 and b.cuifnot&lt;&gt;2 and b.cuifnot&lt;&gt;3<br>and (cuddate between @d1 and &nbsp;@d2) &nbsp;group by a.clid,a.clname )as b<br>,consumblelist as e<br>where b.clid=@clid and b.clid=e.clid and b.clid not in (select clid from OutgoodTableback where checkid=1 and (createdate between @d1 and &nbsp;@d2) group by clid)<br><br> &nbsp; &nbsp; <br> &nbsp; &nbsp; <br> &nbsp; &nbsp; &nbsp;update &nbsp;#tmp set a4=<br> &nbsp; &nbsp; &nbsp; (select sum(a.num) as num &nbsp;from &nbsp;inproducts as a,intable as b,consumblelist as c<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where a.clid=@clid and a.clid=c.clid and a.intaid=b.intaid and b.intaisin=1 and (a.createdate between &nbsp;@d1 and &nbsp;@d2)<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;group by a.clid,a.clname),<br> &nbsp; &nbsp; a5=(select sum(a.fnum5) as fnum5 from &nbsp;inproducts as a,intable as b,consumblelist as c<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;where a.clid=@clid and a.clid=c.clid and a.intaid=b.intaid and b.intaisin=1 and (a.createdate between &nbsp;@d1 and &nbsp;@d2)<br> &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;group by a.clid,a.clname) where a1=@clid<br><br> &nbsp;Fetch Next From Cr_tmp into @clid<br>end<br> Insert into #tmp<br> select &nbsp;'总计','总计','',0,0,0,0,0,0,sum(a10),sum(a11),sum(a12) from #tmp<br>select a1 as 商品编码,a2 as 商品名称,a3 as 种类,a4 as 进货数,a5 as 进货额,a6 as 本期份数,a7 as 最高库存,a8 as 最低库存,a9 as 出货数,a10 as 出货成本,a11 as 出货额,a12 毛利润 from #tmp order by a13<br><br>Close Cr_tmp<br>Deallocate Cr_tmp<br>GO
 
根据数量算出元件可以跟库存对比一下,
 
過程我上周六已經寫完,但還有待檢測計算的正確性,“勇者”對我的提示作用是不小的,我想知道還有沒有哪位高手有更好的方法能給大家共享一下,不管有沒有新方法過幾天都要發分了。
 
謝謝各位,結貼了!
 
后退
顶部