存储过程
CREATE proc spSF_OutWarehouseCheck(
@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