USP_REVENUESPLITGIFTAIDAMOUNTS_REFRESH
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @IDSETREGISTERID | uniqueidentifier | IN | |
| @RECORDCOUNT | int | INOUT |
Definition
Copy
CREATE procedure dbo.USP_REVENUESPLITGIFTAIDAMOUNTS_REFRESH
(
@IDSETREGISTERID uniqueidentifier = null,
@RECORDCOUNT int = 0 output
)
as
begin
set nocount on;
begin try
set @RECORDCOUNT = 0;
declare @CURRENTDATE datetime = getdate();
declare @SELECTION nvarchar(250) = '';
if @IDSETREGISTERID is not null
begin
set @SELECTION = N'inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) REVENUESELECTION on REVENUE.ID = REVENUESELECTION.ID';
end
declare @SQL nvarchar(max);
--inserts
set @SQL = N'insert into dbo.REVENUESPLITGIFTAIDAMOUNTS
(
ID,
ELIGIBILITYCODE,
DECLINESGIFTAID,
ISSPONSORSHIP,
TAXCLAIMNUMBER,
QUALIFICATIONSTATUS,
TAXCLAIMAMOUNT,
TRANSACTIONTAXCLAIMAMOUNT,
ORGANIZATIONTAXCLAIMAMOUNT,
CLAIMABLEGIFTAIDAMOUNT,
TRANSACTIONCLAIMABLEGIFTAIDAMOUNT,
ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT,
RECEIVEDGIFTAIDAMOUNT,
TRANSACTIONRECEIVEDGIFTAIDAMOUNT,
ORGANIZATIONRECEIVEDGIFTAIDAMOUNT,
GROSSAMOUNT,
TRANSACTIONGROSSAMOUNT,
ORGANIZATIONGROSSAMOUNT,
POTENTIALGROSSAMOUNT,
TRANSACTIONPOTENTIALGROSSAMOUNT,
ORGANIZATIONPOTENTIALGROSSAMOUNT,
ORGANIZATIONEXCHANGERATEID,
BASEEXCHANGERATEID,
BASECURRENCYID,
TRANSACTIONCURRENCYID,
REFRESHDATE,
REVENUESPLITGIFTAIDDATEADDED,
REVENUESPLITGIFTAIDDATECHANGED,
REVENUESPLITGIFTAIDADDEDBYUSERNAME,
REVENUESPLITGIFTAIDCHANGEDBYUSERNAME,
REVENUESPLITGIFTAIDTS,
FINANCIALTRANSACTIONID,
REVENUEORGANIZATIONEXCHANGERATEID,
REVENUEBASEEXCHANGERATEID,
REVENUETRANSACTIONCURRENCYID,
REVENUEBASECURRENCYID,
REVENUEDATEADDED,
REVENUEDATECHANGED,
REVENUEADDEDBYUSERNAME,
REVENUECHANGEDBYUSERNAME,
REVENUETS
)
select
REVENUESPLIT.ID,
coalesce(REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY,0),
coalesce(REVENUESPLITGIFTAID.DECLINESGIFTAID,0),
coalesce(REVENUESPLITGIFTAID.ISSPONSORSHIP,0),
coalesce(REVENUESPLITGIFTAID.TAXCLAIMNUMBER,''''),
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1 then ''Not qualified''
else ''Qualified for Gift Aid''
end as [QUALIFICATIONSTATUS],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then 0
when REVENUE.TYPECODE = 0
then coalesce(REVENUESPLITGIFTAID.TAXCLAIMAMOUNT,0)
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else 0
end as [TAXCLAIMAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then 0
when REVENUE.TYPECODE = 0
then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else 0
end as [TRANSACTIONTAXCLAIMAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then 0
when REVENUE.TYPECODE = 0
then REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else 0
end as [ORGANIZATIONTAXCLAIMAMOUNT],
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
REVENUESPLITGIFTAID.TAXCLAIMNUMBER = '''' and -- Ensure the split hasn''t already been received
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
else 0
end as CLAIMABLEGIFTAIDAMOUNT,
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
REVENUESPLITGIFTAID.TAXCLAIMNUMBER = '''' and -- Ensure the split hasn''t already been received
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
else 0
end as TRANSACTIONCLAIMABLEGIFTAIDAMOUNT,
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
REVENUESPLITGIFTAID.TAXCLAIMNUMBER = '''' and -- Ensure the split hasn''t already been received
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
else 0
end as ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT,
case
when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '''' then REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
else 0
end as RECEIVEDGIFTAIDAMOUNT,
case
when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '''' then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
else 0
end as TRANSACTIONRECEIVEDGIFTAIDAMOUNT,
case
when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '''' then REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
else 0
end as ORGANIZATIONRECEIVEDGIFTAIDAMOUNT,
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null
or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4)
or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1
or REVENUESPLITGIFTAID.RULES_STATUS = 0
or REVENUESPLITGIFTAID.ATTRIBUTES_STATUS = 0
or CONSTITUENT.ISGROUP = 1
or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.BASEAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.BASEAMOUNT + case when REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
when RELIEFNOTEXPIRED.ID is not null then BASETAXCLAIMAMOUNT + TRANSITIONALTAXCLAIMAMOUNT
else coalesce(BASETAXCLAIMAMOUNT,0)
end
when REVENUE.TYPECODE = 1
then REVENUESPLIT.BASEAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.BASEAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else REVENUESPLIT.BASEAMOUNT
end as [GROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4) or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.TRANSACTIONAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.TRANSACTIONAMOUNT + REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.TRANSACTIONAMOUNT
end as [TRANSACTIONGROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4) or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.ORGAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.ORGAMOUNT + REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.ORGAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.ORGAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.ORGAMOUNT
end as [ORGANIZATIONGROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.BASEAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.BASEAMOUNT + REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.BASEAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.BASEAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else REVENUESPLIT.BASEAMOUNT
end as [POTENTIALGROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.TRANSACTIONAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.TRANSACTIONAMOUNT + REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.TRANSACTIONAMOUNT
end as [TRANSACTIONPOTENTIALGROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.ORGAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.ORGAMOUNT + REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.ORGAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.ORGAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.ORGAMOUNT
end as [ORGANIZATIONPOTENTIALGROSSAMOUNT],
coalesce(REVENUESPLITGIFTAID.ORGANIZATIONEXCHANGERATEID,REVENUE.ORGEXCHANGERATEID) as ORGANIZATIONEXCHANGERATEID,
coalesce(REVENUESPLITGIFTAID.BASEEXCHANGERATEID,REVENUE.BASEEXCHANGERATEID) as BASEEXCHANGERATEID,
coalesce(REVENUESPLITGIFTAID.BASECURRENCYID,REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as BASECURRENCYID,
coalesce(REVENUESPLITGIFTAID.TRANSACTIONCURRENCYID,REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCYID,
@CURRENTDATEPARAMETER,
REVENUESPLITGIFTAID.DATEADDED,
REVENUESPLITGIFTAID.DATECHANGED,
coalesce([ADDEDBY].USERNAME,''''),
coalesce([CHANGEDBY].USERNAME,''''),
(select max(v) from (values (REVENUESPLIT.TS), (REVENUE.TS), (REVENUESPLITGIFTAID.TS)) as value(v)),
REVENUESPLIT.FINANCIALTRANSACTIONID,
REVENUE.ORGEXCHANGERATEID,
REVENUE.BASEEXCHANGERATEID,
REVENUE.TRANSACTIONCURRENCYID,
isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as BASECURRENCYID,
REVENUE.DATEADDED,
REVENUE.DATECHANGED,
coalesce(REVENUEADDEDBY.USERNAME,''''),
coalesce(REVENUECHANGEDBY.USERNAME,''''),
REVENUE.TS
from
dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
left join dbo.REVENUESPLITGIFTAIDAMOUNTS on REVENUESPLIT.ID = REVENUESPLITGIFTAIDAMOUNTS.ID
left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE with (nolock) on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID ' +
@SELECTION + N'
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
outer apply dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS_INLINE_2(REVENUESPLIT.ID) as REVENUESPLITGIFTAIDELIGIBILITY
left join dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT_ALLCURRENCYTYPES() as PLEDGETAXCLAIMAMOUNT on REVENUESPLIT.ID = PLEDGETAXCLAIMAMOUNT.REVENUESPLITID
left join dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATERECURRINGGIFTTAXCLAIMAMOUNT_INLINE() as RECURRINGGIFTTAXCLAIMAMOUNT on REVENUESPLIT.ID = RECURRINGGIFTTAXCLAIMAMOUNT.REVENUESPLITID
left join dbo.UFN_REVENUESPLITGIFTAID_GETTRANSITIONALRELIEFNOTEXPIRED() as RELIEFNOTEXPIRED on RELIEFNOTEXPIRED.ID = REVENUESPLITGIFTAID.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = REVENUESPLITGIFTAID.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = REVENUESPLITGIFTAID.CHANGEDBYID
left join dbo.CHANGEAGENT as [REVENUEADDEDBY] on REVENUEADDEDBY.ID = REVENUE.ADDEDBYID
left join dbo.CHANGEAGENT as [REVENUECHANGEDBY] on REVENUECHANGEDBY.ID = REVENUE.CHANGEDBYID
where
REVENUE.DELETEDON is null
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1
and REVENUESPLITGIFTAIDAMOUNTS.ID is null
'
exec sp_executesql @SQL, N'@CURRENTDATEPARAMETER datetime, @IDSETREGISTERID uniqueidentifier', @CURRENTDATEPARAMETER = @CURRENTDATE, @IDSETREGISTERID = @IDSETREGISTERID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
--updates
declare @UPDATESQL nvarchar(max);
set @UPDATESQL = N'
;with GIFTAID_CTE as (
select
REVENUESPLIT.ID as ID,
coalesce(REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY,0) as ELIGIBILITY,
coalesce(REVENUESPLITGIFTAID.DECLINESGIFTAID,0) as DECLINESGIFTAID,
coalesce(REVENUESPLITGIFTAID.ISSPONSORSHIP,0) as ISSPONSORSHIP,
coalesce(REVENUESPLITGIFTAID.TAXCLAIMNUMBER,'''') as TAXCLAIMNUMBER,
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1 then ''Not qualified''
else ''Qualified for Gift Aid''
end as [QUALIFICATIONSTATUS],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then 0
when REVENUE.TYPECODE = 0
then coalesce(REVENUESPLITGIFTAID.TAXCLAIMAMOUNT,0)
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else 0
end as [TAXCLAIMAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then 0
when REVENUE.TYPECODE = 0
then coalesce(REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT,0)
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else 0
end as [TRANSACTIONTAXCLAIMAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then 0
when REVENUE.TYPECODE = 0
then coalesce(REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT,0)
when REVENUE.TYPECODE = 1
then coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else 0
end as [ORGANIZATIONTAXCLAIMAMOUNT],
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
REVENUESPLITGIFTAID.TAXCLAIMNUMBER = '''' and
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
else 0
end as CLAIMABLEGIFTAIDAMOUNT,
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
REVENUESPLITGIFTAID.TAXCLAIMNUMBER = '''' and
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
else 0
end as TRANSACTIONCLAIMABLEGIFTAIDAMOUNT,
case
when REVENUE.TYPECODE = 0 and
CONSTITUENT.ISGROUP = 0 and
CONSTITUENT.ISORGANIZATION = 0 and
REVENUESPLITGIFTAID.TAXCLAIMNUMBER = '''' and
REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (0, 1) and
REVENUESPLITGIFTAID.DECLINESGIFTAID = 0 and
RULES_STATUS = 1 and
ATTRIBUTES_STATUS = 1
then REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
else 0
end as ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT,
case
when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '''' then REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
else 0
end as RECEIVEDGIFTAIDAMOUNT,
case
when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '''' then REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
else 0
end as TRANSACTIONRECEIVEDGIFTAIDAMOUNT,
case
when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '''' then REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
else 0
end as ORGANIZATIONRECEIVEDGIFTAIDAMOUNT,
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null
or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4)
or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1
or REVENUESPLITGIFTAID.RULES_STATUS = 0
or REVENUESPLITGIFTAID.ATTRIBUTES_STATUS = 0
or CONSTITUENT.ISGROUP = 1
or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.BASEAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.BASEAMOUNT + case when REVENUESPLITGIFTAID.TRANSITIONALTAXCLAIMAMOUNT = 0 then REVENUESPLITGIFTAID.BASETAXCLAIMAMOUNT
when RELIEFNOTEXPIRED.ID is not null then BASETAXCLAIMAMOUNT + TRANSITIONALTAXCLAIMAMOUNT
else coalesce(BASETAXCLAIMAMOUNT,0)
end
when REVENUE.TYPECODE = 1
then REVENUESPLIT.BASEAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.BASEAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else REVENUESPLIT.BASEAMOUNT
end as [GROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4) or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.TRANSACTIONAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.TRANSACTIONAMOUNT + REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.TRANSACTIONAMOUNT
end as [TRANSACTIONGROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAIDELIGIBILITY.ELIGIBILITY not in (2,3,4) or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.ORGAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.ORGAMOUNT + REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.ORGAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.ORGAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.ORGAMOUNT
end as [ORGANIZATIONGROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.BASEAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.BASEAMOUNT + REVENUESPLITGIFTAID.TAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.BASEAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.BASEAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TAXCLAIMAMOUNT, 0)
else REVENUESPLIT.BASEAMOUNT
end as [POTENTIALGROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.TRANSACTIONAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.TRANSACTIONAMOUNT + REVENUESPLITGIFTAID.TRANSACTIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.TRANSACTIONAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.TRANSACTIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.TRANSACTIONAMOUNT
end as [TRANSACTIONPOTENTIALGROSSAMOUNT],
case
when REVENUESPLITGIFTAID.DECLINESGIFTAID is null or REVENUESPLITGIFTAID.DECLINESGIFTAID = 1 or RULES_STATUS = 0 or ATTRIBUTES_STATUS = 0 or CONSTITUENT.ISGROUP = 1 or CONSTITUENT.ISORGANIZATION = 1
then REVENUESPLIT.ORGAMOUNT
when REVENUE.TYPECODE = 0
then REVENUESPLIT.ORGAMOUNT + REVENUESPLITGIFTAID.ORGANIZATIONTAXCLAIMAMOUNT
when REVENUE.TYPECODE = 1
then REVENUESPLIT.ORGAMOUNT + coalesce(PLEDGETAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
when REVENUE.TYPECODE = 2
then REVENUESPLIT.ORGAMOUNT + coalesce(RECURRINGGIFTTAXCLAIMAMOUNT.ORGANIZATIONTAXCLAIMAMOUNT, 0)
else REVENUESPLIT.ORGAMOUNT
end as [ORGANIZATIONPOTENTIALGROSSAMOUNT],
coalesce(REVENUESPLITGIFTAID.ORGANIZATIONEXCHANGERATEID,REVENUE.ORGEXCHANGERATEID) as ORGANIZATIONEXCHANGERATEID,
coalesce(REVENUESPLITGIFTAID.BASEEXCHANGERATEID,REVENUE.BASEEXCHANGERATEID) as BASEEXCHANGERATEID,
coalesce(REVENUESPLITGIFTAID.BASECURRENCYID,REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as BASECURRENCYID,
coalesce(REVENUESPLITGIFTAID.TRANSACTIONCURRENCYID,REVENUE.TRANSACTIONCURRENCYID) as TRANSACTIONCURRENCYID,
REVENUESPLITGIFTAID.DATEADDED as REVENUESPLITGIFTAIDDATEADDED,
REVENUESPLITGIFTAID.DATECHANGED as REVENUESPLITGIFTAIDDATECHANGED,
coalesce([ADDEDBY].USERNAME,'''') as REVENUESPLITGIFTAIDADDEDBYUSERNAME,
coalesce([CHANGEDBY].USERNAME,'''') as REVENUESPLITGIFTAIDCHANGEDBYUSERNAME,
(select max(v) from (values (REVENUESPLIT.TS), (REVENUE.TS), (REVENUESPLITGIFTAID.TS)) as value(v)) as MAXTS,
(select max(v) from (values (REVENUESPLIT.TSLONG), (REVENUE.TSLONG), (REVENUESPLITGIFTAID.TSLONG)) as value(v)) as MAXTSLONG,
REVENUESPLIT.FINANCIALTRANSACTIONID,
REVENUE.ORGEXCHANGERATEID as REVENUEORGANIZATIONEXCHANGERATEID,
REVENUE.BASEEXCHANGERATEID as REVENUEBASEEXCHANGERATEID,
REVENUE.TRANSACTIONCURRENCYID as REVENUETRANSACTIONCURRENCYID,
isnull(REVENUE_EXT.NONPOSTABLEBASECURRENCYID, V.BASECURRENCYID) as REVENUEBASECURRENCYID,
REVENUE.DATEADDED as REVENUEDATEADDED,
REVENUE.DATECHANGED as REVENUEDATECHANGED,
coalesce(REVENUEADDEDBY.USERNAME,'''') as REVENUEADDEDBYUSERNAME,
coalesce(REVENUECHANGEDBY.USERNAME,'''') as REVENUECHANGEDBYUSERNAME,
REVENUE.TS as REVENUETS
from
dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE with (nolock) on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID ' +
@SELECTION + N'
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
left outer join dbo.V_BASECURRENCYFORFINANCIALTRANSACTION_I V with (noexpand) on REVENUE.ID = V.FINANCIALTRANSACTIONID
outer apply dbo.UFN_GIFTAIDREVENUESPLIT_GETTAXCLAIMELIGIBILITYSTATUS_INLINE_2(REVENUESPLIT.ID) as REVENUESPLITGIFTAIDELIGIBILITY
left join dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATEPLEDGETAXCLAIMAMOUNT_ALLCURRENCYTYPES() as PLEDGETAXCLAIMAMOUNT on REVENUESPLIT.ID = PLEDGETAXCLAIMAMOUNT.REVENUESPLITID
left join dbo.UFN_GIFTAIDREVENUESPLIT_CALCULATERECURRINGGIFTTAXCLAIMAMOUNT_INLINE() as RECURRINGGIFTTAXCLAIMAMOUNT on REVENUESPLIT.ID = RECURRINGGIFTTAXCLAIMAMOUNT.REVENUESPLITID
left join dbo.UFN_REVENUESPLITGIFTAID_GETTRANSITIONALRELIEFNOTEXPIRED() as RELIEFNOTEXPIRED on RELIEFNOTEXPIRED.ID = REVENUESPLITGIFTAID.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = REVENUESPLITGIFTAID.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = REVENUESPLITGIFTAID.CHANGEDBYID
left join dbo.CHANGEAGENT as [REVENUEADDEDBY] on REVENUEADDEDBY.ID = REVENUE.ADDEDBYID
left join dbo.CHANGEAGENT as [REVENUECHANGEDBY] on REVENUECHANGEDBY.ID = REVENUE.CHANGEDBYID
where
REVENUE.DELETEDON is null
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1
)
update
dbo.REVENUESPLITGIFTAIDAMOUNTS
set
REVENUESPLITGIFTAIDAMOUNTS.ELIGIBILITYCODE = GIFTAID_CTE.ELIGIBILITY,
REVENUESPLITGIFTAIDAMOUNTS.DECLINESGIFTAID = GIFTAID_CTE.DECLINESGIFTAID,
REVENUESPLITGIFTAIDAMOUNTS.ISSPONSORSHIP = GIFTAID_CTE.ISSPONSORSHIP,
REVENUESPLITGIFTAIDAMOUNTS.TAXCLAIMNUMBER = GIFTAID_CTE.TAXCLAIMNUMBER,
REVENUESPLITGIFTAIDAMOUNTS.QUALIFICATIONSTATUS = GIFTAID_CTE.QUALIFICATIONSTATUS,
REVENUESPLITGIFTAIDAMOUNTS.TAXCLAIMAMOUNT = GIFTAID_CTE.TAXCLAIMAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.TRANSACTIONTAXCLAIMAMOUNT = GIFTAID_CTE.TRANSACTIONTAXCLAIMAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.ORGANIZATIONTAXCLAIMAMOUNT = GIFTAID_CTE.ORGANIZATIONTAXCLAIMAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.CLAIMABLEGIFTAIDAMOUNT = GIFTAID_CTE.CLAIMABLEGIFTAIDAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.TRANSACTIONCLAIMABLEGIFTAIDAMOUNT = GIFTAID_CTE.TRANSACTIONCLAIMABLEGIFTAIDAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT = GIFTAID_CTE.ORGANIZATIONCLAIMABLEGIFTAIDAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.RECEIVEDGIFTAIDAMOUNT = GIFTAID_CTE.RECEIVEDGIFTAIDAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.TRANSACTIONRECEIVEDGIFTAIDAMOUNT = GIFTAID_CTE.TRANSACTIONRECEIVEDGIFTAIDAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.ORGANIZATIONRECEIVEDGIFTAIDAMOUNT = GIFTAID_CTE.ORGANIZATIONRECEIVEDGIFTAIDAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.GROSSAMOUNT = GIFTAID_CTE.GROSSAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.TRANSACTIONGROSSAMOUNT = GIFTAID_CTE.TRANSACTIONGROSSAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.ORGANIZATIONGROSSAMOUNT = GIFTAID_CTE.ORGANIZATIONGROSSAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.POTENTIALGROSSAMOUNT = GIFTAID_CTE.POTENTIALGROSSAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.TRANSACTIONPOTENTIALGROSSAMOUNT = GIFTAID_CTE.TRANSACTIONPOTENTIALGROSSAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.ORGANIZATIONPOTENTIALGROSSAMOUNT = GIFTAID_CTE.ORGANIZATIONPOTENTIALGROSSAMOUNT,
REVENUESPLITGIFTAIDAMOUNTS.ORGANIZATIONEXCHANGERATEID = GIFTAID_CTE.ORGANIZATIONEXCHANGERATEID,
REVENUESPLITGIFTAIDAMOUNTS.BASEEXCHANGERATEID = GIFTAID_CTE.BASEEXCHANGERATEID,
REVENUESPLITGIFTAIDAMOUNTS.BASECURRENCYID = GIFTAID_CTE.BASECURRENCYID,
REVENUESPLITGIFTAIDAMOUNTS.TRANSACTIONCURRENCYID = GIFTAID_CTE.TRANSACTIONCURRENCYID,
REVENUESPLITGIFTAIDAMOUNTS.REVENUESPLITGIFTAIDDATEADDED = GIFTAID_CTE.REVENUESPLITGIFTAIDDATEADDED,
REVENUESPLITGIFTAIDAMOUNTS.REVENUESPLITGIFTAIDDATECHANGED = GIFTAID_CTE.REVENUESPLITGIFTAIDDATECHANGED,
REVENUESPLITGIFTAIDAMOUNTS.REVENUESPLITGIFTAIDADDEDBYUSERNAME = GIFTAID_CTE.REVENUESPLITGIFTAIDADDEDBYUSERNAME,
REVENUESPLITGIFTAIDAMOUNTS.REVENUESPLITGIFTAIDCHANGEDBYUSERNAME = GIFTAID_CTE.REVENUESPLITGIFTAIDCHANGEDBYUSERNAME,
REVENUESPLITGIFTAIDAMOUNTS.REVENUESPLITGIFTAIDTS = GIFTAID_CTE.MAXTS,
REVENUESPLITGIFTAIDAMOUNTS.FINANCIALTRANSACTIONID = GIFTAID_CTE.FINANCIALTRANSACTIONID,
REVENUESPLITGIFTAIDAMOUNTS.REVENUEORGANIZATIONEXCHANGERATEID = GIFTAID_CTE.REVENUEORGANIZATIONEXCHANGERATEID,
REVENUESPLITGIFTAIDAMOUNTS.REVENUEBASEEXCHANGERATEID = GIFTAID_CTE.REVENUEBASEEXCHANGERATEID,
REVENUESPLITGIFTAIDAMOUNTS.REVENUETRANSACTIONCURRENCYID = GIFTAID_CTE.REVENUETRANSACTIONCURRENCYID,
REVENUESPLITGIFTAIDAMOUNTS.REVENUEBASECURRENCYID = GIFTAID_CTE.REVENUEBASECURRENCYID,
REVENUESPLITGIFTAIDAMOUNTS.REVENUEDATEADDED = GIFTAID_CTE.REVENUEDATEADDED,
REVENUESPLITGIFTAIDAMOUNTS.REVENUEDATECHANGED = GIFTAID_CTE.REVENUEDATECHANGED,
REVENUESPLITGIFTAIDAMOUNTS.REVENUEADDEDBYUSERNAME = GIFTAID_CTE.REVENUEADDEDBYUSERNAME,
REVENUESPLITGIFTAIDAMOUNTS.REVENUECHANGEDBYUSERNAME = GIFTAID_CTE.REVENUECHANGEDBYUSERNAME,
REVENUESPLITGIFTAIDAMOUNTS.REVENUETS = GIFTAID_CTE.REVENUETS,
REVENUESPLITGIFTAIDAMOUNTS.REFRESHDATE = @CURRENTDATEPARAMETER
from
GIFTAID_CTE
inner join dbo.REVENUESPLITGIFTAIDAMOUNTS on GIFTAID_CTE.ID = REVENUESPLITGIFTAIDAMOUNTS.ID
where
not
(
REVENUESPLITGIFTAIDAMOUNTS.REVENUESPLITGIFTAIDTSLONG = coalesce(GIFTAID_CTE.MAXTSLONG,0)
and REVENUESPLITGIFTAIDAMOUNTS.ELIGIBILITYCODE = GIFTAID_CTE.ELIGIBILITY
and REVENUESPLITGIFTAIDAMOUNTS.TAXCLAIMAMOUNT = GIFTAID_CTE.TAXCLAIMAMOUNT
and REVENUESPLITGIFTAIDAMOUNTS.TRANSACTIONTAXCLAIMAMOUNT = GIFTAID_CTE.TRANSACTIONTAXCLAIMAMOUNT
and REVENUESPLITGIFTAIDAMOUNTS.ORGANIZATIONTAXCLAIMAMOUNT = GIFTAID_CTE.ORGANIZATIONTAXCLAIMAMOUNT
and REVENUESPLITGIFTAIDAMOUNTS.REVENUESPLITGIFTAIDADDEDBYUSERNAME = GIFTAID_CTE.REVENUESPLITGIFTAIDADDEDBYUSERNAME
and REVENUESPLITGIFTAIDAMOUNTS.REVENUESPLITGIFTAIDCHANGEDBYUSERNAME = GIFTAID_CTE.REVENUESPLITGIFTAIDCHANGEDBYUSERNAME
and REVENUESPLITGIFTAIDAMOUNTS.REVENUEADDEDBYUSERNAME = GIFTAID_CTE.REVENUEADDEDBYUSERNAME
and REVENUESPLITGIFTAIDAMOUNTS.REVENUECHANGEDBYUSERNAME = GIFTAID_CTE.REVENUECHANGEDBYUSERNAME
);
'
exec sp_executesql @UPDATESQL, N'@CURRENTDATEPARAMETER datetime, @IDSETREGISTERID uniqueidentifier', @CURRENTDATEPARAMETER = @CURRENTDATE, @IDSETREGISTERID = @IDSETREGISTERID;
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
--deletes will always clean up all records from the REVENUESPLITGIFTAIDAMOUNTS table that shouldn't belong in it. The selection, if it exists, will not be taken into account.
delete
from
dbo.REVENUESPLITGIFTAIDAMOUNTS
where
REVENUESPLITGIFTAIDAMOUNTS.ID not in
(
select
REVENUESPLIT.ID
from
dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.FINANCIALTRANSACTION REVENUE with (nolock) on REVENUESPLIT.FINANCIALTRANSACTIONID = REVENUE.ID
inner join dbo.REVENUE_EXT on REVENUE.ID = REVENUE_EXT.ID
inner join dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
where
REVENUE.DELETEDON is null
and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.TYPECODE <> 1
);
set @RECORDCOUNT = @RECORDCOUNT + @@ROWCOUNT;
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;
end