USP_INSTALLMENT_WRITEOFFINSTALLMENTSPLITS

        CREATE procedure dbo.USP_INSTALLMENT_WRITEOFFINSTALLMENTSPLITS
        (
            @PLEDGEID uniqueidentifier, 
            @INSTALLMENTID uniqueidentifier, 
            @WRITEOFFID uniqueidentifier, 
            @WRITEOFFAMOUNT money,
            @CHANGEAGENTID uniqueidentifier,
            @CHANGEDATE datetime
        )
        as
            set nocount on;

            -- All amounts are in the transaction currency of the installment (and also pledge)

            -- unless otherwise noted.


            declare @TRANSACTIONCURRENCYID uniqueidentifier;
            declare @BASEEXCHANGERATEID uniqueidentifier;
            declare @BASECURRENCYID uniqueidentifier;
            declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
            declare @BASECURRENCYDECIMALDIGITS tinyint;
            declare @TRANSACTIONCURRENCYDECIMALDIGITS tinyint;
            declare @ORGANIZATIONCURRENCYID uniqueidentifier;
            declare @ORGANIZATIONCURRENCYDECIMALDIGITS tinyint;
            declare @ORGANIZATIONAMOUNTORIGINCODE tinyint;

            set @ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
            select @ORGANIZATIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS from dbo.CURRENCY where CURRENCY.ID = @ORGANIZATIONCURRENCYID;

            set @ORGANIZATIONAMOUNTORIGINCODE = coalesce((select ORGANIZATIONAMOUNTORIGINCODE from dbo.MULTICURRENCYCONFIGURATION), 0);

            select
                @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
                @BASECURRENCYID = CURRENCYSET.BASECURRENCYID,
                @BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
                @ORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID,
                @BASECURRENCYDECIMALDIGITS = BASECURRENCY.DECIMALDIGITS,
                @TRANSACTIONCURRENCYDECIMALDIGITS = TRANSACTIONCURRENCY.DECIMALDIGITS 
            from
                dbo.FINANCIALTRANSACTION
                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
                inner join dbo.CURRENCY BASECURRENCY on CURRENCYSET.BASECURRENCYID = BASECURRENCY.ID
                inner join dbo.CURRENCY TRANSACTIONCURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = TRANSACTIONCURRENCY.ID
            where
                FINANCIALTRANSACTION.ID = @PLEDGEID
                and FINANCIALTRANSACTION.DELETEDON is null;

            -- initialize the writeoff amount for each installment split as its balance; we can adjust later if only a portion of the installment is being written off

            declare @SPLITS table(
                INSTALLMENTSPLITID uniqueidentifier, 
                DESIGNATIONID uniqueidentifier, 
                AMOUNTWRITEOFFTX money, 
                AMOUNTWRITEOFFBASE money, 
                AMOUNTWRITEOFFORG money
            );

            --AdamBu 10/19/10

            --    1) Don't need to track writeoff ID.

            --    2) Don't just use the installment split balance.  In the event that we are editing an existing 

            --        writeoff, we want to ignore any affect that writeoff had on the IS's balance.  As such,

            --        start from that balance, but add back the amount that had been written off by this writeoff

            --        before it was edited.

            insert into @SPLITS(
                INSTALLMENTSPLITID, 
                AMOUNTWRITEOFFTX, 
                DESIGNATIONID
            )
            select 
                INSTALLMENTSPLIT.ID, 
                dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID)
                    + coalesce(
                        (
                            select sum(INSTALLMENTSPLITWRITEOFF.AMOUNT)
                            from dbo.INSTALLMENTSPLITWRITEOFF
                            where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
                                and INSTALLMENTSPLITWRITEOFF.WRITEOFFID = @WRITEOFFID
                        )
                    ,0), 
                INSTALLMENTSPLIT.DESIGNATIONID
            from dbo.INSTALLMENTSPLIT 
                inner join dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
            where INSTALLMENT.ID = @INSTALLMENTID 

            declare @installmentBalance money
            select
                @installmentBalance = SUM(AMOUNTWRITEOFFTX) 
            from @SPLITS

            if @WRITEOFFAMOUNT <> @installmentBalance
            begin
                -- only a portion of the installment is being written off, so pro-rate among designations

                declare @SPLITSXML xml = (
                    select 
                        AMOUNTWRITEOFFTX as AMOUNT, 
                        INSTALLMENTSPLITID as ID 
                    from @SPLITS 
                    for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),BINARY BASE64
                );

                declare @PRORATED table(
                    ID uniqueidentifier, 
                    AMOUNT money
                );

                insert into @PRORATED(
                    ID, 
                    AMOUNT
                )
                select 
                    ID, 
                    AMOUNT 
                from dbo.UFN_SPLITS_PRORATEAMOUNTS(@installmentBalance, @WRITEOFFAMOUNT, @TRANSACTIONCURRENCYDECIMALDIGITS, @SPLITSXML);

                update @SPLITS
                set 
                    AMOUNTWRITEOFFTX = PROR.AMOUNT
                from @PRORATED PROR
                    inner join @SPLITS on PROR.ID = INSTALLMENTSPLITID
            end

            -- perform currency conversion for writeoff split records

            declare @currencySplits xml = (
                select 
                    INSTALLMENTSPLITID ID, 
                    @INSTALLMENTID as INSTALLMENTID, 
                    DESIGNATIONID, 
                    AMOUNTWRITEOFFTX as AMOUNT
                from @SPLITS
                for xml raw('ITEM'),type,elements,root('INSTALLMENTSPLITS'),BINARY BASE64
            );
            -- please note that UFN_INSTALLMENTSPLIT_CONVERTAMOUNTSINXML is evil insofar as the "AMOUNT" on the way in becomes "TRANSACTIONAMOUNT" on the way out, 

            -- and the calculated "BASEAMOUNT" is returned as the new "AMOUNT."  

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

            update 
                @SPLITS
            set 
                AMOUNTWRITEOFFBASE = INSTALLMENTSPLITSITEM.ELEMENT.value('AMOUNT[1]', 'money'), 
                AMOUNTWRITEOFFORG = INSTALLMENTSPLITSITEM.ELEMENT.value('ORGANIZATIONAMOUNT[1]', 'money')
            from 
                @currencySplits.nodes('/INSTALLMENTSPLITS/ITEM') INSTALLMENTSPLITSITEM(ELEMENT)
                inner join @SPLITS SP on INSTALLMENTSPLITSITEM.ELEMENT.value('(ID)[1]', 'uniqueidentifier') = SP.INSTALLMENTSPLITID 

            --AdamBu 10/19/10 - In the event that we are editing an existing writeoff, update the existing

            --    installment split writeoffs whose new amount is not 0.

            update dbo.INSTALLMENTSPLITWRITEOFF
            set
                AMOUNT = SPLITS.AMOUNTWRITEOFFBASE, 
                CHANGEDBYID = @CHANGEAGENTID, 
                DATECHANGED = @CHANGEDATE, 
                TRANSACTIONAMOUNT = SPLITS.AMOUNTWRITEOFFTX, 
                ORGANIZATIONAMOUNT = SPLITS.AMOUNTWRITEOFFORG,
                BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
                ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID
            from @SPLITS SPLITS
            where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = SPLITS.INSTALLMENTSPLITID
                and INSTALLMENTSPLITWRITEOFF.WRITEOFFID = @WRITEOFFID
                and SPLITS.AMOUNTWRITEOFFTX > 0

            --AdamBu 10/19/10 - In the event that we are editing an existing writeoff, delete existing

            --    installment split writeoffs whose new amount is 0.

            delete dbo.INSTALLMENTSPLITWRITEOFF
            from @SPLITS SPLITS
            where INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = SPLITS.INSTALLMENTSPLITID
                and INSTALLMENTSPLITWRITEOFF.WRITEOFFID = @WRITEOFFID
                and SPLITS.AMOUNTWRITEOFFTX = 0

            -- insert new INSTALLMENTSPLITWRITEOFF records

            --AdamBu 10/19/10 - Don't create 0 amount installment split writeoffs.

            insert into dbo.INSTALLMENTSPLITWRITEOFF(
                ID, 
                WRITEOFFID, 
                INSTALLMENTSPLITID, 
                AMOUNT, 
                ADDEDBYID, 
                CHANGEDBYID, 
                DATEADDED, 
                DATECHANGED, 
                BASECURRENCYID, 
                TRANSACTIONAMOUNT, 
                TRANSACTIONCURRENCYID, 
                ORGANIZATIONAMOUNT, 
                BASEEXCHANGERATEID, 
                ORGANIZATIONEXCHANGERATEID
            )
            select 
                newid(), 
                @WRITEOFFID, 
                SPLITS.INSTALLMENTSPLITID, 
                SPLITS.AMOUNTWRITEOFFBASE, 
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CHANGEDATE, 
                @CHANGEDATE, 
                @BASECURRENCYID, 
                SPLITS.AMOUNTWRITEOFFTX, 
                @TRANSACTIONCURRENCYID, 
                SPLITS.AMOUNTWRITEOFFORG, 
                @BASEEXCHANGERATEID, 
                @ORGANIZATIONEXCHANGERATEID
            from @SPLITS SPLITS
                left join dbo.INSTALLMENTSPLITWRITEOFF 
                    on INSTALLMENTSPLITWRITEOFF.INSTALLMENTSPLITID = SPLITS.INSTALLMENTSPLITID
                        and INSTALLMENTSPLITWRITEOFF.WRITEOFFID = @WRITEOFFID
            where INSTALLMENTSPLITWRITEOFF.ID is null
                and SPLITS.AMOUNTWRITEOFFTX > 0

            return 0;