USP_DATALIST_ACCOUNTDISTRIBUTIONREPORT
Returns account distribution information for posted revenue records.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @IDSETREGISTERID | uniqueidentifier | IN | Selected revenue |
| @FROM | datetime | IN | From |
| @TO | datetime | IN | To |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_ACCOUNTDISTRIBUTIONREPORT
(
@IDSETREGISTERID uniqueidentifier = null,
@FROM datetime,
@TO datetime
)
as
set nocount on;
begin try
if @IDSETREGISTERID is null
begin
set @FROM = dbo.[UFN_DATE_GETEARLIESTTIME](@FROM);
set @TO = dbo.[UFN_DATE_GETLATESTTIME](@TO);
with REVENUE_CTE (REVENUEID, DATE, CONSTITUENTNAME)as (
select
REVENUE.ID as REVENUEID,
REVENUE.DATE,
CONSTITUENT.NAME as CONSTITUENTNAME
from
dbo.REVENUE
inner join
dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
where
REVENUE.DATE between @FROM and @TO and
REVENUE.TRANSACTIONTYPECODE not in (
2,--Recurring gift
3,--Matching gift claim
8--Donor challenge
)
)
select distinct
CTE.REVENUEID,
CTE.DATE,
MAP.DESCRIPTION,
CTE.CONSTITUENTNAME as CONSTITUENTNAME,
tf.AMOUNT,
tf.ACCOUNT,
tf.DEBITCREDIT,
tf.TRANSACTIONTYPECODE
from
REVENUE_CTE CTE
outer apply
dbo.UFN_REVENUE_GETGLDISTRIBUTION(CTE.REVENUEID) as tf
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
union all
select distinct
CTE.REVENUEID,
CTE.DATE,
MAP.DESCRIPTION,
CTE.CONSTITUENTNAME as CONSTITUENTNAME,
tf.AMOUNT,
tf.ACCOUNT,
tf.DEBITCREDIT,
tf.TRANSACTIONTYPECODE
from
REVENUE_CTE CTE
inner join
dbo.REVENUEPAYMENTMETHOD on CTE.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
outer apply
dbo.UFN_REVENUE_GETSTOCKDETAILGLDISTRIBUTION(REVENUEPAYMENTMETHOD.ID) as tf
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
union all
select distinct
CTE.REVENUEID,
CTE.DATE,
MAP.DESCRIPTION,
CTE.CONSTITUENTNAME as CONSTITUENTNAME,
tf.AMOUNT,
tf.ACCOUNT,
tf.DEBITCREDIT,
tf.TRANSACTIONTYPECODE
from
REVENUE_CTE CTE
inner join
dbo.REVENUEPAYMENTMETHOD on CTE.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
outer apply
dbo.UFN_REVENUE_GETPROPERTYDETAILGLDISTRIBUTION(REVENUEPAYMENTMETHOD.ID) as tf
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
union all
select distinct
CTE.REVENUEID,
CTE.DATE,
MAP.DESCRIPTION,
CTE.CONSTITUENTNAME as CONSTITUENTNAME,
tf.AMOUNT,
tf.ACCOUNT,
tf.DEBITCREDIT,
tf.TRANSACTIONTYPECODE
from
REVENUE_CTE CTE
inner join
dbo.WRITEOFF on CTE.REVENUEID = WRITEOFF.REVENUEID
outer apply
dbo.UFN_REVENUE_GETWRITEOFFGLDISTRIBUTION(WRITEOFF.ID) as tf
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
order by
CTE.DATE,CTE.CONSTITUENTNAME,CTE.REVENUEID,MAP.DESCRIPTION,tf.DEBITCREDIT desc
end
else
begin
set @FROM = dbo.[UFN_DATE_GETEARLIESTTIME](@FROM);
set @TO = dbo.[UFN_DATE_GETLATESTTIME](@TO);
with REVENUE_CTE (REVENUEID, DATE, CONSTITUENTNAME)as (
select
REVENUE.ID as REVENUEID,
REVENUE.DATE,
CONSTITUENT.NAME as CONSTITUENTNAME
from
dbo.REVENUE
inner join
dbo.UFN_IDSETREADER_GETRESULTS_GUID(@IDSETREGISTERID) SELECTION on REVENUE.ID = SELECTION.ID and @IDSETREGISTERID is not null
inner join
dbo.CONSTITUENT on REVENUE.CONSTITUENTID = CONSTITUENT.ID
where
REVENUE.DATE between @FROM and @TO and
REVENUE.TRANSACTIONTYPECODE not in (
2,--Recurring gift
3,--Matching gift claim
8--Donor challenge
)
)
select distinct
CTE.REVENUEID,
CTE.DATE,
MAP.DESCRIPTION,
CTE.CONSTITUENTNAME as CONSTITUENTNAME,
tf.AMOUNT,
tf.ACCOUNT,
tf.DEBITCREDIT,
tf.TRANSACTIONTYPECODE
from
REVENUE_CTE CTE
outer apply
dbo.UFN_REVENUE_GETGLDISTRIBUTION(CTE.REVENUEID) as tf
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
union all
select distinct
CTE.REVENUEID,
CTE.DATE,
MAP.DESCRIPTION,
CTE.CONSTITUENTNAME as CONSTITUENTNAME,
tf.AMOUNT,
tf.ACCOUNT,
tf.DEBITCREDIT,
tf.TRANSACTIONTYPECODE
from
REVENUE_CTE CTE
inner join
dbo.REVENUEPAYMENTMETHOD on CTE.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
outer apply
dbo.UFN_REVENUE_GETSTOCKDETAILGLDISTRIBUTION(REVENUEPAYMENTMETHOD.ID) as tf
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
union all
select distinct
CTE.REVENUEID,
CTE.DATE,
MAP.DESCRIPTION,
CTE.CONSTITUENTNAME as CONSTITUENTNAME,
tf.AMOUNT,
tf.ACCOUNT,
tf.DEBITCREDIT,
tf.TRANSACTIONTYPECODE
from
REVENUE_CTE CTE
inner join
dbo.REVENUEPAYMENTMETHOD on CTE.REVENUEID = REVENUEPAYMENTMETHOD.REVENUEID
outer apply
dbo.UFN_REVENUE_GETPROPERTYDETAILGLDISTRIBUTION(REVENUEPAYMENTMETHOD.ID) as tf
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
union all
select distinct
CTE.REVENUEID,
CTE.DATE,
MAP.DESCRIPTION,
CTE.CONSTITUENTNAME as CONSTITUENTNAME,
tf.AMOUNT,
tf.ACCOUNT,
tf.DEBITCREDIT,
tf.TRANSACTIONTYPECODE
from
REVENUE_CTE CTE
inner join
dbo.WRITEOFF on CTE.REVENUEID = WRITEOFF.REVENUEID
outer apply
dbo.UFN_REVENUE_GETWRITEOFFGLDISTRIBUTION(WRITEOFF.ID) as tf
inner join
dbo.GLPAYMENTMETHODREVENUETYPEMAPPING MAP on tf.GLPAYMENTMETHODREVENUETYPEMAPPINGID = MAP.ID
order by
CTE.DATE,CTE.CONSTITUENTNAME,CTE.REVENUEID,MAP.DESCRIPTION,tf.DEBITCREDIT desc
end
end try
begin catch
exec dbo.USP_RAISE_ERROR;
return 1;
end catch
return 0;