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

发表于:2007-07-02来源:作者:点击数: 标签:
CREATE proc spSF_OutWarehouseCheck( @vOutWarehouseNo varchar(255), --出库单号 @vAuditor varchar(255), --审核人名称 @UseDbTRAN bit=0 --启动 数据库 事务(默认不启动为0) ) AS begin Set noCount on --兼容ADO 原生 COM对象 declare @dtAuditDate Dat

 

 

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

 

 

 

原文转自:http://www.ltesting.net