分类： 职业技能

【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='形态转换')