UFN_REVENUEUPDATEBATCH_GETAPPLICATIONS
Returns all applications for a given update revenue batch record.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @BATCHREVENUEID | uniqueidentifier | IN | |
| @SPLITS | xml | IN | |
| @REVENUESTREAMS | xml | IN | |
| @APPEALID | uniqueidentifier | IN | |
| @CATEGORYCODEID | uniqueidentifier | IN | |
| @RECOGNITIONS | xml | IN | |
| @SOLICITORS | xml | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUEUPDATEBATCH_GETAPPLICATIONS
(
@BATCHREVENUEID uniqueidentifier,
@SPLITS xml,
@REVENUESTREAMS xml,
@APPEALID uniqueidentifier,
@CATEGORYCODEID uniqueidentifier,
@RECOGNITIONS xml,
@SOLICITORS xml
)
returns @RESULT table
(
ID uniqueidentifier,
APPLICATIONID uniqueidentifier,
APPLICATIONCODE tinyint,
APPLIED money,
BALANCE money,
AMOUNTDUE money,
DATEDUE datetime,
TYPECODE tinyint,
APPLICATIONTRANSACTIONTYPECODE tinyint,
DESCRIPTION nvarchar(255),
GIFTFIELDS xml,
OTHERFIELDS xml,
MEMBERSHIPFIELDS xml,
DECLINESGIFTAID bit default 0,
GIFTAIDCOMMITTED bit default 0,
ISGIFTAIDSPONSORSHIP bit default 0,
CATEGORYCODEID uniqueidentifier
)
as
begin
--DONATIONS
insert into @RESULT
(
ID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
GIFTFIELDS,
DECLINESGIFTAID,
GIFTAIDCOMMITTED,
ISGIFTAIDSPONSORSHIP
)
select
coalesce(APPS.REVENUESPLITID, '00000000-0000-0000-0000-000000000000') as ID,
0 as APPLICATIONCODE,
APPS.APPLIED,
case APPS.TYPECODE when 3 then 9 else 0 end as TYPECODE,
(select
APPS.OPPORTUNITYID,
@APPEALID as APPEALID,
APPS.DESIGNATIONID,
case when @SPLITS.exist('(/ADDITIONALAPPLICATIONSSTREAM/ITEM/CATEGORYCODEID)') = 0 then @CATEGORYCODEID else APPS.CATEGORYCODEID end as CATEGORYCODEID,
(select
CAMP.REVENUESPLITCAMPAIGNID as ID,
CAMP.CAMPAIGNID,
CAMP.CAMPAIGNSUBPRIORITYID
from dbo.UFN_REVENUEBATCH_GETSPLITCAMPAIGNS_FROMITEMLISTXML(CAMPAIGNS) CAMP
for xml raw('ITEM'),type,elements,BINARY BASE64) as CAMPAIGNS,
(select
SOL.REVENUESOLICITORID as ID,
SOL.CONSTITUENTID,
SOL.AMOUNT,
SOL.SEQUENCE
from dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORSFOREDIT_FROMITEMLISTXML(@SOLICITORS) SOL
where (SOL.APPLICATIONCODE = APPS.TYPECODE and SOL.DESIGNATIONID = APPS.DESIGNATIONID
and SOL.DECLINESGIFTAID = APPS.DECLINESGIFTAID
and coalesce(SOL.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(APPS.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000'))
for xml raw('ITEM'),type,elements,BINARY BASE64) SOLICITORS,
(select
RECS.ID,
RECS.CONSTITUENTID,
RECS.REVENUERECOGNITIONTYPECODEID,
RECS.EFFECTIVEDATE,
RECS.AMOUNT,
RECS.DESIGNATIONID as DESIGNATIONID,
RECS.RECOGNITIONCREDITFKID as RECOGNITIONCREDITFKID,
RECS.DONORCHALLENGERECOGNITIONTYPECODE as DONORCHALLENGERECOGNITIONTYPECODE
FROM
(select -- existing recognitions
REC.REVENUERECOGNITIONID as ID,
REC.CONSTITUENTID,
REC.REVENUERECOGNITIONTYPECODEID,
REC.EFFECTIVEDATE,
REC.AMOUNT as AMOUNT,
REC.RECOGNITIONCREDITDESIGNATIONID as DESIGNATIONID,
REC.RECOGNITIONCREDITFKID as RECOGNITIONCREDITFKID,
REC.DONORCHALLENGERECOGNITIONTYPECODE as DONORCHALLENGERECOGNITIONTYPECODE
from dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSFOREDIT_FROMITEMLISTXML(@RECOGNITIONS) REC
where (REC.REVENUESPLITID is not null and REC.REVENUESPLITID = APPS.REVENUESPLITID) or
(REC.APPLICATIONCODE = APPS.TYPECODE and REC.DESIGNATIONID = APPS.DESIGNATIONID
and coalesce(REC.DECLINESGIFTAID,'0') = coalesce(APPS.DECLINESGIFTAID,'0')
and coalesce(REC.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000') = coalesce(APPS.SPONSORSHIPID,'00000000-0000-0000-0000-000000000000'))
union all
select -- default recognitions
null as ID,
BR.CONSTITUENTID,
null as REVENUERECOGNITIONTYPECODEID,
BR.DATE as EFFECTIVEDATE,
APPS.APPLIED as AMOUNT,
null as DESIGNATIONID,
null as RECOGNITIONCREDITFKID,
0 as DONORCHALLENGERECOGNITIONTYPECODE
from BATCHREVENUEADDITIONALAPPLICATIONS BRAA
inner join dbo.BATCHREVENUE BR on BR.ID = BRAA.BATCHREVENUEID
where BRAA.ID = APPS.ID
and BRAA.REVENUESPLITID is null
and not exists (select 'x' from dbo.BATCHREVENUERECOGNITION BRR
where BRR.ADDITIONALAPPLICATIONTYPECODE = BRAA.TYPECODE
and BRR.ADDITIONALAPPLICATIONDESIGNATIONID = BRAA.DESIGNATIONID)
) RECS
for xml raw('ITEM'),type,elements,BINARY BASE64) RECOGNITIONS,
case APPS.TYPECODE when 3 then 17 else 0 end as REVENUETYPECODE
for xml raw('ITEM'),type,elements,BINARY BASE64) GIFTFIELDS,
APPS.DECLINESGIFTAID,
case when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '' then 1 else 0 end,
APPS.ISGIFTAIDSPONSORSHIP
from dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS_FROMITEMLISTXML(@SPLITS) APPS
left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = APPS.ID
where APPS.TYPECODE in (0, 3)
--OTHER
insert into @RESULT
(
ID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
OTHERFIELDS
)
select
coalesce(APPS.REVENUESPLITID, '00000000-0000-0000-0000-000000000000') as ID,
4 as APPLICATIONCODE,
APPS.APPLIED,
4 as TYPECODE,
(select
APPS.OTHERTYPECODEID,
@APPEALID as APPEALID,
APPS.DESIGNATIONID,
case when @SPLITS.exist('(/ADDITIONALAPPLICATIONSSTREAM/ITEM/CATEGORYCODEID)') = 0 then @CATEGORYCODEID else APPS.CATEGORYCODEID end as CATEGORYCODEID,
(select
CAMP.REVENUESPLITCAMPAIGNID as ID,
CAMP.CAMPAIGNID,
CAMP.CAMPAIGNSUBPRIORITYID
from dbo.UFN_REVENUEBATCH_GETSPLITCAMPAIGNS_FROMITEMLISTXML(CAMPAIGNS) CAMP
for xml raw('ITEM'),type,elements,BINARY BASE64) as CAMPAIGNS,
(select
SOL.REVENUESOLICITORID as ID,
SOL.CONSTITUENTID,
SOL.AMOUNT,
SOL.SEQUENCE
from dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORSFOREDIT_FROMITEMLISTXML(@SOLICITORS) SOL
where (SOL.APPLICATIONCODE = APPS.TYPECODE and SOL.DESIGNATIONID = APPS.DESIGNATIONID)
for xml raw('ITEM'),type,elements,BINARY BASE64) SOLICITORS,
(select
RECS.ID,
RECS.CONSTITUENTID,
RECS.REVENUERECOGNITIONTYPECODEID,
RECS.EFFECTIVEDATE,
RECS.AMOUNT,
RECS.DESIGNATIONID as DESIGNATIONID,
RECS.RECOGNITIONCREDITFKID as RECOGNITIONCREDITFKID,
RECS.DONORCHALLENGERECOGNITIONTYPECODE as DONORCHALLENGERECOGNITIONTYPECODE
FROM
(select -- existing recognitions
REC.REVENUERECOGNITIONID as ID,
REC.CONSTITUENTID,
REC.REVENUERECOGNITIONTYPECODEID,
REC.EFFECTIVEDATE,
REC.AMOUNT as AMOUNT,
REC.RECOGNITIONCREDITDESIGNATIONID as DESIGNATIONID,
REC.RECOGNITIONCREDITFKID as RECOGNITIONCREDITFKID,
REC.DONORCHALLENGERECOGNITIONTYPECODE as DONORCHALLENGERECOGNITIONTYPECODE
from dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSFOREDIT_FROMITEMLISTXML(@RECOGNITIONS) REC
where (REC.REVENUESPLITID is not null and REC.REVENUESPLITID = APPS.REVENUESPLITID) or
(REC.APPLICATIONCODE = APPS.TYPECODE and REC.DESIGNATIONID = APPS.DESIGNATIONID)
union all
select -- default recognitions
null as ID,
BR.CONSTITUENTID,
null as REVENUERECOGNITIONTYPECODEID,
BR.DATE as EFFECTIVEDATE,
APPS.APPLIED as AMOUNT,
null as DESIGNATIONID,
null as RECOGNITIONCREDITFKID,
0 as DONORCHALLENGERECOGNITIONTYPECODE
from BATCHREVENUEADDITIONALAPPLICATIONS BRAA
inner join dbo.BATCHREVENUE BR on BR.ID = BRAA.BATCHREVENUEID
where BRAA.ID = APPS.ID
and BRAA.REVENUESPLITID is null
and not exists (select 'x' from dbo.BATCHREVENUERECOGNITION BRR
where BRR.ADDITIONALAPPLICATIONTYPECODE = BRAA.TYPECODE
and BRR.ADDITIONALAPPLICATIONDESIGNATIONID = BRAA.DESIGNATIONID)
) RECS
for xml raw('ITEM'),type,elements,BINARY BASE64) RECOGNITIONS
for xml raw('ITEM'),type,elements,BINARY BASE64) OTHERFIELDS
from dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS_FROMITEMLISTXML(@SPLITS) APPS
where TYPECODE = 1
--UNAPPLIED MATCHING GIFTS
insert into @RESULT
(
ID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
GIFTFIELDS
)
select
APPS.REVENUESPLITID as ID,
100,
APPS.APPLIED,
7 as [TYPECODE],
(select
@APPEALID as APPEALID,
APPS.DESIGNATIONID,
case when @SPLITS.exist('(/ADDITIONALAPPLICATIONSSTREAM/ITEM/CATEGORYCODEID)') = 0 then @CATEGORYCODEID else APPS.CATEGORYCODEID end as CATEGORYCODEID,
(select
CAMP.REVENUESPLITCAMPAIGNID as ID,
CAMP.CAMPAIGNID,
CAMP.CAMPAIGNSUBPRIORITYID
from dbo.UFN_REVENUEBATCH_GETSPLITCAMPAIGNS_FROMITEMLISTXML(CAMPAIGNS) CAMP
for xml raw('ITEM'),type,elements,BINARY BASE64) as CAMPAIGNS,
(select
SOL.REVENUESOLICITORID as ID,
SOL.CONSTITUENTID,
SOL.AMOUNT,
SOL.SEQUENCE
from dbo.UFN_REVENUEUPDATEBATCH_GETSOLICITORSFOREDIT_FROMITEMLISTXML(@SOLICITORS) SOL
where (SOL.APPLICATIONCODE = APPS.TYPECODE and SOL.DESIGNATIONID = APPS.DESIGNATIONID)
for xml raw('ITEM'),type,elements,BINARY BASE64) SOLICITORS,
(select
RECS.ID,
RECS.CONSTITUENTID,
RECS.REVENUERECOGNITIONTYPECODEID,
RECS.EFFECTIVEDATE,
RECS.AMOUNT,
RECS.DESIGNATIONID as DESIGNATIONID,
RECS.RECOGNITIONCREDITFKID as RECOGNITIONCREDITFKID,
RECS.DONORCHALLENGERECOGNITIONTYPECODE as DONORCHALLENGERECOGNITIONTYPECODE
FROM
(select -- existing recognitions
REC.REVENUERECOGNITIONID as ID,
REC.CONSTITUENTID,
REC.REVENUERECOGNITIONTYPECODEID,
REC.EFFECTIVEDATE,
REC.AMOUNT as AMOUNT,
REC.RECOGNITIONCREDITDESIGNATIONID as DESIGNATIONID,
REC.RECOGNITIONCREDITFKID as RECOGNITIONCREDITFKID,
REC.DONORCHALLENGERECOGNITIONTYPECODE as DONORCHALLENGERECOGNITIONTYPECODE
from dbo.UFN_REVENUEUPDATEBATCH_GETRECOGNITIONSFOREDIT_FROMITEMLISTXML(@RECOGNITIONS) REC
where (REC.REVENUESPLITID is not null and REC.REVENUESPLITID = APPS.REVENUESPLITID) or
(REC.APPLICATIONCODE = APPS.TYPECODE and REC.DESIGNATIONID = APPS.DESIGNATIONID)
union all
select -- default recognitions
null as ID,
BR.CONSTITUENTID,
null as REVENUERECOGNITIONTYPECODEID,
BR.DATE as EFFECTIVEDATE,
APPS.APPLIED as AMOUNT,
null as DESIGNATIONID,
null as RECOGNITIONCREDITFKID,
0 as DONORCHALLENGERECOGNITIONTYPECODE
from BATCHREVENUEADDITIONALAPPLICATIONS BRAA
inner join dbo.BATCHREVENUE BR on BR.ID = BRAA.BATCHREVENUEID
where BRAA.ID = APPS.ID
and BRAA.REVENUESPLITID is null
and not exists (select 'x' from dbo.BATCHREVENUERECOGNITION BRR
where BRR.ADDITIONALAPPLICATIONTYPECODE = BRAA.TYPECODE
and BRR.ADDITIONALAPPLICATIONDESIGNATIONID = BRAA.DESIGNATIONID)
) RECS
for xml raw('ITEM'),type,elements,BINARY BASE64) RECOGNITIONS
for xml raw('ITEM'),type,elements,BINARY BASE64) GIFTFIELDS
from dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS_FROMITEMLISTXML(@SPLITS) APPS
where TYPECODE = 2
-- applications
insert into @RESULT
(
ID,
APPLICATIONID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
DECLINESGIFTAID,
GIFTAIDCOMMITTED,
MEMBERSHIPFIELDS,
CATEGORYCODEID
)
select
coalesce(T.c.value('(REVENUESPLITID)[1]','uniqueidentifier'), '00000000-0000-0000-0000-000000000000') AS 'REVENUESPLITID',
T.c.value('(APPLICATIONID)[1]','uniqueidentifier') AS 'APPLICATIONID',
(case T.c.value('(TYPECODE)[1]','tinyint')
when 1 then 2 --Pledge Payment
when 3 then 7 --MGPledge Payment
when 2 then 3 --Recurring Gift Payment
when 6 then 1 --Event Registration Payment
when 4 then 6 --Planned gift
when 5 then 5 --Membership
when 9 then 8 --Grant Award Payment
when 10 then 13 --Donor challenge payment
when 33 then 3 -- sponsorship payment handling for single designation payment
else 99
end) as APPLICATIONCODE,
T.c.value('(APPLIED)[1]','money') AS 'APPLIED',
(case T.c.value('(TYPECODE)[1]','tinyint')
when 6 then 1
when 5 then 2
when 2 then (case when T.c.value('(SPONSORSHIPOPPORTUNITY)[1]','nvarchar(300)') is not null then 9 else 0 end)
when 333 then 9 -- handling for single designation payments
else 0
end) as TYPECODE,
REVENUESPLITGIFTAID.DECLINESGIFTAID,
case when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '' then 1 else 0 end,
case when T.c.exist('./MEMBERSHIPS/ITEM') = 1 then T.c.query('MEMBERSHIPS/ITEM') else null end AS 'MEMBERSHIPFIELDS',
@CATEGORYCODEID as CATEGORYCODEID
from @REVENUESTREAMS.nodes('/REVENUESTREAMS/ITEM') T(c)
left outer join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = T.c.value('(REVENUESPLITID)[1]','uniqueidentifier')
where (T.c.value('(APPLIED)[1]','money') <> 0 or (T.c.value('(APPLIED)[1]','money') = 0 and T.c.value('(REVENUESPLITID)[1]','uniqueidentifier') is not null));
return;
end