USP_DATALIST_REVENUECOMMITMENTS
A datalist of commitments for a given constituent.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @CONSTITUENTID | uniqueidentifier | IN | Input parameter indicating the context ID for the data list. |
| @APPLICATIONCODE | tinyint | IN | Application |
| @APPEALID | uniqueidentifier | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | Input parameter indicating the ID of the current user. |
| @SECURITYFEATUREID | uniqueidentifier | IN | Input parameter indicating the ID of the feature to use for site security checking. |
| @SECURITYFEATURETYPE | tinyint | IN | Input parameter indicating the type of the feature to use for site security checking. |
| @PDACCOUNTSYSTEMID | uniqueidentifier | IN | |
| @PAYMENTID | uniqueidentifier | IN | |
| @EXCLUDEMEMBERSHIPS | bit | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_REVENUECOMMITMENTS(
@CONSTITUENTID uniqueidentifier,
@APPLICATIONCODE tinyint = null,
@APPEALID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null,
@PDACCOUNTSYSTEMID uniqueidentifier = null,
@PAYMENTID uniqueidentifier = null,
@EXCLUDEMEMBERSHIPS bit = 1
)
as
begin
set nocount on
declare @RETURNTABLE as table(
ID uniqueidentifier,
FORMID uniqueidentifier,
APPLIED bit default 0,
APPLICATIONCODE tinyint
);
declare @TMP_DATA_MATCHINGGIFTCLAIMS UDT_GENERICID;
declare @ISADMIN bit;
declare @APPUSER_IN_NONRACROLE bit;
declare @APPUSER_IN_NOSECGROUPROLE bit;
set @ISADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
set @APPUSER_IN_NONRACROLE = dbo.UFN_SECURITY_APPUSER_IN_NONRACROLE(@CURRENTAPPUSERID);
set @APPUSER_IN_NOSECGROUPROLE = dbo.UFN_SECURITY_APPUSER_IN_NO_SECURITY_GROUP_ROLE(@CURRENTAPPUSERID);
declare @CONSTITUENTS as table(
ID uniqueidentifier
);
if @APPLICATIONCODE = 200 --200 = all
set @APPLICATIONCODE = null;
--Get all relevant constits
declare @CURRENTDATE datetime;
set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());
declare @HOUSEHOLDSCANBEDONORS bit;
set @HOUSEHOLDSCANBEDONORS = dbo.UFN_INSTALLATIONINFO_GETHOUSEHOLDSCANBEDONORS();
declare @HOUSEHOLDID uniqueidentifier;
select top(1) @HOUSEHOLDID = GM.GROUPID
from dbo.GROUPMEMBER as GM
left outer join dbo.GROUPDATA as GD on GD.ID = GM.GROUPID
where GM.MEMBERID = @CONSTITUENTID
and GD.GROUPTYPECODE = 0
and dbo.UFN_GROUPMEMBER_ISCURRENTMEMBER(GM.ID) = 1
and @HOUSEHOLDSCANBEDONORS = 1;
insert into @CONSTITUENTS(
ID
)
select
CONSTITUENTS.ID
from (
select @HOUSEHOLDID as ID
union
select
case
when dbo.UFN_CONSTITUENT_ISHOUSEHOLD(@CONSTITUENTID) = 1
then
case
when @HOUSEHOLDSCANBEDONORS = 1
then @CONSTITUENTID
else null
end
else @CONSTITUENTID
end
union
select
GM.MEMBERID
from dbo.GROUPMEMBER GM
left outer join dbo.GROUPMEMBERDATERANGE GMDR on GM.ID = GMDR.GROUPMEMBERID
where(
GM.GROUPID = @HOUSEHOLDID
or GM.GROUPID = @CONSTITUENTID
)
and (
(GMDR.DATEFROM is null and (GMDR.DATETO is null or GMDR.DATETO > @CURRENTDATE))
or (GMDR.DATETO is null and (GMDR.DATEFROM is null or GMDR.DATEFROM <= @CURRENTDATE))
or (GMDR.DATEFROM <= @CURRENTDATE and GMDR.DATETO > @CURRENTDATE)
)
) CONSTITUENTS
where @ISADMIN = 1
or ( --If form check by form
@SECURITYFEATURETYPE = 1
and dbo.UFN_SECURITY_APPUSER_GRANTED_FORM_FORCONSTIT(@CURRENTAPPUSERID, @SECURITYFEATUREID, CONSTITUENTS.ID) = 1
)
or ( --If datalist check by datalist
@SECURITYFEATURETYPE = 2
and dbo.UFN_SECURITY_APPUSER_GRANTED_DATALIST_FORCONSTIT(@CURRENTAPPUSERID, @SECURITYFEATUREID, CONSTITUENTS.ID) = 1
)
or ( --If otherwise check all roles
@SECURITYFEATURETYPE not in(1, 2)
and (
@APPUSER_IN_NONRACROLE = 1
or dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITID_FORREPORT(@CURRENTAPPUSERID, CONSTITUENTS.ID, @APPUSER_IN_NOSECGROUPROLE) = 1
)
);
--Events
if coalesce(@APPLICATIONCODE, 1) = 1
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REGISTRANT.ID,
'72700FCF-AFF8-4B63-8EDF-80B467EB30DB',
1
from dbo.REGISTRANT
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REGISTRANT.CONSTITUENTID
left join V_QUERY_EVENTREGISTRANT_BALANCE REGISTRANTBALANCE on REGISTRANTBALANCE.REGISTRANTID = REGISTRANT.ID
where (
REGISTRANTBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.EVENTREGISTRANTPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.ID = EVENTREGISTRANTPAYMENT.PAYMENTID
where EVENTREGISTRANTPAYMENT.REGISTRANTID = REGISTRANT.ID and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.FINANCIALTRANSACTIONID = @PAYMENTID
)
)
)
and REGISTRANT.ISCANCELLED = 0
and exists( --Check site security
select 1
from dbo.UFN_SITEID_MAPFROM_REGISTRANTID(REGISTRANT.ID) as SITE
where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
)
--Pledge
if coalesce(@APPLICATIONCODE, 2) = 2
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUE.ID,
'6C4958A9-7A6C-4C23-A1BB-8EA0CFA267B4',
2
from dbo.FINANCIALTRANSACTION as REVENUE
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
--left join dbo.PDACCOUNTSYSTEMFORREVENUE on REVENUE.ID = PDACCOUNTSYSTEMFORREVENUE.ID
left join V_QUERY_REVENUE_PLEDGEBALANCE on V_QUERY_REVENUE_PLEDGEBALANCE.ID = REVENUE.ID
where REVENUE.TYPECODE = 1 and REVENUE.DELETEDON is null
and (
V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.FINANCIALTRANSACTIONID = @PAYMENTID
)
)
)
and (REVENUE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID or dbo.UFN_VALID_BASICGL_INSTALLED() = 0)
and exists(--Check site security
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITE
where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
)
--RG
if coalesce(@APPLICATIONCODE, 3) = 3
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUE.ID,
'84D79558-F2BC-449C-A806-EA29AC9B4983',
3
from dbo.FINANCIALTRANSACTION as REVENUE
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
inner join dbo.REVENUESCHEDULE on REVENUE.ID = REVENUESCHEDULE.ID
where REVENUE.DELETEDON is null
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??? 10.9.2014 Changing from baseamount to transaction amount WI#428649
and (@EXCLUDEMEMBERSHIPS = 0 or dbo.UFN_RECURRINGGIFT_ISMEMBERSHIP(REVENUE.ID) = 0) --Only show membership recurring gifts on the edit form #302239
and exists(--Check site security
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITE
where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
)
--Planned gifts with additions
if coalesce(@APPLICATIONCODE, 6) = 6
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUE.ID,
'3fe5d648-e129-407c-aa83-ccdf77430e5d',
6
from dbo.FINANCIALTRANSACTION as REVENUE
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
inner join dbo.V_PLANNEDGIFTREVENUE_WITHADDITIONS PLANNEDGIFTREVENUE on PLANNEDGIFTREVENUE.REVENUEID = REVENUE.ID
inner join dbo.PLANNEDGIFT on PLANNEDGIFT.ID = PLANNEDGIFTREVENUE.PLANNEDGIFTID
--left join dbo.PDACCOUNTSYSTEMFORREVENUE on REVENUE.ID = PDACCOUNTSYSTEMFORREVENUE.ID
left join V_QUERY_REVENUE_PLEDGEBALANCE on V_QUERY_REVENUE_PLEDGEBALANCE.ID = REVENUE.ID
where REVENUE.TYPECODE = 4 and REVENUE.DELETEDON is null
and (
V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.FINANCIALTRANSACTIONID = @PAYMENTID
)
)
)
and REVENUE.BASEAMOUNT > 0
and (REVENUE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID or dbo.UFN_VALID_BASICGL_INSTALLED() = 0)
and exists(--Check site security
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITE
where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
)
--MG Claim
if coalesce(@APPLICATIONCODE, 7) = 7
begin
declare @SITEONFEATURE table (ID uniqueidentifier)
insert into @SITEONFEATURE (ID)
select SITEID from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE)
--declare @ISSYSADMIN bit
--select @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID)
if @ISADMIN = 1
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUEWITHBALANCE.ID,
'2B4A336A-8774-4126-ABCF-5FAD92DFB15A',
7
from
(
-- A derived table is used rather than coalescing the sub-query directly since coalescing the sub-query directly
-- caused the INSTALLMENTSPLITPAYMENT and INSTALLMENTSPLIT tables to be hit twice: once to see if data exists and the other
-- time to calculate the result. The derived table allows those tables to be hit only once.
-- NOTE: The logic for the balance was pulled from RevenuePledgeBalance.Query.xml. It was inlined and rewritten
-- but any functional changes to balance calculation should be made in both places.
select
ID,
TYPECODE,
PAID,
cast(TRANSACTIONAMOUNT - coalesce(PAID, 0) as money) as BALANCE
from
(
select
FINANCIALTRANSACTION.ID,
TYPECODE,
TRANSACTIONAMOUNT,
(
select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
from dbo.INSTALLMENTSPLITPAYMENT
where INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
) as PAID
from dbo.FINANCIALTRANSACTION
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
inner join dbo.REVENUEMATCHINGGIFT on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
where FINANCIALTRANSACTION.DELETEDON is null and REVENUEMATCHINGGIFT.ISACTIVE = 1 --Check if status for Revenue matching gift is Active
) as T
) as REVENUEWITHBALANCE
where
REVENUEWITHBALANCE.TYPECODE = 3
and (
REVENUEWITHBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUEWITHBALANCE.ID and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.FINANCIALTRANSACTIONID = @PAYMENTID
)
)
)
else
begin
insert into @TMP_DATA_MATCHINGGIFTCLAIMS(ID)
select
REVENUEWITHBALANCE.ID
from
(
-- A derived table is used rather than coalescing the sub-query directly since coalescing the sub-query directly
-- caused the INSTALLMENTSPLITPAYMENT and INSTALLMENTSPLIT tables to be hit twice: once to see if data exists and the other
-- time to calculate the result. The derived table allows those tables to be hit only once.
-- NOTE: The logic for the balance was pulled from RevenuePledgeBalance.Query.xml. It was inlined and rewritten
-- but any functional changes to balance calculation should be made in both places.
select
ID,
TYPECODE,
PAID,
cast(TRANSACTIONAMOUNT - coalesce(PAID, 0) as money) as BALANCE
from
(
select
FINANCIALTRANSACTION.ID,
TYPECODE,
TRANSACTIONAMOUNT,
(
select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
from dbo.INSTALLMENTSPLITPAYMENT
where INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
) as PAID
from dbo.FINANCIALTRANSACTION
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = FINANCIALTRANSACTION.CONSTITUENTID
inner join dbo.REVENUEMATCHINGGIFT on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
where FINANCIALTRANSACTION.DELETEDON is null and REVENUEMATCHINGGIFT.ISACTIVE = 1 --Check if status for Revenue matching gift is Active
) as T
) as REVENUEWITHBALANCE
where
REVENUEWITHBALANCE.TYPECODE = 3
and (
REVENUEWITHBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUEWITHBALANCE.ID and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.FINANCIALTRANSACTIONID = @PAYMENTID
)
)
);
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUEWITHBALANCE.ID,
'2B4A336A-8774-4126-ABCF-5FAD92DFB15A',
7
from @TMP_DATA_MATCHINGGIFTCLAIMS REVENUEWITHBALANCE
where
exists(
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUEWITHBALANCE.ID) as SITE
inner join @SITEONFEATURE as SITEONFEATURE on SITEONFEATURE.ID=[SITE].[SITEID] or (SITEONFEATURE.ID is null and [SITE].[SITEID] is null)
)
end
--Subsidiary MG Claims
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUEWITHBALANCE.ID,
'2B4A336A-8774-4126-ABCF-5FAD92DFB15A',
7
from
(
-- A derived table is used rather than coalescing the sub-query directly since coalescing the sub-query directly
-- caused the INSTALLMENTSPLITPAYMENT and INSTALLMENTSPLIT tables to be hit twice: once to see if data exists and the other
-- time to calculate the result. The derived table allows those tables to be hit only once.
-- NOTE: The logic for the balance was pulled from RevenuePledgeBalance.Query.xml. It was inlined and rewritten
-- but any functional changes to balance calculation should be made in both places.
select
ID,
TYPECODE,
PAID,
cast(TRANSACTIONAMOUNT - coalesce(PAID, 0) as money) as BALANCE
from
(
select
FINANCIALTRANSACTION.ID,
TYPECODE,
TRANSACTIONAMOUNT,
(
select sum(INSTALLMENTSPLITPAYMENT.AMOUNT)
from dbo.INSTALLMENTSPLITPAYMENT
where INSTALLMENTSPLITPAYMENT.PLEDGEID = FINANCIALTRANSACTION.ID
) as PAID
from dbo.FINANCIALTRANSACTION
inner join dbo.ORGANIZATIONDATA on ORGANIZATIONDATA.ID = FINANCIALTRANSACTION.CONSTITUENTID
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = ORGANIZATIONDATA.PARENTCORPID
inner join dbo.REVENUEMATCHINGGIFT on FINANCIALTRANSACTION.ID = REVENUEMATCHINGGIFT.ID
where REVENUEMATCHINGGIFT.ISACTIVE = 1 --Check if status for Revenue matching gift is Active
) as T
) as REVENUEWITHBALANCE
where REVENUEWITHBALANCE.TYPECODE = 3
and (
REVENUEWITHBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUEWITHBALANCE.ID and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.FINANCIALTRANSACTIONID = @PAYMENTID
)
)
)
and (
@ISADMIN = 1
or exists (
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUEWITHBALANCE.ID) as SITE
where
exists (
select 1 from @SITEONFEATURE as SITEONFEATURE
where SITEONFEATURE.ID=[SITE].[SITEID] or (SITEONFEATURE.ID is null and [SITE].[SITEID] is null)
)
)
)
end
--Grant award
if coalesce(@APPLICATIONCODE, 8) = 8
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUE.ID,
'3AFDF279-6EF0-43CB-BB84-86BDCA72E4E0',
8
from dbo.FINANCIALTRANSACTION as REVENUE
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
inner join dbo.REVENUESCHEDULE on REVENUESCHEDULE.ID = REVENUE.ID
--left join dbo.PDACCOUNTSYSTEMFORREVENUE on REVENUE.ID = PDACCOUNTSYSTEMFORREVENUE.ID
left join V_QUERY_REVENUE_PLEDGEBALANCE on V_QUERY_REVENUE_PLEDGEBALANCE.ID = REVENUE.ID
where REVENUE.TYPECODE = 6 and REVENUE.DELETEDON is null
and (
V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.FINANCIALTRANSACTIONID = @PAYMENTID
)
)
)
and (REVENUE.PDACCOUNTSYSTEMID = @PDACCOUNTSYSTEMID or dbo.UFN_VALID_BASICGL_INSTALLED() = 0)
and exists(--Check site security
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITE
where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
)
--Auction purchases
if coalesce(@APPLICATIONCODE, 12) = 12
begin
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
AUCTIONITEM.ID,
'366f6c42-2b66-4c1f-9aec-60e7ab5382f6',
12
from dbo.AUCTIONITEMRESERVATION
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = AUCTIONITEMRESERVATION.PURCHASERID
inner join dbo.AUCTIONITEM on AUCTIONITEM.ID = AUCTIONITEMRESERVATION.AUCTIONITEMID
left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
where AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is null
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID,
'366f6c42-2b66-4c1f-9aec-60e7ab5382f6',
12
from dbo.REVENUE
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
inner join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID = REVENUE.ID
where REVENUE.ID = @PAYMENTID
end
--Donor Challenge
if coalesce(@APPLICATIONCODE, 13) = 13
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUE.ID,
'59704F65-893E-4DCE-A1B2-A6912ED72DD5',
13
from dbo.REVENUE
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = REVENUE.CONSTITUENTID
left join V_QUERY_REVENUE_PLEDGEBALANCE on V_QUERY_REVENUE_PLEDGEBALANCE.ID = REVENUE.ID
where REVENUE.TRANSACTIONTYPECODE = 8
and (
V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM as REVENUESPLIT on REVENUESPLIT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUE.ID and REVENUESPLIT.DELETEDON is null
and REVENUESPLIT.FINANCIALTRANSACTIONID = @PAYMENTID
)
)
)
and exists(--Check site security
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID(REVENUE.ID) as SITE
where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
)
--Pending gift -- add for FAF
if coalesce(@APPLICATIONCODE, 17) = 17
insert into @RETURNTABLE(
ID, FORMID, APPLICATIONCODE
)
select
REVENUE.ID,
'a6ac328e-d279-4f12-9429-8d2ccbe9afd3',
17
from dbo.REVENUE R
inner join dbo.FINANCIALTRANSACTION [REVENUE] on R.ID = [REVENUE].ID
inner join @CONSTITUENTS CONSTITUENT on CONSTITUENT.ID = R.CONSTITUENTID
left join dbo.REVENUEOFFLINEDONATION on REVENUEOFFLINEDONATION.ID = R.ID
left join dbo.ADDRESSBOOKFAF on REVENUEOFFLINEDONATION.ADDRESSBOOKFAFID = ADDRESSBOOKFAF.ID
left join V_QUERY_REVENUE_PLEDGEBALANCE on V_QUERY_REVENUE_PLEDGEBALANCE.ID = [REVENUE].ID
where [REVENUE].TYPECODE = 9
and (
V_QUERY_REVENUE_PLEDGEBALANCE.BALANCE > 0
or (
@PAYMENTID is not null
and exists(
select 1
from dbo.INSTALLMENTSPLITPAYMENT
inner join dbo.FINANCIALTRANSACTIONLINEITEM [REVENUESPLIT] on [REVENUESPLIT].ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
where INSTALLMENTSPLITPAYMENT.PLEDGEID = [REVENUE].ID
and [REVENUESPLIT].FINANCIALTRANSACTIONID = @PAYMENTID
)
)
)
and exists(--Check site security
select 1
from dbo.UFN_SITEID_MAPFROM_REVENUEID([REVENUE].ID) as SITE
where (@ISADMIN = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[SITE].[SITEID] or (SITEID is null and [SITE].[SITEID] is null)))
)
--where REVENUE.TRANSACTIONTYPECODE = 9
select ID, FORMID, APPLIED, APPLICATIONCODE
from @RETURNTABLE
order by APPLICATIONCODE;
end