用友t3升级报错列名cmemo无效和对象名dbostcalbeginvage无效Word下载.docx
- 文档编号:19598533
- 上传时间:2023-01-08
- 格式:DOCX
- 页数:13
- 大小:239.74KB
用友t3升级报错列名cmemo无效和对象名dbostcalbeginvage无效Word下载.docx
《用友t3升级报错列名cmemo无效和对象名dbostcalbeginvage无效Word下载.docx》由会员分享,可在线阅读,更多相关《用友t3升级报错列名cmemo无效和对象名dbostcalbeginvage无效Word下载.docx(13页珍藏版)》请在冰豆网上搜索。
StoredProcedure[dbo].[ST_CalBegInvAge]脚本日期:
08/15/201413:
12:
13******/
SETANSI_NULLSON
SETQUOTED_IDENTIFIERON
--计算上年结存库龄
CREATEPROCEDURE[dbo].[ST_CalBegInvAge]
@cDBNameVarchar(20),--上一个账套名称
@CurrentDateDatetime,--计算日期
@cCurDBNamevarchar(20)--当前账套名称
As
Declare@strLeftQtynVarchar(4000)
Declare@StockStartDatedatetime
declare@cWhCodenvarchar(50)
declare@cInvCodenvarchar(50)
declare@cBatchnvarchar(50)
declare@cFree1nvarchar(120)
declare@cFree2nvarchar(120)
declare@iQttyfloat
declare@iNumfloat
declare@autoIdint
declare@iVouchQttyfloat
declare@iVouchNumfloat
declare@iTempQttyfloat
declare@iTempNumfloat
declare@strSQLInvAgenVarchar(4000)
declare@strInvAgenVarchar(4000)
declare@strSQLInnVarchar(4000)
declare@strSQLOutnVarchar(4000)
declare@strNewRecordnVarchar(4000)
declare@strSqlnVarchar(200)
declare@sSQLnVarchar(200)
declare@sBegdateAsnVarchar(200)
declare@iCouint
--取库存启用日期
Set@sBegdate=N'
select@StockStartDate=cast(cValueasDatetime)from'
+
@cDBName+'
..accinformation'
+'
WherecSysid='
'
ST'
andcName='
dSTStartDate'
'
execsp_executesql@sBegdate,N'
@StockStartDatedatetimeoutput'
@StockStartDate
output
If@StockStartDateisNull
Begin
Return
End
--计算本期当前结存
Set@strNewRecord=N'
InsertInto'
+@cDBName+'
..STStockAgeTempTable(autoid,iqtty,
inum)
values(@autoid,@iTempQtty,@iTempNum)'
set@strLeftQty=
SELECTcWhCode,
cInvCode,
IsNull(cBatch,'
)AscBatch,
IsNull(cFree1,'
)AscFree1,
IsNull(cFree2,'
)AscFree2,
SUM(casewhenbRdFlag=1theniQuantityelse-iQuantityend)asiQtty,
SUM(casewhenbRdFlag=1theniNumelse-iNumend)ASiNum
From(SelectbRdFlag,cWhCode,cInvCode,iQuantity,INum,cBatch,cFree1,cFree2
From'
+@cDBName+'
..ST_BegInvAge
UnionALL
SelectR.bRdFlag,R.cWhCode,Rs.cInvCode,Rs.iQuantity,Rs.INum,Rs.cBatch,Rs.cFree1,
Rs.cFree2
+@cDBName+'
..RdRecordRinnerjoin'
+@cDBName+'
..RdRecordsRsonR.id=Rs.id
WhereR.dDate>
=@StartDateAndR.dDate<
=@CurrentDateAndcVouchType<
>
33'
AndcVouchType<
34'
)X
groupbycWhCode,cInvCode,IsNull(cBatch,'
),IsNull(cFree1,'
),IsNull(cFree2,'
)'
--取入库语句
Set@strSQLIn='
SelectAutoId,iQtty,iNum
From(SelectdDate,AutoId,abs(iQuantity)AsiQtty,abs(iNum)AsiNum
WherecWhCode=@cWhCodeandcInvCode=@cInvCodeandisnull(cBatch,'
)=@cBatch
andisnull(cFree1,'
)=@cFree1andisnull(cFree2,'
)=@cFree2
Selectd.dDate,ds.autoid,Abs(ds.iQuantity)asiQtty,Abs(ds.iNum)asiNum
from'
..RdRecorddjoin'
+@cDBName+'
..RdRecordsdson(d.id=ds.id)
where((d.bRdFlag=1andds.iQuantity>
0)or(d.bRdFlag<
1andds.iQuantity<
0))
Andd.dDate>
=@StartDateandd.dDate<
=@CurrentDateandd.cWhCode=@cWhCode
andds.cInvCode=@cInvCodeandIsNull(ds.cBatch,'
)=@cBatchandIsNull(ds.cFree1,'
)=@cFree1
andIsNull(ds.cFree2,'
)=@cFree2)X
orderbydDateDesc,AutoIDDescforreadonly'
Set@strSQLIn=N'
declarecurInvIninsensitivecursorfor'
+@strSQLIn
set@strLeftQty=N'
declarecurRdcursorfor'
+@strLeftQty
executesp_executesql@strLeftQty,
N'
@CurrentDateDatetime,@StartDateDatetime'
@CurrentDate,@StockStartDate
opencurRd
fetchnextfromcurRdinto
@cWhCode,@cInvCode,@cBatch,
@cFree1,@cFree2,@iQtty,@iNum
--建立计算结果临时表
Set@sSQL=N'
select@Num=count
(1)from'
..sysobjects'
Where
name='
STStockAgeTempTable'
execsp_executesql@sSQL,N'
@Numintoutput'
@iCououtput
If@iCou<
0
Exec('
DropTable'
+@cDBName+'
..STStockAgeTempTable'
)
CreateTable'
..STStockAgeTempTable(autoIdint,iQttyfloat,
iNumfloat)'
--sp_helpST_BegInvAge
while@@fetch_status=0
begin
if(@iQtty<
0)set@iQtty=0
if(@iNum<
0or@iNumisnull)set@iNum=0
Set@iTempQtty=0.0
Set@iTempNum=0.0
--按降序取入库
executesp_executesql@strSQLIn,
@CurrentDateDatetime,@StartDateDatetime,@cWhCodevarchar(50),
@cInvCodevarchar(20),@cBatchvarchar(50),
@cFree1varchar(120),@cFree2varchar(120)'
@CurrentDate,@StockStartDate,@cWhCode,@cInvCode,@cBatch,@cFree1,
@cFree2
opencurInvIn
fetchnextfromcurInvIninto@autoId,@iVouchQtty,@iVouchNum
while(round(@iQtty,6)>
0orround(@iNum,6)>
0)and@@fetch_status=0
ifRound(@iQtty,6)>
Round(@iVouchQtty,6)
set@iTempQtty=@iVouchQtty
set@iQtty=Round(@iQtty,6)-Round(@iVouchQtty,6)
end
elsebegin
set@iTempQtty=@iQtty
set@iQtty=0.0
ifRound(@iNum,6)>
Round(@iVouchNum,6)
set@iTempNum=@iVouchNum
set@iNum=Round(@iNum,6)-Round(@iVouchNum,6)
set@iTempNum=@iNum
set@iNum=0.0
--增加计算结果
executesp_executesql@strNewRecord,
@autoidint,@iTempQttyfloat,@iTempNumfloat'
@autoid,@iTempQtty,@iTempNum
fetchnextfromcurInvIninto@autoid,@iVouchQtty,@iVouchNum
closecurInvIn
deallocatecurInvIn
@cWhCode,@cInvCode,@cBatch,@cFree1,@cFree2,@iQtty,@iNum
closecurRd
deallocatecurRd
--将结果数据输出
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],
(casewhenx.bRdFlag=0then-1*RT.[iNum]elseRT.[iNum]end)AsiNum,
(casewhenx.bRdFlag=0then-1*RT.[iQtty]elseRT.[iQtty]end)AsiQuantity,
[iUnitCost],[iPrice],[cBatch],[cFree1],
[cFree2],[dVDate],[cDefine22],[cDefine23],[cDefine24],
[cDefine25],[cDefine26],[cDefine27],[cItem_class],[cItemCode],
[cName],[cItemCName]
Into'
+@cCurDBName+'
..STStockAgeTempTableRTInnerJoin(
[cDefine8],[cDefine9],[cDefine10],[AutoID],[cInvCode],
[iNum],[iQuantity],[iUnitCost],[iPrice],[cBatch],[cFree1],
UnionAll
SelectR.[ID],[bRdFlag],[cVouchType],[cWhCode],[dDate],
..RdRecordsRsinnerjoin'
..RdRecordROn(R.ID=Rs.ID)
WheredDate>
=@StartDateAnddDate<
)XOnRT.autoId=X.autoId
executesp_executesql@strSQLInvAge,
@CurrentDate,
@StockStartDate
然后再回到系统管理/系统/升级SQLSERVER数据
选择005账套的2014\2015年度,升级
提示成功
B,如果出现以下情况:
列名cMemo无效
请执行这个文件:
更新数据库MatchVouchs.sql
--账套116年度为2016升级时,软件报错列名cMemo无效
--请执行以下语句
USEUFDATA_116_2016
altertableMatchVouchsaddcMemo[varchar](255)NULL
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 用友 t3 升级 报错列名 cmemo 无效 对象 dbostcalbeginvage
![提示](https://static.bdocx.com/images/bang_tan.gif)
链接地址:https://www.bdocx.com/doc/19598533.html