USE AA
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Last]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmp_Last]
CREATE TABLE [tmp_Last] (
[FEntryID] [int] NOT NULL ,
[FBillNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FNumber] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FQty] [decimal](18, 4) NOT NULL ,
[FBatchNo] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FSPID] [int] NOT NULL
) ON [PRIMARY]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Source]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmp_Source]
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tmp_Batch]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tmp_Batch]
Select nOutBillSeq As FEntryID , cOutBillNo As FBillNo , FNumber , nQua As FQty , FStockPlaceID As FSPID Into tmp_Source From Czq092801
Select FNumber ,FBatchNo , FQty , FStockID , FStockPlaceID As FSPID Into tmp_Batch From Czq092802
ALTER TABLE tmp_Batch ADD FDetailID int IDENTITY (1, 1) NOT NULL
go
Update tmp_Batch
Set FSPID = 464
where FNumber = 'E.A.AZ9719410054'
Update tmp_Batch
Set FSPID = 464
where FNumber = 'E.A.880410054'
Update tmp_Batch
Set FSPID = 465
Where fnumber = 'E.A.AZ9719410054'
Update tmp_Source
Set FSPID = t.FSPID
From tmp_Batch t
Where tmp_Source.FNumber = t.FNumber
--以下是把批号拆分的游标
declare @EntryID int --定义变量行号
declare @BillNo varchar(100) --定义变量单据号
declare @Number varchar(100) --定义变量物料代码
declare @qty decimal(18,6) --定义变量数量
declare @SPID int --定义变量仓位代码
declare @BatchNo varchar(100) --定义变量批号
declare @DID int
declare @BQty decimal(18,6)
Declare subgroup Cursor For
Select FEntryID, FBillNo , FNumber , FQty , FSPID From tmp_Source
Open subgroup
Fetch Next From subgroup Into @EntryID , @BillNo , @Number , @qty , @SPID
While @@FETCH_STATUS = 0
Begin
re:
set @DID = 0
set @DID = (Select min(FDetailID) From tmp_Batch Where FNumber = @Number And FSPID = @SPID And FQty <> 0 )
set @BatchNo =(Select FBatchNo From tmp_Batch Where FDetailID = @DID )
Set @BQty =(Select FQty From tmp_Batch Where FDetailID = @DID )
print @BillNo
print @EntryID
if @BQty >= @Qty
Begin
Insert Into tmp_Last values (@EntryID , @BillNo , @Number , @Qty , @BatchNo , @SPID)
Update tmp_Batch Set FQty = FQty - @Qty Where FDetailID = @DID
print -1
Select -1
print @qty
end
Else
Begin
Insert Into tmp_Last values (@EntryID , @BillNo , @Number , @BQty , @BatchNo , @SPID)
Update tmp_Batch Set FQty = FQty - @BQty Where FDetailID = @DID
set @Qty = @Qty - @BQty
print 1
print @qty
goto re --返回到re：
End
print '888888'
Fetch Next From subgroup Into @EntryID , @BillNo , @Number , @qty , @SPID
End
Close subgroup
Deallocate subgroup --释放游标
-----------------------------------------------------------------
--更新行号
ALTER TABLE tmp_Last ADD FDetailID int IDENTITY (1, 1) NOT NULL
ALTER TABLE tmp_Last ADD FNewEntryID int
go
declare @DID int
declare @BillNo varchar(100)
declare @i int
Declare subgroup Cursor For
Select Distinct FBillNo From tmp_Last
Open subgroup
Fetch Next From subgroup Into @BillNo
While @@FETCH_STATUS = 0
Begin
set @i =1
Declare detail Cursor For
Select FDetailID From tmp_Last Where FBillNo = @BillNo
Open detail
Fetch Next From detail Into @DID --可以认为FDetailID的值附给了@DID
While @@FETCH_STATUS = 0
Begin
update tmp_Last Set FNewEntryID = @i Where FDetailID = @DID
set @i = @i + 1
Fetch Next From detail Into @DID
End
Close detail
Deallocate detail
Fetch Next From subgroup Into @BillNo
End
Close subgroup
Deallocate subgroup
Select * From tmp_Last
-------------------------------------------------------------------
/*
Select FNumber,Sum(FQty) FQty
From tmp_Last
Group by FNumber
Select FBillNo,Sum(FQty) FQty
From tmp_Last
Group by FBillNo
Select FNumber,Sum(nQua) nQua
From Czq092801
Group by FNumber
Select cOutBillNo,Sum(nQua) nQua
From Czq092801
Group by cOutBillNo
*/
