USP_ADJUSTMENTHISTORY_STOCK_FIXFORDELETE

            CREATE procedure dbo.USP_ADJUSTMENTHISTORY_STOCK_FIXFORDELETE
            (
                @STOCKDETAILID uniqueidentifier,
                @UNSOLD bit,
                @CHANGEAGENTID uniqueidentifier,
                @CHANGEDATE datetime = null,
                @REVERSALADJUSTMENTHISTORYSTOCKID uniqueidentifier = null output,
                @STOCKSALEID uniqueidentifier = null
            )
            as 
            set nocount on;

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

            declare @UNPOSTEDSTOCKSALEADJUSTMENTID uniqueidentifier;
            declare @UNPOSTEDADJUSTMENTHISTORYSTOCKID uniqueidentifier;
            declare @LASTPOSTEDSTOCKSALEADJUSTMENTID uniqueidentifier;
            declare @LASTPOSTEDADJUSTMENTHISTORYSTOCKID uniqueidentifier;


            /*make sure the sold stock information is posted*/
            if exists(select top 1 ID from dbo.STOCKSALE where STOCKDETAILID = @STOCKDETAILID and SALEPOSTSTATUSCODE = 0 and (@STOCKSALEID is null or @STOCKSALEID = STOCKSALE.ID))
            begin

                /*determine if there is an unposted adjustment for this stock */
                select 
                    @UNPOSTEDSTOCKSALEADJUSTMENTID = STOCKSALEADJUSTMENT.ID
                from dbo.STOCKSALEADJUSTMENT
                inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
                where 
                    STOCKSALE.STOCKDETAILID = @STOCKDETAILID and 
                    STOCKSALE.SALEPOSTSTATUSCODE = 1 and
                    (@STOCKSALEID is null or @STOCKSALEID = STOCKSALE.ID);

                select 
                    @UNPOSTEDADJUSTMENTHISTORYSTOCKID = ID
                from dbo.ADJUSTMENTHISTORYSTOCK
                where STOCKSALEADJUSTMENTID = @UNPOSTEDSTOCKSALEADJUSTMENTID;

                /*get the most recent posted adjustment so we can write out GL history for it*/
                select top 1
                    @LASTPOSTEDSTOCKSALEADJUSTMENTID = STOCKSALEADJUSTMENT.ID
                from dbo.STOCKSALEADJUSTMENT
                inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
                where 
                    STOCKSALE.STOCKDETAILID = @STOCKDETAILID and 
                    STOCKSALE.SALEPOSTSTATUSCODE = 0 and 
                    (@STOCKSALEID is null or @STOCKSALEID = STOCKSALE.ID)
                order by STOCKSALEADJUSTMENT.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 @UNPOSTEDSTOCKSALEADJUSTMENTID is null and not @LASTPOSTEDSTOCKSALEADJUSTMENTID is null
                begin
                    select @LASTPOSTEDADJUSTMENTHISTORYSTOCKID = ID
                    from dbo.ADJUSTMENTHISTORYSTOCK
                    where STOCKSALEADJUSTMENTID = @LASTPOSTEDSTOCKSALEADJUSTMENTID;

                    if not @LASTPOSTEDADJUSTMENTHISTORYSTOCKID is null
                        insert into dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTION(ADJUSTMENTHISTORYSTOCKID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                            select
                                @LASTPOSTEDADJUSTMENTHISTORYSTOCKID,
                                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_GETSTOCKDETAILGLDISTRIBUTION(@STOCKDETAILID);
                end

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

                insert into dbo.ADJUSTMENTHISTORYSTOCK(ID, STOCKSALEADJUSTMENTID, STOCKDETAILIDENTIFIER, ADJUSTMENTIDENTIFIER, CONSTITUENTNAME, REVENUEDATE, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        @REVERSALADJUSTMENTHISTORYSTOCKID,
                        null,
                        cast(@STOCKDETAILID 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 = @STOCKDETAILID; --This works because the PK for STOCKDETAIL is an FK to REVENUE


                insert into dbo.ADJUSTMENTHISTORYSTOCKDETAIL(ID, ADJUSTMENTHISTORYSTOCKID, ADJUSTEDFIELD,ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
                    select
                        newid(), 
                        @REVERSALADJUSTMENTHISTORYSTOCKID, 
                        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 @UNPOSTEDSTOCKSALEADJUSTMENTID is null
                    insert into dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTION(ADJUSTMENTHISTORYSTOCKID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                        select
                            @REVERSALADJUSTMENTHISTORYSTOCKID,
                            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_GETSTOCKDETAILGLDISTRIBUTION(@STOCKDETAILID)
                        where @STOCKSALEID is null or STOCKSALEID = @STOCKSALEID;

                else /*otherwise, the reversal will be the reversal from the unposted adjustment */
                    update dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTION
                        set ADJUSTMENTHISTORYSTOCKID = @REVERSALADJUSTMENTHISTORYSTOCKID
                    where ADJUSTMENTHISTORYSTOCKID = @UNPOSTEDADJUSTMENTHISTORYSTOCKID 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 @UNPOSTEDSTOCKSALEADJUSTMENTID is null /*ADJUSTMENTSTOCKHISTORY.STOCKSALEADJUSTMENTID is allowed to be null, so check first*/
                    delete from dbo.ADJUSTMENTHISTORYSTOCK where STOCKSALEADJUSTMENTID = @UNPOSTEDSTOCKSALEADJUSTMENTID

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