CREATE proc spSF_OutWarehouseCheck(
http://www.gaodaima.com/35204.html一段出库单冲减现存量的存储过程源码_sqlserver
@vOutWarehouseNo varchar(255), –出库单号
@vAuditor varchar(255), –审核人名称
@UseDbTRAN bit=0 –启动数据库事务(默认不启动为0)
) AS
begin
Set noCount on –兼容ADO 原生 COM对象
declare @dtAuditDate DateTime –审核日期
declare @OldvSingleID varchar(255) –源表ID
declare @vSingleID int –单表流水号
declare @vOrganizationCode varchar(255) –分支机构代码
declare @vWarehouseCode varchar(255) –仓库编码
declare @vInvCode varchar(255) –商品编码
declare @vColorCode varchar(255) –花色编码
declare @nSurplusNumber varchar(255) –结存数量
declare @bInsert varchar(255) –写入新数据
declare @Direction varchar(255) –方向
declare @isError bit –是否有错误
declare @ErrorInfo varchar(1024) –错误信息
declare @CanNegative bit –允许负出库
— 外部参数
— declare @UseDbTRAN bit –使用数据库事务
— declare @vOutWarehouseNo varchar(255) –出库单号
— declare @vAuditor varchar(255) –审核人
set @CanNegative = 1 –0不允许，1允许
set @isError = 0 –默认无错误
set @ErrorInfo = ” –错误信息
set @dtAuditDate = GetDate() –审核日期
— 调试开关
— set @vOutWarehouseNo = ‘XSCK0012004000000001’
— set @vAuditor = ‘S.F.’
— set @UseDbTRAN = 0
if not Exists(Select * from OutWareHouse where (vOutWarehouseNo = @vOutWarehouseNo) and (isNull(vAuditor,”) = ”))
begin
Set @isError = 1
Set @ErrorInfo = ‘单据不存在或者已审核！’
end
if @isError=0
begin
— 获取现存量表流水号
— 1. 获取现存量编号
— 2. 写入临时记录到现存量表
— 3. 删除刚刚写入的临时记录
— 4. 编号递增
— 开始事务
if @UseDbTRAN=1 BEGIN TRANSACTION
declare cur cursor for
select
c.vSingleID as 现存量编号,
b.vOrganizationCode as 分支机构代码,
b.vWarehouseCode as 仓库编码,
a.vInvCode as 商品编码,
a.vColorCode as 花色编码,
a.nOutNumber as 出库数量,
IsNull(Convert(varchar(255),c.nSurplusNumber),’现存量无’) as 结存数量,
(Case when b.bRBFlag=1 then ‘+’ else ‘-‘ end) as 方向
from OutWarehouses as a left join OutWarehouse as b on a.vOutWarehouseNo=b.vOutWarehouseNo
left join CurrentStock as c on (b.vOrganizationCode=c.vOrganizationCode) and (b.vWarehouseCode=c.vWarehouseCode) and (a.vInvCode=c.vInvCode) and (a.vColorCode=c.vColorCode)
where (b.vOutWarehouseNo = @vOutWarehouseNo) And (isNull(b.vAuditor,”) = ”)
Open Cur Fetch Next From Cur
Into @OldvSingleID,
@vOrganizationCode,
@vWarehouseCode,
@vInvCode,
@vColorCode,
@nSurplusNumber,
@bInsert,
@Direction
— 插入临时记录，锁定现存量表
Select @vSingleID=Convert(decimal(38),isNull(Max(Convert(decimal(38),
Case when vSingleID>0 and Convert(varChar(38),Convert(decimal(38),vSingleID))=Convert(varChar(38),vSingleID) then vSingleID end)),0)+1)
from CurrentStock where ISNUMERIC(vSingleID)=1 and CharIndex(‘.’,vSingleID)<=0 and CharIndex(‘e’,LOWER(vSingleID))<=0
Insert Into CurrentStock
(vSingleID,vOrganizationCode,vWarehouseCode,vInvCode,vColorCode,nSurplusNumber)
values(@vSingleID,@vOrganizationCode,@vWarehouseCode,@vInvCode,@vColorCode,@nSurplusNumber)
Delete From CurrentStock where vSingleID=@vSingleID
while (@@FETCH_STATUS = 0) And (@isError=0)
begin
— 检查现存量表是否存在
if @bInsert=’现存量无’
begin
if @CanNegative = 1 –允许负出库
begin
— 保存新ID到变量，作为更改现存量的查询条件
Set @OldvSingleID = @vSingleID
— 1.写入新记录到现存量表
Insert Into CurrentStock(
vSingleID,vOrganizationCode,vWarehouseCode,vInvCode,vColorCode,nSurplusNumber)
values(
@vSingleID,
@vOrganizationCode,
@vWarehouseCode,
@vInvCode,
@vColorCode,
0
)
end
else
begin — 不允许负出库
— 1.跳出处理
— 2.回滚
— 3.报告负出库的信息
set @isError = 1
set @ErrorInfo = ‘商品未入库,不允许负出库’
end
end
else — 有现存量，检查是否会产生负库存
if @bInsert<>”
begin
— 检查是否为数值
if ISNUMERIC(@bInsert)=0
begin
— 不为数值
— 跳出
set @isError = 1
set @ErrorInfo = ‘现存量异常：不为数值’
end
— 如果不允许负库存(@CanNegative=0)并且是减现存量则检查是否会产生负库存
if (@Direction=’-‘) and (@CanNegative=0)
if (Convert(float,@bInsert)-@nSurplusNumber)<0
begin
— 负库存了，跳出
set @isError = 1
set @ErrorInfo = ‘出库数大于现存量,不允许负出库’
end
end
— 检查方向，来至红蓝字
if @Direction=’+’
Update CurrentStock Set nSurplusNumber=nSurplusNumber + @nSurplusNumber Where vSingleID=@OldvSingleID
else
Update CurrentStock Set nSurplusNumber=nSurplusNumber – @nSurplusNumber Where vSingleID=@OldvSingleID
–Print @OldvSingleID
if @isError=0
Fetch Next From Cur
Into @OldvSingleID,
@vOrganizationCode,
@vWarehouseCode,
@vInvCode,
@vColorCode,
@nSurplusNumber,
@bInsert,
@Direction
Set @vSingleID = @vSingleID + 1
End
CLOSE Cur
DEALLOCATE Cur
if @isError=0 –没有错误
begin
Update
OutWarehouse
Set vAuditor = @vAuditor,
dtAuditDate = @dtAuditDate
Where vOutWarehouseNo = @vOutWarehouseNo
set @ErrorInfo = ‘审核成功’
–提交事务
if @UseDbTRAN=1 COMMIT
end –产生了错误，无法审核
else
begin
–回滚事务
if @UseDbTRAN=1 ROLLBACK
end
end — 查找单据是否存在
–显示执行信息
Select @vOrganizationCode as 机构编码,
@vWarehouseCode as 仓库编码,
@vInvCode as 商品编码,
@vColorCode as 花色编码,
@nSurplusNumber as 出库数量,
@bInsert as 现存量,
@Direction as 方向,
@isError as 冲减失败,
@ErrorInfo as 错误信息
end
GO
欢迎大家阅读《一段出库单冲减现存量的存储过程源码_sqlserver》,跪求各位点评,若觉得好的话请收藏本文，by 搞代码
转载请注明原文链接：一段出库单冲减现存量的存储过程源码_sqlserver
