USP_ADJUSTMENTHISTORY_STOCK_SAVESNAPSHOT

            CREATE procedure dbo.USP_ADJUSTMENTHISTORY_STOCK_SAVESNAPSHOT
            (
                @STOCKSALEADJUSTMENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier
            )
            as
            set nocount on;

            declare @CHANGEDATE datetime;
            declare @STOCKSALEID uniqueidentifier;
            declare @ADJUSTMENTHISTORYSTOCKID uniqueidentifier;
            declare @SNAPSHOTEXISTS bit;

            set @CHANGEDATE = getdate();
            set @SNAPSHOTEXISTS = 0;

            /*determine if a snapshot exists*/
            if (select count(ADJUSTMENTHISTORYSTOCKSNAPSHOT.ID) 
                from dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT
                inner join dbo.ADJUSTMENTHISTORYSTOCK on ADJUSTMENTHISTORYSTOCK.ID = ADJUSTMENTHISTORYSTOCKSNAPSHOT.ID
                where ADJUSTMENTHISTORYSTOCK.STOCKSALEADJUSTMENTID = @STOCKSALEADJUSTMENTID) > 0
                set @SNAPSHOTEXISTS = 1;

            if @SNAPSHOTEXISTS = 1 /*update the ADJUSTMENTHISTORYSTOCK record to match STOCKSALEADJUSTMENT */
            begin
                select @ADJUSTMENTHISTORYSTOCKID = ID
                from dbo.ADJUSTMENTHISTORYSTOCK
                where STOCKSALEADJUSTMENTID = @STOCKSALEADJUSTMENTID;

                if @STOCKSALEADJUSTMENTID is not null
                    update dbo.ADJUSTMENTHISTORYSTOCK set
                        ADJUSTMENTHISTORYSTOCK.CONSTITUENTNAME = CONSTITUENT.NAME,
                        ADJUSTMENTHISTORYSTOCK.REVENUEDATE = REVENUE.DATE,
                        ADJUSTMENTHISTORYSTOCK.ADJUSTMENTDATE = STOCKSALEADJUSTMENT.DATE,
                        ADJUSTMENTHISTORYSTOCK.ADJUSTMENTPOSTDATE = STOCKSALEADJUSTMENT.POSTDATE,
                        ADJUSTMENTHISTORYSTOCK.ADJUSTMENTREASON = STOCKSALEADJUSTMENT.REASON,
                        ADJUSTMENTHISTORYSTOCK.ADJUSTMENTREASONCODEID = STOCKSALEADJUSTMENT.REASONCODEID,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CHANGEDATE
                    from dbo.ADJUSTMENTHISTORYSTOCK
                    inner join dbo.STOCKSALEADJUSTMENT on STOCKSALEADJUSTMENT.ID = ADJUSTMENTHISTORYSTOCK.STOCKSALEADJUSTMENTID
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = STOCKSALEADJUSTMENT.STOCKSALEID
                    inner join dbo.REVENUE on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID 
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    where STOCKSALEADJUSTMENT.ID = @STOCKSALEADJUSTMENTID;
            end
            else
            begin
                /*since the snapshot has an FK ADJUSTMENTHISTORYSTOCK, need to create an ADJUSTMENTHISTORYSTOCK row. The needed information is 
                attached to the STOCKSALEADJUSTMENT record*/

                select
                    @STOCKSALEID = STOCKSALEADJUSTMENT.STOCKSALEID
                from dbo.STOCKSALEADJUSTMENT 
                where ID = @STOCKSALEADJUSTMENTID;

                set @ADJUSTMENTHISTORYSTOCKID = newid();

                insert into dbo.ADJUSTMENTHISTORYSTOCK(ID, STOCKSALEADJUSTMENTID, STOCKDETAILIDENTIFIER, ADJUSTMENTIDENTIFIER, CONSTITUENTNAME, REVENUEDATE, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADJUSTMENTREASONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select top 1
                        @ADJUSTMENTHISTORYSTOCKID,
                        STOCKSALEADJUSTMENT.ID,
                        cast(STOCKSALEADJUSTMENT.STOCKSALEID as nvarchar(36)),
                        cast(STOCKSALEADJUSTMENT.ID as nvarchar(36)),
                        CONSTITUENT.NAME,
                        REVENUE.DATE,
                        STOCKSALEADJUSTMENT.DATE,
                        STOCKSALEADJUSTMENT.POSTDATE,
                        STOCKSALEADJUSTMENT.REASON,
                        STOCKSALEADJUSTMENT.REASONCODEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE
                    from dbo.STOCKSALEADJUSTMENT
                    inner join dbo.STOCKSALE on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = STOCKSALE.STOCKDETAILID
                    inner join dbo.REVENUE on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    where STOCKSALEADJUSTMENT.ID = @STOCKSALEADJUSTMENTID;

                /*create the snapshot of the stock detail information*/
                insert into dbo.ADJUSTMENTHISTORYSTOCKSNAPSHOT(ID, SALEAMOUNT, BROKERFEE, CONSTITUENTIDENTIFIER, NUMBEROFUNITS, PAYMENTNUMBEROFUNITS, PAYMENTMEDIANPRICE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONSALEAMOUNT, TRANSACTIONBROKERFEE, TRANSACTIONPAYMENTMEDIANPRICE, ORGANIZATIONSALEAMOUNT, ORGANIZATIONBROKERFEE, ORGANIZATIONPAYMENTMEDIANPRICE, BASECURRENCYID, TRANSACTIONCURRENCYID, BASERATE, ORGANIZATIONRATE)
                    select
                        @ADJUSTMENTHISTORYSTOCKID,
                        STOCKSALE.SALEAMOUNT,
                        STOCKSALE.FEE,
                        REVENUE.CONSTITUENTID,
                        STOCKSALE.NUMBEROFUNITS,
                        STOCKDETAIL.NUMBEROFUNITS,
                        STOCKDETAIL.MEDIANPRICE,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        STOCKSALE.TRANSACTIONSALEAMOUNT,
                        STOCKSALE.TRANSACTIONFEE,
                        STOCKDETAIL.TRANSACTIONMEDIANPRICE,
                        STOCKSALE.ORGANIZATIONSALEAMOUNT,
                        STOCKSALE.ORGANIZATIONFEE,
                        STOCKDETAIL.ORGANIZATIONMEDIANPRICE,
                        STOCKSALE.BASECURRENCYID,
                        STOCKSALE.TRANSACTIONCURRENCYID,
                        coalesce(BASERATE.RATE,0),
                        coalesce(ORGANIZATIONRATE.RATE,0)
                    from dbo.STOCKSALE
                    inner join dbo.STOCKSALEADJUSTMENT on STOCKSALEADJUSTMENT.STOCKSALEID = STOCKSALE.ID
                    inner join dbo.STOCKDETAIL on STOCKSALE.STOCKDETAILID = STOCKDETAIL.ID
                    inner join dbo.REVENUEPAYMENTMETHOD on STOCKSALE.STOCKDETAILID = REVENUEPAYMENTMETHOD.ID
                    inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                    left join dbo.CURRENCYEXCHANGERATE BASERATE on BASERATE.ID = STOCKSALE.BASEEXCHANGERATEID
                    left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONRATE on ORGANIZATIONRATE.ID = STOCKSALE.ORGANIZATIONEXCHANGERATEID
                    where STOCKSALEADJUSTMENT.ID = @STOCKSALEADJUSTMENTID;

                /*create the distribution snapshot*/
                /*JamesWill CR254668-091306 10/02/2006 Reverse the debit and credit accounts to actually capture a reversal*/
                insert into dbo.ADJUSTMENTHISTORYSTOCKDISTRIBUTIONSNAPSHOT(ADJUSTMENTHISTORYSTOCKID, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
                    select
                        @ADJUSTMENTHISTORYSTOCKID,
                        case TRANSACTIONTYPECODE when 0 then 1 else 0 end,
                        ACCOUNT,
                        PROJECT,
                        REFERENCE,
                        AMOUNT,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        TRANSACTIONAMOUNT,
                        ORGANIZATIONAMOUNT,
                        BASECURRENCYID,
                        TRANSACTIONCURRENCYID
                    from dbo.STOCKSALEGLDISTRIBUTION
                    where STOCKSALEID = @STOCKSALEID
                    and STOCKSALEGLDISTRIBUTION.OUTDATED = 0;

            end