USP_PLEDGE_UPDATEINSTALLMENT3

CREATE procedure dbo.USP_PLEDGE_UPDATEINSTALLMENT3 (
    @PLEDGEID uniqueidentifier
    ,@INSTALLMENTS xml
    ,@CHANGEAGENTID uniqueidentifier
    ,@CURRENTDATE datetime
    ,@ADJPAYMENT_DATE datetime = null
    ,@ADJPAYMENT_POSTDATE datetime = null
    ,@ADJPAYMENT_REASONCODEID uniqueidentifier = null
    ,@ADJPAYMENT_DETAILS nvarchar(255) = null
    ,@BASECURRENCYID uniqueidentifier = null
    ,@ORGANIZATIONEXCHANGERATEID uniqueidentifier = null
    ,@TRANSACTIONCURRENCYID uniqueidentifier = null
    ,@BASEEXCHANGERATEID uniqueidentifier = null
  ,@SPLITS xml = null
    )
as
begin
    set nocount on;

    --what this routine will actually do:

    --1.  caches the payment and writeoff applications for the revenueid specified

    --2.  if adjustment info is passed in, it will

    --    iterate over each of these payment applications and

    --    call USP_SAVE_ADJUSTMENT for each posted application

    --3.  it will attempt to delete/mark deleted the payment applications

    --    which are no longer in the new installment splits - this is necessary

    --    in order to actually make the changes to the installment tables since

    --    there are database constraints in play that will prevent installments

    --    or installment splits from being deleted.  In addition to the deletion/mark deleted

    --    of the payment applications, it will also attempt to manually perform 'cascade deletes'

    --    on many foreign tables.

    --4.  adjustment history and GL stuff is done based on the cached payment applications

    --5.  the installments and installment splits that were passed in will

    --    get saved to the DB

    --6.  now it will iterate of the cached payment application info and re-add the payments

    --    presuming this will re-distribute them properly.  In the case of UK, it will also

    --    do some additional UK processing

    --7.  finally it will iterate over the adjustments it created in step 2 and save the history

    declare @PROCESSADJUSTMENTS bit = 1

    if @ADJPAYMENT_DATE is null
        and @ADJPAYMENT_POSTDATE is null
        and @ADJPAYMENT_REASONCODEID is null
    begin
        set @PROCESSADJUSTMENTS = 0
    end

    declare @INSTALLMENTSPLITS xml;

    if @CHANGEAGENTID is null
        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

    if @CURRENTDATE is null
        set @CURRENTDATE = GetDate();

    --We need to make sure all of the installments have an ID so the child objects will update properly                    

    declare @NewInstallmentsWithSplits table (
        [ID] uniqueidentifier
        ,[DATE] datetime
        ,[AMOUNT] money
        ,[RECEIPTAMOUNT] money
        ,[BALANCE] money
        ,[APPLIED] money
        ,[SEQUENCE] int
        ,[SPLITID] uniqueidentifier
        ,[SPLITDESIGNATIONID] uniqueidentifier
        ,[SPLITAMOUNT] money
        ,[REVENUESPLITID] uniqueidentifier
    ,[DECLINESGIFTAID] bit
        );

    insert into @NewInstallmentsWithSplits
    select T1.c.value('(ID)[1]', 'uniqueidentifier') as 'ID'
        ,T1.c.value('(DATE)[1]', 'datetime') as 'DATE'
        ,T1.c.value('(AMOUNT)[1]', 'money') as 'AMOUNT'
        ,T1.c.value('(RECEIPTAMOUNT)[1]', 'money') as 'RECEIPTAMOUNT'
        ,T1.c.value('(BALANCE)[1]', 'money') as 'BALANCE'
        ,T1.c.value('(APPLIED)[1]', 'money') as 'APPLIED'
        ,T1.c.value('(SEQUENCE)[1]', 'int') as 'SEQUENCE'
        ,T2.split.value('(ID)[1]', 'nvarchar(50)') as 'SPLITID'
        ,T2.split.value('(DESIGNATIONID)[1]', 'uniqueidentifier') as 'SPLITDESIGNATIONID'
        ,T2.split.value('(AMOUNT)[1]', 'money') as 'SPLITAMOUNT'
        ,T2.split.value('(REVENUESPLITID)[1]', 'uniqueidentifier') as 'REVENUESPLITID'
         ,T2.split.value('(DECLINESGIFTAID)[1]', 'bit') as 'DECLINESGIFTAID'
    from @INSTALLMENTS.nodes('/INSTALLMENTS/ITEM') T1(c)
    cross apply T1.c.nodes('./INSTALLMENTSPLITS/ITEM') as T2(split);

  --only need to handle gift aid declines for uk

    --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 @SPLITSTBL table
       (ID uniqueidentifier
      ,[DESIGNATIONID] uniqueidentifier
      ,[DECLINESGIFTAID] bit);

      --If @SPLITS information has been included, it is assumed that this should be used to set the declines gift aid value for the installment splits.

      if @SPLITS is not null 
      begin

          insert into @SPLITSTBL
          select SPLITS.c.value('(ID)[1]', 'uniqueidentifier') as 'ID'
              ,SPLITS.c.value('(DESIGNATIONID)[1]', 'uniqueidentifier') as 'DESIGNATIONID'
              ,SPLITS.c.value('(DECLINESGIFTAID)[1]', 'bit') as 'DECLINESGIFTAID'
            from @SPLITS.nodes('/SPLITS/ITEM') SPLITS(c);


        --update @NewInstallmentsWithSplits to have the declines gift aid values from the splits

          update @NewInstallmentsWithSplits set DECLINESGIFTAID= SPLITS.DECLINESGIFTAID
          from @NewInstallmentsWithSplits INSTALLMENTSPLITS
            inner join @SPLITSTBL SPLITS on INSTALLMENTSPLITS.REVENUESPLITID=SPLITS.ID; 
    end
  end

    declare @TempSeqTbl table (
        [ID] uniqueidentifier
        ,[SEQUENCE] int
        );

    insert into @TempSeqTbl
    select newid() ID
        ,MISSINGID.SEQUENCE
    from (
        select distinct SEQUENCE
        from @NewInstallmentsWithSplits
        where id is null
        ) MISSINGID

    -- ** Work for PBI 40790 **

    declare @REVENUEID uniqueidentifier
    declare @DEPOSITID uniqueidentifier
    declare @WRITEOFFID uniqueidentifier;
    declare @APPLIEDAMOUNT money
    declare @DONOTPOST bit
    declare @REVENUEDATE datetime
    declare @UNAPPLIEDMATCHINGGIFTSPLITS xml
    declare @CREATEDSPLITS xml
    declare @OVERPAYMENTAPPLICATIONTYPECODE tinyint
    declare @PAYMENTCONSTITUENTID uniqueidentifier
    declare @REVENUEAPPEALID uniqueidentifier
    declare @REVENUEPAYMENTMETHODCODE tinyint
    declare @REVENUECREDITTYPECODEID uniqueidentifier
    declare @APPLICATIONTYPE tinyint
    declare @AMOUNTPAID money
    declare @ADJUSTMENTID uniqueidentifier
    declare @GIFTFEEADJUSTMENTID uniqueidentifier
    declare @ADJ_POSTSTATUS tinyint
    declare @ADJUSTED bit
    declare @ISPAYMENT bit;
    declare @POSTED bit;
    declare @ISDELETED bit;
    declare @PAYMENTADJUSTMENT bit;

-- Save the IDs for any payments that need to be updated. --

    -- Replaced Installment Split amount, with Payment amount, because we need the amount in the payment currency. --

    declare @TempRevenue table (
        REVENUEID uniqueidentifier
        ,RSPLITID uniqueidentifier
        ,DEPOSITID uniqueidentifier
        ,PAYMENTAMOUNT money
        ,REVENUEDATE datetime
        ,POSTED bit
        ,DONOTPOST bit
        ,ADJUSTED bit
        ,OVERPAYMENTAPPLICATIONTYPECODE tinyint
        ,CONSTITUENTID uniqueidentifier
        ,APPEALID uniqueidentifier
        ,PAYMENTMETHODCODE tinyint
        ,REVENUEDATEADDED datetime
        ,EXISTINGDESIGNATIONID bit
        ,ISPAYMENT bit
        ,INSTALLMENTSPLITTODELETEID uniqueidentifier
        ,SEQUENCENO tinyint
        ,DELETEDANDCHANGEDSPLITSINFOXML xml
        ,DECLINESGIFTAID bit
        ,ISDELETED bit
        );
    declare @RevenueAdjustmentHistoryMapping table (
        REVENUEID uniqueidentifier
        ,ADJUSTMENTID uniqueidentifier
        ,ISPAYMENT bit
        );

    --add info from the payment line items that will be impacted 

    insert into @TempRevenue (
        REVENUEID
        ,RSPLITID
        ,DEPOSITID
        ,PAYMENTAMOUNT
        ,REVENUEDATE
        ,POSTED
        ,DONOTPOST
        ,ADJUSTED
        ,OVERPAYMENTAPPLICATIONTYPECODE
        ,CONSTITUENTID
        ,APPEALID
        ,PAYMENTMETHODCODE
        ,REVENUEDATEADDED
        ,EXISTINGDESIGNATIONID
        ,ISPAYMENT
        ,SEQUENCENO
        ,DECLINESGIFTAID
        ,ISDELETED
        )
    select 
        --distinct: Removing distinct. Payments for multiple installments for the same amount were being excluded. 

        --These installment amounts are needed when rebuilding the payment (summation on PAYMENTAMOUNT later on in this sproc)

        --Multiple rows were already being placed in this table variable for payment/splits when payments covered multiple installments of different amounts, so this shouldn't introduce new bugs

        --Distinct probably isn't the most efficient method for excluding redundant information here. This could probably use a good refactoring.

         FTLI.FINANCIALTRANSACTIONID REVENUEID
        ,FTLI.ID RSPLITID
        ,BANKACCOUNTDEPOSITPAYMENT.DEPOSITID DEPOSITID
        ,SUM(case when INSTALLMENTSPLITPAYMENT.APPLICATIONEXCHANGERATEID is not null then dbo.UFN_CURRENCY_CONVERTINVERSE(INSTALLMENTSPLITPAYMENT.AMOUNT, INSTALLMENTSPLITPAYMENT.APPLICATIONEXCHANGERATEID) else INSTALLMENTSPLITPAYMENT.AMOUNT end) PAYMENTAMOUNT
        ,cast(FT.[DATE] as datetime) REVENUEDATE
        ,case 
            when (FT.POSTSTATUSCODE = 2)
                and (
                    FT.TYPECODE in (
                        0
                        ,1
                        ,2
                        ,3
                        ,4
                        ,5
                        ,6
                        ,7
                        ,8
                        ,9
                        )
                    )
                and (FT.DELETEDON is null)
                then 1
            else 0
            end POSTED
        ,case FT.POSTSTATUSCODE
            when 3
                then 1
            else 0
            end DONOTPOST
        ,case 
            when exists (
                    select 1
                    from dbo.ADJUSTMENT
                    where ADJUSTMENT.REVENUEID = FTLI.FINANCIALTRANSACTIONID
                        and POSTSTATUSCODE = 1
                    )
                then 1
            else 0
            end ADJUSTED
        ,INSTALLMENTSPLITPAYMENT.OVERPAYMENTAPPLICATIONTYPECODE OVERPAYMENTAPPLICATIONTYPECODE
        ,FT.CONSTITUENTID CONSTITUENTID
        ,REVENUE_EXT.APPEALID APPEALID
        ,REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE PAYMENTMETHODCODE
        ,FT.DATEADDED REVENUEDATEADDED
        ,case 
            when INSTALLMENTSPLIT.DESIGNATIONID = NEWINSTALLMENTSPLITS.SPLITDESIGNATIONID
                then 1
            else 0
            end EXISTINGDESIGNATIONID
        ,1 ISPAYMENT
        ,INSTALLMENT.SEQUENCE
        ,NEWINSTALLMENTSPLITS.DECLINESGIFTAID
        ,1
    from dbo.INSTALLMENT
    inner join dbo.INSTALLMENTSPLIT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
    inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on INSTALLMENTSPLITPAYMENT.PAYMENTID = FTLI.ID
    inner join dbo.FINANCIALTRANSACTION FT on FTLI.FINANCIALTRANSACTIONID = FT.ID
    inner join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
    inner join dbo.REVENUEPAYMENTMETHOD on FT.ID = REVENUEPAYMENTMETHOD.REVENUEID
    left join dbo.BANKACCOUNTDEPOSITPAYMENT on FTLI.FINANCIALTRANSACTIONID = BANKACCOUNTDEPOSITPAYMENT.ID
    left join @NewInstallmentsWithSplits as NEWINSTALLMENTSPLITS on INSTALLMENTSPLIT.ID = NEWINSTALLMENTSPLITS.SPLITID
    where INSTALLMENT.REVENUEID = @PLEDGEID --and

        --(

        --  INSTALLMENTSPLIT.AMOUNT <> isnull(NEWINSTALLMENTSPLITS.SPLITAMOUNT, 0) or

        --  INSTALLMENTSPLIT.DESIGNATIONID <> IsNull(NEWINSTALLMENTSPLITS.SPLITDESIGNATIONID, '00000000-0000-0000-0000-000000000000')

        --)

    group by
     FTLI.FINANCIALTRANSACTIONID
     ,FTLI.ID
     ,BANKACCOUNTDEPOSITPAYMENT.DEPOSITID
     ,FT.[DATE]
     ,FT.POSTSTATUSCODE
     ,FT.TYPECODE
     ,FT.DELETEDON
     ,INSTALLMENTSPLITPAYMENT.OVERPAYMENTAPPLICATIONTYPECODE
     ,FT.CONSTITUENTID
     ,REVENUE_EXT.APPEALID
     ,REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE 
     ,FT.DATEADDED
     ,INSTALLMENTSPLIT.DESIGNATIONID
     ,NEWINSTALLMENTSPLITS.SPLITDESIGNATIONID
     ,INSTALLMENT.SEQUENCE
   ,NEWINSTALLMENTSPLITS.DECLINESGIFTAID

    --add info from the write off line items that will be impacted 

    insert into @TempRevenue (
        REVENUEID
        ,RSPLITID
        ,PAYMENTAMOUNT
        ,REVENUEDATE
        ,POSTED
        ,DONOTPOST
        ,ADJUSTED
        ,CONSTITUENTID
        ,APPEALID
        ,REVENUEDATEADDED
        ,EXISTINGDESIGNATIONID
        ,ISPAYMENT
        ,DECLINESGIFTAID
        ,ISDELETED
        )
    select distinct WOLI.FINANCIALTRANSACTIONID REVENUEID
        ,WOLI.ID RSPLITID
        ,SUM(WOLI.TRANSACTIONAMOUNT) PAYMENTAMOUNT
        ,cast(FT.[DATE] as datetime) REVENUEDATE
        ,case 
            when (FT.POSTSTATUSCODE = 2)
                and (
                    FT.TYPECODE in (
                        0
                        ,1
                        ,2
                        ,3
                        ,4
                        ,5
                        ,6
                        ,7
                        ,8
                        ,9
                        ,20
                        )
                    )
                and (FT.DELETEDON is null)
                then 1
            else 0
            end POSTED
        ,case FT.POSTSTATUSCODE
            when 3
                then 1
            else 0
            end DONOTPOST
        ,case 
            when exists (
                    select 1
                    from dbo.ADJUSTMENT
                    where ADJUSTMENT.REVENUEID = WOLI.FINANCIALTRANSACTIONID
                        and POSTSTATUSCODE = 1
                    )
                then 1
            else 0
            end ADJUSTED
        ,FT.CONSTITUENTID CONSTITUENTID
        ,REVENUE_EXT.APPEALID APPEALID
        ,FT.DATEADDED REVENUEDATEADDED
        ,case 
            when ISPLIT.DESIGNATIONID = NEWINSTALLMENTSPLITS.SPLITDESIGNATIONID
                then 1
            else 0
            end EXISTINGDESIGNATIONID
        ,0 ISPAYMENT
        ,NEWINSTALLMENTSPLITS.DECLINESGIFTAID
        ,1
    from dbo.INSTALLMENT I
    inner join dbo.INSTALLMENTSPLIT ISPLIT on I.ID = ISPLIT.INSTALLMENTID
    inner join dbo.INSTALLMENTSPLITWRITEOFF ISPWO on ISPWO.INSTALLMENTSPLITID = ISPLIT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM WOLI on ISPWO.WRITEOFFID = WOLI.FINANCIALTRANSACTIONID
    inner join dbo.FINANCIALTRANSACTION FT on WOLI.FINANCIALTRANSACTIONID = FT.ID
    left join dbo.REVENUE_EXT on FT.ID = REVENUE_EXT.ID
    left join @NewInstallmentsWithSplits as NEWINSTALLMENTSPLITS on ISPLIT.ID = NEWINSTALLMENTSPLITS.SPLITID
    where I.REVENUEID = @PLEDGEID
        and WOLI.DELETEDON is null
        and (
            ISPLIT.REVENUESPLITID = WOLI.SOURCELINEITEMID
            or exists (
                select 1
                from dbo.FINANCIALTRANSACTIONLINEITEM NEW
                where NEW.ID = WOLI.SOURCELINEITEMID
                    and NEW.REVERSEDLINEITEMID = ISPLIT.REVENUESPLITID
                )
            )
    group by
     WOLI.FINANCIALTRANSACTIONID
     ,WOLI.ID
     ,WOLI.TRANSACTIONAMOUNT
     ,FT.[DATE]
     ,FT.POSTSTATUSCODE
     ,FT.TYPECODE
     ,FT.DELETEDON
     ,FT.CONSTITUENTID
     ,REVENUE_EXT.APPEALID
     ,FT.DATEADDED
     ,ISPLIT.DESIGNATIONID
     ,NEWINSTALLMENTSPLITS.SPLITDESIGNATIONID
   ,NEWINSTALLMENTSPLITS.DECLINESGIFTAID

    --cache the current Write-off info

    declare @WriteOffInstallmentAmounts table (
        WRITEOFFID uniqueidentifier
        ,INSTALLMENTID uniqueidentifier
        ,WRITEOFFAMOUNT money
        )

    --cache the current Gift Fees info

    declare @GiftFeeAdjustments table (
        REVENUEID uniqueidentifier
        ,GIFTFEEADJUSTMENTID uniqueidentifier
    )

    insert into @WriteOffInstallmentAmounts (
        WRITEOFFID
        ,INSTALLMENTID
        ,WRITEOFFAMOUNT
        )
    select INSTALLMENTSPLITWRITEOFF.WRITEOFFID
        ,INSTALLMENTSPLIT.INSTALLMENTID
        ,sum(INSTALLMENTSPLITWRITEOFF.TRANSACTIONAMOUNT)
    from dbo.INSTALLMENTSPLIT
    inner join dbo.INSTALLMENTSPLITWRITEOFF on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID
    where INSTALLMENTSPLIT.PLEDGEID = @PLEDGEID
    group by INSTALLMENTSPLITWRITEOFF.WRITEOFFID
        ,INSTALLMENTSPLIT.INSTALLMENTID


    --Now cache the gift aid stuff for later too.

  --Gift Aid is for UK only

    if @PRODUCTISUK = 1
    begin
        declare @DELETEDANDCHANGEDSPLITSINFOXML xml ;

        declare POSTEDCURSOR cursor local fast_forward
            for
            select distinct REVENUEID
            from @TempRevenue
            where ISPAYMENT = 1;

            open POSTEDCURSOR;

            fetch next
            from POSTEDCURSOR
            into @REVENUEID;

                while (@@FETCH_STATUS = 0)
                begin
                    exec USP_GETGIFTAIDSPLITSTOCACHE @REVENUEID, @BASECURRENCYID, null, @DELETEDANDCHANGEDSPLITSINFOXML output;

                    update @TempRevenue set DELETEDANDCHANGEDSPLITSINFOXML=@DELETEDANDCHANGEDSPLITSINFOXML where REVENUEID=@REVENUEID;

                    fetch next
                    from POSTEDCURSOR
                    into @REVENUEID;
            end

        close POSTEDCURSOR;

        deallocate POSTEDCURSOR;

    end

    -- Bug 122814

-- Remove posted transactions from update.

  if @PROCESSADJUSTMENTS <> 1
    begin
        delete
        from @TempRevenue
        where POSTED = 1;
    end

        -- Create adjustments for Posted Payments.  But do not create unnecessary payment reversals

        -- Check if any change is made in pledge amount or designation which should reset the payment split.

        declare PAYMENTCURSOR cursor local fast_forward
        for
        select distinct REVENUEID
            ,ADJUSTED,POSTED
        from @TempRevenue 
        where ISPAYMENT = 1;
        open PAYMENTCURSOR;

        fetch next
        from PAYMENTCURSOR
        into @REVENUEID
            ,@ADJUSTED,@POSTED;

        while (@@FETCH_STATUS = 0)
        begin
            set @ADJUSTMENTID = null
            set @GIFTFEEADJUSTMENTID = null
            set @ADJ_POSTSTATUS = 1 --< Revisit do we need to let User set this?


            declare @OldImpactedInstallmentSplitsForCurrentPayment table (
                [INSTALLMENTSPLITDESIGNATIONID] nvarchar(73)
                ,[DESIGNATIONID] uniqueidentifier
                ,[AMOUNT] money
                )
            declare @NewImpactedInstallmentSplitsForCurrentPayment table (
                [INSTALLMENTSPLITDESIGNATIONID] nvarchar(73)
                ,[DESIGNATIONID] uniqueidentifier
                ,[AMOUNT] money
                )

            delete @OldImpactedInstallmentSplitsForCurrentPayment

            insert into @OldImpactedInstallmentSplitsForCurrentPayment (
                [INSTALLMENTSPLITDESIGNATIONID]
                ,[DESIGNATIONID]
                ,[AMOUNT]
                )
            select CAST(INSTALLMENTSPLIT.ID as nvarchar(36)) + CAST(INSTALLMENTSPLIT.DESIGNATIONID as nvarchar(36)) [INSTALLMENTSPLITDESIGNATIONID]
                ,INSTALLMENTSPLIT.DESIGNATIONID [DESIGNATIONID]
                ,(INSTALLMENTSPLIT.TRANSACTIONAMOUNT) [AMOUNT]
            from dbo.FINANCIALTRANSACTIONLINEITEM LI
            inner join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = LI.ID
            inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = ISP.INSTALLMENTSPLITID
            inner join dbo.INSTALLMENT I on I.ID = INSTALLMENTSPLIT.INSTALLMENTID
            where LI.FINANCIALTRANSACTIONID = @REVENUEID -- PAYMENT

                and ISP.PLEDGEID = @PLEDGEID

            delete @NewImpactedInstallmentSplitsForCurrentPayment

            insert into @NewImpactedInstallmentSplitsForCurrentPayment (
                [INSTALLMENTSPLITDESIGNATIONID]
                ,[DESIGNATIONID]
                ,[AMOUNT]
                )
            select CAST(NIS.SPLITID as nvarchar(36)) + CAST(SPLITDESIGNATIONID as nvarchar(36)) [INSTALLMENTSPLITDESIGNATIONID]
                ,NIS.SPLITDESIGNATIONID [DESIGNATIONID]
                ,NIS.SPLITAMOUNT [AMOUNT]
            from @NewInstallmentsWithSplits NIS
            where NIS.SPLITID in (
                    select INSTALLMENTSPLIT.ID
                    from dbo.FINANCIALTRANSACTIONLINEITEM LI
                    inner join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = LI.ID
                    inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = ISP.INSTALLMENTSPLITID
                    where LI.FINANCIALTRANSACTIONID = @REVENUEID
                    )

            declare @CurrentPaymentImpactedInstallments table (
                [OLDDESIGNATIONID] uniqueidentifier
                ,[NEWDESIGNATIONID] uniqueidentifier
                ,[OLDAMOUNT] money
                ,[NEWAMOUNT] money
                )

            delete @CurrentPaymentImpactedInstallments

            insert into @CurrentPaymentImpactedInstallments (
                [OLDDESIGNATIONID]
                ,[NEWDESIGNATIONID]
                ,[OLDAMOUNT]
                ,[NEWAMOUNT]
                )
            select OLD.DESIGNATIONID
                ,NEW.DESIGNATIONID
                ,ISNULL(OLD.AMOUNT, 0)
                ,ISNULL(NEW.AMOUNT, 0)
            from @NewImpactedInstallmentSplitsForCurrentPayment NEW
            full join @OldImpactedInstallmentSplitsForCurrentPayment OLD on NEW.INSTALLMENTSPLITDESIGNATIONID = OLD.INSTALLMENTSPLITDESIGNATIONID

            declare @OldTotal as money
            declare @NewTotal as money

            select @OldTotal = SUM(OLDAMOUNT)
                ,@NewTotal = SUM(NEWAMOUNT)
            from @CurrentPaymentImpactedInstallments

            declare @PAYMENTADJUSTMENTNEEDED as bit = 0

            -- Check if the posted payment requires an adjustment, by checking if there are any differences between the new and old portions per designation

            if exists (
                    select *
                    from (
                        select case 
                                when @OldTotal <> 0
                                    then sum(OLDAMOUNT) / @OldTotal
                                else - 1
                                end OLD
                            ,case 
                                when @NewTotal <> 0
                                    then sum(NEWAMOUNT) / @NewTotal
                                else - 1
                                end NEW
                        from @CurrentPaymentImpactedInstallments
                        group by OLDDESIGNATIONID
                        ) D
                    where D.OLD != D.NEW
                    )
                set @PAYMENTADJUSTMENTNEEDED = 1

            if exists ( -- check if a revenue category change on the pledge causes a needed posted payment adjustment

                     select RCPLEDGE.GLREVENUECATEGORYMAPPINGID,RCPAYMENT.GLREVENUECATEGORYMAPPINGID
                    from dbo.FINANCIALTRANSACTION FT
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FTLI.FINANCIALTRANSACTIONID = FT.ID
                    inner join dbo.INSTALLMENTSPLITPAYMENT ISP on ISP.PAYMENTID = FTLI.ID
                    inner join dbo.FINANCIALTRANSACTION FT2 on ISP.PLEDGEID = FT2.ID
                    inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI2 on FTLI2.FINANCIALTRANSACTIONID = FT2.ID
                    full outer join REVENUECATEGORY RCPAYMENT on RCPAYMENT.ID = FTLI.ID
                    full outer join REVENUECATEGORY RCPLEDGE on RCPLEDGE.ID = FTLI2.ID
                    where FT.ID = @REVENUEID -- payment

                        and ISP.PLEDGEID = @PLEDGEID
                        and IsNull(RCPAYMENT.GLREVENUECATEGORYMAPPINGID, '00000000-0000-0000-0000-000000000000') != IsNull(RCPLEDGE.GLREVENUECATEGORYMAPPINGID, '00000000-0000-0000-0000-000000000000')
                    )
                set @PAYMENTADJUSTMENTNEEDED = 1

                if exists(
                select 1 from @NewInstallmentsWithSplits NI 
                inner join dbo.INSTALLMENT on NI.ID = INSTALLMENT.ID
                inner join dbo.INSTALLMENTPAYMENT on NI.ID = INSTALLMENTPAYMENT.INSTALLMENTID
                where NI.AMOUNT <> INSTALLMENT.AMOUNT)
            begin 
                    set @PAYMENTADJUSTMENTNEEDED = 1 
            end

                if exists( 
                select 1 from @NewInstallmentsWithSplits NI
                left join INSTALLMENTSPLIT on NI.ID = INSTALLMENTSPLIT.INSTALLMENTID
                and INSTALLMENTSPLIT.DESIGNATIONID = NI.SPLITDESIGNATIONID
                where INSTALLMENTSPLIT.DESIGNATIONID is null)
            begin 
                    set @PAYMENTADJUSTMENTNEEDED = 1 
            end

            if @PAYMENTADJUSTMENTNEEDED = 1 
            begin
                -- update TempRevenue table to set ISDELETED so that all payment splits should reset.

                update @TempRevenue set ISDELETED = 1
                -- payment adjustment field is set so that not posted payments are not deleted in case of Uk flag from temp revenue table.

                set @PAYMENTADJUSTMENT = 1
                -- Do not add a new adjustment if an unposted adjustment exists. ?

                if @ADJUSTED = 0 and @POSTED = 1 and @PROCESSADJUSTMENTS = 1
                begin
                    exec dbo.USP_SAVE_ADJUSTMENT @REVENUEID
                        ,@ADJUSTMENTID output
                        ,@CHANGEAGENTID
                        ,@CURRENTDATE
                        ,@ADJPAYMENT_DATE
                        ,@ADJPAYMENT_POSTDATE
                        ,@ADJPAYMENT_DETAILS
                        ,default
                        ,@ADJPAYMENT_REASONCODEID
                        ,@ADJ_POSTSTATUS;

                    insert into @RevenueAdjustmentHistoryMapping (
                        REVENUEID
                        ,ADJUSTMENTID
                        ,ISPAYMENT
                        )
                    values (
                        @REVENUEID
                        ,@ADJUSTMENTID
                        ,1
                        )

                    exec dbo.USP_SAVE_GIFTFEEADJUSTMENT @REVENUEID
                        ,@GIFTFEEADJUSTMENTID output
                        ,@CHANGEAGENTID
                        ,@CURRENTDATE
                        ,@ADJPAYMENT_DATE
                        ,@ADJPAYMENT_POSTDATE
                        ,@ADJPAYMENT_DETAILS
                        ,default
                        ,@ADJPAYMENT_REASONCODEID
                        ,@ADJ_POSTSTATUS;

                    insert into @GiftFeeAdjustments
                    values (
                        @REVENUEID
                        ,@GIFTFEEADJUSTMENTID
                        )
                end
            end
            else
            begin
                -- remove payments that do not require an adjustment

                -- if product is uk and status is not posted then set isdeleted flag to prevent revenue from being deleted.

                if @PRODUCTISUK = 1 and @POSTED = 0 and @PAYMENTADJUSTMENT is null
                begin
                         update @TempRevenue set ISDELETED = 0 where REVENUEID = @REVENUEID
                end
                    -- revenue with uk flag and not posted status will not be deleted to manage declines gift aid status.

                        delete
                        from @TempRevenue
                        where REVENUEID = @REVENUEID and (case when @PRODUCTISUK = 1 then POSTED else 1 end) = 1;
            end
            fetch next
            from PAYMENTCURSOR
            into @REVENUEID
                ,@ADJUSTED,@POSTED;
        end

        close PAYMENTCURSOR;

        deallocate PAYMENTCURSOR;

    if @PROCESSADJUSTMENTS = 1
    begin
        if exists (
                select 1
                from dbo.JOURNALENTRY
                inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY_EXT.ID = JOURNALENTRY.ID
                inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                where FINANCIALTRANSACTION.PARENTID = @PLEDGEID
                    and FINANCIALTRANSACTION.TYPECODE = 20 --writeoff

                    and JOURNALENTRY_EXT.OUTDATED = 0
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0 --standard

                )
        begin
            declare WRITEOFFCURSOR cursor local fast_forward
            for
            select WRITEOFF.ID
            from dbo.FINANCIALTRANSACTION WRITEOFF
            where WRITEOFF.PARENTID = @PLEDGEID
                and WRITEOFF.TYPECODE = 20
                and WRITEOFF.POSTSTATUSCODE = 2 --only adjust posted

                and WRITEOFF.DELETEDON is null;

            open WRITEOFFCURSOR;

            fetch next
            from WRITEOFFCURSOR
            into @WRITEOFFID;

            while @@FETCH_STATUS = 0
            begin
                declare @WRITEOFFADJUSTMENTID uniqueidentifier = null;

                exec dbo.USP_SAVE_WRITEOFFADJUSTMENT @WRITEOFFID
                    ,@WRITEOFFADJUSTMENTID output
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE
                    ,@ADJPAYMENT_DATE
                    ,@ADJPAYMENT_POSTDATE
                    ,@ADJPAYMENT_DETAILS
                    ,@ADJPAYMENT_REASONCODEID;

                --Save adjustment IDs for adjustment history

                insert into @RevenueAdjustmentHistoryMapping (
                    REVENUEID
                    ,ADJUSTMENTID
                    ,ISPAYMENT
                    )
                values (
                    @WRITEOFFID
                    ,@WRITEOFFADJUSTMENTID
                    ,0
                    );

                fetch next
                from WRITEOFFCURSOR
                into @WRITEOFFID;
            end

            close WRITEOFFCURSOR;

            deallocate WRITEOFFCURSOR;
        end
    end
    --    I want all the INSTALLMENTSPLITPAYMENT records to regenerate, but don't regenerate if the payment does not require adjustment

    delete INSTALLMENTSPLITPAYMENT
    from dbo.INSTALLMENTSPLITPAYMENT
    inner join @TempRevenue tr on INSTALLMENTSPLITPAYMENT.PAYMENTID = tr.RSPLITID and tr.ISDELETED = 1
    where tr.ISPAYMENT = 1

    delete INSTALLMENTSPLITWRITEOFF
    from dbo.INSTALLMENTSPLITWRITEOFF ISPWO
    inner join dbo.FINANCIALTRANSACTIONLINEITEM WOLI on ISPWO.WRITEOFFID = WOLI.FINANCIALTRANSACTIONID
    inner join @TempRevenue tr on WOLI.ID = tr.RSPLITID
    and tr.ISDELETED = 1
    where tr.ISPAYMENT = 0

    -- Delete the Revenue Split records and distributions.

    --Delete trigger on RevenueSplit view does a lot of checking for Foreign Keys to the view so I'll leave the view here

    declare @SplitsToDelete xml = (
            select RSPLITID as ID
            from @TempRevenue
            where EXISTINGDESIGNATIONID = 0 and ISDELETED = 1
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('SPLITSTODELETE')
                ,binary BASE64
            )

    exec dbo.USP_FINANCIALTRANSACTIONLINEITEM_DELETESPLITSANDMARKORPHANED @SplitsToDelete
        ,@CHANGEAGENTID
        ,@CURRENTDATE

    --This marks the lineitems that can be updated in the merges in USP_PLEDGE_PAYINSTALLMENTS

    --Set TYPECODE to 99 which will signal to the merge statement and the records should be updated instead of new records created

    update FINANCIALTRANSACTIONLINEITEM
    set TYPECODE = 99
    from @TempRevenue t1
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on t1.RSPLITID = FINANCIALTRANSACTIONLINEITEM.ID
    where t1.EXISTINGDESIGNATIONID = 1 and t1.ISDELETED = 1
        and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null 

    -- Delete the Distributions for any Un-posted Payments.

    -- I'm only commenting this code because it seems wrong.  PostStatusCode = 1 in Revenue Model is unposted, so this would delete records that are 

    -- other than unposted instead of deleting the unposted payments.  I think the deletion from RevenueGLDistribution saves it.

    /*Delete from GLTRANSACTION 
        where POSTSTATUSCODE <> 1 and ID In
            (Select GLTRANSACTIONID from REVENUEGLDISTRIBUTION 
             where OUTDATED=0 and REVENUEID in (Select REVENUEID from @TempRevenue)) 

        Delete from REVENUEGLDISTRIBUTION 
        where OUTDATED=0 and REVENUEID in (Select REVENUEID from @TempRevenue) 
        */
    --FTM Deletion

    --First do foreign keys

    update dbo.ADJUSTMENTHISTORY
    set GLTRANSACTIONID = null
        ,DATECHANGED = getdate()
        ,CHANGEDBYID = @CHANGEAGENTID
    from ADJUSTMENTHISTORY as AH
    inner join dbo.JOURNALENTRY on AH.GLTRANSACTIONID = JOURNALENTRY.ID
    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join @TempRevenue as D on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = D.REVENUEID and D.ISDELETED = 1
    where JOURNALENTRY_EXT.OUTDATED = 0
        and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2;

    update dbo.ADJUSTMENTHISTORYPROPERTY
    set GLTRANSACTIONID = null
        ,DATECHANGED = getdate()
        ,CHANGEDBYID = @CHANGEAGENTID
    from ADJUSTMENTHISTORYPROPERTY as AH
    inner join dbo.JOURNALENTRY on AH.GLTRANSACTIONID = JOURNALENTRY.ID
    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join @TempRevenue as D on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = D.REVENUEID and D.ISDELETED = 1
    where JOURNALENTRY_EXT.OUTDATED = 0
        and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2;

    update dbo.ADJUSTMENTHISTORYSTOCK
    set GLTRANSACTIONID = null
        ,DATECHANGED = getdate()
        ,CHANGEDBYID = @CHANGEAGENTID
    from ADJUSTMENTHISTORYSTOCK as AH
    inner join dbo.JOURNALENTRY on AH.GLTRANSACTIONID = JOURNALENTRY.ID
    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join @TempRevenue as D on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = D.REVENUEID and D.ISDELETED = 1
    where JOURNALENTRY_EXT.OUTDATED = 0
        and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2;

    delete JOURNALENTRY
    from dbo.JOURNALENTRY
    inner join dbo.JOURNALENTRY_EXT on JOURNALENTRY.ID = JOURNALENTRY_EXT.ID
    inner join dbo.FINANCIALTRANSACTIONLINEITEM on JOURNALENTRY.FINANCIALTRANSACTIONLINEITEMID = FINANCIALTRANSACTIONLINEITEM.ID
    inner join @TempRevenue as D on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = D.REVENUEID and D.ISDELETED = 1
    where JOURNALENTRY_EXT.OUTDATED = 0
        and FINANCIALTRANSACTIONLINEITEM.POSTSTATUSCODE != 2
        and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1
        --and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null;


    -- Update installments 

    update NEWI
    set NEWI.ID = SEQ.ID
    from @NewInstallmentsWithSplits NEWI
    inner join @TempSeqTbl SEQ on NEWI.SEQUENCE = SEQ.SEQUENCE;

    set @INSTALLMENTS = (
            select distinct NEWI.ID
                ,NEWI.[DATE]
                ,NEWI.AMOUNT
                ,NEWI.RECEIPTAMOUNT
                ,NEWI.BALANCE
                ,NEWI.APPLIED
                ,NEWI.SEQUENCE
            from @NewInstallmentsWithSplits NEWI
            order by NEWI.SEQUENCE --ordering shouldn't matter here, but it makes the XML look more like what it would normally

            for xml raw('ITEM')
                ,type
                ,elements
                ,root('INSTALLMENTS')
                ,binary BASE64
            );
    set @INSTALLMENTSPLITS = (
            select distinct NEWI.SPLITID ID
                ,NEWI.ID INSTALLMENTID
                ,NEWI.SPLITDESIGNATIONID DESIGNATIONID
                ,NEWI.SPLITAMOUNT AMOUNT
                ,isnull(REVSPLITS.ID, NEWI.REVENUESPLITID) as REVENUESPLITID
            from @NewInstallmentsWithSplits NEWI
            left join (
                select FINANCIALTRANSACTIONLINEITEM.ID
                    ,REVENUESPLIT_EXT.DESIGNATIONID
                from FINANCIALTRANSACTIONLINEITEM
                inner join REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
                    and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                    and FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0
                ) REVSPLITS on NEWI.SPLITDESIGNATIONID = REVSPLITS.DESIGNATIONID
            for xml raw('ITEM')
                ,type
                ,elements
                ,root('INSTALLMENTSPLITS')
                ,binary BASE64
            );
    set @INSTALLMENTS = dbo.UFN_INSTALLMENT_CONVERTAMOUNTSINXML(@INSTALLMENTS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID)

    exec dbo.USP_INSTALLMENT_GETINSTALLMENTS_2_UPDATEFROMXML @PLEDGEID
        ,@INSTALLMENTS
        ,@CHANGEAGENTID
        ,@CURRENTDATE;

    set @INSTALLMENTSPLITS = dbo.UFN_INSTALLMENTSPLIT_CONVERTAMOUNTSINXML(@INSTALLMENTSPLITS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID)

    exec dbo.USP_PLEDGE_GETINSTALLMENTSPLITS_2_UPDATEFROMXML @PLEDGEID
        ,@INSTALLMENTSPLITS
        ,@CHANGEAGENTID
        ,@CURRENTDATE;

    -- More Work for PBI 40790

    set @APPLICATIONTYPE = 2

    --Update writeoffs

    declare @WRITEOFFIDTABLE UDT_GENERICID;

    insert into @WRITEOFFIDTABLE
    select ID
    from dbo.FINANCIALTRANSACTION
    where 
        PARENTID = @PLEDGEID
        and TYPECODE = 20 --writeoff

        and DELETEDON is null
        and exists (
            --exclude write-offs we've removed from updating

            select 1 
            from @TempRevenue [TempRevenue] 
            where FINANCIALTRANSACTION.ID = [TempRevenue].REVENUEID and [TempRevenue].ISDELETED = 1 
        )

    if exists (
            select 1
            from @WRITEOFFIDTABLE
            )
    begin
        declare @WRITEOFFCURSOR cursor;set @WRITEOFFCURSOR = cursor local fast_forward
        for
        select ID
        from @WRITEOFFIDTABLE

        open @WRITEOFFCURSOR;

        fetch next
        from @WRITEOFFCURSOR
        into @WRITEOFFID;

        while @@FETCH_STATUS = 0
        begin
            declare @WRITEOFFINSTALLMENTS xml = (
                    select ID
                        ,[DATE]
                        ,WRITEOFFINSTALLMENT.TRANSACTIONAMOUNT as AMOUNT
                        ,BALANCE
                        ,t1.WRITEOFFAMOUNT
                        ,SEQUENCE
                        ,TRANSACTIONCURRENCYID
                    from dbo.UFN_WRITEOFF_GETINSTALLMENTSFOREDIT(@WRITEOFFID) WRITEOFFINSTALLMENT
                    left join @WriteOffInstallmentAmounts t1 on WRITEOFFINSTALLMENT.ID = t1.InstallmentID
                        and t1.WRITEOFFID = @WRITEOFFID
                    for xml raw('ITEM')
                        ,type
                        ,elements
                        ,root('INSTALLMENTS')
                        ,binary BASE64
                    );
            declare @WRITEOFFTOTALAMOUNT money = (
                    select sum(WRITEOFFAMOUNT)
                    from @WriteOffInstallmentAmounts
                    where WRITEOFFID = @WRITEOFFID
                    );

            exec dbo.USP_INSTALLMENT_WRITEOFFINSTALLMENTS @WRITEOFFID
                ,@WRITEOFFTOTALAMOUNT
                ,@CHANGEAGENTID
                ,@CURRENTDATE
                ,1
                ,@WRITEOFFINSTALLMENTS;

            exec dbo.USP_WRITEOFF_FIXSPLITS2 @WRITEOFFID
                ,@PLEDGEID
                ,@CHANGEAGENTID
                ,@CURRENTDATE
                ,@ADJPAYMENT_POSTDATE;

            fetch next
            from @WRITEOFFCURSOR
            into @WRITEOFFID;
        end

        deallocate @WRITEOFFCURSOR;
    end

    declare REVENUECURSOR cursor local fast_forward
    for
    select REVENUEID
        ,DEPOSITID
        ,sum(PAYMENTAMOUNT)
        ,REVENUEDATE
        ,DONOTPOST
        ,OVERPAYMENTAPPLICATIONTYPECODE
        ,CONSTITUENTID
        ,APPEALID
        ,PAYMENTMETHODCODE
        ,ISDELETED
    from @TempRevenue
    where ISPAYMENT = 1
    group by REVENUEID
        ,DEPOSITID
        ,REVENUEDATE
        ,DONOTPOST
        ,OVERPAYMENTAPPLICATIONTYPECODE
        ,CONSTITUENTID
        ,APPEALID
        ,PAYMENTMETHODCODE
        ,REVENUEDATEADDED
        ,ISDELETED
    order by REVENUEDATEADDED

    open REVENUECURSOR;

    fetch next
    from REVENUECURSOR
    into @REVENUEID
        ,@DEPOSITID
        ,@APPLIEDAMOUNT
        ,@REVENUEDATE
        ,@DONOTPOST
        ,@OVERPAYMENTAPPLICATIONTYPECODE
        ,@PAYMENTCONSTITUENTID
        ,@REVENUEAPPEALID
        ,@REVENUEPAYMENTMETHODCODE
        ,@ISDELETED;

    while (@@FETCH_STATUS = 0)
    begin
        set @CREATEDSPLITS = null
        set @AMOUNTPAID = null

        -- Re-Add the Payment Split if the revenue require adjustment else pick the existing.  Except not for payments not requiring adjustment

    If @ISDELETED = 1 
        begin
        exec dbo.USP_PLEDGE_ADDPAYMENT @REVENUEID
            ,@PLEDGEID
            ,@APPLIEDAMOUNT
            ,@PAYMENTCONSTITUENTID
            ,@REVENUEDATE
            ,@UNAPPLIEDMATCHINGGIFTSPLITS
            ,@APPLICATIONTYPE
            ,@AMOUNTPAID output
            ,@CURRENTDATE
            ,@CHANGEAGENTID
            ,@CREATEDSPLITS output
            ,@OVERPAYMENTAPPLICATIONTYPECODE
        end
        else
        begin
            set @CREATEDSPLITS = (select
                FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT AMOUNT,
                REVENUESPLIT_EXT.DESIGNATIONID,
                FINANCIALTRANSACTIONLINEITEM.ID,
                FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
                REVENUESPLIT_EXT.APPLICATIONCODE,
                REVENUESPLIT_EXT.TYPECODE,
                FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
                from FINANCIALTRANSACTIONLINEITEM
                inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
                inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
                and FINANCIALTRANSACTION.ID = @REVENUEID
                inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
                and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
                for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64)
        end

        declare @ORIGINALPAYMETHODID uniqueidentifier
        declare @ORIGINALPAYMENTMETHODCODE tinyint

        select @ORIGINALPAYMETHODID = RPM.ID
            ,@ORIGINALPAYMENTMETHODCODE = PAYMENTMETHODCODE
        from dbo.REVENUEPAYMENTMETHOD RPM
        where RPM.REVENUEID = @REVENUEID

        declare @STOCKSALEADJUSTMENTIDS xml;
        declare @GIFTINKINDSALEADJUSTMENTIDS xml;
        declare @PROPERTYDETAILADJUSTMENTID uniqueidentifier;

        if (@PROCESSADJUSTMENTS = 1)
        begin
            declare @STOCKSALEEXISTS as bit

            select @STOCKSALEEXISTS = case 
                    when exists (
                            select 1
                            from dbo.STOCKSALE_EXT T1
                            inner join dbo.FINANCIALTRANSACTION T2 on T1.ID = T2.ID
                            where T2.PARENTID = @REVENUEID
                                and T2.POSTSTATUSCODE = 2
                                and T2.DELETEDON is null
                            )
                        then 1
                    else 0
                    end

            /* If sold stock has been posted, log stock detail adjustment */
            if (@STOCKSALEEXISTS = 1)
                and @ORIGINALPAYMENTMETHODCODE = 4
            begin
                exec dbo.USP_SAVE_STOCKDETAILADJUSTMENT @ORIGINALPAYMETHODID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE
                    ,@ADJPAYMENT_DATE
                    ,@ADJPAYMENT_POSTDATE
                    ,@ADJPAYMENT_DETAILS
                    ,@STOCKSALEADJUSTMENTIDS output
                    ,@ADJPAYMENT_REASONCODEID
                    ,@ADJ_POSTSTATUS;

                if @STOCKSALEADJUSTMENTIDS is not null
                    exec dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVEHISTORY @ORIGINALPAYMETHODID
                        ,@CHANGEAGENTID
                        ,null
                        ,@STOCKSALEADJUSTMENTIDS;
            end

            declare @GIFTINKINDSALEEXISTS as bit

            select @GIFTINKINDSALEEXISTS = case 
                    when exists (
                            select 1
                            from dbo.GIFTINKINDSALE_EXT T1
                            inner join dbo.FINANCIALTRANSACTION T2 on T1.ID = T2.ID
                            where T2.PARENTID = @REVENUEID
                                and T2.POSTSTATUSCODE = 2
                                and T2.DELETEDON is null
                            )
                        then 1
                    else 0
                    end

            /* If the sold gift-in-kind has been posted, log the gift-in-kind detail adjustment */
            if (@GIFTINKINDSALEEXISTS = 1)
                and @ORIGINALPAYMENTMETHODCODE = 6
            begin
                exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILADJUSTMENT @ORIGINALPAYMETHODID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE
                    ,@ADJPAYMENT_DATE
                    ,@ADJPAYMENT_POSTDATE
                    ,@ADJPAYMENT_DETAILS
                    ,@GIFTINKINDSALEADJUSTMENTIDS output
                    ,@ADJPAYMENT_REASONCODEID
                    ,@ADJ_POSTSTATUS;

                if @GIFTINKINDSALEADJUSTMENTIDS is not null
                    exec dbo.USP_ADJUSTMENTHISTORY_GIFTINKIND_SAVEHISTORY @ORIGINALPAYMETHODID
                        ,@CHANGEAGENTID
                        ,null
                        ,@GIFTINKINDSALEADJUSTMENTIDS;
            end

            declare @PROPERTYSALEEXISTS as bit

            select @PROPERTYSALEEXISTS = case 
                    when exists (
                            select 1
                            from dbo.PROPERTYDETAIL_EXT T1
                            inner join dbo.FINANCIALTRANSACTION T2 on T1.ID = T2.ID
                            where T2.PARENTID = @REVENUEID
                                and T2.POSTSTATUSCODE = 2
                                and T2.DELETEDON is null
                            )
                        then 1
                    else 0
                    end

            /* If sold property has been posted, log property detail adjustment */
            if (@PROPERTYSALEEXISTS = 1)
                and @ORIGINALPAYMENTMETHODCODE = 5
            begin
                exec dbo.USP_SAVE_PROPERTYDETAILADJUSTMENT @ORIGINALPAYMETHODID
                    ,@PROPERTYDETAILADJUSTMENTID output
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE
                    ,@ADJPAYMENT_DATE
                    ,@ADJPAYMENT_POSTDATE
                    ,ADJPAYMENT_DETAILS
                    ,@ADJPAYMENT_REASONCODEID
                    ,@ADJ_POSTSTATUS;

                if @PROPERTYDETAILADJUSTMENTID is not null
                    exec dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY @ORIGINALPAYMETHODID
                        ,@CHANGEAGENTID
                        ,null
                        ,@PROPERTYDETAILADJUSTMENTID;
            end
        end

        if @REVENUEPAYMENTMETHODCODE = 4
        begin
            if @PROCESSADJUSTMENTS = 0
                delete
                from dbo.STOCKSALEGLDISTRIBUTION
                where REVENUEID = @REVENUEID
                    and OUTDATED = 0;

            if @ADJ_POSTSTATUS <> 2
                or @PROCESSADJUSTMENTS = 0
                exec dbo.USP_SAVE_STOCKDETAILGLDISTRIBUTION @REVENUEID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE;-- Add new stock detail GL distributions

        end

        if @REVENUEPAYMENTMETHODCODE = 6
        begin
            if @PROCESSADJUSTMENTS = 0
                delete
                from dbo.GIFTINKINDSALEGLDISTRIBUTION
                where REVENUEID = @REVENUEID
                    and OUTDATED = 0;

            if @ADJ_POSTSTATUS <> 2
                or @PROCESSADJUSTMENTS = 0
                exec dbo.USP_SAVE_GIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION @REVENUEID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE;-- Add new gift-in-kind detail GL distributions

        end

        if @REVENUEPAYMENTMETHODCODE = 5
        begin
            if @PROCESSADJUSTMENTS = 0
                delete
                from dbo.PROPERTYDETAILGLDISTRIBUTION
                where REVENUEID = @REVENUEID
                    and OUTDATED = 0;

            if @ADJ_POSTSTATUS <> 2
                or @PROCESSADJUSTMENTS = 0
                exec dbo.USP_SAVE_PROPERTYDETAILGLDISTRIBUTION @REVENUEID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE;-- Add new property detail GL distributions

        end

        --Only perform the following if the product is UK

        if @PRODUCTISUK = 1
        begin
            if @REVENUEPAYMENTMETHODCODE = 2
                select @REVENUECREDITTYPECODEID = CREDITCARD.CREDITTYPECODEID
                from dbo.FINANCIALTRANSACTION
                inner join dbo.REVENUEPAYMENTMETHOD on FINANCIALTRANSACTION.ID = REVENUEPAYMENTMETHOD.REVENUEID
                left join dbo.REVENUESCHEDULE on FINANCIALTRANSACTION.ID = REVENUESCHEDULE.ID
                left join dbo.CREDITCARD on CREDITCARD.ID = REVENUESCHEDULE.CREDITCARDID
                where FINANCIALTRANSACTION.ID = @REVENUEID

            set @DELETEDANDCHANGEDSPLITSINFOXML= 
                (select top 1 DELETEDANDCHANGEDSPLITSINFOXML from @TempRevenue where REVENUEID=@REVENUEID);

          --Need to identify the new splits id which need declines gift aid set to true on them (otherwise it will default to false.)

            declare @CREATEDSPLITSTBL table 
              ([ID] uniqueidentifier
                ,[DESIGNATIONID] uniqueidentifier
                ,[FINANCIALTRANSACTIONID] uniqueidentifier)

            insert into @CREATEDSPLITSTBL (ID, DESIGNATIONID, FINANCIALTRANSACTIONID)
          select T1.split.value('(ID)[1]', 'uniqueidentifier') as 'ID'
                ,T1.split.value('(DESIGNATIONID)[1]', 'uniqueidentifier') as 'DESIGNATIONID'
                ,T1.split.value('(FINANCIALTRANSACTIONID)[1]', 'uniqueidentifier') as 'FINANCIALTRANSACTIONID'  
                from @CREATEDSPLITS.nodes('/SPLITS/ITEM') T1(split);

            declare @SPLITSDECLININGGIFTAID xml
             declare @SPLITSDECLININGGIFTAIDTBL table (REVENUESPLITID uniqueidentifier)

      insert into @SPLITSDECLININGGIFTAIDTBL (REVENUESPLITID)
         (select RSPLITID as REVENUESPLITID
         from @TempRevenue
         where DECLINESGIFTAID = 1)
             union
             (select CREATEDSPLITS.ID as REVENUESPLITID
                  from @CREATEDSPLITSTBL CREATEDSPLITS
                    inner join @SPLITSTBL SPLITS on SPLITS.DESIGNATIONID=CREATEDSPLITS.DESIGNATIONID
                    where SPLITS.DECLINESGIFTAID=1
                    and CREATEDSPLITS.FINANCIALTRANSACTIONID=@REVENUEID)

          set @SPLITSDECLININGGIFTAID=
              (select REVENUESPLITID
            from @SPLITSDECLININGGIFTAIDTBL
          for xml raw('ITEM')
            ,type
            ,elements
            ,root('SPLITSDECLININGGIFTAID')
            ,binary BASE64
          )

            exec USP_MANAGEGIFTAIDFORSPLITS 
                @REVENUEID, 
                @BASECURRENCYID,
                @CHANGEAGENTID,
                @CURRENTDATE,
                @CURRENTDATE,
                @SPLITSDECLININGGIFTAID,
                null,
                @DELETEDANDCHANGEDSPLITSINFOXML;

        end

        if @DONOTPOST = 0
        begin
            set @GIFTFEEADJUSTMENTID = (
                    select GIFTFEEADJUSTMENTID
                    from @GiftFeeAdjustments
                    where REVENUEID = @REVENUEID
                    )

            -- Recreate the gift fees and associated gift fee distributions

            exec dbo.USP_PLEDGE_PAYMENT_ADDGIFTFEES @REVENUEID
                ,@CREATEDSPLITS
                ,@PAYMENTCONSTITUENTID
                ,@CHANGEAGENTID
                ,@CURRENTDATE
                ,@GIFTFEEADJUSTMENTID;

            if @GIFTFEEADJUSTMENTID is not null
            begin
                --Remap the existing gift-fee adjustments to the current payment source

                update FTLI
                set ftli.SOURCELINEITEMID = SOURCELINEITEM.REVERSEDLINEITEMID
                from FINANCIALTRANSACTIONLINEITEM FTLI
                inner join FINANCIALTRANSACTIONLINEITEM SOURCELINEITEM on ftli.SOURCELINEITEMID = SOURCELINEITEM.ID
                where FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @GIFTFEEADJUSTMENTID
                    and ftli.TYPECODE = 7
                    and SOURCELINEITEM.SOURCELINEITEMID is null
                    and SOURCELINEITEM.REVERSEDLINEITEMID is not null

                exec dbo.USP_SAVE_GIFTFEEADJUSTMENTGLDISTRIBUTION @REVENUEID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE;

                --Link gift fees to adjustments

                update FTLI
                set FTLI.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @GIFTFEEADJUSTMENTID
                from FINANCIALTRANSACTIONLINEITEM FTLI
                where FTLI.TYPECODE = 7
                    and FTLI.FINANCIALTRANSACTIONID = @REVENUEID
                    and FINANCIALTRANSACTIONLINEITEMADJUSTMENTID is null
            end
            else
            begin
                exec dbo.USP_SAVE_GIFTFEEGLDISTRIBUTION @REVENUEID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE;
            end

            -- Update the Distribution.

            exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEID
                ,@CHANGEAGENTID
                ,@CURRENTDATE;

            -- Update the distribution with the deposit info.

            if @DEPOSITID is not null
                exec dbo.USP_BANKACCOUNTDEPOSIT_OVERWRITEPAYMENTDEBITACCOUNTS @REVENUEID
                    ,@DEPOSITID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE;
        end

        fetch next
        from REVENUECURSOR
        into @REVENUEID
            ,@DEPOSITID
            ,@APPLIEDAMOUNT
            ,@REVENUEDATE
            ,@DONOTPOST
            ,@OVERPAYMENTAPPLICATIONTYPECODE
            ,@PAYMENTCONSTITUENTID
            ,@REVENUEAPPEALID
            ,@REVENUEPAYMENTMETHODCODE
            ,@ISDELETED;
    end

    close REVENUECURSOR;

    deallocate REVENUECURSOR;

    if exists (
            select 1
            from @RevenueAdjustmentHistoryMapping
            )
    begin
        -- Call USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY for all adjustments initially created by the call to USP_SAVE_ADJUSTMENT earlier

        declare ADJUSTMENTCURSOR cursor local fast_forward
        for
        select distinct REVENUEID
            ,ADJUSTMENTID
            ,ISPAYMENT
        from @RevenueAdjustmentHistoryMapping

        open ADJUSTMENTCURSOR

        fetch next
        from ADJUSTMENTCURSOR
        into @REVENUEID
            ,@ADJUSTMENTID
            ,@ISPAYMENT

        while @@FETCH_STATUS = 0
        begin
            if (@ISPAYMENT = 1)
                exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY @REVENUEID
                    ,@CHANGEAGENTID
                    ,@CURRENTDATE
                    ,@ADJUSTMENTID
            else
                exec dbo.USP_ADJUSTMENTHISTORY_WRITEOFF_SAVEHISTORY @REVENUEID
                    ,@CHANGEAGENTID
                    ,null
                    ,@ADJUSTMENTID

            fetch next
            from ADJUSTMENTCURSOR
            into @REVENUEID
                ,@ADJUSTMENTID
                ,@ISPAYMENT
        end

        close ADJUSTMENTCURSOR

        deallocate ADJUSTMENTCURSOR
    end

    -- clear the user-defined gl distributions

    --if (@PROCESSADJUSTMENTS = 1)

    --begin

    delete
    from dbo.WRITEOFFGLDISTRIBUTION
    where WRITEOFFID in (
            select WO.ID
            from dbo.WRITEOFF WO
            where WO.REVENUEID = @PLEDGEID
            )
        and OUTDATED = 0;

    -- Add new writeoff GL distributions if appropriate

    if exists (
            select ID
            from dbo.FINANCIALTRANSACTION
            where PARENTID = @PLEDGEID
                and TYPECODE = 20 --writeoff

                and POSTSTATUSCODE <> 3 --do not post

                and DELETEDON is null
            )
    begin
        if (dbo.UFN_VALID_BASICGL_INSTALLED() = 1)
        begin
            exec dbo.USP_SAVE_PLEDGEWRITEOFFGLDISTRIBUTION @PLEDGEID
                ,@WRITEOFFIDTABLE
                ,@CHANGEAGENTID
                ,@CURRENTDATE;
        end
        else
        begin
            exec dbo.USP_SAVE_WRITEOFFGLDISTRIBUTION @PLEDGEID
                ,@CHANGEAGENTID
                ,@CURRENTDATE;
        end
    end
            --end

end