USP_R68_PROCESSREFUNDS
Process the refunds for the given R68 business process.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @R68ID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CHANGEDATE | datetime | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_R68_PROCESSREFUNDS
(
@R68ID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier = null,
@CHANGEDATE datetime = null,
@CURRENTAPPUSERID uniqueidentifier = null
)
as
begin
set nocount on;
if @CHANGEAGENTID is null
exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;
if @CHANGEDATE is null
set @CHANGEDATE = getdate();
declare @CHARITYCLAIMREFERENCENUMBER nvarchar(10) = '';
declare @PREVIEWONLY bit = 0;
declare @IDSETREGISTERID uniqueidentifier;
declare @STARTDATE datetime;
declare @ENDDATE datetime;
declare @REFUNDTAXCLAIMNUMBER nvarchar(10) = '';
declare @INCLUDEPOSTEDGIFTS bit = 0;
begin try
-- retrieve the process parameters
select
@CHARITYCLAIMREFERENCENUMBER = REFERENCENUMBER,
@PREVIEWONLY = PREVIEWONLY,
@IDSETREGISTERID = IDSETREGISTERID,
@STARTDATE = STARTDATE,
@ENDDATE = ENDDATE,
@REFUNDTAXCLAIMNUMBER = TAXCLAIMNUMBER,
@INCLUDEPOSTEDGIFTS = INCLUDEPOSTEDGIFTS
from dbo.R68
where ID = @R68ID;
-- Populate site list table based on Charity Claim Number
declare @R68SITES table (SITEID uniqueidentifier);
insert into @R68SITES
select SITEID
from dbo.UFN_R68_SITESBYREFERENCENUMBER(@CHARITYCLAIMREFERENCENUMBER);
if not @IDSETREGISTERID is null
begin
-- Refunds based on altered declarations
insert into dbo.R68REFUNDDETAIL
(R68ID, REVENUESPLITID, CONSTITUENTID, ORIGINALGIFTAMOUNT, ORIGINALTAXCLAIMNUMBER, ORIGINALBASETAXCLAIMAMOUNT, ORIGINALTRANSITIONALTAXCLAIMAMOUNT, INCLUDETRANSITIONALAMOUNTCODE, REFUNDSOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
@R68ID,
REVENUESPLIT.ID,
REVENUE.CONSTITUENTID,
REVENUESPLIT.AMOUNT,
REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT,
REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT,
REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from dbo.REVENUESPLITGIFTAID
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) SELECTION
on REVENUESPLITGIFTAID.ID = SELECTION.ID
inner join dbo.REVENUESPLIT
on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
inner join dbo.REVENUE
on REVENUESPLIT.REVENUEID = REVENUE.ID
left outer join dbo.REVENUEPOSTED
on REVENUE.ID = REVENUEPOSTED.ID
left outer join dbo.EVENTREGISTRANTPAYMENT
on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
left outer join dbo.MEMBERSHIPTRANSACTION
on REVENUESPLIT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
where len(REVENUESPLITGIFTAID.TAXCLAIMNUMBER) > 0
and (@STARTDATE is null or REVENUE.DATE >= @STARTDATE )
and (@ENDDATE is null or REVENUE.DATE <= @ENDDATE)
and (@INCLUDEPOSTEDGIFTS = 1 or REVENUEPOSTED.ID is null)
and exists (
select REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
inner join @R68SITES R68SITES
on (R68SITES.SITEID = REVSITES.SITEID) or (R68SITES.SITEID is null and REVSITES.SITEID is null)
/* where clause is #SITEEXTENTION code*/
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
or exists (
select 1
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '5F83E4C6-7CAD-4b2c-95A6-506BB18130AB', 4) userSites
where userSites.SITEID=[REVSITES].[SITEID]
or (userSites.SITEID is null and [REVSITES].[SITEID] is null)
)
)
and (dbo.UFN_VALIDYESDECLARATION(REVENUE.DATE, REVENUE.CONSTITUENTID, REVENUESPLIT.DESIGNATIONID, EVENTREGISTRANTPAYMENT.REGISTRANTID, MEMBERSHIPTRANSACTION.MEMBERSHIPID) = 0);
-- manual refunds and adjustment refunds that have not been claimed and fit criteria
insert into dbo.R68REFUNDDETAIL
(R68ID, REVENUESPLITID, CONSTITUENTID, ORIGINALGIFTAMOUNT, ORIGINALTAXCLAIMNUMBER, ORIGINALBASETAXCLAIMAMOUNT, ORIGINALTRANSITIONALTAXCLAIMAMOUNT, INCLUDETRANSITIONALAMOUNTCODE, REFUNDSOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
@R68ID,
REVENUESPLITGIFTAIDREFUND.REVENUESPLITID,
REVENUE.CONSTITUENTID,
REVENUESPLITGIFTAIDREFUND.ORIGINALGIFTAMOUNT,
REVENUESPLITGIFTAIDREFUND.ORIGINALTAXCLAIMNUMBER,
REVENUESPLITGIFTAIDREFUND.ORIGINALBASETAXCLAIMAMOUNT,
REVENUESPLITGIFTAIDREFUND.ORIGINALTRANSITIONALTAXCLAIMAMOUNT,
REVENUESPLITGIFTAIDREFUND.INCLUDETRANSITIONALAMOUNTCODE,
REVENUESPLITGIFTAIDREFUND.REFUNDSOURCECODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from dbo.REVENUESPLITGIFTAIDREFUND
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) SELECTION
on REVENUESPLITGIFTAIDREFUND.REVENUESPLITID = SELECTION.ID
inner join dbo.REVENUESPLIT
on REVENUESPLITGIFTAIDREFUND.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.REVENUE
on REVENUESPLIT.REVENUEID = REVENUE.ID
left outer join dbo.REVENUEPOSTED
on REVENUE.ID = REVENUEPOSTED.ID
left outer join dbo.EVENTREGISTRANTPAYMENT
on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
left outer join dbo.MEMBERSHIPTRANSACTION
on REVENUESPLIT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
where REVENUESPLITGIFTAIDREFUND.REFUNDTAXCLAIMNUMBER = ''
and REFUNDSOURCECODE in (1,2)
and (@STARTDATE is null or REVENUE.DATE >= @STARTDATE )
and (@ENDDATE is null or REVENUE.DATE <= @ENDDATE)
and (@INCLUDEPOSTEDGIFTS = 1 or REVENUEPOSTED.ID is null)
and exists (
select REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
inner join @R68SITES R68SITES
on (R68SITES.SITEID = REVSITES.SITEID) or (R68SITES.SITEID is null and REVSITES.SITEID is null)
/* where clause is #SITEEXTENTION code*/
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
or exists (
select 1
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '5F83E4C6-7CAD-4b2c-95A6-506BB18130AB', 4) userSites
where userSites.SITEID=[REVSITES].[SITEID]
or (userSites.SITEID is null and [REVSITES].[SITEID] is null)
)
)
end
else
begin
-- Refunds based on altered declarations
insert into dbo.R68REFUNDDETAIL
(R68ID, REVENUESPLITID, CONSTITUENTID, ORIGINALGIFTAMOUNT, ORIGINALTAXCLAIMNUMBER, ORIGINALBASETAXCLAIMAMOUNT, ORIGINALTRANSITIONALTAXCLAIMAMOUNT, INCLUDETRANSITIONALAMOUNTCODE, REFUNDSOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
@R68ID,
REVENUESPLIT.ID,
REVENUE.CONSTITUENTID,
REVENUESPLIT.AMOUNT,
REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT,
REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT,
REVENUESPLITGIFTAID.INCLUDETRANSITIONALAMOUNTCODE,
0,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from dbo.REVENUESPLITGIFTAID
inner join dbo.REVENUESPLIT
on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
inner join dbo.REVENUE
on REVENUESPLIT.REVENUEID = REVENUE.ID
left outer join dbo.REVENUEPOSTED
on REVENUE.ID = REVENUEPOSTED.ID
left outer join dbo.EVENTREGISTRANTPAYMENT
on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
left outer join dbo.MEMBERSHIPTRANSACTION
on REVENUESPLIT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
where len(REVENUESPLITGIFTAID.TAXCLAIMNUMBER) > 0
and (@STARTDATE is null or REVENUE.DATE >= @STARTDATE)
and (@ENDDATE is null or REVENUE.DATE <= @ENDDATE)
and (@INCLUDEPOSTEDGIFTS = 1 or REVENUEPOSTED.ID is null)
and exists (
select REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
inner join @R68SITES R68SITES
on (R68SITES.SITEID = REVSITES.SITEID) or (R68SITES.SITEID is null and REVSITES.SITEID is null)
/* where clause is #SITEEXTENTION code*/
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
or exists (
select 1
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '5F83E4C6-7CAD-4b2c-95A6-506BB18130AB', 4) userSites
where userSites.SITEID=[REVSITES].[SITEID]
or (userSites.SITEID is null and [REVSITES].[SITEID] is null)
)
)
and (dbo.UFN_VALIDYESDECLARATION(REVENUE.DATE, REVENUE.CONSTITUENTID, REVENUESPLIT.DESIGNATIONID, EVENTREGISTRANTPAYMENT.REGISTRANTID, MEMBERSHIPTRANSACTION.MEMBERSHIPID) = 0);
-- manual refunds and adjustment refunds that have not been claimed and fit criteria
insert into dbo.R68REFUNDDETAIL
(R68ID, REVENUESPLITID, CONSTITUENTID, ORIGINALGIFTAMOUNT, ORIGINALTAXCLAIMNUMBER, ORIGINALBASETAXCLAIMAMOUNT, ORIGINALTRANSITIONALTAXCLAIMAMOUNT, INCLUDETRANSITIONALAMOUNTCODE, REFUNDSOURCECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
select
@R68ID,
REVENUESPLITGIFTAIDREFUND.REVENUESPLITID,
REVENUE.CONSTITUENTID,
REVENUESPLITGIFTAIDREFUND.ORIGINALGIFTAMOUNT,
REVENUESPLITGIFTAIDREFUND.ORIGINALTAXCLAIMNUMBER,
REVENUESPLITGIFTAIDREFUND.ORIGINALBASETAXCLAIMAMOUNT,
REVENUESPLITGIFTAIDREFUND.ORIGINALTRANSITIONALTAXCLAIMAMOUNT,
REVENUESPLITGIFTAIDREFUND.INCLUDETRANSITIONALAMOUNTCODE,
REVENUESPLITGIFTAIDREFUND.REFUNDSOURCECODE,
@CHANGEAGENTID,
@CHANGEAGENTID,
@CHANGEDATE,
@CHANGEDATE
from dbo.REVENUESPLITGIFTAIDREFUND
inner join dbo.REVENUESPLIT
on REVENUESPLITGIFTAIDREFUND.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.REVENUE
on REVENUESPLIT.REVENUEID = REVENUE.ID
left outer join dbo.REVENUEPOSTED
on REVENUE.ID = REVENUEPOSTED.ID
left outer join dbo.EVENTREGISTRANTPAYMENT
on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
left outer join dbo.MEMBERSHIPTRANSACTION
on REVENUESPLIT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
where REVENUESPLITGIFTAIDREFUND.REFUNDTAXCLAIMNUMBER = ''
and REFUNDSOURCECODE in (1,2)
and (@STARTDATE is null or REVENUE.DATE >= @STARTDATE )
and (@ENDDATE is null or REVENUE.DATE <= @ENDDATE)
and (@INCLUDEPOSTEDGIFTS = 1 or REVENUEPOSTED.ID is null)
and exists (
select REVSITES.SITEID
from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(REVENUESPLIT.ID) REVSITES
inner join @R68SITES R68SITES
on (R68SITES.SITEID = REVSITES.SITEID) or (R68SITES.SITEID is null and REVSITES.SITEID is null)
/* where clause is #SITEEXTENTION code*/
where (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1)
or exists (
select 1
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, '5F83E4C6-7CAD-4b2c-95A6-506BB18130AB', 4) userSites
where userSites.SITEID=[REVSITES].[SITEID]
or (userSites.SITEID is null and [REVSITES].[SITEID] is null)
)
)
end
-- process report summary values (MaxDate, TotalGiftAmount, TotalClaimAmount)
declare DATA_CURSOR cursor LOCAL FAST_FORWARD READ_ONLY for
select
CONSTITUENTID,
ORIGINALTAXCLAIMNUMBER
from dbo.R68REFUNDDETAIL
where R68ID = @R68ID
and coalesce(TOTALGIFTAMOUNTFROMORIGINALCLAIM, 0) = 0;
declare @MAXREVENUEDATE datetime;
declare @TOTALAMOUNT money;
declare @TOTALAMOUNT_REFUNDED money;
declare @TOTALCLAIM money;
declare @TOTALCLAIM_REFUNDED money;
declare @CONSTITUENTID uniqueidentifier;
declare @TAXCLAIMNUMBER nvarchar(10);
open DATA_CURSOR;
fetch next from DATA_CURSOR into @CONSTITUENTID, @TAXCLAIMNUMBER;
while @@FETCH_STATUS = 0
begin
select
@MAXREVENUEDATE = max(REVENUE.DATE),
@TOTALAMOUNT = coalesce(sum(REVENUESPLIT.AMOUNT), 0),
@TOTALCLAIM = coalesce(sum(REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT), 0)
from dbo.REVENUE
inner join dbo.REVENUESPLIT
on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUESPLITGIFTAID
on REVENUESPLIT.ID = REVENUESPLITGIFTAID.ID
where REVENUE.CONSTITUENTID = @CONSTITUENTID
and REVENUESPLITGIFTAID.TAXCLAIMNUMBER = @TAXCLAIMNUMBER;
-- include refunds in total gift and claim amounts
select
@TOTALAMOUNT_REFUNDED = coalesce(sum(REVENUESPLITGIFTAIDREFUND.ORIGINALGIFTAMOUNT), 0),
@TOTALCLAIM_REFUNDED = coalesce(sum(REVENUESPLITGIFTAIDREFUND.ORIGINALBASETAXCLAIMAMOUNT), 0)
from dbo.REVENUE
inner join dbo.REVENUESPLIT
on REVENUE.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUESPLITGIFTAIDREFUND
on REVENUESPLIT.ID = REVENUESPLITGIFTAIDREFUND.REVENUESPLITID
where REVENUE.CONSTITUENTID = @CONSTITUENTID
and REVENUESPLITGIFTAIDREFUND.ORIGINALTAXCLAIMNUMBER = @TAXCLAIMNUMBER;
update dbo.R68REFUNDDETAIL set
MAXGIFTDATEFROMORIGINALCLAIM = @MAXREVENUEDATE,
TOTALGIFTAMOUNTFROMORIGINALCLAIM = @TOTALAMOUNT + @TOTALAMOUNT_REFUNDED,
TOTALTAXCLAIMAMOUNTFROMORIGINALCLAIM = @TOTALCLAIM + @TOTALCLAIM_REFUNDED
where R68ID = @R68ID
and CONSTITUENTID = @CONSTITUENTID
and ORIGINALTAXCLAIMNUMBER = @TAXCLAIMNUMBER;
fetch next from DATA_CURSOR into @CONSTITUENTID, @TAXCLAIMNUMBER;
end
close DATA_CURSOR;
deallocate DATA_CURSOR;
-- if not previewing the report, merge new refunds into refund table
-- merging because you may have to update if an application was refunded, re-claimed, and refunded again...
if @PREVIEWONLY = 0
begin
merge dbo.REVENUESPLITGIFTAIDREFUND as refunds
using (select * from dbo.R68REFUNDDETAIL where R68ID = @R68ID) as newRefunds
on refunds.REVENUESPLITID = newRefunds.REVENUESPLITID and refunds.REFUNDTAXCLAIMNUMBER = ''
when matched then update
set refunds.ORIGINALTAXCLAIMNUMBER = newRefunds.ORIGINALTAXCLAIMNUMBER,
refunds.ORIGINALGIFTAMOUNT = newRefunds.ORIGINALGIFTAMOUNT,
refunds.ORIGINALBASETAXCLAIMAMOUNT = newRefunds.ORIGINALBASETAXCLAIMAMOUNT,
refunds.ORIGINALTRANSITIONALTAXCLAIMAMOUNT = newRefunds.ORIGINALTRANSITIONALTAXCLAIMAMOUNT,
refunds.INCLUDETRANSITIONALAMOUNTCODE = newRefunds.INCLUDETRANSITIONALAMOUNTCODE,
refunds.REFUNDSOURCECODE = newRefunds.REFUNDSOURCECODE,
refunds.REFUNDTAXCLAIMNUMBER = @REFUNDTAXCLAIMNUMBER,
refunds.DATEREFUNDED = @CHANGEDATE,
refunds.CHANGEDBYID = @CHANGEAGENTID,
refunds.DATECHANGED = @CHANGEDATE
when not matched by target then
insert (ID, REVENUESPLITID, ORIGINALTAXCLAIMNUMBER, ORIGINALGIFTAMOUNT, ORIGINALBASETAXCLAIMAMOUNT, ORIGINALTRANSITIONALTAXCLAIMAMOUNT, INCLUDETRANSITIONALAMOUNTCODE, REFUNDTAXCLAIMNUMBER, DATEREFUNDED, REFUNDSOURCECODE, R68ID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
values (newID(), newRefunds.REVENUESPLITID, newRefunds.ORIGINALTAXCLAIMNUMBER, newRefunds.ORIGINALGIFTAMOUNT, newRefunds.ORIGINALBASETAXCLAIMAMOUNT, newRefunds.ORIGINALTRANSITIONALTAXCLAIMAMOUNT, newRefunds.INCLUDETRANSITIONALAMOUNTCODE, @REFUNDTAXCLAIMNUMBER, @CHANGEDATE, newRefunds.REFUNDSOURCECODE, newRefunds.R68ID, @CHANGEAGENTID, @CHANGEAGENTID, @CHANGEDATE, @CHANGEDATE);
-- turn off nocount, so the #records updated can be returned
set nocount off;
-- remove tax claim number from original applications
update dbo.REVENUESPLITGIFTAID set
TAXCLAIMNUMBER = '',
INCLUDETRANSITIONALAMOUNTCODE = 0,
CHANGEDBYID = @CHANGEAGENTID,
DATECHANGED = @CHANGEDATE
where ID in (
select REVENUESPLITID
from dbo.R68REFUNDDETAIL
where R68ID = @R68ID
);
end
end try
begin catch
exec dbo.USP_RAISE_ERROR
return 1
end catch
return 0;
end