USP_RECONCILEMATCHINGGIFT_UPDATEPAYMENTSPLITS
Updates a matching gift payment's splits when reconciling matching gifts.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUESPLITID | uniqueidentifier | IN | |
| @TOTALAMOUNTAPPLIED | money | IN | |
| @REVENUESPLITAMOUNT | money | IN | |
| @REVENUEID | uniqueidentifier | IN | |
| @NEWDESIGNATIONEXISTS | bit | IN | |
| @CLAIMSPAIDCOUNT | int | IN | |
| @DESIGNATIONSPAIDXML | xml | IN | |
| @PAYMENTDESIGNATIONID | uniqueidentifier | IN | |
| @POSTDATE | datetime | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CREATIONDATE | datetime | IN | |
| @REVENUESPLITDESIGNATIONCHANGED | uniqueidentifier | INOUT |
Definition
Copy
CREATE procedure dbo.USP_RECONCILEMATCHINGGIFT_UPDATEPAYMENTSPLITS
(
@REVENUESPLITID uniqueidentifier,
@TOTALAMOUNTAPPLIED money,
@REVENUESPLITAMOUNT money,
@REVENUEID uniqueidentifier,
@NEWDESIGNATIONEXISTS bit,
@CLAIMSPAIDCOUNT int,
@DESIGNATIONSPAIDXML xml,
@PAYMENTDESIGNATIONID uniqueidentifier,
@POSTDATE datetime,
@CHANGEAGENTID uniqueidentifier,
@CREATIONDATE datetime,
@REVENUESPLITDESIGNATIONCHANGED uniqueidentifier = null output
)
as
set nocount on;
-- Holds the designations and amounts being paid
declare @DESIGNATIONSPAID table
(
INSTALLMENTSPLITPAYMENTID uniqueidentifier,
DESIGNATIONID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
AMOUNT money,
BASEAMOUNT money,
APPLICATIONID uniqueidentifier
);
insert into @DESIGNATIONSPAID(INSTALLMENTSPLITPAYMENTID, DESIGNATIONID, REVENUESPLITID, AMOUNT, APPLICATIONID)
select
T.c.value('(INSTALLMENTSPLITPAYMENTID)[1]','uniqueidentifier'),
T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
T.c.value('(REVENUESPLITID)[1]','uniqueidentifier'),
T.c.value('(AMOUNT)[1]','money'),
T.c.value('(APPLICATIONID)[1]','uniqueidentifier')
from @DESIGNATIONSPAIDXML.nodes('/DESIGNATIONSPAID/ITEM') T(c);
-- First, if all the split amount was simply given to a different designation and was only applied to a single
-- claim, just update the designation ID for the existing split
if @NEWDESIGNATIONEXISTS = 1 and @TOTALAMOUNTAPPLIED = @REVENUESPLITAMOUNT and @CLAIMSPAIDCOUNT = 1
begin
update dbo.REVENUESPLIT set
DESIGNATIONID = (select DESIGNATIONID from @DESIGNATIONSPAID),
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CREATIONDATE
where ID = @REVENUESPLITID;
set @REVENUESPLITDESIGNATIONCHANGED = @REVENUESPLITID;
end
else
begin
declare @INSTALLMENTSPLITPAYMENTIDTONOTUPDATE uniqueidentifier, @RECOGNITIONS xml, @SOLICITORS xml;
set @RECOGNITIONS = (select ID, AMOUNT
from dbo.REVENUERECOGNITION
where REVENUESPLITID = @REVENUESPLITID
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64);
set @SOLICITORS = (select ID, AMOUNT
from dbo.REVENUESOLICITOR
where REVENUESPLITID = @REVENUESPLITID
for xml raw('ITEM'),type,elements,root('AMOUNTSTOPRORATE'),binary base64);
declare @DELETEEXISTINGSPLIT bit;
set @DELETEEXISTINGSPLIT = 0;
declare @PAYMENTBASEAMOUNT money, @DEDUCTBASEAMOUNT money, @DEDUCTORGANIZATIONAMOUNT money;
declare @BASECURRENCYID uniqueidentifier, @BASEEXCHANGERATEID uniqueidentifier, @TRANSACTIONCURRENCYID uniqueidentifier, @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();
select
@PAYMENTBASEAMOUNT = AMOUNT,
@BASECURRENCYID = BASECURRENCYID,
@BASEEXCHANGERATEID = BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID = TRANSACTIONCURRENCYID,
@ORGANIZATIONEXCHANGERATEID = ORGANIZATIONEXCHANGERATEID
from dbo.REVENUESPLIT
where ID = @REVENUESPLITID;
declare @BASEDECIMALDIGITS tinyint;
select
@BASEDECIMALDIGITS = DECIMALDIGITS
from dbo.CURRENCY
where CURRENCY.ID = @BASECURRENCYID;
if @TOTALAMOUNTAPPLIED < @REVENUESPLITAMOUNT or
exists(select top 1 INSTALLMENTSPLITPAYMENTID from @DESIGNATIONSPAID where DESIGNATIONID = @PAYMENTDESIGNATIONID)
begin
declare @AMOUNTTODEDUCT money;
if @TOTALAMOUNTAPPLIED < @REVENUESPLITAMOUNT
set @AMOUNTTODEDUCT = @TOTALAMOUNTAPPLIED;
else
begin
select top 1
@INSTALLMENTSPLITPAYMENTIDTONOTUPDATE = INSTALLMENTSPLITPAYMENTID
from @DESIGNATIONSPAID
where DESIGNATIONID = @PAYMENTDESIGNATIONID;
select @AMOUNTTODEDUCT = sum(AMOUNT)
from @DESIGNATIONSPAID
where INSTALLMENTSPLITPAYMENTID <> @INSTALLMENTSPLITPAYMENTIDTONOTUPDATE;
end
exec dbo.USP_CURRENCY_GETCURRENCYVALUES
@AMOUNT = @AMOUNTTODEDUCT,
@DATE = null,
@BASECURRENCYID = @BASECURRENCYID,
@BASEEXCHANGERATEID = @BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID = @TRANSACTIONCURRENCYID,
@BASEAMOUNT = @DEDUCTBASEAMOUNT output,
@ORGANIZATIONAMOUNT = @DEDUCTORGANIZATIONAMOUNT output,
@ORGANIZATIONEXCHANGERATEID = @ORGANIZATIONEXCHANGERATEID;
update dbo.REVENUESPLIT set
TRANSACTIONAMOUNT = TRANSACTIONAMOUNT - @AMOUNTTODEDUCT,
AMOUNT = @PAYMENTBASEAMOUNT - @DEDUCTBASEAMOUNT,
ORGANIZATIONAMOUNT = ORGANIZATIONAMOUNT - @DEDUCTORGANIZATIONAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CREATIONDATE
where ID = @REVENUESPLITID;
--Bug#734240 - Updating JOURNALENTRY table AMOUNT fields, since these are not updated when updating REVENUESPLIT
update dbo.JOURNALENTRY set
TRANSACTIONAMOUNT = TRANSACTIONAMOUNT - @AMOUNTTODEDUCT,
BASEAMOUNT = @PAYMENTBASEAMOUNT - @DEDUCTBASEAMOUNT,
ORGAMOUNT = ORGAMOUNT - @DEDUCTORGANIZATIONAMOUNT,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CREATIONDATE
where FINANCIALTRANSACTIONLINEITEMID = @REVENUESPLITID;
update dbo.REVENUERECOGNITION set
AMOUNT = UPDATEDAMOUNT.AMOUNT,
ORGANIZATIONAMOUNT = case
when BASECURRENCYID <> @ORGANIZATIONCURRENCYID
then dbo.UFN_CURRENCY_CONVERT(UPDATEDAMOUNT.AMOUNT, ORGANIZATIONEXCHANGERATEID)
else UPDATEDAMOUNT.AMOUNT
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CREATIONDATE
from dbo.UFN_SPLITS_PRORATEAMOUNTS(@PAYMENTBASEAMOUNT, @PAYMENTBASEAMOUNT - @DEDUCTBASEAMOUNT, @BASEDECIMALDIGITS, @RECOGNITIONS) UPDATEDAMOUNT
where REVENUERECOGNITION.ID = UPDATEDAMOUNT.ID;
update dbo.REVENUESOLICITOR set
AMOUNT = UPDATEDAMOUNT.AMOUNT,
ORGANIZATIONAMOUNT = case
when BASECURRENCYID <> @ORGANIZATIONCURRENCYID
then dbo.UFN_CURRENCY_CONVERT(UPDATEDAMOUNT.AMOUNT, ORGANIZATIONEXCHANGERATEID)
else UPDATEDAMOUNT.AMOUNT
end,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CREATIONDATE
from dbo.UFN_SPLITS_PRORATEAMOUNTS(@PAYMENTBASEAMOUNT, @PAYMENTBASEAMOUNT - @DEDUCTBASEAMOUNT, @BASEDECIMALDIGITS, @SOLICITORS) UPDATEDAMOUNT
where REVENUESOLICITOR.ID = UPDATEDAMOUNT.ID;
end
else
begin
--Bug 13617 AdamBu 9/17/2008
--If an unapplied MG payment is being reconciled to more than one MG claim and there is no
-- unapplied/unreconciled revenue left on the payment and none of the claims are applied
-- to the same designation as the unapplied payment, then we need to delete to original
-- payment split because it isn't being modified and reused. See below.
set @DELETEEXISTINGSPLIT = 1;
end
-- Create new splits for claims
update @DESIGNATIONSPAID set
REVENUESPLITID = newid()
where
@INSTALLMENTSPLITPAYMENTIDTONOTUPDATE is null or
INSTALLMENTSPLITPAYMENTID <> @INSTALLMENTSPLITPAYMENTIDTONOTUPDATE;
insert into dbo.REVENUESPLIT (ID, REVENUEID, DESIGNATIONID, TRANSACTIONAMOUNT, TRANSACTIONCURRENCYID, AMOUNT, BASECURRENCYID, BASEEXCHANGERATEID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, TYPECODE, APPLICATIONCODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
DESIGNATIONSPAID.REVENUESPLITID,
REVENUESPLIT.REVENUEID,
DESIGNATIONSPAID.DESIGNATIONID,
DESIGNATIONSPAID.AMOUNT,
REVENUESPLIT.TRANSACTIONCURRENCYID,
CURRENCYVALUES.BASEAMOUNT,
REVENUESPLIT.BASECURRENCYID,
REVENUESPLIT.BASEEXCHANGERATEID,
CURRENCYVALUES.ORGANIZATIONAMOUNT,
REVENUESPLIT.ORGANIZATIONEXCHANGERATEID,
REVENUESPLIT.TYPECODE,
REVENUESPLIT.APPLICATIONCODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CREATIONDATE,
@CREATIONDATE
from dbo.REVENUESPLIT
cross join @DESIGNATIONSPAID as DESIGNATIONSPAID
cross apply dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(
DESIGNATIONSPAID.AMOUNT,
null,
REVENUESPLIT.BASECURRENCYID,
REVENUESPLIT.BASEEXCHANGERATEID,
REVENUESPLIT.TRANSACTIONCURRENCYID,
null,
null,
null,
REVENUESPLIT.ORGANIZATIONEXCHANGERATEID,
0) as CURRENCYVALUES
where
REVENUESPLIT.ID = @REVENUESPLITID and
DESIGNATIONSPAID.REVENUESPLITID <> @REVENUESPLITID;
-- Set the base amounts in the DESIGNATIONSPAID table.
update DP
set BASEAMOUNT = (select AMOUNT
from dbo.REVENUESPLIT
where ID = DP.REVENUESPLITID)
from @DESIGNATIONSPAID as DP;
update dbo.INSTALLMENTSPLITPAYMENT set
PAYMENTID = DESIGNATIONSPAID.REVENUESPLITID,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CREATIONDATE
from @DESIGNATIONSPAID as DESIGNATIONSPAID
where
INSTALLMENTSPLITPAYMENT.ID = DESIGNATIONSPAID.INSTALLMENTSPLITPAYMENTID and
DESIGNATIONSPAID.REVENUESPLITID <> @REVENUESPLITID;
-- Copy recognition credits and solicitors to new splits
insert into dbo.REVENUERECOGNITION (REVENUESPLITID, CONSTITUENTID, AMOUNT, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, EFFECTIVEDATE, REVENUERECOGNITIONTYPECODEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
DESIGNATIONSPAID.REVENUESPLITID,
REVENUERECOGNITION.CONSTITUENTID,
UPDATEDAMOUNT.AMOUNT,
REVENUERECOGNITION.BASECURRENCYID,
case
when REVENUERECOGNITION.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
then dbo.UFN_CURRENCY_CONVERT(UPDATEDAMOUNT.AMOUNT, REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID)
else UPDATEDAMOUNT.AMOUNT
end ORGANIZATIONAMOUNT,
REVENUERECOGNITION.ORGANIZATIONEXCHANGERATEID,
REVENUERECOGNITION.EFFECTIVEDATE,
REVENUERECOGNITION.REVENUERECOGNITIONTYPECODEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CREATIONDATE,
@CREATIONDATE
from @DESIGNATIONSPAID as DESIGNATIONSPAID
cross apply dbo.UFN_SPLITS_PRORATEAMOUNTS(@PAYMENTBASEAMOUNT, DESIGNATIONSPAID.BASEAMOUNT, @BASEDECIMALDIGITS, @RECOGNITIONS) UPDATEDAMOUNT
inner join dbo.REVENUERECOGNITION on REVENUERECOGNITION.ID = UPDATEDAMOUNT.ID
where DESIGNATIONSPAID.REVENUESPLITID <> @REVENUESPLITID;
insert into dbo.REVENUESOLICITOR (REVENUESPLITID, CONSTITUENTID, AMOUNT, BASECURRENCYID, ORGANIZATIONAMOUNT, ORGANIZATIONEXCHANGERATEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
DESIGNATIONSPAID.REVENUESPLITID,
REVENUESOLICITOR.CONSTITUENTID,
UPDATEDAMOUNT.AMOUNT,
REVENUESOLICITOR.BASECURRENCYID,
case
when REVENUESOLICITOR.BASECURRENCYID <> @ORGANIZATIONCURRENCYID
then dbo.UFN_CURRENCY_CONVERT(UPDATEDAMOUNT.AMOUNT, REVENUESOLICITOR.ORGANIZATIONEXCHANGERATEID)
else UPDATEDAMOUNT.AMOUNT
end ORGANIZATIONAMOUNT,
REVENUESOLICITOR.ORGANIZATIONEXCHANGERATEID,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CREATIONDATE,
@CREATIONDATE
from @DESIGNATIONSPAID as DESIGNATIONSPAID
cross apply dbo.UFN_SPLITS_PRORATEAMOUNTS(@PAYMENTBASEAMOUNT, DESIGNATIONSPAID.BASEAMOUNT, @BASEDECIMALDIGITS, @SOLICITORS) UPDATEDAMOUNT
inner join dbo.REVENUESOLICITOR on REVENUESOLICITOR.ID = UPDATEDAMOUNT.ID
where DESIGNATIONSPAID.REVENUESPLITID <> @REVENUESPLITID;
--Bug 13617 AdamBu 9/17/2008
--If an unapplied MG payment is being reconciled to more than one MG claim and there is no
-- unapplied/unreconciled revenue left on the payment and none of the claims are applied
-- to the same designation as the unapplied payment, then we need to delete to original
-- payment split because it isn't being modified and reused. See above.
if @DELETEEXISTINGSPLIT = 1
begin
delete REVENUESPLIT
where ID=@REVENUESPLITID;
end
end
-- Create the adjustment if the revenue has already posted
if exists (select 1 from dbo.REVENUEPOSTED where ID = @REVENUEID)
begin
exec dbo.USP_RECONCILEMATCHINGGIFTPAYMENT_ADJUST @REVENUEID,@CHANGEAGENTID,@CREATIONDATE,@POSTDATE,'Designation adjusted',1,1;
end
else if (select POSTSTATUSCODE from dbo.FINANCIALTRANSACTION where ID = @REVENUEID) <> 2
begin
exec dbo.USP_SAVE_REVENUEGLDISTRIBUTION @REVENUEID, @CHANGEAGENTID, @CREATIONDATE;
end
if @DELETEEXISTINGSPLIT = 1
begin
--Re-create Gift Fee's
declare @CONSTITUENTID uniqueidentifier = (select CONSTITUENTID from dbo.REVENUE where ID=@REVENUEID)
exec USP_PAYMENT_ADDGIFTFEES @REVENUEID,@CONSTITUENTID,@CHANGEAGENTID,@CREATIONDATE
end
-- Validate the splits
/* commenting out unnecessary code since USP_REVENUESPLIT_VALIDATESPLITS has been commented out.
declare @SPLITS xml;
select @SPLITS =
(
select
AMOUNT,
DESIGNATIONID,
ID
from dbo.REVENUESPLIT
where REVENUEID = @REVENUEID
for xml raw('ITEM'),type,elements,root('SPLITS'),binary base64
);
declare @REVENUEAMOUNT money;
select @REVENUEAMOUNT = AMOUNT
from dbo.REVENUE
where ID = @REVENUEID;
*/
declare @REVENUEGIVENANONYMOUSLY bit
select @REVENUEGIVENANONYMOUSLY = R.GIVENANONYMOUSLY
from dbo.REVENUE_EXT R
where R.ID = @REVENUEID
--exec dbo.USP_REVENUESPLIT_VALIDATESPLITS @SPLITS, @REVENUEAMOUNT;
declare @CREATEDSPLITS xml;
declare @SPLITID uniqueidentifier, @APPLIEDAMOUNT money, @APPLICATIONID uniqueidentifier, @DESIGNATIONID uniqueidentifier;
declare MGSCURSOR cursor local fast_forward for
select REVENUESPLITID,
AMOUNT,
APPLICATIONID,
DESIGNATIONID
from @DESIGNATIONSPAID
open MGSCURSOR;
fetch next from MGSCURSOR into @SPLITID, @APPLIEDAMOUNT, @APPLICATIONID, @DESIGNATIONID;
while @@FETCH_STATUS = 0
begin
select @CREATEDSPLITS =
(
select
@SPLITID as ID,
CURRENCYVALUES.BASEAMOUNT as AMOUNT,
@DESIGNATIONID as DESIGNATIONID
from dbo.UFN_CURRENCY_GETCURRENCYVALUES_2(@APPLIEDAMOUNT,
null,
@BASECURRENCYID,
@BASEEXCHANGERATEID,
@TRANSACTIONCURRENCYID,
null,
null,
null,
@ORGANIZATIONEXCHANGERATEID,
0) as CURRENCYVALUES
for xml raw('ITEM'),type,elements,root('SPLITS'),BINARY BASE64
);
exec dbo.USP_RECOGNITIONCREDITS_ADDBASEDONMGCPREFERENCES
@SPLITS = @CREATEDSPLITS,
@APPLICATIONID = @APPLICATIONID,
@CHANGEAGENTID = @CHANGEAGENTID,
@CURRENTDATE = @CREATIONDATE,
@REVENUEGIVENANONYMOUSLY = @REVENUEGIVENANONYMOUSLY
fetch next from MGSCURSOR into @SPLITID, @APPLIEDAMOUNT, @APPLICATIONID, @DESIGNATIONID;
end
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close MGSCURSOR;
deallocate MGSCURSOR;