UFN_REVENUESPLITINFORMATION_ALL
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @INFORMATIONSOURCECODE | tinyint | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN |
Definition
Copy
CREATE function dbo.UFN_REVENUESPLITINFORMATION_ALL
(
@REVENUEID 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
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,
REVENUESPLIT.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.REVENUEID = @REVENUEID
else if @INFORMATIONSOURCECODE = 1
begin
--Revenue Basic stores data in the splits table
declare @isRevenueBasic bit = 0
if exists(select 1 from dbo.BATCHREVENUE
inner join dbo.BATCH on BATCHREVENUE.BATCHID = BATCH.ID
inner join dbo.BATCHTEMPLATE on BATCHTEMPLATE.ID = BATCH.BATCHTEMPLATEID
where BATCHREVENUE.ID = @REVENUEID and BATCHTEMPLATE.BATCHTYPECATALOGID = '1a6ca8f4-5444-4db1-a2b0-72efe6219df2') -- Revenue Basic Batch Type ID
set @isRevenueBasic = 1;
if (@isRevenueBasic = 1 or (select TYPECODE from dbo.BATCHREVENUE where ID = @REVENUEID) <> 0)
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,
(select GLREVENUECATEGORYMAPPINGID
from BATCHREVENUE
where ID = @REVENUEID),
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
end
else
begin
-- 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
-- Get the revenue streams
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 = APPLICATIONSWITHCHILDREN.ID)
when (APPLICATIONSWITHCHILDREN.TYPECODE in (2, 3, 4, 6, 9)) then
(select top 1 RS.DESIGNATIONID
from FINANCIALTRANSACTIONLINEITEM LI inner join
REVENUESPLIT_EXT RS on LI.ID = RS.ID
where LI.FINANCIALTRANSACTIONID = APPLICATIONSWITHCHILDREN.APPLICATIONID)
else
null
end
end,
isnull(REGISTRANT.EVENTID, BATCHREVENUEREGISTRANT.EVENTID),
isnull(REGISTRANTEVENT.PROGRAMID, BATCHREVENUEREGISTRANTEVENT.PROGRAMID),
APPLICATIONSWITHCHILDREN.CATEGORYCODEID,
BATCHREVENUEAPPLICATIONMEMBERSHIP.MEMBERSHIPID,
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 0 -- Recurring gift payment, should be marked as a payment
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.APPLIED > 0
-- Collections are not sync with single payment so try and pull information from the batch revenue row
if not exists(select 1 from @RETVAL)
begin
declare @SINGLEAPPLICATIONID uniqueidentifier;
declare @APPLICATIONTYPECODE tinyint;
select
@SINGLEAPPLICATIONID = SINGLEAPPLICATIONID,
@APPLICATIONTYPECODE = APPLICATIONTYPECODE
from dbo.UFN_REVENUEBATCH_GETSINGLEAPPLICATIONINFO(@REVENUEID);
if @SINGLEAPPLICATIONID is not null
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.REVENUEID = @SINGLEAPPLICATIONID
end
end
end
return;
end