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

 
 
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
 
 

 

  • 0
    点赞
  • 0
    收藏
  • 打赏
    打赏
  • 2
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
评论 2

打赏作者

S.F.

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值