USP_ADJUSTMENTHISTORY_GIFTINKINDSALE_SAVEHISTORY
Store historical sold gift-in-kind adjustment information for reporting purposes.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @GIFTINKINDSALEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @GIFTINKINDSALEADJUSTMENTID | uniqueidentifier | IN |
Definition
Copy
create procedure dbo.USP_ADJUSTMENTHISTORY_GIFTINKINDSALE_SAVEHISTORY
(
@GIFTINKINDSALEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CHANGEDATE datetime,
@GIFTINKINDSALEADJUSTMENTID uniqueidentifier
)
as
set nocount on;
/*Before calling this procedure, be sure that you call USP_SAVE_GIFTINKINDSALEADJUSTMENT and then save changes to the GIFTINKINDSALE table*/
declare @ADJUSTMENTCOUNT int;
declare @PREVIOUSGIFTINKINDSALEADJUSTMENTID uniqueidentifier;
declare @PREVIOUSADJUSTMENTHISTORYGIFTINKINDID uniqueidentifier;
declare @ADJUSTMENTHISTORYGIFTINKINDID uniqueidentifier;
declare @SALEAMOUNTCHANGED bit;
declare @NUMBEROFUNITSCHANGED bit;
declare @CONSTITUENTCHANGED bit;
declare @PAYMENTNUMBEROFUNITSCHANGED bit;
declare @PAYMENTFAIRMARKETVALUECHANGED bit;
declare @BASERATECHANGED bit;
declare @ORGANIZATIONRATECHANGED bit;
declare @EDITINGADJUSTMENT bit;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
select @ADJUSTMENTCOUNT = count(ID)
from dbo.GIFTINKINDSALEADJUSTMENT
where GIFTINKINDSALEADJUSTMENT.GIFTINKINDSALEID = @GIFTINKINDSALEID;
select @ADJUSTMENTHISTORYGIFTINKINDID = ID
from dbo.ADJUSTMENTHISTORYGIFTINKIND
where GIFTINKINDSALEADJUSTMENTID = @GIFTINKINDSALEADJUSTMENTID
/*if this is a new adjustment, there will only be a header row (no details or distributions). Otherwise, it's an edit*/
if not exists
(
select ID from dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAIL where ADJUSTMENTHISTORYGIFTINKINDID = @ADJUSTMENTHISTORYGIFTINKINDID
union all
select ID from dbo.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTION where ADJUSTMENTHISTORYGIFTINKINDID = @ADJUSTMENTHISTORYGIFTINKINDID
)
set @EDITINGADJUSTMENT = 0;
else
set @EDITINGADJUSTMENT = 1;
/*delete the previous report information so we can recreate it*/
delete from dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAIL where ADJUSTMENTHISTORYGIFTINKINDID = @ADJUSTMENTHISTORYGIFTINKINDID;
/*determine what changed by comparing the snapshot to the live data*/
select
@SALEAMOUNTCHANGED = case when SNAPSHOT.SALEAMOUNT <> LIVEDATA.SALEAMOUNT then 1 else 0 end,
@NUMBEROFUNITSCHANGED = case when SNAPSHOT.NUMBEROFUNITS <> LIVEDATA.NUMBEROFUNITS then 1 else 0 end,
@CONSTITUENTCHANGED = case when SNAPSHOT.CONSTITUENTIDENTIFIER <> LIVEDATA.CONSTITUENTID then 1 else 0 end,
@PAYMENTNUMBEROFUNITSCHANGED = case when SNAPSHOT.PAYMENTNUMBEROFUNITS <> LIVEDATA.PAYMENTNUMBEROFUNITS then 1 else 0 end,
@PAYMENTFAIRMARKETVALUECHANGED = case when SNAPSHOT.PAYMENTFAIRMARKETVALUE <> LIVEDATA.PAYMENTFAIRMARKETVALUE then 1 else 0 end,
@BASERATECHANGED = case when SNAPSHOT.BASERATE <> LIVEDATA.BASEEXCHANGERATE then 1 else 0 end,
@ORGANIZATIONRATECHANGED = case when SNAPSHOT.ORGANIZATIONRATE <> LIVEDATA.ORGANIZATIONEXCHANGERATE then 1 else 0 end
from dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT as SNAPSHOT
cross join
(
select
GIFTINKINDSALE.SALEAMOUNT,
GIFTINKINDSALE.NUMBEROFUNITS,
BASEEXCHANGERATE.RATE as BASEEXCHANGERATE,
ORGANIZATIONEXCHANGERATE.RATE as ORGANIZATIONEXCHANGERATE,
REVENUE.CONSTITUENTID,
GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS as PAYMENTNUMBEROFUNITS,
GIFTINKINDPAYMENTMETHODDETAIL.FAIRMARKETVALUE as PAYMENTFAIRMARKETVALUE
from dbo.GIFTINKINDSALE
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
inner join dbo.REVENUE on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
left join dbo.CURRENCYEXCHANGERATE as BASEEXCHANGERATE on BASEEXCHANGERATE.ID = GIFTINKINDSALE.BASEEXCHANGERATEID
left join dbo.CURRENCYEXCHANGERATE as ORGANIZATIONEXCHANGERATE on ORGANIZATIONEXCHANGERATE.ID = GIFTINKINDSALE.ORGANIZATIONEXCHANGERATEID
where GIFTINKINDSALE.ID = @GIFTINKINDSALEID
) as LIVEDATA
where ID = @ADJUSTMENTHISTORYGIFTINKINDID;
/*Log the detail for this adjustment*/
--SALEAMOUNTCHANGED or
--PAYMENTFAIRMARKETVALUECHANGED
insert into dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAIL(ADJUSTMENTHISTORYGIFTINKINDID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, ISMONEY, ADJUSTEDFIELDPREVIOUSMONEYVALUE, ADJUSTEDFIELDADJUSTEDMONEYVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, TRANSACTIONADJUSTEDFIELDPREVIOUSMONEYVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDMONEYVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSMONEYVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDMONEYVALUE, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYGIFTINKINDID,
'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.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT as [SNAP]
inner join dbo.GIFTINKINDSALE as [REAL] on [REAL].ID = @GIFTINKINDSALEID
where [SNAP].ID = @ADJUSTMENTHISTORYGIFTINKINDID and @SALEAMOUNTCHANGED = 1
union all
select
@ADJUSTMENTHISTORYGIFTINKINDID,
'Payment''s median price',
[SNAP].PAYMENTFAIRMARKETVALUE,
[REAL].FAIRMARKETVALUE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
1,
[SNAP].PAYMENTFAIRMARKETVALUE,
[REAL].FAIRMARKETVALUE,
[SNAP].TRANSACTIONPAYMENTFAIRMARKETVALUE,
[REAL].TRANSACTIONFAIRMARKETVALUE,
[SNAP].TRANSACTIONPAYMENTFAIRMARKETVALUE,
[REAL].TRANSACTIONFAIRMARKETVALUE,
[SNAP].ORGANIZATIONPAYMENTFAIRMARKETVALUE,
[REAL].ORGANIZATIONFAIRMARKETVALUE,
[SNAP].ORGANIZATIONPAYMENTFAIRMARKETVALUE,
[REAL].ORGANIZATIONFAIRMARKETVALUE,
[REAL].BASECURRENCYID,
[REAL].TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT as [SNAP]
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALE.ID = @GIFTINKINDSALEID
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL as [REAL] on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = [REAL].ID
where [SNAP].ID = @ADJUSTMENTHISTORYGIFTINKINDID and @PAYMENTFAIRMARKETVALUECHANGED = 1
-- NUMBEROFUNITSCHANGED or
-- CONSTITUENTCHANGED or
-- PAYMENTNUMBEROFUNITSCHANGED or
-- BASERATECHANGED or
-- ORGANIZATIONRATECHANGED
insert into dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAIL(ADJUSTMENTHISTORYGIFTINKINDID, ADJUSTEDFIELD, ADJUSTEDFIELDPREVIOUSVALUE, ADJUSTEDFIELDADJUSTEDVALUE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONADJUSTEDFIELDPREVIOUSVALUE, TRANSACTIONADJUSTEDFIELDADJUSTEDVALUE, ORGANIZATIONADJUSTEDFIELDPREVIOUSVALUE, ORGANIZATIONADJUSTEDFIELDADJUSTEDVALUE)
select
@ADJUSTMENTHISTORYGIFTINKINDID,
'Units sold',
[SNAP].NUMBEROFUNITS,
[REAL].NUMBEROFUNITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
[SNAP].NUMBEROFUNITS,
[REAL].NUMBEROFUNITS,
[SNAP].NUMBEROFUNITS,
[REAL].NUMBEROFUNITS
from dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT as [SNAP]
inner join dbo.GIFTINKINDSALE as [REAL] on [REAL].ID = @GIFTINKINDSALEID
where [SNAP].ID = @ADJUSTMENTHISTORYGIFTINKINDID and @NUMBEROFUNITSCHANGED = 1
union all
select
@ADJUSTMENTHISTORYGIFTINKINDID,
'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.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT as [SNAP]
inner join dbo.GIFTINKINDSALE as [REAL] on [REAL].ID = @GIFTINKINDSALEID
inner join dbo.REVENUEPAYMENTMETHOD on [REAL].ID = REVENUEPAYMENTMETHOD.ID
inner join dbo.REVENUE on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
where [SNAP].ID = @ADJUSTMENTHISTORYGIFTINKINDID and @CONSTITUENTCHANGED = 1
union all
select
@ADJUSTMENTHISTORYGIFTINKINDID,
'Payment''s number of units',
[SNAP].PAYMENTNUMBEROFUNITS,
[REAL].NUMBEROFUNITS,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
[SNAP].PAYMENTNUMBEROFUNITS,
[REAL].NUMBEROFUNITS,
[SNAP].PAYMENTNUMBEROFUNITS,
[REAL].NUMBEROFUNITS
from dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT as [SNAP]
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALE.ID = @GIFTINKINDSALEID
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL as [REAL] on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = [REAL].ID
where [SNAP].ID = @ADJUSTMENTHISTORYGIFTINKINDID and @PAYMENTNUMBEROFUNITSCHANGED = 1
union all
select
@ADJUSTMENTHISTORYGIFTINKINDID,
'Base exchange rate',
ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT.BASERATE,
OLDBASERATE.RATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT.BASERATE,
OLDBASERATE.RATE,
ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT.BASERATE,
OLDBASERATE.RATE
from dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT
cross join
(
select CURRENCYEXCHANGERATE.RATE
from dbo.GIFTINKINDSALE
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = GIFTINKINDSALE.BASEEXCHANGERATEID
where GIFTINKINDSALE.ID = @GIFTINKINDSALEID
) OLDBASERATE
where ID = @ADJUSTMENTHISTORYGIFTINKINDID and @BASERATECHANGED = 1
union all
select
@ADJUSTMENTHISTORYGIFTINKINDID,
'Organization exchange rate',
ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT.ORGANIZATIONRATE,
OLDORGANIZATIONRATE.RATE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT.ORGANIZATIONRATE,
OLDORGANIZATIONRATE.RATE,
ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT.ORGANIZATIONRATE,
OLDORGANIZATIONRATE.RATE
from dbo.ADJUSTMENTHISTORYGIFTINKINDPAYMENTMETHODDETAILSNAPSHOT
cross join
(
select CURRENCYEXCHANGERATE.RATE
from dbo.GIFTINKINDSALE
left join dbo.CURRENCYEXCHANGERATE on CURRENCYEXCHANGERATE.ID = GIFTINKINDSALE.ORGANIZATIONEXCHANGERATEID
where GIFTINKINDSALE.ID = @GIFTINKINDSALEID
) OLDORGANIZATIONRATE
where ID = @ADJUSTMENTHISTORYGIFTINKINDID and @ORGANIZATIONRATECHANGED = 1;
/*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.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTION(ADJUSTMENTHISTORYGIFTINKINDID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@ADJUSTMENTHISTORYGIFTINKINDID,
0, /*Reversal*/
TRANSACTIONTYPECODE,
ACCOUNT,
PROJECT,
REFERENCE,
AMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT,
BASECURRENCYID,
TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTIONSNAPSHOT
where ADJUSTMENTHISTORYGIFTINKINDID = @ADJUSTMENTHISTORYGIFTINKINDID;
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
@PREVIOUSGIFTINKINDSALEADJUSTMENTID = ID
from dbo.GIFTINKINDSALEADJUSTMENT
where
GIFTINKINDSALEID = @GIFTINKINDSALEID and
ID <> @GIFTINKINDSALEADJUSTMENTID
order by TSLONG desc;
/*If there is a previous adjustment, write the same reversal as the "adjusted" portion of the previous adjustment */
if not @PREVIOUSGIFTINKINDSALEADJUSTMENTID is null
begin
select
@PREVIOUSADJUSTMENTHISTORYGIFTINKINDID = ID
from dbo.ADJUSTMENTHISTORYGIFTINKIND
where GIFTINKINDSALEADJUSTMENTID = @PREVIOUSGIFTINKINDSALEADJUSTMENTID;
if @PREVIOUSADJUSTMENTHISTORYGIFTINKINDID is not null
begin
/*first, delete the previous adjustment record*/
delete from dbo.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTION where ADJUSTMENTHISTORYGIFTINKINDID = @PREVIOUSADJUSTMENTHISTORYGIFTINKINDID and TYPECODE = 1;
/*then insert the updated ones*/
insert into dbo.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTION(ADJUSTMENTHISTORYGIFTINKINDID, TYPECODE, TRANSACTIONTYPECODE, ACCOUNT, PROJECT, REFERENCE, AMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, BASECURRENCYID, TRANSACTIONCURRENCYID)
select
@PREVIOUSADJUSTMENTHISTORYGIFTINKINDID,
1, /*Adjustment*/
TRANSACTIONTYPECODE,
ACCOUNT,
PROJECT,
REFERENCE,
AMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
TRANSACTIONAMOUNT,
ORGANIZATIONAMOUNT,
BASECURRENCYID,
TRANSACTIONCURRENCYID
from dbo.ADJUSTMENTHISTORYGIFTINKINDDISTRIBUTIONSNAPSHOT
where ADJUSTMENTHISTORYGIFTINKINDID = @ADJUSTMENTHISTORYGIFTINKINDID;
end
end
end