USP_GETGIFTAIDSPLITSTOCACHE

            create procedure USP_GETGIFTAIDSPLITSTOCACHE
                (@REVENUEID uniqueidentifier,
                @ORGANIZATIONCURRENCYID uniqueidentifier,
                @DELETEDIDSXML xml,
                @DELETEDANDCHANGEDSPLITSINFOXML xml output)
            as
                declare  @DELETEDANDCHANGEDSPLITSINFO table
                    (REVENUESPLITID uniqueidentifier,
                    CONSTITUENTID uniqueidentifier,
                    TAXCLAIMNUMBER nvarchar(10),
                    CHARITYCLAIMREFERENCENUMBER nvarchar(20),
                    GIFTDATE datetime,
                    GIFTAMOUNT money,
                    BASETAXCLAIMAMOUNT money, 
                    TRANSITIONALTAXCLAIMAMOUNT money, 
                    INCLUDETRANSITIONALAMOUNTCODE tinyint, 
                    CLAIMEDASSPONSORSHIP bit,
                    TRANSACTIONBASETAXCLAIMAMOUNT money,
                    TRANSACTIONCURRENCYID uniqueidentifier,
                    TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT money,
                    ORGANIZATIONBASETAXCLAIMAMOUNT money,
                    ORGANIZATIONEXCHANGERATEID uniqueidentifier,
                    ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT money,
                    BASECURRENCYID uniqueidentifier,
                    BASEEXCHANGERATEID uniqueidentifier,
                    TRANSACTIONGIFTAMOUNT money,
                    ORGANIZATIONGIFTAMOUNT money,
                    ISSPONSORSHIP bit,
                    ISDELETED bit,
                    BASERATE numeric(30,6),
                    TRANSITIONALRATE numeric(30,6),
                    AMOUNTTOSUM money)

                --Gift Aid is for UK only

                declare @PRODUCTISUK bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D');
                declare @GBPCURRENCYID uniqueidentifier = (select CURRENCY.ID from dbo.CURRENCY where CURRENCY.ISO4217 = 'GBP');

                if @PRODUCTISUK = 1
        begin

                    declare @DELETEDIDS table (ID uniqueidentifier);
                    insert into @DELETEDIDS(ID)
                         select T.c.value('(ID)[1]', 'uniqueidentifier') as ID
                         from @DELETEDIDSXML.nodes('/DELETEDIDS/ITEM') T(c);

                    --We need to cache the deleted/changed split information in order potentially refund it later in this SP after the splits have been

                    --actually deleted or updated. We have to wait until after the new splits are created (to calculate CCRN) in order determine if the deleted/changed splits should be refunded.


                    insert into @DELETEDANDCHANGEDSPLITSINFO
                        select
                            FINANCIALTRANSACTIONLINEITEM.ID,
                            FINANCIALTRANSACTION.CONSTITUENTID,
                            REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
                            REVENUESPLITGIFTAID.CHARITYCLAIMREFERENCENUMBER,
                            cast(FINANCIALTRANSACTION.DATE as datetime) as DATE,
                            FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
                            REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT,
                            REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE,
                    REVENUESPLITGIFTAID.CLAIMEDASSPONSORSHIP,
                    REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.TRANSACTIONCURRENCYID,
                    REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.ORGANIZATIONEXCHANGERATEID,
                    REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
                    REVENUESPLITGIFTAID.BASECURRENCYID,
                    REVENUESPLITGIFTAID.BASEEXCHANGERATEID,
                    FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
                    FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT,
                    REVENUESPLITGIFTAID.ISSPONSORSHIP,
                    1,
                    REVENUESPLITGIFTAID.BASERATE,
                    REVENUESPLITGIFTAID.TRANSITIONALRATE,
                    case when @ORGANIZATIONCURRENCYID = @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT
                      when FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                      when REVENUESPLITGIFTAID.BASECURRENCYID =  @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
                      else 0
                    end as AMOUNTTOSUM
                from @DELETEDIDS DELETEDIDS
                  inner join dbo.FINANCIALTRANSACTIONLINEITEM on DELETEDIDS.ID = FINANCIALTRANSACTIONLINEITEM.ID
                  inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                  inner join dbo.REVENUESPLITGIFTAID on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLITGIFTAID.ID
                  inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                where
                  coalesce(REVENUESPLITGIFTAID.TAXCLAIMNUMBER, '') <> ''
                  and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                  and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                  and FINANCIALTRANSACTION.DELETEDON is null;

              insert into @DELETEDANDCHANGEDSPLITSINFO
                select
                      FINANCIALTRANSACTIONLINEITEM.ID,
                      FINANCIALTRANSACTION.CONSTITUENTID,
                      REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
                      REVENUESPLITGIFTAID.CHARITYCLAIMREFERENCENUMBER,
                      cast(FINANCIALTRANSACTION.DATE as datetime) as DATE,
                      FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT,
                      REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT,
                      REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT,
                      REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE,
                      REVENUESPLITGIFTAID.CLAIMEDASSPONSORSHIP,
                      REVENUESPLITGIFTAID.TRANSACTIONBASETAXCLAIMAMOUNT,
                      REVENUESPLITGIFTAID.TRANSACTIONCURRENCYID,
                      REVENUESPLITGIFTAID.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
                      REVENUESPLITGIFTAID.ORGANIZATIONBASETAXCLAIMAMOUNT,
                      REVENUESPLITGIFTAID.ORGANIZATIONEXCHANGERATEID,
                      REVENUESPLITGIFTAID.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
                      REVENUESPLITGIFTAID.BASECURRENCYID,
                      REVENUESPLITGIFTAID.BASEEXCHANGERATEID,
                      FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT,
                      FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT,
                      REVENUESPLITGIFTAID.ISSPONSORSHIP,
                      0,
                      REVENUESPLITGIFTAID.BASERATE,
                      REVENUESPLITGIFTAID.TRANSITIONALRATE,
                    case when @ORGANIZATIONCURRENCYID = @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT
                      when FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
                      when REVENUESPLITGIFTAID.BASECURRENCYID =  @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
                      else 0
                    end as AMOUNTTOSUM
              from dbo.FINANCIALTRANSACTIONLINEITEM 
                inner join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
                  and FINANCIALTRANSACTIONLINEITEM.ID not in (select REVENUESPLITID from @DELETEDANDCHANGEDSPLITSINFO)
                  and coalesce(REVENUESPLITGIFTAID.TAXCLAIMNUMBER, '') <> ''
                  and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                  and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                  and FINANCIALTRANSACTION.DELETEDON is null;

          set @DELETEDANDCHANGEDSPLITSINFOXML = (    select
                    REVENUESPLITID,
                    CONSTITUENTID,
                    TAXCLAIMNUMBER,
                    CHARITYCLAIMREFERENCENUMBER,
                    GIFTDATE,
                    GIFTAMOUNT,
                    BASETAXCLAIMAMOUNT, 
                    TRANSITIONALTAXCLAIMAMOUNT, 
                    INCLUDETRANSITIONALAMOUNTCODE, 
                    CLAIMEDASSPONSORSHIP,
                    TRANSACTIONBASETAXCLAIMAMOUNT,
                    TRANSACTIONCURRENCYID,
                    TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
                    ORGANIZATIONBASETAXCLAIMAMOUNT,
                    ORGANIZATIONEXCHANGERATEID,
                    ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
                    BASECURRENCYID,
                    BASEEXCHANGERATEID,
                    TRANSACTIONGIFTAMOUNT,
                    ORGANIZATIONGIFTAMOUNT,
                    ISSPONSORSHIP,
                    ISDELETED,
                    BASERATE,
                    TRANSITIONALRATE,
                    AMOUNTTOSUM 
                from @DELETEDANDCHANGEDSPLITSINFO 
                for xml raw('ITEM'),type,elements,root('DELETEDANDCHANGEDSPLITSINFO'),BINARY BASE64);

       end;