USP_ADJUSTMENTHISTORY_PROPERTY_SAVESNAPSHOT

            CREATE procedure dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVESNAPSHOT
            (
                @PROPERTYDETAILADJUSTMENTID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier
            )
            as
            set nocount on;

            declare @CHANGEDATE datetime;
            declare @PROPERTYDETAILID uniqueidentifier;
            declare @ADJUSTMENTHISTORYPROPERTYID uniqueidentifier;
            declare @SNAPSHOTEXISTS bit;

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

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

            if @SNAPSHOTEXISTS = 1 /*update the ADJUSTMENTHISTORYPROPERTY record to match PROPERTYDETAILADJUSTMENT */
            begin
                select @ADJUSTMENTHISTORYPROPERTYID = ID
                from dbo.ADJUSTMENTHISTORYPROPERTY
                where PROPERTYDETAILADJUSTMENTID = @PROPERTYDETAILADJUSTMENTID;

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

                select
                    @PROPERTYDETAILID = PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID
                from dbo.PROPERTYDETAILADJUSTMENT 
                where ID = @PROPERTYDETAILADJUSTMENTID;

                set @ADJUSTMENTHISTORYPROPERTYID = newid();

                insert into dbo.ADJUSTMENTHISTORYPROPERTY(ID, PROPERTYDETAILADJUSTMENTID, PROPERTYDETAILIDENTIFIER, ADJUSTMENTIDENTIFIER, CONSTITUENTNAME, REVENUEDATE, ADJUSTMENTDATE, ADJUSTMENTPOSTDATE, ADJUSTMENTREASON, ADJUSTMENTREASONCODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select top 1
                        @ADJUSTMENTHISTORYPROPERTYID,
                        PROPERTYDETAILADJUSTMENT.ID,
                        cast(PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID as nvarchar(36)),
                        cast(PROPERTYDETAILADJUSTMENT.ID as nvarchar(36)),
                        CONSTITUENT.NAME,
                        REVENUE.DATE,
                        PROPERTYDETAILADJUSTMENT.DATE,
                        PROPERTYDETAILADJUSTMENT.POSTDATE,
                        PROPERTYDETAILADJUSTMENT.REASON,
                        PROPERTYDETAILADJUSTMENT.REASONCODEID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE
                    from dbo.PROPERTYDETAILADJUSTMENT
                    inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID
                    inner join dbo.REVENUE on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
                    inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
                    where PROPERTYDETAILADJUSTMENT.ID = @PROPERTYDETAILADJUSTMENTID;

                /*create the snapshot of the PROPERTY detail information*/
                insert into dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT(ID, SALEAMOUNT, BROKERFEE, CONSTITUENTIDENTIFIER, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONSALEAMOUNT, TRANSACTIONBROKERFEE, ORGANIZATIONSALEAMOUNT, ORGANIZATIONBROKERFEE, BASECURRENCYID, TRANSACTIONCURRENCYID, BASERATE, ORGANIZATIONRATE)
                    select
                        @ADJUSTMENTHISTORYPROPERTYID,
                        PROPERTYDETAIL.SALEAMOUNT,
                        PROPERTYDETAIL.BROKERFEE,
                        REVENUE.CONSTITUENTID,
                        @CHANGEAGENTID,
                        @CHANGEAGENTID,
                        @CHANGEDATE,
                        @CHANGEDATE,
                        PROPERTYDETAIL.TRANSACTIONSALEAMOUNT,
                        PROPERTYDETAIL.TRANSACTIONBROKERFEE,
                        PROPERTYDETAIL.ORGANIZATIONSALEAMOUNT,
                        PROPERTYDETAIL.ORGANIZATIONBROKERFEE,
                        PROPERTYDETAIL.BASECURRENCYID,
                        PROPERTYDETAIL.TRANSACTIONCURRENCYID,
                        coalesce(BASERATE.RATE,0),
                        coalesce(ORGANIZATIONRATE.RATE,0)
                    from dbo.PROPERTYDETAIL
                    inner join dbo.PROPERTYDETAILADJUSTMENT on PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = PROPERTYDETAIL.ID
                    inner join dbo.REVENUEPAYMENTMETHOD on PROPERTYDETAIL.ID = REVENUEPAYMENTMETHOD.ID
                    inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
                    left join dbo.CURRENCYEXCHANGERATE BASERATE on BASERATE.ID = PROPERTYDETAIL.BASEEXCHANGERATEID
                    left join dbo.CURRENCYEXCHANGERATE ORGANIZATIONRATE on ORGANIZATIONRATE.ID = PROPERTYDETAIL.ORGANIZATIONEXCHANGERATEID
                    where PROPERTYDETAILADJUSTMENT.ID = @PROPERTYDETAILADJUSTMENTID;

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

            end