问题描述

1、用户反馈删除两张之前通过采购转固定资产的卡片后,想要再次生成卡片时候,却找不到这两张卡片。

2、用户尝试对其他未转固定资产的订单进行转固,在保存卡片时提示“本次结转将放弃全部已完成的业务”,无法保存。

解决过程

1、先后台跟踪采购订单转固定资产界面的查询语句:

 select
zpurRkdList.cpoid,zpurRkdList.cinvcode,zpurRkdList.cinvname,zpurRkdList.cinvstd,
iquantity,iprice,istax as itaxprice,autoid,zpurRkdList.ufts,ddate,ccode,iUnitCost,
zpurRkdList.cexch_name,iexchrate,cFree1,cFree2,cFree3,cFree4,cFree5,cFree6,cFree7,
cFree8,cFree9,cFree10 ,zpurRkdList.cvencode ,cvenabbname as cVenName ,p.dPODate as dPurDate,
I.cBarCode as sCommodityCode
From
zpurRkdList with(nolock)
inner join inventory I on zpurRkdList.cinvcode = I.cInvCode
inner join po_pomain p on zpurRkdList.cpoid = P.cPOID
where
isnull(zpurRkdList.cpoid,'')<>''
and zpurRkdList.cbustype= N'固定资产'
and isnull(iFaQty,0)=0
and abs(isnull(iquantity,0))>0.000001
and abs(abs(iquantity) -abs(isnull(isquantity,0)) )<0.000001
and 1=1

2、分析发现上面第14行过滤条件为采购入库单子表的iFaQty=0,进一步查询发现,原订单对应入库单的iFaQty字段没有清空:

select iFaQty,rdrecords01.* from rdrecords01
left join rdrecord01 on rdrecords01.id=rdrecord01.id
where rdrecord01.cOrderCode in ('CGDD190800097','CGDD190800098')

3、那么问题应该出在这里,把这两张订单对应采购入库单的iFaQty字段清空后,就能在采购转固界面找到这两行记录了:

--先备份rdrecords01表
select * into rdrecords0120191128 from rdrecords01
--然后查询确认要修改范围
select iFaQty ,* from rdrecords01 where AutoID in ('',''
--清空这两行记录的iFaQty字段
update rdrecords01 set iFaQty=NULL where AutoID in ('','')

4、但这并不一定足够,进一步分析固定资产模块各表格看是否有未删除记录:

--查询固定资产采购资产表 有结果
select * from fa_CardOfInv
where cpoid in ('CGDD190800097','CGDD190800098')
--查询固定资产卡片明细表 无结果
select * from fa_Cards_Detail
where sCardNum in (select sCardNum from fa_CardOfInv where cpoid in ('CGDD190800097','CGDD190800098'))
--查询固定资产卡片主表 无结果
select * from fa_Cards
where sCardNum in (select sCardNum from fa_CardOfInv where cpoid in ('CGDD190800097','CGDD190800098'))
--查询固定资产卡片子表 无结果
select * from fa_CardsSheets
where sCardNum in (select sCardNum from fa_CardOfInv where cpoid in ('CGDD190800097','CGDD190800098'))

5、看来问题还出现在固定资产模块的fa_CardOfInv数据未清空,占用了两个卡片编号,导致后续其他资产无法转固,删除这两条记录即可

--先备份fa_CardOfInv表
select * into fa_CardOfInv20191128 from fa_CardOfInv
--然后查询确认要修改范围
select * from fa_CardOfInv where cPoid in ('CGDD190800097','CGDD190800098')
--确认无误后删除这两行
delete from fa_CardOfInv where cPoid in ('CGDD190800097','CGDD190800098')

问题总结

用户删除采购转固定资产卡片后,可能由于系统未正确清空原订单对应采购入库单的“转固定资产数量”字段,以及未删除“固定资产采购表”数据,导致问题出现。

05-11 21:58