U872-结算成本处理步骤及索引处理
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代理中的一些计划任务。如备份、同步等
第二步：点【暂估】button前。一定要对rdrecords，Ia_Summary ，ia_subsidiary，PurBillVouchs，Inventory，Ia_Summary 重建或整理索引，索引碎片能够用dbo.fn_ShowIndexSP函数，重建索引能够用Dyl_ReindexNew过程
第三步：检查这些表的索引的碎片是否已所有在10下面。若是表示所有整理成功！
能够做结算成本处理的暂估操作了。
/*
功能：显示指定表的索引碎片
创建人：baronyang
创建时间：2014-07-02
select * from dbo.fn_ShowIndexSP('')
*/
Alter function dbo.fn_ShowIndexSP
(
@tablename varchar(255)
)
returns @table table (tablename varchar(255),indexname varchar(255),spbl int)
as
BEGIN
DECLARE @dbid int,@objid int
select @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-02
exec 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
BEGIN
print @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。请手动重建索引'
