UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION_2
Generates GL Account Code for auction purchases from the account code mappings defined in the system.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @IGNOREREVENUEID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUE_GENERATEAUCTIONPURCHASEGLDISTRIBUTION_2
(
@REVENUEID uniqueidentifier,
@IGNOREREVENUEID uniqueidentifier
)
returns @DISTRIBUTIONS table
(
REVENUEID uniqueidentifier,
GLPAYMENTMETHODREVENUETYPEMAPPINGID uniqueidentifier,
TRANSACTIONTYPECODE tinyint,
POSTDATE datetime,
POSTSTATUSCODE tinyint,
ACCOUNTSTRING nvarchar(100),
PROJECT nvarchar(100),
AMOUNT money,
REFERENCE nvarchar(255),
ERRORMESSAGE nvarchar(max),
PAYMENTMETHODCODE tinyint,
REVENUETRANSACTIONTYPECODE tinyint,
ACCOUNTID uniqueidentifier,
REVENUESPLITTYPECODE tinyint,
AUCTIONITEMVALUE money,
AUCTIONITEMPOSTSTATUSCODE tinyint,
REVENUEPURCHASEID uniqueidentifier,
AUCTIONITEMPURCHASEAMOUNT money,
REVENUESPLITAMOUNT money,
REVENUESPLITTRANSACTIONAMOUNT money,
REVENUESPLITORGANIZATIONAMOUNT money,
AUCTIONITEMTRANSACTIONPURCHASEAMOUNT money,
AUCTIONITEMORGANIZATIONPURCHASEAMOUNT money,
TRANSACTIONAMOUNT money,
ORGANIZATIONAMOUNT money,
BASECURRENCYID uniqueidentifier,
TRANSACTIONCURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
AUCTIONITEMDONATIONDATE datetime,
MAPPEDVALUES xml,
REVENUESPLITID uniqueidentifier
)
as
begin
-- Currently, we only take payments for auction item donations in the same currencies and PDACCOUNTSYSTEM
-- as the initial auction item donation.
--The distributions for auction items/auction item purchases are different than
--everything else in the system. Auction items can be purchased by multiple
--revenue transactions, and we have to calculate a all distributions based off of all
--the postable payments towards the auction item.
--When one payment changes, we must re-calculate all of the distributions!!
--If the @IGNOREREVENUEID is not null, then we do not count any revenue towards auction items
--with that ID
declare @ORGANIZATIONCURRENCYDECIMALDIGITS int;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @ORGANIZATIONAMOUNTORIGINCODE tinyint;
select
@ORGANIZATIONCURRENCYID = CURRENCY.ID,
@ORGANIZATIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS
from dbo.CURRENCY
where ID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY()
select top 1
@ORGANIZATIONAMOUNTORIGINCODE = ORGANIZATIONAMOUNTORIGINCODE
from
dbo.MULTICURRENCYCONFIGURATION;
declare @AUCTIONITEMS table
(
ID uniqueidentifier,
REVENUEAUCTIONDONATIONID uniqueidentifier,
VALUE decimal(20,8),
DONOTPOST tinyint,
PAYMENTCOUNT int,
TRANSACTIONCURRENCYID uniqueidentifier,
BASECURRENCYID uniqueidentifier,
TRANSACTIONVALUE money,
ORGANIZATIONVALUE money,
BASECURRENCYDECIMALDIGITS int,
TRANSACTIONCURRENCYDECIMALDIGITS int
)
--Start by finding all items that are affected by this payment
insert into @AUCTIONITEMS
select
AUCTIONITEM.ID,
AUCTIONITEM.REVENUEAUCTIONDONATIONID,
AUCTIONITEM.VALUE,
REVENUE.DONOTPOST,
count(AUCTIONITEMREVENUEPURCHASE.ID),
AUCTIONITEM.TRANSACTIONCURRENCYID,
AUCTIONITEM.BASECURRENCYID,
AUCTIONITEM.TRANSACTIONVALUE,
AUCTIONITEM.ORGANIZATIONVALUE,
BASE_CURRENCY.DECIMALDIGITS,
TRANSACTION_CURRENCY.DECIMALDIGITS
from
dbo.AUCTIONITEMREVENUEPURCHASE
inner join dbo.AUCTIONITEM on AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
inner join dbo.REVENUE on AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = REVENUE.ID
left join dbo.REVENUEPOSTED on REVENUE.ID = REVENUEPOSTED.ID
left join dbo.CURRENCY BASE_CURRENCY on AUCTIONITEM.BASECURRENCYID = BASE_CURRENCY.ID
left join dbo.CURRENCY TRANSACTION_CURRENCY on AUCTIONITEM.TRANSACTIONCURRENCYID = TRANSACTION_CURRENCY.ID
where
AUCTIONITEM.ID in (select AUCTIONITEMID from dbo.AUCTIONITEMREVENUEPURCHASE [AUCTIONITEMPURCHASE] where [AUCTIONITEMPURCHASE].REVENUEPURCHASEID = @REVENUEID)
and AUCTIONITEM.TYPECODE = 0
and (@IGNOREREVENUEID is null or REVENUE.ID <> @IGNOREREVENUEID)
group by AUCTIONITEM.ID,AUCTIONITEM.REVENUEAUCTIONDONATIONID,AUCTIONITEM.VALUE,REVENUE.DONOTPOST,
AUCTIONITEM.TRANSACTIONCURRENCYID,AUCTIONITEM.BASECURRENCYID,AUCTIONITEM.TRANSACTIONVALUE,AUCTIONITEM.ORGANIZATIONVALUE,
BASE_CURRENCY.DECIMALDIGITS,TRANSACTION_CURRENCY.DECIMALDIGITS
-- Create a cursor to generate new distributions for affected items/payments
-- We do that by finding all payment records that helped pay for the items
declare @AUCTIONITEM_CURRENTPAYMENTCOUNT int = 0;
-- Variables to keep track of "wash" distribution amounts
declare @ITEM_TRANSACTIONAMOUNT_TO_DISTRIBUTE money = 0;
declare @ITEM_AMOUNT_TO_DISTRIBUTE money = 0;
declare @ITEM_ORGANIZATIONAMOUNT_TO_DISTRIBUTE money = 0;
declare @DISTRIBUTED_ITEM_TRANSACTIONAMOUNT money = 0;
declare @DISTRIBUTED_ITEM_AMOUNT money = 0;
declare @DISTRIBUTED_ITEM_ORGANIZATIONAMOUNT money = 0;
declare @TOTALDISTRIBUTED_ITEM_TRANSACTIONAMOUNT money = 0;
declare @TOTALDISTRIBUTED_ITEM_AMOUNT money = 0;
declare @TOTALDISTRIBUTED_ITEM_ORGANIZATIONAMOUNT money = 0;
-- Variables to keep track of gain/loss amounts
declare @TOTALBASEGAINLOSS money = 0;
declare @TOTALORGANIZATIONGAINLOSS money = 0;
declare @GAINLOSS_TRANSACTIONAMOUNT_TO_DISTRIBUTE money = 0;
--declare @GAINLOSS_AMOUNT_TO_DISTRIBUTE money = 0;
--declare @GAINLOSS_ORGANIZATIONAMOUNT_TO_DISTRIBUTE money = 0;
declare @DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT money = 0;
declare @DISTRIBUTED_GAINLOSS_AMOUNT money = 0;
declare @DISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT money = 0;
declare @TOTALDISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT money = 0;
declare @TOTALDISTRIBUTED_GAINLOSS_AMOUNT money = 0;
declare @TOTALDISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT money = 0;
-- Variables to keep track of currency gain/loss amounts
declare @TOTALITEMBASEGAINLOSS money = 0;
declare @TOTALITEMORGANIZATIONGAINLOSS money = 0;
declare @TOTALBASECURRENCYGAINLOSS money = 0;
declare @TOTALORGANIZATIONCURRENCYGAINLOSS money = 0;
declare @DISTRIBUTED_CURRENCYGAINLOSS_AMOUNT money = 0;
declare @DISTRIBUTED_CURRENCYGAINLOSS_ORGANIZATIONAMOUNT money = 0;
declare @TOTALDISTRIBUTED_CURRENCYGAINLOSS_AMOUNT money = 0;
declare @TOTALDISTRIBUTED_CURRENCYGAINLOSS_ORGANIZATIONAMOUNT money = 0;
-- Variables for the payment/item cursor
declare @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID uniqueidentifier,@PAYMENTCURSOR_REVENUEPURCHASEID uniqueidentifier, @PAYMENTCURSOR_AUCTIONITEMID uniqueidentifier, @PAYMENTCURSOR_PAYMENTCOUNT integer;
declare @PAYMENTCURSOR_ITEMTRANSACTIONCURRENCYID uniqueidentifier, @PAYMENTCURSOR_ITEMBASECURRENCYID uniqueidentifier;
declare @PAYMENTCURSOR_ITEMVALUE money, @PAYMENTCURSOR_ITEMTRANSACTIONVALUE money, @PAYMENTCURSOR_ITEMORGANIZATIONVALUE money;
declare @PAYMENTCURSOR_ITEMBASECURRENCYDECIMALDIGITS int, @PAYMENTCURSOR_ITEMTRANSACTIONCURRENCYDECIMALDIGITS int;
declare PAYMENTCURSOR cursor local fast_forward
for select
AUCTIONITEMS.REVENUEAUCTIONDONATIONID,
AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID,
AUCTIONITEMS.ID,
AUCTIONITEMS.PAYMENTCOUNT,
AUCTIONITEMS.TRANSACTIONCURRENCYID,
AUCTIONITEMS.BASECURRENCYID,
AUCTIONITEMS.VALUE,
AUCTIONITEMS.TRANSACTIONVALUE,
AUCTIONITEMS.ORGANIZATIONVALUE,
AUCTIONITEMS.BASECURRENCYDECIMALDIGITS,
AUCTIONITEMS.TRANSACTIONCURRENCYDECIMALDIGITS
from
@AUCTIONITEMS AUCTIONITEMS
inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEMS.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
where
((@IGNOREREVENUEID is null) or (AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID <> @IGNOREREVENUEID))
order by AUCTIONITEMS.ID, AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID
open PAYMENTCURSOR
fetch next from PAYMENTCURSOR into
@PAYMENTCURSOR_REVENUEAUCTIONDONATIONID,@PAYMENTCURSOR_REVENUEPURCHASEID,@PAYMENTCURSOR_AUCTIONITEMID,
@PAYMENTCURSOR_PAYMENTCOUNT,@PAYMENTCURSOR_ITEMTRANSACTIONCURRENCYID,@PAYMENTCURSOR_ITEMBASECURRENCYID,
@PAYMENTCURSOR_ITEMVALUE,@PAYMENTCURSOR_ITEMTRANSACTIONVALUE,@PAYMENTCURSOR_ITEMORGANIZATIONVALUE,
@PAYMENTCURSOR_ITEMBASECURRENCYDECIMALDIGITS, @PAYMENTCURSOR_ITEMTRANSACTIONCURRENCYDECIMALDIGITS
while @@FETCH_STATUS = 0
begin
-- Keep track of the payment count
select @AUCTIONITEM_CURRENTPAYMENTCOUNT = @AUCTIONITEM_CURRENTPAYMENTCOUNT + 1;
-- Generate the distribution rows for the payment/item
-- We do not generate the amounts for the distributions here. We handle this
-- below to ensure no rounding issues, and proper conversions.
insert into @DISTRIBUTIONS
(
REVENUEID,
GLPAYMENTMETHODREVENUETYPEMAPPINGID,
TRANSACTIONTYPECODE,
POSTDATE,
POSTSTATUSCODE,
ACCOUNTSTRING,
PROJECT,
AMOUNT,
REFERENCE,
ERRORMESSAGE,
PAYMENTMETHODCODE,
REVENUETRANSACTIONTYPECODE,
ACCOUNTID,
REVENUESPLITTYPECODE,
AUCTIONITEMVALUE,
AUCTIONITEMPOSTSTATUSCODE,
REVENUEPURCHASEID,
AUCTIONITEMPURCHASEAMOUNT,
REVENUESPLITAMOUNT,
REVENUESPLITTRANSACTIONAMOUNT,
REVENUESPLITORGANIZATIONAMOUNT,
AUCTIONITEMTRANSACTIONPURCHASEAMOUNT,
AUCTIONITEMORGANIZATIONPURCHASEAMOUNT,
BASECURRENCYID,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
ORGANIZATIONEXCHANGERATEID,
AUCTIONITEMDONATIONDATE,
MAPPEDVALUES,
REVENUESPLITID
)
select
AUCTIONITEM.REVENUEAUCTIONDONATIONID as REVENUEID,
dbo.UFN_POSTTOGLPROCESS_GETGLPAYMENTMETHODREVENUETYPEMAPPINGID(REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, REVENUE.TRANSACTIONTYPECODE, CODES.REVENUESPLITTYPECODE, 12, tf.TRANSACTIONTYPECODE) as GLPAYMENTMETHODREVENUETYPEMAPPINGID,
tf.TRANSACTIONTYPECODE,
REVENUE.POSTDATE,
case when REVENUE.DONOTPOST = 1 then 2 when REVENUEPOSTED.ID is not null then 0 else 1 end as POSTSTATUSCODE,
tf.ACCOUNTSTRING,
tf.PROJECTCODE as PROJECT,
0 as AMOUNT, -- We figure this below to ensure no rounding issues
dbo.UFN_POSTTOGLPROCESS_GETJOURNALREFERENCE (REVENUESPLIT.ID, REVENUEPAYMENTMETHOD.PAYMENTMETHOD, CODES.REVENUESPLITTYPE) as REFERENCE,
tf.ERRORMESSAGE,
REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE,
REVENUE.TRANSACTIONTYPECODE as REVENUETRANSACTIONTYPECODE,
tf.ACCOUNTID as ACCOUNTID,
CODES.REVENUESPLITTYPECODE,
AUCTIONITEM.VALUE as AUCTIONITEMVALUE,
case
when AUCTIONITEMDONATION.DONOTPOST = 1 then 2
when AUCTIONITEMDONATIONPOSTED.ID is not null then 0
else 1
end as AUCTIONITEMPOSTSTATUSCODE,
REVENUE.ID as REVENUEPURCHASEID,
case
when @IGNOREREVENUEID is null then
PURCHASEPRICE.AMOUNT
when @IGNOREREVENUEID is not null then
PURCHASEPRICEIGNOREREVENUE.AMOUNT
end as AUCTIONITEMPURCHASEAMOUNT,
REVENUESPLIT.AMOUNT,
REVENUESPLIT.TRANSACTIONAMOUNT,
REVENUESPLIT.ORGANIZATIONAMOUNT,
case
when @IGNOREREVENUEID is null then
PURCHASEPRICE.TRANSACTIONAMOUNT
when @IGNOREREVENUEID is not null then
PURCHASEPRICEIGNOREREVENUE.TRANSACTIONAMOUNT
end as AUCTIONITEMTRANSACTIONPURCHASEAMOUNT,
case
when @IGNOREREVENUEID is null then
PURCHASEPRICE.ORGANIZATIONAMOUNT
when @IGNOREREVENUEID is not null then
PURCHASEPRICEIGNOREREVENUE.ORGANIZATIONAMOUNT
end as AUCTIONITEMORGANIZATIONPURCHASEAMOUNT,
case
when CODES.REVENUESPLITTYPECODE = 12 then AUCTIONITEM.BASECURRENCYID
when CODES.REVENUESPLITTYPECODE in (203, 204) then coalesce(REVENUESPLIT.BASECURRENCYID, REVENUE.BASECURRENCYID)
end as BASECURRENCYID,
case
when CODES.REVENUESPLITTYPECODE = 12 then AUCTIONITEM.TRANSACTIONCURRENCYID
when CODES.REVENUESPLITTYPECODE in (203, 204) then coalesce(REVENUESPLIT.TRANSACTIONCURRENCYID, REVENUE.TRANSACTIONCURRENCYID)
end as TRANSACTIONCURRENCYID,
AUCTIONITEM.BASEEXCHANGERATEID as BASEEXCHANGERATEID,
AUCTIONITEM.ORIGINTOORGANIZATIONEXCHANGERATEID as ORGANIZATIONEXCHANGERATEID,
AUCTIONITEMDONATION.DATE,
tf.MAPPEDVALUES,
REVENUESPLIT.ID
from
dbo.REVENUE with (nolock)
inner join dbo.REVENUEPAYMENTMETHOD with (nolock) on REVENUE.ID = REVENUEPAYMENTMETHOD.REVENUEID
left join dbo.REVENUEPOSTED with (nolock) on REVENUEPOSTED.ID = REVENUE.ID
inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.AUCTIONITEMPURCHASE with (nolock) on AUCTIONITEMPURCHASE.PURCHASEID = REVENUESPLIT.ID
inner join dbo.AUCTIONITEM with (nolock) on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
inner join dbo.REVENUE as AUCTIONITEMDONATION with (nolock) on AUCTIONITEM.REVENUEAUCTIONDONATIONID = AUCTIONITEMDONATION.ID
left join dbo.REVENUEPOSTED as AUCTIONITEMDONATIONPOSTED with (nolock) on AUCTIONITEMDONATIONPOSTED.ID = AUCTIONITEMDONATION.ID
cross join
(
select '12' as REVENUESPLITTYPECODE, 'Auction purchase' as REVENUESPLITTYPE, 'Payment' as REVENUETYPE, '0' as REVENUETYPECODE
union all
select '203' as REVENUESPLITTYPECODE, 'Auction purchase gain' AS REVENUESPLITTYPE, 'Payment' as REVENUETYPE, '0' as REVENUETYPECODE
union all
select '204' as REVENUESPLITTYPECODE, 'Auction purchase loss' AS REVENUESPLITTYPE, 'Payment' as REVENUETYPE, '0' as REVENUETYPECODE
) as CODES
cross apply dbo.UFN_REVENUE_GENERATEGLACCOUNT(REVENUESPLIT.ID, REVENUE.TRANSACTIONTYPECODE, CODES.REVENUESPLITTYPECODE, REVENUESPLIT.APPLICATIONCODE, REVENUEPAYMENTMETHOD.PAYMENTMETHODCODE, null) as tf
cross apply dbo.UFN_AUCTIONITEM_GETPOSTABLEPURCHASEAMOUNT(AUCTIONITEM.ID, null) as PURCHASEPRICE
cross apply dbo.UFN_AUCTIONITEM_GETPOSTABLEPURCHASEAMOUNT(AUCTIONITEM.ID, @IGNOREREVENUEID) as PURCHASEPRICEIGNOREREVENUE
where
(REVENUE.ID = @PAYMENTCURSOR_REVENUEPURCHASEID)
and
(@IGNOREREVENUEID is null or (REVENUE.ID <> @IGNOREREVENUEID))
and
(AUCTIONITEM.ID = @PAYMENTCURSOR_AUCTIONITEMID)
and
(
(@IGNOREREVENUEID is null and CODES.REVENUESPLITTYPECODE = 203 and tf.TRANSACTIONTYPECODE = 1 and AUCTIONITEM.TRANSACTIONVALUE < PURCHASEPRICE.TRANSACTIONAMOUNT) -- Gain
or
(@IGNOREREVENUEID is not null and CODES.REVENUESPLITTYPECODE = 203 and tf.TRANSACTIONTYPECODE = 1 and AUCTIONITEM.TRANSACTIONVALUE < PURCHASEPRICEIGNOREREVENUE.TRANSACTIONAMOUNT) -- Gain
or
(@IGNOREREVENUEID is null and CODES.REVENUESPLITTYPECODE = 204 and tf.TRANSACTIONTYPECODE = 0 and AUCTIONITEM.TRANSACTIONVALUE > PURCHASEPRICE.TRANSACTIONAMOUNT) -- Loss
or
(@IGNOREREVENUEID is not null and CODES.REVENUESPLITTYPECODE = 204 and tf.TRANSACTIONTYPECODE = 0 and AUCTIONITEM.TRANSACTIONVALUE > PURCHASEPRICEIGNOREREVENUE.TRANSACTIONAMOUNT) -- Loss
or
(CODES.REVENUESPLITTYPECODE = 12)
)
and
(REVENUE.DONOTPOST <> 1)
declare @BASECURRENCYID uniqueidentifier
declare @TRANSACTIONCURRENCYID uniqueidentifier
declare @BASEEXCHANGERATEID uniqueidentifier
declare @ORGEXCHANGERATEID uniqueidentifier
--Figure the distribution amounts here
if @AUCTIONITEM_CURRENTPAYMENTCOUNT = 1
begin
-- If this is the first payment towards an item, find the "wash" and gain/loss amounts that need to be
-- distributed across all payments towards an item.
-- The "wash" distributions need to be for the exact amounts as the original auction item donation.
-- So, we set the amount to distribute equal to the item values.
select @ITEM_TRANSACTIONAMOUNT_TO_DISTRIBUTE = @PAYMENTCURSOR_ITEMTRANSACTIONVALUE;
select @ITEM_AMOUNT_TO_DISTRIBUTE = @PAYMENTCURSOR_ITEMVALUE;
select @ITEM_ORGANIZATIONAMOUNT_TO_DISTRIBUTE = @PAYMENTCURSOR_ITEMORGANIZATIONVALUE;
-- The gain/loss distributions need to use the same currencies and exchange rates as the original auction
-- item donation. The gain/loss amounts for currency exchange rate changes are done elsewhere.
select top 1
@GAINLOSS_TRANSACTIONAMOUNT_TO_DISTRIBUTE = abs(DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT - @PAYMENTCURSOR_ITEMTRANSACTIONVALUE),
@TOTALBASEGAINLOSS = abs(DISTRIBUTIONS.AUCTIONITEMPURCHASEAMOUNT - @PAYMENTCURSOR_ITEMVALUE),
@TOTALORGANIZATIONGAINLOSS = abs(DISTRIBUTIONS.AUCTIONITEMORGANIZATIONPURCHASEAMOUNT - @PAYMENTCURSOR_ITEMORGANIZATIONVALUE),
@TOTALBASECURRENCYGAINLOSS = case
when DISTRIBUTIONS.BASEEXCHANGERATEID is not null then
abs(DISTRIBUTIONS.AUCTIONITEMPURCHASEAMOUNT - (select dbo.UFN_AUCTIONPACKAGE_CONVERT_UNROUNDED(DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, DISTRIBUTIONS.BASEEXCHANGERATEID)))
else
0
end,
@TOTALORGANIZATIONCURRENCYGAINLOSS = case
when DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID is not null then
abs(DISTRIBUTIONS.AUCTIONITEMORGANIZATIONPURCHASEAMOUNT - (select dbo.UFN_AUCTIONPACKAGE_CONVERTTOORGANIZATIONCURRENCY_UNROUNDED(DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID, DISTRIBUTIONS.AUCTIONITEMPURCHASEAMOUNT)))
when DISTRIBUTIONS.BASEEXCHANGERATEID is not null then
abs(DISTRIBUTIONS.AUCTIONITEMPURCHASEAMOUNT - (select dbo.UFN_AUCTIONPACKAGE_CONVERT_UNROUNDED(DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, DISTRIBUTIONS.BASEEXCHANGERATEID)))
else
0
end
from
@DISTRIBUTIONS DISTRIBUTIONS
where
DISTRIBUTIONS.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
and DISTRIBUTIONS.REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID
select @TOTALITEMBASEGAINLOSS = abs(@TOTALBASEGAINLOSS - @TOTALBASECURRENCYGAINLOSS)
select @TOTALITEMORGANIZATIONGAINLOSS = abs(@TOTALORGANIZATIONGAINLOSS - @TOTALORGANIZATIONCURRENCYGAINLOSS)
end
declare @TEMP_BASEGAINLOSS money = 0;
declare @TEMP_ORGANIZATIONGAINLOSS money = 0;
select
@TEMP_BASEGAINLOSS = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(DISTRIBUTIONS.REVENUESPLITTRANSACTIONAMOUNT, DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, @TOTALBASEGAINLOSS, @PAYMENTCURSOR_ITEMBASECURRENCYDECIMALDIGITS),
@TEMP_ORGANIZATIONGAINLOSS = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(DISTRIBUTIONS.REVENUESPLITTRANSACTIONAMOUNT, DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, @TOTALORGANIZATIONGAINLOSS, @ORGANIZATIONCURRENCYDECIMALDIGITS)
,@BASECURRENCYID = DISTRIBUTIONS.BASECURRENCYID
,@TRANSACTIONCURRENCYID = DISTRIBUTIONS.TRANSACTIONCURRENCYID
,@BASEEXCHANGERATEID = DISTRIBUTIONS.BASEEXCHANGERATEID
,@ORGEXCHANGERATEID = DISTRIBUTIONS.ORGANIZATIONEXCHANGERATEID
from
@DISTRIBUTIONS DISTRIBUTIONS
where
DISTRIBUTIONS.REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
and DISTRIBUTIONS.REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID
if @PAYMENTCURSOR_PAYMENTCOUNT <> @AUCTIONITEM_CURRENTPAYMENTCOUNT
begin
-- If this is not the last payment, we need to calculate the distribution amounts.
-- The "wash" and gain/loss amounts are proportions of the payment amount towards
-- the entire purchase amount of the auction item donation.
-- Example: If 3 payment pay for an item... One payment is half the total purchase price, and the
-- other two payments are for one quarter of the total purchase price... The payment
-- for half the total purchase price needs to receive half of the "wash" and gain/loss
-- amounts (if gain/loss exists). The other payments will receive one quarter of the
-- "wash" and gain/loss amounts.
select top 1
@DISTRIBUTED_ITEM_TRANSACTIONAMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(DISTRIBUTIONS.REVENUESPLITTRANSACTIONAMOUNT, DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, @ITEM_TRANSACTIONAMOUNT_TO_DISTRIBUTE, @PAYMENTCURSOR_ITEMTRANSACTIONCURRENCYDECIMALDIGITS),
@DISTRIBUTED_ITEM_AMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(DISTRIBUTIONS.REVENUESPLITTRANSACTIONAMOUNT, DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, @ITEM_AMOUNT_TO_DISTRIBUTE, @PAYMENTCURSOR_ITEMBASECURRENCYDECIMALDIGITS),
@DISTRIBUTED_ITEM_ORGANIZATIONAMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(DISTRIBUTIONS.REVENUESPLITTRANSACTIONAMOUNT, DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, @ITEM_ORGANIZATIONAMOUNT_TO_DISTRIBUTE, @ORGANIZATIONCURRENCYDECIMALDIGITS),
@DISTRIBUTED_CURRENCYGAINLOSS_AMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(DISTRIBUTIONS.REVENUESPLITTRANSACTIONAMOUNT, DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, @TOTALBASECURRENCYGAINLOSS, @PAYMENTCURSOR_ITEMBASECURRENCYDECIMALDIGITS),
@DISTRIBUTED_CURRENCYGAINLOSS_ORGANIZATIONAMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(DISTRIBUTIONS.REVENUESPLITTRANSACTIONAMOUNT, DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, @TOTALORGANIZATIONCURRENCYGAINLOSS, @ORGANIZATIONCURRENCYDECIMALDIGITS)
from
@DISTRIBUTIONS DISTRIBUTIONS
where
REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
and REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID
select top 1
@DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT = dbo.UFN_AUCTIONPACKAGE_CONVERTBYPROPORTION_UNROUNDED(DISTRIBUTIONS.REVENUESPLITTRANSACTIONAMOUNT, DISTRIBUTIONS.AUCTIONITEMTRANSACTIONPURCHASEAMOUNT, @GAINLOSS_TRANSACTIONAMOUNT_TO_DISTRIBUTE, @PAYMENTCURSOR_ITEMTRANSACTIONCURRENCYDECIMALDIGITS)
from
@DISTRIBUTIONS DISTRIBUTIONS
where
REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
and REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID;
set @DISTRIBUTED_GAINLOSS_AMOUNT = case when isnull(@TRANSACTIONCURRENCYID, @BASECURRENCYID) = @BASECURRENCYID then @DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT else dbo.UFN_CURRENCY_CONVERT(@DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT, @BASEEXCHANGERATEID) end
set @DISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT =
case when @ORGANIZATIONAMOUNTORIGINCODE = 0
then case when @BASECURRENCYID = @ORGANIZATIONCURRENCYID
then @DISTRIBUTED_GAINLOSS_AMOUNT
else dbo.UFN_CURRENCY_CONVERT(@DISTRIBUTED_GAINLOSS_AMOUNT, @ORGEXCHANGERATEID)
end
else case when isnull(@TRANSACTIONCURRENCYID, @ORGANIZATIONCURRENCYID) = @ORGANIZATIONCURRENCYID
then @DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT
else dbo.UFN_CURRENCY_CONVERT(@DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT, @ORGEXCHANGERATEID)
end
end
-- Keep track of how much we have already distributed
set @TOTALDISTRIBUTED_ITEM_TRANSACTIONAMOUNT = @TOTALDISTRIBUTED_ITEM_TRANSACTIONAMOUNT + @DISTRIBUTED_ITEM_TRANSACTIONAMOUNT;
set @TOTALDISTRIBUTED_ITEM_AMOUNT = @TOTALDISTRIBUTED_ITEM_AMOUNT + @DISTRIBUTED_ITEM_AMOUNT;
set @TOTALDISTRIBUTED_ITEM_ORGANIZATIONAMOUNT = @TOTALDISTRIBUTED_ITEM_ORGANIZATIONAMOUNT + @DISTRIBUTED_ITEM_ORGANIZATIONAMOUNT;
set @TOTALDISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT = @TOTALDISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT + @DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT;
set @TOTALDISTRIBUTED_GAINLOSS_AMOUNT = @TOTALDISTRIBUTED_GAINLOSS_AMOUNT + @DISTRIBUTED_GAINLOSS_AMOUNT;
set @TOTALDISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT = @TOTALDISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT + @DISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT;
set @TOTALDISTRIBUTED_CURRENCYGAINLOSS_AMOUNT = @TOTALDISTRIBUTED_CURRENCYGAINLOSS_AMOUNT + @DISTRIBUTED_CURRENCYGAINLOSS_AMOUNT;
set @TOTALDISTRIBUTED_CURRENCYGAINLOSS_ORGANIZATIONAMOUNT = @TOTALDISTRIBUTED_CURRENCYGAINLOSS_ORGANIZATIONAMOUNT + @DISTRIBUTED_CURRENCYGAINLOSS_ORGANIZATIONAMOUNT;
-- Update the amount fields in the distributions temp table
update @DISTRIBUTIONS
set AMOUNT =
case
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 0 then
REVENUESPLITAMOUNT
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 1 then
@DISTRIBUTED_ITEM_AMOUNT
when REVENUESPLITTYPECODE in (203,204) then
@DISTRIBUTED_GAINLOSS_AMOUNT
else
0
end,
TRANSACTIONAMOUNT =
case
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 0 then
REVENUESPLITTRANSACTIONAMOUNT
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 1 then
@DISTRIBUTED_ITEM_TRANSACTIONAMOUNT
when REVENUESPLITTYPECODE in (203,204) then
@DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT
else
0
end,
ORGANIZATIONAMOUNT =
case
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 0 then
REVENUESPLITORGANIZATIONAMOUNT
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 1 then
@DISTRIBUTED_ITEM_ORGANIZATIONAMOUNT
when REVENUESPLITTYPECODE in (203,204) then
@DISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT
else
0
end
where REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID and REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
end
else
begin
--The last payment towards an item deals with the rounding issues
--It gets what is left to distribute
declare @GAINLOSS_TRAN money = abs(@GAINLOSS_TRANSACTIONAMOUNT_TO_DISTRIBUTE - @TOTALDISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT);
declare @GAINLOSS_BASE money = case when isnull(@TRANSACTIONCURRENCYID, @BASECURRENCYID) = @BASECURRENCYID then @GAINLOSS_TRAN else dbo.UFN_CURRENCY_CONVERT(@GAINLOSS_TRAN, @BASEEXCHANGERATEID) end;
declare @GAINLOSS_ORG money =
case when @ORGANIZATIONAMOUNTORIGINCODE = 0
then case when @BASECURRENCYID = @ORGANIZATIONCURRENCYID
then @GAINLOSS_BASE
else dbo.UFN_CURRENCY_CONVERT(@GAINLOSS_BASE, @ORGEXCHANGERATEID)
end
else case when isnull(@TRANSACTIONCURRENCYID, @ORGANIZATIONCURRENCYID) = @ORGANIZATIONCURRENCYID
then @GAINLOSS_TRAN
else dbo.UFN_CURRENCY_CONVERT(@GAINLOSS_TRAN, @ORGEXCHANGERATEID)
end
end;
update @DISTRIBUTIONS
set AMOUNT =
case
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 0 then
REVENUESPLITAMOUNT
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 1 then
abs(@ITEM_AMOUNT_TO_DISTRIBUTE - @TOTALDISTRIBUTED_ITEM_AMOUNT)
when REVENUESPLITTYPECODE in (203,204) then
@GAINLOSS_BASE
else
0
end,
TRANSACTIONAMOUNT =
case
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 0 then
REVENUESPLITTRANSACTIONAMOUNT
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 1 then
abs(@ITEM_TRANSACTIONAMOUNT_TO_DISTRIBUTE - @TOTALDISTRIBUTED_ITEM_TRANSACTIONAMOUNT)
when REVENUESPLITTYPECODE in (203,204) then
@GAINLOSS_TRAN
else
0
end,
ORGANIZATIONAMOUNT =
case
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 0 then
REVENUESPLITORGANIZATIONAMOUNT
when REVENUESPLITTYPECODE = 12 and TRANSACTIONTYPECODE = 1 then
abs(@ITEM_ORGANIZATIONAMOUNT_TO_DISTRIBUTE - @TOTALDISTRIBUTED_ITEM_ORGANIZATIONAMOUNT)
when REVENUESPLITTYPECODE in (203,204)then
@GAINLOSS_ORG
else
0
end
where REVENUEID = @PAYMENTCURSOR_REVENUEAUCTIONDONATIONID and REVENUEPURCHASEID = @PAYMENTCURSOR_REVENUEPURCHASEID
end
--Reset the payment count and totals if we are moving on to a different item
if @PAYMENTCURSOR_PAYMENTCOUNT = @AUCTIONITEM_CURRENTPAYMENTCOUNT
begin
select @AUCTIONITEM_CURRENTPAYMENTCOUNT = 0;
select @GAINLOSS_TRANSACTIONAMOUNT_TO_DISTRIBUTE = 0;
--select @GAINLOSS_AMOUNT_TO_DISTRIBUTE = 0;
--select @GAINLOSS_ORGANIZATIONAMOUNT_TO_DISTRIBUTE = 0;
select @DISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT = 0;
select @DISTRIBUTED_GAINLOSS_AMOUNT = 0;
select @DISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT = 0;
select @TOTALDISTRIBUTED_GAINLOSS_TRANSACTIONAMOUNT = 0;
select @TOTALDISTRIBUTED_GAINLOSS_AMOUNT = 0;
select @TOTALDISTRIBUTED_GAINLOSS_ORGANIZATIONAMOUNT = 0;
select @ITEM_TRANSACTIONAMOUNT_TO_DISTRIBUTE = 0;
select @ITEM_AMOUNT_TO_DISTRIBUTE = 0;
select @ITEM_ORGANIZATIONAMOUNT_TO_DISTRIBUTE = 0;
select @DISTRIBUTED_ITEM_TRANSACTIONAMOUNT = 0;
select @DISTRIBUTED_ITEM_AMOUNT = 0;
select @DISTRIBUTED_ITEM_ORGANIZATIONAMOUNT = 0;
select @TOTALDISTRIBUTED_ITEM_TRANSACTIONAMOUNT = 0;
select @TOTALDISTRIBUTED_ITEM_AMOUNT = 0;
select @TOTALDISTRIBUTED_ITEM_ORGANIZATIONAMOUNT = 0;
select @TOTALBASEGAINLOSS = 0;
select @TOTALORGANIZATIONGAINLOSS = 0;
select @TOTALITEMBASEGAINLOSS = 0;
select @TOTALITEMORGANIZATIONGAINLOSS = 0;
select @TOTALBASECURRENCYGAINLOSS = 0;
select @TOTALORGANIZATIONCURRENCYGAINLOSS = 0;
select @DISTRIBUTED_CURRENCYGAINLOSS_AMOUNT = 0;
select @DISTRIBUTED_CURRENCYGAINLOSS_ORGANIZATIONAMOUNT = 0;
select @TOTALDISTRIBUTED_CURRENCYGAINLOSS_AMOUNT = 0;
select @TOTALDISTRIBUTED_CURRENCYGAINLOSS_ORGANIZATIONAMOUNT = 0;
end
fetch next from PAYMENTCURSOR into
@PAYMENTCURSOR_REVENUEAUCTIONDONATIONID,@PAYMENTCURSOR_REVENUEPURCHASEID,@PAYMENTCURSOR_AUCTIONITEMID,
@PAYMENTCURSOR_PAYMENTCOUNT,@PAYMENTCURSOR_ITEMTRANSACTIONCURRENCYID,@PAYMENTCURSOR_ITEMBASECURRENCYID,
@PAYMENTCURSOR_ITEMVALUE,@PAYMENTCURSOR_ITEMTRANSACTIONVALUE,@PAYMENTCURSOR_ITEMORGANIZATIONVALUE,
@PAYMENTCURSOR_ITEMBASECURRENCYDECIMALDIGITS, @PAYMENTCURSOR_ITEMTRANSACTIONCURRENCYDECIMALDIGITS
end
close PAYMENTCURSOR
deallocate PAYMENTCURSOR
return
end