U872-结算成本处理步骤及索引处理
来源:互联网 发布:java程序员做什么 编辑:程序博客网 时间:2024/05/18 00:50
U872每月都需要做月结,对于制造企业来说,结算成本处理是必不可少的一个处理环节,每次查询出来待暂估记录也比较多(我接触到的有3万左右),暂估时间一般要2-3小时左右,若调用的大表索引碎片多时,会需要更长的时间,先看一下处理过程调用的主要步骤及脚本有哪些:
第一步:取存货模块的最新会计期间
第二步:取入库单明细账记录
第三步:取存货总账表记录
第四步:取采购结算单主从档记录
第五步:取存货明细表记录
第六步:新增存货明细账记录
第七步:取存货档案的核算自由项
第八步:写总账
注:数量是负数
第九步:重复执行第六步的脚本
第十步:写总账
结算成本处理涉及到的大表有 出入库明细表rdrecords,总账明细表ia_subsidiary,采购结算单明细表PurBillVouchs
若要保证暂估的速度,需要对优化处理:
第一步:取存货模块的最新会计期间
SELECT MAX(iPeriod) AS CurMonth FROM GL_mEnd WHERE bFlag_IA=1
Select top 1 rdrecords.autoid from rdrecords inner join ia_subsidiary
on rdrecords.autoid=ia_subsidiary.id Where ia_subsidiary.CvouType = N'01'
and isnull(rdrecords.iquantity,0)=isnull(rdrecords.isquantity,0)
and rdrecords.autoid=5403479 And ia_subsidiary.imonth=6
Select * from Ia_Summary
where cinvcode= N'021299000098' And IsNull(iDirect, 0) = 0
and IsNull(Ia_Summary.iPeriod, 0) <> 0 And Ia_Summary.iMonth = 6
select PSVID from pursettlevouch where PSVID=32128
Select * From PurBillVouchs Where ID=1709941
Select Autoid From ia_subsidiary
Where ID=5403457 And iMonth=6 And (cVouType= N'01' Or (cVouType= N'30' and cSrcVouType=N'01'))
Select iMonth,cPZID ,* from ia_subsidiary
where (cVouType= N'01' or (cVouType= N'33' and cSrcVouType=N'01'))
and (bflag=1 or bflag=2) and id=5403457
Select top 1 * from ia_subsidiary
where (CVOUTYPE='01' OR ((CVOUTYPE='33' or CVOUTYPE='30')
and csrcvoutype='01' )) and id=5403457 And bFlag=1 order by autoid desc
Select * from ia_subsidiary where CVOUTYPE= N'24' and id=5403457 and imonth=6 and cSRcvoutype='01'
Select Top 1 * From Ia_Subsidiary Where AutoID=1091064
insert into IA_Subsidiary (bRdFlag, cBusType, cBusCode, cVouCode, ID, ValueID, JustID, dVouDate,
dKeepDate, iMonth, iPZID, cInvHead, cDifHead, cVouType, cPTCode, cSTCode,cWhCode,
cInvCode,cAccDep, cRdCode, cCusCode,cBillCode, cDLCode, cPSPCode, cProCode, cDepCode,
cPersonCode,iAInQuantity,iAOutQuantity, iInCost, iOutCost,iAInPrice, iAOutPrice,
iDebitDifCost, iCreditDifCost,cBatchCode, cMaker,cAccounter, bFlag, bMoneyFlag,
bSale, cMemo,cDefine1, cDefine2, cDefine3,cDefine4, cDefine5, cDefine6,cDefine7,
cDefine8, cDefine9,cDefine10, cDefine11,cDefine12,cDefine13,cDefine14,cDefine15,
cDefine16,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,cFree8,cFree9,cFree10,
cdefine22,cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,cdefine28,cdefine29,
cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35,cdefine36,cdefine37,
citem_class,citemcode,citemcname,cVenCode,cHandler,cOrderCode,cARVCode,cName,
cBatchia,dMadeDateia,iMassDateia,cMassUnit,dVDateia,cproordercode,iproorderid,
iproorderids,cworkprocode,cworkprocodedis,cworkcentercode,cworkcentername,cendcode,
csaleordercode,isaleorderid,isaleordersid,isaleorderids,centrustordercode,ientrustorderid,
ientrustordersid,ipurordersid,idlsid,cAssUnit,inum,strContractCode,cpurordercode,exoCode,
iExRowno,consignMentCode,iconsignmentautoid,imaterialfee,iprocessfee,cSRcVouType,
cDemandCode,cDemandMemo,cIMOrdercode)
values (1,N'普通采购',null,N'TL20140519396',5403457,null,null,'2014-05-19','2014-06-25',
6,1603742,null,null,N'24',N'01',null,N'07',N'021299000098',N'6901',N'101',null,null,null,
null,null,null,N'07409',-20,null,1.0769,null,-21.54,null,null,null,null,N'lml',N'lh',
N'1',0,0,null,N'0911',null,null,null,null,null,0,null,null,null,null,N'PO00001903',
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,N'4',
N'CP140404-040',null,0,0,null,null,N'CPWG1404-075',N'PO00001903',null,null,null,null,
null,null,null,null,null,N'001587',N'yql',N'TLWG-140400006151',N'TL201405160263',
null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,
null,null,null,1916612,null,null,null,null,N'TLWG-140400006151',null,null,null,null,null,
null,N'01',null,null,null)
Select bCheckFree1,bCheckFree2,bCheckFree3,bCheckFree4,bCheckFree5,bCheckFree6,bCheckFree7,
bCheckFree8,bCheckFree9,bCheckFree10 FROM inventory where cInvcode= N'021299000098'
Exec IA_WriSummary 按仓库核算,2,6, N'07', N'021299000098', N'', N'', N'', N'', N'',
N'', N'', N'', N'', N'',-20,-21.54,0,0,0,0
第九步:重复执行第六步的脚本
第十步:写总账
<span style="font-size:12px;">Exec IA_WriSummary 按仓库核算,2,6, N'07', N'021299000098', N'', N'', N'', N'', N'', N'', N'', N'', N'', N'',20,21.54,0,0,0,0</span>注:数量为正数
结算成本处理涉及到的大表有 出入库明细表rdrecords,总账明细表ia_subsidiary,采购结算单明细表PurBillVouchs
若要保证暂估的速度,需要对优化处理:
第一步:暂时禁用SQL代理中的一些计划任务,如备份、同步等
第二步:点【暂估】按钮前,一定要对rdrecords,Ia_Summary ,ia_subsidiary,PurBillVouchs,Inventory,Ia_Summary 重建或整理索引,索引碎片可以用dbo.fn_ShowIndexSP函数,重建索引可以用Dyl_ReindexNew过程
第三步:检查这些表的索引的碎片是否已全部在10以下,若是表示全部整理成功!
可以做结算成本处理的暂估操作了。
/*功能:显示指定表的索引碎片创建人:baronyang创建时间:2014-07-02select * from dbo.fn_ShowIndexSP('')*/Alter function dbo.fn_ShowIndexSP(@tablename varchar(255))returns @table table (tablename varchar(255),indexname varchar(255),spbl int)asBEGINDECLARE @dbid int,@objid intselect @dbid=DB_ID(),@objid=OBJECT_ID(@tablename)insert into @table (tablename,indexname,spbl)SELECT c.name,b.name,avg_fragmentation_in_percent FROM sys.dm_db_index_physical_stats(@dbid,@objid,NULL,NULL,NULL) as a inner JOIN sys.indexes b on a.object_id=b.object_id and a.index_id=b.index_id inner JOIN sys.objects c on a.object_id=c.object_id where b.index_id>0 and avg_fragmentation_in_percent>=1 return end
/*功能:重建整理创建人:baronyang创建时间:2014-07-02exec Dyl_ReindexNew 'KQ_OtherData'select * from dbo.fn_showindexsp('KQ_OtherData')*/Alter procedure dbo.Dyl_ReindexNew@TableName varchar(255),@indexname varchar(255)='' as set nocount on declare @dbid int,@objid int,@sql varchar(1000) select @dbid=DB_ID(),@objid=isnull(OBJECT_ID(@TableName),0) if @objid=0 BEGINprint @TableName+'表不存在' return End IF Exists(SELECT * FROM sys.dm_db_index_physical_stats(@dbid,@objid,NULL,NULL,NULL) where avg_fragmentation_in_percent>30 and index_id>0 ) and @objid>0 BEGIN set @sql='alter index '+case when isnull(@indexname,'')<>'' THEN @indexname else 'all' END+' on '+@TableName+' rebuild WITH(online=on,STATISTICS_NORECOMPUTE=ON)' exec (@SQL) End IF Exists(SELECT * FROM sys.dm_db_index_physical_stats(@dbid,@objid,NULL,NULL,NULL) where avg_fragmentation_in_percent>30 and index_id>0 and @objid>0 ) print @TableName+'表索引碎片还是超过30,请手动重建索引'
0 0
- U872-结算成本处理步骤及索引处理
- T6 结算成本处理失败
- U852存货核算 结算成本处理失败
- 购物车的实现及结算处理
- 购物车的实现及结算处理
- 购物车的实现及结算处理
- 结算单尾差处理
- 购物车的实现及结算处理[转]
- 购物车的C#实现及结算处理
- ASP.NET购物车的实现及结算处理源代码
- ASP.NET购物车的实现及结算处理
- c++异常处理成本
- PM工单结算规则处理方法
- 【】mysql结算批量处理的优化
- SSH配置详细步骤及异常处理
- phpMyAdmin安装步骤及问题处理
- expdp 导出步骤及错误处理
- 遥感图像处理步骤及经验
- Android 命名规范 (提高代码可以读性)
- android屏幕适配问题
- easyui tree打开新节点清除缓存问题
- find命令
- 【畅言】不把C作为第一门语言是个好主意么?-----转自 http://www.csdn.net/article/2014-07-01/2820467
- U872-结算成本处理步骤及索引处理
- 云计算里的安全:警惕云服务被恶意利用
- MongoDB数据库插入、更新和删除操作详解
- 实用简约的JAVA注释标签-高手必备
- CppCMS渲染content中内容到页面上
- 无需App Store,无线发布iOS应用(OTA)
- 内存泄露错误
- Mac 下 Chrome多个Tab之间切换
- 第一篇:知识库,Apache Jena,DL,Description Logic,Ontology。