UFN_REVENUE_GETAPPLICATIONS_2
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @TRANSACTIONID | uniqueidentifier | IN | |
| @USEDESIGNATIONUSERID | bit | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUE_GETAPPLICATIONS_2
(
@TRANSACTIONID uniqueidentifier,
@USEDESIGNATIONUSERID bit
)
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,
TRANSACTIONCURRENCYID uniqueidentifier,
APPLICATIONCURRENCYID uniqueidentifier,
EXCHANGERATE decimal(20,8),
RECURRINGGIFTFIELDS xml,
CATEGORYCODEID uniqueidentifier default null
)
as
begin
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @APPEALID uniqueidentifier;
declare @CONSTITUENTID uniqueidentifier;
select @TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
,@APPEALID = REVENUE_EXT.APPEALID
,@CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID
from dbo.FINANCIALTRANSACTION
inner join dbo.REVENUE_EXT on FINANCIALTRANSACTION.ID = REVENUE_EXT.ID
where FINANCIALTRANSACTION.ID = @TRANSACTIONID;
--DONATIONS
insert into @RESULT
(
ID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
DESCRIPTION,
GIFTFIELDS,
DECLINESGIFTAID,
GIFTAIDCOMMITTED,
ISGIFTAIDSPONSORSHIP,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE
)
select
coalesce(REVENUESPLIT.ID, '00000000-0000-0000-0000-000000000000') as ID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
--11/04/2014 Jason.Perry WI439722 Determine which DESIGNATION value to display: "DESIGNATION.NAME" (through BUILDNAME function), or "DESIGNATION.USERID"
case
when @USEDESIGNATIONUSERID = 1 then
DESIGNATION.USERID
else
dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID)
end as [DESCRIPTION],
(select
REVENUEOPPORTUNITY.OPPORTUNITYID,
@APPEALID,
REVENUESPLIT_EXT.DESIGNATIONID,
GLREVENUECATEGORYMAPPINGID CATEGORYCODEID,
(select
REVENUESPLITCAMPAIGN.ID,
REVENUESPLITCAMPAIGN.CAMPAIGNID,
REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
from REVENUESPLITCAMPAIGN
where REVENUESPLITCAMPAIGN.REVENUESPLITID =RSSUB.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) CAMPAIGNS,
(select
REVENUESOLICITOR.ID,
REVENUESOLICITOR.CONSTITUENTID,
REVENUESOLICITOR.AMOUNT,
REVENUESOLICITOR.SEQUENCE
from REVENUESOLICITOR
where REVENUESOLICITOR.REVENUESPLITID =RSSUB.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) SOLICITORS,
(select
RC.ID,
RC.CONSTITUENTID,
RC.REVENUERECOGNITIONTYPECODEID,
RC.EFFECTIVEDATE,
RC.AMOUNT,
RC.DONORCHALLENGERECOGNITIONTYPECODE,
RC.DONOR,
RC.DESIGNATION,
RC.DESIGNATIONID,
RC.RECOGNITIONCREDITFKID
from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS(RSSUB.ID) RC
for xml raw('ITEM'),type,elements,BINARY BASE64) RECOGNITIONS
from dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
left outer join dbo.REVENUEOPPORTUNITY on REVENUEOPPORTUNITY.ID = RSSUB.ID
left outer join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RSSUB.ID
where RSSUB.ID = REVENUESPLIT.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) GIFTFIELDS,
REVENUESPLITGIFTAID.DECLINESGIFTAID,
case when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '' then 1 else 0 end,
REVENUESPLITGIFTAID.ISSPONSORSHIP,
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
@TRANSACTIONCURRENCYID [APPLICATIONCURRENCYID],
1 as EXCHANGERATE
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID and REVENUESPLIT_EXT.APPLICATIONCODE = 0
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
--PLEDGE PAYMENTS
insert into @RESULT
(
ID,
APPLICATIONID,
APPLICATIONCODE,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
TYPECODE,
APPLICATIONTRANSACTIONTYPECODE,
DESCRIPTION,
DECLINESGIFTAID,
GIFTAIDCOMMITTED,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE
)
select
APPLICATIONS.ID
,APPLICATIONS.APPLICATIONID
,APPLICATIONS.APPLICATIONCODE
,APPLICATIONS.APPLIED
,APPLICATIONS.BALANCE
,case when APPLICATIONS.APPLICATIONCODE in (6, 7, 13) then
APPLICATIONS.BALANCE
else
0.00
end [AMOUNTDUE]
,APPLICATIONS.DATEDUE
,APPLICATIONS.TYPECODE
,APPLICATIONS.TRANSACTIONTYPECODE
,APPLICATIONS.DESCRIPTION
,APPLICATIONS.DECLINESGIFTAID
,APPLICATIONS.GIFTAIDCOMMITTED
,APPLICATIONS.TRANSACTIONCURRENCYID
,APPLICATIONS.APPLICATIONCURRENCYID
,APPLICATIONS.EXCHANGERATE
from (
select
coalesce(REVENUESPLIT.ID, '00000000-0000-0000-0000-000000000000') as ID,
INSTALLMENTSPLITPAYMENT.PLEDGEID as APPLICATIONID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
dbo.UFN_PLEDGE_GETBALANCE(INSTALLMENTSPLITPAYMENT.PLEDGEID) as [BALANCE],
-- case when REVENUESPLIT_EXT.APPLICATIONCODE in (6, 7, 13) then
--dbo.UFN_PLEDGE_GETBALANCE(INSTALLMENTSPLITPAYMENT.PLEDGEID)
-- else
--0.00
-- end as AMOUNTDUE,
min(INSTALLMENT.DATE) as [DATEDUE],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
PLEDGEREVENUE.TYPECODE as [TRANSACTIONTYPECODE],
case
when REVENUESPLIT_EXT.APPLICATIONCODE = 7 then NF.NAME +
case when [MATCHEDGIFTCONSTITUENT].LOOKUPID is null
then ''
else
' - ' + (
select
CASE [MATCHEDGIFTCONSTITUENT].ISORGANIZATION WHEN 1 THEN
CASE [MATCHEDGIFTCONSTITUENT].KEYNAMEPREFIX WHEN '' THEN [MATCHEDGIFTCONSTITUENT].KEYNAME ELSE [MATCHEDGIFTCONSTITUENT].KEYNAMEPREFIX + ' ' + [MATCHEDGIFTCONSTITUENT].KEYNAME END
ELSE
CASE [MATCHEDGIFTCONSTITUENT].ISGROUP WHEN 1 THEN
CASE [MATCHEDGIFTCONSTITUENT].DISPLAYNAME WHEN '' THEN [MATCHEDGIFTCONSTITUENT].KEYNAME ELSE [MATCHEDGIFTCONSTITUENT].DISPLAYNAME END
ELSE
CASE [MATCHEDGIFTCONSTITUENT].FIRSTNAME WHEN '' THEN '' ELSE [MATCHEDGIFTCONSTITUENT].FIRSTNAME + ' ' END
+
CASE [MATCHEDGIFTCONSTITUENT].MIDDLENAME WHEN '' THEN '' ELSE LEFT([MATCHEDGIFTCONSTITUENT].MIDDLENAME,1) + '. ' END
+
[MATCHEDGIFTCONSTITUENT].KEYNAME
END
END) +' ('+ [MATCHEDGIFTCONSTITUENT].LOOKUPID + ')' end
when REVENUESPLIT_EXT.APPLICATIONCODE = 19 then
case when REVENUESPLIT_EXT.TYPECODE = 0 then
--11/04/2014 Jason.Perry WI439722 Determine which DESIGNATION value to display: "DESIGNATION.NAME" (through BUILDNAME function), or "DESIGNATION.USERID"
case
when @USEDESIGNATIONUSERID = 1 then
DESIGNATION.USERID
else
dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID)
end
when REVENUESPLIT_EXT.TYPECODE = 2 then (select top 1 MEMBERSHIPPROGRAM.NAME from dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT
inner join dbo.INSTALLMENTSPLITPAYMENT on PAYMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
inner join dbo.INSTALLMENTSPLIT PLEDGEINSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = PLEDGEINSTALLMENTSPLIT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTIONLINEITEM.ID = PLEDGEINSTALLMENTSPLIT.REVENUESPLITID
inner join dbo.MEMBERSHIPTRANSACTION on MEMBERSHIPTRANSACTION.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIPLEVEL.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID
inner join dbo.MEMBERSHIPPROGRAM on MEMBERSHIPPROGRAM.ID = MEMBERSHIPLEVEL.MEMBERSHIPPROGRAMID
where PAYMENTSPLIT.ID = REVENUESPLIT.ID)
when REVENUESPLIT_EXT.TYPECODE = 18 then (select top 1 ADDON.NAME from dbo.ADDON
inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.ID = ADDON.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM on MEMBERSHIPADDON.REVENUESPLITID = FINANCIALTRANSACTIONLINEITEM.ID and FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = PLEDGEREVENUE.ID
inner join dbo.INSTALLMENTSPLIT on FINANCIALTRANSACTIONLINEITEM.ID = INSTALLMENTSPLIT.REVENUESPLITID
inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM PAYMENTSPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = PAYMENTSPLIT.ID
where PAYMENTSPLIT.ID = REVENUESPLIT.ID)
else
--11/04/2014 Jason.Perry WI439722 Determine which DESIGNATION value to display: "DESIGNATION.NAME" (through BUILDNAME function), or "DESIGNATION.USERID"
case
when @USEDESIGNATIONUSERID = 1 then
DESIGNATION.USERID
else
dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID)
end
end
else
--11/04/2014 Jason.Perry WI439722 Determine which DESIGNATION value to display: "DESIGNATION.NAME" (through BUILDNAME function), or "DESIGNATION.USERID"
case
when @USEDESIGNATIONUSERID = 1 then
DESIGNATION.USERID
else
dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID)
end
end as [DESCRIPTION],
REVENUESPLITGIFTAID.DECLINESGIFTAID,
case when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '' then 1 else 0 end [GIFTAIDCOMMITTED],
@TRANSACTIONCURRENCYID TRANSACTIONCURRENCYID,
INSTALLMENTSPLITPAYMENT.APPLICATIONCURRENCYID,
dbo.UFN_CURRENCYEXCHANGERATE_GETRATE(INSTALLMENTSPLITPAYMENT.APPLICATIONEXCHANGERATEID) [EXCHANGERATE]
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
left join dbo.INSTALLMENT on INSTALLMENT.ID = INSTALLMENTSPLIT.INSTALLMENTID
left join dbo.FINANCIALTRANSACTION as [PLEDGEREVENUE] on [PLEDGEREVENUE].ID = INSTALLMENTSPLITPAYMENT.PLEDGEID and PLEDGEREVENUE.DELETEDON is null and PLEDGEREVENUE.TYPECODE in (1, 3, 15)
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(PLEDGEREVENUE.CONSTITUENTID) NF
left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
left join dbo.REVENUEMATCHINGGIFT on REVENUEMATCHINGGIFT.ID = [PLEDGEREVENUE].ID
left join dbo.FINANCIALTRANSACTION as [MATCHEDGIFT] on REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = [MATCHEDGIFT].ID and [MATCHEDGIFT].DELETEDON is null
left join dbo.CONSTITUENT as [MATCHEDGIFTCONSTITUENT] on [MATCHEDGIFTCONSTITUENT].ID = [MATCHEDGIFT].CONSTITUENTID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID and REVENUESPLIT_EXT.APPLICATIONCODE in (2, 6, 7, 8, 13, 17, 19)
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
group by
REVENUESPLIT.ID,
INSTALLMENTSPLITPAYMENT.PLEDGEID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT_EXT.TYPECODE,
PLEDGEREVENUE.TYPECODE,
PLEDGEREVENUE.CONSTITUENTID,
REVENUESPLITGIFTAID.DECLINESGIFTAID,
REVENUESPLITGIFTAID.TAXCLAIMNUMBER,
INSTALLMENTSPLITPAYMENT.APPLICATIONCURRENCYID,
REVENUESPLIT.TRANSACTIONAMOUNT,
INSTALLMENTSPLITPAYMENT.APPLICATIONEXCHANGERATEID,
NF.NAME,
DESIGNATION.USERID,
[MATCHEDGIFTCONSTITUENT].LOOKUPID,
[MATCHEDGIFTCONSTITUENT].ISORGANIZATION,
[MATCHEDGIFTCONSTITUENT].KEYNAMEPREFIX,
[MATCHEDGIFTCONSTITUENT].KEYNAME,
[MATCHEDGIFTCONSTITUENT].ISGROUP,
[MATCHEDGIFTCONSTITUENT].DISPLAYNAME,
[MATCHEDGIFTCONSTITUENT].FIRSTNAME,
[MATCHEDGIFTCONSTITUENT].MIDDLENAME,
REVENUESPLIT_EXT.DESIGNATIONID,
PLEDGEREVENUE.ID) APPLICATIONS;
--RECURRING GIFT PAYMENTS
insert into @RESULT
(
ID,
APPLICATIONID,
APPLICATIONCODE,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
TYPECODE,
APPLICATIONTRANSACTIONTYPECODE,
DESCRIPTION,
DECLINESGIFTAID,
GIFTAIDCOMMITTED,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE,
RECURRINGGIFTFIELDS
)
select
REVENUESPLIT.ID as ID,
TEMPLATE.ID as APPLICATIONID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
TEMPLATESPLIT_LI.TRANSACTIONAMOUNT as [BALANCE],
0.00 as AMOUNTDUE,
RECURRINGGIFTACTIVITY.SCHEDULEDATE as [DATEDUE],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
TEMPLATE.TYPECODE as [TRANSACTIONTYPECODE],
case
when @USEDESIGNATIONUSERID = 1 then
DESIGNATION.USERID
else
dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID)
end as [DESCRIPTION],
REVENUESPLITGIFTAID.DECLINESGIFTAID,
case when REVENUESPLITGIFTAID.TAXCLAIMNUMBER <> '' then 1 else 0 end,
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
RECURRINGGIFTACTIVITY.APPLICATIONCURRENCYID,
dbo.UFN_CURRENCYEXCHANGERATE_GETRATE(RECURRINGGIFTACTIVITY.APPLICATIONEXCHANGERATEID),
(select
REVENUESPLIT_EXT.DESIGNATIONID,
ACTIONABOVEAMOUNT,
OVERPAYMENTCODE,
ACTIONBELOWAMOUNTS,
INSTALLMENTUNDERPAYMENTCODE as UNDERPAYMENTCODE
from dbo.RECURRINGGIFTINSTALLMENT I
cross apply dbo.UFN_RECURRINGGIFT_GETPAYMENTUIFIELDS(TEMPLATE.ID,
dbo.UFN_RECURRINGGIFTINSTALLMENT_GETINSTALLMENTBALANCEIGNOREPAYMENT(I.ID, @TRANSACTIONID),
dbo.UFN_RECURRINGGIFT_GETPASTDUEAMOUNTIGNOREPAYMENT_2(TEMPLATE.ID, @TRANSACTIONID, REVENUESPLIT.DATEADDED, 1),
I.DATE,
REVENUESPLIT.DATEADDED,
@TRANSACTIONID) U
where I.ID = dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTIGNOREPAYMENT_2(TEMPLATE.ID, @TRANSACTIONID, null, REVENUESPLIT.DATEADDED)
for xml raw('ITEM'),type,elements,BINARY BASE64) RECURRINGGIFTFIELDS
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.RECURRINGGIFTACTIVITY on PAYMENTREVENUEID = REVENUESPLIT.ID
inner join dbo.FINANCIALTRANSACTION TEMPLATE on TEMPLATE.ID = RECURRINGGIFTACTIVITY.SOURCEREVENUEID
left outer join dbo.REVENUESPLIT_EXT TEMPLATESPLIT on TEMPLATESPLIT.ID = TEMPLATE.ID and TEMPLATESPLIT.DESIGNATIONID = REVENUESPLIT_EXT.DESIGNATIONID
left outer join dbo.FINANCIALTRANSACTIONLINEITEM TEMPLATESPLIT_LI on TEMPLATESPLIT_LI.ID = TEMPLATESPLIT.ID and TEMPLATESPLIT_LI.DELETEDON is null
left join dbo.REVENUESPLITGIFTAID on REVENUESPLITGIFTAID.ID = REVENUESPLIT.ID
left join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID and REVENUESPLIT_EXT.APPLICATIONCODE = 3
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
and TEMPLATE.TYPECODE = 2 and TEMPLATE.DELETEDON is null
--EVENT REGISTRATION FEE PAYMENTS
insert into @RESULT
(
ID,
APPLICATIONID,
APPLICATIONCODE,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
TYPECODE,
APPLICATIONTRANSACTIONTYPECODE,
DESCRIPTION,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE,
CATEGORYCODEID
)
select
APPLICATIONS.ID
,APPLICATIONS.APPLICATIONID
,APPLICATIONS.APPLICATIONCODE
,APPLICATIONS.APPLIED
,APPLICATIONS.BALANCE
,APPLICATIONS.BALANCE [AMOUNTDUE]
,APPLICATIONS.DATEDUE
,APPLICATIONS.TYPECODE
,APPLICATIONS.TRANSACTIONTYPECODE [APPLICATIONTRANSACTIONTYPECODE]
,APPLICATIONS.DESCRIPTION
,APPLICATIONS.TRANSACTIONCURRENCYID
,APPLICATIONS.APPLICATIONCURRENCYID
,APPLICATIONS.EXCHANGERATE
,APPLICATIONS.GLREVENUECATEGORYMAPPINGID [CATEGORYCODEID]
from (
select
REVENUESPLIT.ID as ID,
REGISTRANT.ID as APPLICATIONID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
dbo.UFN_EVENTREGISTRANT_GETBALANCE(REGISTRANT.ID) as [BALANCE],
EVENT.STARTDATE as [DATEDUE],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
0 as [TRANSACTIONTYPECODE],
EVENT.NAME as [DESCRIPTION],
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
[PAYMENT].APPLICATIONCURRENCYID,
dbo.UFN_CURRENCYEXCHANGERATE_GETRATE(PAYMENT.APPLICATIONEXCHANGERATEID) [EXCHANGERATE],
REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID [GLREVENUECATEGORYMAPPINGID]
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.EVENTREGISTRANTPAYMENT as [PAYMENT] on REVENUESPLIT.ID = [PAYMENT].PAYMENTID
inner join dbo.REGISTRANT on [PAYMENT].REGISTRANTID = REGISTRANT.ID
inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
left outer join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = REVENUESPLIT.ID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID and REVENUESPLIT_EXT.APPLICATIONCODE = 1
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1) APPLICATIONS
--MEMBERSHIPS
insert into @RESULT
(
ID,
APPLICATIONID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
APPLICATIONTRANSACTIONTYPECODE,
DESCRIPTION,
MEMBERSHIPFIELDS,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE
)
select
REVENUESPLIT.ID as ID,
MEMBERSHIP.ID as APPLICATIONID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
0 as [TRANSACTIONTYPECODE],
dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID) as [DESCRIPTION],
(select
ID,
MEMBERSHIPPROGRAMID,
MEMBERSHIPLEVELID,
MEMBERSHIPLEVELTERMID,
MEMBERSHIPLEVELTYPECODEID,
NUMBEROFCHILDREN,
COMMENTS,
ISGIFT,
SENDRENEWALCODE,
EXPIRATIONDATE,
GIVENBYID,
(select
MEMBER.ID,
MEMBER.CONSTITUENTID,
MEMBER.ISPRIMARY,
(select
MEMBERSHIPCARD.ID,
MEMBERSHIPCARD.NAMEONCARD,
MEMBERSHIPCARD.EXPIRATIONDATE
from dbo.MEMBERSHIPCARD
where MEMBERSHIPCARD.MEMBERID = MEMBER.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) MEMBERSHIPCARDS
from dbo.MEMBER
where MEMBER.ID = MEMBERSHIPSUB.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) MEMBERS
from dbo.MEMBERSHIP MEMBERSHIPSUB
where MEMBERSHIPSUB.ID = MEMBERSHIP.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) MEMBERSHIPFIELDS,
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
@TRANSACTIONCURRENCYID [APPLICATIONCURRENCYID],
1 as EXCHANGERATE
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.MEMBERSHIPTRANSACTION on REVENUESPLIT.ID = MEMBERSHIPTRANSACTION.REVENUESPLITID
inner join dbo.MEMBERSHIP on MEMBERSHIP.ID = MEMBERSHIPTRANSACTION.MEMBERSHIPID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID and REVENUESPLIT_EXT.APPLICATIONCODE = 5
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
--MEMBERSHIP ADD-ON
insert into @RESULT
(
ID,
APPLICATIONID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
APPLICATIONTRANSACTIONTYPECODE,
DESCRIPTION,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE
)
select
REVENUESPLIT.ID as ID,
ADDON.ID as APPLICATIONID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
0 as [TRANSACTIONTYPECODE],
ADDON.NAME as [DESCRIPTION],
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
@TRANSACTIONCURRENCYID [APPLICATIONCURRENCYID],
1 as EXCHANGERATE
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.MEMBERSHIPADDON on MEMBERSHIPADDON.REVENUESPLITID = REVENUESPLIT.ID
inner join dbo.ADDON on ADDON.ID = MEMBERSHIPADDON.ADDONID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID and REVENUESPLIT_EXT.APPLICATIONCODE = 18
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
--OTHER
insert into @RESULT
(
ID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
DESCRIPTION,
OTHERFIELDS,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE
)
select
REVENUESPLIT.ID as ID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
--11/04/2014 Jason.Perry WI439722 Determine which DESIGNATION value to display: "DESIGNATION.NAME" (through BUILDNAME function), or "DESIGNATION.USERID"
case
when @USEDESIGNATIONUSERID = 1 then
DESIGNATION.USERID
else
REVENUEOTHERTYPECODE.DESCRIPTION
end as [DESCRIPTION],
(select
REVENUESPLITOTHER.OTHERTYPECODEID,
@APPEALID,
DESIGNATIONID,
GLREVENUECATEGORYMAPPINGID CATEGORYCODEID,
(select
REVENUESPLITCAMPAIGN.ID,
REVENUESPLITCAMPAIGN.CAMPAIGNID,
REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
from REVENUESPLITCAMPAIGN
where REVENUESPLITCAMPAIGN.REVENUESPLITID =RSSUB.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) CAMPAIGNS,
(select
REVENUESOLICITOR.ID,
REVENUESOLICITOR.CONSTITUENTID,
REVENUESOLICITOR.AMOUNT,
REVENUESOLICITOR.SEQUENCE
from REVENUESOLICITOR
where REVENUESOLICITOR.REVENUESPLITID =RSSUB.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) SOLICITORS,
(select
RC.ID,
RC.CONSTITUENTID,
RC.REVENUERECOGNITIONTYPECODEID,
RC.EFFECTIVEDATE,
RC.AMOUNT,
RC.DONORCHALLENGERECOGNITIONTYPECODE,
RC.DONOR,
RC.DESIGNATION,
RC.DESIGNATIONID,
RC.RECOGNITIONCREDITFKID
from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS(RSSUB.ID) RC
for xml raw('ITEM'),type,elements,BINARY BASE64) RECOGNITIONS
from dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
left outer join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RSSUB.ID
where RSSUB.ID = REVENUESPLIT.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) OTHERFIELDS,
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
@TRANSACTIONCURRENCYID [APPLICATIONCURRENCYID],
1 as EXCHANGERATE
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
inner join dbo.REVENUESPLITOTHER on REVENUESPLIT.ID = REVENUESPLITOTHER.ID
inner join dbo.REVENUEOTHERTYPECODE on REVENUESPLITOTHER.OTHERTYPECODEID = REVENUEOTHERTYPECODE.ID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID and REVENUESPLIT_EXT.APPLICATIONCODE = 4
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
--UNAPPLIED MATCHING GIFTS
insert into @RESULT
(
ID,
APPLICATIONCODE,
APPLIED,
TYPECODE,
DESCRIPTION,
GIFTFIELDS,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE
)
select
REVENUESPLIT.ID as ID,
100,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
--11/04/2014 Jason.Perry WI439722 Determine which DESIGNATION value to display: "DESIGNATION.NAME" (through BUILDNAME function), or "DESIGNATION.USERID"
case
when @USEDESIGNATIONUSERID = 1 then
DESIGNATION.USERID
else
dbo.UFN_DESIGNATION_BUILDNAME(REVENUESPLIT_EXT.DESIGNATIONID)
end as [DESCRIPTION],
(select
@APPEALID,
REVENUESPLIT_EXT.DESIGNATIONID,
GLREVENUECATEGORYMAPPINGID CATEGORYCODEID,
(select
REVENUESPLITCAMPAIGN.ID,
REVENUESPLITCAMPAIGN.CAMPAIGNID,
REVENUESPLITCAMPAIGN.CAMPAIGNSUBPRIORITYID
from REVENUESPLITCAMPAIGN
where REVENUESPLITCAMPAIGN.REVENUESPLITID =RSSUB.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) CAMPAIGNS,
(select
REVENUESOLICITOR.ID,
REVENUESOLICITOR.CONSTITUENTID,
REVENUESOLICITOR.AMOUNT,
REVENUESOLICITOR.SEQUENCE
from REVENUESOLICITOR
where REVENUESOLICITOR.REVENUESPLITID =RSSUB.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) SOLICITORS,
(select
RC.ID,
RC.CONSTITUENTID,
RC.REVENUERECOGNITIONTYPECODEID,
RC.EFFECTIVEDATE,
RC.AMOUNT,
RC.DONORCHALLENGERECOGNITIONTYPECODE,
RC.DONOR,
RC.DESIGNATION,
RC.DESIGNATIONID,
RC.RECOGNITIONCREDITFKID
from dbo.UFN_RECOGNITIONCREDIT_DONORCHALLENGE_GETRECOGNITIONS(RSSUB.ID) RC
for xml raw('ITEM'),type,elements,BINARY BASE64) RECOGNITIONS
from dbo.FINANCIALTRANSACTIONLINEITEM RSSUB
left outer join dbo.REVENUECATEGORY on REVENUECATEGORY.ID = RSSUB.ID
where RSSUB.ID = REVENUESPLIT.ID
for xml raw('ITEM'),type,elements,BINARY BASE64) GIFTFIELDS,
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
@TRANSACTIONCURRENCYID [APPLICATIONCURRENCYID],
1 as EXCHANGERATE
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
left join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT_EXT.DESIGNATIONID
left outer join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLITPAYMENT.PAYMENTID = REVENUESPLIT.ID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID and REVENUESPLIT_EXT.APPLICATIONCODE = 7
and INSTALLMENTSPLITPAYMENT.ID is null
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
--Auction purchase payments
insert into @RESULT
(
ID,
APPLICATIONID,
APPLICATIONCODE,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
TYPECODE,
APPLICATIONTRANSACTIONTYPECODE,
DESCRIPTION,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE
)
select distinct
case
when AUCTIONITEM.TYPECODE = 0 then REVENUESPLIT.ID
else AUCTIONITEM.ID
end as ID,
AUCTIONITEM.ID as APPLICATIONID,
12 as APPLICATIONCODE,
case
when AUCTIONITEM.TYPECODE = 0 then REVENUESPLIT.TRANSACTIONAMOUNT
else (select sum([PACKAGEITEMSPLIT].TRANSACTIONAMOUNT) from dbo.AUCTIONITEM [PACKAGEITEMS] left join dbo.AUCTIONITEMPURCHASE [ITEMPURCHASE] on [PACKAGEITEMS].ID = [ITEMPURCHASE].AUCTIONITEMID left join dbo.REVENUESPLIT [PACKAGEITEMSPLIT] on [ITEMPURCHASE].PURCHASEID = [PACKAGEITEMSPLIT].ID where [PACKAGEITEMS].PACKAGEID = AUCTIONITEM.ID and [PACKAGEITEMSPLIT].REVENUEID = @TRANSACTIONID)
end as APPLIED,
0 as [BALANCE],
0 as AMOUNTDUE,
null as [DATEDUE],
12 as [TYPECODE],
0 as [TRANSACTIONTYPECODE],
AUCTIONITEM.NAME as [DESCRIPTION],
/* WI123584 for packages, this returns a result set where REVENUESPLIT is null (because of the left join).
* So attempting to format currency based on REVENUESPLIT.TRANSACTIONCURRENCY results in the currency formatting as
* the org currency.
*
* We can get away with using the AUCTIONITEM's transaction currency because we've locked it down so that the
* payment always has to have the same transaction currency as the purchased item or package. If we relax this
* requirement in the future, this will need to be revisited.
*/
AUCTIONITEM.TRANSACTIONCURRENCYID as [TRANSACTIONCURRENCYID],
AUCTIONITEM.TRANSACTIONCURRENCYID as [APPLICATIONCURRENCYID],
1 as [EXCHANGERATE]
from dbo.AUCTIONITEMREVENUEPURCHASE
inner join dbo.AUCTIONITEM on AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
left join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
left join dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT on REVENUESPLIT.ID = AUCTIONITEMPURCHASE.PURCHASEID and REVENUESPLIT.FINANCIALTRANSACTIONID = AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID and REVENUESPLIT.DELETEDON is null
left join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
where
AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = @TRANSACTIONID
and
((AUCTIONITEM.TYPECODE = 0 and REVENUESPLIT_EXT.APPLICATIONCODE = 12) or AUCTIONITEM.TYPECODE = 1)
and
((AUCTIONITEM.TYPECODE = 0 and AUCTIONITEM.PACKAGEID is null) or (AUCTIONITEM.TYPECODE = 1))
-- event sponsorship payment
insert into @RESULT
(
ID,
APPLICATIONID,
APPLICATIONCODE,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
TYPECODE,
APPLICATIONTRANSACTIONTYPECODE,
DESCRIPTION,
TRANSACTIONCURRENCYID,
APPLICATIONCURRENCYID,
EXCHANGERATE
)
select
REVENUESPLIT.ID as ID,
EVENTSPONSOR.ID as APPLICATIONID,
REVENUESPLIT_EXT.APPLICATIONCODE,
REVENUESPLIT.TRANSACTIONAMOUNT as [APPLIED],
dbo.UFN_EVENTSPONSOR_GETBALANCE(EVENTSPONSOR.ID) as [BALANCE],
dbo.UFN_EVENTSPONSOR_GETBALANCE(EVENTSPONSOR.ID) as AMOUNTDUE,
EVENT.STARTDATE as [DATEDUE],
REVENUESPLIT_EXT.TYPECODE as [TYPECODE],
0 as [TRANSACTIONTYPECODE],
EVENT.NAME as [DESCRIPTION],
@TRANSACTIONCURRENCYID [TRANSACTIONCURRENCYID],
@TRANSACTIONCURRENCYID as APPLICATIONCURRENCYID,
1 as EXCHANGERATE
from dbo.FINANCIALTRANSACTIONLINEITEM REVENUESPLIT
inner join dbo.REVENUESPLIT_EXT on REVENUESPLIT.ID = REVENUESPLIT_EXT.ID
inner join dbo.EVENTSPONSOR on @CONSTITUENTID = isnull(EVENTSPONSOR.ORGANIZATIONCONSTITUENTID, EVENTSPONSOR.CONSTITUENTID)
inner join dbo.[EVENT] on [EVENT].ID = EVENTSPONSOR.EVENTID
where REVENUESPLIT.FINANCIALTRANSACTIONID = @TRANSACTIONID
and REVENUESPLIT_EXT.APPLICATIONCODE = 15
and [EVENT].APPEALID = @APPEALID
and REVENUESPLIT.DELETEDON is null and REVENUESPLIT.TYPECODE != 1
return;
end