需求：采购入库单和发票金额过大，需要进行控制
解决方案：分3步骤处理。
第一，采购入库单控制，实操发现订单的金额与采购入库单不含税金额有微末差异（主要由于计算规则不一致）通过数据库语句和自定义项检查系统的暂估有无问题，存在差异需要进行修正。通过表体自定义解决
update rdrecords01
set cDefine27 = (select cast(round( ((rdrecords01.iQuantity*rdrecords01.iOriTaxCost)/((100+rdrecords01.iTaxRate)/100) ) ,2) as numeric(20,2)) )
from RdRecord01
where rdrecords01.ID=RdRecord01.ID and RdRecord01.cHandler is null
第二，在发票汇总暂估金额进行核对，看是否存在差异（最终反馈在自定义项上）
步骤一
use UFDATA_888_2019
update PurBillVouch
set cDefine16 =(select cast(round( (select sum(cdefine27)
from PurBillVouchs where PurBillVouch.PBVID=PurBillVouchs.PBVID) ,2) as numeric(20,2)) )
from PurBillVouchs
where PurBillVouch.PBVID=PurBillVouchs.PBVID and PurBillVouch.cVerifier is not null and PurBillVouch.cPBVVerifier is null
步骤二
use UFDATA_888_2019
update PurBillVouch
set cDefine7 =
(select cast(round( (select sum(iMoney)
from PurBillVouchs where PurBillVouch.PBVID=PurBillVouchs.PBVID) ,2) as numeric(20,2)) )
from PurBillVouchs
where PurBillVouch.PBVID=PurBillVouchs.PBVID and PurBillVouch.cVerifier is not null and PurBillVouch.cPBVVerifier is null
步骤三
use UFDATA_888_2019
update PurBillVouch
set cDefine14=‘暂估正常’
where (cDefine7-cDefine16)<0.1 and (cDefine7-cDefine16)>-0.1 and PurBillVouch.cVerifier is not null and PurBillVouch.cPBVVerifier is null
update PurBillVouch
set cDefine14=‘暂估错误’
where (cDefine7-cDefine16)>0.1 or (cDefine7-cDefine16)<-0.1 and PurBillVouch.cVerifier is not null and PurBillVouch.cPBVVerifier is null
第三，通过在发票触发器进行处理解决
--检测复核人，复核是暂估金额和发票金额差异过大进行提示操作–
----采购发票金额与入库单进行差异对比，金额差异过大，系统不允许复核—
---系统留部分权限人员不受上述限制控制—-
ALTER trigger [dbo].[zz002]
on [dbo].[PurBillVouch]
for update as
if Update(cVerifier) begin
declare @财务暂估汇总 float,
@复核人 nvarchar(20),
@发票金额汇总 float;
set @财务暂估汇总=(select
sum(cdefine27)
from PurBillVouchs,PurBillVouch,inserted
where PurBillVouch.PBVID=PurBillVouchs.PBVID and inserted.PBVID=PurBillVouchs.PBVID );
set @发票金额汇总=(select
sum(iMoney)
from PurBillVouchs,PurBillVouch,inserted
where PurBillVouch.PBVID=PurBillVouchs.PBVID and inserted.PBVID=PurBillVouchs.PBVID);
set @复核人=(select inserted.cVerifier from PurBillVouch,inserted
where inserted.PBVID=PurBillVouch.PBVID)
set nocount on
if ISNULL(@复核人,'')=N’null’
begin
set nocount off
return
end
if ISNULL(@复核人,'')=N’张正’ or ISNULL(@复核人,'')=N’汪克祥’or ISNULL(@复核人,'')=N’葛美霞’
begin
set nocount off
return
end
if ISNULL(@复核人,'')<>N’null’
begin
if exists (select \* from inserted
inner join PurBillVouchs on inserted.PBVID=PurBillVouchs.PBVID
and (@财务暂估汇总-@发票金额汇总>0.1 or @财务暂估汇总-@发票金额汇总<-0.1 )
and inserted.cVerifier<>‘张正’ and inserted.cVerifier<> ‘汪克祥’ and inserted.cVerifier<>‘葛美霞’ )
raiserror(‘友情提示：暂估金额与发票金额存在差异过大，请查找原因并更正差异！’,16,0)
end
set nocount off
end
实操：第一和第二步骤建议使用SQL SERVER 代理进行执行即可
