U872供应链结转卡在存货明细账处

yuhuaqiang

给客户结转一套U872财务+供应链。在结转供应链时卡在存货明细账处。等待10多个小时，报错，提示磁盘空间不够。经过跟踪发现出错位置。
CREATE TABLE [tmpIAJZ] ([ID] [int] not NULL,[cVouType] nvarchar(10),[cSrcVouType] nvarchar(10), [iaInQuantity] Decimal(34,3) Default 0,
[iInCost] Decimal(34,6) Default 0, [IAinPrice] Decimal(34,2) Default 0, [iPCost] Decimal(34,6) Default 0, [IAIPrice] Decimal(34,2) Default 0,
[iDebitDifCost] Decimal(34,2) Null, [iCreditDifCost] Decimal(34,2) Null, [bCalDif] [bit] Not Null,imaterialfee Decimal(34,2) Null, iProcessFee Decimal(34,2) Null)
go
--创建tmpIAJZ临时表
insert into tmpIAJZ(ID,cVouType,cSrcVouType,iaInQuantity,iInCost,IAIPrice,iPCost,iDebitDifCost,iCreditDifCost,bCalDif,imaterialfee,iProcessFee)
select Ia_Sub.Id,Ia_Sub.cVouType, Ia_Sub.cSrcVouType,
ia_settle.iainquantity as iainquantity,Ia_Sub.iInCost As iInCost,
ia_settle.IAinPrice As IAIPrice,
Ia_pCost.finvRcost As iPCost, Null As iDebitDifCost, Null As iCreditDifCost,
Case When wh.cWhValueStyle In (N'计划价法', N'售价法') Then 1 Else 0 End As bCalDif,ia_settle.imaterialfee,ia_settle.iProcessFee
FROM UFDATA_002_2012..Ia_Subsidiary Ia_Sub with (nolock)
INNER JOIN (select Id,isnull(csrcvoutype,cvoutype) as cvoutype,sum(round(iainquantity,3)) as iainquantity,
sum(IAinPrice + IsNull(iDebitDifCost, 0) - IsNull(iCreditDifCost, 0)) as iainprice,sum(imaterialfee) as imaterialfee,sum(iProcessFee) as iProcessFee from UFDATA_002_2012..ia_subsidiary with (nolock)
Where cvoutype In (N'01', N'70',N'24', N'30',N'33') And bFlag = 1 And cBusType <> N'受托代销'
group by id,isnull(csrcvoutype,cvoutype) having sum(round(iainquantity,3))<>0) ia_settle
on ia_sub.id=ia_settle.id and isnull(ia_sub.csrcvoutype, ia_sub.cvoutype)= ia_settle.cvoutype
Left Join UFDATA_002_2012..Warehouse wh with (nolock) ON Ia_Sub.cWhcode = wh.cWhcode
Left Join UFDATA_002_2012..invcheckfreeIa870 Ia_pCost with (nolock) On Ia_Sub.cInvCode = Ia_pCost.cInvCode And wh.cWhValueStyle In (N'计划价法', N'售价法')
And (Ia_Sub.cFree1 = Ia_pCost.cFree1 Or IsNull(Ia_pCost.cFree1, N'') = N'')
And (Ia_Sub.cFree2 = Ia_pCost.cFree2 Or IsNull(Ia_pCost.cFree2, N'') = N'')
And (Ia_Sub.cFree3 = Ia_pCost.cFree3 Or IsNull(Ia_pCost.cFree3, N'') = N'')
And (Ia_Sub.cFree4 = Ia_pCost.cFree4 Or IsNull(Ia_pCost.cFree4, N'') = N'')
And (Ia_Sub.cFree5 = Ia_pCost.cFree5 Or IsNull(Ia_pCost.cFree5, N'') = N'')
And (Ia_Sub.cFree6 = Ia_pCost.cFree6 Or IsNull(Ia_pCost.cFree6, N'') = N'')
And (Ia_Sub.cFree7 = Ia_pCost.cFree7 Or IsNull(Ia_pCost.cFree7, N'') = N'')
And (Ia_Sub.cFree8 = Ia_pCost.cFree8 Or IsNull(Ia_pCost.cFree8, N'') = N'')
And (Ia_Sub.cFree9 = Ia_pCost.cFree9 Or IsNull(Ia_pCost.cFree9, N'') = N'')
And (Ia_Sub.cFree10 = Ia_pCost.cFree10 Or IsNull(Ia_pCost.cFree10, N'') = N'')
Where Ia_Sub.cvoutype In (N'01', N'70',N'33') And bFlag = 1 And Ia_Sub.cBusType <> N'受托代销' And not wh.cWhValueStyle in (N'个别计价法',N'先进先出法')
go
--通过id号去分组汇总记录，应该是一个id号一条记录,系统的语句分组后一个id号有多条记录.()

insert into IA_Subsidiary (bRdFlag, cBusType, cBusCode, cVouCode, ID, ValueID, JustID, dVouDate, dKeepDate, iMonth, iPZID,
cInvHead, cDifHead, cVouType, cPTCode, cSTCode,cWhCode, cInvCode, cAccDep, cRdCode, cVenCode, cCusCode,cBillCode, cDLCode, cPSPCode,
cProCode, cDepCode, cPersonCode,iAInQuantity, iInCost, iAInPrice,iAOutQuantity, iOutCost, 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,cPZID,citem_class,citemcode,citemcname,cname,cBatchia,dMadeDateia,
iMassDateia,cMassUnit,dVDateia,cproordercode,iproorderid,iproorderids,cworkprocode,cworkprocodedis,cworkcentercode,cworkcentername,
cendcode,csaleordercode,isaleorderid,isaleordersid,isaleorderids,centrustordercode,ientrustorderid,ientrustordersid,cpurordercode,
ipurordersid,idlsid,strContractCode,inum,cAssUnit,exocode,iexrowno,consignmentcode,iconsignmentautoid,imaterialfee,iProcessFee,
cmaterialhead,cprocesshead,cSrcVoutype,cIMOrdercode,cDemandCode,cDemandMemo)
select 1, IA_Sub.cbustype,IA_Sub.cbuscode,IA_Sub.cvoucode,IA_Sub.ID,IA_Sub.ValueID,
IA_Sub.JustID,IA_Sub.dVouDate,'2013-01-01',0,IA_Sub.iPzID,IA_Sub.cinvhead,IA_Sub.cdifhead,
'33',IA_Sub.cPTCode,IA_Sub.cSTCode,IA_Sub.cWhCode,IA_Sub.cInvCode,IA_Sub.cAccDep,IA_Sub.cRdCode,
IA_Sub.cVenCode,IA_Sub.cCusCode,IA_Sub.cBillCode,IA_Sub.cDLCode, IA_Sub.cPSPCode,
IA_Sub.cProCode, IA_Sub.cDepCode,IA_Sub.cPersonCode,tmpIAJZ.iAInQuantity, tmpIAJZ.iInCost,tmpIAJZ.IAinPrice,
NULL, NULL, NULL,tmpIAJZ.iDebitDifCost,tmpIAJZ.iCreditDifCost,IA_Sub.cBatchCode, IA_Sub.cMaker,IA_Sub.cHandler,
1, 1, 0, IA_Sub.cMemo, cDefine1, cDefine2, cDefine3,cDefine4, cDefine5, cDefine6,cDefine7, cDefine8, cDefine9,cDefine10, cDefine11,
cDefine12, cDefine13,cDefine14, cDefine15, cDefine16,IA_Sub.cFree1,IA_Sub.cFree2,IA_Sub.cFree3,IA_Sub.cFree4,
IA_Sub.cFree5,IA_Sub.cFree6,IA_Sub.cFree7,IA_Sub.cFree8,IA_Sub.cFree9,IA_Sub.cFree10,cdefine22,
cdefine23,cdefine24,cdefine25,cdefine26,cdefine27,cdefine28,cdefine29,cdefine30,cdefine31,cdefine32,cdefine33,cdefine34,cdefine35,
cdefine36,cdefine37,cPZID,citem_class,citemcode,citemcname,cname,cBatchia,dMadeDateia,iMassDateia,IA_Sub.cMassUnit,dVDateia,
cproordercode,iproorderid,iproorderids,cworkprocode,cworkprocodedis,cworkcentercode,cworkcentername,cendcode,csaleordercode,isaleorderid,
isaleordersid,isaleorderids,centrustordercode,ientrustorderid,ientrustordersid,cpurordercode,ipurordersid,idlsid,strContractCode,inum,
cAssUnit , exocode, iexrowno, consignmentcode, iconsignmentautoid,
tmpIAJZ.imaterialfee, tmpIAJZ.iProcessFee,
cmaterialhead, cprocesshead, (Case When IA_Sub.cVouType = '33' Then IA_Sub.cSrcVouType Else IA_Sub.cVouType End) as cSrcVouType, cIMOrdercode,cDemandCode,cDemandMemo
From tmpIAJZ inner join UFDATA_002_2012..ia_subsidiary IA_Sub with (nolock) on tmpIAJZ.id=IA_Sub.id And IA_Sub.bFlag = 1
and (Case When IA_Sub.cVouType = N'33' Then IA_Sub.cSrcVouType Else IA_Sub.cVouType End)= IsNull(tmpIAJZ.cSrcVouType, tmpIAJZ.cVouType)
--将12年存货明细记录插入13年的数据库导致数据库崩溃(超过500万条记录)


发现上年IA_Subsidiary表中存在重复数据，导致结转失败


解决办法
在上年数据库中执行
delete  from  
  ia_subsidiary where autoid not in
(
select max(autoid) auotid from ia_subsidiary where cvoutype='33' group by
id)
and cvoutype='33'即可