USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY

            CREATE procedure dbo.USP_ADJUSTMENTHISTORY_REVENUE_SAVEHISTORY
            (
                @REVENUEID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime,
                @ADJUSTMENTID uniqueidentifier
            )
            as
            set nocount on;
            /*Before calling this procedure, be sure that you called USP_SAVE_ADJUSTMENT and then save all of the changes to the various revenue tables*/

            declare @ADJUSTMENTCOUNT int; 

            declare @PREVIOUSADJUSTMENTID uniqueidentifier;
            declare @PREVIOUSADJUSTMENTHISTORYID uniqueidentifier;
            declare @ADJUSTMENTHISTORYID uniqueidentifier; 
            declare @ISNEWREVENUE bit;
            declare @BASECURRENCYID uniqueidentifier;
            declare @TRANSACTIONCURRENCYID uniqueidentifier;
            declare @AMOUNTCHANGED bit;
            declare @SPLITSCHANGED bit;
            declare @REVENUESTREAMSCHANGED bit;
            declare @PAYMENTMETHODCHANGED bit;
            declare @CONSTITUENTCHANGED bit;
            declare @BASERATECHANGED bit;
            declare @ORGANIZATIONRATECHANGED bit;

            declare @EDITINGADJUSTMENT bit;

            declare @REVENUETYPECODE tinyint;

            set @AMOUNTCHANGED = 0;
            set @REVENUESTREAMSCHANGED = 0;
            set @PAYMENTMETHODCHANGED = 0;
            set @CONSTITUENTCHANGED = 0;
            set @BASERATECHANGED = 0;
            set @ORGANIZATIONRATECHANGED = 0;

            if @CHANGEDATE is null
                set @CHANGEDATE = getdate();

            select @ADJUSTMENTCOUNT = count(ID) 
            from dbo.ADJUSTMENT 
            where ADJUSTMENT.REVENUEID = @REVENUEID 

            select @ADJUSTMENTHISTORYID = ID, @ISNEWREVENUE = ISNEWREVENUE
            from dbo.ADJUSTMENTHISTORY
            where ADJUSTMENTID = @ADJUSTMENTID and REVENUEIDENTIFIER = cast(@REVENUEID as nvarchar(36));

            select @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
                ,@TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID
            from dbo.FINANCIALTRANSACTION FT
            inner join dbo.PDACCOUNTSYSTEM on FT.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
            inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
            where FT.ID = @REVENUEID;

            /*if this is a new adjustment, there will only be a header row (no details or distributions). Otherwise, it's an edit*/
            if (select count(ID) from dbo.ADJUSTMENTHISTORYDETAIL where ADJUSTMENTHISTORYID = @ADJUSTMENTHISTORYID) = 0 
                    and (select count(ID) from dbo.ADJUSTMENTHISTORYDISTRIBUTION where ADJUSTMENTHISTORYID = @ADJUSTMENTHISTORYID) = 0
                set @EDITINGADJUSTMENT = 0;
            else
                set @EDITINGADJUSTMENT = 1;

            select 
                @REVENUETYPECODE = TYPECODE
            from dbo.FINANCIALTRANSACTION 
            where ID = @REVENUEID;

            if @EDITINGADJUSTMENT = 1 
            begin
                /*If we're editing the adjustment, delete the previous report information so we can recreate it */
                delete from ADJUSTMENTHISTORYDETAIL where ADJUSTMENTHISTORYID = @ADJUSTMENTHISTORYID; 
            end

            /*Determine what changed by comparing the snapshot to the live data; Note that nothing will have changed for a "new revenue" adjustment since that adjustment hasn't posted yet*/
            if @ISNEWREVENUE = 0 and (select AMOUNT from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION where ID = @ADJUSTMENTHISTORYID) 
                    <> (select sum(FINANCIALTRANSACTION.BASEAMOUNT) from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null)
                set @AMOUNTCHANGED = 1;
            else
                set @AMOUNTCHANGED = 0;

            if @ISNEWREVENUE = 0 and (select PAYMENTMETHODCODE from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION where ID = @ADJUSTMENTHISTORYID) 
                    <> (select PAYMENTMETHODCODE from dbo.REVENUEPAYMENTMETHOD where REVENUEID = @REVENUEID)
                set @PAYMENTMETHODCHANGED = 1;
            else
                set @PAYMENTMETHODCHANGED = 0;

            if @ISNEWREVENUE = 0 and @REVENUETYPECODE = 0 /*only payments have revenue streams which can change */
                set @REVENUESTREAMSCHANGED = dbo.UFN_ADJUSTMENTHISTORY_STREAMSCHANGED(@ADJUSTMENTHISTORYID, @REVENUEID);
            else
                set @REVENUESTREAMSCHANGED = 0;

            if @ISNEWREVENUE = 0 and (select CONSTITUENTIDENTIFIER from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION where ID = @ADJUSTMENTHISTORYID)
                    <> (select FINANCIALTRANSACTION.CONSTITUENTID from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null)
                set @CONSTITUENTCHANGED = 1;
            else
                set @CONSTITUENTCHANGED = 0;

            if @ISNEWREVENUE = 0 and (select BASERATE from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION where ID = @ADJUSTMENTHISTORYID) 
                    <> (select BASERATE.RATE from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID left join dbo.CURRENCYEXCHANGERATE BASERATE on BASERATE.ID = FINANCIALTRANSACTION.BASEEXCHANGERATEID where FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null)
                set @BASERATECHANGED = 1;
            else
                set @BASERATECHANGED = 0;

            if @ISNEWREVENUE = 0 and (select ORGANIZATIONRATE from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION where ID = @ADJUSTMENTHISTORYID) 
                    <> (select ORGANIZATIONRATE.RATE from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONRATE on ORGANIZATIONRATE.ID = FINANCIALTRANSACTION.ORGEXCHANGERATEID where FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null)
                set @ORGANIZATIONRATECHANGED = 1;
            else
                set @ORGANIZATIONRATECHANGED = 0;

            /*Log the detail data for this adjustment*/
            if @AMOUNTCHANGED = 1
                insert into dbo.ADJUSTMENTHISTORYDETAIL(ADJUSTMENTHISTORYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISMONEY, ADJUSTEDFIELDPREVIOUSMONEYVALUE, ADJUSTEDFIELDADJUSTEDMONEYVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select 
                        @ADJUSTMENTHISTORYID,
                        'Amount',
                        T1.AMOUNT,
                        T3.BASEAMOUNT,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        1,
                        T1.AMOUNT,
                        T3.BASEAMOUNT,
                        T1.TRANSACTIONAMOUNT,
                        T3.TRANSACTIONAMOUNT,
                        T1.TRANSACTIONAMOUNT,
                        T3.TRANSACTIONAMOUNT,
                        T1.ORGANIZATIONAMOUNT,
                        T3.ORGAMOUNT,
                        T1.ORGANIZATIONAMOUNT,
                        T3.ORGAMOUNT,
                        isnull(T4.NONPOSTABLEBASECURRENCYID, @BASECURRENCYID),
                        T3.TRANSACTIONCURRENCYID
                    from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION T1
                        join dbo.ADJUSTMENTHISTORY T2 on T1.ID = T2.ID
                        inner join dbo.FINANCIALTRANSACTION T3 on T3.ID = @REVENUEID
                        inner join dbo.REVENUE_EXT T4 on T3.ID = T4.ID
                    where T1.ID = @ADJUSTMENTHISTORYID;

            if @CONSTITUENTCHANGED = 1
                insert into dbo.ADJUSTMENTHISTORYDETAIL(ADJUSTMENTHISTORYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                    select 
                        @ADJUSTMENTHISTORYID,
                        'Constituent',
                        dbo.UFN_CONSTITUENT_BUILDNAME(CONSTITUENTIDENTIFIER),
                        (select dbo.UFN_CONSTITUENT_BUILDNAME(FINANCIALTRANSACTION.CONSTITUENTID) from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null),
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        dbo.UFN_CONSTITUENT_BUILDNAME(CONSTITUENTIDENTIFIER),
                        (select dbo.UFN_CONSTITUENT_BUILDNAME(FINANCIALTRANSACTION.CONSTITUENTID) from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null),
                        dbo.UFN_CONSTITUENT_BUILDNAME(CONSTITUENTIDENTIFIER),
                        (select dbo.UFN_CONSTITUENT_BUILDNAME(FINANCIALTRANSACTION.CONSTITUENTID) from dbo.FINANCIALTRANSACTION inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID where FINANCIALTRANSACTION.ID = @REVENUEID and FINANCIALTRANSACTION.DELETEDON is null)
                    from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION
                    where ID = @ADJUSTMENTHISTORYID;

            if @PAYMENTMETHODCHANGED = 1
                insert into dbo.ADJUSTMENTHISTORYDETAIL(ADJUSTMENTHISTORYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                    select
                        @ADJUSTMENTHISTORYID,
                        'Payment method',
                        case when [SNAP].PAYMENTMETHODCODE = 0 then 'Cash'
                             when [SNAP].PAYMENTMETHODCODE = 1 then 'Check'
                             when [SNAP].PAYMENTMETHODCODE = 2 then 'Credit card'
                             when [SNAP].PAYMENTMETHODCODE = 3 then 'Direct debit'
                             when [SNAP].PAYMENTMETHODCODE = 4 then 'Stock'
                             when [SNAP].PAYMENTMETHODCODE = 5 then 'Property'
                             when [SNAP].PAYMENTMETHODCODE = 6 then 'Gift-in-kind'
                             when [SNAP].PAYMENTMETHODCODE = 9 then 'None'
                             when [SNAP].PAYMENTMETHODCODE = 10 then 'Other'
                             when [SNAP].PAYMENTMETHODCODE = 11 then 'Standing order'
                             when [SNAP].PAYMENTMETHODCODE = 101 then 'PayPal'
                             when [SNAP].PAYMENTMETHODCODE = 102 then 'Venmo'
                             else 'Unknown' end,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        case when [SNAP].PAYMENTMETHODCODE = 0 then 'Cash'
                             when [SNAP].PAYMENTMETHODCODE = 1 then 'Check'
                             when [SNAP].PAYMENTMETHODCODE = 2 then 'Credit card'
                             when [SNAP].PAYMENTMETHODCODE = 3 then 'Direct debit'
                             when [SNAP].PAYMENTMETHODCODE = 4 then 'Stock'
                             when [SNAP].PAYMENTMETHODCODE = 5 then 'Property'
                             when [SNAP].PAYMENTMETHODCODE = 6 then 'Gift-in-kind'
                             when [SNAP].PAYMENTMETHODCODE = 9 then 'None'
                             when [SNAP].PAYMENTMETHODCODE = 10 then 'Other'
                             when [SNAP].PAYMENTMETHODCODE = 11 then 'Standing order'
                             when [SNAP].PAYMENTMETHODCODE = 101 then 'PayPal'
                             when [SNAP].PAYMENTMETHODCODE = 102 then 'Venmo'
                             else 'Unknown' end,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD,
                        case when [SNAP].PAYMENTMETHODCODE = 0 then 'Cash'
                             when [SNAP].PAYMENTMETHODCODE = 1 then 'Check'
                             when [SNAP].PAYMENTMETHODCODE = 2 then 'Credit card'
                             when [SNAP].PAYMENTMETHODCODE = 3 then 'Direct debit'
                             when [SNAP].PAYMENTMETHODCODE = 4 then 'Stock'
                             when [SNAP].PAYMENTMETHODCODE = 5 then 'Property'
                             when [SNAP].PAYMENTMETHODCODE = 6 then 'Gift-in-kind'
                             when [SNAP].PAYMENTMETHODCODE = 9 then 'None'
                             when [SNAP].PAYMENTMETHODCODE = 10 then 'Other'
                             when [SNAP].PAYMENTMETHODCODE = 11 then 'Standing order'
                             when [SNAP].PAYMENTMETHODCODE = 101 then 'PayPal'
                             when [SNAP].PAYMENTMETHODCODE = 102 then 'Venmo'
                             else 'Unknown' end,
                        REVENUEPAYMENTMETHOD.PAYMENTMETHOD
                    from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION as [SNAP]
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = @REVENUEID
                    where [SNAP].ID = @ADJUSTMENTHISTORYID

            if @REVENUESTREAMSCHANGED = 1
                insert into dbo.ADJUSTMENTHISTORYDETAIL(ADJUSTMENTHISTORYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select 
                        @ADJUSTMENTHISTORYID,
                        'Applications',
                        dbo.UFN_ADJUSTMENTHISTORY_GETPREVIOUSSTREAMS_2(@ADJUSTMENTID, 0),
                        dbo.UFN_ADJUSTMENTHISTORY_GETADJUSTEDSTREAMS_2(@ADJUSTMENTID, 0),
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        dbo.UFN_ADJUSTMENTHISTORY_GETPREVIOUSSTREAMS_2(@ADJUSTMENTID, 2),
                        dbo.UFN_ADJUSTMENTHISTORY_GETADJUSTEDSTREAMS_2(@ADJUSTMENTID, 2),
                        dbo.UFN_ADJUSTMENTHISTORY_GETPREVIOUSSTREAMS_2(@ADJUSTMENTID, 1),
                        dbo.UFN_ADJUSTMENTHISTORY_GETADJUSTEDSTREAMS_2(@ADJUSTMENTID, 1),
                        @BASECURRENCYID,
                        @TRANSACTIONCURRENCYID;

            if @BASERATECHANGED = 1
            begin
                declare @OLDBASERATE decimal(20,8);
                select @OLDBASERATE = CURRENCYEXCHANGERATE.RATE 
                from dbo.FINANCIALTRANSACTION REVENUE
                left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = REVENUE.BASEEXCHANGERATEID 
                where REVENUE.ID = @REVENUEID                

                insert into dbo.ADJUSTMENTHISTORYDETAIL(ADJUSTMENTHISTORYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                    select 
                        @ADJUSTMENTHISTORYID,
                        'Base exchange rate',
                        ADJUSTMENTHISTORYREVENUETRANSACTION.BASERATE,
                        @OLDBASERATE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        ADJUSTMENTHISTORYREVENUETRANSACTION.BASERATE,
                        @OLDBASERATE,
                        ADJUSTMENTHISTORYREVENUETRANSACTION.BASERATE,
                        @OLDBASERATE
                    from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION
                    where ID = @ADJUSTMENTHISTORYID;
            end

            if @ORGANIZATIONRATECHANGED = 1
            begin
                declare @OLDORGANIZATIONRATE decimal(20,8);
                select @OLDORGANIZATIONRATE = CURRENCYEXCHANGERATE.RATE 
                from dbo.FINANCIALTRANSACTION
                    inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
                    left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = FINANCIALTRANSACTION.ORGEXCHANGERATEID
                where FINANCIALTRANSACTION.ID = @REVENUEID
                    and FINANCIALTRANSACTION.DELETEDON is null

                insert into dbo.ADJUSTMENTHISTORYDETAIL(ADJUSTMENTHISTORYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                    select 
                        @ADJUSTMENTHISTORYID,
                        'Organization exchange rate',
                        ADJUSTMENTHISTORYREVENUETRANSACTION.ORGANIZATIONRATE,
                        @OLDORGANIZATIONRATE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        ADJUSTMENTHISTORYREVENUETRANSACTION.ORGANIZATIONRATE,
                        @OLDORGANIZATIONRATE,
                        ADJUSTMENTHISTORYREVENUETRANSACTION.ORGANIZATIONRATE,
                        @OLDORGANIZATIONRATE
                    from dbo.ADJUSTMENTHISTORYREVENUETRANSACTION
                    where ID = @ADJUSTMENTHISTORYID;
            end

            if @ISNEWREVENUE = 0 --JamesWill No designations could have changed for a new revenue adjustment since it's not been posted yet

                exec dbo.USP_ADJUSTMENTHISTORY_REVENUE_WRITEDESIGNATIONS @ADJUSTMENTHISTORYID, @CHANGEAGENTID, @CHANGEDATE;

            /*Record the GL reversal and adjustment information */
            if @EDITINGADJUSTMENT = 0 /*if this is a new adjustment, store the reversal that will take place*/
            begin
                insert into dbo.ADJUSTMENTHISTORYDISTRIBUTION(ADJUSTMENTHISTORYID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select
                        @ADJUSTMENTHISTORYID,
                        0, /*Reversal*/
                        TRANSACTIONTYPECODE,
                        ACCOUNT,
                        PROJECT,
                        REFERENCE,
                        AMOUNT, 
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        TRANSACTIONAMOUNT,
                        ORGANIZATIONAMOUNT,
                        BASECURRENCYID,
                        TRANSACTIONCURRENCYID
                    from dbo.ADJUSTMENTHISTORYDISTRIBUTIONSNAPSHOT
                    where @ISNEWREVENUE = 0 --JamesWill Don't write reversals for new revenue adjustments because they haven't been posted yet and there's nothing to reverse!

                        and ADJUSTMENTHISTORYID = @ADJUSTMENTHISTORYID;

                /*JamesWill 03/12/2008 If this adjustment is for adding a new revenue record to an already posted transaction, some weird things happen. We need to write the GL data to snapshot table so the NEXT 
                    adjustment will be able to reference it in its history. But we need to do that after the previous statement so we don't write reversals to the report that never happened. So write it here. The easiest 
                    way to tell that this is the case is if we didn't in fact, write anything in the past statement since this is the one time that an adjustment won't trigger a reversal.*/
                if @ISNEWREVENUE = 1
                begin
                    --Delete any previous distributions that may have been stored (in case this adjustment is being edited)

                    delete from ADJUSTMENTHISTORYDISTRIBUTIONSNAPSHOT where ADJUSTMENTHISTORYID = @ADJUSTMENTHISTORYID;

                    insert into dbo.ADJUSTMENTHISTORYDISTRIBUTIONSNAPSHOT(ADJUSTMENTHISTORYID, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select
                        @ADJUSTMENTHISTORYID,
                        case JE.TRANSACTIONTYPECODE when 0 then 1 else 0 end,
                        coalesce(GLACCOUNT.ACCOUNTNUMBER,JEX.ACCOUNT,''),
                        JEX.PROJECT,
                        JE.COMMENT,
                        JE.BASEAMOUNT,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        JE.TRANSACTIONAMOUNT,
                        JE.ORGAMOUNT,
                        @BASECURRENCYID,
                        JE.TRANSACTIONCURRENCYID
                    from dbo.FINANCIALTRANSACTIONLINEITEM LI
                    inner join dbo.JOURNALENTRY JE on JE.FINANCIALTRANSACTIONLINEITEMID = LI.ID
                    inner join dbo.JOURNALENTRY_EXT JEX on JEX.ID = JE.ID
                    left join dbo.GLACCOUNT on GLACCOUNT.ID = JE.GLACCOUNTID
                    where LI.FINANCIALTRANSACTIONID = @REVENUEID
                        and LI.DELETEDON is null
                        and LI.TYPECODE != 1;
                end
            end

            if @ADJUSTMENTCOUNT > 1 --if this is not the first adjustment, we need to copy our reversal information to be the adjustment information for the last adjustment

            begin 
                select top 1 
                    @PREVIOUSADJUSTMENTID = ID 
                from dbo.ADJUSTMENT 
                where REVENUEID = @REVENUEID
                    and ID <> @ADJUSTMENTID 
                order by TSLONG desc;

                --If there is a previous adjustment, write that same reversal as the "adjusted" portion of the previous adjustment

                if not @PREVIOUSADJUSTMENTID is null
                begin
                    select 
                        @PREVIOUSADJUSTMENTHISTORYID = ID
                    from dbo.ADJUSTMENTHISTORY
                    where ADJUSTMENTID = @PREVIOUSADJUSTMENTID and ISNEWREVENUE = 0

                    if not @PREVIOUSADJUSTMENTHISTORYID is null
                    begin
                        --first delete the previous adjustment record

                        delete from dbo.ADJUSTMENTHISTORYDISTRIBUTION where ADJUSTMENTHISTORYID = @PREVIOUSADJUSTMENTHISTORYID and TYPECODE = 1;

                        --then insert the updated one

                        insert into dbo.ADJUSTMENTHISTORYDISTRIBUTION(ADJUSTMENTHISTORYID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                            select
                                @PREVIOUSADJUSTMENTHISTORYID,
                                1, --Adjustment

                                case TRANSACTIONTYPECODE when 0 then 1 else 0 end,
                                ACCOUNT,
                                PROJECT,
                                REFERENCE,
                                AMOUNT, 
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE,
                                TRANSACTIONAMOUNT,
                                ORGANIZATIONAMOUNT,
                                BASECURRENCYID,
                                TRANSACTIONCURRENCYID
                            from dbo.ADJUSTMENTHISTORYDISTRIBUTIONSNAPSHOT
                            where ADJUSTMENTHISTORYID = @ADJUSTMENTHISTORYID;

                    end
                end
            end