UFN_REVENUE_GETGIFTINKINDPAYMENTMETHODDETAILSPLITSBYTRANSACTION
Generates the splits for Gift-in-kind Gain/Loss by transaction.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @TRANSACTIONID | uniqueidentifier | IN | |
| @PAYMENTMETHODCODE | int | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUE_GETGIFTINKINDPAYMENTMETHODDETAILSPLITSBYTRANSACTION
(
@TRANSACTIONID uniqueidentifier,
@PAYMENTMETHODCODE int
)
returns @SPLITS TABLE
(
REVENUESPLITID uniqueidentifier,
AMOUNT money,
TRANSACTIONAMOUNT money,
REVENUEID uniqueidentifier,
GIFTINKINDSALEID uniqueidentifier,
TRANSACTIONSPLITAMOUNT money,
ORGANIZATIONAMOUNT money
)
as
begin
--Retrieve information about the revenue record.
declare @TRANSACTIONREVENUEAMOUNT money;
declare @BASEREVENUEAMOUNT money;
declare @ORGANIZATIONREVENUEAMOUNT money;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier;
declare @TOTALGIFTINKINDCOUNT int
select
@TRANSACTIONREVENUEAMOUNT = REVENUE.TRANSACTIONAMOUNT,
@BASEREVENUEAMOUNT = REVENUE.AMOUNT,
@ORGANIZATIONREVENUEAMOUNT = REVENUE.ORGANIZATIONAMOUNT,
@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = REVENUE.BASECURRENCYID,
@ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
@TOTALGIFTINKINDCOUNT = GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS
from dbo.REVENUE with (nolock)
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
where REVENUE.ID = @TRANSACTIONID;
--Count the number of splits for aggregation later.
declare @NUMBEROFSPLITS int
select @NUMBEROFSPLITS = count(ID)
from dbo.REVENUESPLIT
where REVENUEID = @TRANSACTIONID;
--Count the number of Gift-in-kind sales and units of sold Gift-in-kind for aggregation later.
declare @NUMBEROFGIFTINKINDSALES int;
declare @SOLDGIFTINKINDCOUNT int;
select
@NUMBEROFGIFTINKINDSALES = count(GIFTINKINDSALE.ID),
@SOLDGIFTINKINDCOUNT = sum(GIFTINKINDSALE.NUMBEROFUNITS)
from dbo.GIFTINKINDSALE with (nolock)
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;
--Has all the Gift-in-kind been sold?
declare @ALLGIFTINKINDSOLD bit = 0;
if @SOLDGIFTINKINDCOUNT >= @TOTALGIFTINKINDCOUNT
begin
set @ALLGIFTINKINDSOLD = 1;
end
--Retrieve decimal digit values for each currency for rounding later.
declare @TRANSACTIONCURRENCYDECIMALDIGITS int
select @TRANSACTIONCURRENCYDECIMALDIGITS = DECIMALDIGITS
from dbo.CURRENCY
where ID = @TRANSACTIONCURRENCYID;
declare @BASECURRENCYDECIMALDIGITS int
select @BASECURRENCYDECIMALDIGITS = DECIMALDIGITS
from dbo.CURRENCY
where ID = @BASECURRENCYID;
declare @ORGANIZATIONCURRENCYDECIMALDIGITS int
select @ORGANIZATIONCURRENCYDECIMALDIGITS = DECIMALDIGITS
from dbo.CURRENCY
where ID = @ORGANIZATIONCURRENCYID;
--Values needed for math in the cursor, regardless of PAYMENTMETHODCODE
declare @TRANSACTIONIDEALAMOUNT money;
declare @TRANSACTIONWEIGHTEDAMOUNT money;
declare @BASEWEIGHTEDAMOUNT money;
declare @ORGANIZATIONWEIGHTEDAMOUNT money;
declare @TRANSACTIONAMOUNTROLLUP money;
declare @BASEAMOUNTROLLUP money;
declare @ORGANIZATIONAMOUNTROLLUP money;
declare @TRANSACTIONSPLITAMOUNTDISTRIBUTEDIDEAL money = 0;
declare @TRANSACTIONSPLITAMOUNTDISTRIBUTED money = 0;
declare @BASESPLITAMOUNTDISTRIBUTED money = 0;
declare @ORGANIZATIONSPLITAMOUNTDISTRIBUTED money = 0;
declare @SPLITIDEALAMOUNTLIST table
(
SPLITID uniqueidentifier,
IDEALAMOUNT money,
TRANSACTIONDISTRIBUTEDAMOUNT money,
BASEDISTRIBUTEDAMOUNT money,
ORGANIZATIONDISTRIBUTEDAMOUNT money
);
declare @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTEDIDEAL money = 0;
declare @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED money = 0;
declare @BASEGIFTINKINDSALEAMOUNTDISTRIBUTED money = 0;
declare @ORGANIZATIONGIFTINKINDSALEAMOUNTDISTRIBUTED money = 0;
declare @GIFTINKINDSALEIDEALAMOUNTLIST table
(
GIFTINKINDSALEID uniqueidentifier,
IDEALAMOUNT money,
TRANSACTIONDISTRIBUTEDAMOUNT money,
BASEDISTRIBUTEDAMOUNT money,
ORGANIZATIONDISTRIBUTEDAMOUNT money
);
declare @SPLITCOUNT int = 1;
declare @GIFTINKINDSALECOUNT int = 0;
--Hold values from all cursors
declare @REVENUESPLITID uniqueidentifier;
declare @GIFTINKINDSALEID uniqueidentifier;
declare @TRANSACTIONSPLITAMOUNT money;
--If generating for Gift-in-kind sold or gain/loss GL, we need to calculate expected amounts
if @PAYMENTMETHODCODE in (12, 207, 208)
begin
--Hold values from expected amount cursor
declare @GIFTINKINDSALENUMBEROFUNITS int;
--Fill tables to hold accumulations with IDs for relevant splits and Gift-in-kind sales.
insert into @SPLITIDEALAMOUNTLIST(SPLITID)
select ID from REVENUESPLIT where REVENUEID = @TRANSACTIONID;
insert into @GIFTINKINDSALEIDEALAMOUNTLIST(GIFTINKINDSALEID)
select GIFTINKINDSALE.ID
from dbo.GIFTINKINDSALE with (nolock)
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;
--Expected amount cursor will need IDs, split amounts per split, and the number of units of Gift-in-kind sold per Gift-in-kind sale.
-- Order by ensures that we iterate over all the Gift-in-kind sales with a given split before going to the next split,
-- which is necessary because we have solid expected amount totals for splits, but not Gift-in-kind sales.
declare EXPECTEDAMOUNT_CURSOR cursor local fast_forward for
select
REVENUESPLIT.ID as REVENUESPLITID,
GIFTINKINDSALE.ID as GIFTINKINDSALEID,
REVENUESPLIT.TRANSACTIONAMOUNT,
GIFTINKINDSALE.NUMBEROFUNITS
from dbo.REVENUE with (nolock)
inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDPAYMENTMETHODDETAIL.ID
where
REVENUE.ID = @TRANSACTIONID
order by REVENUESPLIT.ID, GIFTINKINDSALE.ID
open EXPECTEDAMOUNT_CURSOR;
fetch next from EXPECTEDAMOUNT_CURSOR into @REVENUESPLITID, @GIFTINKINDSALEID, @TRANSACTIONSPLITAMOUNT, @GIFTINKINDSALENUMBEROFUNITS;
while @@FETCH_STATUS = 0 begin
set @GIFTINKINDSALECOUNT = @GIFTINKINDSALECOUNT + 1;
--Retrieve accumulated values.
select
@TRANSACTIONSPLITAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT,0),
@TRANSACTIONSPLITAMOUNTDISTRIBUTED = coalesce(TRANSACTIONDISTRIBUTEDAMOUNT,0),
@BASESPLITAMOUNTDISTRIBUTED = coalesce(BASEDISTRIBUTEDAMOUNT,0),
@ORGANIZATIONSPLITAMOUNTDISTRIBUTED = coalesce(ORGANIZATIONDISTRIBUTEDAMOUNT,0)
from @SPLITIDEALAMOUNTLIST
where SPLITID = @REVENUESPLITID
select
@TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT,0),
@TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED = coalesce(TRANSACTIONDISTRIBUTEDAMOUNT,0),
@BASEGIFTINKINDSALEAMOUNTDISTRIBUTED = coalesce(BASEDISTRIBUTEDAMOUNT,0),
@ORGANIZATIONGIFTINKINDSALEAMOUNTDISTRIBUTED = coalesce(ORGANIZATIONDISTRIBUTEDAMOUNT,0)
from @GIFTINKINDSALEIDEALAMOUNTLIST
where GIFTINKINDSALEID = @GIFTINKINDSALEID
--If any Gift-in-kind as been sold, calculate the ideal expected amount in transaction currency
if @TOTALGIFTINKINDCOUNT <> 0
begin
set @TRANSACTIONIDEALAMOUNT = (@TRANSACTIONSPLITAMOUNT * @GIFTINKINDSALENUMBEROFUNITS) / @TOTALGIFTINKINDCOUNT;
end
else
begin
set @TRANSACTIONIDEALAMOUNT = 0;
end
--If we aren't on the last split, roll up and convert by proportion using the total applied to the current split.
if @SPLITCOUNT < @NUMBEROFSPLITS
begin
set @TRANSACTIONWEIGHTEDAMOUNT = round(@TRANSACTIONIDEALAMOUNT + @TRANSACTIONSPLITAMOUNTDISTRIBUTEDIDEAL - @TRANSACTIONSPLITAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS);
select
@BASEWEIGHTEDAMOUNT = BASEAMOUNT - @BASESPLITAMOUNTDISTRIBUTED,
@ORGANIZATIONWEIGHTEDAMOUNT = ORGANIZATIONAMOUNT - @ORGANIZATIONSPLITAMOUNTDISTRIBUTED
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
(@TRANSACTIONWEIGHTEDAMOUNT + @TRANSACTIONSPLITAMOUNTDISTRIBUTED),
@TRANSACTIONREVENUEAMOUNT,
@BASECURRENCYID,
@BASEREVENUEAMOUNT,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@ORGANIZATIONREVENUEAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
end
else --We are on the last split, so roll up and convert by proportion using the total applied to the current Gift-in-kind sale.
begin
set @TRANSACTIONWEIGHTEDAMOUNT = round(@TRANSACTIONIDEALAMOUNT + @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTEDIDEAL - @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS);
select
@BASEWEIGHTEDAMOUNT = BASEAMOUNT - @BASEGIFTINKINDSALEAMOUNTDISTRIBUTED,
@ORGANIZATIONWEIGHTEDAMOUNT = ORGANIZATIONAMOUNT - @ORGANIZATIONGIFTINKINDSALEAMOUNTDISTRIBUTED
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
(@TRANSACTIONWEIGHTEDAMOUNT + @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED),
@TRANSACTIONREVENUEAMOUNT,
@BASECURRENCYID,
@BASEREVENUEAMOUNT,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@ORGANIZATIONREVENUEAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
end
--Populate return table
insert into @SPLITS (
REVENUESPLITID,
TRANSACTIONAMOUNT,
REVENUEID,
GIFTINKINDSALEID,
TRANSACTIONSPLITAMOUNT,
AMOUNT,
ORGANIZATIONAMOUNT
)
values(
@REVENUESPLITID,
@TRANSACTIONREVENUEAMOUNT,
@TRANSACTIONID,
@GIFTINKINDSALEID,
@TRANSACTIONWEIGHTEDAMOUNT,
@BASEWEIGHTEDAMOUNT,
@ORGANIZATIONWEIGHTEDAMOUNT
);
--Update accumulation tables.
update @SPLITIDEALAMOUNTLIST
set
IDEALAMOUNT = @TRANSACTIONSPLITAMOUNTDISTRIBUTEDIDEAL + @TRANSACTIONIDEALAMOUNT,
TRANSACTIONDISTRIBUTEDAMOUNT = @TRANSACTIONSPLITAMOUNTDISTRIBUTED + @TRANSACTIONWEIGHTEDAMOUNT,
BASEDISTRIBUTEDAMOUNT = @BASESPLITAMOUNTDISTRIBUTED + @BASEWEIGHTEDAMOUNT,
ORGANIZATIONDISTRIBUTEDAMOUNT = @ORGANIZATIONSPLITAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTEDAMOUNT
where SPLITID = @REVENUESPLITID
update @GIFTINKINDSALEIDEALAMOUNTLIST
set
IDEALAMOUNT = @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTEDIDEAL + @TRANSACTIONIDEALAMOUNT,
TRANSACTIONDISTRIBUTEDAMOUNT = @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED + @TRANSACTIONWEIGHTEDAMOUNT,
BASEDISTRIBUTEDAMOUNT = @BASEGIFTINKINDSALEAMOUNTDISTRIBUTED + @BASEWEIGHTEDAMOUNT,
ORGANIZATIONDISTRIBUTEDAMOUNT = @ORGANIZATIONGIFTINKINDSALEAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTEDAMOUNT
where GIFTINKINDSALEID = @GIFTINKINDSALEID
--If we've gone through all the Gift-in-kind sales for the currency split, so reset the Gift-in-kind sale counter and increment the split counter.
if @GIFTINKINDSALECOUNT >= @NUMBEROFGIFTINKINDSALES
begin
set @GIFTINKINDSALECOUNT = 0;
set @SPLITCOUNT = @SPLITCOUNT + 1;
end
fetch next from EXPECTEDAMOUNT_CURSOR into @REVENUESPLITID, @GIFTINKINDSALEID, @TRANSACTIONSPLITAMOUNT, @GIFTINKINDSALENUMBEROFUNITS;
end
close EXPECTEDAMOUNT_CURSOR;
deallocate EXPECTEDAMOUNT_CURSOR;
--If generating for gain/loss GL, we need to calculate difference between expected and actual amounts.
if @PAYMENTMETHODCODE in (207, 208)
begin
--Table to hold expected amounts so we can clear the return table, as we don't actually want to send back expected amounts.
declare @EXPECTEDAMOUNTS table
(
REVENUESPLITID uniqueidentifier,
GIFTINKINDSALEID uniqueidentifier,
EXPECTEDAMOUNT money
);
insert into @EXPECTEDAMOUNTS
select
REVENUESPLITID,
GIFTINKINDSALEID,
TRANSACTIONSPLITAMOUNT
from @SPLITS
delete @SPLITS
--Hold values from revenue gain/loss cursor
declare @TRANSACTIONGIFTINKINDSALESALEAMOUNT money;
declare @BASEGIFTINKINDSALESALEAMOUNT money;
declare @ORGANIZATIONGIFTINKINDSALESALEAMOUNT money;
declare @TRANSACTIONGIFTINKINDSALESPLITEXPECTEDAMOUNT money;
--Reset counters.
set @SPLITCOUNT = 0;
set @GIFTINKINDSALECOUNT = 1;
--Clear, then fill tables to hold accumulations with IDs for relevant splits and Gift-in-kind sales.
delete @SPLITIDEALAMOUNTLIST
insert into @SPLITIDEALAMOUNTLIST(SPLITID)
select ID from REVENUESPLIT where REVENUEID = @TRANSACTIONID
delete @GIFTINKINDSALEIDEALAMOUNTLIST
insert into @GIFTINKINDSALEIDEALAMOUNTLIST(GIFTINKINDSALEID)
select GIFTINKINDSALE.ID
from dbo.GIFTINKINDSALE with (nolock)
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = GIFTINKINDPAYMENTMETHODDETAIL.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;
--Revenue gain/loss cursor will need IDs, split amounts per split, and sale amounts per Gift-in-kind sale.
-- Order by ensures that we iterate over all the splits with a given Gift-in-kind sale before going to the next Gift-in-kind sale,
-- which is necessary because we have solid actual amount totals for Gift-in-kind sales, but not splits.
declare GAINLOSS_CURSOR cursor local fast_forward for
select
REVENUESPLIT.ID,
GIFTINKINDSALE.ID,
REVENUESPLIT.TRANSACTIONAMOUNT,
GIFTINKINDSALE.TRANSACTIONSALEAMOUNT,
GIFTINKINDSALE.SALEAMOUNT,
GIFTINKINDSALE.ORGANIZATIONSALEAMOUNT
from dbo.REVENUE with (nolock)
inner join dbo.REVENUESPLIT with (nolock) on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
inner join dbo.GIFTINKINDSALE on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDPAYMENTMETHODDETAIL.ID
where
REVENUE.ID = @TRANSACTIONID
order by GIFTINKINDSALE.ID, REVENUESPLIT.ID
open GAINLOSS_CURSOR;
fetch next from GAINLOSS_CURSOR into @REVENUESPLITID, @GIFTINKINDSALEID, @TRANSACTIONSPLITAMOUNT, @TRANSACTIONGIFTINKINDSALESALEAMOUNT, @BASEGIFTINKINDSALESALEAMOUNT, @ORGANIZATIONGIFTINKINDSALESALEAMOUNT;
while @@FETCH_STATUS = 0 begin
set @SPLITCOUNT = @SPLITCOUNT + 1;
--Retrieve accumulated values.
select
@TRANSACTIONSPLITAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT,0),
@TRANSACTIONSPLITAMOUNTDISTRIBUTED = coalesce(TRANSACTIONDISTRIBUTEDAMOUNT,0),
@BASESPLITAMOUNTDISTRIBUTED = coalesce(BASEDISTRIBUTEDAMOUNT,0),
@ORGANIZATIONSPLITAMOUNTDISTRIBUTED = coalesce(ORGANIZATIONDISTRIBUTEDAMOUNT,0)
from @SPLITIDEALAMOUNTLIST
where SPLITID = @REVENUESPLITID
select
@TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT,0),
@TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED = coalesce(TRANSACTIONDISTRIBUTEDAMOUNT,0),
@BASEGIFTINKINDSALEAMOUNTDISTRIBUTED = coalesce(BASEDISTRIBUTEDAMOUNT,0),
@ORGANIZATIONGIFTINKINDSALEAMOUNTDISTRIBUTED = coalesce(ORGANIZATIONDISTRIBUTEDAMOUNT,0)
from @GIFTINKINDSALEIDEALAMOUNTLIST
where GIFTINKINDSALEID = @GIFTINKINDSALEID
--If revenue has an amount, calculate the ideal expected amount in transaction currency
if @TRANSACTIONREVENUEAMOUNT <> 0
begin
select @TRANSACTIONGIFTINKINDSALESPLITEXPECTEDAMOUNT = EXPECTEDAMOUNT
from @EXPECTEDAMOUNTS
where REVENUESPLITID = @REVENUESPLITID and GIFTINKINDSALEID = @GIFTINKINDSALEID;
set @TRANSACTIONIDEALAMOUNT = abs(@TRANSACTIONGIFTINKINDSALESPLITEXPECTEDAMOUNT - (@TRANSACTIONGIFTINKINDSALESALEAMOUNT * @TRANSACTIONSPLITAMOUNT) / @TRANSACTIONREVENUEAMOUNT);
end
else
begin
set @TRANSACTIONIDEALAMOUNT = 0;
end
--If the ideal amount is 0, then there was no gain/loss, so don't generate GL distributions.
if @TRANSACTIONIDEALAMOUNT <> 0
begin
--If we aren't on the last Gift-in-kind sale or not all the Gift-in-kind has been sold, roll up and convert by proportion using the total applied to the current Gift-in-kind sale.
if @GIFTINKINDSALECOUNT < @NUMBEROFGIFTINKINDSALES or @ALLGIFTINKINDSOLD = 0
begin
set @TRANSACTIONWEIGHTEDAMOUNT = (ROUND(@TRANSACTIONIDEALAMOUNT + @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTEDIDEAL - @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS));
select
@BASEWEIGHTEDAMOUNT = BASEAMOUNT - @BASEGIFTINKINDSALEAMOUNTDISTRIBUTED,
@ORGANIZATIONWEIGHTEDAMOUNT = ORGANIZATIONAMOUNT - @ORGANIZATIONGIFTINKINDSALEAMOUNTDISTRIBUTED
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
(@TRANSACTIONWEIGHTEDAMOUNT + @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED),
@TRANSACTIONREVENUEAMOUNT,
@BASECURRENCYID,
@BASEREVENUEAMOUNT,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@ORGANIZATIONREVENUEAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
end
else --We are on the Gift-in-kind sale and have sold all the Gift-in-kind, so roll up and convert by proportion using the total applied to the current split.
begin
set @TRANSACTIONWEIGHTEDAMOUNT = (ROUND(@TRANSACTIONIDEALAMOUNT + @TRANSACTIONSPLITAMOUNTDISTRIBUTEDIDEAL - @TRANSACTIONSPLITAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS));
select
@BASEWEIGHTEDAMOUNT = BASEAMOUNT - @BASESPLITAMOUNTDISTRIBUTED,
@ORGANIZATIONWEIGHTEDAMOUNT = ORGANIZATIONAMOUNT - @ORGANIZATIONSPLITAMOUNTDISTRIBUTED
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
(@TRANSACTIONWEIGHTEDAMOUNT + @TRANSACTIONSPLITAMOUNTDISTRIBUTED),
@TRANSACTIONREVENUEAMOUNT,
@BASECURRENCYID,
@BASEREVENUEAMOUNT,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@ORGANIZATIONREVENUEAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
end
--Populate return table
insert into @SPLITS (
REVENUESPLITID,
TRANSACTIONAMOUNT,
REVENUEID,
GIFTINKINDSALEID,
TRANSACTIONSPLITAMOUNT,
AMOUNT,
ORGANIZATIONAMOUNT
)
values(
@REVENUESPLITID,
@TRANSACTIONREVENUEAMOUNT,
@TRANSACTIONID,
@GIFTINKINDSALEID,
@TRANSACTIONWEIGHTEDAMOUNT,
@BASEWEIGHTEDAMOUNT,
@ORGANIZATIONWEIGHTEDAMOUNT
);
--Update accumulation tables.
update @SPLITIDEALAMOUNTLIST
set
IDEALAMOUNT = @TRANSACTIONSPLITAMOUNTDISTRIBUTEDIDEAL + @TRANSACTIONIDEALAMOUNT,
TRANSACTIONDISTRIBUTEDAMOUNT = @TRANSACTIONSPLITAMOUNTDISTRIBUTED + @TRANSACTIONWEIGHTEDAMOUNT,
BASEDISTRIBUTEDAMOUNT = @BASESPLITAMOUNTDISTRIBUTED + @BASEWEIGHTEDAMOUNT,
ORGANIZATIONDISTRIBUTEDAMOUNT = @ORGANIZATIONSPLITAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTEDAMOUNT
where SPLITID = @REVENUESPLITID;
update @GIFTINKINDSALEIDEALAMOUNTLIST
set
IDEALAMOUNT = @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTEDIDEAL + @TRANSACTIONIDEALAMOUNT,
TRANSACTIONDISTRIBUTEDAMOUNT = @TRANSACTIONGIFTINKINDSALEAMOUNTDISTRIBUTED + @TRANSACTIONWEIGHTEDAMOUNT,
BASEDISTRIBUTEDAMOUNT = @BASEGIFTINKINDSALEAMOUNTDISTRIBUTED + @BASEWEIGHTEDAMOUNT,
ORGANIZATIONDISTRIBUTEDAMOUNT = @ORGANIZATIONGIFTINKINDSALEAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTEDAMOUNT
where GIFTINKINDSALEID = @GIFTINKINDSALEID;
end
--If we've gone through all the splits for the currency split, so reset the split counter and increment the Gift-in-kind sale counter.
if @SPLITCOUNT >= @NUMBEROFSPLITS
begin
set @SPLITCOUNT = 0;
set @GIFTINKINDSALECOUNT = @GIFTINKINDSALECOUNT + 1;
end
fetch next from GAINLOSS_CURSOR into @REVENUESPLITID, @GIFTINKINDSALEID, @TRANSACTIONSPLITAMOUNT, @TRANSACTIONGIFTINKINDSALESALEAMOUNT, @BASEGIFTINKINDSALESALEAMOUNT, @ORGANIZATIONGIFTINKINDSALESALEAMOUNT;
end
close GAINLOSS_CURSOR;
deallocate GAINLOSS_CURSOR;
end
end
return;
end