T3年结常见脚本

iufida

T3 年结常见脚本请注意：执行脚本前务必备份好可用的账套数据！！！ 一、建立年度账报错：1、新建立年度帐提示”.“附近语法错误

                               
登录/注册后可看大图

通用脚本：对上年的数据库执行alter table code DROP COLUMN timestamalter table Vendor DROP COLUMN timestamalter table Person DROP COLUMN timestamalter table Department DROP COLUMN timestamalter table Gl_accvouch DROP COLUMN timestamalter table Customer DROP COLUMN timestam
2、建立年度账，提示：字符串”之前有未闭合的引号

                               
登录/注册后可看大图

通用脚本：对上一年数据库执行update customer set uniqueid = newid()
3、建立年度长报错，提示：列名 cf_name 无效

                               
登录/注册后可看大图

通用脚本：alter table GL_bfreq drop column cf_namealter table GL_blreq drop column cf_name
4、建立年度账提示：列名：ilnvNTaxCost 无效

                               
登录/注册后可看大图

通用脚本：在旧年度库中执行以下 sql 语句：select iInvNTaxCost from inventory --- 查 询 表 inventory中是否有iInvNTaxCost 字段如果有该字段，继续执行：alter table inventory drop column iInvNTaxCost如果没有该字段，继续执行：alter table inventory add iInvNTaxCost float NULL
5、新建年度提示列名 bAssistant1 无效

                               
登录/注册后可看大图

常用脚本：A、找到 code 表对应的约束去删除

                               
登录/注册后可看大图

B、或执行语句：（将所有数据库名和约束名替换成要删除的）USE [数据库名]GOIF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[约束名]') AND type= 'D')BEGINALTER TABLE [dbo].[code] DROP CONSTRAINT [约束名]ENDGOC、对上年执行：alter table code drop column bAssistant1alter table code drop column bAssistant2alter table code drop column bAssistant3alter table code drop column bAssistant4alter table code drop column bAssistant5alter table code drop column bAssistant6alter table code drop column bAssistant7
6、其他的情况建立年度账报错，需要降级再升级。（二进制截断、列名无效）通用脚本：对上年执行update accinformation set cvalue='8.216',cdefault='8.216' wherecname='versionflag'drop tablerpt_flddef,rpt_fltdef,rpt_glbdef,rpt_grpdef,rpt_reldef,rpt_folder,rpt_itmdefselect * into rpt_flddef from UFDATA_999_2011..rpt_flddefselect * into rpt_fltdef from UFDATA_999_2011..rpt_fltdefselect * into rpt_glbdef from UFDATA_999_2011..rpt_glbdefselect * into rpt_grpdef from UFDATA_999_2011..rpt_grpdef4select * into rpt_reldef from UFDATA_999_2011..rpt_reldefselect * into rpt_folder from UFDATA_999_2011..rpt_folderselect * into rpt_itmdef from UFDATA_999_2011..rpt_itmdef
二、结转报错1、结转供应链时 提示 存货系统结转失败。常用脚本：对上年执行update IA_Subsidiary set ioutCost=d.iUnitPrice,iAoutPrice=d.iSum from (select c.autoid,b.iUnitPrice,b.iSum from SaleBillVouch a inner join SaleBillVouchs b on a.SBVID=b.SBVIDinner join IA_Subsidiary c on a.SBVID=c.iPZID and b.AutoID=c.ID where iAOutPrice>1000000000) d where IA_Subsidiary.AutoID=d.autoid and IA_Subsidiary.iAOutPrice>1000000000update RdRecords set iUnitCost=0,iPrice=0 where AutoID in( select id from IA_Subsidiary where iAInPrice>1000000000 )update IA_Subsidiary set iInCost=0,iAInPrice=0 where iAInPrice>1000000000
2、结转固定资产报错：列名或所提供值的数目与表定义不匹配

                               
登录/注册后可看大图

通用脚本：对上下两个年度执行update accinformation set cvalue='8.216' where csysid='AA' and cid='99'drop tablerpt_flddef,rpt_fltdef,rpt_glbdef,rpt_grpdef,rpt_reldef,rpt_folder,rpt_itmdefselect * into rpt_flddef from UFDATA_999_2011..rpt_flddefselect * into rpt_fltdef from UFDATA_999_2011..rpt_fltdefselect * into rpt_glbdef from UFDATA_999_2011..rpt_glbdef5select * into rpt_grpdef from UFDATA_999_2011..rpt_grpdefselect * into rpt_reldef from UFDATA_999_2011..rpt_reldefselect * into rpt_folder from UFDATA_999_2011..rpt_folderselect * into rpt_itmdef from UFDATA_999_2011..rpt_itmdefalter table RPT_FldDEF drop column CurGroupCol,CurSubTotalCol
3、年度结转应手应付结转报错错误为 0。通用脚本对上年执行delete From ap_detail where ccovouchid not in (select cvouchid from ap_vouch where cvouchtype='R0') and cCoVouchType = 'R0'delete from Ap_Detail where cFlag = 'AR' and cCoVouchType = 'R0' and ccoVouchID ='01'delete From Ap_Detail where cFlag = 'AR' and cCoVouchType = 'R0' and (ccoVouchID>='01'and ccoVouchID
三、反年结1、固定资产反年结（工具无法选择 20 年以后的年度）通用脚本：update ufsystem..ua_account_sub set bClosing=0 where cacc_id=' 账 套 号 ' andcsub_id='FA' and iyear = 反年结的年度其中 账套号改成实际的账套号、反年结年度改成对应需要反年结的年度
2、上度账启用了某模块，但年度结转后想反启用通用脚本:模块反启用需要在数据为中执行，执行语句前修改 cacc_id 为账套编号，UFdata_001_2022为用户需要反启用的账套库，执行前请备份所有账套数据，以免执行到其他账套中：——库存Update ufdata_001_2022..accinformation set cvalue='cdefault' where csysid='st'and ctype='ddate'Delete ufsystem..ua_account_sub where cacc_id='001' and csub_id='st'and iyear='2022'Delete ufsystem..ua_account_sub where cacc_id='001' and csub_id='st'and iyear='9999'~~~~~~~~~~~~~~~~~~~~~~~其他模块反启用语句~~~~~~~~~~~~~~~~~~~~~~~~~ ——应收Update ufdata_001_2022..accinformation set cvalue='cdefault' where csysid='ar'and ctype='ddate'Delete ufsystem..ua_account_sub where cacc_id='001' and csub_id='ar' and iyear='2022'Delete ufsystem..ua_account_sub where cacc_id='001' and csub_id='ar' and iyear='9999'6——应付update ufdata_001_2022..accinformation set cvalue='cdefault'wherecsysid='ap'and ctype='ddate'delete ufsystem..ua_account_sub where cacc_id='001' and csub_id='ap' and iyear='2022'delete ufsystem..ua_account_sub where cacc_id='001' and csub_id='ap' and iyear='9999' ——采购update ufdata_001_2022..accinformation setcvalue='cdefault'wherecsysid='pu'and ctype='ddate'delete ufsystem..ua_account_sub where cacc_id='001'andcsub_id='pu'andiyear='2022'delete ufsystem..ua_account_sub where cacc_id='001'andcsub_id='pu'andiyear='9999' ——销售update ufdata_001_2022..accinformation set cvalue='cdefault'wherecsysid='sa'and ctype='ddate'delete ufsystem..ua_account_sub where cacc_id='001'and csub_id='sa'and iyear='2022'delete ufsystem..ua_account_sub where cacc_id='001'and csub_id='sa'and iyear='9999' ——核算update ufdata_001_2022..accinformation set cvalue='cdefault'where csysid='ia'and ctype='ddate'delete ufsystem..ua_account_sub where cacc_id='001'and csub_id='ia'and iyear='2022'delete ufsystem..ua_account_sub where cacc_id='001'and csub_id='ia'and iyear='9999' ——固定资产update ufdata_001_2022..accinformation set cvalue='cdefault'where csysid='fa'and ctype='ddate'delete ufsystem..ua_account_sub where cacc_id='001'and csub_id='fa'and iyear='2022'delete ufsystem..ua_account_sub where cacc_id='001'and csub_id='fa'and iyear='9999'
3、核算模块取消期初年结过来的数据通用脚本：对新年执行（记得备份）delete from IA_Subsidiary where iMonth = 0delete from IA_Summary where iMonth = 0
4、工资模块上年需要反年结通用脚本：对上年执行Update wa_account set ilastmonth=11 where cgzgradenum='工资类别编号'Update gl_mend set bflag_wa=0 where iperiod=12其中工资类别编号改成对应的编号对系统库执行update UFSystem..ua_account_sub set bClosing='0' where cacc_id='账套号' and iYear=' 年度' 其中【账套号】改成实际账套号，【年度】那里改成反年结的那个年度
四、其他1、清除异常互斥通用脚本：对账套对应年度执行delete from UFSystem..UA_taskdelete from UFSystem..UA_TaskLogdelete from UFSystem..UA_Logdelete from GL_mccontroldelete from GL_mvcontroldelete from GL_mvocontroldelete from LockVouchdelete from fa_Control
2、年结日志提示正确 0，错误 0通用脚本：对上年执行update gl_accvouch set csettle=null where csettle=' '
3、年结后进入固定资产模块提示，请在启用日期后登陆账套。通用脚本：update AccInformation set cValue =' 年 度 -01-01' where cSysID ='FA' And cName ='dStartDate' 其中【年度】改成实际的年度
4、查询年结前后，上个年度存在，而下个年度不存在的客户编码通用脚本：对上个年度执行select ccuscode as 客户编码 from customer Where ccuscode not in (select ccuscodefrom ufdata_账套号_下个年度..customer) group by ccuscode其中【账套号】、【下个年度】按照实际去修改
5、年结结转后，进入账套销售模块提示：在对应所需名称或序数的集合中，未找到项目--cShipAddress。

                               
登录/注册后可看大图

通用脚本：alter table rdrecord add csccode varchar(2),cshipaddress varchar(200)
6、年结后，特殊行业性质无法结转损益（工具 20 年以后无法使用）通用脚本：UPDATE GL_CodeClass SET bcheck=1 WHERE cclass IN ('收入','支出','收入费用') ANDitrade IN (SELECT itrade_id FROM GL_BTrade WHERE ctrade_name='行业性质')
7、系统管理升级 SQL 提示：视图 MatchVouchM、KcMatchlist 无效通用脚本：create view MatchVouchM as select * from MatchVouchGocreate view KcMatchlist as select * from MatchVouchGo
8、系统管理里升级 SQL 提示：不能将 Null 值插入 Order 列

                               
登录/注册后可看大图

通用脚本：alter table ufsystem..ua_userflow alter column iorder tinyint null