USP_REVENUE_APPLYTOREVENUESTREAMS
Stored proc to apply a payment to one or more revenue streams
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @CONSTITUENTID | uniqueidentifier | IN | |
| @DATE | datetime | IN | |
| @REVENUESTREAMS | xml | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CREATIONDATE | datetime | IN | |
| @SPLITSDECLININGGIFTAID | xml | INOUT | |
| @COVENANTGIFTSPLITS | xml | INOUT | |
| @GIFTAIDSPONSORSHIPSPLITS | xml | INOUT | |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_REVENUE_APPLYTOREVENUESTREAMS
(
@REVENUEID uniqueidentifier,
@CONSTITUENTID uniqueidentifier,
@DATE datetime,
@REVENUESTREAMS xml,
@CHANGEAGENTID uniqueidentifier,
@CREATIONDATE datetime,
@SPLITSDECLININGGIFTAID xml = null output,
@COVENANTGIFTSPLITS xml = null output,
@GIFTAIDSPONSORSHIPSPLITS xml = null output,
@PDACCOUNTSYSTEMID uniqueidentifier = null
)
as
set nocount on;
declare @APPLICATIONID uniqueidentifier;
declare @APPLICATIONTYPE tinyint;
declare @APPLIEDAMOUNT money;
declare @APPLIEDBASEAMOUNT money;
declare @APPLIEDORGANIZATIONAMOUNT money;
declare @APPLICATIONSPLITS xml;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASECURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
declare @ORGANIZATIONEXCHANGERATEID uniqueidentifier;
declare @APPLICATIONCODE tinyint;
declare @GIFTFIELDS xml;
declare @OTHERFIELDS xml;
declare @DECLINESGIFTAID bit;
declare @ISGIFTAIDSPONSORSHIP bit;
declare @OVERPAYMENTAPPLICATIONTYPECODE tinyint;
declare @DESIGNATIONID uniqueidentifier;
declare @OPPORTUNITYID uniqueidentifier;
declare @CATEGORYCODEID uniqueidentifier;
declare @OTHERTYPECODEID uniqueidentifier;
declare @CAMPAIGNS xml;
declare @SOLICITORS xml;
declare @RECOGNITIONCREDITS xml;
declare @MEMBERSHIPS xml;
declare @REVENUETYPECODE tinyint;
declare @SPLITSGIFTAIDINFOTBL table
(
REVENUESPLITID uniqueidentifier,
DECLINESGIFTAID bit,
ISCOVENANT bit,
ISSPONSORSHIP bit
)
if @PDACCOUNTSYSTEMID is null
set @PDACCOUNTSYSTEMID = '4B121C2C-CCE6-440D-894C-EA0DEF80D50B'
select
@TRANSACTIONCURRENCYID = FT.TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID = FT.BASEEXCHANGERATEID,
@ORGANIZATIONEXCHANGERATEID = FT.ORGEXCHANGERATEID
from
dbo.FINANCIALTRANSACTION as FT
where
FT.ID = @REVENUEID;
select
@BASECURRENCYID = CS.BASECURRENCYID
from PDACCOUNTSYSTEM as PAS
inner join CURRENCYSET as CS on PAS.CURRENCYSETID = CS.ID
where PAS.ID = @PDACCOUNTSYSTEMID
-- Revenue stream amounts need to be converted by proportion unless they were already converted by USP_REVENUE_UPDATEREVENUESTREAMS
if ((@REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM/BASEAMOUNT') = 0) and (@REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM/APPLIEDBASEAMOUNT') = 0)) or ((@REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM/ORGANIZATIONAMOUNT') = 0) and (@REVENUESTREAMS.exist('/REVENUESTREAMS/ITEM/APPLIEDORGANIZATIONAMOUNT') = 0))
set @REVENUESTREAMS = dbo.UFN_REVENUE_REVENUESTREAMS_CONVERTAMOUNTSINXML(@REVENUESTREAMS, @BASECURRENCYID, @ORGANIZATIONEXCHANGERATEID, @TRANSACTIONCURRENCYID, @BASEEXCHANGERATEID);
declare APPLICATIONSCURSOR cursor local fast_forward for
select
APPLICATIONID,
APPLIED,
APPLICATIONCODE,
GIFTFIELDS,
OTHERFIELDS,
MEMBERSHIPS,
DECLINESGIFTAID,
ISSPONSORSHIP,
OVERPAYMENTAPPLICATIONTYPECODE,
APPLIEDBASEAMOUNT,
APPLIEDORGANIZATIONAMOUNT,
APPLICATIONSPLITS,
CATEGORYCODEID
from dbo.UFN_REVENUE_GETAPPLICATIONS_FROMXML(@REVENUESTREAMS)
--TODO WHAT DO WE DO WITH THIS NOW
declare @UNAPPLIEDMATCHINGGIFTSPLITS xml
declare @RECEIPTTYPEPERPAYMENTFOUND bit
SET @RECEIPTTYPEPERPAYMENTFOUND = 0
begin try
open APPLICATIONSCURSOR;
fetch next from APPLICATIONSCURSOR into @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @GIFTFIELDS, @OTHERFIELDS, @MEMBERSHIPS, @DECLINESGIFTAID, @ISGIFTAIDSPONSORSHIP, @OVERPAYMENTAPPLICATIONTYPECODE, @APPLIEDBASEAMOUNT, @APPLIEDORGANIZATIONAMOUNT, @APPLICATIONSPLITS, @CATEGORYCODEID;
while @@FETCH_STATUS = 0
begin
if @APPLICATIONCODE = -1
raiserror('The application type is not supported.', 13, 1);
declare @AMOUNTPAID money
set @AMOUNTPAID = @APPLIEDAMOUNT;
if @APPLICATIONTYPE = 0 --Gift
begin
SELECT top 1
@DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
@OPPORTUNITYID = T.c.value('(OPPORTUNITYID)[1]','uniqueidentifier'),
@CATEGORYCODEID = T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier'),
@CAMPAIGNS = c.query('./CAMPAIGNS'),
@SOLICITORS = c.query('./SOLICITORS'),
@RECOGNITIONCREDITS = c.query('./RECOGNITIONS'),
@REVENUETYPECODE = T.c.value('(REVENUETYPECODE)[1]','tinyint')
FROM @GIFTFIELDS.nodes('/GIFTFIELDS/ITEM') T(c);
declare @GIFTID uniqueidentifier
set @GIFTID = null
exec dbo.USP_GIFT_ADDPAYMENT
@REVENUEID = @REVENUEID,
@AMOUNT = @APPLIEDAMOUNT,
@DESIGNATIONID = @DESIGNATIONID,
@OPPORTUNITYID = @OPPORTUNITYID,
@CAMPAIGNS = @CAMPAIGNS,
@SOLICITORS = @SOLICITORS,
@RECOGNITIONCREDITS = @RECOGNITIONCREDITS,
@CATEGORYCODEID = @CATEGORYCODEID,
@CREATIONDATE = @CREATIONDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@ID = @GIFTID output,
@REVENUETYPECODE = @REVENUETYPECODE,
@BASEAMOUNT = @APPLIEDBASEAMOUNT,
@ORGANIZATIONAMOUNT = @APPLIEDORGANIZATIONAMOUNT;
if @DECLINESGIFTAID = 1
insert into @SPLITSGIFTAIDINFOTBL (REVENUESPLITID, DECLINESGIFTAID) values (@GIFTID, 1)
if @ISGIFTAIDSPONSORSHIP = 1
begin
if exists(select 1 from @SPLITSGIFTAIDINFOTBL where REVENUESPLITID = @GIFTID)
begin
update @SPLITSGIFTAIDINFOTBL set ISSPONSORSHIP = 1 where REVENUESPLITID = @GIFTID;
end
else
begin
insert into @SPLITSGIFTAIDINFOTBL (REVENUESPLITID, ISSPONSORSHIP) values (@GIFTID, 1);
end
end
end
if @APPLICATIONTYPE = 1 --Event Registration
exec dbo.USP_EVENT_ADDPAYMENT
@REVENUEID = @REVENUEID,
@APPLICATIONID = @APPLICATIONID,
@APPLIEDAMOUNT = @APPLIEDAMOUNT,
@CREATIONDATE = @CREATIONDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@APPLIEDBASEAMOUNT = @APPLIEDBASEAMOUNT,
@APPLIEDORGANIZATIONAMOUNT = @APPLIEDORGANIZATIONAMOUNT,
@CATEGORYCODEID = @CATEGORYCODEID;
if @APPLICATIONTYPE in (
2, --Pledge
7, --MG Pledge
6, --Planned Gift
8, --Grant award
13, --Donor Challenge
17, --Pending gift
19 --Membership installment plan
)
begin
declare @PLEDGECREATEDSPLITS xml
exec dbo.USP_PLEDGE_ADDPAYMENT
@REVENUEID = @REVENUEID,
@APPLICATIONID = @APPLICATIONID,
@APPLIEDAMOUNT = @APPLIEDAMOUNT,
@CONSTITUENTID = @CONSTITUENTID,
@DATE = @DATE,
@UNAPPLIEDMATCHINGGIFTSPLITS = @UNAPPLIEDMATCHINGGIFTSPLITS,
@APPLICATIONTYPE = @APPLICATIONTYPE,
@AMOUNTPAID = @AMOUNTPAID output,
@CREATIONDATE = @CREATIONDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@CREATEDSPLITS = @PLEDGECREATEDSPLITS output,
@OVERPAYMENTAPPLICATIONTYPECODE = @OVERPAYMENTAPPLICATIONTYPECODE,
@APPLIEDBASEAMOUNT = @APPLIEDBASEAMOUNT,
@APPLIEDORGANIZATIONAMOUNT = @APPLIEDORGANIZATIONAMOUNT,
@APPLICATIONSPLITS = @APPLICATIONSPLITS;
if @AMOUNTPAID <> @APPLIEDAMOUNT
raiserror('BBERR_PLEDGENOTFULLYAPPLIED', 13, 1);
if @APPLICATIONTYPE = 2
begin
if @RECEIPTTYPEPERPAYMENTFOUND = 0
begin
-- reset the receipttypecode for pledge since initially set to just payment
-- which does not get the correct preference.
declare @PLEDGERECEIPTTYPECODE tinyint;
set @PLEDGERECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID,@APPLICATIONTYPE);
if @PLEDGERECEIPTTYPECODE = 0
begin
set @RECEIPTTYPEPERPAYMENTFOUND = 1
end
update dbo.REVENUE_EXT
set RECEIPTTYPECODE = @PLEDGERECEIPTTYPECODE
where ID = @REVENUEID;
end
end
-- Default the declines gift aid and covenant values for a pledge payment split from the pledge split
insert into @SPLITSGIFTAIDINFOTBL
(
REVENUESPLITID,
DECLINESGIFTAID,
ISCOVENANT
)
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
REVENUESPLITGIFTAID.DECLINESGIFTAID,
REVENUESPLITGIFTAID.ISCOVENANT
from @PLEDGECREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
where
REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or
REVENUESPLITGIFTAID.ISCOVENANT = 1
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
REVENUESPLITGIFTAID.ID,
REVENUESPLITGIFTAID.DECLINESGIFTAID,
REVENUESPLITGIFTAID.ISCOVENANT
from @PLEDGECREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
end
if @APPLICATIONTYPE = 3 --Recurring Gift
begin
declare @RECURRINGGIFTCREATEDSPLITS xml
exec dbo.USP_RECURRINGGIFT_ADDPAYMENT
@REVENUEID = @REVENUEID,
@APPLICATIONID = @APPLICATIONID,
@APPLIEDAMOUNT = @APPLIEDAMOUNT,
@CONSTITUENTID = @CONSTITUENTID,
@DATE = @DATE,
@CREATIONDATE = @CREATIONDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@CREATEDSPLITS = @RECURRINGGIFTCREATEDSPLITS output,
@BASEAPPLIEDAMOUNT = @APPLIEDBASEAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @APPLIEDORGANIZATIONAMOUNT;
if @RECEIPTTYPEPERPAYMENTFOUND = 0
begin
-- reset the receipttypecode for recurring gift since initially set to just payment
-- which does not get the correct preference.
declare @RECEIPTTYPECODE tinyint;
set @RECEIPTTYPECODE = dbo.UFN_CONSTITUENT_GETRECEIPTPREFERENCE(@CONSTITUENTID,@APPLICATIONTYPE);
if @RECEIPTTYPECODE = 0
begin
set @RECEIPTTYPEPERPAYMENTFOUND = 1
end
update dbo.REVENUE_EXT
set RECEIPTTYPECODE = @RECEIPTTYPECODE
where ID = @REVENUEID;
end
-- Default the declines gift aid value for a pledge payment split from the pledge split
insert into @SPLITSGIFTAIDINFOTBL
(
REVENUESPLITID,
DECLINESGIFTAID
)
select
T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
1
from @RECURRINGGIFTCREATEDSPLITS.nodes('/SPLITS/ITEM') T(c)
inner join dbo.REVENUESPLITGIFTAID on T.c.value('(SOURCEREVENUESPLITID)[1]','uniqueidentifier') = REVENUESPLITGIFTAID.ID
where REVENUESPLITGIFTAID.DECLINESGIFTAID = 1
end
if @APPLICATIONTYPE = 4 --Other
begin
SELECT top 1
@DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
@OTHERTYPECODEID = T.c.value('(OTHERTYPECODEID)[1]','uniqueidentifier'),
@CATEGORYCODEID = T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier'),
@CAMPAIGNS = c.query('./CAMPAIGNS'),
@SOLICITORS = c.query('./SOLICITORS'),
@RECOGNITIONCREDITS = c.query('./RECOGNITIONS')
FROM @OTHERFIELDS.nodes('/OTHERFIELDS/ITEM') T(c);
exec dbo.USP_OTHER_ADDPAYMENT
@REVENUEID = @REVENUEID,
@AMOUNT = @APPLIEDAMOUNT,
@DESIGNATIONID = @DESIGNATIONID,
@OTHERTYPECODEID = @OTHERTYPECODEID,
@CAMPAIGNS = @CAMPAIGNS,
@SOLICITORS = @SOLICITORS,
@CATEGORYCODEID = @CATEGORYCODEID,
@RECOGNITIONCREDITS = @RECOGNITIONCREDITS,
@CREATIONDATE = @CREATIONDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@BASEAMOUNT = @APPLIEDBASEAMOUNT,
@ORGANIZATIONAMOUNT = @APPLIEDORGANIZATIONAMOUNT;
end
if @APPLICATIONTYPE = 5 --Membership
begin
exec dbo.USP_MEMBERSHIP_ADDPAYMENT
@REVENUEID = @REVENUEID,
@CONSTITUENTID = @CONSTITUENTID,
@MEMBERSHIPID = @APPLICATIONID,
@AMOUNT = @APPLIEDAMOUNT,
@TRANSACTIONDATE = @DATE,
@MEMBERSHIPS = @MEMBERSHIPS,
@CREATIONDATE = @CREATIONDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@BASEAMOUNT = @APPLIEDBASEAMOUNT,
@ORGANIZATIONAMOUNT = @APPLIEDORGANIZATIONAMOUNT;
end
if @APPLICATIONTYPE = 100 -- unnappliedmatching gift claim
begin
SELECT top 1
@DESIGNATIONID = T.c.value('(DESIGNATIONID)[1]','uniqueidentifier'),
@CATEGORYCODEID = T.c.value('(CATEGORYCODEID)[1]','uniqueidentifier'),
@CAMPAIGNS = c.query('./CAMPAIGNS'),
@SOLICITORS = c.query('./SOLICITORS'),
@RECOGNITIONCREDITS = c.query('./RECOGNITIONS')
FROM @GIFTFIELDS.nodes('/GIFTFIELDS/ITEM') T(c);
exec dbo.USP_UNAPPLIEDMG_ADDPAYMENT
@REVENUEID = @REVENUEID,
@AMOUNT = @APPLIEDAMOUNT,
@DESIGNATIONID = @DESIGNATIONID,
@CAMPAIGNS = @CAMPAIGNS,
@SOLICITORS = @SOLICITORS,
@RECOGNITIONCREDITS = @RECOGNITIONCREDITS,
@CATEGORYCODEID = @CATEGORYCODEID,
@CREATIONDATE = @CREATIONDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@BASEAMOUNT = @APPLIEDBASEAMOUNT,
@ORGANIZATIONAMOUNT = @APPLIEDORGANIZATIONAMOUNT;
end
if @APPLICATIONTYPE = 12 --auction item purchase
begin
exec dbo.USP_AUCTIONPURCHASE_ADDPAYMENT
@REVENUEID = @REVENUEID,
@APPLICATIONID = @APPLICATIONID,
@APPLIEDAMOUNT = @APPLIEDAMOUNT,
@CONSTITUENTID = @CONSTITUENTID,
@DATE = @DATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID,
@BASEAPPLIEDAMOUNT = @APPLIEDBASEAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @APPLIEDORGANIZATIONAMOUNT;
end
if @APPLICATIONTYPE = 15 --Event Sponsorship
begin
exec dbo.USP_SPONSOR_ADDPAYMENT
@REVENUEID = @REVENUEID,
@APPLICATIONID = @APPLICATIONID,
@APPLIEDAMOUNT = @APPLIEDAMOUNT,
@CREATIONDATE = @CREATIONDATE,
@CHANGEAGENTID = @CHANGEAGENTID,
@BASEAPPLIEDAMOUNT = @APPLIEDBASEAMOUNT,
@ORGANIZATIONAPPLIEDAMOUNT = @APPLIEDORGANIZATIONAMOUNT;
end
fetch next from APPLICATIONSCURSOR into @APPLICATIONID, @APPLIEDAMOUNT, @APPLICATIONTYPE, @GIFTFIELDS, @OTHERFIELDS, @MEMBERSHIPS, @DECLINESGIFTAID, @ISGIFTAIDSPONSORSHIP, @OVERPAYMENTAPPLICATIONTYPECODE, @APPLIEDBASEAMOUNT, @APPLIEDORGANIZATIONAMOUNT, @APPLICATIONSPLITS, @CATEGORYCODEID;
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
end catch
--When a cursor is used, it should be explicitly closed/deallocated in case of blocking or USP running long
close APPLICATIONSCURSOR;
deallocate APPLICATIONSCURSOR;
set @SPLITSDECLININGGIFTAID = (select
REVENUESPLITID
from @SPLITSGIFTAIDINFOTBL
where DECLINESGIFTAID = 1
for xml raw('ITEM'),type,elements,root('SPLITSDECLININGGIFTAID'),BINARY BASE64)
set @COVENANTGIFTSPLITS = (select
REVENUESPLITID
from @SPLITSGIFTAIDINFOTBL
where ISCOVENANT = 1
for xml raw('ITEM'),type,elements,root('COVENANTGIFTSPLITS'),BINARY BASE64)
set @GIFTAIDSPONSORSHIPSPLITS = (select
REVENUESPLITID
from @SPLITSGIFTAIDINFOTBL
where ISSPONSORSHIP = 1
for xml raw('ITEM'),type,elements,root('GIFTAIDSPONSORSHIPSPLITS'),BINARY BASE64)