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 <span lang="EN" style="FONT-SIZE: 9pt; BACK