U872-结算成本处理步骤及索引处理

       U872每月都需要做月结，对于制造企业来说，结算成本处理是必不可少的一个处理环节，每次查询出来待暂估记录也比较多（我接触到的有3万左右），暂估时间一般要2-3小时左右，若调用的大表索引碎片多时，会需要更长的时间，先看一下处理过程调用的主要步骤及脚本有哪些：
第一步：取存货模块的最新会计期间

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-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，请手动重建索引'