USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS

CREATE procedure USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS
(
    @ID uniqueidentifier,
    @APPEALID uniqueidentifier,
    @PAYMENTMETHODCODE tinyint,
    @CREDITTYPECODEID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier,
    @DATE datetime,
    @TRANSACTIONTYPECODE tinyint,
    @SPLITSDECLININGGIFTAID xml = null,
    @COVENANTGIFTSPLITS xml = null,
    @GIFTAIDSPONSORSHIPSPLITS xml = null,
    @SPLITSTOTRANSFERGIFTAID xml = null
)
as
begin
    set nocount on;

    -- Only create the records if this is a UK product

    if dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D') = 1
    begin
        declare @TAXCLAIMAMOUNT numeric(30,6);
        declare @DISQUALIFIEDBYATTRIBUTES bit = 1; --This is necessary for when we add a new split record, as there should not be any attributes to disqualify it


        set @TAXCLAIMAMOUNT = 0;

        declare @SPLITID uniqueidentifier;
        declare @REVENUEID uniqueidentifier;
        declare @DESIGNATIONID uniqueidentifier;
        declare @AMOUNT money;
        declare @TRANSACTIONAMOUNT money;
        declare @TYPECODE tinyint;
        declare @APPLICATIONCODE tinyint;
        declare @BASECURRENCYID uniqueidentifier;
        declare @TRANSACTIONCURRENCYID uniqueidentifier;
        declare @BASEEXCHANGERATEID uniqueidentifier;
        declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;

        declare @CONSTITUENTID uniqueidentifier;
        select @CONSTITUENTID = CONSTITUENTID from dbo.REVENUE where ID = @ID;

        declare @SPLITSDECLININGGIFTAIDTBL table(
            REVENUESPLITID uniqueidentifier
        )

        if @SPLITSDECLININGGIFTAID is not null
            insert into @SPLITSDECLININGGIFTAIDTBL (REVENUESPLITID)
            select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier')
            from @SPLITSDECLININGGIFTAID.nodes('/SPLITSDECLININGGIFTAID/ITEM') T(c)

        declare @COVENANTGIFTSPLITSTBL table(
            REVENUESPLITID uniqueidentifier
        )

        if @COVENANTGIFTSPLITS is not null
            insert into @COVENANTGIFTSPLITSTBL (REVENUESPLITID)
            select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier')
            from @COVENANTGIFTSPLITS.nodes('/COVENANTGIFTSPLITS/ITEM') T(c)

        declare @GIFTAIDSPONSORSHIPSPLITSTBL table(
            REVENUESPLITID uniqueidentifier
        )

        if @GIFTAIDSPONSORSHIPSPLITS is not null
            insert into @GIFTAIDSPONSORSHIPSPLITSTBL (REVENUESPLITID)
            select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier')
            from @GIFTAIDSPONSORSHIPSPLITS.nodes('/GIFTAIDSPONSORSHIPSPLITS/ITEM') T(c)


             declare @SPLITSTOTRANSFERGIFTAIDTBL table ( REVENUESPLITID uniqueidentifier,
                                                                                                     TAXCLAIMNUMBER nvarchar(10),
                                                                                                     CHARITYCLAIMREFERENCENUMBER nvarchar(20),
                                                                                                     BASERATE numeric(30,6),
                                                                                                     TRANSITIONALRATE numeric(30,6)
                                                                                                    )
        if @SPLITSTOTRANSFERGIFTAID is not null
            insert into @SPLITSTOTRANSFERGIFTAIDTBL (REVENUESPLITID,TAXCLAIMNUMBER,CHARITYCLAIMREFERENCENUMBER,BASERATE,TRANSITIONALRATE)
            select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier'),
                         T.c.value('(TAXCLAIMNUMBER)[1]','nvarchar(10)'),
                         T.c.value('(CHARITYCLAIMREFERENCENUMBER)[1]','nvarchar(20)'),
                         T.c.value('(BASERATE)[1]','numeric(30,6)'),
                         T.c.value('(TRANSITIONALRATE)[1]','numeric(30,6)')
            from @SPLITSTOTRANSFERGIFTAID.nodes('/SPLITSTOTRANSFERGIFTAID/ITEM') T(c);



        declare @TAXCLAIMNUMBER nvarchar(10);
        declare @CHARITYCLAIMREFERENCENUMBER nvarchar(20);
        declare @BASERATE numeric(30,6);
        declare @TRANSITIONALRATE numeric(30,6);

        declare SPLITSCURSOR cursor local fast_forward for 
            select
                FINANCIALTRANSACTIONLINEITEM.ID,
                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                REVENUESPLIT_EXT.DESIGNATIONID,
                FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
                FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
                REVENUESPLIT_EXT.TYPECODE,
                REVENUESPLIT_EXT.APPLICATIONCODE,
                isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, CURRENCYSET.BASECURRENCYID),
                FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                FINANCIALTRANSACTION.BASEEXCHANGERATEID, 
                FINANCIALTRANSACTION.ORGEXCHANGERATEID    
            from dbo.FINANCIALTRANSACTIONLINEITEM
            inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
            inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
            inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
            inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
            where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @ID
            and FINANCIALTRANSACTION.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
            and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1

        open SPLITSCURSOR;

        fetch next from SPLITSCURSOR into @SPLITID, @REVENUEID, @DESIGNATIONID, @AMOUNT, @TRANSACTIONAMOUNT, @TYPECODE, @APPLICATIONCODE, @BASECURRENCYID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID

        while @@FETCH_STATUS = 0
        begin

        set @TAXCLAIMNUMBER  = null;
        set @CHARITYCLAIMREFERENCENUMBER = null;
        set @BASERATE = null;
        set @TRANSITIONALRATE = null;

            declare @DECLINESGIFTAID bit;
            if exists (select 1 from @SPLITSDECLININGGIFTAIDTBL where REVENUESPLITID = @SPLITID)
                set @DECLINESGIFTAID = 1;
            else
                set @DECLINESGIFTAID = 0;

            declare @ISCOVENANT bit;
            if exists (select 1 from @COVENANTGIFTSPLITSTBL where REVENUESPLITID = @SPLITID)
                set @ISCOVENANT = 1;
            else
                set @ISCOVENANT = null;

            declare @ISSPONSORSHIP bit;
            if exists (select 1 from @GIFTAIDSPONSORSHIPSPLITSTBL where REVENUESPLITID = @SPLITID)
                set @ISSPONSORSHIP = 1;
            else
                set @ISSPONSORSHIP = 0;

                select @TAXCLAIMNUMBER = SPLITSTOTRANSFERGIFTAIDTBL.TAXCLAIMNUMBER,
                             @CHARITYCLAIMREFERENCENUMBER = SPLITSTOTRANSFERGIFTAIDTBL.CHARITYCLAIMREFERENCENUMBER,
                             @BASERATE = SPLITSTOTRANSFERGIFTAIDTBL.BASERATE,
                             @TRANSITIONALRATE =  SPLITSTOTRANSFERGIFTAIDTBL.TRANSITIONALRATE
                from @SPLITSTOTRANSFERGIFTAIDTBL SPLITSTOTRANSFERGIFTAIDTBL
                where @SPLITID = SPLITSTOTRANSFERGIFTAIDTBL.REVENUESPLITID;

            exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLIT 
                    @REVENUEID = @ID,
                    @TRANSACTIONTYPECODE = @TRANSACTIONTYPECODE,
                    @CONSTITUENTID = @CONSTITUENTID,
                    @DATE = @DATE,
                    @SPLITID = @SPLITID,
                    @DESIGNATIONID = @DESIGNATIONID,
                    @AMOUNT = @AMOUNT,
                    @TRANSACTIONAMOUNT = @TRANSACTIONAMOUNT,
                    @DECLINESGIFTAID = @DECLINESGIFTAID,
                    @APPLICATIONCODE = @APPLICATIONCODE,
                    @TYPECODE = @TYPECODE,
                    @DISQUALIFIEDBYATTRIBUTES = @DISQUALIFIEDBYATTRIBUTES,
                    @CHANGEAGENTID = @CHANGEAGENTID,
                    @ISCOVENANT = @ISCOVENANT,
                    @ISSPONSORSHIP = @ISSPONSORSHIP,
                    @BASECURRENCYID = @BASECURRENCYID,
                    @TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
                    @BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                    @ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID,
                    @TAXCLAIMNUMBER = @TAXCLAIMNUMBER,
                    @CHARITYCLAIMREFERENCENUMBER = @CHARITYCLAIMREFERENCENUMBER,
                    @BASERATE = @BASERATE,
                    @TRANSITIONALRATE = @TRANSITIONALRATE;

            --reset DISQVAR to its initial value

            fetch next from SPLITSCURSOR into @SPLITID, @REVENUEID, @DESIGNATIONID, @AMOUNT, @TRANSACTIONAMOUNT, @TYPECODE, @APPLICATIONCODE, @BASECURRENCYID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID, @ORGANIZATIONEXCHANGERATEID;
        end        -- end while fetch


        close SPLITSCURSOR;
        deallocate SPLITSCURSOR;

    --If this is for a payment record was created from a pledge or recurring gift that has an attribute on it, that attribute will be carried over to the

    --  payment record. Those attributes could potentially be disqualified, so call function to check if they are and update the REVENUESPLITGIFTAID records

    --  as needed.

    exec dbo.USP_REVENUE_DISQUALIFIED_GIFT_AID_ATTRIBUTE_2 @ID;
    end
end