USP_MANAGEGIFTAIDFORSPLITS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @ORGANIZATIONCURRENCYID | uniqueidentifier | IN | |
| @CHANGEAGENTID | uniqueidentifier | IN | |
| @CURRENTDATE | datetime | IN | |
| @DATE | datetime | IN | |
| @SPLITSDECLININGGIFTAIDTBLXML | xml | IN | |
| @GIFTAIDSPONSORSHIPSPLITSTBLXML | xml | IN | |
| @DELETEDANDCHANGEDSPLITSINFOXML | xml | IN |
Definition
Copy
create procedure USP_MANAGEGIFTAIDFORSPLITS
(
@REVENUEID uniqueidentifier,
@ORGANIZATIONCURRENCYID uniqueidentifier,
@CHANGEAGENTID uniqueidentifier,
@CURRENTDATE datetime,
@DATE datetime,
@SPLITSDECLININGGIFTAIDTBLXML xml,
@GIFTAIDSPONSORSHIPSPLITSTBLXML xml,
@DELETEDANDCHANGEDSPLITSINFOXML xml
)
as
--Gift Aid Code pulled from USP_REVENUE_UPDATEREVENUESTREAMS so that it can be reused.
--Gift Aid is for UK only
declare @PRODUCTISUK bit = dbo.UFN_INSTALLEDPRODUCTS_PRODUCTIS('9568A6C2-F7AA-45fd-8F54-21FE9654EE2D');
declare @GBPCURRENCYID uniqueidentifier = (select CURRENCY.ID from dbo.CURRENCY where CURRENCY.ISO4217 = 'GBP');
if @PRODUCTISUK = 1
begin
declare @SPLITSDECLININGGIFTAIDTBL table (REVENUESPLITID uniqueidentifier);
insert into @SPLITSDECLININGGIFTAIDTBL (REVENUESPLITID)
select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier')
from @SPLITSDECLININGGIFTAIDTBLXML.nodes('/SPLITSDECLININGGIFTAID/ITEM') T(c);
declare @GIFTAIDSPONSORSHIPSPLITSTBL table (REVENUESPLITID uniqueidentifier);
insert into @GIFTAIDSPONSORSHIPSPLITSTBL (REVENUESPLITID)
select T.c.value('(REVENUESPLITID)[1]','uniqueidentifier')
from @GIFTAIDSPONSORSHIPSPLITSTBLXML.nodes('/GIFTAIDSPONSORSHIPSPLITS/ITEM') T(c);
declare @DELETEDANDCHANGEDSPLITSINFO table ( REVENUESPLITID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
TAXCLAIMNUMBER nvarchar(10),
CHARITYCLAIMREFERENCENUMBER nvarchar(20),
GIFTDATE datetime,
GIFTAMOUNT money,
BASETAXCLAIMAMOUNT money,
TRANSITIONALTAXCLAIMAMOUNT money,
INCLUDETRANSITIONALAMOUNTCODE tinyint,
CLAIMEDASSPONSORSHIP bit,
TRANSACTIONBASETAXCLAIMAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT money,
ORGANIZATIONBASETAXCLAIMAMOUNT money,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT money,
BASECURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
TRANSACTIONGIFTAMOUNT money,
ORGANIZATIONGIFTAMOUNT money,
ISSPONSORSHIP bit,
ISDELETED bit,
BASERATE numeric(30,6),
TRANSITIONALRATE numeric(30,6),
AMOUNTTOSUM money)
insert into @DELETEDANDCHANGEDSPLITSINFO(REVENUESPLITID,
CONSTITUENTID,
TAXCLAIMNUMBER,
CHARITYCLAIMREFERENCENUMBER,
GIFTDATE,
GIFTAMOUNT,
BASETAXCLAIMAMOUNT,
TRANSITIONALTAXCLAIMAMOUNT,
INCLUDETRANSITIONALAMOUNTCODE,
CLAIMEDASSPONSORSHIP,
TRANSACTIONBASETAXCLAIMAMOUNT,
TRANSACTIONCURRENCYID,
TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
ORGANIZATIONBASETAXCLAIMAMOUNT,
ORGANIZATIONEXCHANGERATEID,
ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
BASECURRENCYID,
BASEEXCHANGERATEID,
TRANSACTIONGIFTAMOUNT,
ORGANIZATIONGIFTAMOUNT,
ISSPONSORSHIP,
ISDELETED,
BASERATE,
TRANSITIONALRATE,
AMOUNTTOSUM)
select T.c.value('(REVENUESPLITID)[1]', 'uniqueidentifier') as REVENUESPLITID,
T.c.value('(CONSTITUENTID)[1]', 'uniqueidentifier') as CONSTITUENTID,
T.c.value('(TAXCLAIMNUMBER)[1]', 'nvarchar(10)') as TAXCLAIMNUMBER,
T.c.value('(CHARITYCLAIMREFERENCENUMBER)[1]', 'nvarchar(20)') as CHARITYCLAIMREFERENCENUMBER,
T.c.value('(GIFTDATE)[1]', 'datetime') as GIFTDATE,
T.c.value('(GIFTAMOUNT)[1]', 'money') as GIFTAMOUNT,
T.c.value('(BASETAXCLAIMAMOUNT)[1]', 'money') as BASETAXCLAIMAMOUNT,
T.c.value('(TRANSITIONALTAXCLAIMAMOUNT)[1]', 'money') as TRANSITIONALTAXCLAIMAMOUNT,
T.c.value('(INCLUDETRANSITIONALAMOUNTCODE)[1]', 'tinyint') as INCLUDETRANSITIONALAMOUNTCODE,
T.c.value('(CLAIMEDASSPONSORSHIP)[1]', 'bit') as CLAIMEDASSPONSORSHIP,
T.c.value('(TRANSACTIONBASETAXCLAIMAMOUNT)[1]', 'money') as TRANSACTIONBASETAXCLAIMAMOUNT,
T.c.value('(TRANSACTIONCURRENCYID)[1]', 'uniqueidentifier') as TRANSACTIONCURRENCYID,
T.c.value('(TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT)[1]', 'money') as TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
T.c.value('(ORGANIZATIONBASETAXCLAIMAMOUNT)[1]', 'money') as ORGANIZATIONBASETAXCLAIMAMOUNT,
T.c.value('(ORGANIZATIONEXCHANGERATEID)[1]', 'uniqueidentifier') as ORGANIZATIONEXCHANGERATEID,
T.c.value('(ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT)[1]', 'money') as ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
T.c.value('(BASECURRENCYID)[1]', 'uniqueidentifier') as BASECURRENCYID,
T.c.value('(BASEEXCHANGERATEID)[1]', 'uniqueidentifier') as BASEEXCHANGERATEID,
T.c.value('(TRANSACTIONGIFTAMOUNT)[1]', 'money') as TRANSACTIONGIFTAMOUNT,
T.c.value('(ORGANIZATIONGIFTAMOUNT)[1]', 'money') as ORGANIZATIONGIFTAMOUNT,
T.c.value('(ISSPONSORSHIP)[1]', 'bit') as ISSPONSORSHIP,
T.c.value('(ISDELETED)[1]', 'bit') as ISDELETED,
T.c.value('(BASERATE)[1]', 'numeric(30,6)') as BASERATE,
T.c.value('(TRANSITIONALRATE)[1]', 'numeric(30,6)') as TRANSITIONALRATE,
T.c.value('(AMOUNTTOSUM)[1]', 'money') as AMOUNTTOSUM
from @DELETEDANDCHANGEDSPLITSINFOXML.nodes('/DELETEDANDCHANGEDSPLITSINFO/ITEM') T(c);
--New splits that aren't declined. Not including CCRN until the actual line item is created.
declare @NEWGIFTAIDSPLITS table (NEWREVENUESPLITID uniqueidentifier,
AMOUNT money,
CHARITYCLAIMREFERENCENUMBER nvarchar(20),
ISSPONSORSHIP bit,
TRANSACTIONAMOUNT money
);
insert into @NEWGIFTAIDSPLITS (NEWREVENUESPLITID,AMOUNT,CHARITYCLAIMREFERENCENUMBER,ISSPONSORSHIP,TRANSACTIONAMOUNT)
select FINANCIALTRANSACTIONLINEITEM.ID,
case when @ORGANIZATIONCURRENCYID = @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.ORGAMOUNT
when FINANCIALTRANSACTION.TRANSACTIONCURRENCYID = @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
when V.BASECURRENCYID = @GBPCURRENCYID then FINANCIALTRANSACTIONLINEITEM.BASEAMOUNT
else 0
end as AMOUNT,
(select CHARITYCLAIMREFERENCENUMBER.REFERENCENUMBER from dbo.CHARITYCLAIMREFERENCENUMBER
where ID = (dbo.UFN_CHARITYCLAIMREFERENCENUMBER_GETBYSITE((select top 1 SITEID from dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(FINANCIALTRANSACTIONLINEITEM.ID))))),
0,
FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT
from dbo.FINANCIALTRANSACTIONLINEITEM
inner join dbo.FINANCIALTRANSACTION on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on FINANCIALTRANSACTION.ID = V.FINANCIALTRANSACTIONID
where FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = @REVENUEID
and FINANCIALTRANSACTIONLINEITEM.ID not in (select REVENUESPLITID from @SPLITSDECLININGGIFTAIDTBL)
and FINANCIALTRANSACTIONLINEITEM.DELETEDON is null
and FINANCIALTRANSACTIONLINEITEM.TYPECODE not in (1,8)
and dbo.UFN_GIFTAIDSTATUS_GETBASEDONRULES(FINANCIALTRANSACTIONLINEITEM.ID) = 1; --Qualified
update @NEWGIFTAIDSPLITS
set NEWGIFTAIDSPLITS.ISSPONSORSHIP = 1
from @NEWGIFTAIDSPLITS NEWGIFTAIDSPLITS
inner join @GIFTAIDSPONSORSHIPSPLITSTBL GIFTAIDSPONSORSHIPSPLITSTBL on GIFTAIDSPONSORSHIPSPLITSTBL.REVENUESPLITID = NEWGIFTAIDSPLITS.NEWREVENUESPLITID;
declare @SPLITSTOUPDATE table (NEWREVENUESPLITID uniqueidentifier,
TAXCLAIMNUMBER nvarchar(10),
CHARITYCLAIMREFERENCENUMBER nvarchar(20),
OLDREVENUESPLITID uniqueidentifier,
ISDELETED bit,
OLDBASERATE numeric(30,6),
OLDTRANSITIONALRATE numeric(30,6)
);
--Build a old and new splits table and include the CCRN, group by CCRN,sum(transactionamount) and ISSPONSORSHIP. For any CCRNS where the sum's/sponsorships
--match we should copy the TAXCLAIMNUMBER of the old split to the new split and not create a refund.
--This will prevent the R68 process from picking these new splits up or refunding them when there is no reason as it is still the same amount and CCRN.
with OLDSPLITSCTE as
(
select sum(DELETEDANDCHANGEDSPLITSINFO.AMOUNTTOSUM) as AMOUNTSUM,
DELETEDANDCHANGEDSPLITSINFO.CHARITYCLAIMREFERENCENUMBER,
DELETEDANDCHANGEDSPLITSINFO.ISSPONSORSHIP
from @DELETEDANDCHANGEDSPLITSINFO as DELETEDANDCHANGEDSPLITSINFO
group by DELETEDANDCHANGEDSPLITSINFO.CHARITYCLAIMREFERENCENUMBER,DELETEDANDCHANGEDSPLITSINFO.ISSPONSORSHIP
)
,NEWSPLITSCTE as
(
select sum(NEWGIFTAIDSPLITS.AMOUNT) as AMOUNTSUM,
NEWGIFTAIDSPLITS.CHARITYCLAIMREFERENCENUMBER,
NEWGIFTAIDSPLITS.ISSPONSORSHIP
from @NEWGIFTAIDSPLITS as NEWGIFTAIDSPLITS
group by NEWGIFTAIDSPLITS.CHARITYCLAIMREFERENCENUMBER,NEWGIFTAIDSPLITS.ISSPONSORSHIP
)
insert into @SPLITSTOUPDATE
select NEWGIFTAIDSPLITS.NEWREVENUESPLITID,
DELETEDANDCHANGEDSPLITSINFO.TAXCLAIMNUMBER,
CCRN.CHARITYCLAIMREFERENCENUMBER,
DELETEDANDCHANGEDSPLITSINFO.REVENUESPLITID,
DELETEDANDCHANGEDSPLITSINFO.ISDELETED,
DELETEDANDCHANGEDSPLITSINFO.BASERATE,
DELETEDANDCHANGEDSPLITSINFO.TRANSITIONALRATE
from (
select
OLDSPLITSCTE.CHARITYCLAIMREFERENCENUMBER,
OLDSPLITSCTE.ISSPONSORSHIP
from OLDSPLITSCTE,NEWSPLITSCTE
where OLDSPLITSCTE.AMOUNTSUM = NEWSPLITSCTE.AMOUNTSUM
and OLDSPLITSCTE.CHARITYCLAIMREFERENCENUMBER = NEWSPLITSCTE.CHARITYCLAIMREFERENCENUMBER
and NEWSPLITSCTE.ISSPONSORSHIP = OLDSPLITSCTE.ISSPONSORSHIP) as CCRN
inner join @NEWGIFTAIDSPLITS NEWGIFTAIDSPLITS on NEWGIFTAIDSPLITS.CHARITYCLAIMREFERENCENUMBER = CCRN.CHARITYCLAIMREFERENCENUMBER and NEWGIFTAIDSPLITS.ISSPONSORSHIP = CCRN.ISSPONSORSHIP
inner join @DELETEDANDCHANGEDSPLITSINFO DELETEDANDCHANGEDSPLITSINFO on DELETEDANDCHANGEDSPLITSINFO.CHARITYCLAIMREFERENCENUMBER = CCRN.CHARITYCLAIMREFERENCENUMBER and DELETEDANDCHANGEDSPLITSINFO.ISSPONSORSHIP = CCRN.ISSPONSORSHIP
where dbo.UFN_GIFTAIDSTATUS_GETBASEDONRULES(NEWGIFTAIDSPLITS.NEWREVENUESPLITID) = 1; --Qualified
--Generate Gift Aid refunds for splits that were claimed on the R68 report and are now being deleted
--and not being re-claimed by a same amount/CCRN split(s).
--If the split existed before and still does but under a different CCRN, don't create the refund record.
insert into dbo.REVENUESPLITGIFTAIDREFUND
(
ID,
CONSTITUENTID,
REVENUESPLITID,
ORIGINALTAXCLAIMNUMBER,
ORIGINALCHARITYCLAIMREFERENCENUMBER,
ORIGINALGIFTDATE,
ORIGINALGIFTAMOUNT,
ORIGINALBASETAXCLAIMAMOUNT,
ORIGINALTRANSITIONALTAXCLAIMAMOUNT,
INCLUDETRANSITIONALAMOUNTCODE,
DATEREFUNDED,
REFUNDSOURCECODE,
ADDEDBYID,
CHANGEDBYID,
DATEADDED,
DATECHANGED,
ORIGINALCLAIMEDASSPONSORSHIP,
ORIGINALTRANSACTIONBASETAXCLAIMAMOUNT,
ORIGINALTRANSACTIONCURRENCYID,
ORIGINALTRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
ORIGINALORGANIZATIONBASETAXCLAIMAMOUNT,
ORIGINALORGANIZATIONEXCHANGERATEID,
ORIGINALORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
ORIGINALBASECURRENCYID,
ORIGINALBASEEXCHANGERATEID,
ORIGINALTRANSACTIONGIFTAMOUNT,
ORIGINALORGANIZATIONGIFTAMOUNT
)
select
newID(),
DELETEDANDCHANGEDSPLITSINFO.CONSTITUENTID,
DELETEDANDCHANGEDSPLITSINFO.REVENUESPLITID,
DELETEDANDCHANGEDSPLITSINFO.TAXCLAIMNUMBER,
DELETEDANDCHANGEDSPLITSINFO.CHARITYCLAIMREFERENCENUMBER,
cast(DELETEDANDCHANGEDSPLITSINFO.GIFTDATE as datetime) as DATE,
DELETEDANDCHANGEDSPLITSINFO.GIFTAMOUNT,
DELETEDANDCHANGEDSPLITSINFO.BASETAXCLAIMAMOUNT,
DELETEDANDCHANGEDSPLITSINFO.TRANSITIONALTAXCLAIMAMOUNT,
DELETEDANDCHANGEDSPLITSINFO.INCLUDETRANSITIONALAMOUNTCODE,
@CURRENTDATE,
case when DELETEDANDCHANGEDSPLITSINFO.ISDELETED = 1 then 3 --Application deleted
else 2 -- Application amount changed
end,
@CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE,
DELETEDANDCHANGEDSPLITSINFO.CLAIMEDASSPONSORSHIP,
DELETEDANDCHANGEDSPLITSINFO.TRANSACTIONBASETAXCLAIMAMOUNT,
DELETEDANDCHANGEDSPLITSINFO.TRANSACTIONCURRENCYID,
DELETEDANDCHANGEDSPLITSINFO.TRANSACTIONTRANSITIONALTAXCLAIMAMOUNT,
DELETEDANDCHANGEDSPLITSINFO.ORGANIZATIONBASETAXCLAIMAMOUNT,
DELETEDANDCHANGEDSPLITSINFO.ORGANIZATIONEXCHANGERATEID,
DELETEDANDCHANGEDSPLITSINFO.ORGANIZATIONTRANSITIONALTAXCLAIMAMOUNT,
DELETEDANDCHANGEDSPLITSINFO.BASECURRENCYID,
DELETEDANDCHANGEDSPLITSINFO.BASEEXCHANGERATEID,
DELETEDANDCHANGEDSPLITSINFO.TRANSACTIONGIFTAMOUNT,
DELETEDANDCHANGEDSPLITSINFO.ORGANIZATIONGIFTAMOUNT
from @DELETEDANDCHANGEDSPLITSINFO DELETEDANDCHANGEDSPLITSINFO
left join @NEWGIFTAIDSPLITS NEWGIFTAIDSPLITS on NEWGIFTAIDSPLITS.NEWREVENUESPLITID = DELETEDANDCHANGEDSPLITSINFO.REVENUESPLITID
where DELETEDANDCHANGEDSPLITSINFO.REVENUESPLITID not in (select OLDREVENUESPLITID from @SPLITSTOUPDATE)
and( (NEWGIFTAIDSPLITS.NEWREVENUESPLITID is null) or (NEWGIFTAIDSPLITS.TRANSACTIONAMOUNT <> DELETEDANDCHANGEDSPLITSINFO.TRANSACTIONGIFTAMOUNT and DELETEDANDCHANGEDSPLITSINFO.ISDELETED = 0))
and not exists (select REVENUESPLITID from dbo.REVENUESPLITGIFTAIDREFUND where REVENUESPLITGIFTAIDREFUND.REFUNDTAXCLAIMNUMBER = ''
and REVENUESPLITGIFTAIDREFUND.REVENUESPLITID = DELETEDANDCHANGEDSPLITSINFO.REVENUESPLITID);
--Handle refund creation for non-deleted, amount changed splits.
--Pulled from USP_REVENUESPLIT_CREATEREFUND
update dbo.REVENUESPLITGIFTAID set
TAXCLAIMNUMBER = '',
CHARITYCLAIMREFERENCENUMBER = '',
INCLUDETRANSITIONALAMOUNTCODE = 0
from dbo.REVENUESPLITGIFTAID
inner join @DELETEDANDCHANGEDSPLITSINFO DELETEDANDCHANGEDSPLITSINFO on DELETEDANDCHANGEDSPLITSINFO.REVENUESPLITID = REVENUESPLITGIFTAID.ID
left join @NEWGIFTAIDSPLITS NEWGIFTAIDSPLITS on NEWGIFTAIDSPLITS.NEWREVENUESPLITID = DELETEDANDCHANGEDSPLITSINFO.REVENUESPLITID
where DELETEDANDCHANGEDSPLITSINFO.REVENUESPLITID not in (select OLDREVENUESPLITID from @SPLITSTOUPDATE)
and DELETEDANDCHANGEDSPLITSINFO.ISDELETED = 0
and (NEWGIFTAIDSPLITS.NEWREVENUESPLITID is not null and (NEWGIFTAIDSPLITS.TRANSACTIONAMOUNT <> DELETEDANDCHANGEDSPLITSINFO.TRANSACTIONGIFTAMOUNT));
--Passing in null for APPEALID,PAYMENTMETHODCODE and CREDITTYPECODEID. The USP never uses them.
--Passing in @SPLITSTOTRANSFERGIFTAID to include additional information about splits that don't need to be re-claimed because
--there already exists a claim for the same amount/ccrn on the revenue.
declare @SPLITSTOTRANSFERGIFTAID xml = (select NEWREVENUESPLITID as REVENUESPLITID,
TAXCLAIMNUMBER,
CHARITYCLAIMREFERENCENUMBER,
OLDBASERATE as BASERATE,
OLDTRANSITIONALRATE as TRANSITIONALRATE
from @SPLITSTOUPDATE SPLITSTOUPDATE
for xml raw('ITEM'), type, elements, root('SPLITSTOTRANSFERGIFTAID'), binary base64);
exec dbo.USP_GIFTAIDREVENUESPLIT_ADDUPDATESPLITS @ID=@REVENUEID,
@APPEALID=null,
@PAYMENTMETHODCODE =null,
@CREDITTYPECODEID = null,
@CHANGEAGENTID=@CHANGEAGENTID,
@DATE=@DATE,
@TRANSACTIONTYPECODE = 0, --revenue transaction type code of payment is 0
@SPLITSDECLININGGIFTAID = @SPLITSDECLININGGIFTAIDTBLXML,
@COVENANTGIFTSPLITS = null,
@GIFTAIDSPONSORSHIPSPLITS=@GIFTAIDSPONSORSHIPSPLITSTBLXML,
@SPLITSTOTRANSFERGIFTAID=@SPLITSTOTRANSFERGIFTAID;
end