USP_ADJUSTMENTHISTORY_GIFTINKIND_FIXFORDELETE

            create procedure dbo.USP_ADJUSTMENTHISTORY_GIFTINKIND_FIXFORDELETE
            (
                @GIFTINKINDPAYMENTMETHODDETAILID uniqueidentifier,
                @UNSOLD bit,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime = null,
                @REVERSALADJUSTMENTHISTORYGIFTINKINDID uniqueidentifier = null output,
                @GIFTINKINDSALEID uniqueidentifier = null
            )
            as 
            set nocount on;

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

            declare @UNPOSTEDGIFTINKINDSALEADJUSTMENTID uniqueidentifier;
            declare @UNPOSTEDADJUSTMENTHISTORYGIFTINKINDID uniqueidentifier;
            declare @LASTPOSTEDGIFTINKINDSALEADJUSTMENTID uniqueidentifier;
            declare @LASTPOSTEDADJUSTMENTHISTORYGIFTINKINDID uniqueidentifier;

            /*make sure the sold gift-in-kind information is posted*/
            if exists(select top 1 ID from dbo.GIFTINKINDSALE where GIFTINKINDPAYMENTMETHODDETAILID = @GIFTINKINDPAYMENTMETHODDETAILID and SALEPOSTSTATUSCODE = 0 and (@GIFTINKINDSALEID is null or @GIFTINKINDSALEID = GIFTINKINDSALE.ID))
            begin

                /*determine if there is an unposted adjustment for this gift-in-kind */
                select 
                    @UNPOSTEDGIFTINKINDSALEADJUSTMENTID = GIFTINKINDSALEADJUSTMENT.ID
                from dbo.GIFTINKINDSALEADJUSTMENT
                inner join dbo.GIFTINKINDSALE on GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID = GIFTINKINDSALE.ID
                where 
                    GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @GIFTINKINDPAYMENTMETHODDETAILID and 
                    GIFTINKINDSALE.SALEPOSTSTATUSCODE = 1 and
                    (@GIFTINKINDSALEID is null or @GIFTINKINDSALEID = GIFTINKINDSALE.ID);

                select 
                    @UNPOSTEDADJUSTMENTHISTORYGIFTINKINDID = ID
                from dbo.ADJUSTMENTHISTORYGIFTINKIND
                where GIFTINKINDSALEADJUSTMENTID = @UNPOSTEDGIFTINKINDSALEADJUSTMENTID;

                /*get the most recent posted adjustment so we can write out GL history for it*/
                select top 1
                    @LASTPOSTEDGIFTINKINDSALEADJUSTMENTID = GIFTINKINDSALEADJUSTMENT.ID
                from dbo.GIFTINKINDSALEADJUSTMENT
                inner join dbo.GIFTINKINDSALE on GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID = GIFTINKINDSALE.ID
                where 
                    GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = @GIFTINKINDPAYMENTMETHODDETAILID and 
                    GIFTINKINDSALE.SALEPOSTSTATUSCODE = 0 and 
                    (@GIFTINKINDSALEID is null or @GIFTINKINDSALEID = GIFTINKINDSALE.ID)
                order by GIFTINKINDSALEADJUSTMENT.TSLONG desc;

                /*if there is an unposted adjustment, the last posted adjustment will already have distribution information in its history*/
                /*so do nothing*/
                /*write the current GL information to the history for the last posted adjustment only if there was not an adjustment after it*/

                if @UNPOSTEDGIFTINKINDSALEADJUSTMENTID is null and not @LASTPOSTEDGIFTINKINDSALEADJUSTMENTID is null
                begin
                    select @LASTPOSTEDADJUSTMENTHISTORYGIFTINKINDID = ID
                    from dbo.ADJUSTMENTHISTORYGIFTINKIND
                    where GIFTINKINDSALEADJUSTMENTID = @LASTPOSTEDGIFTINKINDSALEADJUSTMENTID;

                    if not @LASTPOSTEDADJUSTMENTHISTORYGIFTINKINDID is null
                        insert into dbo.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTION(ADJUSTMENTHISTORYGIFTINKINDID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                            select
                                @LASTPOSTEDADJUSTMENTHISTORYGIFTINKINDID,
                                1, --'Adjustment'

                                TRANSACTIONTYPECODE,
                                ACCOUNT,
                                PROJECT,
                                '', --The function does not return a reference

                                AMOUNT,
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CHANGEDATE,
                                @CHANGEDATE,
                                TRANSACTIONAMOUNT,
                                ORGANIZATIONAMOUNT,
                                BASECURRENCYID, 
                                TRANSACTIONCURRENCYID
                            from dbo.UFN_REVENUE_GETGIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION(@GIFTINKINDPAYMENTMETHODDETAILID);
                end

                /*log the history header for the reversal*/
                set @REVERSALADJUSTMENTHISTORYGIFTINKINDID = newid();

                insert into dbo.ADJUSTMENTHISTORYGIFTINKIND(ID, GIFTINKINDSALEADJUSTMENTID, GIFTINKINDPAYMENTMETHODDETAILIDENTIFIER, ADJUSTMENTIDENTIFIER, CONSTITUENTNAME, REVENUEDATE, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        @REVERSALADJUSTMENTHISTORYGIFTINKINDID,
                        null,
                        cast(@GIFTINKINDPAYMENTMETHODDETAILID as nvarchar(36)),
                        cast(newid() as nvarchar(36)), --This is just something to group by

                        CONSTITUENT.NAME,
                        REVENUE.DATE, --This is the postdate of the reversal that got logged, so we'll use it too

                        @CHANGEDATE,
                        dbo.UFN_DATE_GETEARLIESTTIME(@CHANGEDATE), --The adjustment post date needs to be the date of the deletion

                        '',
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE
                    from dbo.REVENUEPAYMENTMETHOD
                    inner join dbo.REVENUE on dbo.REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    where REVENUEPAYMENTMETHOD.ID = @GIFTINKINDPAYMENTMETHODDETAILID; --This works because the PK for GIFTINKINDPAYMENTMETHODDETAIL is an FK to REVENUE


                insert into dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAIL(ID, ADJUSTMENTHISTORYGIFTINKINDID, ADJUSTEDFIELD,ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                    select
                        newid(), 
                        @REVERSALADJUSTMENTHISTORYGIFTINKINDID, 
                        case when @UNSOLD = 1 then 'Unsold' else 'Deleted' end,
                        '', 
                        '', 
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CHANGEDATE, 
                        @CHANGEDATE,
                        '',
                        '',
                        '',
                        '';

                /*if there's not an unposted adjustment, the reversal for the deletion will be the calculated GL information*/
                if @UNPOSTEDGIFTINKINDSALEADJUSTMENTID is null
                    insert into dbo.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTION(ADJUSTMENTHISTORYGIFTINKINDID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                        select
                            @REVERSALADJUSTMENTHISTORYGIFTINKINDID,
                            0, --'Reversal'

                            case TRANSACTIONTYPECODE when 0 then 1 else 0 end,
                            ACCOUNT,
                            PROJECT,
                            '', --The function does not return a reference

                            AMOUNT,
                            @CHANGEAGENTID,
                            @CHANGEAGENTID,
                            @CHANGEDATE,
                            @CHANGEDATE,
                            TRANSACTIONAMOUNT,
                            ORGANIZATIONAMOUNT,
                            BASECURRENCYID,
                            TRANSACTIONCURRENCYID
                        from dbo.UFN_REVENUE_GETGIFTINKINDPAYMENTMETHODDETAILGLDISTRIBUTION(@GIFTINKINDPAYMENTMETHODDETAILID)
                        where @GIFTINKINDSALEID is null or GIFTINKINDSALEID = @GIFTINKINDSALEID;

                else /*otherwise, the reversal will be the reversal from the unposted adjustment */
                    update dbo.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTION
                        set ADJUSTMENTHISTORYGIFTINKINDID = @REVERSALADJUSTMENTHISTORYGIFTINKINDID
                    where ADJUSTMENTHISTORYGIFTINKINDID = @UNPOSTEDADJUSTMENTHISTORYGIFTINKINDID and TYPECODE = 0 --We only want the reversals

                /*if it exists, delete the unposted adjustment history. Since this will never post, it will never interact with the GL
                and we don't need to remember it.*/

                declare @CONTEXTCACHE varbinary(128);
                set @CONTEXTCACHE = CONTEXT_INFO();

                if not @CHANGEAGENTID is null
                    set CONTEXT_INFO @CHANGEAGENTID;

                if not @UNPOSTEDGIFTINKINDSALEADJUSTMENTID is null /*ADJUSTMENTGIFTINKINDHISTORY.GIFTINKINDSALEADJUSTMENTID is allowed to be null, so check first*/
                    delete from dbo.ADJUSTMENTHISTORYGIFTINKIND where GIFTINKINDSALEADJUSTMENTID = @UNPOSTEDGIFTINKINDSALEADJUSTMENTID

                if not @CONTEXTCACHE is null
                    set CONTEXT_INFO @CONTEXTCACHE;
            end