if not exists(select * from StGoodsCheck3 where isnull(整理标志,'')<>'是')
raiserror('没有整理的盘点表!!',16,1)
select 盘点表编号 into #TabTemp1 From StGoodsCheck3 where isnull(整理标志,'')<>'是'
SELECT distinct 商品编号, 盘点表编号 into #TabTemp2
FROM StGoodsCheck4
where 盘点表编号 in (select 盘点表编号 from #TabTemp1)
SELECT top 0 成本价, 商品编号, 库存数量, 库存金额, 盘存数量, 盘存金额, 损益数量, 损益金额,
盘点表编号 into #TabTemp3
FROM StGoodsCheck4
insert into #TabTemp3(商品编号,盘存数量,成本价,盘点表编号)
select 商品编号,sum(isnull(盘存数量,0)),成本价,1 from StGoodsCheck4
where 盘点表编号 in (select 盘点表编号 from #TabTemp1)
group by 商品编号,成本价
update #TabTemp3 set 库存数量=B.实际库存,库存金额=B.库存金额 from (
SELECT 商品编号, Sum(isnull(实际库存,0)) as 实际库存
,sum(isnull(实际库存,0)*isnull(结算价,0)) as 库存金额 FROM StWareStocProstk
group by 商品编号) B inner join #TabTemp3 A on A.商品编号=B.商品编号
update #TabTemp3 set 库存数量=0
where isnull(库存数量,0)=0
update #TabTemp3 set 成本价=round(库存金额/库存数量,6)
where isnull(库存数量,0)<>0
update #TabTemp3 set 成本价=B.商品进价
from #TabTemp3 A inner join BaWareFile B on A.商品编号=B.商品编号
where isnull(成本价,0)=0
update #TabTemp3 set 盘存金额=round(isnull(盘存数量,0)*isnull(成本价,0),2),
损益数量=isnull(盘存数量,0)-isnull(库存数量,0)
update #TabTemp3 set 损益金额=isnull(盘存金额,0)-isnull(库存金额,0)
delete from StGoodsCheck4
where 盘点表编号 in (select 盘点表编号 from #TabTemp1)
update #TabTemp3 set 盘点表编号 =A.盘点表编号 from #TabTemp2 A inner join #TabTemp3 B
on A.商品编号=B.商品编号