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 [email protected]
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)[email protected])<0
begin
-- 负库存了，跳出
set @iserror = 1
set @errorinfo = '出库数大于现存量,不允许负出库'
end
end
-- 检查方向，来至红蓝字
if @direction='+'
update currentstock set nsurplusnumber=nsurplusnumber + @nsurplusnumber where [email protected]
else
update currentstock set nsurplusnumber=nsurplusnumber - @nsurplusnumber where [email protected]
--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
