USP_PLEDGE_PAYINSTALLMENTS
Stored proc to apply a payment to a pledge
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @PLEDGEID | uniqueidentifier | IN | |
| @REVENUEID | uniqueidentifier | IN | |
| @AMOUNT | money | INOUT | |
| @CAID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @CREATEDSPLITS | xml | INOUT | |
| @OVERPAYMENTAPPLICATIONTYPECODE | tinyint | IN | |
| @BASEAMOUNT | money | IN | |
| @ORGANIZATIONAMOUNT | money | IN | |
| @APPLICATIONSPLITS | xml | IN |
Definition
Copy
CREATE procedure [dbo].[USP_PLEDGE_PAYINSTALLMENTS]
(
@PLEDGEID uniqueidentifier,
@REVENUEID uniqueidentifier,
@AMOUNT money output, -- the transaction amount of the revenue stream
@CAID uniqueidentifier,
@CHANGEDATE datetime,
@CREATEDSPLITS xml = null output,
@OVERPAYMENTAPPLICATIONTYPECODE tinyint = null,
@BASEAMOUNT money = null,
@ORGANIZATIONAMOUNT money = null,
@APPLICATIONSPLITS xml = null
)
as
set nocount on;
declare @installmentPayAmount money;
declare @amountPaid money;
declare @amountLeft money;
declare @installmentBalance money;
declare @PLEDGETYPECODE tinyint;
declare @APPLICATIONCODE tinyint;
set @amountPaid = 0;
set @amountLeft = @AMOUNT;
declare @splits table
(
INSTALLMENTSPLITID uniqueidentifier,
AMOUNTAPPLICATION money,
DESIGNATIONID uniqueidentifier,
PLEDGETYPECODE tinyint,
PLEDGEREVENUESPLITID uniqueidentifier
);
declare @splitsXml xml;
declare @paymentSplitsXml xml;
declare @applicationSplitsTable table
(
ID uniqueidentifier,
AMOUNT money
);
declare @amountApplication money;
declare @installmentPayAmountApplication money;
declare @amountPaidApplication money;
declare @amountLeftApplication money;
declare @PAYMENTBASECURRENCYID uniqueidentifier;
declare @PAYMENTTRANSACTIONCURRENCYID uniqueidentifier;
declare @PAYMENTBASEEXCHANGERATEID uniqueidentifier;
declare @PAYMENTORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @PAYMENTRANSACTIONCURRENCYDECIMALDIGITS tinyint;
declare @PAYMENBASECURRENCYDECIMALDIGITS tinyint;
declare @REVENUEDATE datetime;
declare @APPLICATIONCURRENCYID uniqueidentifier;
declare @APPLICATIONEXCHANGERATEID uniqueidentifier;
declare @APPLICATIONCURRENCYDECIMALDIGITS tinyint;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
declare @ORGANIZATIONCURRENCYDECIMALDIGITS tinyint;
select
@REVENUEDATE = FINANCIALTRANSACTION.DATE,
@PAYMENTBASECURRENCYID = CURRENCYSET.BASECURRENCYID,
@PAYMENTTRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
@PAYMENTBASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID,
@PAYMENTORGANIZATIONEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID,
@PAYMENTRANSACTIONCURRENCYDECIMALDIGITS = TRANSACTIONCURRENCY.DECIMALDIGITS,
@PAYMENBASECURRENCYDECIMALDIGITS = BASECURRENCY.DECIMALDIGITS
from
dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
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 = @REVENUEID
and FINANCIALTRANSACTION.DELETEDON is null;
select
@ORGANIZATIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS
from
dbo.CURRENCY
where
CURRENCY.ID = @ORGANIZATIONCURRENCYID;
select
@APPLICATIONCURRENCYID = TRANSACTIONCURRENCYID,
@APPLICATIONCURRENCYDECIMALDIGITS = CURRENCY.DECIMALDIGITS,
@APPLICATIONCODE = case FINANCIALTRANSACTION.TYPECODE
when 3 then 7 -- Matching Gift Pledge
when 4 then 6 -- Planned Gift
when 6 then 8 -- Grant award
when 8 then 13 -- Donor Challenge
when 9 then 17 -- Pending Gift
when 15 then 19 -- Membership installment plan
else 2 -- Pledge
end
from
dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
inner join dbo.CURRENCY on FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = CURRENCY.ID
where
FINANCIALTRANSACTION.ID = @PLEDGEID
and FINANCIALTRANSACTION.DELETEDON is null;
if @APPLICATIONCURRENCYID <> @PAYMENTTRANSACTIONCURRENCYID
begin
if @APPLICATIONCURRENCYID = @PAYMENTBASECURRENCYID
set @APPLICATIONEXCHANGERATEID = @PAYMENTBASEEXCHANGERATEID;
else
set @APPLICATIONEXCHANGERATEID = dbo.UFN_CURRENCYEXCHANGERATE_GETLATEST(@PAYMENTTRANSACTIONCURRENCYID,@APPLICATIONCURRENCYID,@REVENUEDATE,1,null);
if @APPLICATIONEXCHANGERATEID is null
begin
raiserror('BBERR_APPLICATIONEXCHANGERATEDOESNOTEXIST : You cannot apply the payment toward this application. No exchange rate exists between the payment and application currencies.', 13, 1);
return 1;
end
end
-- Convert the applied amount into base and organization amounts if it is not provided by the caller
if @BASEAMOUNT is null or @ORGANIZATIONAMOUNT is null
exec dbo.USP_CURRENCY_GETCURRENCYVALUES @AMOUNT, @REVENUEDATE, @PAYMENTBASECURRENCYID, @PAYMENTBASEEXCHANGERATEID, @PAYMENTTRANSACTIONCURRENCYID, @BASEAMOUNT output, null, @ORGANIZATIONAMOUNT output, @PAYMENTORGANIZATIONEXCHANGERATEID, 0, null;
if @APPLICATIONCURRENCYID = @PAYMENTTRANSACTIONCURRENCYID
set @amountApplication = @AMOUNT;
else
set @amountApplication = dbo.UFN_CURRENCY_CONVERT(@AMOUNT, @APPLICATIONEXCHANGERATEID);
set @amountPaidApplication = 0;
set @amountLeftApplication = @amountApplication;
declare @CREATEDSPLIT table
(
CREATEDREVENUESPLITID uniqueidentifier,
SOURCEREVENUESPLITID uniqueidentifier
);
if @OVERPAYMENTAPPLICATIONTYPECODE is null
set @OVERPAYMENTAPPLICATIONTYPECODE = coalesce(dbo.UFN_PLEDGE_GETDEFAULTOVERPAYMENTAPPLICATIONCODE(), 255);
if not @OVERPAYMENTAPPLICATIONTYPECODE in (0, 1, 255)
set @OVERPAYMENTAPPLICATIONTYPECODE = 1;
--Overpayment option to pay pledge balance, so pay first installment as usual, then pay from last installment forward
if @OVERPAYMENTAPPLICATIONTYPECODE = 0
begin
--Find the minimum valid sequence number to be paid
declare @MINSEQUENCE integer;
select @MINSEQUENCE = min(INSTALLMENT.SEQUENCE)
from dbo.INSTALLMENTSPLIT
inner join dbo.INSTALLMENT
on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
where
(
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID) > 0 or
(
-- WI 191314: $0 pledges can have $0 installments that need to be included
-- so the link between the pledge and payment isn't broken
INSTALLMENT.AMOUNT = 0 and
@AMOUNT = 0
)
)
and INSTALLMENT.REVENUEID = @PLEDGEID;
--Find a sequence number above valid installments for this pledge
declare @PAYFIRSTSEQUENCE integer;
select @PAYFIRSTSEQUENCE = (MAX(SEQUENCE) + 1) from [dbo].[UFN_PLEDGE_GETINSTALLMENTS](@PLEDGEID)
declare INSTALLMENTCURSOR cursor local fast_forward for
select
INSTALLMENT.ID,
dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) [BALANCE]
from
dbo.INSTALLMENT
where
(
dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) > 0 or
(
-- WI 191314: $0 pledges can have $0 installments that need to be included
-- so the link between the pledge and payment isn't broken
INSTALLMENT.AMOUNT = 0 and
@AMOUNT = 0
)
)
and INSTALLMENT.REVENUEID = @PLEDGEID
order by
case
--mark the first installment to pay with the highest sequence
when SEQUENCE = @MINSEQUENCE then @PAYFIRSTSEQUENCE
else SEQUENCE
end desc;
end
--No overpayment option or overpay to next installments, so pay installments in order
else
begin
declare INSTALLMENTCURSOR cursor local fast_forward for
select
INSTALLMENT.ID,
dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(INSTALLMENT.ID) [BALANCE]
from
dbo.INSTALLMENT
where
(
dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE(ID) > 0 or
(
-- WI 191314: $0 pledges can have $0 installments that need to be included
-- so the link between the pledge and payment isn't broken
INSTALLMENT.AMOUNT = 0 and
@AMOUNT = 0
)
)
and INSTALLMENT.REVENUEID = @PLEDGEID
order by
SEQUENCE asc;
end
declare @INSTALLMENTCOUNTER int = 0;
declare @INSTALLMENTID uniqueidentifier;
open INSTALLMENTCURSOR;
fetch next from INSTALLMENTCURSOR into @INSTALLMENTID, @installmentBalance;
while @@FETCH_STATUS = 0
begin
-- WI 191314: Changed break condition to support $0 pledges. For those pledges,
-- @amountPaidApplication always equals @amountApplication so need to check that
-- @amountPaidApplication is greater than 0. To avoid applying the same $0 payment
-- to multiple installments, only allow this condition change to take affect on the
-- first loop.
if @amountPaidApplication = @amountApplication and (@amountPaidApplication > 0 or @INSTALLMENTCOUNTER > 0)
break;
-- determine payment amount
if @amountLeftApplication < @installmentBalance
begin
set @installmentPayAmountApplication = @amountLeftApplication;
if (@APPLICATIONSPLITS is not null and @APPLICATIONSPLITS.exist('/APPLICATIONSPLITS/ITEM') = 1)
and (@APPLICATIONCODE = 7) --Matching gift claim
and (@PAYMENTTRANSACTIONCURRENCYID = @APPLICATIONCURRENCYID)
begin
--This should only be executed for the last installment, but clear this
-- table just in case.
delete from @applicationSplitsTable;
if @APPLICATIONCURRENCYID <> @PAYMENTTRANSACTIONCURRENCYID
begin
--Convert application splits amounts from payment transaction currency to application currency.
declare @DECIMALDIGITSAPPLICATIONCURRENCY tinyint;
select @DECIMALDIGITSAPPLICATIONCURRENCY = DECIMALDIGITS from dbo.CURRENCY where ID = @APPLICATIONCURRENCYID;
with CTE_ITEMLIST as ( --Generate row numbers and amounts for each item in the given list.
select
ITEMNUMBER.ROW,
ITEMLIST.ITEM.value('(ITEM[position() = sql:column("ITEMNUMBER.ROW")]/ID)[1]','uniqueidentifier') ID,
ITEMLIST.ITEM.value('(ITEM[position() = sql:column("ITEMNUMBER.ROW")]/AMOUNT)[1]','money') AMOUNT
from @APPLICATIONSPLITS.nodes('/APPLICATIONSPLITS') ITEMLIST(ITEM)
cross join (
select
row_number() over (order by ITEMLIST.ITEM) ROW
from
@APPLICATIONSPLITS.nodes('/APPLICATIONSPLITS/ITEM') ITEMLIST(ITEM)
) ITEMNUMBER
),
CTE_ITEMAPPLIEDRUNNINGTOTAL as ( --Calculate the running total for the applied amount.
select
ROW,
ID,
dbo.UFN_CURRENCY_CONVERTBYPROPORTION(
(
select sum(ALLPREVIOUSITEMAMOUNT.AMOUNT)
from CTE_ITEMLIST ALLPREVIOUSITEMAMOUNT
where ALLPREVIOUSITEMAMOUNT.ROW <= ITEMLIST.ROW
),
@AMOUNT,
@amountApplication,
@DECIMALDIGITSAPPLICATIONCURRENCY
) APPLIEDAMOUNTORRUNNINGTOTAL
from CTE_ITEMLIST as ITEMLIST
)
insert into @applicationSplitsTable
(
ID,
AMOUNT
)
select
ID,
ITEMAPPLIEDRUNNINGTOTAL.APPLIEDAMOUNTORRUNNINGTOTAL
- coalesce(
(
select PREVIOUSCONVERTEDRUNNINGTOTAL.APPLIEDAMOUNTORRUNNINGTOTAL
from CTE_ITEMAPPLIEDRUNNINGTOTAL as PREVIOUSCONVERTEDRUNNINGTOTAL
where PREVIOUSCONVERTEDRUNNINGTOTAL.ROW = ITEMAPPLIEDRUNNINGTOTAL.ROW - 1
)
,0
) APPLIEDAMOUNT
from CTE_ITEMAPPLIEDRUNNINGTOTAL as ITEMAPPLIEDRUNNINGTOTAL;
end
else
begin
insert into @applicationSplitsTable
(
ID,
AMOUNT
)
select
APPLICATIONSPLITS.ITEM.value('ID[1]', 'uniqueidentifier'),
APPLICATIONSPLITS.ITEM.value('AMOUNT[1]', 'money')
from
@APPLICATIONSPLITS.nodes('/APPLICATIONSPLITS/ITEM') as APPLICATIONSPLITS(ITEM);
end
-- use the splits that were passed in (e.g. paying a specific matching gift claim split)
set @splitsXml =
(
select
INSTALLMENTSPLIT.ID,
case
when APPLICATIONSPLITS.AMOUNT < dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID)
then APPLICATIONSPLITS.AMOUNT
else
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID)
end as AMOUNT
from
dbo.INSTALLMENTSPLIT
inner join @applicationSplitsTable as APPLICATIONSPLITS on INSTALLMENTSPLIT.REVENUESPLITID = APPLICATIONSPLITS.ID
where
INSTALLMENTSPLIT.INSTALLMENTID = @INSTALLMENTID
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),BINARY BASE64
);
--We will prorate below based on these splits and the @installmentBalance;
-- since the splits may be lower than the full potential allowed by the installment
-- we want to update @installmentBalance here.
set @installmentBalance = (select sum(SPLITS.ITEM.value('AMOUNT[1]', 'money')) from @splitsXml.nodes('/AMOUNTSTOPRORATE/ITEM') as SPLITS(ITEM));
--The amounts are prorated below, but these values represent the maximum that should be applied
-- if the amount remaining to be applied would exceed this sum (which itself is up to but not
-- exceeding the true installment balance), we should not allow the application with this value.
-- @APPLICATIONSPLITS should only be used when there is a single installment (e.g. with a matching gift claim).
if @installmentBalance < @installmentPayAmountApplication
begin
raiserror('BBERR_APPLICATIONSPLITSUNDERPAIDFINALINSTALLMENT : The amount applied to a designation exceeded the balance for that portion of the commitment.', 13, 1);
return 1;
end
end
else
-- otherwise, prorate among designations
set @splitsXml =
(
select
INSTALLMENTSPLIT.ID,
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID) as AMOUNT
from
dbo.INSTALLMENTSPLIT
where
INSTALLMENTSPLIT.INSTALLMENTID = @INSTALLMENTID
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),BINARY BASE64
);
--TODO: We could probably do away with these CTEs if we want to say this function should fail when there
-- are multiple pledge splits with the same designation. This would affect unit tests and my affect customizations.
with
INSTALLMENTSPLITFORINSTALLMENT as
(
select
INSTALLMENTSPLIT.ID,
INSTALLMENTSPLIT.TRANSACTIONAMOUNT,
INSTALLMENTSPLIT.DESIGNATIONID,
INSTALLMENTSPLIT.REVENUESPLITID
from
dbo.INSTALLMENTSPLIT
inner join
dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
where
INSTALLMENT.ID = @INSTALLMENTID
),
REVENUESPLITFORINSTALLMENT as
(
select
REVENUESPLIT_EXT.DESIGNATIONID,
REVENUESPLIT_EXT.TYPECODE,
FINANCIALTRANSACTIONLINEITEM.ID
from
dbo.INSTALLMENT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
inner join
dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where
INSTALLMENT.ID = @INSTALLMENTID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
)
insert into @splits (INSTALLMENTSPLITID, AMOUNTAPPLICATION, DESIGNATIONID, PLEDGETYPECODE, PLEDGEREVENUESPLITID)
select
INSTALLMENTSPLITFORINSTALLMENT.ID,
PRORATEDAMOUNT.AMOUNT,
INSTALLMENTSPLITFORINSTALLMENT.DESIGNATIONID,
REVENUESPLITFORINSTALLMENT.TYPECODE,
REVENUESPLITFORINSTALLMENT.ID
from
INSTALLMENTSPLITFORINSTALLMENT
inner join
REVENUESPLITFORINSTALLMENT on
INSTALLMENTSPLITFORINSTALLMENT.REVENUESPLITID = REVENUESPLITFORINSTALLMENT.ID
inner join
dbo.UFN_SPLITS_PRORATEAMOUNTS(@installmentBalance, @installmentPayAmountApplication, @APPLICATIONCURRENCYDECIMALDIGITS, @splitsXml) PRORATEDAMOUNT on PRORATEDAMOUNT.ID = INSTALLMENTSPLITFORINSTALLMENT.ID
where
--Don't include $0 payment splits unless the pledge split had a $0 amount.
-- This avoids problem with extra payment splits when paying a matching gift claim that
-- has had certain splits paid off by other payments. It also could come up where
-- a very small payment is paid and some of the splits have $0.01 applied and others
-- have a $0.00 applied.
-- However, do allow $0 application to pledges only. This information is used by clients
-- to track pledge payment history.
PRORATEDAMOUNT.AMOUNT > 0
or
(
(
INSTALLMENTSPLITFORINSTALLMENT.TRANSACTIONAMOUNT = 0
and @AMOUNT = 0
)
or
(
PRORATEDAMOUNT.AMOUNT = 0
and @APPLICATIONCODE = 2
)
);
end
else
begin
set @installmentPayAmountApplication = @installmentBalance;
-- use the full remaining balance for each split because we are paying the whole installment balance
--TODO: We could probably do away with these CTEs if we want to say this function should fail when there
-- are multiple pledge splits with the same designation. This would affect unit tests and my affect customizations.
with
INSTALLMENTSPLITFORINSTALLMENT as
(
select
INSTALLMENTSPLIT.ID,
INSTALLMENTSPLIT.TRANSACTIONAMOUNT,
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE(INSTALLMENTSPLIT.ID) as BALANCE,
INSTALLMENTSPLIT.DESIGNATIONID,
INSTALLMENTSPLIT.REVENUESPLITID
from
dbo.INSTALLMENTSPLIT
inner join
dbo.INSTALLMENT on INSTALLMENTSPLIT.INSTALLMENTID = INSTALLMENT.ID
where
INSTALLMENT.ID = @INSTALLMENTID
),
REVENUESPLITFORINSTALLMENT as
(
select
REVENUESPLIT_EXT.DESIGNATIONID,
REVENUESPLIT_EXT.TYPECODE,
FINANCIALTRANSACTIONLINEITEM.ID
from
dbo.INSTALLMENT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = INSTALLMENT.REVENUEID
inner join
dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where
INSTALLMENT.ID = @INSTALLMENTID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
)
insert into @splits (INSTALLMENTSPLITID, AMOUNTAPPLICATION, DESIGNATIONID, PLEDGETYPECODE, PLEDGEREVENUESPLITID)
select
INSTALLMENTSPLITFORINSTALLMENT.ID,
INSTALLMENTSPLITFORINSTALLMENT.BALANCE,
INSTALLMENTSPLITFORINSTALLMENT.DESIGNATIONID,
REVENUESPLITFORINSTALLMENT.TYPECODE,
REVENUESPLITFORINSTALLMENT.ID
from
INSTALLMENTSPLITFORINSTALLMENT
inner join
REVENUESPLITFORINSTALLMENT on
INSTALLMENTSPLITFORINSTALLMENT.REVENUESPLITID = REVENUESPLITFORINSTALLMENT.ID
where
--Don't include $0 payment splits unless the pledge split had a $0 amount.
-- This avoids problem with extra payment splits when paying a matching gift claim that
-- has had certain splits paid off by other payments. It also could come up where
-- nearly all of a pledge is paid and some of the splits have a $0.01 balance and others
-- have a $0.00 balance.
INSTALLMENTSPLITFORINSTALLMENT.BALANCE > 0
or
(
INSTALLMENTSPLITFORINSTALLMENT.TRANSACTIONAMOUNT = 0
and @AMOUNT = 0
);
end
set @amountPaidApplication = @amountPaidApplication + @installmentPayAmountApplication;
set @amountLeftApplication = @amountLeftApplication - @installmentPayAmountApplication;
set @INSTALLMENTCOUNTER = @INSTALLMENTCOUNTER + 1;
fetch next from INSTALLMENTCURSOR into @INSTALLMENTID, @installmentBalance;
end -- fetch while
close INSTALLMENTCURSOR;
deallocate INSTALLMENTCURSOR;
set @amountPaid = dbo.UFN_CURRENCY_CONVERTBYPROPORTION(@amountPaidApplication, @amountApplication, @AMOUNT, @PAYMENTRANSACTIONCURRENCYDECIMALDIGITS);
-- Determine payment split amounts in the transaction currency of the pledge
set @paymentSplitsXml =
(
select
ID,
AMOUNT
from
(
select
PLEDGEREVENUESPLITID as ID,
sum(AMOUNTAPPLICATION) as AMOUNT
from
@splits
group by
PLEDGEREVENUESPLITID
union all
-- if any amount is unapplied, include that too so the proportions work out, filter this row out of the results below
select
null as ID,
@amountApplication - (select sum(AMOUNTAPPLICATION) from @splits) as AMOUNT
where
@amountPaidApplication <> @amountApplication
) T
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),BINARY BASE64
);
-- Convert payment split amounts to the transaction currency of the payment
set @paymentSplitsXml =
(
select
case when V1.ID is not null and V1.TYPECODE = 99 then V1.ID else newid() end as PAYMENTREVENUESPLITID,
--newid() as PAYMENTREVENUESPLITID,
PRORATEDAMOUNT.ID as PLEDGEREVENUESPLITID,
REVENUESPLIT_EXT.DESIGNATIONID,
PRORATEDAMOUNT.AMOUNT,
REVENUESPLIT_EXT.TYPECODE as PLEDGETYPECODE
from
dbo.UFN_SPLITS_PRORATEAMOUNTS(@amountApplication, @AMOUNT, @PAYMENTRANSACTIONCURRENCYDECIMALDIGITS, @paymentSplitsXml) as PRORATEDAMOUNT
left join dbo.FINANCIALTRANSACTIONLINEITEM on PRORATEDAMOUNT.ID = FINANCIALTRANSACTIONLINEITEM.ID
left join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
left join
(select FINANCIALTRANSACTIONLINEITEM.ID, REVENUESPLIT_EXT.DESIGNATIONID, FINANCIALTRANSACTIONLINEITEM.TYPECODE
from dbo.FINANCIALTRANSACTIONLINEITEM inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE != 1) V1 on REVENUESPLIT_EXT.DESIGNATIONID = V1.DESIGNATIONID and V1.TYPECODE = 99
where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
for xml raw('ITEM'),type,elements,root('ITEMLIST'),BINARY BASE64
);
declare @ADJUSTMENTPOSTSTATUSCODE tinyint;
declare @ADJUSTMENTPOSTDATE date;
declare @ADJUSTMENTID uniqueidentifier;
select top 1
@ADJUSTMENTPOSTSTATUSCODE = case A.POSTSTATUSCODE when 2 then 3 else 1 end
,@ADJUSTMENTPOSTDATE = A.POSTDATE
,@ADJUSTMENTID = ALI.ID
from dbo.ADJUSTMENT A
left join dbo.FINANCIALTRANSACTIONLINEITEMADJUSTMENT ALI on A.ID = ALI.ID
where A.REVENUEID = @REVENUEID
order by A.DATEADDED desc;
declare @PAYMENTREVENUESPLITAPPLIEDCONVERTED table
(
PAYMENTREVENUESPLITID uniqueidentifier,
BASEAMOUNT money,
TRANSACTIONAMOUNT money,
ORGANIZATIONAMOUNT money,
DESIGNATIONID uniqueidentifier,
PLEDGETYPECODE tinyint,
PLEDGEREVENUESPLITID uniqueidentifier
);
insert into
@PAYMENTREVENUESPLITAPPLIEDCONVERTED
select
PAYMENTREVENUESPLITAPPLIEDCONVERTED.ITEM.value('(ITEM/PAYMENTREVENUESPLITID)[1]', 'uniqueidentifier'),
PAYMENTREVENUESPLITAPPLIEDCONVERTED.BASEAMOUNT,
PAYMENTREVENUESPLITAPPLIEDCONVERTED.ITEM.value('(ITEM/AMOUNT)[1]', 'money'),
PAYMENTREVENUESPLITAPPLIEDCONVERTED.ORGANIZATIONAMOUNT,
PAYMENTREVENUESPLITAPPLIEDCONVERTED.ITEM.value('(ITEM/DESIGNATIONID)[1]', 'uniqueidentifier'),
PAYMENTREVENUESPLITAPPLIEDCONVERTED.ITEM.value('(ITEM/PLEDGETYPECODE)[1]', 'tinyint'),
PAYMENTREVENUESPLITAPPLIEDCONVERTED.ITEM.value('(ITEM/PLEDGEREVENUESPLITID)[1]', 'uniqueidentifier')
from
dbo.UFN_CURRENCY_GETCURRENCYVALUESBYPROPORTIONINXML
(
@paymentSplitsXml,
@PAYMENTTRANSACTIONCURRENCYID,
@PAYMENTBASECURRENCYID,
@ORGANIZATIONCURRENCYID,
@AMOUNT,
@BASEAMOUNT,
@PAYMENBASECURRENCYDECIMALDIGITS,
@ORGANIZATIONAMOUNT,
@ORGANIZATIONCURRENCYDECIMALDIGITS
) PAYMENTREVENUESPLITAPPLIEDCONVERTED
merge dbo.FINANCIALTRANSACTIONLINEITEM as target
using
(select
PAYMENTREVENUESPLITAPPLIEDCONVERTED.PAYMENTREVENUESPLITID as FINANCIALTRANSACTIONLINEITEMID,
@REVENUEID as FINANCIALTRANSACTIONID,
PAYMENTREVENUESPLITAPPLIEDCONVERTED.BASEAMOUNT,
PAYMENTREVENUESPLITAPPLIEDCONVERTED.TRANSACTIONAMOUNT as TRANSACTIONAMOUNT,
PAYMENTREVENUESPLITAPPLIEDCONVERTED.ORGANIZATIONAMOUNT,
isnull(@ADJUSTMENTPOSTSTATUSCODE, case when FINANCIALTRANSACTION.POSTSTATUSCODE = 3 then 3 else 1 end) as POSTSTATUSCODE,
coalesce(@ADJUSTMENTPOSTDATE, FINANCIALTRANSACTION.POSTDATE, @REVENUEDATE) as POSTDATE,
PAYMENTREVENUESPLITAPPLIEDCONVERTED.PLEDGEREVENUESPLITID as SOURCELINEITEMID
from
@PAYMENTREVENUESPLITAPPLIEDCONVERTED as PAYMENTREVENUESPLITAPPLIEDCONVERTED
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = @REVENUEID
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
where
PAYMENTREVENUESPLITAPPLIEDCONVERTED.DESIGNATIONID is not null --filter out the null designation holding the unapplied amount
or (PAYMENTREVENUESPLITAPPLIEDCONVERTED.PLEDGETYPECODE in (2, 18)) --filter back in null designations if typecode is for membership or membership add-on
and FINANCIALTRANSACTION.DELETEDON is null
) as source
on (source.FINANCIALTRANSACTIONLINEITEMID = target.ID)
when matched and target.TYPECODE = 99 and target.DELETEDON is null then update
set target.BASEAMOUNT = source.BASEAMOUNT,
target.TRANSACTIONAMOUNT = source.TRANSACTIONAMOUNT,
target.ORGAMOUNT = source.ORGANIZATIONAMOUNT,
target.POSTSTATUSCODE = source.POSTSTATUSCODE,
target.POSTDATE = source.POSTDATE,
target.DATEADDED = @CHANGEDATE,
target.DATECHANGED = @CHANGEDATE,
target.ADDEDBYID = @CAID,
target.CHANGEDBYID = @CAID,
target.FINANCIALTRANSACTIONLINEITEMADJUSTMENTID = @ADJUSTMENTID,
target.SOURCELINEITEMID = source.SOURCELINEITEMID,
target.TYPECODE = 0
when not matched by target then insert
(ID, FINANCIALTRANSACTIONID, BASEAMOUNT, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED, TRANSACTIONAMOUNT, ORGAMOUNT, POSTSTATUSCODE, POSTDATE, FINANCIALTRANSACTIONLINEITEMADJUSTMENTID, SOURCELINEITEMID)
values
(source.FINANCIALTRANSACTIONLINEITEMID, source.FINANCIALTRANSACTIONID, source.BASEAMOUNT, @CAID, @CAID, @CHANGEDATE, @CHANGEDATE, source.TRANSACTIONAMOUNT, source.ORGANIZATIONAMOUNT, source.POSTSTATUSCODE, source.POSTDATE, @ADJUSTMENTID, source.SOURCELINEITEMID);
merge dbo.REVENUESPLIT_EXT as target
using
(select
PAYMENTREVENUESPLITAPPLIEDCONVERTED.PAYMENTREVENUESPLITID as FINANCIALTRANSACTIONLINEITEMID,
PAYMENTREVENUESPLITAPPLIEDCONVERTED.DESIGNATIONID as DESIGNATIONID,
PAYMENTREVENUESPLITAPPLIEDCONVERTED.PLEDGETYPECODE as TYPECODE
from
@PAYMENTREVENUESPLITAPPLIEDCONVERTED as PAYMENTREVENUESPLITAPPLIEDCONVERTED
where
PAYMENTREVENUESPLITAPPLIEDCONVERTED.DESIGNATIONID is not null --filter out the null designation holding the unapplied amount
or (PAYMENTREVENUESPLITAPPLIEDCONVERTED.PLEDGETYPECODE in (2, 18)) --filter back in null designations if typecode is for membership or membership add-on
) as source
on (source.FINANCIALTRANSACTIONLINEITEMID = target.ID)
when not matched by target then insert
(ID, DESIGNATIONID, APPLICATIONCODE, TYPECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values
(source.FINANCIALTRANSACTIONLINEITEMID, source.DESIGNATIONID, @APPLICATIONCODE, source.TYPECODE, @CAID, @CAID, @CHANGEDATE, @CHANGEDATE);
insert into @CREATEDSPLIT(CREATEDREVENUESPLITID, SOURCEREVENUESPLITID)
select
SPLIT.ITEM.value('PAYMENTREVENUESPLITID[1]', 'uniqueidentifier'),
SPLIT.ITEM.value('PLEDGEREVENUESPLITID[1]', 'uniqueidentifier')
from
@paymentSplitsXml.nodes('/ITEMLIST/ITEM') as SPLIT(ITEM)
insert into dbo.INSTALLMENTSPLITPAYMENT
(
ID,
PAYMENTID,
PLEDGEID,
INSTALLMENTSPLITID,
AMOUNT,
OVERPAYMENTAPPLICATIONTYPECODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
APPLICATIONCURRENCYID,
APPLICATIONEXCHANGERATEID
)
select
newid(),
PAYMENTSPLIT.ITEM.value('PAYMENTREVENUESPLITID[1]', 'uniqueidentifier'),
@PLEDGEID,
PLEDGESPLIT.INSTALLMENTSPLITID,
PLEDGESPLIT.AMOUNTAPPLICATION,
@OVERPAYMENTAPPLICATIONTYPECODE,
@CAID,
@CAID,
@CHANGEDATE,
@CHANGEDATE,
@APPLICATIONCURRENCYID,
@APPLICATIONEXCHANGERATEID
from
@splits as PLEDGESPLIT
cross apply @paymentSplitsXml.nodes('/ITEMLIST/ITEM[sql:column("PLEDGESPLIT.PLEDGEREVENUESPLITID") = PLEDGEREVENUESPLITID]') PAYMENTSPLIT(ITEM);
set @AMOUNT = @amountPaid;
set @CREATEDSPLITS = (
select
FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT AMOUNT,
REVENUESPLIT_EXT.DESIGNATIONID,
FINANCIALTRANSACTIONLINEITEM.ID,
FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT_EXT.TYPECODE,
SOURCEREVENUESPLITID,
FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
from @CREATEDSPLIT as CREATEDSPLIT
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = CREATEDSPLIT.CREATEDREVENUESPLITID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
where FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE <> 1
for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
)