USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY
Store historical sold property adjustment information for reporting purposes.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PROPERTYDETAILID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @PROPERTYDETAILADJUSTMENTID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_ADJUSTMENTHISTORY_PROPERTY_SAVEHISTORY
(
@PROPERTYDETAILID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CHANGEDATE datetime,
@PROPERTYDETAILADJUSTMENTID uniqueidentifier
)
as
set nocount on;
/*Before calling this procedure, be sure that you call USP_SAVE_PROPERTYDETAILADJUSTMENT and then save changes to the PROPERTYDETAIL table*/
declare @ADJUSTMENTCOUNT int;
declare @PREVIOUSPROPERTYDETAILADJUSTMENTID uniqueidentifier;
declare @PREVIOUSADJUSTMENTHISTORYPROPERTYID uniqueidentifier;
declare @ADJUSTMENTHISTORYPROPERTYID uniqueidentifier;
declare @SALEAMOUNTCHANGED bit;
declare @BROKERFEECHANGED bit;
declare @CONSTITUENTCHANGED bit;
declare @BASERATECHANGED bit;
declare @ORGANIZATIONRATECHANGED bit;
declare @EDITINGADJUSTMENT bit;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
select @ADJUSTMENTCOUNT = count(ID)
from dbo.PROPERTYDETAILADJUSTMENT
where PROPERTYDETAILADJUSTMENT.PROPERTYDETAILID = @PROPERTYDETAILID;
select @ADJUSTMENTHISTORYPROPERTYID = ID
from dbo.ADJUSTMENTHISTORYPROPERTY
where PROPERTYDETAILADJUSTMENTID = @PROPERTYDETAILADJUSTMENTID
/*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.ADJUSTMENTHISTORYPROPERTYDETAIL where ADJUSTMENTHISTORYPROPERTYID = @ADJUSTMENTHISTORYPROPERTYID) = 0
and (select count(ID) from dbo.ADJUSTMENTHISTORYPROPERTYDISTRIBUTION where ADJUSTMENTHISTORYPROPERTYID = @ADJUSTMENTHISTORYPROPERTYID) = 0
set @EDITINGADJUSTMENT = 0;
else
set @EDITINGADJUSTMENT = 1;
if @EDITINGADJUSTMENT = 1
begin
/*if we're editing the adjustment, delete the previous report information so we can recreate it*/
delete from ADJUSTMENTHISTORYPROPERTYDETAIL where ADJUSTMENTHISTORYPROPERTYID = @ADJUSTMENTHISTORYPROPERTYID;
end
/*determine what changed by comparing the snapshot to the live data*/
if (select SALEAMOUNT from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT where ID = @ADJUSTMENTHISTORYPROPERTYID)
<> (select SALEAMOUNT from dbo.PROPERTYDETAIL where ID = @PROPERTYDETAILID)
set @SALEAMOUNTCHANGED = 1;
else
set @SALEAMOUNTCHANGED = 0;
if (select BROKERFEE from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT where ID = @ADJUSTMENTHISTORYPROPERTYID)
<> (select BROKERFEE from dbo.PROPERTYDETAIL where ID = @PROPERTYDETAILID)
set @BROKERFEECHANGED = 1;
else
set @BROKERFEECHANGED = 0;
if (select CONSTITUENTIDENTIFIER from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT where ID = @ADJUSTMENTHISTORYPROPERTYID)
<> (select REVENUE.CONSTITUENTID from dbo.REVENUE inner join dbo.REVENUEPAYMENTMETHOD on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID where REVENUEPAYMENTMETHOD.ID = @PROPERTYDETAILID)
set @CONSTITUENTCHANGED = 1;
else
set @CONSTITUENTCHANGED = 0;
if (select BASERATE from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT where ID = @ADJUSTMENTHISTORYPROPERTYID)
<> (select CURRENCYEXCHANGERATE.RATE from dbo.PROPERTYDETAIL left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = PROPERTYDETAIL.BASEEXCHANGERATEID where PROPERTYDETAIL.ID = @PROPERTYDETAILID)
set @BASERATECHANGED = 1;
else
set @BASERATECHANGED = 0;
if (select ORGANIZATIONRATE from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT where ID = @ADJUSTMENTHISTORYPROPERTYID)
<> (select CURRENCYEXCHANGERATE.RATE from dbo.PROPERTYDETAIL left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = PROPERTYDETAIL.ORGANIZATIONEXCHANGERATEID where PROPERTYDETAIL.ID = @PROPERTYDETAILID)
set @ORGANIZATIONRATECHANGED = 1;
else
set @ORGANIZATIONRATECHANGED = 0;
/*Log the detail for this adjustment*/
if @SALEAMOUNTCHANGED = 1
insert into dbo.ADJUSTMENTHISTORYPROPERTYDETAIL(ADJUSTMENTHISTORYPROPERTYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISMONEY, ADJUSTEDFIELDPREVIOUSMONEYVALUE, ADJUSTEDFIELDADJUSTEDMONEYVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYPROPERTYID,
'Sale amount',
[SNAP].SALEAMOUNT,
[REAL].SALEAMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
1,
[SNAP].SALEAMOUNT,
[REAL].SALEAMOUNT,
[SNAP].TRANSACTIONSALEAMOUNT,
[REAL].TRANSACTIONSALEAMOUNT,
[SNAP].TRANSACTIONSALEAMOUNT,
[REAL].TRANSACTIONSALEAMOUNT,
[SNAP].ORGANIZATIONSALEAMOUNT,
[REAL].ORGANIZATIONSALEAMOUNT,
[SNAP].ORGANIZATIONSALEAMOUNT,
[REAL].ORGANIZATIONSALEAMOUNT,
[REAL].BASECURRENCYID,
[REAL].TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT as [SNAP]
inner join dbo.PROPERTYDETAIL as [REAL] on [REAL].ID = @PROPERTYDETAILID
where [SNAP].ID = @ADJUSTMENTHISTORYPROPERTYID
if @BROKERFEECHANGED = 1
insert into dbo.ADJUSTMENTHISTORYPROPERTYDETAIL(ADJUSTMENTHISTORYPROPERTYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISMONEY, ADJUSTEDFIELDPREVIOUSMONEYVALUE, ADJUSTEDFIELDADJUSTEDMONEYVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYPROPERTYID,
'Fees',
[SNAP].BROKERFEE,
[REAL].BROKERFEE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
1,
[SNAP].BROKERFEE,
[REAL].BROKERFEE,
[SNAP].TRANSACTIONBROKERFEE,
[REAL].TRANSACTIONBROKERFEE,
[SNAP].TRANSACTIONBROKERFEE,
[REAL].TRANSACTIONBROKERFEE,
[SNAP].ORGANIZATIONBROKERFEE,
[REAL].ORGANIZATIONBROKERFEE,
[SNAP].ORGANIZATIONBROKERFEE,
[REAL].ORGANIZATIONBROKERFEE,
[REAL].BASECURRENCYID,
[REAL].TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT as [SNAP]
inner join dbo.PROPERTYDETAIL as [REAL] on [REAL].ID = @PROPERTYDETAILID
where [SNAP].ID = @ADJUSTMENTHISTORYPROPERTYID
if @CONSTITUENTCHANGED = 1
insert into dbo.ADJUSTMENTHISTORYPROPERTYDETAIL(ADJUSTMENTHISTORYPROPERTYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYPROPERTYID,
'Constituent',
dbo.UFN_CONSTITUENT_BUILDNAME([SNAP].CONSTITUENTIDENTIFIER),
dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID),
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
dbo.UFN_CONSTITUENT_BUILDNAME([SNAP].CONSTITUENTIDENTIFIER),
dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID),
dbo.UFN_CONSTITUENT_BUILDNAME([SNAP].CONSTITUENTIDENTIFIER),
dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID)
from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT as [SNAP]
inner join dbo.PROPERTYDETAIL as [REAL] on [REAL].ID = @PROPERTYDETAILID
inner join dbo.REVENUEPAYMENTMETHOD on [REAL].ID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
where [SNAP].ID = @ADJUSTMENTHISTORYPROPERTYID
if @BASERATECHANGED = 1
begin
declare @OLDBASERATE decimal(20,8);
select @OLDBASERATE = CURRENCYEXCHANGERATE.RATE
from dbo.PROPERTYDETAIL
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = PROPERTYDETAIL.BASEEXCHANGERATEID
where PROPERTYDETAIL.ID = @PROPERTYDETAILID
insert into dbo.ADJUSTMENTHISTORYPROPERTYDETAIL(ADJUSTMENTHISTORYPROPERTYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYPROPERTYID,
'Base exchange rate',
ADJUSTMENTHISTORYPROPERTYSNAPSHOT.BASERATE,
@OLDBASERATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
ADJUSTMENTHISTORYPROPERTYSNAPSHOT.BASERATE,
@OLDBASERATE,
ADJUSTMENTHISTORYPROPERTYSNAPSHOT.BASERATE,
@OLDBASERATE
from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT
where ID = @ADJUSTMENTHISTORYPROPERTYID;
end
if @ORGANIZATIONRATECHANGED = 1
begin
declare @OLDORGANIZATIONRATE decimal(20,8);
select @OLDORGANIZATIONRATE = CURRENCYEXCHANGERATE.RATE
from dbo.PROPERTYDETAIL
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = PROPERTYDETAIL.ORGANIZATIONEXCHANGERATEID
where PROPERTYDETAIL.ID = @PROPERTYDETAILID
insert into dbo.ADJUSTMENTHISTORYPROPERTYDETAIL(ADJUSTMENTHISTORYPROPERTYID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYPROPERTYID,
'Organization exchange rate',
ADJUSTMENTHISTORYPROPERTYSNAPSHOT.ORGANIZATIONRATE,
@OLDORGANIZATIONRATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
ADJUSTMENTHISTORYPROPERTYSNAPSHOT.ORGANIZATIONRATE,
@OLDORGANIZATIONRATE,
ADJUSTMENTHISTORYPROPERTYSNAPSHOT.ORGANIZATIONRATE,
@OLDORGANIZATIONRATE
from dbo.ADJUSTMENTHISTORYPROPERTYSNAPSHOT
where ID = @ADJUSTMENTHISTORYPROPERTYID;
end
/*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.ADJUSTMENTHISTORYPROPERTYDISTRIBUTION(ADJUSTMENTHISTORYPROPERTYID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYPROPERTYID,
0, /*Reversal*/
TRANSACTIONTYPECODE,
ACCOUNT,
PROJECT,
REFERENCE,
AMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT,
BASECURRENCYID,
TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYPROPERTYDISTRIBUTIONSNAPSHOT
where ADJUSTMENTHISTORYPROPERTYID = @ADJUSTMENTHISTORYPROPERTYID;
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
@PREVIOUSPROPERTYDETAILADJUSTMENTID = ID
from dbo.PROPERTYDETAILADJUSTMENT
where
PROPERTYDETAILID = @PROPERTYDETAILID and
ID <> @PROPERTYDETAILADJUSTMENTID
order by TSLONG desc;
/*If there is a previous adjustment, write the same reversal as the "adjusted" portion of the previous adjustment */
if not @PREVIOUSPROPERTYDETAILADJUSTMENTID is null
begin
select
@PREVIOUSADJUSTMENTHISTORYPROPERTYID = ID
from dbo.ADJUSTMENTHISTORYPROPERTY
where PROPERTYDETAILADJUSTMENTID = @PREVIOUSPROPERTYDETAILADJUSTMENTID;
if not @PREVIOUSADJUSTMENTHISTORYPROPERTYID is null
begin
/*first, delete the previous adjustment record*/
delete from dbo.ADJUSTMENTHISTORYPROPERTYDISTRIBUTION where ADJUSTMENTHISTORYPROPERTYID = @PREVIOUSADJUSTMENTHISTORYPROPERTYID and TYPECODE = 1;
/*then insert the updated ones*/
insert into dbo.ADJUSTMENTHISTORYPROPERTYDISTRIBUTION(ADJUSTMENTHISTORYPROPERTYID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@PREVIOUSADJUSTMENTHISTORYPROPERTYID,
1, /*Adjustment*/
TRANSACTIONTYPECODE,
ACCOUNT,
PROJECT,
REFERENCE,
AMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT,
BASECURRENCYID,
TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYPROPERTYDISTRIBUTIONSNAPSHOT
where ADJUSTMENTHISTORYPROPERTYID = @ADJUSTMENTHISTORYPROPERTYID;
end
end
end