USP_DATALIST_COMMITMENTINFO
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @COMMITMENTID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @SECURITYFEATUREID | uniqueidentifier | IN | |
| @SECURITYFEATURETYPE | tinyint | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_COMMITMENTINFO
(
@COMMITMENTID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
set nocount on;
begin
/*
* this datalist is based on UFN_CONSTITUENTUNREALIZEDREVENUE
* it's intent is to return the same shape data as UFN_CONSTITUENTUNREALIZEDREVENUE
* but based on the COMMITMENTID instead of other parameters
*/
declare @APPUSERISSYSADMIN bit = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
/*
* The site security check was removed from the initial select because it
* considerably slowed down the query for databases with large numbers of
* revenue records. Given that only a small number of records should be
* returned for a given constituent/household's unrealized revenue, it was
* quicker to insert all the records into a table variable, then remove any
* ones the user did not have site access to.
*/
declare @REVENUECOMMITMENTS table(
REVENUEID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
CONSTITUENTNAME nvarchar(255),
APPLIED money,
BALANCE money,
AMOUNTDUE money,
DATEDUE datetime,
REVENUETYPECODE tinyint,
DESCRIPTION nvarchar(255),
SORTORDER int,
DESIGNATIONLIST nvarchar(500),
SPONSORSHIPOPPORTUNITY nvarchar(255),
APPLICATIONCURRENCYID uniqueidentifier,
PDACCOUNTSYSTEMID uniqueidentifier,
SPONSORSHIPRGADDITIONALGIFT bit,
MEMBERSHIPPROGRAMNAME nvarchar(255),
MEMBERSHIPLEVELNAME nvarchar(255)
);
insert into @REVENUECOMMITMENTS
select
REVENUE.ID,
CONSTITUENT.ID,
case
when MGREVENUE.ID is not null
then
case REVENUE.TYPECODE
when 3
then CONSTITUENT.NAME + ' - ' + NF.NAME + ' (' + MGCONSTIT.LOOKUPID + ')'
else CONSTITUENT.NAME + ' - ' + MGCONSTIT.NAME
end
else CONSTITUENT.NAME
end as CONSTITUENTNAME,
0,
V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE,
INSTALLMENTBALANCE.BALANCE,
NEXTINSTALLMENT.INSTALLMENTDATE,
case REVENUE.TYPECODE
when 6 then 9
when 8 then 10
else REVENUE.TYPECODE
end, -- handle grant awards differently to avoid type collision with event registrations
'',
1 SORTORDER,
null as DESIGNATIONLIST,
null as SPONSORSHIPOPPORTUNITY,
REVENUE.TRANSACTIONCURRENCYID,
REVENUE.PDACCOUNTSYSTEMID,
0,
null as MEMBERSHIPPROGRAMNAME,
null as MEMBERSHIPLEVELNAME
from dbo.FINANCIALTRANSACTION as REVENUE
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
inner join dbo.UFN_REVENUE_GETNEXTINSTALLMENT_BULK() as NEXTINSTALLMENT on NEXTINSTALLMENT.REVENUEID = REVENUE.ID
inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
left join dbo.REVENUEMATCHINGGIFT on REVENUE.ID = REVENUEMATCHINGGIFT.ID
left join dbo.FINANCIALTRANSACTION MGREVENUE on MGREVENUE.ID = REVENUEMATCHINGGIFT.MGSOURCEREVENUEID
left join dbo.CONSTITUENT MGCONSTIT on MGCONSTIT.ID = MGREVENUE.CONSTITUENTID
left join V_QUERY_REVENUE_PLEDGEBALANCE on V_QUERY_REVENUE_PLEDGEBALANCE.ID = REVENUE.ID
left join dbo.UFN_INSTALLMENT_GETINSTALLMENTBALANCE_BULK() as INSTALLMENTBALANCE on INSTALLMENTBALANCE.ID = NEXTINSTALLMENT.INSTALLMENTID
outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(MGCONSTIT.ID) NF
where
REVENUE.ID=@COMMITMENTID
and
case
when V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE > 0
then 1
else 0
end = 1
--and REVENUESCHEDULE.ISPENDING = 0 --Isn't Pending
and( REVENUEMATCHINGGIFT.ISACTIVE = 1
or REVENUE.TYPECODE <> 3
)
and REVENUE.TYPECODE in (0,1,2,3,4,5,6,7,8,9)
and REVENUE.DELETEDON is null
and NEXTINSTALLMENT.INSTALLMENTID is not null
union all
select
REVENUE.ID,
CONSTITUENT.ID,
CONSTITUENT.NAME,
0,
REVENUE.TRANSACTIONAMOUNT, -- TRANSACTIONAMOUNT
NEXTINSTALLMENT.BALANCE,
NEXTINSTALLMENT.DATE,
REVENUE.TYPECODE,
'',
1,
null,
coalesce(SPONSORSHIPINFO.SPONSORSHIPOPPORTUNITY,null) as SPONSORSHIPOPPORTUNITY,
REVENUE.TRANSACTIONCURRENCYID,
null as PDACCOUNTSYSTEMID,
case
when exists(
select 1
from dbo.REVENUESPLIT
where REVENUEID = REVENUE.ID and TYPECODE = 17
)
then 1
else 0
end,
coalesce(MEMBERSHIPINFO.PROGRAMNAME,null) as MEMBERSHIPPROGRAMNAME,
coalesce(MEMBERSHIPINFO.LEVELNAME, null) as MEMBERSHIPLEVELNAME
from dbo.FINANCIALTRANSACTION as REVENUE
--inner join @CONSTITUENTCTE as CONSTITUENTCTE on CONSTITUENTCTE.ID = REVENUE.CONSTITUENTID
inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
left outer join(
select
SP.REVENUEID,
coalesce(
(
select NAME
from SPONSORSHIPOPPORTUNITYCHILD
where ID=S.SPONSORSHIPOPPORTUNITYID
),
(
select NAME
from SPONSORSHIPOPPORTUNITYPROJECT
where ID = S.SPONSORSHIPOPPORTUNITYID
)
) SPONSORSHIPOPPORTUNITY
from dbo.REVENUESPLIT SP
inner join SPONSORSHIP S on S.REVENUESPLITID = SP.ID
inner join SPONSORSHIPOPPORTUNITY SO on SO.ID = S.SPONSORSHIPOPPORTUNITYID
where S.ISMOSTRECENTFORCOMMITMENT = 1
) SPONSORSHIPINFO on SPONSORSHIPINFO.REVENUEID = REVENUE.ID
left outer join(
select
MS.REVENUEID,
MP.NAME as [PROGRAMNAME],
ML.NAME as [LEVELNAME]
from
dbo.REVENUESPLIT MS
inner join MEMBERSHIPTRANSACTION MT on MT.REVENUESPLITID = MS.ID
inner join MEMBERSHIP M on M.ID = MT.MEMBERSHIPID
inner join MEMBERSHIPPROGRAM MP on MP.ID = M.MEMBERSHIPPROGRAMID
inner join MEMBERSHIPLEVEL ML on ML.ID = M.MEMBERSHIPLEVELID
) MEMBERSHIPINFO on MEMBERSHIPINFO.REVENUEID = REVENUE.ID
outer apply dbo.UFN_RECURRINGGIFT_GETNEXTINSTALLMENTINFO(REVENUE.ID,null) NEXTINSTALLMENT
where
REVENUE.ID=@COMMITMENTID
and REVENUE.TYPECODE = 2 --Recurring Gift
and REVENUESCHEDULE.STATUSCODE in (0,5) --Active & Lapsed
--and REVENUESCHEDULE.ISPENDING = 0 --Isn't pending
and REVENUE.TRANSACTIONAMOUNT > 0 --Has Value???
and REVENUE.DELETEDON is null
if @APPUSERISSYSADMIN = 0
begin
-- Remove records that the user does not have site access to.
delete from @REVENUECOMMITMENTS
where not exists (
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUEID) as SITE
where exists (
select 1
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
where SITEID=[SITE].[SITEID]
or (SITEID is null
and [SITE].[SITEID] is null
)
)
);
end
-- Set DESIGNATIONLIST separately from the insert to get a better query plan.
update REVENUECOMMITMENTS
set
DESIGNATIONLIST = (
select
left(dbo.UDA_BUILDLIST(DESIGNATION.USERID), 500) as DESIGNATIONLIST
from dbo.REVENUESPLIT
inner join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID
where REVENUESPLIT.REVENUEID = REVENUECOMMITMENTS.REVENUEID
)
from @REVENUECOMMITMENTS as REVENUECOMMITMENTS;
select
REVENUEID,
CONSTITUENTID,
CONSTITUENTNAME,
APPLIED,
BALANCE,
AMOUNTDUE,
DATEDUE,
REVENUETYPECODE,
DESCRIPTION,
SORTORDER,
DESIGNATIONLIST,
SPONSORSHIPOPPORTUNITY,
APPLICATIONCURRENCYID,
PDACCOUNTSYSTEMID,
SPONSORSHIPRGADDITIONALGIFT,
MEMBERSHIPPROGRAMNAME,
MEMBERSHIPLEVELNAME
from @REVENUECOMMITMENTS
union all
select
REGISTRANT.ID,
CONSTITUENT.ID,
CONSTITUENT.NAME,
0,
REGISTRANTBALANCE.BALANCE,
REGISTRANTBALANCE.BALANCE,
[EVENT].STARTDATE, --JamesWill CR260844-113006 2007/03/15 Use the event start date as the due date
6, --Event Registration
[EVENT].NAME,
1,
'' as DESIGNATIONLIST,
null as SPONSORSHIPOPPORTUNITY,
EVENT.BASECURRENCYID,
null as PDACCOUNTSYSTEMID,
0,
null as MEMBERSHIPPROGRAMNAME,
null as MEMBERSHIPLEVELNAME
from dbo.REGISTRANT
--inner join @CONSTITUENTCTE as CONSTITUENTCTE on CONSTITUENTCTE.ID = REGISTRANT.CONSTITUENTID
inner join dbo.CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
inner join dbo.[EVENT] on [EVENT].ID = REGISTRANT.EVENTID
left join V_QUERY_EVENTREGISTRANT_BALANCE REGISTRANTBALANCE on REGISTRANTBALANCE.REGISTRANTID = REGISTRANT.ID
where
REGISTRANT.ID=@COMMITMENTID
and REGISTRANTBALANCE.BALANCE > 0
and (--Check site security
@APPUSERISSYSADMIN = 1
or exists (
select 1
from dbo.UFN_SITEID_MAPFROM_EVENTID([EVENT].ID) as SITE
where exists (
select 1
from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
where SITEID=[SITE].[SITEID]
or (SITEID is null
and [SITE].[SITEID] is null
)
)
)
)
--union all
--select --only pull primary members, due for renewal
-- MEMBERSHIP.ID,
-- CONSTITUENT.ID,
-- CONSTITUENT.NAME,
-- 0,
-- MEMBERSHIPLEVELTERM.AMOUNT,
-- MEMBERSHIPLEVELTERM.AMOUNT,
-- MEMBERSHIP.EXPIRATIONDATE,
-- 5, --Membership
-- dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),
-- 1,
-- '' as DESIGNATIONLIST,
-- null as SPONSORSHIPOPPORTUNITY,
-- MEMBERSHIPLEVELTERM.BASECURRENCYID,
-- null as PDACCOUNTSYSTEMID,
-- 0
--from dbo.MEMBERSHIP
-- inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
-- inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
--inner join @CONSTITUENTCTE C on C.ID = MEMBER.CONSTITUENTID
-- inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID
-- inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
--where
-- MEMBERSHIP.ID=@COMMITMENTID
-- and MEMBER.ISPRIMARY = 1
-- and MEMBER.ISDROPPED = 0
-- and (MEMBERSHIP.ISGIFT = 0
-- or MEMBERSHIP.SENDRENEWALCODE <> 0
-- )
--and @LOADALL = 0
-- and (
-- @APPUSERISSYSADMIN = 1
-- or exists (
-- select 1
-- from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
-- where SITEID = dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID)
-- or (SITEID is null
-- and dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) is null
-- )
-- )
-- )
--union all
--select
-- MEMBERSHIP.ID,
-- CONSTITUENT.ID,
-- CONSTITUENT.NAME,
-- 0,
-- MEMBERSHIPLEVELTERM.AMOUNT,
-- MEMBERSHIPLEVELTERM.AMOUNT,
-- MEMBERSHIP.EXPIRATIONDATE,
-- 5, --Membership
-- dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),
-- 1,
-- '' as DESIGNATIONLIST,
-- null as SPONSORSHIPOPPORTUNITY,
-- MEMBERSHIPLEVELTERM.BASECURRENCYID,
-- null as PDACCOUNTSYSTEMID,
-- 0
--from dbo.MEMBERSHIP
-- inner join dbo.MEMBERSHIPLEVEL on MEMBERSHIP.MEMBERSHIPLEVELID = MEMBERSHIPLEVEL.ID
-- inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
--inner join @CONSTITUENTCTE C on C.ID = MEMBERSHIP.GIVENBYID
-- inner join dbo.CONSTITUENT on MEMBERSHIP.GIVENBYID = CONSTITUENT.ID
-- inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
--where
-- MEMBERSHIP.ID=@COMMITMENTID
-- and MEMBERSHIP.SENDRENEWALCODE <> 1
--and @LOADALL = 0
-- and (
-- @APPUSERISSYSADMIN = 1
-- or exists (
-- select 1
-- from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
-- where SITEID = dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID)
-- or (SITEID is null
-- and dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) is null
-- )
-- )
-- )
--union all
--select --only pull primary members, due for renewal
-- MEMBERSHIP.ID,
-- CONSTITUENT.ID,
-- CONSTITUENT.NAME,
-- 0,
-- MEMBERSHIPLEVELTERM.AMOUNT,
-- MEMBERSHIPLEVELTERM.AMOUNT,
-- MEMBERSHIP.EXPIRATIONDATE,
-- 5, --Membership
-- dbo.UFN_MEMBERSHIPPROGRAM_GETNAME(MEMBERSHIP.MEMBERSHIPPROGRAMID) + ' - ' + dbo.UFN_MEMBERSHIPLEVEL_GETNAME(MEMBERSHIP.MEMBERSHIPLEVELID),
-- 1,
-- '' as DESIGNATIONLIST,
-- null as SPONSORSHIPOPPORTUNITY,
-- MEMBERSHIPLEVELTERM.BASECURRENCYID,
-- null as PDACCOUNTSYSTEMID,
-- 0
--from dbo.MEMBERSHIP
-- inner join dbo.MEMBER on MEMBERSHIP.ID = MEMBER.MEMBERSHIPID
--inner join @CONSTITUENTCTE C on C.ID = MEMBER.CONSTITUENTID
-- inner join dbo.CONSTITUENT on MEMBER.CONSTITUENTID = CONSTITUENT.ID
-- inner join dbo.MEMBERSHIPLEVELTERM on MEMBERSHIP.MEMBERSHIPLEVELTERMID = MEMBERSHIPLEVELTERM.ID
--where
-- MEMBERSHIP.ID=@COMMITMENTID
-- and MEMBER.ISDROPPED = 0
--and @LOADALL <> 0
-- and (
-- @APPUSERISSYSADMIN = 1
-- or exists (
-- select 1
-- from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
-- where SITEID = dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID)
-- or (SITEID is null
-- and dbo.UFN_SITEID_MAPFROM_MEMBERSHIPID(MEMBERSHIP.ID) is null
-- )
-- )
-- )
union all
-- THIS SECTION MAY NOT BE USED, PLEASE CONSIDER FOR REMOVAL - DavidHe
select
OPPORTUNITY.ID,
CONSTITUENT.ID,
CONSTITUENT.NAME,
0,
OPPORTUNITY.AMOUNT - dbo.UFN_OPPORTUNITY_GETAMOUNTPAID(OPPORTUNITY.ID),
null, -- amount due
null, -- date due
100, -- arbitrary application type code for opportunities, resolved in USP_REVENUE_APPLYTOREVENUESTREAMS
dbo.UFN_OPPORTUNITY_GETDESCRIPTION(OPPORTUNITY.ID),
1,
(
select top 1 left(dbo.UDA_BUILDLIST(DESIGNATION.USERID), 500) as DESIGNATIONLIST
from dbo.DESIGNATION
inner join dbo.OPPORTUNITYDESIGNATION on DESIGNATION.ID = OPPORTUNITYDESIGNATION.DESIGNATIONID
where OPPORTUNITYDESIGNATION.OPPORTUNITYID = OPPORTUNITY.ID
) as DESIGNATIONLIST,
null as SPONSORSHIPOPPORTUNITY,
null as APPLCATIONCURRENCYID ,
null as PDACCOUNTSYSTEMID,
0,
null as MEMBERSHIPPROGRAMNAME,
null as MEMBERSHIPLEVELNAME
from dbo.OPPORTUNITY
inner join dbo.PROSPECTPLAN on PROSPECTPLAN.ID = OPPORTUNITY.PROSPECTPLANID
--inner join @CONSTITUENTCTE as CONSTITUENTCTE on CONSTITUENTCTE.ID = PROSPECTPLAN.PROSPECTID
-- CR298118-041508 Only include opportunities which have designations associated with them so that
-- when we go to fill in a payment for them we have something to apply them to.
-- DavidHe (removed inner join and made a where exists clause to prevent multiple rows from pulling where only one should).
left outer join dbo.CONSTITUENT on CONSTITUENT.ID = PROSPECTPLAN.PROSPECTID
where
OPPORTUNITY.ID=@COMMITMENTID
and exists (
select ID
from dbo.OPPORTUNITYDESIGNATION
where OPPORTUNITYDESIGNATION.OPPORTUNITYID = OPPORTUNITY.ID);
end