付款申请单剩余可申请金额触发器

USE [UFDATA_001_2018]
GO
/****** Object:  Trigger [dbo].[tr_FHS_GetPaymentAgreement]    Script Date: 01/13/2020 10:42:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        <Author,,Name>
-- Create date: <Create Date,,>
-- Description:    <Description,,>
-- =============================================
ALTER TRIGGER [dbo].[tr_FHS_GetPaymentAgreement] 
   ON  [dbo].[AP_ApplyPayVouchs]  
   FOR INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    declare @id int; --表体ID号
    declare @PID int; --主表ID号
    declare @BillType varchar(50); --单据类型
    declare @BillID varchar(50); --订单编码
    declare @AgreementCode varchar(50); --收付款协议编码
    declare @AgreementName varchar(100); --收付款协议名称
    declare @ZSumApply float; --订单或发票总金额 
    declare @SumApply float; --累计申请金额
    declare @SurplusApply float; --剩余申请金额
    declare @SourceRowId int; --源单行ID
    set @id=(select AutoID from inserted);
    set @PID=(select PID from inserted);
    set @BillID=(select cCoVouchID  from inserted);
    set @BillType=(select cSource from inserted);
    set @SourceRowId=(select iBVid from inserted);
    --set @ZSumApply=(select iOriSum from inserted);
    
    if @BillType='采购订单'
        begin
            --获取对应订单上面的收付款协议
            select @AgreementCode=PO.cVenPUOMProtocol,@AgreementName=AA.cName from PO_Pomain PO
            inner join AA_Agreement AA on AA.cCode=PO.cVenPUOMProtocol 
            where PO.cPOID=@BillID
            select @SumApply=sum(iApplyAmt_f) from AP_ApplyPayVouchs 
            where cSource='采购订单' and cCoVouchID=@BillID and iBVid=@SourceRowId and AutoID!=@id
            SET @SurplusApply=(SELECT iApplySum_f FROM AP_V_PUOrderApply WHERE POsID=(SELECT Inserted.iBVid FROM Inserted))
        end
    else if @BillType='采购发票'
        begin
            --获取表头上面的收付款协议
            select @AgreementCode=cGatheringPlan from AP_ApplyPayVouch where PID=@PID;
            select @AgreementName=cName from AA_Agreement where cCode=@AgreementCode;
            --获取已申请金额
            select @SumApply=sum(iApplyAmt_f) from AP_ApplyPayVouchs 
            where cSource='采购发票' and cCoVouchID=@BillID and iBVid=@SourceRowId and AutoID!=@id
            SET @SurplusApply=(SELECT iRAmount_f FROM AP_V_PUBillApply WHERE id=(SELECT Inserted.iBVid FROM Inserted))
        end
    set @SumApply=(select ISNULL(@SumApply,0));
    --set @SurplusApply=@ZSumApply-@SumApply;
    update AP_ApplyPayVouchs set cDefine26=@SumApply,cDefine27=@SurplusApply,cDefine30=@AgreementName,cDefine29=@AgreementCode where AutoID=@id
    -- Insert statements for trigger here

END