用友U8 进入库存管理查询供应商库存表: 1、账表—》库存账—》供应商库存表—》过滤(此表没有数据) 2、存货核算系统期初数据与库存期初数据对不上: 如:存货编号为31010012的咀中华在库存中的期初数是:8429,在存货的期初数据是:6679。用友U8 进入库存管理查询供应商库存表: 1、账表—》库存账—》供应商库存表—》过滤(此表没有数据) 2、存货核算系统期初数据与库存期初数据对不上: 如:存货编号为31010012的咀中华在库存中的期初数是:8429,在存货的期初数据是:6679。
问题原因:问题1:存货没有出库跟踪入库,而“供应商库存表”是对出入库跟踪的业务进行查询,这是产品定义的需求,并非数据错误; 【问题2分析】:首先查看2004年末存货与库存是否对应。但查询后发现,在2004年的库存模块中,现存量查询和收发存汇总表不一致,以您给出的存货为例,分别是8429和6679;而查询存货模块中的收发存汇总表为6679;再查2005年期初数据,发现存货前后可对照,库存前后也可对照(对照04年现存量)。则可初步判断结转中问题不大,可能现存量在2004年有问题。在2004年整理现存量后,发现各种数据皆能对应,即:库存内部,存货内部以及库存与存货间在04年数据都可对应。因此,结转时,可能将当时错误的数据结转过来,造成数据不一致。故需要修复2005年的数据。 解决方法:【问题2解决】:将年前年后的数据汇总并对比,修正需要修正的数据。具体脚本如下(特别注意,此脚本只适用非批次和非出入跟踪业务): use ufdata_222_2005 --筛选04年相关数据 select a.brdflag,a.cwhcode,b.cinvcode, case brdflag when 1 then iquantity else (-1)*iquantity end as iquantity into ufdata_222_2004..aa from ufdata_222_2004..rdrecord a join (ufdata_222_2004..rdrecords b join inventory c on b.cinvcode=c.cinvcode) on a.id=b.id where bpufirst<>1 and biafirst<>1 and c.btrack=0 select cwhcode,cinvcode,sum(iquantity) as iquantity into ufdata_222_2004..bb from ufdata_222_2004..aa group by cwhcode,cinvcode --筛选05年相关数据 select a.brdflag,a.cwhcode,b.cinvcode,cbatch, case brdflag when 1 then iquantity else (-1)*iquantity end as iquantity --into ufdata_222_2005..aa from ufdata_222_2005..rdrecord a join (ufdata_222_2005..rdrecords b join inventory c on b.cinvcode=c.cinvcode) on a.id=b.id where bisstqc=1 and c.btrack=0 select cwhcode,cinvcode,sum(iquantity) as iquantity into ufdata_222_2005..bb from ufdata_222_2005..aa group by cwhcode,cinvcode --关联查询需要修正记录 select b.iquantity,c.iquantity,* from ufdata_222_2005..rdrecord a join (ufdata_222_2005..rdrecords b join ufdata_222_2004..bb c on b.cinvcode=c.cinvcode ) on a.id=b.id and a.cwhcode=c.cwhcode where b.iquantity<>c.iquantity and a.bisstqc=1 and cvouchtype='34' --修正记录 update ufdata_222_2005..rdrecords set iquantity=c.iquantity from ufdata_222_2005..rdrecord a join (ufdata_222_2005..rdrecords b join ufdata_222_2004..bb c on b.cinvcode=c.cinvcode ) on a.id=b.id and a.cwhcode=c.cwhcode where b.iquantity<>c.iquantity and a.bisstqc=1 and cvouchtype='34' drop table ufdata_222_2005..aa drop table ufdata_222_2004..aa drop table ufdata_222_2005..bb drop table ufdata_222_2004..bb --另注:以下步骤是插入上年未结转得数据。需要新建账套结转2004年得数据才可进行。已通过电话告知。其中ufdata_333_2005为新建账套结转后得05年度账 use ufdata_333_2005 --选择该转而未转的数据至临时表,需要关联两个数据库 SELECT [bRdFlag], [cBusType], [cBusCode], [cVouCode], [ID], [ValueID], [JustID], [dVouDate], [dKeepDate], [iMonth], [iPZID], [iPZDate], [cPZtype], [cPZdigest], [cInvHead], [cDifHead], [cOppHead], [cVouType], [cPTCode], [cSTCode], [cWhCode], [cAccDep], [cInvCode], [cRdCode], [cVenCode], [cCusCode], [cOrderCode], [cARVCode], [cBillCode], [cDLCode], [cPSPCode], [cProCode], [cDepCode], [cPersonCode], [cHandler], [iAInQuantity], [iAOutQuantity], [iInCost], [iOutCost], [iAInPrice], [iAOutPrice], [cBatchCode], [iDebitDifCost], [iCreditDifCost], [cAccounter], [cMaker], [bFlag], [bMoneyFlag], [bSale], [cMemo], [cDefine1], [cDefine2], [cDefine3], [cDefine4], [cDefine5], [cDefine6], [cDefine7], [cDefine8], [cDefine9], [cDefine10], [cFree1], [cFree2], [cPZID], [cDefine22], [cDefine23], [cDefine24], [cDefine25], [cDefine26], [cDefine27], [cItem_class], [cItemCode], [cName], [cItemCName], [noJustQuantity], [cFree3], [cFree4], [cFree5], [cFree6], [cFree7], [cFree8], [cFree9], [cFree10], [cDefine11], [cDefine12], [cDefine13], [cDefine14], [cDefine15], [cDefine16], [cDefine28], [cDefine29], [cDefine30], [cDefine31], [cDefine32], [cDefine33], [cDefine34], [cDefine35], [cDefine36], [cDefine37], [psvsid], [cCXHDcode], [cCXFScode] into tempaaa FROM [UFDATA_333_2005].[dbo].[IA_Subsidiary] where cinvcode not in (select cinvcode from ufdata_222_2005..ia_subsidiary) --将临时表中数据,插入用户现在使用的数据库中 INSERT INTO [UFDATA_222_2005].[dbo].[IA_Subsidiary]( [bRdFlag], [cBusType], [cBusCode], [cVouCode], [ID], [ValueID], [JustID], [dVouDate], [dKeepDate], [iMonth], [iPZID], [iPZDate], [cPZtype], [cPZdigest], [cInvHead], [cDifHead], [cOppHead], [cVouType], [cPTCode], [cSTCode], [cWhCode], [cAccDep], [cInvCode], [cRdCode], [cVenCode], [cCusCode], [cOrderCode], [cARVCode], [cBillCode], [cDLCode], [cPSPCode], [cProCode], [cDepCode], [cPersonCode], [cHandler], [iAInQuantity], [iAOutQuantity], [iInCost], [iOutCost], [iAInPrice], [iAOutPrice], [cBatchCode], [iDebitDifCost], [iCreditDifCost], [cAccounter], [cMaker], [bFlag], [bMoneyFlag], [bSale], [cMemo], [cDefine1], [cDefine2], [cDefine3], [cDefine4], [cDefine5], [cDefine6], [cDefine7], [cDefine8], [cDefine9], [cDefine10], [cFree1], [cFree2], [cPZID], [cDefine22], [cDefine23], [cDefine24], [cDefine25], [cDefine26], [cDefine27], [cItem_class], [cItemCode], [cName], [cItemCName], [noJustQuantity], [cFree3], [cFree4], [cFree5], [cFree6], [cFree7], [cFree8], [cFree9], [cFree10], [cDefine11], [cDefine12], [cDefine13], [cDefine14], [cDefine15], [cDefine16], [cDefine28], [cDefine29], [cDefine30], [cDefine31], [cDefine32], [cDefine33], [cDefine34], [cDefine35], [cDefine36], [cDefine37], [psvsid], [cCXHDcode], [cCXFScode]) select * from ufdata_333_2005..tempaaa
解决方案:
问题原因:问题1:存货没有出库跟踪入库,而“供应商库存表”是对出入库跟踪的业务进行查询,这是产品定义的需求,并非数据错误; 【问题2分析】:首先查看2004年末存货与库存是否对应。但查询后发现,在2004年的库存模块中,现存量查询和收发存汇总表不一致,以您给出的存货为例,分别是8429和6679;而查询存货模块中的收发存汇总表为6679;再查2005年期初数据,发现存货前后可对照,库存前后也可对照(对照04年现存量)。则可初步判断结转中问题不大,可能现存量在2004年有问题。在2004年整理现存量后,发现各种数据皆能对应,即:库存内部,存货内部以及库存与存货间在04年数据都可对应。因此,结转时,可能将当时错误的数据结转过来,造成数据不一致。故需要修复2005年的数据。 解决方法:【问题2解决】:将年前年后的数据汇总并对比,修正需要修正的数据。具体脚本如下(特别注意,此脚本只适用非批次和非出入跟踪业务): use ufdata_222_2005 --筛选04年相关数据 select a.brdflag,a.cwhcode,b.cinvcode, case brdflag when 1 then iquantity else (-1)*iquantity end as iquantity into ufdata_222_2004..aa from ufdata_222_2004..rdrecord a join (ufdata_222_2004..rdrecords b join inventory c on b.cinvcode=c.cinvcode) on a.id=b.id where bpufirst<>1 and biafirst<>1 and c.btrack=0 select cwhcode,cinvcode,sum(iquantity) as iquantity into ufdata_222_2004..bb from ufdata_222_2004..aa group by cwhcode,cinvcode --筛选05年相关数据 select a.brdflag,a.cwhcode,b.cinvcode,cbatch, case brdflag when 1 then iquantity else (-1)*iquantity end as iquantity --into ufdata_222_2005..aa from ufdata_222_2005..rdrecord a join (ufdata_222_2005..rdrecords b join inventory c on b.cinvcode=c.cinvcode) on a.id=b.id where bisstqc=1 and c.btrack=0 select cwhcode,cinvcode,sum(iquantity) as iquantity into ufdata_222_2005..bb from ufdata_222_2005..aa group by cwhcode,cinvcode --关联查询需要修正记录 select b.iquantity,c.iquantity,* from ufdata_222_2005..rdrecord a join (ufdata_222_2005..rdrecords b join ufdata_222_2004..bb c on b.cinvcode=c.cinvcode ) on a.id=b.id and a.cwhcode=c.cwhcode where b.iquantity<>c.iquantity and a.bisstqc=1 and cvouchtype='34' --修正记录 update ufdata_222_2005..rdrecords set iquantity=c.iquantity from ufdata_222_2005..rdrecord a join (ufdata_222_2005..rdrecords b join ufdata_222_2004..bb c on b.cinvcode=c.cinvcode ) on a.id=b.id and a.cwhcode=c.cwhcode where b.iquantity<>c.iquantity and a.bisstqc=1 and cvouchtype='34' drop table ufdata_222_2005..aa drop table ufdata_222_2004..aa drop table ufdata_222_2005..bb drop table ufdata_222_2004..bb --另注:以下步骤是插入上年未结转得数据。需要新建账套结转2004年得数据才可进行。已通过电话告知。其中ufdata_333_2005为新建账套结转后得05年度账 use ufdata_333_2005 --选择该转而未转的数据至临时表,需要关联两个数据库 SELECT [bRdFlag], [cBusType], [cBusCode], [cVouCode], [ID], [ValueID], [JustID], [dVouDate], [dKeepDate], [iMonth], [iPZID], [iPZDate], [cPZtype], [cPZdigest], [cInvHead], [cDifHead], [cOppHead], [cVouType], [cPTCode], [cSTCode], [cWhCode], [cAccDep], [cInvCode], [cRdCode], [cVenCode], [cCusCode], [cOrderCode], [cARVCode], [cBillCode], [cDLCode], [cPSPCode], [cProCode], [cDepCode], [cPersonCode], [cHandler], [iAInQuantity], [iAOutQuantity], [iInCost], [iOutCost], [iAInPrice], [iAOutPrice], [cBatchCode], [iDebitDifCost], [iCreditDifCost], [cAccounter], [cMaker], [bFlag], [bMoneyFlag], [bSale], [cMemo], [cDefine1], [cDefine2], [cDefine3], [cDefine4], [cDefine5], [cDefine6], [cDefine7], [cDefine8], [cDefine9], [cDefine10], [cFree1], [cFree2], [cPZID], [cDefine22], [cDefine23], [cDefine24], [cDefine25], [cDefine26], [cDefine27], [cItem_class], [cItemCode], [cName], [cItemCName], [noJustQuantity], [cFree3], [cFree4], [cFree5], [cFree6], [cFree7], [cFree8], [cFree9], [cFree10], [cDefine11], [cDefine12], [cDefine13], [cDefine14], [cDefine15], [cDefine16], [cDefine28], [cDefine29], [cDefine30], [cDefine31], [cDefine32], [cDefine33], [cDefine34], [cDefine35], [cDefine36], [cDefine37], [psvsid], [cCXHDcode], [cCXFScode] into tempaaa FROM [UFDATA_333_2005].[dbo].[IA_Subsidiary] where cinvcode not in (select cinvcode from ufdata_222_2005..ia_subsidiary) --将临时表中数据,插入用户现在使用的数据库中 INSERT INTO [UFDATA_222_2005].[dbo].[IA_Subsidiary]( [bRdFlag], [cBusType], [cBusCode], [cVouCode], [ID], [ValueID], [JustID], [dVouDate], [dKeepDate], [iMonth], [iPZID], [iPZDate], [cPZtype], [cPZdigest], [cInvHead], [cDifHead], [cOppHead], [cVouType], [cPTCode], [cSTCode], [cWhCode], [cAccDep], [cInvCode], [cRdCode], [cVenCode], [cCusCode], [cOrderCode], [cARVCode], [cBillCode], [cDLCode], [cPSPCode], [cProCode], [cDepCode], [cPersonCode], [cHandler], [iAInQuantity], [iAOutQuantity], [iInCost], [iOutCost], [iAInPrice], [iAOutPrice], [cBatchCode], [iDebitDifCost], [iCreditDifCost], [cAccounter], [cMaker], [bFlag], [bMoneyFlag], [bSale], [cMemo], [cDefine1], [cDefine2], [cDefine3], [cDefine4], [cDefine5], [cDefine6], [cDefine7], [cDefine8], [cDefine9], [cDefine10], [cFree1], [cFree2], [cPZID], [cDefine22], [cDefine23], [cDefine24], [cDefine25], [cDefine26], [cDefine27], [cItem_class], [cItemCode], [cName], [cItemCName], [noJustQuantity], [cFree3], [cFree4], [cFree5], [cFree6], [cFree7], [cFree8], [cFree9], [cFree10], [cDefine11], [cDefine12], [cDefine13], [cDefine14], [cDefine15], [cDefine16], [cDefine28], [cDefine29], [cDefine30], [cDefine31], [cDefine32], [cDefine33], [cDefine34], [cDefine35], [cDefine36], [cDefine37], [psvsid], [cCXHDcode], [cCXFScode]) select * from ufdata_333_2005..tempaaa