您現(xiàn)在的位置：首頁>>服務(wù)支持>>自助知識庫>>其他產(chǎn)品
升級報“計算上年結(jié)存庫齡”錯誤修改語句
升級報“計算上年結(jié)存庫齡”錯誤修改語句
 

 
 
 
1，ALTER TABLE MatchVouch ADD bReqAnalyse bit
 
 
 
 
2，/****** 對象:  StoredProcedure [dbo].[ST_CalBegInvAge]    腳本日期: 10/24/2016 17:34:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
--計算上年結(jié)存庫齡
CREATE PROCEDURE [dbo].[ST_CalBegInvAge]
@cDBName Varchar(20),     --上一個賬套名稱
@CurrentDate Datetime,     --計算日期
@cCurDBName  varchar(20)    --當(dāng)前賬套名稱
As
 
Declare @strLeftQty nVarchar(4000)
 
Declare @StockStartDate datetime
 
declare @cWhCode nvarchar(50)
declare @cInvCode nvarchar(50)
declare @cBatch nvarchar(50)
declare @cFree1 nvarchar(120)
declare @cFree2 nvarchar(120)
declare @iQtty float
declare @iNum float
 
declare @autoId int
declare @iVouchQtty float
declare @iVouchNum float
declare @iTempQtty float
declare @iTempNum float
 
declare @strSQLInvAge nVarchar(4000)
declare @strInvAge nVarchar(4000)
declare @strSQLIn  nVarchar(4000)
declare @strSQLOut nVarchar(4000)
declare @strNewRecord nVarchar(4000)
 
declare @strSql nVarchar(200)
declare @sSQL  nVarchar(200)
declare @sBegdate As nVarchar(200)
 
declare @iCou int
 
--取庫存啟用日期
Set @sBegdate = N'select @StockStartDate = cast(cValue as Datetime)  from ' + @cDBName + '..accinformation ' + ' Where cSysid=''ST'' and cName= ''dSTStartDate'' '
 
exec sp_executesql @sBegdate,N'@StockStartDate datetime output', @StockStartDate output
 
If @StockStartDate is Null
Begin
Return
End
 
--計算本期當(dāng)前結(jié)存
 
Set @strNewRecord = N'Insert Into ' +@cDBName+'..STStockAgeTempTable(autoid, iqtty, inum)
values(@autoid, @iTempQtty, @iTempNum) '
set @strLeftQty =
' SELECT cWhCode,
cInvCode,
IsNull(cBatch,'''') As cBatch,
IsNull(cFree1,'''') As cFree1,
IsNull(cFree2,'''') As cFree2,
SUM(case when bRdFlag = 1 then iQuantity else -iQuantity end) as iQtty,
SUM(case when bRdFlag = 1 then iNum else - iNum end) AS iNum
  From(  Select bRdFlag, cWhCode, cInvCode, iQuantity, INum, cBatch, cFree1, cFree2
 From '+ @cDBName+'..ST_BegInvAge
 Union ALL
 Select R.bRdFlag, R.cWhCode, Rs.cInvCode, Rs.iQuantity, Rs.INum, Rs.cBatch, Rs.cFree1, Rs.cFree2
 From ' + @cDBName+ '..RdRecord R inner join '+@cDBName+'..RdRecords Rs on R.id = Rs.id
 Where R.dDate >= @StartDate And R.dDate <= @CurrentDate And cVouchType <> ''33'' And cVouchType <> ''34''
 ) X
 group by cWhCode, cInvCode, IsNull(cBatch,''''),IsNull(cFree1,''''), IsNull(cFree2,'''') '
 
--取入庫語句
Set @strSQLIn = ' Select AutoId, iQtty, iNum
  From (Select dDate, AutoId, abs(iQuantity) As iQtty, abs(iNum) As iNum
From '+@cDBName+'..ST_BegInvAge
Where cWhCode = @cWhCode and cInvCode = @cInvCode and isnull(cBatch,'''') = @cBatch
and isnull(cFree1,'''') = @cFree1 and isnull(cFree2,'''') = @cFree2
Union ALL
Select d.dDate,ds.autoid, Abs(ds.iQuantity) as iQtty, Abs(ds.iNum) as iNum
from '+@cDBName+'..RdRecord d join ' + @cDBName + '..RdRecords ds on (d.id = ds.id)
where ( (d.bRdFlag = 1 and ds.iQuantity > 0) or (d.bRdFlag <> 1 and ds.iQuantity < 0))
And d.dDate >= @StartDate and d.dDate <= @CurrentDate and d.cWhCode = @cWhCode
and ds.cInvCode = @cInvCode and IsNull(ds.cBatch,'''') = @cBatch and IsNull(ds.cFree1,'''') = @cFree1
and IsNull(ds.cFree2,'''') = @cFree2) X
order by dDate Desc,AutoID Desc for read only '
 
Set @strSQLIn = N' declare curInvIn insensitive cursor for ' + @strSQLIn
 
set @strLeftQty = N'declare curRd cursor for ' + @strLeftQty
execute sp_executesql @strLeftQty,
N'@CurrentDate Datetime, @StartDate Datetime',
@CurrentDate, @StockStartDate
 
open curRd
 
 
fetch next from curRd into  
@cWhCode,@cInvCode,@cBatch,
@cFree1,@cFree2,@iQtty, @iNum
 
--建立計算結(jié)果臨時表
Set @sSQL = N'select @Num = count(1) from ' + @cDBName + '..sysobjects ' + ' Where name = ''STStockAgeTempTable'' '
 
exec sp_executesql @sSQL,N'@Num int output', @iCou output
 
If @iCou <> 0
Begin
Exec (' Drop Table ' + @cDBName+'..STStockAgeTempTable')
End
 
Exec (' Create Table '+@cDBName+'..STStockAgeTempTable (autoId int , iQtty float,  iNum float)')
 
--sp_help ST_BegInvAge
 
while @@fetch_status = 0
begin
if(@iQtty < 0 ) set @iQtty = 0

if(@iNum < 0 or @iNum is null) set @iNum = 0

Set @iTempQtty = 0.0
Set @iTempNum = 0.0
 
--按降序取入庫
execute sp_executesql @strSQLIn,
N'@CurrentDate Datetime, @StartDate Datetime, @cWhCode varchar(50),
@cInvCode varchar(20), @cBatch varchar(50),
@cFree1 varchar(120), @cFree2 varchar(120)',
@CurrentDate, @StockStartDate, @cWhCode, @cInvCode, @cBatch, @cFree1, @cFree2
 
open curInvIn
fetch next from curInvIn into @autoId, @iVouchQtty, @iVouchNum
 
while (round(@iQtty,6) > 0 or round(@iNum,6) > 0) and @@fetch_status =0
begin
if Round(@iQtty, 6) > Round(@iVouchQtty, 6)
begin
set @iTempQtty = @iVouchQtty
set @iQtty = Round(@iQtty, 6) - Round(@iVouchQtty, 6)
end
else begin
set @iTempQtty = @iQtty
set @iQtty = 0.0
end


if Round(@iNum, 6) > Round(@iVouchNum,6)
begin
set @iTempNum = @iVouchNum
set @iNum = Round(@iNum, 6) - Round(@iVouchNum, 6)
end
else begin
set @iTempNum = @iNum
set @iNum = 0.0
end

--增加計算結(jié)果
execute sp_executesql @strNewRecord,
N'@autoid int, @iTempQtty float, @iTempNum float',
@autoid, @iTempQtty, @iTempNum

Set @iTempQtty = 0.0
Set @iTempNum = 0.0
 
fetch next from curInvIn into @autoid, @iVouchQtty, @iVouchNum
 
end
close curInvIn
deallocate curInvIn
 
fetch next from curRd into  
@cWhCode,@cInvCode,@cBatch,@cFree1,@cFree2,@iQtty, @iNum
end
 
close curRd
deallocate curRd
 
--將結(jié)果數(shù)據(jù)輸出
 
set @strSQLInvAge =N'
Select  [ID], [bRdFlag],[cVouchType],[cWhCode] ,[dDate] ,
[cCode],[cRdCode],[cDepCode] ,[cPersonCode] ,[cVenCode] ,
[cHandler] ,[cMemo] ,[cMaker] ,[cDefine1] ,[cDefine2] ,
[cDefine3]  ,[cDefine4] ,[cDefine5] ,[cDefine6] ,[cDefine7]  ,
[cDefine8] ,[cDefine9] ,[cDefine10] , RT.[AutoID] ,[cInvCode],
(case when x.bRdFlag = 0 then -1 * RT.[iNum] else RT.[iNum] end)  As iNum,
(case when x.bRdFlag = 0 then -1 * RT.[iQtty] else RT.[iQtty] end) As iQuantity ,
[iUnitCost] ,[iPrice]  ,[cBatch],[cFree1] ,
[cFree2] ,[dVDate] ,[cDefine22] ,[cDefine23] ,[cDefine24] ,
[cDefine25] ,[cDefine26] ,[cDefine27] ,[cItem_class]  , [cItemCode]  ,
[cName] ,[cItemCName]
Into '+ @cCurDBName+'..ST_BegInvAge
 
From '+@cDBName+'..STStockAgeTempTable RT Inner Join (
Select  [ID], [bRdFlag],[cVouchType],[cWhCode] ,[dDate] ,
[cCode],[cRdCode],[cDepCode] ,[cPersonCode] ,[cVenCode] ,
[cHandler] ,[cMemo] ,[cMaker] ,[cDefine1] ,[cDefine2] ,
[cDefine3]  ,[cDefine4] ,[cDefine5] ,[cDefine6] ,[cDefine7]  ,
[cDefine8] ,[cDefine9] ,[cDefine10] ,[AutoID] ,[cInvCode],
[iNum] ,[iQuantity] ,[iUnitCost] ,[iPrice]  ,[cBatch],[cFree1] ,
[cFree2] ,[dVDate] ,[cDefine22] ,[cDefine23] ,[cDefine24] ,
[cDefine25] ,[cDefine26] ,[cDefine27] ,[cItem_class]  , [cItemCode]  ,
[cName] ,[cItemCName]
From '+@cDBName+'..ST_BegInvAge
Union All
Select  R.[ID], [bRdFlag],[cVouchType],[cWhCode] ,[dDate] ,
[cCode],[cRdCode],[cDepCode] ,[cPersonCode] ,[cVenCode] ,
[cHandler] ,[cMemo] ,[cMaker] ,[cDefine1] ,[cDefine2] ,
[cDefine3]  ,[cDefine4] ,[cDefine5] ,[cDefine6] ,[cDefine7]  ,
[cDefine8] ,[cDefine9] ,[cDefine10] ,[AutoID] ,[cInvCode],
[iNum] ,[iQuantity] ,[iUnitCost] ,[iPrice]  ,[cBatch],[cFree1] ,
[cFree2] ,[dVDate] ,[cDefine22] ,[cDefine23] ,[cDefine24] ,
[cDefine25] ,[cDefine26] ,[cDefine27] ,[cItem_class]  , [cItemCode]  ,
[cName] ,[cItemCName]
From '+@cDBName+'..RdRecords Rs inner join '+@cDBName+'..RdRecord R On (R.ID = Rs.ID)
Where dDate >= @StartDate And dDate <= @CurrentDate And cVouchType <> ''33'' And cVouchType <> ''34''
) X On RT.autoId = X.autoId     
'
execute sp_executesql @strSQLInvAge,
N'@CurrentDate Datetime, @StartDate Datetime',
@CurrentDate,
@StockStartDate
立即開啟您的數(shù)字化轉(zhuǎn)型體驗(yàn)之旅

免費(fèi)體驗(yàn)

在線體驗(yàn)

X



*姓名：
*手機(jī)號：
公司：
職務(wù)：
郵箱：
需求描述：
?
400-780-9880