假期额度的在途额度为负数

解决方案：

问题：假期额度的在途额度为负数

【适用版本】

8.2SP2

【分析处理】

确认为产品bug，已有补丁修复（参考补丁PT146451），关于历史预留问题，可参考以下语句批量修复：

//001备份表

select * into T_HR_ATS_HolidayLimitDetailBK from T_HR_ATS_HolidayLimitDetail

select * into T_HR_ATS_HolidayLimitBK from T_HR_ATS_HolidayLimit

//002此条语句执行之后数据不为空（验证数据备份成功）

select * from T_HR_ATS_HolidayLimitDetailBK 

select * from T_HR_ATS_HolidayLimitBK 

select * from t_hr_ats_holidaylimitdetail where FCHANGETYPE <>1

//003查找有问题人员

select tb.fnumber, tb.fname_l2,de.fid,de.FREEZELIMIT,dt.FREEZELIMIT_DE,dt.FREEZELIMIT_R

from T_BD_PERSON  as tb

inner join(

 select * from T_HR_ATS_HolidayLimit where FREEZELIMIT<>0

) as de on tb.fid  = de.FPROPOSERID 

left join (--在途额度

select FHOLIDAYLIMITID ,FCHANGEUNIT, sum(case when a.FCHANGETYPE =6 then FCHANGEVALUE else 0 end) FREEZELIMIT_DE,

sum(case when b.FBILLSTATE =2 then FCHANGEVALUE else 0 end )  FREEZELIMIT_R

from t_hr_ats_holidaylimitdetail a

inner join t_hr_ats_leavebill b on a.fleavebillid=b.fid

where a.FCHANGETYPE <>1

group by FHOLIDAYLIMITID ,FCHANGEUNIT 

)as dt on de.fid=dt.FHOLIDAYLIMITID

where  de.FREEZELIMIT<>isnull(dt.FREEZELIMIT_R,0)

//004修复数据

--修复余额数据sqlserver(选择语句，执行方言）

update de set de.FREEZELIMIT =isnull(dt.FREEZELIMIT_R,0)

from T_HR_ATS_HolidayLimit de 

left join (--在途额度

select FHOLIDAYLIMITID ,FCHANGEUNIT, sum(case when a.FCHANGETYPE =6 then FCHANGEVALUE else 0 end) FREEZELIMIT_DE,

sum(case when b.FBILLSTATE =2 then FCHANGEVALUE else 0 end )  FREEZELIMIT_R

from t_hr_ats_holidaylimitdetail a

inner join t_hr_ats_leavebill b on a.fleavebillid=b.fid

where a.FCHANGETYPE <>1

group by FHOLIDAYLIMITID ,FCHANGEUNIT 

)as dt on de.fid=dt.FHOLIDAYLIMITID

where de.FREEZELIMIT<>0 and de.FREEZELIMIT<>isnull(dt.FREEZELIMIT_R,0)