无法删除新增存货档案问题案例分享用友软件股份有限公司王小朋Thursday,March28,2024案例分享一、错误现象二、分析/跟踪过程三、最终解决方案四、总结一、错误现象环境：U890+SP1补丁客户来电：新增存货档案后，发现存货编码有误，删除时报错，如下提示：系统忙，稍后再试！不可删除！删除存货档案问题，想必是平时热线中经常遇到的，因为涉及到存货使用的地方太多太多，可能大家又不知从何下手，接下来的分析，给大家抛砖引玉！问题现象：如下图所示一、错误现象按照正常的分析思路，如果在单据中使用，应该提示在RD表中或其他表使用，不允许删除。另一种情况，如果在存货总账中ia_summary有记录，即使是数量、金额为0，同样也无法删除。但指导客户查询存货总账，同样是没有记录的。再接着分析，就要看常用的几张存货表了，存货档案副表Inventory_Sub，bas_part表，看看是否有多余的记录。一、错误现象查询结果如下，都正常：一、错误现象二、分析/跟踪过程那么究竟还有什么地方使用了该存货档案呢？我们用事件探查器跟踪看看：迅速定位有exception红字语句：二、分析/跟踪过程看一下报错语句的执行结果：EXECUTEArchIsUsedProxy'Inventory','0107','tempU8501','cRetVal‘执行结果如下，继续发现报错信息：对象名'GL_mitemused'无效。二、分析/跟踪过程ArchIsUsedProxy存储过程，是查询基础档案是否引用的，再看执行该存储过程的报错信息：对象名‘GL_mitemused’无效。我们知道，报错“列名无效”时，是因为表中缺少字段，“对象名无效”时，说明缺少表，难道是该存储过程在判断哪些表中是否使用该存货档案时，找不到该表：GL_mitemused？？？那么，我们就来查询一下该表：select*fromdbo.GL_mitemused，同样是如上报错，缺少表。那么随之是不是也有解决方案了，将正常账套中的该表在问题账套中创建即可？二、分析/跟踪过程我们赶紧来对照正常账套，把正常账套的该表在有问题账套中创建：USE[UFDATA_001_2010]GO/******Object:Table[dbo].[GL_mitemused]ScriptDate:09/08/201008:57:26******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONGOCREATETABLE[dbo].[GL_mitemused]([cuser][nvarchar](20)NOTNULL,[citem_class][nvarchar](2)NOTNULL,[citem_id][nvarchar](20)NOTNULL,CONSTRAINT[aaaaaGL_mitemused_PK]PRIMARYKEYNONCLUSTERED([cuser]ASC,[citem_class]ASC,[citem_id]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GO三、最终解决方案再回到账套中删除存货档案，可以成功删除了。三、最终解决方案通过此小案例，将查询存货档案是否被引用的语句总结如下：----下面是具体的表查询语句将9999替换成需要查询的存货编码select*fromAccessarieswith(NoLock)whereCaccCode='99999'select*fromAccessarieswith(NoLock)whereCaccPCode='99999'select*fromAdjustPVouchswith(NoLock)wherecInvCode='99999'select*fromAp_Detailwith(NoLock)wherecInvCode='99999'select*fromAp_InvCodewith(NoLock)wherecKeyCode='99999'and(iTag=2)select*fromAssemVouchswith(NoLock)wherecInvCode='99999'select*fromATPResultMainwith(NoLock)wherecInvCode='99999'select*frombom_cboma,bas_partbwhereb.InvCode='99999'and(a.ParentId=b.PartId)select*frombom_cbomcompa,bas_partbwhereb.InvCode='99999'and(a.ComponentId=b.PartId)select*frombom_cbomcomponenta,bas_partbwhereb.InvCode='99999'and(a.ComponentId=b.PartId)select*frombom_cbomcompsuba,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frombom_cbomparenta,bas_partbwhereb.InvCode='99999'and(a.ParentId=b.PartId)select*frombom_opcomponenta,bas_partbwhereb.InvCode='99999'and(a.ComponentId=b.PartId)select*frombom_opcomponentsuba,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frombom_parenta,bas_partbwhereb.InvCode='99999'and(a.ParentId=b.PartId)select*fromCA_EnMMCwith(NoLock)wherecMatID='99999'select*fromCA_EnMOMwith(NoLock)wherecMatID='99999'select*fromCA_MaBSWwith(NoLock)wherecMatID='99999'四、总结及延伸select*fromCA_MatDfwith(NoLock)wherecMatID='99999'select*fromca_quowith(NoLock)wherecamoid='99999'select*fromca_quoallwith(NoLock)wherecamoid='99999'select*fromCAQ_COBASEwith(NoLock)whereinvcode='99999'select*fromCAQ_COPLANwith(NoLock)whereinvcode='99999'select*fromCAQ_COREALwith(NoLock)whereinvcode='99999'select*fromCheckVouchswith(NoLock)wherecInvCode='99999'select*fromCM_Balanceswith(NoLock)wherecItemID='99999'and(cItemSource=1)select*fromCM_Contract_Item_Awith(NoLock)wherestrInvoiceID='99999'select*fromCM_Contract_Item_Bwith(NoLock)wherestrInvoiceID='99999'select*fromCM_Contract_Item_Cwith(NoLock)wherestrInvoiceID='99999'select*fromCostJustVouchwith(NoLock)wherecInvCode='99999'select*fromCostJustVouchswith(NoLock)wherecInvCode='99999'select*fromCrm_CrossSalesInventory_swith(NoLock)wherecInvCode='99999'select*fromCrm_CusComplaintwith(NoLock)wherecComplaintDetail='99999'and(bcComplaintObjectID=75)select*fromCrm_Lead_swith(NoLock)wherecInvCode='99999'select*fromCrm_OppInventorywith(NoLock)wherecInvCode='99999'select*fromCrm_UpSalesInventory_swith(NoLock)wherecInvCode='99999'四、总结及延伸select*fromcrp_resloada,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*fromcrp_resourcelista,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*fromCusInvContraposewith(NoLock)wherecInvCode='99999'select*fromDispatchListswith(NoLock)wherecInvCode='99999'select*fromecn_boma,bas_partbwhereb.InvCode='99999'and(a.ParentId=b.PartId)select*fromecn_ecnapplydetaila,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*fromecn_opcomponenta,bas_partbwhereb.InvCode='99999'and(a.ComponentId=b.PartId)select*fromecn_opcomponentsuba,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*fromecn_proutinga,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*fromEQ_Inventorywith(NoLock)wherecInvCode='99999'select*fromEQ_ListInventorywith(NoLock)wherecInvCode='99999'select*fromEQ_PlanInventorywith(NoLock)wherecInvCode='99999'select*fromEQ_WorkInventorywith(NoLock)wherecInvCode='99999'select*fromex_consignmentdetailwherecinvcode='99999'select*fromex_consignmentdetail_historywherecinvcode='99999'select*fromex_creditdetailwherecinvcode='99999'select*fromex_creditdetail_historywherecinvcode='99999'select*fromex_faredetailwherecinvcode='99999'select*fromex_inventorypricedetailwherecinvcode='99999'select*fromex_invoicedetailwherecinvcode='99999'select*fromex_orderdetailwherecinvcode='99999'select*fromex_orderdetail_historywherecinvcode='99999'四、总结及延伸select*fromex_packingdetailwherecinvcode='99999'select*fromex_quotationdetailwherecinvcode='99999'select*fromex_stockupdetailwherecinvcode='99999'select*fromExpenseVouchswith(NoLock)wherecInvCode='99999'select*fromfa_ZWVoucherswith(NoLock)wherecitem_id='99999'and(citem_class='ch')select*fromFD_AccSetwith(NoLock)wherecitem_id='99999'and(citem_class='ch')select*fromFM_NBCKDwith(NoLock)wherecProduct='99999'and(iII=0)select*fromFM_NBJKDwith(NoLock)wherecProduct='99999'and(iII=0)select*fromFM_Planwith(NoLock)wherecProduct='99999'and(iII=0)select*fromFM_PlanAddDetailwith(NoLock)wherecProduct='99999'and(iII=0)select*fromFM_PlanDetailwith(NoLock)wherecProduct='99999'and(iII=0)select*fromGL_accasswith(NoLock)wherecitem_id='99999'and(citem_class='ch')select*fromGL_accvouchwith(NoLock)wherecitem_id='99999'and(citem_class='ch')select*fromGL_bautotranwith(NoLock)wherecitem_id='99999'and(citem_class='ch')select*fromGL_bfreqwith(NoLock)wherecitem_id='99999'and(citem_class='ch')select*fromgl_cashtablewith(NoLock)wherecitem_id='99999'and(citem_class='ch')四、总结及延伸select*fromGL_mitemusedwith(NoLock)wherecitem_id='99999'and(citem_class='ch')select*fromGL_QCUserDefwith(NoLock)wherecitem_id='99999'and(citem_class='ch')select*fromGSP_VouchDInformswith(NoLock)wherecInvCode='99999'select*fromGSP_VouchDRecordswith(NoLock)wherecInvCode='99999'select*fromGSP_VouchInformwith(NoLock)wherecInvCode='99999'select*fromGSP_VouchInspectwith(NoLock)wherecInvCode='99999'select*fromGSP_VouchPAuditwith(NoLock)wherecInvCode='99999'select*fromGSP_VouchQIFeedbackwith(NoLock)wherecInvCode='99999'select*fromGSP_VouchsNotewith(NoLock)wherecInvCode='99999'select*fromGSP_VouchsQCwith(NoLock)wherecInvCode='99999'select*fromGSP_VouchUQAuditwith(NoLock)wherecInvCode='99999'select*fromGSP_VouchZYYHFilewith(NoLock)wherecInvCode='99999'select*fromHY_MODETAILSwith(NoLock)whereCINVCODE='99999'select*fromHY_SCRAPREPORTSwith(NoLock)whereCINVCODE='99999'select*fromHY_USEDVOUCHSwith(NoLock)whereCINVCODE='99999'select*fromHYOPERATIONPRICEwith(NoLock)whereCINVCODE='99999'select*fromIA_DecReadyHeadwith(NoLock)wherecInvCode='99999'select*fromIA_DecReadyswith(NoLock)wherecInvCode='99999'select*fromIA_HeadSetwith(NoLock)wherecInvCode='99999'select*fromIA_InvTDatawith(NoLock)whereInvCode='99999'select*fromIA_MaxMinCostDifwith(NoLock)wherecInvCode='99999'四、总结及延伸select*fromIA_MoneyPlanwith(NoLock)wherecInvCode='99999'select*fromIA_OppHeadwith(NoLock)wherecInvCode='99999'select*fromIA_Subsidiarywith(NoLock)wherecInvCode='99999'select*fromim_creditdetailwherecinvcode='99999'select*fromim_invoicedetailwherecinvcode='99999'select*fromim_orderdetailwherecinvcode='99999'select*fromInventoryBarCodeSetwith(NoLock)wherecinvcode='99999'select*fromInvPosContraposewith(NoLock)wherecInvCode='99999'select*fromJustInVouchswith(NoLock)wherecInvCode='99999'select*fromMatchVouchwith(NoLock)wherecpspcode='99999'select*fromMatchVouchswith(NoLock)wherecInvCode='99999'select*fromMaterialAppVouchswith(NoLock)wherecinvcode='99999'select*frommom_moallocatea,bas_partbwhereb.InvCode='99999'and(a.ComponentId=b.PartId)select*frommom_moallocatehistorya,bas_partbwhereb.InvCode='99999'and(a.ComponentId=b.PartId)select*frommom_moallocatesuba,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommom_orderdetaila,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommom_orderdetailhistorya,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)四、总结及延伸select*frommom_partprolinea,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_atpdetaila,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_consumptiona,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_currentstocka,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_currentstockbaka,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_currentstockdtla,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_currentstockdtlbaka,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_errora,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_forecastdataa,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_forecastdetaila,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_netdemanda,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_netdemandbaka,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_pegginga,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_schedulea,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*frommps_schedulebaka,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*fromNE_ExpenVouchwith(NoLock)wherestrItemID='99999'and(strItemClassID='ch')select*fromNE_LoanVouchwith(NoLock)wherestrItemID='99999'and(strItemClassID='ch')select*fromOM_MatSettleVouchswith(NoLock)wherecInvCode='99999'select*fromOM_MODetailswith(NoLock)wherecInvCode='99999'select*fromOM_MOMaterialswith(NoLock)wherecInvCode='99999'select*fromPM_ActQuaInputSubainnerjoinPM_ItemElementbona.cElementCode=b.cElementCodewherea.cCodeMx='99999'and(b.iElementDetail=1)四、总结及延伸select*fromPM_BudgetSubainnerjoinPM_ItemElementbona.cElementCode=b.cElementCodewherea.cCodeMx='99999'and(b.iElementDetail=1)select*fromPM_ItemQCsubainnerjoinPM_ItemQCbona.iEleVid=b.iEleVidinnerjoinPM_ItemElementconb.cElementCode=c.cElementCodewherea.cCodeMx='99999'and(c.iElementDetail=1)select*fromPm_RecordInswith(NoLock)wherecInvCode='99999'select*fromPO_Podetailswith(NoLock)wherecInvCode='99999'select*fromPP_ForecastDetailswith(NoLock)wherecInvCode='99999'select*fromPP_PODetailswith(NoLock)wherecInvCode='99999'select*fromPP_POMainwith(NoLock)wherecInvCode='99999'select*fromPP_PPCDetailswith(NoLock)wherecInvCode='99999'select*fromPP_PPCQuotawith(NoLock)wherecInvCode='99999'select*fromPP_RMRPdetailswith(NoLock)wherecInvCode='99999'select*fromPP_ROPDetailswith(NoLock)wherecInvCode='99999'select*fromPriceJustifywith(NoLock)wherecInvCode='99999'select*fromProductStructurewith(NoLock)wherecPSPCode='99999'select*fromPU_AppVouchswith(NoLock)wherecInvCode='99999'select*fromPU_ArrivalVouchswith(NoLock)wherecInvCode='99999'select*fromPU_T_VMIUsedVouchswith(NoLock)wherecInvCode='99999'select*frompu_veninvverifydwith(NoLock)wherecInvCode='99999'select*fromPurBillVouchswith(NoLock)wherecInvCode='99999'select*fromPurSettleVouchswith(NoLock)wherecInvCode='99999'select*fromqmCheckVoucherwith(NoLock)wherecInvCode='99999'四、总结及延伸select*fromqmInspectVoucherswith(NoLock)wherecInvCode='99999'select*fromqminvcheckplanwith(NoLock)wherecInvCode='99999'select*fromQMINVCHECKSwith(NoLock)wherecInvCode='99999'select*fromQMINVPROJECTSwith(NoLock)wherecInvCode='99999'select*fromQMKEEPSAMPLEwith(NoLock)wherecInvCode='99999'select*fromqmRejectVoucherwith(NoLock)wherecInvCode='99999'select*fromqmRejectVoucherswith(NoLock)wherecDimInvCode='99999'select*fromRdRecordswith(NoLock)wherecInvCode='99999'select*fromSA_cusInvLimitedwith(NoLock)wherecInvCode='99999'select*fromSA_CusUPricewith(NoLock)wherecInvCode='99999'select*fromSA_InvUPricewith(NoLock)wherecInvCode='99999'select*fromSA_QuantityDisRatewith(NoLock)wherecInvCode='99999'select*fromSA_QuoDetailswith(NoLock)wherecInvCode='99999'select*fromSA_SalePlanwith(NoLock)wherecInvCode='99999'select*fromSA_SettleVouchswith(NoLock)wherecInvCode='99999'select*fromSA_WrapLeasewith(NoLock)wherecInvCode='99999'select*fromSaleBillVouchswith(NoLock)wherecInvCode='99999'select*fromSalePayVouchswith(NoLock)wherecInvCode='99999'select*fromScrapVouchswith(NoLock)wherecInvCode='99999'select*fromsfc_proutingparta,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)select*fromSO_SODetailswith(NoLock)wherecInvCode='99999'select*fromSQ_BOMCOMPa,bas_partbwhereb.InvCode='99999'and(a.PartId=b.PartId)四、总结及延伸select*fromSQ_BOMPARENTa,bas_partbwhereb.InvCode='99999'and(a.iParentId=b.PartId)select*fromSQ_BOMSTRUCTa,bas_partbwhereb.InvCode='99999'and(a.lchildid=b.PartId)select*fromSQ_BOMSTRUCTa,bas_partbwhereb.InvCode='99999'and(a.lparentid=b.PartId)select*fromSR_AGFeedPlanwith(NoLock)wherecInvCode='99999'select*fromSR_AGSerInvwith(NoLock)wherecInvCode='99999'select*fromSR_AGSerItemswith(NoLock)wherecInvCode='99999'select*fromSR_Complaintwith(NoLock)wherecComplaintDetail='99999'and(bcComplaintObjectID='Inventory')select*fromSR_Servicebillwith(NoLock)wherecInvCode='99999'select*fromSR_ServiceExpense_swith(NoLock)wherecInvCode='99999'select*fromSR_ServiceItemswith(NoLock)wherecInvCode='99999'select*fromSR_ServiceProductswith(NoLock)wherecInvCode='99999'select*fromSR_ServiceRequestwith(NoLock)wherecInvCode='99999'select*fromST_AppTransVouchswith(NoLock)wherecinvcode='99999'select*fromSt_QCQualifiedSNdetailwith(NoLock)wherecinvcode='99999'select*fromTransVouchswith(NoLock)wherecInvCode='99999'select*fromVen_Inv_Price_Rulewith(NoLock)wherecInvCode='99999'select*fromVenAndInvwith(NoLock)wherecInvCode='99999'select*fromwa_prdetailwith(NoLock)wherecinvcode='99999'select*fromwa_prpricewith(NoLock)wherecinvcode='99999'select*fromwa_prwgdetailwith(NoLock)wherecinvcode='99999'select*fromWhInvContraposewith(NoLock)wherecInvCode='99999'四、总结及延伸
