用友U8 用户为个别计价,已经做过成本分配的出库单在个别计价分配表中仍然体现为未分配,导致用户有个别存货的金额重复分配成本,出库数量正确用友U8 用户为个别计价,已经做过成本分配的出库单在个别计价分配表中仍然体现为未分配,导致用户有个别存货的金额重复分配成本,出库数量正确
问题原因:数据库问题,ia_individual表缺少相关记录,以及ia_subsidiary表中cbatchcode字段部分数据错误。 解决方法:--先用下面语句添加缺少的部分记录:insert into ia_individual (cinid,coutid,cvouchcode,cquantity,cissale,cvoutype) (select cbatchcode, id, Null, isnull(iaoutquantity,iainquantity), case when cvoutype in ('26','27','28','29') then 1 else 0 end, case when brdflag=1 then 2 else case when isnull(iaoutprice,0)>0 then 0 else 1 end end from ia_subsidiary left join warehouse on warehouse.cwhcode=ia_subsidiary.cwhcode where cwhvaluestyle='个别计价法' and not cbatchcode is null and not id in (select coutid from ia_individual) and not ia_subsidiary.cvoutype in ('24','30') ) update ia_individual set cvoutype='2' from ia_individual a left join ia_subsidiary b on a.coutid=b.id and case when b.cvoutype in ('26','27','28','29') then 1 else 0 end=a.cissale where a.cvoutype<>'2' and b.brdflag=1 update ia_individual set cinid=c.autoid from ia_individual a left join ia_subsidiary b on a.cinid=b.autoid left join ia_subsidiary c on b.id=c.id and c.cvoutype='01' where b.cvoutype='30' --查找到出库大于入库的记录select * from ia_individual where cinid in (select autoid from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join (select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcodewhere outnum>innum)--可以用下面语句找到个别记价成本分配错误数据select * from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join (select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcodewhere outnum>innum--用下面的语句找到cbatchcode错误的明细帐入库记录记录。select cbatchcode,* from ia_subsidiary where autoid in (select autoid from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join (select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcodewhere outnum>innum)
解决方案:
问题原因:数据库问题,ia_individual表缺少相关记录,以及ia_subsidiary表中cbatchcode字段部分数据错误。 解决方法:--先用下面语句添加缺少的部分记录:insert into ia_individual (cinid,coutid,cvouchcode,cquantity,cissale,cvoutype) (select cbatchcode, id, Null, isnull(iaoutquantity,iainquantity), case when cvoutype in ('26','27','28','29') then 1 else 0 end, case when brdflag=1 then 2 else case when isnull(iaoutprice,0)>0 then 0 else 1 end end from ia_subsidiary left join warehouse on warehouse.cwhcode=ia_subsidiary.cwhcode where cwhvaluestyle='个别计价法' and not cbatchcode is null and not id in (select coutid from ia_individual) and not ia_subsidiary.cvoutype in ('24','30') ) update ia_individual set cvoutype='2' from ia_individual a left join ia_subsidiary b on a.coutid=b.id and case when b.cvoutype in ('26','27','28','29') then 1 else 0 end=a.cissale where a.cvoutype<>'2' and b.brdflag=1 update ia_individual set cinid=c.autoid from ia_individual a left join ia_subsidiary b on a.cinid=b.autoid left join ia_subsidiary c on b.id=c.id and c.cvoutype='01' where b.cvoutype='30' --查找到出库大于入库的记录select * from ia_individual where cinid in (select autoid from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join (select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcodewhere outnum>innum)--可以用下面语句找到个别记价成本分配错误数据select * from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join (select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcodewhere outnum>innum--用下面的语句找到cbatchcode错误的明细帐入库记录记录。select cbatchcode,* from ia_subsidiary where autoid in (select autoid from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join (select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcodewhere outnum>innum)