UFN_REVENUE_GETSTOCKDETAILSPLITSBYTRANSACTION
Generates the splits for stock gain/loss and brokerfee 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_GETSTOCKDETAILSPLITSBYTRANSACTION
(
@TRANSACTIONID uniqueidentifier,
@PAYMENTMETHODCODE int
)
returns @SPLITS TABLE
(
REVENUESPLITID uniqueidentifier,
AMOUNT money,
TRANSACTIONAMOUNT money,
REVENUEID uniqueidentifier,
STOCKSALEID 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 @TOTALSTOCKCOUNT decimal(21,4);
declare @SALETRANSACTIONAMOUNT money;
declare @SALEBASEAMOUNT money;
declare @SALEORGAMOUNT money;
select
@TRANSACTIONREVENUEAMOUNT = REVENUE.TRANSACTIONAMOUNT,
@BASEREVENUEAMOUNT = REVENUE.AMOUNT,
@ORGANIZATIONREVENUEAMOUNT = REVENUE.ORGANIZATIONAMOUNT,
@TRANSACTIONCURRENCYID = REVENUE.TRANSACTIONCURRENCYID,
@BASECURRENCYID = REVENUE.BASECURRENCYID,
@ORGANIZATIONCURRENCYID = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY(),
@TOTALSTOCKCOUNT = STOCKDETAIL.NUMBEROFUNITS
from dbo.REVENUE with (nolock)
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.REVENUEID = REVENUE.ID
inner join dbo.STOCKDETAIL on STOCKDETAIL.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 stock sales and units of sold stock for aggregation later.
declare @NUMBEROFSTOCKSALES int;
declare @SOLDSTOCKCOUNT decimal(21,4);
select
@NUMBEROFSTOCKSALES = count(STOCKSALE.ID),
@SOLDSTOCKCOUNT = sum(STOCKSALE.NUMBEROFUNITS)
from dbo.STOCKSALE with (nolock)
inner join dbo.STOCKDETAIL on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;
--Has all the stock been sold?
declare @ALLSTOCKSOLD bit = 0;
if @SOLDSTOCKCOUNT >= @TOTALSTOCKCOUNT
begin
set @ALLSTOCKSOLD = 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 @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL money = 0;
declare @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED money = 0;
declare @BASESTOCKSALEAMOUNTDISTRIBUTED money = 0;
declare @ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED money = 0;
declare @STOCKSALEIDEALAMOUNTLIST table
(
STOCKSALEID uniqueidentifier,
IDEALAMOUNT money,
TRANSACTIONDISTRIBUTEDAMOUNT money,
BASEDISTRIBUTEDAMOUNT money,
ORGANIZATIONDISTRIBUTEDAMOUNT money
);
declare @SPLITCOUNT int = 1;
declare @STOCKSALECOUNT int = 0;
--Hold values from all cursors
declare @REVENUESPLITID uniqueidentifier;
declare @STOCKSALEID uniqueidentifier;
declare @TRANSACTIONSPLITAMOUNT money;
--If generating for stock sold or gain/loss GL, we need to calculate expected amounts
if @PAYMENTMETHODCODE in (7, 200, 202)
begin
--Hold values from expected amount cursor
declare @STOCKSALENUMBEROFUNITS decimal(21,4);
--Fill tables to hold accumulations with IDs for relevant splits and stock sales.
insert into @SPLITIDEALAMOUNTLIST(SPLITID)
select ID from REVENUESPLIT where REVENUEID = @TRANSACTIONID;
insert into @STOCKSALEIDEALAMOUNTLIST(STOCKSALEID)
select STOCKSALE.ID
from dbo.STOCKSALE with (nolock)
inner join dbo.STOCKDETAIL on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;
--Expected amount cursor will need IDs, split amounts per split, and the number of units of stock sold per stock sale.
-- Order by ensures that we iterate over all the stock 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 stock sales.
declare EXPECTEDAMOUNT_CURSOR cursor local fast_forward for
select
REVENUESPLIT.ID as REVENUESPLITID,
STOCKSALE.ID as STOCKSALEID,
REVENUESPLIT.TRANSACTIONAMOUNT,
STOCKSALE.NUMBEROFUNITS
,STOCKSALE.TRANSACTIONSALEAMOUNT
,STOCKSALE.SALEAMOUNT
,STOCKSALE.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.STOCKDETAIL on STOCKDETAIL.ID = REVENUEPAYMENTMETHOD.ID
inner join dbo.STOCKSALE on STOCKSALE.STOCKDETAILID = STOCKDETAIL.ID
where
REVENUE.ID = @TRANSACTIONID
order by REVENUESPLIT.ID, STOCKSALE.ID
open EXPECTEDAMOUNT_CURSOR;
fetch next from EXPECTEDAMOUNT_CURSOR into @REVENUESPLITID, @STOCKSALEID, @TRANSACTIONSPLITAMOUNT, @STOCKSALENUMBEROFUNITS, @SALETRANSACTIONAMOUNT, @SALEBASEAMOUNT, @SALEORGAMOUNT;
while @@FETCH_STATUS = 0 begin
set @STOCKSALECOUNT = @STOCKSALECOUNT + 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
@TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT,0),
@TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED = coalesce(TRANSACTIONDISTRIBUTEDAMOUNT,0),
@BASESTOCKSALEAMOUNTDISTRIBUTED = coalesce(BASEDISTRIBUTEDAMOUNT,0),
@ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED = coalesce(ORGANIZATIONDISTRIBUTEDAMOUNT,0)
from @STOCKSALEIDEALAMOUNTLIST
where STOCKSALEID = @STOCKSALEID
--If any stock as been sold, calculate the ideal expected amount in transaction currency
if @TOTALSTOCKCOUNT <> 0
begin
set @TRANSACTIONIDEALAMOUNT = (@TRANSACTIONSPLITAMOUNT * @STOCKSALENUMBEROFUNITS) / @TOTALSTOCKCOUNT;
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),
@SALETRANSACTIONAMOUNT,
@BASECURRENCYID,
@SALEBASEAMOUNT,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@SALEORGAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
end
else --We are on the last split, so roll up and convert by proportion using the total applied to the current stock sale.
begin
set @TRANSACTIONWEIGHTEDAMOUNT = ROUND(@TRANSACTIONIDEALAMOUNT + @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL - @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS);
select
@BASEWEIGHTEDAMOUNT = BASEAMOUNT - @BASESTOCKSALEAMOUNTDISTRIBUTED,
@ORGANIZATIONWEIGHTEDAMOUNT = ORGANIZATIONAMOUNT - @ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
(@TRANSACTIONWEIGHTEDAMOUNT + @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED),
@SALETRANSACTIONAMOUNT,
@BASECURRENCYID,
@SALEBASEAMOUNT,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@SALEORGAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
end
--Populate return table
insert into @SPLITS (
REVENUESPLITID,
TRANSACTIONAMOUNT,
REVENUEID,
STOCKSALEID,
TRANSACTIONSPLITAMOUNT,
AMOUNT,
ORGANIZATIONAMOUNT
)
values(
@REVENUESPLITID,
@TRANSACTIONREVENUEAMOUNT,
@TRANSACTIONID,
@STOCKSALEID,
@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 @STOCKSALEIDEALAMOUNTLIST
set
IDEALAMOUNT = @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL + @TRANSACTIONIDEALAMOUNT,
TRANSACTIONDISTRIBUTEDAMOUNT = @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED + @TRANSACTIONWEIGHTEDAMOUNT,
BASEDISTRIBUTEDAMOUNT = @BASESTOCKSALEAMOUNTDISTRIBUTED + @BASEWEIGHTEDAMOUNT,
ORGANIZATIONDISTRIBUTEDAMOUNT = @ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTEDAMOUNT
where STOCKSALEID = @STOCKSALEID
--If we've gone through all the stock sales for the currency split, so reset the stock sale counter and increment the split counter.
if @STOCKSALECOUNT >= @NUMBEROFSTOCKSALES
begin
set @STOCKSALECOUNT = 0;
set @SPLITCOUNT = @SPLITCOUNT + 1;
end
fetch next from EXPECTEDAMOUNT_CURSOR into @REVENUESPLITID, @STOCKSALEID, @TRANSACTIONSPLITAMOUNT, @STOCKSALENUMBEROFUNITS, @SALETRANSACTIONAMOUNT, @SALEBASEAMOUNT, @SALEORGAMOUNT;
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 (200, 202)
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,
STOCKSALEID uniqueidentifier,
EXPECTEDAMOUNT money
);
insert into @EXPECTEDAMOUNTS
select
REVENUESPLITID,
STOCKSALEID,
TRANSACTIONSPLITAMOUNT
from @SPLITS
delete @SPLITS
--Hold values from gain/loss cursor
declare @TRANSACTIONSTOCKSALESALEAMOUNT money;
declare @BASESTOCKSALESALEAMOUNT money;
declare @ORGANIZATIONSTOCKSALESALEAMOUNT money;
declare @TRANSACTIONSTOCKSALESPLITEXPECTEDAMOUNT money;
--Reset counters.
set @SPLITCOUNT = 0;
set @STOCKSALECOUNT = 1;
--Clear, then fill tables to hold accumulations with IDs for relevant splits and stock sales.
delete @SPLITIDEALAMOUNTLIST
insert into @SPLITIDEALAMOUNTLIST(SPLITID)
select ID from REVENUESPLIT where REVENUEID = @TRANSACTIONID
delete @STOCKSALEIDEALAMOUNTLIST
insert into @STOCKSALEIDEALAMOUNTLIST(STOCKSALEID)
select STOCKSALE.ID
from dbo.STOCKSALE with (nolock)
inner join dbo.STOCKDETAIL on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;
--Gain/loss cursor will need IDs, split amounts per split, and sale amounts per stock sale.
-- Order by ensures that we iterate over all the splits with a given stock sale before going to the next stock sale,
-- which is necessary because we have solid actual amount totals for stock sales, but not splits.
declare GAINLOSS_CURSOR cursor local fast_forward for
select
REVENUESPLIT.ID,
STOCKSALE.ID,
REVENUESPLIT.TRANSACTIONAMOUNT,
STOCKSALE.TRANSACTIONSALEAMOUNT,
STOCKSALE.SALEAMOUNT,
STOCKSALE.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.STOCKDETAIL on STOCKDETAIL.ID = REVENUEPAYMENTMETHOD.ID
inner join dbo.STOCKSALE on STOCKSALE.STOCKDETAILID = STOCKDETAIL.ID
where
REVENUE.ID = @TRANSACTIONID
order by STOCKSALE.ID, REVENUESPLIT.ID
open GAINLOSS_CURSOR;
fetch next from GAINLOSS_CURSOR into @REVENUESPLITID, @STOCKSALEID, @TRANSACTIONSPLITAMOUNT, @TRANSACTIONSTOCKSALESALEAMOUNT, @BASESTOCKSALESALEAMOUNT, @ORGANIZATIONSTOCKSALESALEAMOUNT;
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
@TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT,0),
@TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED = coalesce(TRANSACTIONDISTRIBUTEDAMOUNT,0),
@BASESTOCKSALEAMOUNTDISTRIBUTED = coalesce(BASEDISTRIBUTEDAMOUNT,0),
@ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED = coalesce(ORGANIZATIONDISTRIBUTEDAMOUNT,0)
from @STOCKSALEIDEALAMOUNTLIST
where STOCKSALEID = @STOCKSALEID
--If revenue has an amount, calculate the ideal expected amount in transaction currency
if @TRANSACTIONREVENUEAMOUNT <> 0
begin
select @TRANSACTIONSTOCKSALESPLITEXPECTEDAMOUNT = EXPECTEDAMOUNT
from @EXPECTEDAMOUNTS
where REVENUESPLITID = @REVENUESPLITID and STOCKSALEID = @STOCKSALEID;
set @TRANSACTIONIDEALAMOUNT = abs(@TRANSACTIONSTOCKSALESPLITEXPECTEDAMOUNT - (@TRANSACTIONSTOCKSALESALEAMOUNT * @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 stock sale or not all the stock has been sold, roll up and convert by proportion using the total applied to the current stock sale.
if @STOCKSALECOUNT < @NUMBEROFSTOCKSALES or @ALLSTOCKSOLD = 0
begin
set @TRANSACTIONWEIGHTEDAMOUNT = (ROUND(@TRANSACTIONIDEALAMOUNT + @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL - @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS));
select
@BASEWEIGHTEDAMOUNT = BASEAMOUNT - @BASESTOCKSALEAMOUNTDISTRIBUTED,
@ORGANIZATIONWEIGHTEDAMOUNT = ORGANIZATIONAMOUNT - @ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
(@TRANSACTIONWEIGHTEDAMOUNT + @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED),
@TRANSACTIONSTOCKSALESALEAMOUNT,
@BASECURRENCYID,
@BASESTOCKSALESALEAMOUNT,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@ORGANIZATIONSTOCKSALESALEAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
end
else --We are on the stock sale and have sold all the stock, 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),
@TRANSACTIONSTOCKSALESALEAMOUNT,
@BASECURRENCYID,
@BASESTOCKSALESALEAMOUNT,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@ORGANIZATIONSTOCKSALESALEAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
end
--Populate return table
insert into @SPLITS (
REVENUESPLITID,
TRANSACTIONAMOUNT,
REVENUEID,
STOCKSALEID,
TRANSACTIONSPLITAMOUNT,
AMOUNT,
ORGANIZATIONAMOUNT
)
values(
@REVENUESPLITID,
@TRANSACTIONREVENUEAMOUNT,
@TRANSACTIONID,
@STOCKSALEID,
@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 @STOCKSALEIDEALAMOUNTLIST
set
IDEALAMOUNT = @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL + @TRANSACTIONIDEALAMOUNT,
TRANSACTIONDISTRIBUTEDAMOUNT = @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED + @TRANSACTIONWEIGHTEDAMOUNT,
BASEDISTRIBUTEDAMOUNT = @BASESTOCKSALEAMOUNTDISTRIBUTED + @BASEWEIGHTEDAMOUNT,
ORGANIZATIONDISTRIBUTEDAMOUNT = @ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTEDAMOUNT
where STOCKSALEID = @STOCKSALEID;
end
--If we've gone through all the splits for the currency split, so reset the split counter and increment the stock sale counter.
if @SPLITCOUNT >= @NUMBEROFSPLITS
begin
set @SPLITCOUNT = 0;
set @STOCKSALECOUNT = @STOCKSALECOUNT + 1;
end
fetch next from GAINLOSS_CURSOR into @REVENUESPLITID, @STOCKSALEID, @TRANSACTIONSPLITAMOUNT, @TRANSACTIONSTOCKSALESALEAMOUNT, @BASESTOCKSALESALEAMOUNT, @ORGANIZATIONSTOCKSALESALEAMOUNT;
end
close GAINLOSS_CURSOR;
deallocate GAINLOSS_CURSOR;
end
--select * from @SPLITS
--order by STOCKSALEID, REVENUESPLITID
end
else if @PAYMENTMETHODCODE = 204 --If generating for fees GL, we need to calculate fee amounts.
begin
--Hold values from fee cursor
declare @TRANSACTIONSTOCKSALEFEE money;
declare @BASESTOCKSALEFEE money;
declare @ORGANIZATIONSTOCKSALEFEE money;
--Fill tables to hold accumulations with IDs for relevant stock sales.
insert into @STOCKSALEIDEALAMOUNTLIST(STOCKSALEID)
select STOCKSALE.ID
from dbo.STOCKSALE with (nolock)
inner join dbo.STOCKDETAIL on STOCKDETAIL.ID = STOCKSALE.STOCKDETAILID
inner join dbo.REVENUEPAYMENTMETHOD on REVENUEPAYMENTMETHOD.ID = STOCKDETAIL.ID
where REVENUEPAYMENTMETHOD.REVENUEID = @TRANSACTIONID;
--Fee cursor will need IDs, split amounts per split, and fees per stock sale.
-- Order by ensures that we iterate over all the splits with a given stock sale before going to the next stock sale,
-- which is necessary because we have solid actual fee totals for stock sales, but not splits.
declare FEE_CURSOR cursor local fast_forward for
select
REVENUESPLIT.ID,
STOCKSALE.ID,
REVENUESPLIT.TRANSACTIONAMOUNT,
STOCKSALE.TRANSACTIONFEE,
STOCKSALE.FEE,
STOCKSALE.ORGANIZATIONFEE
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.STOCKDETAIL on STOCKDETAIL.ID = REVENUEPAYMENTMETHOD.ID
inner join dbo.STOCKSALE on STOCKSALE.STOCKDETAILID = STOCKDETAIL.ID
where
REVENUE.ID = @TRANSACTIONID
order by STOCKSALE.ID, REVENUESPLIT.ID
open FEE_CURSOR;
fetch next from FEE_CURSOR into @REVENUESPLITID, @STOCKSALEID, @TRANSACTIONSPLITAMOUNT, @TRANSACTIONSTOCKSALEFEE, @BASESTOCKSALEFEE, @ORGANIZATIONSTOCKSALEFEE;
while @@FETCH_STATUS = 0 begin
set @SPLITCOUNT = @SPLITCOUNT + 1;
--Retrieve accumulated values.
select
@TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT,0),
@TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED = coalesce(TRANSACTIONDISTRIBUTEDAMOUNT,0),
@BASESTOCKSALEAMOUNTDISTRIBUTED = coalesce(BASEDISTRIBUTEDAMOUNT,0),
@ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED = coalesce(ORGANIZATIONDISTRIBUTEDAMOUNT,0)
from @STOCKSALEIDEALAMOUNTLIST
where STOCKSALEID = @STOCKSALEID
--If revenue has an amount, calculate the ideal expected amount in transaction currency
if @TRANSACTIONREVENUEAMOUNT <> 0
begin
set @TRANSACTIONIDEALAMOUNT = (@TRANSACTIONSTOCKSALEFEE * @TRANSACTIONSPLITAMOUNT) / @TRANSACTIONREVENUEAMOUNT;
end
else
begin
set @TRANSACTIONIDEALAMOUNT = 0;
end
set @TRANSACTIONWEIGHTEDAMOUNT = ROUND(@TRANSACTIONIDEALAMOUNT + @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL - @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS);
select
@BASEWEIGHTEDAMOUNT = BASEAMOUNT - @BASESTOCKSALEAMOUNTDISTRIBUTED,
@ORGANIZATIONWEIGHTEDAMOUNT = ORGANIZATIONAMOUNT - @ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED
from dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTION(
(@TRANSACTIONWEIGHTEDAMOUNT + @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED),
@TRANSACTIONSTOCKSALEFEE,
@BASECURRENCYID,
@BASESTOCKSALEFEE,
@BASECURRENCYDECIMALDIGITS,
@TRANSACTIONCURRENCYID,
default,
@ORGANIZATIONCURRENCYID,
@ORGANIZATIONSTOCKSALEFEE,
@ORGANIZATIONCURRENCYDECIMALDIGITS,
default
);
--Populate return table
insert into @SPLITS (
REVENUESPLITID,
TRANSACTIONAMOUNT,
REVENUEID,
STOCKSALEID,
TRANSACTIONSPLITAMOUNT,
AMOUNT,
ORGANIZATIONAMOUNT
)
values(
@REVENUESPLITID,
@TRANSACTIONREVENUEAMOUNT,
@TRANSACTIONID,
@STOCKSALEID,
@TRANSACTIONWEIGHTEDAMOUNT,
@BASEWEIGHTEDAMOUNT,
@ORGANIZATIONWEIGHTEDAMOUNT
);
--Update accumulation tables.
update @STOCKSALEIDEALAMOUNTLIST
set
IDEALAMOUNT = @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTEDIDEAL + @TRANSACTIONIDEALAMOUNT,
TRANSACTIONDISTRIBUTEDAMOUNT = @TRANSACTIONSTOCKSALEAMOUNTDISTRIBUTED + @TRANSACTIONWEIGHTEDAMOUNT,
BASEDISTRIBUTEDAMOUNT = @BASESTOCKSALEAMOUNTDISTRIBUTED + @BASEWEIGHTEDAMOUNT,
ORGANIZATIONDISTRIBUTEDAMOUNT = @ORGANIZATIONSTOCKSALEAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTEDAMOUNT
where STOCKSALEID = @STOCKSALEID;
fetch next from FEE_CURSOR into @REVENUESPLITID, @STOCKSALEID, @TRANSACTIONSPLITAMOUNT, @TRANSACTIONSTOCKSALEFEE, @BASESTOCKSALEFEE, @ORGANIZATIONSTOCKSALEFEE;
end
close FEE_CURSOR;
deallocate FEE_CURSOR;
--select * from @SPLITS
--order by STOCKSALEID, REVENUESPLITID
end
return;
end