U8知识库| 问题号: | 14063 |
|---|
| 解决状态: | 最终解决方案 |
|---|
| 软件版本: | 8.61 |
|---|
| 软件模块: | 应付 |
|---|
| 行业: | 通用 |
|---|
| 关键字: | 日常操作 |
|---|
| 适用产品: | U861–供应链–存货核算 |
|---|
| 问题名称: | 存货核算对帐不平 |
|---|
| 问题现象: | 存货核算里，2005年年末存货明细账存货总帐结存数量单价，有一部分与2006年初存货明细账存货总帐结存数量单价不一致，但是金额是一致的。有差异的存货在文件EXCEL‘存货年初年末差异明细表’已列出。客户想在不影响以前会计月财务报表的基础上把差异数量金额调整掉，以保证以后会计月的成本正确。 |
|---|
| 问题原因: | 请先做好数据备份！问题原因是年度结转后ia_subsidiary表及ia_summary表中期初记录有错误。执行完附件中的语句后需执行重算存货总帐工具,先点‘修复存货总帐’，再点‘重算存货总帐’即可使上下年的存货期初完全一致。 use ufdata_001_2006 select cwhcode,cinvcode,cinvhead into km from ia_subsidiary where imonth=0 and cvoutype=’34’ group by cwhcode,cinvcode,cinvhead having cinvhead is not null delete ia_subsidiary from ia_subsidiary where autoid not in (select max(autoid) from ia_subsidiary where imonth=0 and cvoutype=’34’ group by cwhcode,cinvcode ) and imonth=0 and cvoutype=’34’ update ia_subsidiary set iainquantity=(case when a.inum=0 then null when a.inum is null then null else a.inum end), iincost=(case when a.inum=0 then null when a.inum is null then null else a.imoney/a.inum end), iainprice=a.imoney from ia_subsidiary full outer join (select cwhcode,cinvcode,inum,imoney from UFDATA_001_2005..ia_summary where imonth=12 )a on ia_subsidiary.cwhcode=a.cwhcode and ia_subsidiary.cinvcode=a.cinvcode where (isnull(ia_subsidiary.iainquantity,0)isnull(a.inum,0) or isnull(ia_subsidiary.iainprice,0)isnull(a.imoney,0)) and ia_subsidiary.autoid is not null and ia_subsidiary.imonth=0 and cvoutype=’34’ delete from ia_subsidiary where cvoutype=’34’ and imonth=0 and (isnull(iainquantity,0)=0 and isnull(iainprice,0)=0) update ia_subsidiary set cinvhead=km.cinvhead From km full outer join ia_subsidiary on ia_subsidiary.cwhcode=km.cwhcode and ia_subsidiary.cinvcode=km.cinvcode where km.cwhcode is not null and cvoutype=’34’ and imonth=0 and isnull(ia_subsidiary.cinvhead,0)km.cinvhead SET NOCOUNT ON declare @cVouCode as varchar(30), @cwhcode as varchar(10),@cinvcode as varchar(20),@iainquantity as float,@iincost as float,@iainprice as money set @cVouCode=(select max(cVouCode)-10000 FROM [IA_Subsidiary] where cvoutype=’34’ and imonth=0) DECLARE aa CURSOR FOR select b.cwhcode,b.cinvcode,(case when inum=0 then null when inum is null then null else inum end)inum, (case when inum=0 then null when inum is null then null else imoney/inum end) cost, imoney from( select cwhcode,cinvcode,sum(isnull(iainquantity,0))q,sum(isnull(iainprice,0)) p from ia_subsidiary where cvoutype=’34’and imonth=0 group by cwhcode,cinvcode)a full outer join ( select cwhcode,cinvcode,inum,imoney from UFDATA_001_2005..ia_summary where imonth=12 and(inum0 or imoney0)) b on a.cwhcode=b.cwhcode and a.cinvcode=b.cinvcode where isnull(a.q,0)isnull(b.inum,0) or isnull(a.p,0)isnull(imoney,0) OPEN aa FETCH NEXT FROM aa INTO @cwhcode,@cinvcode,@iainquantity,@iincost,@iainprice WHILE (@@fetch_status=0) BEGIN insert into ia_subsidiary([bRdFlag], [cVouCode], [ID], [dVouDate], [dKeepDate], [iMonth], [cVouType], [cWhCode], [cInvCode], [cHandler], [iAInQuantity],[iInCost], [iAInPrice], [cAccounter], [cMaker], [bMoneyFlag], [bSale], [cPZID]) values(1, @cVouCode,@cVouCode,’2005-12-31′,’2005-12-31′, 0, 34, @cWhCode, @cInvCode,’朱虹’,@iAInQuantity,@iInCost,@iAInPrice,’朱虹’,’朱虹’,1,0,0) FETCH NEXT FROM aa INTO @cwhcode,@cinvcode,@iainquantity,@iincost,@iainprice set @cVouCode=@cVouCode+1 END CLOSE aa DEALLOCATE aa GO drop table km |
|---|
| 解决方案: | 请先做好数据备份！问题原因是年度结转后ia_subsidiary表及ia_summary表中期初记录有错误。执行完附件中的语句后需执行重算存货总帐工具,先点‘修复存货总帐’，再点‘重算存货总帐’即可使上下年的存货期初完全一致。 use ufdata_001_2006 select cwhcode,cinvcode,cinvhead into km from ia_subsidiary where imonth=0 and cvoutype=’34’ group by cwhcode,cinvcode,cinvhead having cinvhead is not null delete ia_subsidiary from ia_subsidiary where autoid not in (select max(autoid) from ia_subsidiary where imonth=0 and cvoutype=’34’ group by cwhcode,cinvcode ) and imonth=0 and cvoutype=’34’ update ia_subsidiary set iainquantity=(case when a.inum=0 then null when a.inum is null then null else a.inum end), iincost=(case when a.inum=0 then null when a.inum is null then null else a.imoney/a.inum end), iainprice=a.imoney from ia_subsidiary full outer join (select cwhcode,cinvcode,inum,imoney from UFDATA_001_2005..ia_summary where imonth=12 )a on ia_subsidiary.cwhcode=a.cwhcode and ia_subsidiary.cinvcode=a.cinvcode where (isnull(ia_subsidiary.iainquantity,0)isnull(a.inum,0) or isnull(ia_subsidiary.iainprice,0)isnull(a.imoney,0)) and ia_subsidiary.autoid is not null and ia_subsidiary.imonth=0 and cvoutype=’34’ delete from ia_subsidiary where cvoutype=’34’ and imonth=0 and (isnull(iainquantity,0)=0 and isnull(iainprice,0)=0) update ia_subsidiary set cinvhead=km.cinvhead From km full outer join ia_subsidiary on ia_subsidiary.cwhcode=km.cwhcode and ia_subsidiary.cinvcode=km.cinvcode where km.cwhcode is not null and cvoutype=’34’ and imonth=0 and isnull(ia_subsidiary.cinvhead,0)km.cinvhead SET NOCOUNT ON declare @cVouCode as varchar(30), @cwhcode as varchar(10),@cinvcode as varchar(20),@iainquantity as float,@iincost as float,@iainprice as money set @cVouCode=(select max(cVouCode)-10000 FROM [IA_Subsidiary] where cvoutype=’34’ and imonth=0) DECLARE aa CURSOR FOR select b.cwhcode,b.cinvcode,(case when inum=0 then null when inum is null then null else inum end)inum, (case when inum=0 then null when inum is null then null else imoney/inum end) cost, imoney from( select cwhcode,cinvcode,sum(isnull(iainquantity,0))q,sum(isnull(iainprice,0)) p from ia_subsidiary where cvoutype=’34’and imonth=0 group by cwhcode,cinvcode)a full outer join ( select cwhcode,cinvcode,inum,imoney from UFDATA_001_2005..ia_summary where imonth=12 and(inum0 or imoney0)) b on a.cwhcode=b.cwhcode and a.cinvcode=b.cinvcode where isnull(a.q,0)isnull(b.inum,0) or isnull(a.p,0)isnull(imoney,0) OPEN aa FETCH NEXT FROM aa INTO @cwhcode,@cinvcode,@iainquantity,@iincost,@iainprice WHILE (@@fetch_status=0) BEGIN insert into ia_subsidiary([bRdFlag], [cVouCode], [ID], [dVouDate], [dKeepDate], [iMonth], [cVouType], [cWhCode], [cInvCode], [cHandler], [iAInQuantity],[iInCost], [iAInPrice], [cAccounter], [cMaker], [bMoneyFlag], [bSale], [cPZID]) values(1, @cVouCode,@cVouCode,’2005-12-31′,’2005-12-31′, 0, 34, @cWhCode, @cInvCode,’朱虹’,@iAInQuantity,@iInCost,@iAInPrice,’朱虹’,’朱虹’,1,0,0) FETCH NEXT FROM aa INTO @cwhcode,@cinvcode,@iainquantity,@iincost,@iainprice set @cVouCode=@cVouCode+1 END CLOSE aa DEALLOCATE aa GO drop table km |
|---|
| 补丁编号: | |
|---|
| 录入日期: | 2016-03-16 15:23:45 |
|---|
| 最后更新时间: | |
|---|
