零售单执行返利计划出现2次执行返利解决方法原创
73人赞赏了该文章
268次浏览
编辑于2023年05月22日 17:04:56
select * from cmk_t_vip_cardaccts where faccountid=1 and fvalue<0 select * from cmk_vip_t_cardaction where fcardid in(295519,423138) and frecorddatetime>='2022-12-31' --11.6000000000 8.2500000000 --5.6400000000 2.8200000000 --32.8700000000 21.2400000000 --卡流水账-基本信息 cmk_vip_t_cardaction 记账日期FRECORDDATETIME 单据编号fbillno 结存fbalance 账户faccountid 1是积分账户 3返利账户 select * from cmk_vip_t_cardaction where fbiztype='CMK_VIP_PointsRebate' and FRECORDDATETIME>='2022-12-31' and fbillno in( select distinct fbillno from cmk_vip_t_cardaction where fbiztype='CMK_VIP_PointsRebate' and FRECORDDATETIME>='2022-12-31' and faccountid=1 and fbalance<0) select fid,fbillno,fcardid,faccountid,foption,fopervalue,fbalance from cmk_vip_t_cardaction where fbiztype='CMK_VIP_PointsRebate' and FRECORDDATETIME>='2022-12-31' and faccountid=1 and fbalance<0 and fcardid in(363923,363925,363926) --卡资料-账户明细cmk_t_vip_cardaccts select * from cmk_t_vip_cardaccts where fcardid in(363923,363925,363926) and faccountid=1 declare @factionid int declare @fbillno varchar(255) declare @fcardid int declare @faccountid int declare @foption int declare @fopervalue decimal(23,10) declare @factionid2 int declare @faccountid2 int declare @foption2 int declare @fopervalue2 decimal(23,10) declare pointCur cursor for select fid,fbillno,fcardid,faccountid,foption,fopervalue from cmk_vip_t_cardaction where fbiztype='CMK_VIP_PointsRebate' and FRECORDDATETIME>='2022-12-31' and faccountid=1 and fbalance<0 --and fcardid in(363923,363925,363926) open pointCur fetch next from pointCur into @factionid,@fbillno,@fcardid,@faccountid,@foption,@fopervalue while @@FETCH_STATUS=0 begin update cmk_t_vip_cardaccts set fvalue=fvalue+((-1)*@foption*@fopervalue) where fcardid=@fcardid and faccountid=@faccountid select @factionid2=fid,@faccountid2=faccountid,@foption2=foption,@fopervalue2=fopervalue from cmk_vip_t_cardaction where fbiztype='CMK_VIP_PointsRebate' and fbillno=@fbillno and fcardid=@fcardid and faccountid=3 update cmk_t_vip_cardaccts set fvalue=fvalue+((-1)*@foption2*@fopervalue2) where fcardid=@fcardid and faccountid=@faccountid2 delete cmk_vip_t_cardaction where fid=@factionid delete cmk_vip_t_cardaction where fid=@factionid2 fetch next from pointCur into @factionid,@fbillno,@fcardid,@faccountid,@foption,@fopervalue end close pointCur deallocate pointCur
注意:此方法只是解决数据库中出现2次积分返利错误数据恢复到1次返利,没有改变执行计划方案
推荐阅读