基于欠品预知的柔性计划系统模型设计

1）模型创建背景

a、计划变更频繁

b、采购部及供应商如何迅速应对

c、作业部门如何快速应对

d、推行精益生产模式

2）模型创建目的

形成计划——采购——供应商联动机制，能够使供应商清楚的知道计划变更后每天的交货计划，即在什么时间送什么东西送多少？

3）模型要求

a、计划变更后采购订单及作业单处理

b、供应商商务平台建设

4）模型约束

1、确定计划变更管理模型变更周期T，及滚动周期t

2、确定本模型实施供应商范围

3、是否考虑实施约束物料，即针对不同的物料采用不同的订货策略，（即部分物料零库存策略，部分物料订货点策略？）

4、确定计算模式，是按照计划明细中的机型+作业单计算还是按照天计算

5、能否考虑计划模式变更，比如说在一个计划周期之内装某一、二种机型

6、机号连续，系统认为先上线的先下线，并且机号按照从小到大的顺序执行，如出现欠品优先满足机号较小的

7、物流业务处理及时（采购单接收，不合格品处理，其他收发，销售发出）底线为系统运行前不存在该类遗留问题

8、报工过账处理及时，底线为系统运行前不存在该类问题

9、计划周期内的作业单均为下达状态，且拷贝了作业物料

10、考虑该柔性计划管理系统层级，先整机后焊接作业，并确定是否考虑制协关系调整的问题

11、考虑该柔性计划的运作前提，给供应商下达月度需求计划，计划周期内的供应商交货计划，考虑双层计划滚动模式

12、是否考虑在途订单作为可选参数 5）计算流程 模式一:按天计算 step1：计算当前库存量 step2：计算在制作业分配量 step3：计算N天计划到货量 step4：计算有效库存量 step5：计算N天各物料的计划需求量 step6：计算N天预计库存量 step7：计算N+1天计划到货量 step8：计算N+1天个作业物料需求量 step9：计算N+1天预计库存量 step10：在T周期内，N=N+1转step7，否则转step11 step11：形成需求报表明细 step12：在考虑在途订单的情况下给出合理化建议 step13：按照供应商参数汇总形成采购订单或作业单 step14：更新供应商交货平台数据 具体步骤见下图：  欠品计算算法流程：

 系统寻单与生成订单算法流程：

 

6）sql 存储过程：

a、欠品预知计算：

CREATE PROCEDURE strong_ly_lack  
@period int,@fflag bit as
--if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tempwj]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--drop table [dbo].[tempwj]
--create table tempwj (item varchar(20),qty_valid numeric(9),qty_on_road numeric(9),qty_require numeric(9),qty_qp numeric(9),rat numeric(9))
delete from tempwj
declare @T as int
declare @at as int
declare @item  ItemType
declare @qty_on_hand    QtyTotlType
declare  @Yet_distrabution  QtyTotlType
declare  @qty_valid  QtyTotlType
declare  @qty_on_road  QtyTotlType
declare @qty_require  QtyTotlType
declare @flag  bit
select @flag=@fflag
select @at=0
select @T=@period
declare rsta  cursor  for
SELECT distinct jobmatl.item, itemwhse.qty_on_hand
FROM item INNER JOIN
      jobmatl ON item.item = jobmatl.item INNER JOIN
      job_sch INNER JOIN
      job ON job_sch.job = job.job ON jobmatl.job = job_sch.job INNER JOIN
      itemwhse ON item.item = itemwhse.item
WHERE (job_sch.start_date <= GETDATE() + @T) AND (job.qty_released = 1) AND
      (job.qty_complete = 0) AND (job_sch.start_date >= GETDATE() - 1) AND
      (NOT (job.job IN
          (SELECT job
         FROM jobtran)))
open rsta
while 0 = 0
begin
   fetch next from rsta into @item,@qty_on_hand
        if @@fetch_status != 0
  break
  SELECT @Yet_distrabution  =  SUM(jobmatl.matl_qty_conv)
   FROM jobtran INNER JOIN
        job ON jobtran.job = job.job INNER JOIN
        jobmatl ON jobtran.job = jobmatl.job INNER JOIN
        item ON jobmatl.item = item.item
  WHERE (job.qty_released = 1) AND (job.qty_complete = 0) AND (jobtran.oper_num = 10) AND
         (jobtran.next_oper = 20) AND (jobtran.qty_complete = 1) AND (jobmatl.qty_issued = 0)
        AND (jobmatl.matl_qty_conv > 0) AND (job.type = N'j') AND (job.item LIKE N'9%') AND
        (job.stat = N'r') and (item.item=@item)
  GROUP BY item.item, item.description, item.abc_code, item.u_m, item.product_code,
        item.p_m_t_code, item.plan_code, item.charfld4
  ORDER BY item.item
  if @@rowcount=0 set @yet_distrabution=0
 
 set @qty_valid=@qty_on_hand-@yet_distrabution
 while @at<=@T
  begin     
  if @flag=0 set @qty_on_road=0
  if @flag=1
  SELECT @qty_on_road =SUM(qty_ordered - qty_received)
  FROM poitem
  WHERE (due_date >= GETDATE()+@at - 1) AND (stat = N'O') AND (due_date <= GETDATE()+@at) and (item=@item)
  GROUP BY item
 if @@rowcount=0 set @qty_on_road=0
  SELECT @qty_require= SUM(jobmatl.matl_qty_conv)
 FROM job_sch INNER JOIN
       job ON job_sch.job = job.job INNER JOIN
       jobmatl ON job_sch.job = jobmatl.job INNER JOIN
       item ON jobmatl.item = item.item
 WHERE (job_sch.start_date <= GETDATE()+@at) AND (job.qty_released = 1) AND
       (job.qty_complete = 0) AND (job_sch.start_date >= GETDATE() +@at- 1) AND
       (NOT (job.job IN
           (SELECT job
          FROM jobtran)))  and (item.item=@item)
 GROUP BY jobmatl.item, item.description, item.u_m, item.abc_code, item.product_code,
       item.p_m_t_code, item.plan_code, item.charfld4
 if @@rowcount=0 set @qty_require=0
 insert into tempwj select @item,@qty_valid,@qty_on_road,@qty_require,@qty_valid+@qty_on_road-@qty_require,@at
    --    print @item
     --   print @qty_valid
    --    print @qty_on_road
    --    print @qty_require
   --     print @at
 set @qty_valid=@qty_valid+@qty_on_road-@qty_require
 set @at=@at+1
end 
set @at=0
end
close rsta
deallocate rsta 
select tempwj.item,tempwj.qty_valid,tempwj.qty_on_road,tempwj.qty_require,tempwj.qty_qp,tempwj.rat,convert(varchar(20),getdate()+rat,111) as datek,
item.description, item.u_m, item.abc_code,
      item.product_code, item.p_m_t_code, item.family_code, item.low_level,
      item.plan_code, item.phantom_flag, item.charfld4 from tempwj
inner join item on item.item=tempwj.item
GO
b、系统寻单（po）部分

$********************************************************************
主 计算函数
CREATE PROCEDURE CreatSelectPo
as
declare @item varchar(20)
declare @itemb varchar(20)
declare @qty_qp numeric(9)
declare @qty_inc numeric(9)
declare @qty_ungived numeric(9)
declare @qty numeric(9)
declare @po_num int
declare @po_line int
declare @I int
declare @rat int
declare @point int
declare @k int
set @I=0
select @k=max(rat) from tempwj  --寻找控制参数K
delete from poitemgive
insert into poitemgive (po_num,po_line,stat,po_vend_num,item,qty_ordered,qty_received,qty_ungived,due_date_day) select po_num,po_line,stat,po_vend_num,item,qty_ordered,qty_received,qty_ordered-qty_received,convert(varchar(20),due_date_day,111) from poitem where stat='o' and qty_ordered-qty_received>0 order by item,due_date_day
 declare rsta cursor for
 select distinct item from tempwj  --寻找物料明细
open rsta
fetch next from rsta into @item
while @@fetch_status = 0
begin
 while 1=1
  begin
    if @I>@K
 break
     declare rstb cursor for
      select item,qty_inc,qty_qp,rat from tempwj where rat=@I and item=@item
      open rstb
       fetch next from rstb into @itemb,@qty_inc,@qty_qp,@rat     
       if @qty_qp>=0
 begin 
   set @I=@I+1 
 end
      else
 begin
          select   po_num,po_line,qty_ordered,qty_received,item from poitem where stat='o' and qty_ordered-qty_received>0 and item=@itemb order by  due_date_day
   set @point=@@rowcount  
           if @point=0  --系统内无可用的订单,执行追加订单过程
  begin 
   exec @I= AddPoitemSe @itemb,@qty_qp,@I,@K
     end
 else
 begin
 exec @I=SelectPoitem @itemb,@qty_qp,@I,@k,@point
 end
   --       else  @@rowcount >0
 --  begin
   --           set @point=@@rowcount  --获取订单行数参数
   --         set @I=exec SelectPoitem @itemb,@qty_qp,@I,@K,@point
           --  end               
 end
          close rstb
          deallocate rstb

 
--             declare rstc cursor for
--              select po_num,po_line,qty_ordered-qty_received as qty_ungived from poitem where stat='o' and qty_ordered-qty_received>0 and item=@itemb
--              fetch next from rstc into @po_num,@po_line,@qty_ungived
--               if
--   end
-- fetch next from rsta into @item
-- end
-- while @I<@k
-- begin
--  declare rsta cursor for
--  select item,qty_inc,qty_qp,rat from tempwj where 
-- end 
-- select item,qty_qp from tempwj where rat=0 and qty_valid<0
-- open rsta
-- fetch next from rsta into @item,@qty_qp
-- while @@fetch_status = 0
-- begin 
-- select top 1   from poitem where stat='o' and qty_received<qty_ordered and item=@item order by due_date
-- if @@rowcount!=0
--   declare rstb cursor for
--    select item,qty_received-qty_ordered from poitem where stat='o' and qty_received<qty_orderde and item=@item order  by duedate
--    open rstb
--    fetch next from rstb into @itemb,@qty_ungived
--    while @@fetch_status=0
--     if @qty_ungived-abs(@qty_qp)<0
--    
--    begin
--    end
end
fetch next from rsta into @item
set @I=0
end
close rsta
deallocate rsta
GO

主 计算函数
$********************************************************************
寻单算法
CREATE PROCEDURE SelectPoitem
 @itemb varchar(20),@qty_qp numeric(9),@I int output,@K int,@rowcount int
as
--delete from poitemgive
--insert into poitemgive (po_num,po_line,stat,po_vend_num,item,qty_ordered,qty_received,qty_ungived,due_date_day) select po_num,po_line,stat,po_vend_num,item,qty_ordered,qty_received,qty_ordered-qty_received,convert(varchar(20),due_date_day,111) from poitem where stat='o' and qty_ordered-qty_received>0 order by item,due_date_day
declare @qty_new  numeric(9)
declare @qty_get numeric(9)
declare @poitem varchar(20)
declare @qty_ordered numeric(9)
declare @po_num varchar(20)
declare @po_line varchar(20)
declare @yet_date varchar(20)
set @qty_get=abs(@qty_qp)
 while 1=1
 begin 
 if @qty_get>0
 --break
 begin
 select @yet_date=dateak from tempwj where item=@itemb and rat=@I
  declare rst cursor for
  select po_num,po_line,qty_ungived-qty_sub as qty_ungived,item from poitemgive where stat='o' and qty_ungived-qty_sub>0 and item=@itemb order by due_date_day
  open rst  
  --print @po_num
  --print @po_line
   while 1=1
   begin
    fetch next from rst into @po_num,@po_line,@qty_ordered,@poitem
   if @@fetch_status!=0
   break             
     set @qty_new=@qty_ordered-@qty_get   
     if @qty_new>=0
   begin
   --print '层级'
   --print @I
   --print @po_num
   --print @po_line   
   --print @poitem   
   --print abs(@qty_get)   
   update poitemgive set qty_sub=qty_sub+abs(@qty_get),yet_date=@yet_date  where po_num=@po_num and po_line=@po_line
   update poitemgive set yet_date=@yet_date where  po_num=@po_num and po_line=@po_line and yet_date is null
   set @qty_get=0         
   end   
    if @qty_new>=0 break
     else if @qty_new<0
   begin
   --print @po_num
   --print @po_line
   --print @poitem
   --print @qty_ordered
   update poitemgive set qty_sub=qty_sub+abs(@qty_ordered),yet_date=@yet_date  where po_num=@po_num and po_line=@po_line
   update poitemgive set yet_date=@yet_date where  po_num=@po_num and po_line=@po_line and yet_date is null
   set @qty_get=abs(@qty_new)
   end      
    end 
 if @qty_get>0
 exec @I= AddPoitemSe @poitem,@qty_get,@I,@K
 close rst
 deallocate rst
 end
 set @I=@I+1
 if @I>@K break 
 select @qty_get=qty_inc from tempwj where item=@itemb and rat=@I 
--         if @@rowcount=0
--  set @I=exec AddPoitemSe @itemb,@qty_get,@I,@K   
 end
return @I
GO
寻单算法
****************************************************
生成新订单算法
CREATE PROCEDURE AddPoitemSe
 @item varchar(20),
  @qty  numeric(9),
 @I    int output,
 @K    int
AS
declare @qty_qp  numeric(9)
declare @poitem varchar(20)
declare @qty_ordered numeric(9)
declare @qty_inc numeric(9)
declare @rat int
declare @po_ordered numeric(9)
declare @dateak nvarchar(20)
set @poitem=@item
set @po_ordered=abs(@qty)
print @poitem 
print @po_ordered
print getdate()+@I
select @dateak= convert(varchar(20),getdate()+@I,111)
insert into poitemgive(item,qty_sub,yet_date) select @poitem, @po_ordered,@dateak
while 1=1
  begin
 set @I=@i+1
 if @I>@K 
  BREAK
            select @poitem=item,@qty_ordered=qty_inc,@rat=rat,@dateak=dateak from tempwj where rat=@I and item=@item
             if @qty_ordered>0
 begin 
 insert into poitemgive(item,qty_sub,yet_date) select @poitem,@qty_ordered,@dateak
 print @poitem
 print @qty_ordered
 print getdate()+@rat
 end
--    declare rst cursor for
--    select item,qty_qp,qty_inc,rat from tempwj where rat=@I and item=@item
--  open rst
--    fetch next from rst into @poitem,@qty_qp,@qty_inc,@rat
--    while @@fetch_status=0
--   begin
--     if @qty_inc>0
--      print @poitem
--    set @qty_ordered=@qty_inc  
--    print @qty_ordered
--    print getdate()+@rat
--   fetch next from rst into @poitem,@qty_qp,@qty_inc,@rat 
--   end
--  close rst
--  deallocate rst 
     
 end
RETURN @I
GO

生成新订单算法
****************************************************
 7）供应平台建设

通过asp编程实现：

<%
'ASP连接MSSQL2005和MSSQL2000数据库通用代码
Dim ConnStr
'sql数据库连接参数：数据库名、用户密码、用户名、连接名（本地用local，外地用IP）
Dim SqlDatabaseName,SqlPassword,SqlUsername,SqlLocalName
SqlDatabaseName = "bom"
SqlPassword = "infor"
SqlUsername = "sa"
SqlLocalName = "infor"
ConnStr = "Provider = Sqloledb; User ID = " & SqlUsername & "; Password = " & SqlPassword & "; Initial Catalog = " & SqlDatabaseName & "; Data Source = " & SqlLocalName & ";"

Set Conn=Server.CreateObject("ADODB.Connection")
Conn.Open ConnStr

If Err Then
    err.Clear
    Set Conn = Nothing
    Response.Write "数据库连接出错，请检查连接字串。"
    Response.End
End If

%>
<html>
<head>
<title>XX供应商交货平台</title>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<STYLE type=text/css>TABLE {
 FONT-FAMILY: "宋体"; FONT-SIZE: 9pt
}
BODY {
 FONT-FAMILY: "宋体"; FONT-SIZE: 9pt
}
td{
 line-height:22px;
}
A:link {
  TEXT-DECORATION: none
}
A:visited {
  TEXT-DECORATION: none
}
A:hover {
  TEXT-DECORATION: underline
}
</STYLE>
</head>
<body bgcolor="#FFFFFF" text="#000000" link="#FF0000" vlink="#FF0000" alink="#FF9900">
<table width="100%" style="border:#808080 solid 1px;">
<tr bgcolor="rgb(200,255,0)">
<td height="60" colspan=8 align="center"><font size="5"><b>XX供应商交货明细</b></font></td>
</tr>
</table>
<%
set rs = server.CreateObject("adodb.recordset")'创建对象
sql="SELECT poitemgive.po_num, poitemgive.po_line, poitemgive.stat,"
sql=sql &"      poitemgive.po_vend_num, vendaddr.name, poitemgive.item, item.description, "
sql=sql &"       poitemgive.qty_ordered, poitemgive.qty_ungived, poitemgive.qty_received, "
sql=sql &"       poitemgive.qty_sub, poitemgive.due_date_day, poitemgive.yet_date,item.charfld4,"
sql=sql &"       item.drawing_nbr"
sql=sql &" FROM poitemgive INNER JOIN"
sql=sql &"   item ON poitemgive.item = item.item INNER JOIN"
sql=sql &"       vendaddr ON poitemgive.po_vend_num = vendaddr.vend_num"
sql=sql &" WHERE (poitemgive.qty_sub > 0) order by poitemgive.po_vend_num,poitemgive.yet_date"'定义SQL语句
rs.Open sql,conn,0,2,1 '这里执行你查询SQL并获得结果记录集
 k =0
 Do While rs.eof=flase
 k=k+1
 rs.movenext
   Loop
   rs.movefirst 
if rs.EOF or rs.BOF Then
response.write("没有数据")
Else
%>
 <table width="100%" style="border:#808080 solid 1px;" >
 <tr bgcolor="#abb8d6">
 <td width="7%" height="30" ><b>订单号</b></td>
 <td width="4%" height="30"><b>订单行</b></td>
 <td width="8%" height="30"><b>订单物料</b></td>
 <td width="10%" height="30"><b>物料名称</b></td>
 <td width="4%" height="30"><b>原订购数量</b></td>
 <td width="4%" height="30"><b>已交货数量</b></td>
 <td width="4%" height="30"><b>本次交货</b></td>
 <td width="7%" height="30"><b>原交货日期</b></td>
 <td width="7%" height="30"><b>现交货日期</b></td>
 <td width="7%" height="30"><b>送货物流门</b></td>
 <td width="7%" height="30"><b>现场接收员</b></td>
 </tr>
 <% 
 'response.write(rs.recordcount)
 for i=1 to k
 if rs.EOF or rs.BOf then exit for
 %>
 <tr bgcolor="#6ab8d0">
  <td><% =rs("po_num") %></td>
  <td><% =rs("po_line") %></td> 
  <td><% =rs("item") %></td>
  <td><% =rs("description") %></td>
  <td><% =rs("qty_ordered") %></td>
  <td><% =rs("qty_received") %></td>
  <td><% =rs("qty_sub") %></td>
  <td><% =rs("due_date_day") %></td>
  <td><% =rs("yet_date") %></td>
  <td><% =rs("drawing_nbr") %></td>
  <td><% =rs("charfld4") %></td>
 </tr>
 <% 
 rs.movenext
 next
 rs.close
 set rs=nothing
 %>
 </table>
<%
 End If
%>
<a href="index2.asp">测试页面链接</a>
</body>
</html>
 …………………………………………