USE [ChiefWMS]
GO
/****** Object: StoredProcedure [dbo].[WMS_Check] Script Date: 04/05/2016 09:51:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*
author:CJ
Date:2016-03-09
原存储过程名称:dbo.WMS_Check
货品组装单的反审核功能
*/
ALTER procedure [dbo].[WMS_Check]
(
--@ItemNo nvarchar(200)='ZD1233-350,ZD2993B-314',
--@OutQty nvarchar(200)='10,10',
----加仓库
--@StockNo nvarchar(200)='172仓库,172仓库'
@ItemNo nvarchar(200)=',',
@OutQty nvarchar(200)=',',
--加仓库
@StockNo nvarchar(200)=','
)
AS
begin
--declare @ItemNo nvarchar(200)='ZD1233-350,ZD2993B-314'
--declare @OutQty nvarchar(200)='200,200'
declare @Temp_Table table
(
ID int identity(1,1),
ItemNo nvarchar(20),
OutQty int,
StockNo nvarchar(20)
)
if(@ItemNo<>',' and @OutQty<>',' and @StockNo<>',')
begin
--select * into #Temp_ItemNo FROm dbo.FN_SplitSTR('810647486,810647485,810647484,810647483',',')
--select * into #Temp_OutQty FROm dbo.FN_SplitSTR('123,423,562,147',',')
select * into #ItemNo_Table FROm dbo.FN_SplitSTR(@ItemNo,',')
select * into #OutQty_Table FROm dbo.FN_SplitSTR(@OutQty,',')
select * into #StockNo_Table From dbo.FN_SplitSTR(@StockNo,',')
insert @Temp_Table
select A.Col,B.Col,C.Col from #ItemNo_Table A
join #OutQty_Table B on A.ID=B.ID
join #StockNo_Table C on A.ID=C.ID
--select * from @Temp_Table
end
else
begin
select '产品数据不全！'
return
end
----判断某一个出库数大于总共的库存数 就返回前端告知某物料不能出库 出库数不足
declare @SumStockQty int
declare @Check_ItemNo nvarchar(20)
declare @Check_OutQty int
declare @Check_StockNo nvarchar(20)
declare Check_Cur cursor local
For
select ItemNo,OutQty,StockNo from @Temp_Table
open Check_Cur
Fetch next from Check_Cur into @Check_ItemNo,@Check_OutQty,@Check_StockNo
while(@@FETCH_STATUS=0)
begin
if not exists(select * from dbo.WMS_StockDetail where ItemNo=@Check_ItemNo and StockNo=@Check_StockNo)
begin
select '零件编号:'+@Check_ItemNo+'在 编号:'+@Check_StockNo+'的仓库 没有库存 请更改其他仓库!'
return
end
select @SumStockQty=SUM(StockQty) from dbo.WMS_StockDetail where ItemNo=@Check_ItemNo and StockNo=@Check_StockNo
if(@Check_OutQty>@SumStockQty)--出库数大于总数
begin
select '零件编号:'+@Check_ItemNo+'出库数:'+cast(@Check_OutQty as nvarchar(20))+'大于仓库'+@Check_StockNo+'库存数'+cast(@SumStockQty as nvarchar(30)) as Result
return
end
--select 'dsfds'
Fetch next from Check_Cur into @Check_ItemNo,@Check_OutQty,@Check_StockNo
end
Close Check_Cur
Deallocate Check_Cur
----------------------------------------------------------------- 判断 End
declare @Temp_ItemNo nvarchar(20)
declare @Temp_OutQty int
declare @Temp_StockNo nvarchar(20)
declare @Result int =0
declare First_Cur cursor local
For
select ItemNo,OutQty,StockNo from @Temp_Table
open First_Cur
Fetch next from First_Cur into @Temp_ItemNo,@Temp_OutQty,@Temp_StockNo
while(@@FETCH_STATUS=0)
begin
select * into #TempWMSCheck from dbo.WMS_StockDetail where ItemNo=@Temp_ItemNo and StockNo=@Temp_StockNo order by CreateDate asc
--select * from #TempWMSCheck
declare @ID int
declare @StockQty int
declare MyCursor cursor local
For
select ID,StockQty from #TempWMSCheck
Open MyCursor
Fetch next From MyCursor Into @ID,@StockQty
While(@@Fetch_Status = 0)
Begin
--9000-8590=410
if(@StockQty>0)
begin
if(@Temp_OutQty-@StockQty>0)
begin
update dbo.WMS_StockDetail
set StockQty=0 where ID=@ID
set @Temp_OutQty=@Temp_OutQty-@StockQty
end
else
begin
update dbo.WMS_StockDetail
set StockQty=@StockQty-@Temp_OutQty
where ID=@ID
--select @Result as Result
--return (直接跳出整个循环 所以此处不能用return)
break--当满足的情况下中断这次循环 进行外面的下一次循环
end
end
else
begin
set @Result=1
end
Fetch next From MyCursor Into @ID,@StockQty
end
Close MyCursor
Deallocate MyCursor
drop table #TempWMSCheck
Fetch next from First_Cur into @Temp_ItemNo,@Temp_OutQty,@Temp_StockNo
end
Close First_Cur
Deallocate First_Cur
delete from @Temp_Table
drop table #ItemNo_Table
drop table #OutQty_Table
drop table #StockNo_Table
select @Result as Result
end