USP_MEMBERSHIPPLEDGE_GENERATEINSTALLMENTSPLITS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PLEDGEID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN |
Definition
Copy
CREATE procedure dbo.USP_MEMBERSHIPPLEDGE_GENERATEINSTALLMENTSPLITS
(
@PLEDGEID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CHANGEDATE datetime
)
as
begin
set nocount on;
declare @PLEDGEAMOUNT decimal(30, 5);
declare @INSTALLMENTAMOUNT decimal(30, 5);
declare @INSTALLMENTID uniqueidentifier;
declare @DESIGNATIONID uniqueidentifier;
declare @DESIGNATIONSUM decimal(30, 5);
declare @REVENUESPLITID uniqueidentifier;
declare @EARNEDINCOMEAMOUNT decimal(30, 5);
declare @CONTRIBUTEDINCOMEAMOUNT decimal(30, 5);
declare @WEIGHTAMOUNT decimal(30, 5);
declare @IDEALAMOUNT decimal(30, 5);
declare @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL decimal(30, 5);
declare @INSTALLMENTAMOUNTDISTRIBUTED decimal(30, 5);
declare @INSTALLMENTBASEAMOUNTDISTRIBUTED decimal(30, 5);
declare @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED decimal(30, 5);
declare @INSTALLMENTMAX integer;
declare @INSTALLMENTSEQUENCE integer;
set @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL = 0;
set @INSTALLMENTAMOUNTDISTRIBUTED = 0;
set @INSTALLMENTBASEAMOUNTDISTRIBUTED = 0;
set @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED = 0;
--Multicurrency - AdamBu 3/30/10 - Retrieve the info needed to convert values later.
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @BASECURRENCYID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
declare @DESIGNATIONSUMBASEAMOUNT money;
declare @INSTALLMENTBASEAMOUNT money;
declare @DESIGNATIONSUMORGANIZATIONAMOUNT money;
declare @INSTALLMENTORGANIZATIONAMOUNT money;
declare @BASEWEIGHTAMOUNT money;
declare @ORGANIZATIONWEIGHTAMOUNT money;
declare @TRANSACTIONCURRENCYDECIMALDIGITS tinyint;
declare @BASECURRENCYDECIMALDIGITS tinyint;
declare @ORGANIZATIONCURRENCYDECIMALDIGITS tinyint;
select
@BASECURRENCYID = CURRENCYSET.BASECURRENCYID,
@ORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID,
@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
@PLEDGEAMOUNT = FINANCIALTRANSACTION.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYDECIMALDIGITS = TRANSACTIONCURRENCY.DECIMALDIGITS,
@BASECURRENCYDECIMALDIGITS = BASECURRENCY.DECIMALDIGITS
from
dbo.FINANCIALTRANSACTION
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
inner join dbo.CURRENCY as TRANSACTIONCURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = TRANSACTIONCURRENCY.ID
inner join dbo.CURRENCY as BASECURRENCY on CURRENCYSET.BASECURRENCYID = BASECURRENCY.ID
where
FINANCIALTRANSACTION.ID = @PLEDGEID
select @ORGANIZATIONCURRENCYDECIMALDIGITS = DECIMALDIGITS from dbo.CURRENCY where ID = @ORGANIZATIONCURRENCYID;
declare @ORGANIZATIONAMOUNTORIGINCODE tinyint = 0;
select @ORGANIZATIONAMOUNTORIGINCODE = coalesce(ORGANIZATIONAMOUNTORIGINCODE,0) from dbo.MULTICURRENCYCONFIGURATION;
declare @INSTALLMENTIDEALAMOUNTLIST table
(
INSTALLMENTID uniqueidentifier,
IDEALAMOUNT money,
DISTRIBUTEDAMOUNT money,
DISTRIBUTEDBASEAMOUNT money,
DISTRIBUTEDORGANIZATIONAMOUNT money
)
insert into @INSTALLMENTIDEALAMOUNTLIST(INSTALLMENTID)
select ID from INSTALLMENT where REVENUEID = @PLEDGEID;
select @INSTALLMENTMAX = max(SEQUENCE)
from INSTALLMENT where INSTALLMENT.REVENUEID = @PLEDGEID;
select @EARNEDINCOMEAMOUNT = SUM(TRANSACTIONAMOUNT)
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT.ID
where REVENUESPLIT.TYPECODE in (2,18) and REVENUESPLIT.APPLICATIONCODE in (5,18)
and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
select @CONTRIBUTEDINCOMEAMOUNT = SUM(TRANSACTIONAMOUNT)
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.REVENUESPLIT_EXT REVENUESPLIT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT.ID
where REVENUESPLIT.DESIGNATIONID is not null
and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
declare @SPLITS table(ID uniqueidentifier, AMOUNTREMAINING money)
insert into @SPLITS(ID, AMOUNTREMAINING)
select FINANCIALTRANSACTIONLINEITEM.ID, FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @PLEDGEID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
declare @INSTALLMENTISEARNEDINCOME bit;
declare @INSTALLMENTSPLITREMAININGAMOUNT money;
declare @MAXINSTALLMENTSEQUENCE int = (select max(INSTALLMENT.SEQUENCE) from dbo.INSTALLMENT where INSTALLMENT.REVENUEID = @PLEDGEID)
declare INSTALLMENTSPLITS cursor local fast_forward for
select
INSTALLMENT.ID,
INSTALLMENT.SEQUENCE,
REVENUESPLIT_EXT.DESIGNATIONID,
INSTALLMENT.TRANSACTIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT TRANSACTIONAMOUNT,
INSTALLMENT.AMOUNT,
FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT AMOUNT,
INSTALLMENT.ORGANIZATIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT ORGANIZATIONAMOUNT,
FINANCIALTRANSACTIONLINEITEM.ID REVENUESPLITID,
case when REVENUESPLIT_EXT.TYPECODE in (2,18) and REVENUESPLIT_EXT.APPLICATIONCODE in (5,18) then 1 else 0 end ISEARNEDINCOME
from
dbo.INSTALLMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT_EXT.ID = FINANCIALTRANSACTIONLINEITEM.ID
where
INSTALLMENT.REVENUEID = @PLEDGEID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
order by
INSTALLMENT.SEQUENCE, ISEARNEDINCOME desc, FINANCIALTRANSACTIONLINEITEM.SEQUENCE, FINANCIALTRANSACTIONLINEITEM.TS
open INSTALLMENTSPLITS;
fetch next from INSTALLMENTSPLITS into @INSTALLMENTID, @INSTALLMENTSEQUENCE, @DESIGNATIONID, @INSTALLMENTAMOUNT, @DESIGNATIONSUM, @INSTALLMENTBASEAMOUNT, @DESIGNATIONSUMBASEAMOUNT, @INSTALLMENTORGANIZATIONAMOUNT, @DESIGNATIONSUMORGANIZATIONAMOUNT, @REVENUESPLITID, @INSTALLMENTISEARNEDINCOME;
while @@FETCH_STATUS = 0
begin
select @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL = coalesce(IDEALAMOUNT, 0.00),
@INSTALLMENTAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDAMOUNT, 0.00),
@INSTALLMENTBASEAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDBASEAMOUNT, 0.00),
@INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED = coalesce(DISTRIBUTEDORGANIZATIONAMOUNT, 0.00)
from @INSTALLMENTIDEALAMOUNTLIST
where INSTALLMENTID = @INSTALLMENTID;
select @INSTALLMENTSPLITREMAININGAMOUNT = AMOUNTREMAINING
from @SPLITS
where ID = @REVENUESPLITID
if @INSTALLMENTAMOUNTDISTRIBUTED < @INSTALLMENTAMOUNT and @INSTALLMENTSPLITREMAININGAMOUNT > 0
begin
if @PLEDGEAMOUNT <> 0
--Do multiplication first to reduce rounding errors
set @IDEALAMOUNT = (@INSTALLMENTAMOUNT * @DESIGNATIONSUM)/(select case when @INSTALLMENTISEARNEDINCOME = 1 then @EARNEDINCOMEAMOUNT else @CONTRIBUTEDINCOMEAMOUNT end);
else
set @IDEALAMOUNT = 0;
if @INSTALLMENTSEQUENCE = @MAXINSTALLMENTSEQUENCE or @IDEALAMOUNT > @INSTALLMENTSPLITREMAININGAMOUNT
set @IDEALAMOUNT = @INSTALLMENTSPLITREMAININGAMOUNT
else if @IDEALAMOUNT > @INSTALLMENTAMOUNT - @INSTALLMENTAMOUNTDISTRIBUTED
set @IDEALAMOUNT = @INSTALLMENTAMOUNT - @INSTALLMENTAMOUNTDISTRIBUTED
set @WEIGHTAMOUNT = ROUND(@IDEALAMOUNT
+ @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL - @INSTALLMENTAMOUNTDISTRIBUTED, @TRANSACTIONCURRENCYDECIMALDIGITS);
--Calculate the weighted base amount
if @TRANSACTIONCURRENCYID = @BASECURRENCYID
set @BASEWEIGHTAMOUNT = @WEIGHTAMOUNT;
else
set @BASEWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@WEIGHTAMOUNT + @INSTALLMENTAMOUNTDISTRIBUTED), @INSTALLMENTAMOUNT, @INSTALLMENTBASEAMOUNT, @BASECURRENCYDECIMALDIGITS) - @INSTALLMENTBASEAMOUNTDISTRIBUTED);
--Calculate the weighted organization amount
if @ORGANIZATIONAMOUNTORIGINCODE = 0
begin
if @BASECURRENCYID = @ORGANIZATIONCURRENCYID
set @ORGANIZATIONWEIGHTAMOUNT = @BASEWEIGHTAMOUNT;
else
set @ORGANIZATIONWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@BASEWEIGHTAMOUNT + @INSTALLMENTBASEAMOUNTDISTRIBUTED), @INSTALLMENTBASEAMOUNT, @INSTALLMENTORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) - @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED);
end
else
begin
if @TRANSACTIONCURRENCYID = @ORGANIZATIONCURRENCYID
set @ORGANIZATIONWEIGHTAMOUNT = @WEIGHTAMOUNT;
else
set @ORGANIZATIONWEIGHTAMOUNT = (dbo.UFN_CURRENCY_CONVERTBYPROPORTION((@WEIGHTAMOUNT + @INSTALLMENTAMOUNTDISTRIBUTED), @INSTALLMENTAMOUNT, @INSTALLMENTORGANIZATIONAMOUNT, @ORGANIZATIONCURRENCYDECIMALDIGITS) - @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED);
end
insert into dbo.INSTALLMENTSPLIT(
INSTALLMENTID,
PLEDGEID,
DESIGNATIONID,
AMOUNT,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
REVENUESPLITID)
values(
@INSTALLMENTID,
@PLEDGEID,
@DESIGNATIONID,
@BASEWEIGHTAMOUNT,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE,
@BASECURRENCYID,
@ORGANIZATIONWEIGHTAMOUNT,
@ORGANIZATIONEXCHANGERATEID,
@WEIGHTAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
@REVENUESPLITID)
update @INSTALLMENTIDEALAMOUNTLIST
set IDEALAMOUNT = @INSTALLMENTAMOUNTDISTRIBUTEDIDEAL + @IDEALAMOUNT,
DISTRIBUTEDAMOUNT = @INSTALLMENTAMOUNTDISTRIBUTED + @WEIGHTAMOUNT,
DISTRIBUTEDBASEAMOUNT = @INSTALLMENTBASEAMOUNTDISTRIBUTED + @BASEWEIGHTAMOUNT,
DISTRIBUTEDORGANIZATIONAMOUNT = @INSTALLMENTORGANIZATIONAMOUNTDISTRIBUTED + @ORGANIZATIONWEIGHTAMOUNT
where INSTALLMENTID = @INSTALLMENTID;
update @SPLITS
set AMOUNTREMAINING = AMOUNTREMAINING - @WEIGHTAMOUNT
where ID = @REVENUESPLITID
end
fetch next from INSTALLMENTSPLITS into @INSTALLMENTID, @INSTALLMENTSEQUENCE, @DESIGNATIONID, @INSTALLMENTAMOUNT, @DESIGNATIONSUM, @INSTALLMENTBASEAMOUNT, @DESIGNATIONSUMBASEAMOUNT, @INSTALLMENTORGANIZATIONAMOUNT, @DESIGNATIONSUMORGANIZATIONAMOUNT, @REVENUESPLITID, @INSTALLMENTISEARNEDINCOME;
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close INSTALLMENTSPLITS;
deallocate INSTALLMENTSPLITS;
end