【sql代理处理计划处理问题】
（一）采购入库单 表体自定义项6
--更新采购入库单，设置表体自定义项6为财务暂估无税金额，通过设置含税单价*数量然后扣除税额得到--
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
(二)采购发票 表头自定义项16
1，步骤一：
--在审核人为空复核人不为空，更新采购发票得表头自定义项16，设置值等于表体自定义项6的合计--
use UFDATA_001_2021
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
2，步骤二
--在审核人为空复核人不为空；更新 采购发票表头自定义项7，设置等于表体无税金额的合计--
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
3，步骤三
----在审核人为空复核人不为空；更新采购发票表头自定义项14，通过比对暂估金额和发票金额差异，给出对应得文字反馈--
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
【触发器处理】
1，采购入库单主表
--设置表体自定义项12，当采购入库单审核时，更新子表自定义项12等于审核人--
create trigger [dbo].[rukushenhe003]
on [dbo].[RdRecord01] for update
as
if update (chandler)
update rdrecords01
set cDefine33=rdrecord01.cHandler from rdrecord01 ,inserted
where rdrecord01.id =rdrecords01.ID and inserted.id =rdrecords01.ID
2，采购发票主表
--检测复核人，复核是暂估金额和发票金额差异过大进行提示操作--
ALTER trigger [dbo].[caigouruku001]
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
3，采购发票子表
--在填制采购发票时，更新采购发票表体自定项5等于入库单子表的含税单价，采购发票表体自定项6等于发票数量*表体自定项5计算出来的不含税价，表体自定项12=入库单表体得自定项12--
ALTER trigger [dbo].[rukusee001211]
on [dbo].[PurBillVouchs] for insert,update
not for replication
as
update PurBillVouchs
set cDefine26=rdrecords01.ioritaxcost,PurBillVouchs.cDefine27 = (PurBillVouchs.iPBVQuantity*PurBillVouchs.cDefine26)/((100+PurBillVouchs.iTaxRate)/100),
PurBillVouchs.cDefine33=rdrecords01.cDefine33
from rdrecords01 inner join PurBillVouchs on PurBillVouchs.rdsid= rdrecords01.AutoID
and rdrecords01.cInvCode=PurBillVouchs.cInvCode
inner join inserted on PurBillVouchs.rdsid= inserted.RdsId
4，销售出库单
--发货单自动生成的销售出库单自动审核--
ALTER trigger [dbo].[xiaoshouchuku001]
on [dbo].[rdrecord32]
for insert
as
update rdrecord32 set
chandler=cMaker, dVeriDate=dDate
where chandler is null
5，其他入库单
--上游单据自动生成的其他入库单，自动审核--
ALTER trigger [dbo].[qitaruku001]
on [dbo].[RdRecord08]
for insert
as
update rdrecord08 set
chandler=cMaker, dVeriDate=dDate
where (chandler is NULL) and (cMaker='周志磊'or cMaker='蒋艳梅'or cMaker='郑媛媛'or cMaker='刘广梅'or cMaker='张黔'or cMaker='王静'or cMaker='潘锦萍')and (cSource='调拨'or cSource='形态转换')
6，其他出库单
--上游单据自动生成的其他出库单，自动审核--
ALTER trigger [dbo].[qitachuku001]
on [dbo].[RdRecord09]
for insert
as
update rdrecord09 set
chandler=cMaker, dVeriDate=dDate
where (chandler is null) and (cMaker='周志磊'or cMaker='郑媛媛'or cMaker='蒋艳梅'or cMaker='刘广梅'or cMaker='张黔'or cMaker='王静'or cMaker='潘锦萍')and (cSource='调拨'or cSource='形态转换')