UFN_REVENUESPLITINFORMATION
Returns revenue split information given an ID and a source code, this is used in the GL mapping functions.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @REVENUESPLITID | uniqueidentifier | IN | |
| @INFORMATIONSOURCECODE | tinyint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUESPLITINFORMATION
(
@REVENUEID uniqueidentifier,
@REVENUESPLITID uniqueidentifier,
@INFORMATIONSOURCECODE tinyint, -- 0 is the revenue table, 1 is the batch revenue table
@CURRENTAPPUSERID uniqueidentifier = null
)
returns @RETVAL table
(
ID uniqueidentifier,
REVENUEID uniqueidentifier,
DESIGNATIONID uniqueidentifier,
EVENTID uniqueidentifier,
PROGRAMID uniqueidentifier,
CATEGORYCODEID uniqueidentifier,
MEMBERSHIPLEVELID uniqueidentifier,
ORDERMEMBERSHIPLEVELID uniqueidentifier,
ORDERMERCHANDISEPRODUCTINSTANCEID uniqueidentifier,
ORDERMERCHANDISEDEPARTMENTID uniqueidentifier,
ORDEREVENTID uniqueidentifier,
ORDERPROGRAMID uniqueidentifier,
ORDERFEEID uniqueidentifier,
ORDERTAXID uniqueidentifier,
ORDEREVENTLOCATIONID uniqueidentifier,
ORDERRESOURCECATEGORYCODEID uniqueidentifier,
AMOUNT money,
TYPECODE tinyint,
APPLICATIONCODE tinyint,
BASECURRENCYID uniqueidentifier,
ORGANIZATIONAMOUNT money,
ORGANIZATIONEXCHANGERATEID uniqueidentifier,
TRANSACTIONAMOUNT money,
TRANSACTIONCURRENCYID uniqueidentifier,
BASEEXCHANGERATEID uniqueidentifier,
OVERRIDEBUSINESSUNITS bit,
REVENUESPLITBUSINESSUNITOVERRIDECODEID uniqueidentifier
)
as begin
if @INFORMATIONSOURCECODE = 0
begin
declare @BASECURRENCYID uniqueidentifier;
declare @ORGEXCHANGERATEID uniqueidentifier;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
declare @BASEEXCHANGERATEID uniqueidentifier;
select @BASECURRENCYID = CURRENCYSET.BASECURRENCYID
,@ORGEXCHANGERATEID = FINANCIALTRANSACTION.ORGEXCHANGERATEID
,@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
,@BASEEXCHANGERATEID = FINANCIALTRANSACTION.BASEEXCHANGERATEID
from dbo.FINANCIALTRANSACTION
inner join dbo.PDACCOUNTSYSTEM on FINANCIALTRANSACTION.PDACCOUNTSYSTEMID = PDACCOUNTSYSTEM.ID
inner join dbo.CURRENCYSET on PDACCOUNTSYSTEM.CURRENCYSETID = CURRENCYSET.ID
where FINANCIALTRANSACTION.ID = @REVENUEID;
insert into @RETVAL
(
ID,
REVENUEID,
DESIGNATIONID,
EVENTID,
PROGRAMID,
CATEGORYCODEID,
MEMBERSHIPLEVELID,
ORDERMEMBERSHIPLEVELID,
ORDERMERCHANDISEPRODUCTINSTANCEID,
ORDERMERCHANDISEDEPARTMENTID,
ORDEREVENTID,
ORDERPROGRAMID,
ORDERFEEID,
ORDERTAXID,
ORDEREVENTLOCATIONID,
ORDERRESOURCECATEGORYCODEID,
AMOUNT,
TYPECODE,
APPLICATIONCODE,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
OVERRIDEBUSINESSUNITS,
REVENUESPLITBUSINESSUNITOVERRIDECODEID
)
select REVENUESPLIT.ID,
FTLI.FINANCIALTRANSACTIONID,
REVENUESPLIT.DESIGNATIONID,
REGISTRANT.EVENTID,
EVENT.PROGRAMID,
REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID,
MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
REVENUESPLITORDER.MEMBERSHIPLEVELID,
REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID,
MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTID,
REVENUESPLITORDER.EVENTID,
REVENUESPLITORDER.PROGRAMID,
REVENUESPLITORDER.FEEID,
REVENUESPLITORDER.TAXID,
REVENUESPLITORDER.EVENTLOCATIONID,
RESOURCE.RESOURCECATEGORYCODEID,
FTLI.BASEAMOUNT,
REVENUESPLIT.TYPECODE,
REVENUESPLIT.APPLICATIONCODE,
@BASECURRENCYID BASECURRENCYID,
FTLI.ORGAMOUNT,
@ORGEXCHANGERATEID,
FTLI.TRANSACTIONAMOUNT,
@TRANSACTIONCURRENCYID,
@BASEEXCHANGERATEID,
REVENUESPLIT.OVERRIDEBUSINESSUNITS,
REVENUESPLIT.REVENUESPLITBUSINESSUNITOVERRIDECODEID
from dbo.REVENUESPLIT_EXT REVENUESPLIT
inner join dbo.FINANCIALTRANSACTIONLINEITEM FTLI on REVENUESPLIT.ID = FTLI.ID
left outer join dbo.REVENUESPLITORDER
on REVENUESPLITORDER.ID = REVENUESPLIT.ID
left outer join dbo.MERCHANDISEPRODUCTINSTANCE
on MERCHANDISEPRODUCTINSTANCE.ID = REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID
left outer join dbo.MERCHANDISEPRODUCT
on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
left outer join dbo.REVENUECATEGORY
on REVENUECATEGORY.ID = REVENUESPLIT.ID
--Membership recurring gift payment Membership Level Resolution
left join dbo.RECURRINGGIFTACTIVITY RA on RA.PAYMENTREVENUEID = REVENUESPLIT.ID and REVENUESPLIT.APPLICATIONCODE = 3 and REVENUESPLIT.TYPECODE = 2
left join (
select
RGLI.ID,
RGLI.FINANCIALTRANSACTIONID
from dbo.FINANCIALTRANSACTIONLINEITEM RGLI
inner join dbo.REVENUESPLIT_EXT RGRS on RGRS.ID = RGLI.ID
where RGLI.DELETEDON is null and RGLI.TYPECODE != 1
) RGLI on --Recurring gift line item
RGLI.FINANCIALTRANSACTIONID = RA.SOURCEREVENUEID
--Membership installment plan Membership Level Resolution
outer apply (
select top 1 [MIPLI].ID --The split could be applied to several installments
from dbo.INSTALLMENTPAYMENT with (nolock)
inner join dbo.FINANCIALTRANSACTIONLINEITEM as [MIPLI] on --Membership installment plan line item
INSTALLMENTPAYMENT.PLEDGEID = [MIPLI].FINANCIALTRANSACTIONID and
[MIPLI].DELETEDON is null and
[MIPLI].TYPECODE != 1 --1: Reversal
inner join dbo.REVENUESPLIT_EXT as [MIPLI_EXT] on
[MIPLI].ID = [MIPLI_EXT].ID and
[MIPLI_EXT].APPLICATIONCODE = 5 and --Membership
[MIPLI_EXT].TYPECODE = 2 --Membership
where
REVENUESPLIT.ID = INSTALLMENTPAYMENT.PAYMENTID and
REVENUESPLIT.APPLICATIONCODE = 19 and --Membership installment plan
REVENUESPLIT.TYPECODE = 2 --Membership
) [MEMBERSHIPINSTALLMENTLI]
left outer join dbo.MEMBERSHIPTRANSACTION
on MEMBERSHIPTRANSACTION.REVENUESPLITID = coalesce(RGLI.ID, [MEMBERSHIPINSTALLMENTLI].ID, REVENUESPLIT.ID)
left outer join dbo.EVENTREGISTRANTPAYMENT
on EVENTREGISTRANTPAYMENT.PAYMENTID = REVENUESPLIT.ID
left outer join dbo.REGISTRANT
on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
left outer join dbo.EVENT
on EVENT.ID = REGISTRANT.EVENTID
left outer join dbo.RESOURCE
on RESOURCE.ID = REVENUESPLITORDER.RESOURCEID
where REVENUESPLIT.ID = @REVENUESPLITID and FTLI.DELETEDON is null and FTLI.TYPECODE != 1;
end
else if @INFORMATIONSOURCECODE = 1
begin
-- If the revenue type is pledge then we need to use the older version of the splits function
if ((select TYPECODE from dbo.BATCHREVENUE where ID = @REVENUEID) = 1)
begin
insert into @RETVAL
(
ID,
REVENUEID,
DESIGNATIONID,
EVENTID,
PROGRAMID,
CATEGORYCODEID,
MEMBERSHIPLEVELID,
ORDERMEMBERSHIPLEVELID,
ORDERMERCHANDISEPRODUCTINSTANCEID,
ORDEREVENTID,
ORDERRESOURCECATEGORYCODEID,
AMOUNT,
TYPECODE,
APPLICATIONCODE,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
OVERRIDEBUSINESSUNITS,
REVENUESPLITBUSINESSUNITOVERRIDECODEID
)
select APPLICATIONS.ID,
@REVENUEID,
APPLICATIONS.DESIGNATIONID,
null,
null,
null,
null,
null,
null,
null,
null,
APPLICATIONS.AMOUNT,
APPLICATIONS.TYPECODE,
0,
null,
null,
null,
null,
null,
null,
0,
null
from dbo.UFN_REVENUEBATCH_GETSPLITS(@REVENUEID) as APPLICATIONS
where APPLICATIONS.ID = @REVENUESPLITID;
end
else
begin
-- Get the applications
-- but first check to see if it's a single payment application
declare @SINGLEAPPLICATIONID uniqueidentifier;
declare @APPLICATIONTYPECODE tinyint;
select
@SINGLEAPPLICATIONID = SINGLEAPPLICATIONID,
@APPLICATIONTYPECODE = APPLICATIONTYPECODE
from dbo.UFN_REVENUEBATCH_GETSINGLEAPPLICATIONINFO(@REVENUEID);
if @SINGLEAPPLICATIONID is null
begin
insert into @RETVAL
(
ID,
REVENUEID,
DESIGNATIONID,
EVENTID,
PROGRAMID,
CATEGORYCODEID,
MEMBERSHIPLEVELID,
ORDERMEMBERSHIPLEVELID,
ORDERMERCHANDISEPRODUCTINSTANCEID,
ORDERMERCHANDISEDEPARTMENTID,
ORDEREVENTID,
ORDERPROGRAMID,
ORDERFEEID,
ORDERTAXID,
ORDEREVENTLOCATIONID,
ORDERRESOURCECATEGORYCODEID,
AMOUNT,
TYPECODE,
APPLICATIONCODE,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
OVERRIDEBUSINESSUNITS,
REVENUESPLITBUSINESSUNITOVERRIDECODEID
)
select APPLICATIONSWITHCHILDREN.ID,
@REVENUEID,
case when (APPLICATIONSWITHCHILDREN.TYPECODE = 1) and
(not APPLICATIONSWITHCHILDREN.PLEDGES is null) then
-- When this is a pledge payment we need to get the designation from the pledge
-- grab the designation from the first split.
(select top 1 T.c.value('(DESIGNATIONID)[1]','uniqueidentifier')
from APPLICATIONSWITHCHILDREN.PLEDGES.nodes('(((/ITEM)[1]/SPLITS)[1]/ITEM)[1]') T(c))
else
case when (APPLICATIONSWITHCHILDREN.TYPECODE = 1) then
(select top 1 DESIGNATIONID from REVENUESPLIT T1 join BATCHREVENUEAPPLICATION T2 on T1.REVENUEID = T2.REVENUEID where T2.BATCHREVENUEID = @REVENUEID and T2.ID = @REVENUESPLITID)
else
null
end
end,
isnull(REGISTRANT.EVENTID, BATCHREVENUEREGISTRANT.EVENTID),
isnull(REGISTRANTEVENT.PROGRAMID, BATCHREVENUEREGISTRANTEVENT.PROGRAMID),
null, --CATEGORYCODEID,
isnull(BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPID,(select top 1 MEMBERSHIPLEVELID from dbo.UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN(@REVENUEID) as APPWITHCHILDREN
left outer join dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP on BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPID = APPWITHCHILDREN.APPLICATIONID where APPWITHCHILDREN.ID = @REVENUESPLITID)),
null,
null,
null,
null,
null,
null,
null,
null,
null,
APPLICATIONSWITHCHILDREN.APPLIED,
case APPLICATIONSWITHCHILDREN.TYPECODE
when 6 then 1
when 5 then 2
when 3 then 9
when 1 then 0
when 2 then -- Recurring gift payment, should be marked as a payment if not sponsorship
case
when APPLICATIONSWITHCHILDREN.SPONSORSHIPOPPORTUNITY is not null then 9 -- sponsorship RG
when APPLICATIONSWITHCHILDREN.SPONSORSHIPRGADDITIONALGIFT = 1 then 17 -- sponsorship recurring additional gift
else 0
end
when 10 then 0 -- Donor challenge payment, should be marked as a payment
-- TODO: Other types
end,
case APPLICATIONSWITHCHILDREN.TYPECODE
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
else 99
end,
null,
null,
null,
null,
null,
null,
null,
null
from dbo.UFN_REVENUEBATCH_GETAPPLICATIONSWITHCHILDREN(@REVENUEID) as APPLICATIONSWITHCHILDREN
left outer join dbo.REGISTRANT
on APPLICATIONSWITHCHILDREN.APPLICATIONID = REGISTRANT.ID
left outer join dbo.BATCHREVENUEREGISTRANT
on APPLICATIONSWITHCHILDREN.APPLICATIONID = BATCHREVENUEREGISTRANT.ID
left outer join dbo.EVENT as REGISTRANTEVENT
on REGISTRANT.EVENTID = REGISTRANTEVENT.ID
left outer join dbo.EVENT as BATCHREVENUEREGISTRANTEVENT
on BATCHREVENUEREGISTRANT.EVENTID = BATCHREVENUEREGISTRANTEVENT.ID
left outer join dbo.BATCHREVENUEAPPLICATIONMEMBERSHIP
on BATCHREVENUEAPPLICATIONMEMBERSHIP.ID = APPLICATIONSWITHCHILDREN.APPLICATIONID
where APPLICATIONSWITHCHILDREN.ID = @REVENUESPLITID;
end
else -- Get the single payment application where the applicationinfo field holds the key to the application
begin
insert into @RETVAL
(
ID,
REVENUEID,
DESIGNATIONID,
EVENTID,
PROGRAMID,
CATEGORYCODEID,
MEMBERSHIPLEVELID,
ORDERMEMBERSHIPLEVELID,
ORDERMERCHANDISEPRODUCTINSTANCEID,
ORDERMERCHANDISEDEPARTMENTID,
ORDEREVENTID,
ORDERPROGRAMID,
ORDERFEEID,
ORDERTAXID,
ORDEREVENTLOCATIONID,
ORDERRESOURCECATEGORYCODEID,
AMOUNT,
TYPECODE,
APPLICATIONCODE,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
OVERRIDEBUSINESSUNITS,
REVENUESPLITBUSINESSUNITOVERRIDECODEID
)
select REVENUESPLIT.ID,
REVENUESPLIT.REVENUEID,
REVENUESPLIT.DESIGNATIONID,
REGISTRANT.EVENTID,
EVENT.PROGRAMID,
REVENUECATEGORY.GLREVENUECATEGORYMAPPINGID,
MEMBERSHIPTRANSACTION.MEMBERSHIPLEVELID,
REVENUESPLITORDER.MEMBERSHIPLEVELID,
REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID,
MERCHANDISEPRODUCT.MERCHANDISEDEPARTMENTID,
REVENUESPLITORDER.EVENTID,
REVENUESPLITORDER.PROGRAMID,
REVENUESPLITORDER.FEEID,
REVENUESPLITORDER.TAXID,
REVENUESPLITORDER.EVENTLOCATIONID,
RESOURCE.RESOURCECATEGORYCODEID,
REVENUESPLIT.AMOUNT,
REVENUESPLIT.TYPECODE,
case @APPLICATIONTYPECODE
when 5 then 2 -- pledge payment
when 4 then 3 -- recurring gift payment
when 8 then 7 -- matching gift payment
when 6 then 6 -- planned gift payment
when 10 then 13 -- Donor challenge payment
when 1 then 3 -- sponsorship payment
when 2 then 5 -- membership payment
when 3 then 10 -- order payment
when 9 then 8 -- grant award payment
else 0 end as APPLICATIONCODE,
REVENUESPLIT.BASECURRENCYID,
REVENUESPLIT.ORGANIZATIONAMOUNT,
REVENUESPLIT.ORGANIZATIONEXCHANGERATEID,
REVENUESPLIT.TRANSACTIONAMOUNT,
REVENUESPLIT.TRANSACTIONCURRENCYID,
REVENUESPLIT.BASEEXCHANGERATEID,
REVENUESPLIT.OVERRIDEBUSINESSUNITS,
REVENUESPLIT.REVENUESPLITBUSINESSUNITOVERRIDECODEID
from dbo.REVENUESPLIT
left outer join dbo.REVENUESPLITORDER
on REVENUESPLITORDER.ID = REVENUESPLIT.ID
left outer join dbo.EVENT as ORDEREVENT
on ORDEREVENT.ID = REVENUESPLITORDER.EVENTID
left outer join dbo.MERCHANDISEPRODUCTINSTANCE
on MERCHANDISEPRODUCTINSTANCE.ID = REVENUESPLITORDER.MERCHANDISEPRODUCTINSTANCEID
left outer join dbo.MERCHANDISEPRODUCT
on MERCHANDISEPRODUCT.ID = MERCHANDISEPRODUCTINSTANCE.MERCHANDISEPRODUCTID
left outer join dbo.REVENUECATEGORY
on REVENUECATEGORY.ID = REVENUESPLIT.ID
left outer join dbo.MEMBERSHIPTRANSACTION
on MEMBERSHIPTRANSACTION.REVENUESPLITID = REVENUESPLIT.ID
left outer join dbo.EVENTREGISTRANTPAYMENT
on EVENTREGISTRANTPAYMENT.PAYMENTID = REVENUESPLIT.ID
left outer join dbo.REGISTRANT
on REGISTRANT.ID = EVENTREGISTRANTPAYMENT.REGISTRANTID
left outer join dbo.EVENT
on EVENT.ID = REGISTRANT.EVENTID
left outer join dbo.RESOURCE
on RESOURCE.ID = REVENUESPLITORDER.RESOURCEID
where REVENUESPLIT.ID = @REVENUESPLITID;
end
-- Get the additional applications
insert into @RETVAL
(
ID,
REVENUEID,
DESIGNATIONID,
EVENTID,
PROGRAMID,
CATEGORYCODEID,
MEMBERSHIPLEVELID,
ORDERMEMBERSHIPLEVELID,
ORDERMERCHANDISEPRODUCTINSTANCEID,
ORDEREVENTID,
ORDERRESOURCECATEGORYCODEID,
AMOUNT,
TYPECODE,
APPLICATIONCODE,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
OVERRIDEBUSINESSUNITS,
REVENUESPLITBUSINESSUNITOVERRIDECODEID
)
select ID,
@REVENUEID,
DESIGNATIONID,
null,
null,
CATEGORYCODEID,
null,
null,
null,
null,
null,
APPLIED,
case TYPECODE
when 0 then 0
when 1 then 4
when 2 then 0
when 3 then 9
end,
case TYPECODE
when 0 then 0
when 1 then 4
when 2 then 7
when 3 then 0
end,
null,
null,
null,
null,
null,
null,
null,
null
from dbo.UFN_REVENUEBATCH_GETADDITIONALAPPLICATIONS(@REVENUEID) as ADDITIONALAPPLICATIONS
where ADDITIONALAPPLICATIONS.ID = @REVENUESPLITID;
-- Insert the other applications using the old version that are not part of the new process
insert into @RETVAL
(
ID,
REVENUEID,
DESIGNATIONID,
EVENTID,
PROGRAMID,
CATEGORYCODEID,
MEMBERSHIPLEVELID,
ORDERMEMBERSHIPLEVELID,
ORDERMERCHANDISEPRODUCTINSTANCEID,
ORDEREVENTID,
ORDERRESOURCECATEGORYCODEID,
AMOUNT,
TYPECODE,
APPLICATIONCODE,
BASECURRENCYID,
ORGANIZATIONAMOUNT,
ORGANIZATIONEXCHANGERATEID,
TRANSACTIONAMOUNT,
TRANSACTIONCURRENCYID,
BASEEXCHANGERATEID,
OVERRIDEBUSINESSUNITS,
REVENUESPLITBUSINESSUNITOVERRIDECODEID
)
select ID,
@REVENUEID,
DESIGNATIONID,
null,
null,
null,
null,
null,
null,
null,
null,
AMOUNT,
TYPECODE,
APPLICATIONCODE,
null,
null,
null,
null,
null,
null,
null,
null
from dbo.UFN_REVENUEBATCH_GETSPLITS(@REVENUEID) as OLDAPPLICATIONS
where (OLDAPPLICATIONS.ID = @REVENUESPLITID) and
(not OLDAPPLICATIONS.ID in (select ID from @RETVAL));
end
end
return;
end