USP_KPI_DESIGNATIONLEVEL_REVENUETOTAL
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @VALUE | money | INOUT | |
| @DESIGNATIONLEVELID | uniqueidentifier | IN | |
| @APPEALID | uniqueidentifier | IN | |
| @BUSINESSUNITCODEID | uniqueidentifier | IN | |
| @APPEALREPORTCODE1ID | uniqueidentifier | IN | |
| @STARTDATE | datetime | IN | |
| @ASOFDATE | datetime | IN | |
| @ORGPOSITIONSSELECTIONID | uniqueidentifier | IN | |
| @CURRENCYID | uniqueidentifier | IN |
Definition
Copy
CREATE procedure dbo.USP_KPI_DESIGNATIONLEVEL_REVENUETOTAL
@VALUE money output,
@DESIGNATIONLEVELID uniqueidentifier,
@APPEALID uniqueidentifier = null,
@BUSINESSUNITCODEID uniqueidentifier = null,
@APPEALREPORTCODE1ID uniqueidentifier = null,
@STARTDATE datetime = null,
@ASOFDATE datetime,
@ORGPOSITIONSSELECTIONID uniqueidentifier = null,
@CURRENCYID uniqueidentifier = null
as
set nocount on;
declare @RECEIVED money;
declare @PLEDGEBALANCE money;
select @CURRENCYID = BASECURRENCYID
from dbo.DESIGNATIONLEVEL
where ID = @DESIGNATIONLEVELID;
if @ORGPOSITIONSSELECTIONID is null
begin
with DESIGNATIONS_CTE as (
select ID
from dbo.DESIGNATION D
where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
(D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID)
)
select @RECEIVED =
coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID,@CURRENCYID)), 0)
from dbo.REVENUESPLIT RDS
inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
left join dbo.APPEAL A on R.APPEALID = A.ID
left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = RDS.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
where
(R.DATE >= @STARTDATE or @STARTDATE is null) and
(R.DATE <= @ASOFDATE or @ASOFDATE is null) and
(A.ID = @APPEALID or @APPEALID is null) and
(A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
R.TRANSACTIONTYPECODE = 0;
with DESIGNATIONS_CTE as (
select ID
from dbo.DESIGNATION D
where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
(D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID)
)
select @PLEDGEBALANCE =
coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID,@CURRENCYID)), 0)
-
(
coalesce(( --Subtract payments of the above pledges
select coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYSPLIT.ID,@CURRENCYID)), 0)
from dbo.REVENUESPLIT PAYSPLIT
inner join DESIGNATIONS_CTE PAYD on PAYSPLIT.DESIGNATIONID = PAYD.ID
inner join dbo.REVENUE PAY on PAY.ID = PAYSPLIT.REVENUEID
left join dbo.APPEAL PAYAPP on PAY.APPEALID = PAYAPP.ID
left join dbo.REVENUESPLITBUSINESSUNIT PAYRSB on PAYRSB.REVENUESPLITID = PAYSPLIT.ID and PAYRSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
where
PAYSPLIT.ID IN
(SELECT IP.PAYMENTID
FROM dbo.INSTALLMENTPAYMENT IP
inner join dbo.REVENUE R on IP.PLEDGEID = R.ID
left join dbo.APPEAL A on R.APPEALID = A.ID
inner join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = RS.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
inner join DESIGNATIONS_CTE RD on RS.DESIGNATIONID = RD.ID
WHERE (R.DATE >= @STARTDATE or @STARTDATE is null) and
(R.DATE <= @ASOFDATE or @ASOFDATE is null) and
(A.ID = @APPEALID or @APPEALID is null) and
(A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
and R.TRANSACTIONTYPECODE <> 3) and
(PAY.DATE >= @STARTDATE or @STARTDATE is null) and
(PAY.DATE <= @ASOFDATE or @ASOFDATE is null) and
(PAYAPP.ID = @APPEALID or @APPEALID is null) and
(PAYAPP.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(PAYRSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
), 0)
+
coalesce(( --Subtract Writeoffs of the above pledges
select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID,@CURRENCYID))
from dbo.WRITEOFFSPLIT WOS
inner join DESIGNATIONS_CTE WOSD on WOS.DESIGNATIONID = WOSD.ID
inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
inner join dbo.REVENUE R on WO.REVENUEID = R.ID
left join dbo.APPEAL A on R.APPEALID = A.ID
where
(R.DATE >= @STARTDATE or @STARTDATE is null) and
(R.DATE <= @ASOFDATE or @ASOFDATE is null) and
(A.ID = @APPEALID or @APPEALID is null) and
(A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
--(A.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
(@BUSINESSUNITCODEID in
(
select distinct
BUSINESSUNITCODEID
from dbo.REVENUESPLITBUSINESSUNIT
inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = REVENUESPLITBUSINESSUNIT.REVENUESPLITID
where REVENUESPLIT.REVENUEID = R.ID
) or
@BUSINESSUNITCODEID is null) and
R.TRANSACTIONTYPECODE in (1,8)
), 0))
from dbo.REVENUESPLIT RDS
inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
left join dbo.APPEAL A on R.APPEALID = A.ID
left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = RDS.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
where
(R.DATE >= @STARTDATE or @STARTDATE is null) and
(R.DATE <= @ASOFDATE or @ASOFDATE is null) and
(A.ID = @APPEALID or @APPEALID is null) and
(A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
R.TRANSACTIONTYPECODE in (1,8);
end
else
begin
declare @IDS as table(ID uniqueidentifier);
insert into @IDS exec USP_ORGANIZATIONHIERARCHY_IDSET_POSITIONHOLDERS @ORGPOSITIONSSELECTIONID, null;
with DESIGNATIONS_CTE as (
select ID
from dbo.DESIGNATION D
where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
(D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID)
)
select @RECEIVED =
coalesce (sum(dbo.UFN_REVENUESOLICITOR_GETAMOUNTINCURRENCY(RSOL.ID,@CURRENCYID)), 0)
from dbo.REVENUESPLIT RDS
inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
inner join dbo.REVENUESOLICITOR RSOL on RDS.ID = RSOL.REVENUESPLITID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
on OPH.CONSTITUENTID = RSOL.CONSTITUENTID and R.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, R.DATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
left join dbo.APPEAL A on R.APPEALID = A.ID
left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = RDS.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
where
(R.DATE >= @STARTDATE or @STARTDATE is null) and
(R.DATE <= @ASOFDATE or @ASOFDATE is null) and
(A.ID = @APPEALID or @APPEALID is null) and
(A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
R.TRANSACTIONTYPECODE = 0;
with DESIGNATIONS_CTE as (
select ID
from dbo.DESIGNATION D
where D.DESIGNATIONLEVEL1ID = @DESIGNATIONLEVELID or
(D.DESIGNATIONLEVEL2ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL3ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL4ID = @DESIGNATIONLEVELID) or
(D.DESIGNATIONLEVEL5ID = @DESIGNATIONLEVELID)
)
select @PLEDGEBALANCE =
(coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(RDS.ID,@CURRENCYID)), 0)
-
(
coalesce(( --Subtract payments of the above pledges
select coalesce(sum(dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY(PAYSPLIT.ID,@CURRENCYID)), 0)
from dbo.REVENUESPLIT PAYSPLIT
inner join DESIGNATIONS_CTE PAYD on PAYSPLIT.DESIGNATIONID = PAYD.ID
inner join dbo.REVENUE PAY on PAY.ID = PAYSPLIT.REVENUEID
inner join dbo.REVENUESOLICITOR RSOL on PAYSPLIT.ID = RSOL.REVENUESPLITID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
on OPH.CONSTITUENTID = RSOL.CONSTITUENTID and PAY.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, PAY.DATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
left join dbo.APPEAL PAYAPP on PAY.APPEALID = PAYAPP.ID
left join dbo.REVENUESPLITBUSINESSUNIT PAYRSB on PAYRSB.REVENUESPLITID = PAYSPLIT.ID and PAYRSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
where
PAYSPLIT.ID IN
(SELECT IP.PAYMENTID
FROM dbo.INSTALLMENTPAYMENT IP
inner join dbo.REVENUE R on IP.PLEDGEID = R.ID
left join dbo.APPEAL A on R.APPEALID = A.ID
inner join dbo.REVENUESPLIT RS on R.ID = RS.REVENUEID
left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = RS.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
inner join DESIGNATIONS_CTE RD on RS.DESIGNATIONID = RD.ID
WHERE (R.DATE >= @STARTDATE or @STARTDATE is null) and
(R.DATE <= @ASOFDATE or @ASOFDATE is null) and
(A.ID = @APPEALID or @APPEALID is null) and
(A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
R.TRANSACTIONTYPECODE <> 3) and
(PAY.DATE >= @STARTDATE or @STARTDATE is null) and
(PAY.DATE <= @ASOFDATE or @ASOFDATE is null) and
(PAYAPP.ID = @APPEALID or @APPEALID is null) and
(PAYAPP.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(PAYRSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null)
), 0)
+
coalesce(( --Subtract Writeoffs of the above pledges
select sum(dbo.UFN_WRITEOFFSPLIT_GETAMOUNTINCURRENCY(WOS.ID,@CURRENCYID))
from dbo.WRITEOFFSPLIT WOS
inner join DESIGNATIONS_CTE WOSD on WOS.DESIGNATIONID = WOSD.ID
inner join dbo.WRITEOFF WO on WOS.WRITEOFFID = WO.ID
inner join dbo.REVENUE R on WO.REVENUEID = R.ID
inner join dbo.REVENUESPLIT on R.ID = REVENUESPLIT.REVENUEID
inner join dbo.REVENUESOLICITOR RSOL on REVENUESPLIT.ID = RSOL.REVENUESPLITID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
on OPH.CONSTITUENTID = RSOL.CONSTITUENTID and R.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, R.DATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
left join dbo.APPEAL A on R.APPEALID = A.ID
left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = REVENUESPLIT.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
where
(R.DATE >= @STARTDATE or @STARTDATE is null) and
(R.DATE <= @ASOFDATE or @ASOFDATE is null) and
(A.ID = @APPEALID or @APPEALID is null) and
(A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
R.TRANSACTIONTYPECODE in (1,8)
), 0) ))
from dbo.REVENUESPLIT RDS
inner join DESIGNATIONS_CTE D on RDS.DESIGNATIONID = D.ID
inner join dbo.REVENUE R on R.ID = RDS.REVENUEID
inner join dbo.REVENUESOLICITOR RSOL on RDS.ID = RSOL.REVENUESPLITID
inner join dbo.ORGANIZATIONPOSITIONHOLDER OPH
on OPH.CONSTITUENTID = RSOL.CONSTITUENTID and R.DATE between OPH.DATEFROM and coalesce(OPH.DATETO, R.DATE)
inner join @IDS as SELECTION on OPH.[ID] = SELECTION.[ID]
left join dbo.APPEAL A on R.APPEALID = A.ID
left join dbo.REVENUESPLITBUSINESSUNIT RSB on RSB.REVENUESPLITID = RDS.ID and RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID
where
(R.DATE >= @STARTDATE or @STARTDATE is null) and
(R.DATE <= @ASOFDATE or @ASOFDATE is null) and
(A.ID = @APPEALID or @APPEALID is null) and
(A.APPEALREPORT1CODEID = @APPEALREPORTCODE1ID or @APPEALREPORTCODE1ID is null) and
(RSB.BUSINESSUNITCODEID = @BUSINESSUNITCODEID or @BUSINESSUNITCODEID is null) and
R.TRANSACTIONTYPECODE in (1,8);
end
set @VALUE = coalesce(@RECEIVED, 0) + coalesce(@PLEDGEBALANCE, 0);