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

最新推荐文章于 2022-09-28 15:48:34 发布

告别VVA

最新推荐文章于 2022-09-28 15:48:34 发布

阅读量893

点赞数

分类专栏： sql server

本文链接：https://blog.csdn.net/li1101585183/article/details/43705517

版权

该存储过程主要用于实现批号分摊功能，涉及从指定仓库到另一仓库的商品预调拨。它首先计算销售日均情况，接着处理库存，通过循环分摊批号来完成铺货检索。涉及到的表包括ANGLEBALANCE、GOODSOCCU等，处理过程中考虑了日期范围和库存数量的匹配。

摘要由CSDN通过智能技术生成

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