6 致谢
本文从拟定题目到定稿，历时数月。在本文档完成之际，首先要向我的导师陈尔奎老师致以诚挚的谢意。平时的学习中，陈老师给了我许许多多的帮助和关怀。陈老师学识渊博、治学严谨，待人平易近人，在陈老师的悉心指导中，我不仅学到了扎实的专业知识，也在怎样处人处事等方面收益很多；同时他对工作的积极热情、认真负责、有条不紊、实事求是的态度，给我留下了深刻的印象，使我受益非浅,在此我谨向陈老师表示衷心的感谢和深深的敬意。
同时，我要感谢电气工程专业给我们授课的各位老师，正是由于他们的传道、授业、解惑，让我学到了专业知识，并从他们身上学到了如何求知治学、如何为人处事。我也要感谢我的母校山东科技大学，是她提供了良好的学习环境和生活环境，让我的大学生活丰富多姿，为我的人生留下精彩的一笔。
7 参考文献
[1] 东方人华，陈杰。 PowerBuilder 10.0入门与提高, 清华大学出版社，2005
[2] 陈永，Power Builder编程技术全接触，清华大学出版社，2007
[3] 常丹，孟婕，苟娟琼。ERP系统模拟试验教程，电子工业出版社，2007
[4] 程控，革扬。MRPⅡ/ERP原理与应用(第2版)，清华大学出版社，2006
[5] 张瑞军，徐丽娜。信息系统开发工具POWERBUILDER语言，清华大学出版社，2007
[6] 张晓云，数据库系统开发与POWERBUILDER，高等教育出版社，2007
[7] Shane Schick. Enterprises mull PowerBuilder renovations, Computing Canada, 2004
[8] Joseph R. Muscatello, Diane H. Parente. Enterprise Resource Planning (ERP): A Post implementation Cross-Case Analysis, Information Resources Management Journal, 2006
附录一：程序
入库存储过程
CREATE proc dbo.p_InvTrans02 @entityno varchar(12)
AS Begin
Declare @sInOrOut VarChar(6), --出/入库方向
@sReturnInfo varchar(300), --出错信息
@sBillNo varchar(25), --单据编码
@sorderNo varchar(25), --订单号
@soNo varchar(25), --订单号
@iCount int, --记录数
@iCount1 int, --记录数
@iCount2 int, --记录数
@itransid int, --流水号
@FQTY decimal(18,6), --库存量
@FQTYforsd decimal(18,6), --销售预留
@FQTYforpac decimal(18,6), --生产预留
@sList Varchar(500),
@sassType2 varchar(4), --辅助类型
@sassType varchar(4), --辅助类型
@iprecision int, --数量精度
@fwiptransbillno varchar(25),
@status char(1)
declare @dqty decimal,@dsumqty decimal, @sitemno varchar(25),@swono varchar(25),@iseqid int
declare @ftype char(1),@fworktype char(1) --------发料单类型和类别
Select @sList = ''
Select @sBillNo=isnull(b.fsourceno,''),@sorderNo=isnull(b.fprtsourceno,''), @sInOrOut=a.Fopertype,
@sassType2 = isnull(a.FassType2,''),@sassType = isnull(a.FassType,'')
From t_InvTrans a, #updated b
Where a.FTransNo = b.FTransNo
and a.FEntityNo= b.Fentityno
and b.fsplit=0
if @sBillNo <> '' ---- 增加此判断(如果按收货单入库)
begin
--反写收货单的已入库数量
Update t_PurReceiveEntry
Set FStockedQty = A.FStockedQty + B.Fqty --/(case when A.Fration = 0 then 1 else A.Fration end )),
, FStockedSPareQty = A.FStockedSPareQty + B.FspareQty --)/(case when A.Fration = 0 then 1 else A.Fration end ))
, FdeLostQty = A.FDeLostQty + B.FLostQty --/(case when A.Fration = 0 then 1 else A.Fration end ))
From t_PurReceiveEntry As A , #updated As B
Where A.FPRvNo = B.FsourceNo
and A.FLineID = B.FSourceLineID
and A.FEntityNo = B.FentityNO
and b.Fsplit = 0 and A.fissend=B.Fissend
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新采购收货单信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--取未完成的记录数
select @iCount=count(1) from t_purreceiveentry where fentityNo=@entityno
and fprvNo=@sbillNo and ( FPastQty + FspareQty + FDegradetQty +FLostQty) >
( FStockedQty + FStockedspareQty + FDeLostQty)
if @icount = 0 --为零，继续取，否则没有必要
begin
--取未完成的记录数(取需要检验但是未检验的记录数)
select @icount1=count(*) from t_purreceiveentry where fentityNo=@entityno
and fprvNo=@sbillNo and FCheckMethod in ('1','2') --有需要检验的记录
if @icount1 > 0
begin
select @icount2=count(1) from t_purcheck a,t_purcheckentry b
where a.fentityno = @entityno and a.fprvno = @sbillNo
and a.fentityno = b.fentityno and a.fpcno = b.fpcno
if @icount1 <> @icount2 --有未检验的记录
select @icount = @icount + 1
end
end
if @icount=0 set @status='F'
else begin
--取已执行的记录数
if exists (select 1 from t_purreceiveentry where fentityNo=@entityno
and fprvNo=@sbillNo --and FCheckMethod in ('1','2')
and ( FStockedQty + FStockedspareQty + FDeLostQty + FReturnedQty)>0)
set @status='G'
else set @status='A'
end
update t_PurReceive set fstatus=@status where FprvNo= @sBillNo and fentityNo=@entityno
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新收货单状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
update t_Purcheck set fstatus=@status where FprvNo= @sBillNo and fentityNo=@entityno
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新检验单状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end
------------------------------以上按收货单入库(按定单不反写的部分)
--反写订单赠品的已入库数量
Update t_Purpresent
Set FStockedQty = A.FStockedQty + (( B.Fqty + B.FLostQty )/(case when A.Fration = 0 then 1 else A.Fration end )),
freceivedQty = a.freceivedQty + (( B.Fqty + B.FLostQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
From t_Purpresent As A , #updated As B
Where A.FBillNo = B.FprtsourceNo
and A.FLineID = B.FprtSourceLineID
and a.ftype='03'
and b.fissend='2'
and b.Fsplit = 0
and A.FEntityNo = B.FentityNO
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新赠品信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--反写合同赠品的已入库数量
Update a
Set FStockedQty = A.FStockedQty + (( B.Fqty + B.FLostQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,freceivedqty = case @sbillNo when '' then A.freceivedqty + (( B.Fqty )/(case when A.Fration = 0 then 1 else A.Fration end )) else a.freceivedqty end
From t_Purpresent As A , #updated As B,t_purpresent c
Where B.FentityNo=c.FentityNo
and B.FprtsourceNo=c.FbillNo
and B.Fprtsourcelineid=c.Flineid
and c.Ftype='03'
and A.FEntityNo = c.FentityNO
and A.FBillNo = c.FOrigBillNo
and A.FLineID = c.FOrigLineID
and a.ftype='02'
and b.fissend='2'
and b.Fsplit = 0
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新合同赠品信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--反写订单的已入库数量
if @sbillNo='' --按订单入库
begin
Update t_Purorderentry
Set FStockedQty = A.FStockedQty + (( B.Fqty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,FStockedSPareQty=a.FStockedSPareQty + (( B.FspareQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
-- 2001.05.25修改
--,freceivedQty=a.freceivedQty + A.FStockedQty + (( B.Fqty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,freceivedQty = a.freceivedQty + (( B.Fqty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,FReceivedSpareQty=a.FReceivedSpareQty + + (( B.FspareQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
From t_Purorderentry As A ,
( select fentityNo,FprtsourceNo,FprtSourceLineID,sum(Fqty + FLostQty ) as fqty,sum(FspareQty)as fspareqty
from #updated
where fissend='1'
and Fsplit = 0
group by fentityNo,FprtsourceNo,FprtSourceLineID) As B
Where A.FPoNo = b.FprtsourceNo
and A.FLineID = b.FprtSourceLineID
and A.FEntityNo = b.FentityNO
end
else
begin
Update t_Purorderentry
Set FStockedQty = A.FStockedQty + (( B.Fqty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,FStockedSPareQty=a.FStockedSPareQty + (( B.FspareQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
From t_Purorderentry As A ,
( select fentityNo,FprtsourceNo,FprtSourceLineID,sum(Fqty + FLostQty ) as fqty,sum(FspareQty)as fspareqty
from #updated
where fissend='1'
and Fsplit = 0
group by fentityNo,FprtsourceNo,FprtSourceLineID) As B
Where A.FPoNo = b.FprtsourceNo
and A.FLineID = b.FprtSourceLineID
and A.FEntityNo = b.FentityNO
end
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新订单信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--反写收货排程的已入库数量
if @sbillNo=''
begin
Update t_Purorderqueue
Set FStockedQty = A.FStockedQty + (( B.Fqty + B.FLostQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,freceivedQty=a.freceivedQty + (( B.Fqty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,freceivedSpareQty=a.freceivedSpareQty + (( B.Fspareqty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,FStockedSPareQty=a.FStockedSPareQty + (( B.FspareQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
From t_Purorderqueue As A , #updated As B
Where A.FPoNo = B.FprtsourceNo
and A.Flineid = B.FprtSourceLineID
and A.FseqID = B.Fprtseqid
and A.FEntityNo = B.FentityNO
and b.Fsplit = 0
and b.fissend='1'
end
else
begin
Update t_Purorderqueue
Set FStockedQty = A.FStockedQty + (( B.Fqty + B.FLostQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,FStockedSPareQty=a.FStockedSPareQty + (( B.FspareQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
From t_Purorderqueue As A , #updated As B
Where A.FPoNo = B.FprtsourceNo
and A.Flineid = B.FprtSourceLineID
and A.FseqID = B.Fprtseqid
and A.FEntityNo = B.FentityNO
and b.Fsplit = 0
and b.fissend='1'
end
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新收货信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--更新排程结案状态 kimman 2001.12.28
Update t_Purorderqueue
Set fend = '2'
From t_Purorderqueue As A , #updated As B
Where A.FPoNo = B.FprtsourceNo
and A.Flineid = B.FprtSourceLineID
and A.FseqID = B.Fprtseqid
and A.FEntityNo = B.FentityNO
and b.Fsplit = 0
and a.fqty - a.fstockedqty - a.fcancelqty + a.frebilledqty > 0
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新订单排程结案状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
Update t_Purorderqueue
Set fend = '3'
From t_Purorderqueue As A , #updated As B
Where A.FPoNo = B.FprtsourceNo
and A.Flineid = B.FprtSourceLineID
and A.FseqID = B.Fprtseqid
and A.FEntityNo = B.FentityNO
and b.Fsplit = 0
and a.fqty - a.fstockedqty - a.fcancelqty+ a.frebilledqty <= 0
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新订单排程结案状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--更新订单的状态
--取未完成记录数
if @sBillNo='' -----按收货单收货无此反写
begin
select top 1 @sorderNo=fprtsourceNo from #updated b
if exists (select 1 from t_purorderqueue
where fentityNo=@entityno and fpoNo=@sorderNo
and fqty > freceivedQty - FRefusedQty - freturnedQty + FCancelQty
union select 1 from t_purpresent
where fentityNo=@entityno and fbillNo=@sorderNo and Ftype='03'
and fqty >(freceivedQty - FRefusedQty - freturnedQty))
begin
--取已执行的记录数
if exists (select 1 from t_purorderqueue
where fentityNo=@entityno and fpoNo=@sorderNo
and (freceivedQty+FRefusedQty+freturnedQty+FCancelQty+FRebilledQty+FBilledQty)>0
union select 1 from t_purpresent
where fentityNo=@entityno and fbillNo=@sorderNo and Ftype='03'
and (freceivedQty+FRefusedQty+freturnedQty+FStockedQty)>0)
set @status='G'
else set @status='A'
end
else set @status='F'
update t_purorder set fstatus=@status
where fentityNo=@entityno and fpoNo=@sorderNo
if @@error <> 0
begin
rollback
raiserror('反写采购采购订单状态失败!',16,1)
return
end
end
--反写合同的已入库数量
Update t_Purcontractentry
Set FStockedQty = A.FStockedQty + (( B.Fqty )/(case when A.Fration = 0 then 1 else A.Fration end ))
,FStockedSPareQty=a.FStockedSPareQty + (( B.FspareQty )/(case when A.Fration = 0 then 1 else A.Fration end ))
---------------------以下两句 增加 2001.05.25
,freceivedqty = case @sbillNo when '' then A.freceivedqty + (( B.Fqty )/(case when A.Fration = 0 then 1 else A.Fration end )) else a.freceivedqty end
,freceivedspareqty = case @sbillNo when '' then a.freceivedspareqty + (( B.FspareQty )/(case when A.Fration = 0 then 1 else A.Fration end )) else a.freceivedspareqty end
From t_Purcontractentry As A ,
( select fentityNo,FprtsourceNo,FprtSourceLineID,sum(Fqty + FLostQty ) as fqty,sum(fspareqty) as fspareqty
from #updated
where fissend='1'
and Fsplit = 0
group by fentityNo,FprtsourceNo,FprtSourceLineID) As B
,t_PurOrderentry As C
Where c.fentityNo=b.fentityNo
AND c.fpoNo=b.FprtsourceNo
AND c.flineid=b.FprtSourceLineID
and A.FPCNo = c.FPCNo
and A.FLineID = c.FPClineid
and A.FEntityNo = c.FentityNO
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新合同信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
return
End
GO
出库存储过程
CREATE proc dbo.p_InvTrans07 @entityno varchar(12)
AS Begin
Declare @sInOrOut VarChar(6), --出/入库方向
@sReturnInfo varchar(300), --出错信息
@sBillNo varchar(25), --单据编码
@sorderNo varchar(25), --订单号
@soNo varchar(25), --订单号
@iCount int, --记录数
@iCount1 int, --记录数
@iCount2 int, --记录数
@itransid int, --流水号
@FQTY decimal(18,6), --库存量
@FQTYforsd decimal(18,6), --销售预留
@FQTYforpac decimal(18,6), --生产预留
@sList Varchar(500),
@sassType2 varchar(4), --辅助类型
@sassType varchar(4), --辅助类型
@iprecision int, --数量精度
@fwiptransbillno varchar(25),
@status char(1),
@sTransType varchar(6)
declare @dqty decimal,@dsumqty decimal, @sitemno varchar(25),@swono varchar(25),@iseqid int,@sentityno varchar(12)
declare @ftype char(1),@fworktype char(1) --------发料单类型和类别
Select @sList = ''
Select @sBillNo=isnull(b.fsourceno,''),@sorderNo=isnull(b.fprtsourceno,''), @sInOrOut=a.Fopertype,
@sassType2 = isnull(a.FassType2,''),@sassType = isnull(a.FassType,''),@sTransType=a.ftranstype
From t_InvTrans a, #updated b
Where a.FTransNo = b.FTransNo
and a.FEntityNo= b.Fentityno
--更新发料单已发料数量
Update t_PacBillEntry
Set FProQty = A.FProQty + B.Fqty + B.FSpareQty
From t_PacBillEntry As A , #updated B
Where A.FPacNo = B.FsourceNo
and A.Fitemno = B.Fitemno
and A.FSeqID = B.FSourceLineID
and A.FEntityNo = B.FentityNO
if @@Error <> 0
Begin
Rollback
if @stranstype = '07'
select @sReturnInfo = '更新生产发料单信息时出错!'
else
select @sReturnInfo = '更新生产补料单信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--反写发料单状态
if exists (select 1 from t_PacBillEntry
where fentityNo=@entityno
and FPacNo=@sbillNo
and FQty > FProQty)
begin
--取已执行的记录数
if exists (select 1 from t_PacBillEntry
where fentityNo=@entityno
and FPacNo=@sbillNo
and FProQty>0)
set @status='G'
else set @status='A'
end
else set @status='F'
update t_PacBill
set fstatus=@status
where FPacNo= @sBillNo
and fentityNo=@entityno
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新发料单时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
select @ftype = ftype,@fworktype = fworktype from t_PacBill where FPacNo= @sBillNo and fentityNo=@entityno
if @sasstype = '5' or @sasstype = '6' --为转单转接时，统一不考虑模具工单，统一为推式发料
begin
select @fworktype = '0' --为普通工单，确保下面的动作是按普通工单处理
select @ftype = '0' --为推式发料
end
if @fworktype = '0' --当为普通工单 '1'为模具工单的发料单
if @ftype <> '1' -------当为拉式发料单时，统一不反写工单的数量和状态，不管是否有工单号
begin
--更新工单的已发料数量
Update t_WorkOrderEntry
Set FProQty = A.FProQty + B.Fqty + B.FSpareQty
From t_WorkOrderEntry As A , #updated B
Where A.FWoNo = B.FprtsourceNo --工单号
and A.FSubItem = B.Fitemno
and A.FSeqID = B.FprtSourceLineID --工序号
and A.FEntityNo = B.FentityNO
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单发料信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--反写工单状态
--取已执行的记录数
if exists (select 1 From t_WorkOrderEntry As A
Where A.FWoNo =@sorderno --工单号
-- and A.FSubItem = B.Fitemno
-- and A.FSeqID = B.FprtSourceLineID --工序号
and A.FEntityNo = @entityNO
and FProQty >0)
begin
update a
set fstatus='G'
From t_WorkOrder As A
Where A.FWoNo =@sorderno --工单号
and A.FEntityNo = @entityNO
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--取工序表中该领料单发料工作中心对应的最小需汇报工序号，并把该工序设置为开工状态
update a set fstatus = '2' , factbegdate = getdate()
from t_woroute a, #updated b,(select min(d.fwoseqid) as fwoseqid from t_woroute d,#updated e
where d.fentityno = e.fentityno and d.fwono = e.FprtsourceNo and d.fisreport='1' ) c
where a.fentityno = b.fentityno and a.fwono = b.FprtsourceNo and a.fwoseqid=c.fwoseqid and a.fstatus='0'
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新生产工单工序开工状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end
else begin --全部退料，应把所有工序设置为未开工状态
update a
set fstatus='A'
From t_WorkOrder As A
Where A.FWoNo = @sorderNo --工单号
and A.FEntityNo = @entityNO
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
update a set fstatus = '0' , factbegdate = null
from t_woroute a
where a.fentityno =@entityno and a.fwono = @sorderno
-- and a.FWoSeqID = b.FprtSourceLineID
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新生产工单工序状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end --------------------------------------------------------拉式发料单不反写
end
if @fworktype = '1' --为模具工单
if @ftype <> '1' -------当为拉式发料单时，统一不反写工单的数量和状态，不管是否有工单号
begin
declare c_cur cursor for
select fentityno,fprtsourceno,fqty + fspareqty, fitemno,
FprtSourceLineID from #updated
open c_cur
fetch c_cur into @sentityno,@swono,@dqty,@sitemno,@iseqid
while @@fetch_status = 0
begin
select @dsumqty = sum(freqqty) from t_moldwoentry
where fentityno = @sentityno
and fwono = @Swono
and fseqid = @iseqid
and fitemno = @sitemno
-------------按比率平均分配已发料数量（六位小数，所以用下算法，不考虑四舍五入）
Update t_moldWoEntry
Set FProQty = A.FProQty + a.freqqty * @dqty /@dsumqty
From t_moldwoEntry As A
where fentityno = @sentityno
and fwono = @Swono
and fseqid = @iseqid
and fitemno = @sitemno
if @@Error <> 0
Begin
Rollback;
select @sReturnInfo = '更新工单发料信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
Declare @sUseMoldSchedule Char(1), --是否使用胶件排产
@fSumRouteReqQty decimal(18,6), --工序中的总量
@sColorDustNo varchar(25) --色粉编码
Create table #MoldGroupID(FMoldNo varchar(25),FGroupId int) --用于存放水口对应的模具号及分组号
Select @sUseMoldSchedule = FValue from t_SysParam
Where FEntityNo= @EntityNo
and FSysNo = 'PAC'
and FParamNo = 'UseMoldSchedule'
Select @sColorDustNo = FValue --色粉编码
from t_SysParam
where FEntityNo=@EntityNo
and FsysNo='SYS'
and FParamNo='ColorDustClsNo'
if @sUseMoldSchedule = '1' --使用模具生产时要反写工序中胶料数量及水口数量
begin
if exists(select top 1 1 from t_MoldBom t1,t_MoldWoRoute t2
Where t1.FEntityNo= @EntityNo
and t1.FEntityNO = t2.FEntityNo
and t1.FItemNo = t2.FMoldNo
and t1.FRelateItem = @sItemNo) --当前物料为水口料
begin
Insert into #MoldGroupId(FMoldNo,FGroupID)
select distinct t2.FMoldNO,t2.FGroupID from t_MoldBom t1,t_MoldWoRoute t2
Where t1.FEntityNo= @EntityNo
and t1.FEntityNO = t2.FEntityNo
and t1.FItemNo = t2.FMoldNo
and t1.FRelateItem = @sItemNo
Select @fSumRouteReqQty = sum(FGapReqQty)
from t_MoldWoRoute t1,#MoldGroupID t2
Where t1.FEntityNo = @EntityNo
and t1.FWoNo = @sWoNo
and t1.FMoldNo = t2.FMoldNO
and t1.FGroupID = t2.FGroupId
Update t1 set t1.FGapUtterQty = isnull(t1.FGapUtterQty,0)
+ (Case when @fSumRouteReqQty = 0 then 0 else t1.FGapReqQty * @dQty/@fSumRouteReqQty end)
from t_MoldWoRoute t1,#MoldGroupID t2
Where t1.FEntityNo = @EntityNo
and t1.FWoNO = @sWoNo
and t1.FMoldNo =t2.FMOldNO
and t1.FGroupID = t2.FGroupID
end
else if (select fclass from t_item where fentityno = @EntityNo and fitemno = @sItemNo) <> @sColorDustNo --不是水口料,不是色粉
begin
Select @fSumRouteReqQty = sum(FReqSubQty) from t_MoldWoRoute
Where FEntityNO = @EntityNo and FWoNo = @sWoNo
Update t1 set t1.FUtterSubQty = isnull(t1.FUtterSubQty,0)
+ (case when @fSumRouteReqQty = 0 then 0 else t1.FReqSubQty * @dQty/@fSumRouteReqQty end)
from t_MoldWoRoute t1
Where t1.FEntityNo = @EntityNo
and t1.FWoNO = @sWoNo
end
end
fetch c_cur into @sentityno,@swono,@dqty,@sitemno,@iseqid
end
close c_cur
deallocate c_cur
--反写工单状态(使用胶件排产功能时不必写状态)
--取已执行的记录数(因为按比率平均分配，所以所有胶件的状态一样(在同一张工单上))
if exists (select 1 from t_moldWoEntry a
where a.fentityNo=@entityNo
and a.FwoNo=@sorderno
and a.FProQty >0)
begin
set @status='G' --下达
update a set fstatus = '2' , factbegdate = getdate()
from t_moldWoRoute a, #updated b,t_moldWoEntry d
where a.fentityno = b.fentityno
and b.fentityno = d.fentityno
and d.fwono = b.FprtsourceNo
and d.FSeqID = b.FprtSourceLineID
and d.fitemno = b.fitemno
and a.fseqid =d.fseqid
and a.fwono = d.fwono
and d.fproqty>0
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单工序开工状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end
else
begin
set @status='A'
update a set fstatus='0',FActBegDate=null --0 待工 --工序实际开工日期
from t_moldWoRoute A
Where A.FWoNo = @sorderno
and a.fentityNo=@entityNo
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单工序开工状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end
update t_moldWorkOrder
set fstatus=@status
where FwoNo=@sorderno
and fentityNo=@entityno
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end --------------------------------------------------------拉式发料单不反写
if @fworktype = '2' --为复合工单
if @ftype <> '1' -------当为拉式发料单时，统一不反写工单的数量和状态，不管是否有工单号
begin
--复合工单生产发料时(在领料单中记录了其生产类型，数据为2)，
-- 对每一发放物料，取其发放数量按其在总需求数量（在领料单中）的比例，
-- 然后用该比例乘以该物料在各个小工单体中的需求数量就得到其在对应小工单中的发料数量。
-- ???处理后如果有尾差，自动把尾差放到任一个小工单的记录中（注意一定是有该物料的小工单）
select @swono=fprtsourceno,@dqty=fqty + fspareqty, @sitemno=fitemno,
@iseqid=FprtSourceLineID from #updated
select @dsumqty = sum(a.freqqty)
From t_WorkOrderEntry A ,t_WorkOrder b,#updated c
where b.fentityno = c.fentityno
and b.fcomplexwono=c.fprtsourceno
and b.fentityno=a.fentityno
and b.fwono = a.fwono
and a.fsubitem = @sitemno
-------------按比率平均分配已发料数量（六位小数，所以用下算法，不考虑四舍五入）
Update t_WorkOrderEntry
Set FProQty = A.FProQty + (case @dsumqty when 0 then 0 else a.freqqty * @dqty /@dsumqty end)
From t_WorkOrderEntry A ,t_WorkOrder b
where b.fentityno = @entityno
and b.fcomplexwono=@Swono
and b.fentityno=a.fentityno
and b.fwono = a.fwono
and a.fsubitem = @sitemno
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单发料信息时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--反写工单状态
--取已执行的记录数(因为按比率平均分配，所以所有胶件的状态一样(在同一张工单上))
if exists (select 1 from t_WorkOrderEntry a,#updated b,t_workorder c
where a.fentityNo=b.fentityNo
and c.FcomplexwoNo=b.fprtsourceno
and a.fentityNo=c.fentityNo
and a.FwoNo=c.fwono
and a.FProQty >0)
begin
set @status='G' --下达
update a set fstatus = '2' , factbegdate = getdate()
from t_WoRoute a, #updated b,t_WorkOrderEntry d,t_workorder e
where a.fentityno = b.fentityno
and b.fentityno = d.fentityno
and d.fwono = e.fwono
and d.FSeqID = b.FprtSourceLineID
and d.fsubitem = b.fitemno
and a.fwoseqid =d.fseqid
and a.fwono = e.fwono
and d.fproqty>0
and e.FcomplexwoNo=b.fprtsourceno
and a.fentityNo=e.fentityNo
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单工序开工状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end
else begin
set @status='A'
update a set fstatus='0',FActBegDate=null --0 待工 --工序实际开工日期
from t_WoRoute a, #updated b,t_WorkOrderEntry d,t_workorder e
where a.fentityno = b.fentityno
and b.fentityno = d.fentityno
and d.fwono = e.fwono
--and d.FSeqID = b.FprtSourceLineID
--and d.fsubitem = b.fitemno
--and a.fseqid =d.fseqid
and a.fwono = e.fwono
and d.fproqty>0
and e.FcomplexwoNo=b.fprtsourceno
and a.fentityNo=e.fentityNo
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单工序开工状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end
update t_WorkOrder
set fstatus=@status
where FcomplexwoNo=@sorderno
and fentityNo=@entityno
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '更新工单状态时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
end
--步骤：1.来源单号为空的领料和补料不反写 2.库存存在此物料的直接UPdate 3.库存不存在此物料的 INSERT
-- 4.考虑修改和删除,要判断保证UPDATE 和 Insert 后，库存量大于零
if @sBillNo <> ''
begin
update t_WipInv set FQty=a.FQty + B.FQTY
----转单或者是转接时，没有领料单和退料单，所以这儿要考虑清楚
from t_WipInv a,#updated B,t_invtrans c
Where a.FentityNo=b.FentityNo
-- mars 20020413 工作中心库存-领料单号/来源工序号/批号 不必写对应工单号 要写
and a.fwono=b.fprtsourceno
and a.fitemNo=b.FitemNo
and a.FWcNo=c.ForgNo
and b.fentityNo=c.fentityNo
and b.ftransNo=c.ftransNo
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '增加车间库存时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
--增加车间库存
insert into t_WipInv(FEntityNo,FCtrNo,FItemNo,FWcNo,
FWoNo,FMiNo,FQty,FNote,fissubmold) --是否模具子料
select c.fentityNo,c.fctrNo,c.fitemNo,c.FWcNo,
c.fprtsourceNo,c.FMiNo,c.fqty,c.fnote,@fworktype --此字段不支持转单和转结
from (select a.fentityNo,a.fctrNo,a.fitemNo,b.FWcNo,
a.fprtsourceNo,isnull(b.FMiNo,'') as fmino,a.fqty,b.fnote
from #updated a,t_PacBill b
where a.fentityNo=b.fentityNo
and a.fsourceNo=b.FPacNo) c left outer join t_WipInv d
on ( d.fitemNo=c.FitemNo
and d.FWcNo=c.FWcNo
-- 工作中心库存-领料单号/来源工序号/批号 不必写对应工单号 要写
and d.fwono=c.fprtsourceno
)
where d.fentityNo is null
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '增加车间库存时时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
if exists (select 1
from t_WipInv a,#updated B,t_invtrans c
Where a.FentityNo=b.FentityNo
-- 工作中心库存-领料单号/来源工序号/批号 不必写对应工单号 要写
and a.fwono=b.fprtsourceno
and a.fitemNo=b.FitemNo
and a.FWcNo=c.ForgNo
and b.fentityNo=c.fentityNo
and b.ftransNo=c.ftransNo
and a.fqty < 0)
begin
rollback
select @sReturnInfo = '修改车间库存时出错，库存数量小于零!'
RaisError( @sReturnInfo, 16, 1 )
return
End
delete from t_WipInv Where FQty =0 and FitemScrapQty = 0 and FworkScrapQty = 0
end
------------------------------------------------------------------------
-- t_invtrans与t_wiptrans 转单转接对照关系
-- 类型 t_invtrans.ftranstype t_invtrans.fasstype t_wiptrans.ftranstype
-- 转单发料 07 5 4
-- 转单退料 08 5 5
-- 转接入库 10 6 7
-- 转接发料 07 6 6
select top 1 @fwiptransbillno = b.forgno --工作中心
from #updated a,t_invtrans b
where a.fentityNo=b.fentityNo
and a.ftransNo=b.ftransno
select @fwiptransbillno = isnull(max(fwiptransbillno),'') --取最大号码
from t_wiptrans
where fwcno = @fwiptransbillno
and convert(varchar(8),fdate,112) = convert(char(8),getdate(),112)
--求流水号
select @fwiptransbillno = convert(varchar(4),convert(int,right(@fwiptransbillno,4)) + 1)
select @fwiptransbillno = replicate('0',4 - len(@fwiptransbillno)) + @fwiptransbillno
insert into t_WipTrans (FEntityNo,FCtrNo,FWcNo,FTransID,FPacNo,
FItemNo,FQty,FTransType,FOperator,FOpeName,fwono,
FDate,FSourDestNo,FSourDestName,FSourDestType,fwiptransbillno,
FsourPacNo,FsourSeqID,FbatchNo,FWipTransLineID )
select a.fentityNo,a.fctrNo,b.ForgNo,a.flineid,a.FSourceNo,
a.FItemNo,a.fqty,
case b.fasstype when '5' then '4' when '6' then '6' else '0' end, --求t_wiptrans.ftranstype
b.foperator,b.fopername,a.Fprtsourceno,
getdate(),b.fwhNo,b.fwhname,'0',
b.ForgNo + '-' + convert(char(8),getdate(),112)+ '-' + @fwiptransbillno,
a.FSourceNo,a.FsourcelineID,a.fbatchno,1
from #updated a,t_invtrans b
where a.fentityNo=b.fentityNo
and a.ftransNo=b.ftransno
if @@Error <> 0
Begin
Rollback
select @sReturnInfo = '增加车间库存流水时时出错!'
RaisError( @sReturnInfo, 16, 1 )
return
End
return
End
GO
通变库存管理信息系统的开发与应用(五)由毕业论文网（www.huoyuandh.com）会员上传。
