批号分摊（预调拨可用于门店铺货）sql存储过程

USE [jxgx]
GO
/****** Object:  StoredProcedure [dbo].[batch_ft]    Script Date: 02/10/2015 17:19:46 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[batch_ft]
      @outwhid char(11)
      ,@inwhid char(11)
      ,@whorgid char(11)
 as
 
 declare @errcode integer
 select @errcode = 7000        
   
 declare @return integer
 set @return=0


 
  --启动事务处理
 declare @tran_point int
 set @tran_point = @@trancount
 if @tran_point = 0
  begin tran tran_batch_ft
 else
  save tran tran_batch_ft
 
if exists (select * from sysobjects where id = object_id(N'[dbo].[#anglect]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table #anglect
if exists (select * from sysobjects where id = object_id(N'[dbo].[#mdbalance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table #mdbalance
if exists (select * from sysobjects where id = object_id(N'[dbo].[#zbbalance]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) 
drop table #zbbalance


create table #anglect(
[inLocatId] [char](11) NULL,
[outLocatId] [char](11) NULL,
[GoodsId] [char](11) NULL,
[OwnerId] [char](11) NULL,
[AngleId] [char](11) NULL,
[EntId] [char](11) NULL,
[Num] [decimal](14, 2) NULL,
[MovePrice] [decimal](14, 6) NULL
)
--查询销售日均情况--入库
select (sum(b.BaseNum) - (SUM(w.PlaceNum)-SUM(isnull(x.placeNum,0)))) as basenum,
b.WHId,b.LocatId,b.OwnerId,b.GoodsId into #mdbalance
from saleOutMt a
join SaleOutDt b on a.BillNo = b.BillNo and a.EntId = b.EntId
join ANGLEBALANCE w on b.AngleId= w.AngleId and b.GoodsId = w.GoodsId
and b.EntId = w.EntId and b.OwnerId = w.OwnerId and b.LocatId = w.LocatId
left join (select 
sum(basenum) as placeNum,a.AngleId,a.OwnerId,a.LocatId,a.EntId,a.WHId,a.GoodsId
from GOODSOCCU a
group by a.AngleId,a.OwnerId,a.LocatId,a.EntId,a.WHId,a.GoodsId) x on b.GoodsId = x.GoodsId
and b.OwnerId = x.OwnerId and b.LocatId = x.LocatId and b.EntId = x.EntId
and b.GoodsId = x.GoodsId and b.WHId = x.WHId
where a.ruleid = '4szcn61oa8b0tz79'
--日期设置 前3天销量
--and a.Dates between CONVERT(varchar(100) ,DATEADD(DD,-4,getdate()),23) and CONVERT(varchar(100) ,DATEADD(DD,-1,getdate()),23)
--日期设置 昨天销量
and a.Dates = CONVERT(varchar(100) ,DATEADD(DD,-1,getdate()),23)
and b.WHId = @inwhid
group by b.WHId,b.LocatId,b.OwnerId,b.GoodsId
having (sum(b.BaseNum) - (SUM(w.PlaceNum)-SUM(isnull(x.placeNum,0)))) > 0




declare @basenum int
declare @GoodsId char(11)
declare @locatid char(11)
declare @ownerid char(11)
declare pcurr cursor for select basenum,goodsid,locatid,ownerid from #mdbalance
open pcurr  
fetch next from pcurr into @basenum,@GoodsId,@locatid,@ownerid
while (@@fetch_status = 0)  
begin
declare @indexNum int
set @indexNum = @basenum
--查询整件的库存--出库
select a.GoodsId,a.EntId,a.LocatId,a.AngleId,a.OwnerId,d.ValDate
,SUM(a.PlaceNum) - SUM(isnull(x.basenum,0)) as placeNum
into #zbbalance
from ANGLEBALANCE a
join STOREROOM b on a.LocatId = b.LocatId
join STOREHOUSE c on b.WHId = c.WHId
join BATCHCODE d on a.GoodsId = d.GoodsId and a.AngleId = d.AngleId
left join (select 
sum(basenum) as basenum,a.AngleId,a.OwnerId,a.LocatId,a.EntId,a.WHId,a.GoodsId
from GOODSOCCU a
group by a.AngleId,a.OwnerId,a.LocatId,a.EntId,a.WHId,a.GoodsId) x on a.GoodsId = x.GoodsId
and a.OwnerId = x.OwnerId and a.LocatId = x.LocatId and a.EntId = x.EntId
and a.GoodsId = x.GoodsId and c.WHId = x.WHId
where c.WHId = @outwhid
and a.GoodsId = @GoodsId
and a.OwnerId = @ownerid
group by a.GoodsId,a.EntId,a.LocatId,a.AngleId,a.OwnerId,d.ValDate
having SUM(a.PlaceNum) - SUM(isnull(x.basenum,0)) > 0
order by d.ValDate
----批量分摊批号处理
while @indexNum > 0 
begin
---不存在时跳出循环
if not exists (select top 1 placenum from #zbbalance where placenum > 0)
begin
  set @indexNum = 0;
  break;
end
--满足最大批号要求，进行下一步处理
if @indexNum > (select top 1 PlaceNum from #zbbalance)
begin
--插入暂存表中
insert into #anglect(inLocatId,outLocatId,goodsid,ownerid,angleid,num,EntId)
select @locatid,locatid,goodsid,ownerid,angleid,PlaceNum,EntId
from #zbbalance
where AngleId = (select top 1 AngleId PlaceNum from #zbbalance)
and GoodsId = (select top 1 GoodsId from #zbbalance)
and LocatId = (select top 1 LocatId PlaceNum from #zbbalance)
and OwnerId = (select top 1 OwnerId PlaceNum from #zbbalance) 
---减掉已经处理过的数量
set @indexNum = @indexNum - (select top 1 PlaceNum from #zbbalance);
--删除处理过的总部库存记录
delete from #zbbalance where AngleId = (select top 1 AngleId PlaceNum from #zbbalance)
and GoodsId = (select top 1 GoodsId from #zbbalance)
and LocatId = (select top 1 LocatId PlaceNum from #zbbalance)
and OwnerId = (select top 1 OwnerId PlaceNum from #zbbalance)
if @@error <> 0 or @@rowcount = 0
  begin
  set @return=11
  goto err_lab
  end
end 
  --总部库存完全满足时
 if @indexNum <= (select top 1 PlaceNum from #zbbalance)
--插入暂存表中
begin 
insert into #anglect(inLocatId,outLocatId,goodsid,ownerid,angleid,num,EntId)
select @locatid,locatid,goodsid,ownerid,angleid,@indexNum,EntId
from #zbbalance
where AngleId = (select top 1 AngleId PlaceNum from #zbbalance)
and GoodsId = (select top 1 GoodsId from #zbbalance)
and LocatId = (select top 1 LocatId PlaceNum from #zbbalance)
and OwnerId = (select top 1 OwnerId PlaceNum from #zbbalance) 
--全部满足后跳出循环
set @indexNum = 0;
break;
 if @@error <> 0 or @@rowcount = 0
  begin
  set @return=12
  goto err_lab
  end
end
end
if @indexNum < = 0
begin
drop table #zbbalance
end
 fetch next from pcurr into @basenum,@GoodsId,@locatid,@ownerid  
end
close pcurr
deallocate pcurr 


---铺货检索


select distinct
b.LocatCode as outlocatcode,b.LocatName as outlocatname,b.LocatId as OutLocatId
,j.LocatCode as inlocatcode,j.LocatName as inlocatname,j.LocatId as inLocatId
,d.BatchCode,d.ProduceDate,d.ValDate,d.AngleId
,d.gebjj as Cost,d.gebjj as movePrice,d.gebjj as basePrice
,a.Num,a.Num as baseNum,a.Num as jynum
,a.Num * d.gebjj as Amount,a.Num * d.gebjj as costAmt
,g.RetailP,a.Num * g.RetailP as RetailAmt
,h.PackId,h.unit,h.Meas
,f.GoodsCode,f.GoodsName,f.GoodsSpec,f.Manufacturer,f.Place
,g.ApprovalNo,g.ManagePack,g.Formula,g.GCategory
,@inwhid as inwhid,@outwhid as outwhid,@whorgid as whorgid
,o.ORGID as ownerid,o.ORGNAME as ownername,g.zlnum,ISNULL(x1.innum,0) as innum,ISNULL(x2.outnum,0) as outnum
,a.*
from #anglect a
join STOREROOM b on a.outLocatId = b.LocatId and a.EntId = b.EntId
join STOREROOM j on a.inLocatId = j.LocatId and a.EntId = j.EntId
join BATCHCODE d on a.AngleId = d.AngleId 
and a.GoodsId = d.GoodsId and a.EntId = d.EntId
join GOODSDOC f on a.GoodsId = f.GoodsId and a.EntId = f.EntId
join GOODSATTR g on f.GoodsId = g.GoodsId and f.EntId = g.EntId
left join PGPRICE h on a.GoodsId = h.GoodsId and a.EntId = h.EntId
and h.IsBase = 'Y'
join ORGDOC o on a.OwnerId = o.ORGID and a.EntId = o.ENTID
left join (select a.goodsid,d.batchcode,sum(a.placenum) as innum from AngleBalance a
join STOREROOM b on a.LocatId=b.LocatId
join STOREHOUSE c on b.WHId=c.WHId 
left join BATCHCODE d on a.angleid=d.angleid
where c.WHId=@inwhid
group by a.goodsid,d.batchcode
) x1 on x1.GoodsId=d.GoodsId and x1.batchcode =d.batchcode
left join (select a.goodsid,d.batchcode,sum(a.placenum) as outnum from AngleBalance a
join STOREROOM b on a.LocatId=b.LocatId
join STOREHOUSE c on b.WHId=c.WHId 
left join BATCHCODE d on a.angleid=d.angleid
where c.WHId=@outwhid
group by a.goodsid,d.batchcode
) x2 on x2.GoodsId=d.GoodsId and x2.batchcode =d.batchcode
order by f.GoodsCode
 ok_lab:
 --删除临时表
 drop table #mdbalance
 drop table #anglect
 --结束事务处理
 commit_lab:
 if @tran_point = 0
  commit tran tran_batch_ft
 goto return_lab
 
 err_lab:
 if @return < 10000 
 set @return = cast(cast(@errcode as varchar(6)) + 
              right(cast(10000 + @return as varchar(5)),4) as int)
 
 rollback tran tran_batch_ft
 
 return_lab:
 return @return