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