IDC资讯中心
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
