create proc spsf_outwarehousecheck(
@voutwarehouseno varchar(255), --出庫單號(hào)
@vauditor varchar(255), --審核人名稱
@usedbtran bit=0 --啟動(dòng)數(shù)據(jù)庫事務(wù)(默認(rèn)不啟動(dòng)為0)
) as
begin
set nocount on --兼容ado 原生 com對(duì)象
declare @dtauditdate datetime --審核日期
declare @oldvsingleid varchar(255) --源表id
declare @vsingleid int --單表流水號(hào)
declare @vorganizationcode varchar(255) --分支機(jī)構(gòu)代碼
declare @vwarehousecode varchar(255) --倉庫編碼
declare @vinvcode varchar(255) --商品編碼
declare @vcolorcode varchar(255) --花色編碼
declare @nsurplusnumber varchar(255) --結(jié)存數(shù)量
declare @binsert varchar(255) --寫入新數(shù)據(jù)
declare @direction varchar(255) --方向
declare @iserror bit --是否有錯(cuò)誤
declare @errorinfo varchar(1024) --錯(cuò)誤信息
declare @cannegative bit --允許負(fù)出庫
-- 外部參數(shù)
-- declare @usedbtran bit --使用數(shù)據(jù)庫事務(wù)
-- declare @voutwarehouseno varchar(255) --出庫單號(hào)
-- declare @vauditor varchar(255) --審核人
set @cannegative = 1 --0不允許，1允許
set @iserror = 0 --默認(rèn)無錯(cuò)誤
set @errorinfo = '' --錯(cuò)誤信息
set @dtauditdate = getdate() --審核日期
-- 調(diào)試開關(guān)
-- 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 = '單據(jù)不存在或者已審核！'
end
if @iserror=0
begin
-- 獲取現(xiàn)存量表流水號(hào)
-- 1. 獲取現(xiàn)存量編號(hào)
-- 2. 寫入臨時(shí)記錄到現(xiàn)存量表
-- 3. 刪除剛剛寫入的臨時(shí)記錄
-- 4. 編號(hào)遞增
-- 開始事務(wù)
if @usedbtran=1 begin transaction
declare cur cursor for
select
c.vsingleid as 現(xiàn)存量編號(hào),
b.vorganizationcode as 分支機(jī)構(gòu)代碼,
b.vwarehousecode as 倉庫編碼,
a.vinvcode as 商品編碼,
a.vcolorcode as 花色編碼,
a.noutnumber as 出庫數(shù)量,
isnull(convert(varchar(255),c.nsurplusnumber),'現(xiàn)存量無') as 結(jié)存數(shù)量,
(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
-- 插入臨時(shí)記錄，鎖定現(xiàn)存量表
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
-- 檢查現(xiàn)存量表是否存在
if @binsert='現(xiàn)存量無'
begin
if @cannegative = 1 --允許負(fù)出庫
begin
-- 保存新id到變量，作為更改現(xiàn)存量的查詢條件
set @oldvsingleid = @vsingleid
-- 1.寫入新記錄到現(xiàn)存量表
insert into currentstock(
vsingleid,vorganizationcode,vwarehousecode,vinvcode,vcolorcode,nsurplusnumber)
values(
@vsingleid,
@vorganizationcode,
@vwarehousecode,
@vinvcode,
@vcolorcode,
0
)
end
else
begin -- 不允許負(fù)出庫
-- 1.跳出處理
-- 2.回滾
-- 3.報(bào)告負(fù)出庫的信息
set @iserror = 1
set @errorinfo = '商品未入庫,不允許負(fù)出庫'
end
end
else -- 有現(xiàn)存量，檢查是否會(huì)產(chǎn)生負(fù)庫存
if @binsert<>''
begin
-- 檢查是否為數(shù)值
if isnumeric(@binsert)=0
begin
-- 不為數(shù)值
-- 跳出
set @iserror = 1
set @errorinfo = '現(xiàn)存量異常：不為數(shù)值'
end
-- 如果不允許負(fù)庫存(@cannegative=0)并且是減現(xiàn)存量則檢查是否會(huì)產(chǎn)生負(fù)庫存
if (@direction='-') and (@cannegative=0)
if (convert(float,@binsert)[email protected])<0
begin
-- 負(fù)庫存了，跳出
set @iserror = 1
set @errorinfo = '出庫數(shù)大于現(xiàn)存量,不允許負(fù)出庫'
end
end
-- 檢查方向，來至紅藍(lán)字
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 --沒有錯(cuò)誤
begin
update
outwarehouse
set vauditor = @vauditor,
dtauditdate = @dtauditdate
where voutwarehouseno = @voutwarehouseno
set @errorinfo = '審核成功'
--提交事務(wù)
if @usedbtran=1 commit
end --產(chǎn)生了錯(cuò)誤，無法審核
else
begin
--回滾事務(wù)
if @usedbtran=1 rollback
end
end -- 查找單據(jù)是否存在
--顯示執(zhí)行信息
select @vorganizationcode as 機(jī)構(gòu)編碼,
@vwarehousecode as 倉庫編碼,
@vinvcode as 商品編碼,
@vcolorcode as 花色編碼,
@nsurplusnumber as 出庫數(shù)量,
@binsert as 現(xiàn)存量,
@direction as 方向,
@iserror as 沖減失敗,
@errorinfo as 錯(cuò)誤信息
end
go
