一段出库单冲减现存量的存储过程源码

王朝other·作者佚名  2006-01-30
窄屏简体版  字體: |||超大  

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

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
 
© 2005- 王朝網路 版權所有 導航