--exec SP_WInventoryAccount 39,0,1
CREATE PROC [dbo].[SP_WInventoryAccount]
@doccode int,
@refcode int,
@mode int
AS
BEGIN
SET NOCOUNT ON
-----当前临时表 #WH5
--- mode = 1 销售出库
--- mode = 2 退货/退残
--- mode = 3 采购入库
--- mode = 4 采购退货
--- mode = 5 销售直接退货
--- mode = 6 采购直接退货
--- mode = 7 其他出库
--- mode = 8 其他入库
--- mode = 10 调拨过账
--- mode = 12 生产发料出库帐
--- mode = 13 生产收货入库账
--- mode = 14 生产退货出库帐
--- mode = 15 生产退货入库帐
if @mode in(2,3)
begin
select a.fid,a.sid,a.hid,billno,docdate,billtype,workpNo,workpartners,b.whscode,b.refcode,itemCode,itemName,sum(isnull(quantity,0))quantity,unit,price,location,refcid,batcode into #WH
from dwd_3 a with(nolock) inner join dwc_3 b with(nolock) on a.hid=b.hid where a.hid=@doccode
group by a.fid,a.sid,a.hid,billno,docdate,billtype,workpNo,workpartners,whscode,b.refcode,itemCode,itemName,unit,price,location,refcid,batcode
end
if @mode in(1,4)
begin
select a.fid,a.sid,a.hid,billno,docdate,billtype,workpNo,workpartners,whscode,b.refcode,itemCode,itemName,sum(isnull(quantity,0))quantity,unit,price,location,refcid,batcode into #WH2
from dwd_36 a with(nolock) inner join dwc_36 b with(nolock) on a.hid=b.hid where a.hid=@doccode
group by a.fid,a.sid,a.hid,billno,docdate,billtype,workpNo,workpartners,whscode,b.refcode,itemCode,itemName,unit,price,location,refcid,batcode
end
if @mode=5
begin
select a.fid,a.sid,a.hid,billno,whscode,itemCode,xscode,partnerid,docdate,itemName,sum(isnull(quantity,0))quantity,unit,price,location,refcid,batcode into #WH3
from dwd_27 a with(nolock) inner join dwc_27 b with(nolock) on a.hid=b.hid where a.hid=@doccode
group by a.fid,a.sid,a.hid,billno,whscode,itemCode,xscode,partnerid,docdate,itemName,unit,price,location,refcid,batcode
----回填订单数量
update DWC_5 set thdigit=isnull(thdigit,0)+c.quantity
from DWD_5 a with(nolock) inner join DWC_5 b with(nolock) on a.hid=b.hid
inner join #WH3 c on b.itemcode=c.itemcode and b.cid=c.refcid and a.hid=c.xscode
----即时库存
update ditw set onHand=isnull(a.onHand,0)+isnull(quantity,0)
from ditw a with(nolock) inner join ditm b with(nolock) on a.fid=b.id
inner join (select itemcode,whscode,sum(quantity) as quantity from #WH3 group by itemcode,whscode) c on b.fathercode=c.itemCode and a.whscode=c.whscode
if @@rowcount=0
begin
insert into ditw(whscode,owhs,onhand,fid,w_iswhsmain,fathercode)
select c.whscode,d.whouse,isnull(quantity,0),b.id,'是',b.fathercode
from ditm b with(nolock) inner join (select itemcode,whscode,sum(quantity) as quantity from #WH3 group by itemcode,whscode) c on b.fathercode=c.itemCode
inner join vwhscode d on c.whscode=d.whscode
where not exists(select 1 from ditw a where a.fid=b.id and a.whscode=c.whscode)
end
----进出明细
insert into DWSD
select getdate(),whscode,location,billno,xscode,'销售退货',docdate,partnerid,itemCode,convert(varchar(7),docdate,120),unit,
price,0,quantity,fid,sid,hid,batcode from #WH3
drop table #WH3
end
if @mode=6
begin
select a.fid,a.sid,a.hid,billno,b.whscode,itemCode,b.cgcode,partnerid,docdate,itemName,sum(isnull(quantity,0))quantity,unit,price,location,refcid,batcode into #WH4
from dwd_29 a with(nolock) inner join dwc_29 b with(nolock) on a.hid=b.hid where a.hid=@doccode
group by a.fid,a.sid,a.hid,billno,whscode,itemCode,cgcode,partnerid,docdate,itemName,unit,price,location,refcid,batcode
----回填订单数量
update DWC_8 set thdigit=isnull(thdigit,0)+c.quantity
from DWD_8 a with(nolock) inner join DWC_8 b with(nolock) on a.hid=b.hid
inner join #WH4 c on b.itemcode=c.itemcode and a.hid=c.cgcode and b.cid=c.refcid
----即时库存
update ditw set onHand=isnull(a.onHand,0)-isnull(quantity,0)
from ditw a with(nolock) inner join ditm b with(nolock) on a.fid=b.id
inner join (select sum(quantity) as quantity,itemcode,whscode
from #WH4 group by itemcode,whscode) c on b.fathercode=c.itemCode and a.whscode=c.whscode
----进出明细
insert into DWSD
select getdate(),whscode,location,billno,cgcode,'采购退货',docdate,partnerid,itemCode,convert(varchar(7),docdate,120),unit,
price,0,quantity,fid,sid,hid,batcode from #WH4
drop table #WH4
end
IF @mode=1
BEGIN
if exists(select 1 from #WH2 a inner join ditm b with(nolock) on b.fathercode=a.itemCode
where not exists(select 1 from ditw c with(nolock) where a.whscode=c.whscode and b.id=c.fid))
begin
raiserror('仓库不存在物料编号，请检查',16,1)
return
end
----回填订单数量
update DWC_5 set ckdigit=isnull(ckdigit,0)+c.quantity
from DWD_5 a with(nolock) inner join DWC_5 b with(nolock) on a.hid=b.hid
inner join #WH2 c on b.itemcode=c.itemcode and a.hid=c.refcode and b.cid=c.refcid
----即时库存
update ditw set onHand=isnull(a.onHand,0)-isnull(quantity,0),isCommited=isnull(a.isCommited,0)-isnull(quantity,0)
from ditw a with(nolock) inner join ditm b with(nolock) on a.fid=b.id
inner join (select sum(quantity) as quantity,itemcode,whscode
from #WH2 group by itemcode,whscode) c on b.fathercode=c.itemCode and a.whscode=c.whscode
----进出明细
insert into DWSD
select getdate(),whscode,location,billno,refcode,billtype,docdate,workPno,itemCode,convert(varchar(7),docdate,120),unit,
price,0,quantity,fid,sid,hid,batcode from #WH2
drop table #WH2
END
IF @mode=2
BEGIN
---回填订单数量
update DWC_27 set rkdigit=isnull(rkdigit,0)+c.quantity
from DWD_27 a with(nolock) inner join DWC_27 b with(nolock) on a.hid=b.hid
inner join #WH c on b.itemcode=c.itemcode
where a.hid=@refcode
----即时库存
update ditw set onHand=isnull(a.onHand,0)-isnull(quantity,0),isCommited=isnull(a.isCommited,0)-isnull(quantity,0)
from ditw a with(nolock) inner join ditm b with(nolock) on a.fid=b.id
inner join #WH c on b.fathercode=c.itemCode and a.whscode=c.whscode
insert into ditw(whscode,owhs,onhand,fathercode,fid)
select a.whscode,d.whouse,quantity,a.itemCode,b.id from #WH a inner join ditm b with(nolock) on a.itemCode=b.fathercode
inner join dwhs d with(nolock) on a.whscode=d.whscode
where not exists(select 1 from ditw c with(nolock) where a.whscode=c.whscode and b.id=c.fid)
----进出明细
insert into DWSD
select getdate(),whscode,location,billno,refcode,billtype,docdate,workPno,itemCode,convert(varchar(7),docdate,120),unit,
price,quantity,0,fid,sid,hid,batcode from #WH
drop table #WH
END
IF @mode=3
BEGIN
---回填订单入库数量
update DWC_8 set rkdigit=isnull(rkdigit,0)+c.quantity
from DWD_8 a with(nolock) inner join DWC_8 b with(nolock) on a.hid=b.hid
inner join #WH c on b.itemcode=c.itemcode and a.hid=c.refcode and b.cid=c.refcid
update DWD_8 set rkflag=1 --入库完成标记
from DWD_8 a with(nolock) inner join #WH c on a.hid=c.refcode
where not exists(select 1
from DWC_8 b with(nolock) where a.hid=b.hid and b.quantity>isnull(rkdigit,0)+isnull(thdigit,0)+isnull(qxdigit,0))
update DWD_8 set rkdate=getdate() --最近入库时间
from DWD_8 a with(nolock) inner join #WH c on a.hid=c.refcode
----即时库存
update ditw set onHand=isnull(a.onHand,0)+isnull(quantity,0),onOrder=isnull(a.onOrder,0)-isnull(quantity,0)
from ditw a with(nolock) inner join ditm b with(nolock) on a.fid=b.id
inner join (select sum(quantity) as quantity,itemcode,whscode
from #WH group by itemcode,whscode)c on b.fathercode=c.itemCode and a.whscode=c.whscode
insert into ditw(whscode,owhs,onhand,fathercode,fid)
select a.whscode,d.whouse,quantity,a.itemcode,b.id from (select sum(quantity) as quantity,itemcode,whscode
from #WH group by itemcode,whscode) a inner join ditm b with(nolock) on a.itemCode=b.fathercode
inner join dwhs d with(nolock) on a.whscode=d.whscode
where not exists(select 1 from ditw c with(nolock) where a.whscode=c.whscode and b.id=c.fid)
----进出明细
insert into DWSD(insertTime,whouseID,location,doccode,refcode,doctype,docdate,cltcode,itemcode,periodid,uom,price,innum,outNum,fid,sid,hid,batcode)
select getdate(),whscode,location,billno,refcode,billtype,docdate,workPno,itemCode,convert(varchar(7),docdate,120),unit,
price,quantity,0,fid,sid,hid,batcode from #WH
drop table #WH
END
IF @mode=4
BEGIN
----回填订单数量
update DWC_29 set ckdigit=isnull(ckdigit,0)+c.quantity
from DWD_29 a with(nolock) inner join DWC_29 b with(nolock) on a.hid=b.hid
inner join #WH2 c on b.itemcode=c.itemcode
----即时库存
update ditw set onOrder=isnull(a.onOrder,0)-isnull(quantity,0),onHand=isnull(a.onHand,0)-isnull(quantity,0)
from ditw a with(nolock) inner join ditm b with(nolock) on a.fid=b.id
inner join #WH2 c on b.fathercode=c.itemCode and a.whscode=c.whscode
----进出明细
insert into DWSD
select getdate(),whscode,location,billno,refcode,billtype,docdate,workPno,itemCode,convert(varchar(7),docdate,120),unit,
price,0,quantity,fid,sid,hid,batcode from #WH2
drop table #WH2
END
if @mode=7
begin --其他出库
update ditw set onHand=isnull(a.onHand,0)-isnull(c.quantity,0)
from ditw a inner join DWD_39 b on a.whscode=b.whscode inner join (select hid,itemid,itemcode,sum(quantity) as quantity from DWC_39
where hid=@doccode group by hid,itemid,itemcode) c on b.hid=c.hid and a.fid=c.itemid
where b.hid=@doccode
insert into ditw(whscode,owhs,onhand,fathercode,fid)
select a.whscode,d.whouse,0-quantity,c.itemcode,b.id
from DWD_39 a inner join (select hid,itemid,itemcode,sum(quantity) as quantity from DWC_39
where hid=@doccode group by hid,itemid,itemcode) c on a.hid=c.hid
inner join ditm b with(nolock) on c.itemCode=b.fathercode inner join vwhscode d with(nolock) on a.whscode=d.whscode
where a.hid=@doccode and not exists(select 1 from ditw e with(nolock) where a.whscode=e.whscode and b.id=e.fid)
insert into DWSD(inserttime,whouseid,doctype,docdate,cltcode,itemcode,periodid,price,outnum,fid,sid,hid,batcode)
select getdate(),a.whscode,'其他出库',a.docdate,a.workpno,b.itemcode,convert(varchar(7),a.docdate,120),b.price,b.quantity,a.fid,a.sid,a.hid,batcode
from DWD_39 a with(nolock) inner join DWC_39 b with(nolock) on a.hid=b.hid
where a.hid=@doccode
end
if @mode=8
begin --其他入库
update ditw set onHand=isnull(a.onHand,0)+isnull(c.quantity,0)
from ditw a inner join DWD_39 b on a.whscode=b.whscode inner join (select hid,itemid,itemcode,sum(quantity) as quantity from DWC_39
where hid=@doccode group by hid,itemid,itemcode) c on b.hid=c.hid and a.fid=c.itemid
where b.hid=@doccode
insert into ditw(whscode,owhs,onhand,fathercode,fid)
select a.whscode,d.whouse,quantity,c.itemcode,b.id
from DWD_39 a inner join (select hid,itemid,itemcode,sum(quantity) as quantity from DWC_39
where hid=@doccode group by hid,itemid,itemcode) c on a.hid=c.hid
inner join ditm b with(nolock) on c.itemCode=b.fathercode inner join vwhscode d with(nolock) on a.whscode=d.whscode
where a.hid=@doccode and not exists(select 1 from ditw c with(nolock) where a.whscode=c.whscode and b.id=c.fid)
insert into DWSD(inserttime,whouseid,doctype,docdate,cltcode,itemcode,periodid,price,innum,fid,sid,hid,batcode,refcode)
select getdate(),a.whscode,a.billtype,a.docdate,a.workpno,b.itemcode,convert(varchar(7),a.docdate,120),b.price,b.quantity,a.fid,a.sid,a.hid,batcode,a.refcode
from DWD_39 a with(nolock) inner join DWC_39 b with(nolock) on a.hid=b.hid
where a.hid=@doccode
end
if @mode=10
begin --调拨 select * from dwd_20
select billcode,billdate,whsFrom,locationFrom,whscode,location,itemcode,itemName,barcode,unit,color,
colorName,quantity,price,a.fid,a.sid,a.hid,batcode into #WH5 from dwd_20 a with(nolock) inner join dwc_20 b with(nolock) on a.hid=b.hid
where a.hid=@doccode
----更新调出仓库库存
update ditw set OnHand=isnull(b.OnHand,0)-isnull(quantity,0)
from ditw b inner join ditm c on b.fid=c.id inner join (select itemcode,whsfrom,sum(quantity) as quantity from #WH5
group by itemcode,whsfrom) a on a.whsfrom=b.whscode and a.itemcode=c.fathercode
insert into ditw(whscode,owhs,OnHand,fathercode,fid)
select a.whsfrom,d.whouse,0-a.quantity,a.itemcode,b.id
from ditm b inner join (select itemcode,whsfrom,sum(quantity) as quantity from #WH5
group by itemcode,whsfrom) a on a.itemcode=b.fathercode inner join dwhs d with(nolock) on a.whsfrom=d.whscode
where not exists(select 1 from ditw c with(nolock) where a.whsfrom=c.whscode and b.id=c.fid)
----更新调入仓库库存
update ditw set OnHand=isnull(b.OnHand,0)+isnull(quantity,0)
from ditw b inner join ditm c on b.fid=c.id inner join (select itemcode,whscode,sum(quantity) as quantity from #WH5
group by itemcode,whscode) a on a.whscode=b.whscode and a.itemcode=c.fathercode
insert into ditw(whscode,owhs,OnHand,fathercode,fid)
select a.whscode,d.whouse,a.quantity,a.itemcode,b.id
from ditm b inner join (select itemcode,whscode,sum(quantity) as quantity from #WH5
group by itemcode,whscode) a on a.itemcode=b.fathercode
inner join dwhs d with(nolock) on a.whscode=d.whscode
where not exists(select 1 from ditw c with(nolock) where a.whscode=c.whscode and b.id=c.fid)
--插入出库明细
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,outnum,fid,sid,hid,batcode)
select getdate(),whsfrom,'调拨出库',billdate,itemcode,convert(varchar(7),billdate,120),price,quantity,fid,sid,hid,batcode
from #WH5
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,innum,fid,sid,hid,batcode)
select getdate(),whscode,'调拨入库',billdate,itemcode,convert(varchar(7),billdate,120),price,quantity,fid,sid,hid,batcode
from #WH5
end
if @mode=12 --生产发料
begin
update ditw set iscommited=isnull(a.iscommited,0)-b.dight,onhand=isnull(a.onhand,0)-b.dight
from ditw a inner join ditm c on a.fid=c.id inner join (select itemcode,whscode,sum(dight) as dight
from DWC_43 with(nolock) where hid=@doccode group by itemcode,whscode) b on c.fathercode=b.itemcode and a.whscode=b.whscode --更新已承诺,库存
if @@rowcount=0
begin
raiserror('更新错误',16,1)
return
end
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,outnum,fid,sid,hid,batcode)
select getdate(),b.whscode,'生产发料',a.docdate,b.itemcode,convert(varchar(7),a.docdate,120),b.matcost,dight,a.fid,a.sid,a.hid,batcode
from DWD_43 a INNER JOIN DWC_43 b on a.hid=b.hid
where a.hid=@doccode
update DWC_41 set fhdigit=isnull(a.fhdigit,0)+b.dight
from DWC_41 a inner join (SELECT billno,refcid,itemcode,sum(dight) as dight from DWC_43 with(nolock) where hid=@doccode group by billno,refcid,itemcode) b
on a.itemcode=b.itemcode and a.hid=b.billno and a.cid=b.refcid
update pp_orderemploy set wcdigit=e.fhdigit
from pp_orderemploy a inner join
(select billno,b.itemcode,c.itemcode as matcode,sum(c.fhdigit) as fhdigit
from DWD_41 b inner join DWC_41 c on b.hid=c.hid
where b.hid=@doccode
group by billno,b.itemcode,c.itemcode) e on a.ppno=e.billno and a.itemcode=e.itemcode and a.matcode=e.matcode
exec doa_modifypporder1
end
if @mode=13 --生产收货
begin
update ditw set OnOrder=isnull(a.OnOrder,0)-b.dight,onhand=isnull(a.onhand,0)+b.dight
from ditw a inner join ditm c on a.fid=c.id inner join (select itemcode,whscode,sum(dight) as dight
from DWC_46 with(nolock) where hid=@doccode group by itemcode,whscode) b on c.fathercode=b.itemcode and a.whscode=b.whscode
if @@rowcount=0
begin
raiserror('更新错误',16,1)
return
end
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,innum,fid,sid,hid,batcode)
select getdate(),b.whscode,'生产收货',a.docdate,b.itemcode,convert(varchar(7),a.docdate,120),b.price,dight,a.fid,a.sid,a.hid,batcode
from DWD_46 a INNER JOIN DWC_46 b on a.hid=b.hid
where a.hid=@doccode
update DWD_41 set fhdigit=isnull(a.fhdigit,0)+b.dight
from DWD_41 a inner join (select billno,itemcode,sum(dight) as dight from DWC_46 with(nolock) where hid=@doccode
group by billno,itemcode) b on a.itemcode=b.itemcode and a.hid=b.billno
if exists(select 1 from DWC_41 a inner join DWC_46 b on a.hid=b.billno --倒冲过账
where b.hid=@doccode and a.fhtype='倒冲')
begin
select @doccode as hid,c.billno,b.itemid,b.itemcode,b.itemname,0 as price,b.whscode,case when isnull(a.digit,0)-isnull(a.fhdigit,0)=c.dight then b.planid-isnull(b.fhdigit,0)
else b.planid*c.dight/a.digit end dight,b.cid into #wh6
from DWD_41 a inner join DWC_41 b on a.hid=b.hid inner join DWC_46 c on b.hid=c.billno
where c.hid=@doccode and b.fhtype='倒冲'
update ditw set iscommited=isnull(a.iscommited,0)-b.dight,onhand=isnull(a.onhand,0)-b.dight
from ditw a inner join ditm c on a.fid=c.id inner join (select itemcode,whscode,sum(dight) as dight from #wh6
group by itemcode,whscode) b on c.fathercode=b.itemcode and a.whscode=b.whscode --更新已承诺,库存
if @@rowcount=0
begin
raiserror('更新错误1',16,1)
return
end
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,outnum,fid,sid,hid)
select getdate(),b.whscode,'生产倒冲出库',a.docdate,b.itemcode,convert(varchar(7),a.docdate,120),b.price,dight,a.fid,a.sid,a.hid
from DWD_46 a INNER JOIN #wh6 b on a.hid=b.hid
where a.hid=@doccode
update DWC_41 set fhdigit=isnull(a.fhdigit,0)+b.dight
from DWC_41 a inner join (select itemcode,billno,cid,sum(dight) as dight from #wh6
group by itemcode,billno,cid) b on a.itemcode=b.itemcode and a.hid=b.billno and a.cid=b.cid
end
end
if @mode=14 --生产退货发货出库帐
begin
update ditw set iscommited=isnull(a.iscommited,0)-b.dight,onhand=isnull(a.onhand,0)-b.dight
from ditw a inner join DWC_50 b on a.fathercode=b.itemcode and a.whscode=b.whscode
where b.hid=@doccode --更新已承诺,库存
if @@rowcount=0
begin
raiserror('更新错误',16,1)
return
end
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,outnum,fid,sid,hid,batcode)
select getdate(),b.whscode,'生产退货发货',a.docdate,b.itemcode,convert(varchar(7),a.docdate,120),b.matcost,dight,a.fid,a.sid,a.hid,batcode
from DWD_50 a INNER JOIN DWC_50 b on a.hid=b.hid
where a.hid=@doccode
if exists(select 1 from DWC_48 a inner join DWC_50 b on a.hid=b.billno --倒冲过账
where b.hid=@doccode and a.fhtype='倒冲')
begin
select @doccode as hid,c.billno,b.itemid,b.itemcode,b.itemname,0 as matcost,b.whscode,
case when isnull(a.digit,0)-isnull(a.yfdigit,0)=c.dight then b.planid-isnull(b.fhdigit,0)
else b.planid*c.dight/a.digit end dight,b.cid into #wh7
from DWD_48 a inner join DWC_48 b on a.hid=b.hid inner join DWC_50 c on b.hid=c.billno
where c.hid=@doccode and b.fhtype='倒冲'
update ditw set OnOrder=isnull(a.OnOrder,0)-b.dight,onhand=isnull(a.onhand,0)+b.dight
from ditw a inner join ditm c on a.fid=c.id inner join (select itemcode,whscode,sum(dight) as dight from #wh7
group by itemcode,whscode) b on c.fathercode=b.itemcode and a.whscode=b.whscode --更新已订购,库存
if @@rowcount=0
begin
raiserror('更新错误',16,1)
return
end
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,innum,fid,sid,hid)
select getdate(),b.whscode,'退货倒冲入库',a.docdate,b.itemcode,convert(varchar(7),a.docdate,120),b.matcost,dight,a.fid,a.sid,a.hid
from DWD_50 a INNER JOIN #wh7 b on a.hid=b.hid
where a.hid=@doccode
update DWC_48 set fhdigit=isnull(a.fhdigit,0)+b.dight
from DWC_48 a inner join (select itemcode,billno,cid,sum(dight) as dight from #wh7
group by itemcode,billno,cid) b on a.itemcode=b.itemcode and a.hid=b.billno and a.cid=b.cid
drop table #wh7
end
update DWD_48 set yfdigit=isnull(a.yfdigit,0)+b.dight
from DWD_48 a inner join (select itemcode,billno,sum(dight) as dight from DWC_50 where hid=@doccode group by itemcode,billno) b
on a.itemcode=b.itemcode and a.hid=b.billno
end
if @mode=15 --生产退货收货入库帐
begin
update ditw set OnOrder=isnull(a.OnOrder,0)-b.dight,onhand=isnull(a.onhand,0)+b.dight
from ditw a inner join ditm c on a.fid=c.id inner join (select itemcode,whscode,sum(dight) as dight from
DWC_52 where hid=@doccode group by itemcode,whscode) b on c.fathercode=b.itemcode and a.whscode=b.whscode --更新已订购,库存
if @@rowcount=0
begin
raiserror('更新错误',16,1)
return
end
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,innum,fid,sid,hid,batcode)
select getdate(),b.whscode,'生产退货收货',a.docdate,b.itemcode,convert(varchar(7),a.docdate,120),b.price,dight,a.fid,a.sid,a.hid,batcode
from DWD_52 a INNER JOIN DWC_52 b on a.hid=b.hid
where a.hid=@doccode
update DWC_48 set fhdigit=isnull(a.fhdigit,0)+b.dight
from DWC_48 a inner join (select itemcode,billno,sum(dight) as dight from DWC_52 where hid=@doccode group by itemcode,billno)
b on a.itemcode=b.itemcode and a.hid=b.billno
end
if @mode=16 --生产退件
begin
update ditw set iscommited=isnull(a.iscommited,0)+b.dight,onhand=isnull(a.onhand,0)+b.dight
from ditw a inner join ditm c on a.fid=c.id inner join (select itemcode,whscode,sum(dight) as dight from DWC_46 with(nolock)
where hid=@doccode group by itemcode,whscode) b on c.fathercode=b.itemcode and a.whscode=b.whscode
if @@rowcount=0
begin
raiserror('更新错误',16,1)
return
end
insert into DWSD(inserttime,whouseid,doctype,docdate,itemcode,periodid,price,innum,fid,sid,hid,batcode)
select getdate(),b.whscode,'生产退件',a.docdate,b.itemcode,convert(varchar(7),a.docdate,120),b.price,dight,a.fid,a.sid,a.hid,batcode
from DWD_46 a INNER JOIN DWC_46 b on a.hid=b.hid
where a.hid=@doccode
update DWC_41 set fhdigit=isnull(a.fhdigit,0)-b.dight
from DWC_41 a inner join (select itemcode,billno,refcid,sum(dight) as dight from DWC_46 where hid=@doccode
group by itemcode,billno,refcid) b on a.itemcode=b.itemcode and a.hid=b.billno and a.cid=b.refcid
end
END