USP_DATALIST_REVENUESPLIT_MATCHINGGIFTPAYMENTAVAILABLECLAIMS
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @REVENUEID | uniqueidentifier | IN | |
| @MAXROWS | int | IN | |
| @CURRENTAPPUSERID | uniqueidentifier | IN | |
| @KEYNAME | nvarchar(150) | IN | |
| @FIRSTNAME | nvarchar(150) | IN | |
| @FULLNAME | nvarchar(150) | IN | |
| @DATEFILTER | tinyint | IN | |
| @STARTDATE | datetime | IN | |
| @ENDDATE | datetime | IN | |
| @CLAIMSCODE | tinyint | IN | |
| @NEWLYUNPAIDCLAIMS | xml | IN | |
| @NEWLYPAIDCLAIMS | xml | IN |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_REVENUESPLIT_MATCHINGGIFTPAYMENTAVAILABLECLAIMS
(
@REVENUEID uniqueidentifier = null,
@MAXROWS int = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@KEYNAME nvarchar(150) = null,
@FIRSTNAME nvarchar(150) = null,
@FULLNAME nvarchar(150) = null,
@DATEFILTER tinyint = 29, --Last 6 months
@STARTDATE datetime = null,
@ENDDATE datetime = null,
@CLAIMSCODE tinyint = 0,
@NEWLYUNPAIDCLAIMS xml = null,
@NEWLYPAIDCLAIMS xml = null
)
with execute as owner
as
set nocount on;
/**
* Initialize simple filters.
*/
declare @INCLUDEPAIDCLAIMS bit;
declare @INCLUDEUNPAIDCLAIMS bit;
set @INCLUDEPAIDCLAIMS = case when @CLAIMSCODE = 0 or @CLAIMSCODE = 1 then 1 else 0 end;
set @INCLUDEUNPAIDCLAIMS = case when @CLAIMSCODE = 0 or @CLAIMSCODE = 2 then 1 else 0 end;
declare @TOPMAXROWSCLAUSE nvarchar(max) = N'';
if @MAXROWS is not null and @MAXROWS > 0
set @TOPMAXROWSCLAUSE = N' top (@MAXROWS)';
else
set @MAXROWS = null;
if @DATEFILTER is null
set @DATEFILTER = 29; --Last 6 months
if @DATEFILTER = 0
begin
set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(coalesce(@STARTDATE, getdate()));
set @ENDDATE = dbo.UFN_DATE_GETLATESTTIME(coalesce(@ENDDATE, getdate()));
end
else
exec dbo.USP_RESOLVEDATEFILTER
@DATEFILTER,
@STARTDATE output,
@ENDDATE output;
if @KEYNAME is null
set @KEYNAME = N'';
if @FIRSTNAME is null
set @FIRSTNAME = N'';
if @FULLNAME is null
set @FULLNAME = N'';
declare @ISSYSADMIN bit;
set @ISSYSADMIN = dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID);
/**
* Build a common name where clause that can be used in multiple statements below.
*/
declare @NAMECLAUSE as nvarchar(max) = N'';
if len(@KEYNAME) > 0
begin
set @KEYNAME = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@KEYNAME, default, default);
set @NAMECLAUSE = @NAMECLAUSE + N'CONSTITUENT.KEYNAME like @KEYNAME escape N''\''';
end
if len(@FIRSTNAME) > 0
begin
set @FIRSTNAME = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@FIRSTNAME, default, default);
if len(@NAMECLAUSE) > 0
set @NAMECLAUSE = @NAMECLAUSE + N' and ';
set @NAMECLAUSE = @NAMECLAUSE + N'(CONSTITUENT.FIRSTNAME like @FIRSTNAME escape N''\'' or CONSTITUENT.NICKNAME like @FIRSTNAME escape N''\'')';
end
if len(@FULLNAME) > 0
begin
set @FULLNAME = dbo.UFN_SEARCHCRITERIA_GETLIKEPARAMETERVALUE(@FULLNAME, default, default);
declare @FULLNAMECLAUSE nvarchar(max);
set @FULLNAMECLAUSE = N'CONSTITUENT.KEYNAME like @FULLNAME escape N''\''';
--Use full name to search for first name as well as key name when just one name is specified.
-- This is different than constituent search, but we're doing it this way since there is no
-- more detailed search form displayed to allow users to see that their single-word input was
-- only applied to the last name field.
if len(@FIRSTNAME) = 0
set @FULLNAMECLAUSE = @FULLNAMECLAUSE + N' or (CONSTITUENT.FIRSTNAME like @FULLNAME escape N''\'' or CONSTITUENT.NICKNAME like @FULLNAME escape N''\'')';
--Only include the name clause if it would not be identical to the first part of
-- the full name clause.
if len(@FIRSTNAME) > 0 or @KEYNAME <> @FULLNAME
set @NAMECLAUSE = N'( ' + @FULLNAMECLAUSE + N' or ( ' + @NAMECLAUSE + N' ) )';
else
set @NAMECLAUSE = @FULLNAMECLAUSE;
end
/**
* Build a common update statement can be be used to build the sort constituent name
* after multiple other insert statements below. This technique is based on what is
* done in constituent search so the sort name only has to be built for the records we
* are showing after other filtering is already done.
*/
declare @UPDATESORTCONSTITUENTNAMESQL nvarchar(max);
set @UPDATESORTCONSTITUENTNAMESQL = N'
update @RETVAL set
SORTCONSTITUENTNAME =
(
case
when CONSTITUENT.ISORGANIZATION = 1 then case CONSTITUENT.KEYNAMEPREFIX when '''' then CONSTITUENT.KEYNAME else CONSTITUENT.KEYNAME + '', '' + CONSTITUENT.KEYNAMEPREFIX end
else dbo.UFN_NAMEFORMAT_08(CONSTITUENT.ID, CONSTITUENT.KEYNAME, CONSTITUENT.FIRSTNAME, CONSTITUENT.MIDDLENAME, null, null, null, null, null, null, null)
end
)
from
@RETVAL as RETVAL
inner join dbo.CONSTITUENT on RETVAL.CONSTITUENTID = CONSTITUENT.ID
where
RETVAL.SORTCONSTITUENTNAME is null;
';
/**
* Build temp tables to hold IDs of records that have client-side edits
*/
declare @HASNEWLYUNPAIDCLAIMS bit = 0;
if @NEWLYUNPAIDCLAIMS.exist('/NEWLYUNPAIDCLAIMS/ITEM') = 1
begin
set @HASNEWLYUNPAIDCLAIMS = 1;
create table #NEWLYUNPAIDCLAIMTABLE
(
REVENUESPLITID uniqueidentifier not null,
primary key (REVENUESPLITID),
unique (REVENUESPLITID)
);
insert into #NEWLYUNPAIDCLAIMTABLE (REVENUESPLITID)
select
NEWLYUNPAIDCLAIMS.ITEM.value('REVENUESPLITID[1]', 'uniqueidentifier')
from
@NEWLYUNPAIDCLAIMS.nodes('/NEWLYUNPAIDCLAIMS/ITEM') as NEWLYUNPAIDCLAIMS(ITEM);
end
declare @HASNEWLYPAIDCLAIMS bit = 0;
if @NEWLYPAIDCLAIMS.exist('/NEWLYPAIDCLAIMS/ITEM') = 1
begin
set @HASNEWLYPAIDCLAIMS = 1;
create table #NEWLYPAIDCLAIMTABLE
(
APPLICATIONREVENUESPLITID uniqueidentifier not null,
primary key (APPLICATIONREVENUESPLITID),
unique (APPLICATIONREVENUESPLITID)
);
insert into #NEWLYPAIDCLAIMTABLE (APPLICATIONREVENUESPLITID)
select
NEWLYPAIDCLAIMS.ITEM.value('APPLICATIONREVENUESPLITID[1]', 'uniqueidentifier')
from
@NEWLYPAIDCLAIMS.nodes('/NEWLYPAIDCLAIMS/ITEM') as NEWLYPAIDCLAIMS(ITEM);
end
/**
* Build the main dynamic SQL statement.
*/
declare @SQL nvarchar(max) = N' --Generated by dbo.USP_DATALIST_REVENUESPLIT_MATCHINGGIFTPAYMENTAVAILABLECLAIMS';
set @SQL = @SQL + N'
declare @FOUND int = 0;
declare @RETVAL table
(
REVENUESPLITID uniqueidentifier,
APPLICATIONID uniqueidentifier,
APPLICATIONREVENUESPLITID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
SORTCONSTITUENTNAME nvarchar(700),
CONSTITUENTLOOKUPID nvarchar(100),
APPLIED money,
CLAIMAMOUNT money,
DESIGNATIONID uniqueidentifier,
DESIGNATIONNAME nvarchar(512),
DESIGNATIONUSERID nvarchar(512),
CLAIMLOOKUPID nvarchar(100),
CLAIMDATE datetimeoffset,
ORIGINALREVENUELOOKUPID nvarchar(100),
ORIGINALREVENUEDATE datetimeoffset,
TRANSACTIONCURRENCYID uniqueidentifier,
CLAIMTRANSACTIONCURRENCYID uniqueidentifier
);
';
if @INCLUDEPAIDCLAIMS = 1 or (@INCLUDEUNPAIDCLAIMS = 1 and @HASNEWLYUNPAIDCLAIMS = 1)
begin
set @SQL = @SQL + N'
insert into @RETVAL
select' + @TOPMAXROWSCLAUSE + N'
FINANCIALTRANSACTIONLINEITEM.ID as REVENUESPLITID,
MATCHINGGIFTCLAIMFINANCIALTRANSACTION.ID as APPLICATIONID,
MATCHINGGIFTCLAIMFINANCIALTRANSACTIONLINEITEM.ID as APPLICATIONREVENUESPLITID,
CONSTITUENT.ID as CONSTITUENTID,
null as SORTCONSTITUENTNAME,
CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT as APPLIED,
MATCHINGGIFTCLAIMFINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT as CLAIMAMOUNT,
MATCHINGGIFTCLAIMREVENUESPLIT_EXT.DESIGNATIONID,
DESIGNATION.NAME as DESIGNATIONNAME,
DESIGNATION.USERID as DESIGNATIONUSERID,
MATCHINGGIFTCLAIMFINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as CLAIMLOOKUPID,
MATCHINGGIFTCLAIMFINANCIALTRANSACTION.DATE as CLAIMDATE,
ORIGINALREVENUEFINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID as ORIGINALREVENUELOOKUPID,
ORIGINALREVENUEFINANCIALTRANSACTION.DATE as ORIGINALREVENUEDATE,
FINANCIALTRANSACTION.TRANSACTIONCURRENCYID,
MATCHINGGIFTCLAIMFINANCIALTRANSACTION.TRANSACTIONCURRENCYID as CLAIMTRANSACTIONCURRENCYID
from
dbo.FINANCIALTRANSACTION
inner join dbo.FINANCIALTRANSACTIONLINEITEM on FINANCIALTRANSACTION.ID = FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID
inner join dbo.REVENUESPLIT_EXT on FINANCIALTRANSACTIONLINEITEM.ID = REVENUESPLIT_EXT.ID
--Get only applications paying a matching gift claim by inner joining to dbo.INSTALLMENTSPLITPAYMENT.
-- Unapplied matching gift payments would not have an installment split payment row because there is
-- no commitment being paid.
inner join dbo.INSTALLMENTSPLITPAYMENT on REVENUESPLIT_EXT.ID = INSTALLMENTSPLITPAYMENT.PAYMENTID
inner join dbo.INSTALLMENTSPLIT on INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID = INSTALLMENTSPLIT.ID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as MATCHINGGIFTCLAIMFINANCIALTRANSACTIONLINEITEM on INSTALLMENTSPLIT.REVENUESPLITID = MATCHINGGIFTCLAIMFINANCIALTRANSACTIONLINEITEM.ID
inner join dbo.REVENUESPLIT_EXT as MATCHINGGIFTCLAIMREVENUESPLIT_EXT on MATCHINGGIFTCLAIMFINANCIALTRANSACTIONLINEITEM.ID = MATCHINGGIFTCLAIMREVENUESPLIT_EXT.ID
inner join dbo.DESIGNATION on MATCHINGGIFTCLAIMREVENUESPLIT_EXT.DESIGNATIONID = DESIGNATION.ID
inner join dbo.FINANCIALTRANSACTION as MATCHINGGIFTCLAIMFINANCIALTRANSACTION on MATCHINGGIFTCLAIMFINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = MATCHINGGIFTCLAIMFINANCIALTRANSACTION.ID
inner join dbo.REVENUEMATCHINGGIFT on INSTALLMENTSPLITPAYMENT.PLEDGEID = REVENUEMATCHINGGIFT.ID
inner join dbo.FINANCIALTRANSACTION as ORIGINALREVENUEFINANCIALTRANSACTION on REVENUEMATCHINGGIFT.MGSOURCEREVENUEID = ORIGINALREVENUEFINANCIALTRANSACTION.ID
inner join dbo.CONSTITUENT on ORIGINALREVENUEFINANCIALTRANSACTION.CONSTITUENTID = CONSTITUENT.ID
where
FINANCIALTRANSACTION.ID = @REVENUEID
and FINANCIALTRANSACTION.TYPECODE = 0 --Payment
and REVENUESPLIT_EXT.TYPECODE = 0 --Gift
and REVENUESPLIT_EXT.APPLICATIONCODE = 7 --Matching gift
';
if @INCLUDEPAIDCLAIMS = 0 and @INCLUDEUNPAIDCLAIMS = 1 and @HASNEWLYUNPAIDCLAIMS = 1
set @SQL = @SQL + N'
--Include claim payment splits that are paid in the database but that are unpaid in the UI.
and FINANCIALTRANSACTIONLINEITEM.ID in
(
select
NEWLYUNPAIDCLAIMTABLE.REVENUESPLITID
from
#NEWLYUNPAIDCLAIMTABLE as NEWLYUNPAIDCLAIMTABLE
)
';
else if @INCLUDEPAIDCLAIMS = 1 and @INCLUDEUNPAIDCLAIMS = 0 and @HASNEWLYUNPAIDCLAIMS = 1
set @SQL = @SQL + N'
--Include claim payment splits that are paid in the database but that are unpaid in the UI.
and FINANCIALTRANSACTIONLINEITEM.ID not in
(
select
NEWLYUNPAIDCLAIMTABLE.REVENUESPLITID
from
#NEWLYUNPAIDCLAIMTABLE as NEWLYUNPAIDCLAIMTABLE
)
';
set @SQL = @SQL + N'
and
(
(MATCHINGGIFTCLAIMFINANCIALTRANSACTION.DATE between @STARTDATE and @ENDDATE)
or (ORIGINALREVENUEFINANCIALTRANSACTION.DATE between @STARTDATE and @ENDDATE)
)';
if len(@NAMECLAUSE) > 0
begin
set @SQL = @SQL + N'
and ( ' + @NAMECLAUSE + N' )';
end
set @SQL = @SQL + N'
order by
CONSTITUENT.KEYNAME,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.MIDDLENAME,
CONSTITUENT.LOOKUPID,
ORIGINALREVENUEFINANCIALTRANSACTION.DATE,
ORIGINALREVENUEFINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID,
MATCHINGGIFTCLAIMFINANCIALTRANSACTION.DATE,
MATCHINGGIFTCLAIMFINANCIALTRANSACTION.CALCULATEDUSERDEFINEDID;
set @FOUND = @@ROWCOUNT;
';
set @SQL = @SQL + @UPDATESORTCONSTITUENTNAMESQL;
end
if @INCLUDEUNPAIDCLAIMS = 1 or (@INCLUDEPAIDCLAIMS = 1 and @HASNEWLYPAIDCLAIMS = 1)
begin
set @SQL = @SQL + N'
if @MAXROWS is null or @FOUND < @MAXROWS
begin
declare @CONSTITUENTID uniqueidentifier;
declare @TRANSACTIONCURRENCYID uniqueidentifier;
select
@CONSTITUENTID = FINANCIALTRANSACTION.CONSTITUENTID,
@TRANSACTIONCURRENCYID = FINANCIALTRANSACTION.TRANSACTIONCURRENCYID
from
dbo.FINANCIALTRANSACTION
where
FINANCIALTRANSACTION.ID = @REVENUEID;
declare @FILTERED_REVENUESPLIT table
(
REVENUEID uniqueidentifier,
REVENUESPLITID uniqueidentifier,
APPLICATIONID uniqueidentifier,
APPLICATIONREVENUESPLITID uniqueidentifier,
CONSTITUENTID uniqueidentifier,
CONSTITUENTLOOKUPID nvarchar(100),
CLAIMAMOUNT money,
DESIGNATIONID uniqueidentifier,
DESIGNATIONNAME nvarchar(512),
DESIGNATIONUSERID nvarchar(512),
CLAIMLOOKUPID nvarchar(100),
CLAIMDATE datetimeoffset,
ORIGINALREVENUELOOKUPID nvarchar(100),
ORIGINALREVENUEDATE datetimeoffset,
TRANSACTIONCURRENCYID uniqueidentifier,
CLAIMTRANSACTIONCURRENCYID uniqueidentifier
);
insert into @FILTERED_REVENUESPLIT
select
FT.ID,
null as REVENUESPLITID,
FTLI.FINANCIALTRANSACTIONID as APPLICATIONID,
FTLI.ID as APPLICATIONREVENUESPLITID,
CONSTITUENT.ID as CONSTITUENTID,
CONSTITUENT.LOOKUPID as CONSTITUENTLOOKUPID,
FTLI.TRANSACTIONAMOUNT as CLAIMAMOUNT,
RSE.DESIGNATIONID,
D.NAME as DESIGNATIONNAME,
D.USERID as DESIGNATIONUSERID,
FT.CALCULATEDUSERDEFINEDID as CLAIMLOOKUPID,
CAST(FT.DATE AS datetime) as CLAIMDATE,
FT1.CALCULATEDUSERDEFINEDID as ORIGINALREVENUELOOKUPID,
CAST(FT1.DATE AS datetime) as ORIGINALREVENUEDATE,
null as TRANSACTIONCURRENCYID,
FT.TRANSACTIONCURRENCYID as CLAIMTRANSACTIONCURRENCYID
from
dbo.FINANCIALTRANSACTION FT
inner join
dbo.FINANCIALTRANSACTIONLINEITEM FTLI on FT.ID = FTLI.FINANCIALTRANSACTIONID
inner join
dbo.REVENUESPLIT_EXT RSE on FTLI.ID = RSE.ID
inner join
dbo.CONSTITUENT C on FT.CONSTITUENTID = C.ID
inner join
dbo.DESIGNATION D on RSE.DESIGNATIONID = D.ID
left join
dbo.REVENUEMATCHINGGIFT MG1 on MG1.ID = FT.ID
left join
dbo.FINANCIALTRANSACTION FT1 on FT1.ID = MG1.MGSOURCEREVENUEID
left join
dbo.CONSTITUENT on CONSTITUENT.ID = FT1.CONSTITUENTID
where
FT.TYPECODE = 3 --Matching gift claim
and ((FTLI.DELETEDON is null) and (FTLI.TYPECODE <> 1))
and ((FT1.TYPECODE IN (0, 1, 2, 3, 4, 5, 6, 7, 8, 9)) and (FT1.DELETEDON is null))
';
if @INCLUDEPAIDCLAIMS = 0 and @HASNEWLYPAIDCLAIMS = 1
set @SQL = @SQL + N'
--Do not include claim splits that are unpaid in the database but that are paid in the UI.
and
FTLI.ID not in
(
select
NEWLYPAIDCLAIMTABLE.APPLICATIONREVENUESPLITID
from
#NEWLYPAIDCLAIMTABLE as NEWLYPAIDCLAIMTABLE
)
';
set @SQL = @SQL + N'
and
(
';
if @INCLUDEPAIDCLAIMS = 1 and @HASNEWLYPAIDCLAIMS = 1
set @SQL = @SQL + N'
--Include claim splits that are unpaid in the database but that are paid in the UI.
FTLI.ID in
(
select
NEWLYPAIDCLAIMTABLE.APPLICATIONREVENUESPLITID
from
#NEWLYPAIDCLAIMTABLE as NEWLYPAIDCLAIMTABLE
)
';
if (@INCLUDEPAIDCLAIMS = 1 and @HASNEWLYPAIDCLAIMS = 1) and @INCLUDEUNPAIDCLAIMS = 1
set @SQL = @SQL + N'
or
';
if @INCLUDEUNPAIDCLAIMS = 1
set @SQL = @SQL + N'
--Exclude matching gift claims paid by this payment, if they are
-- filtered in, they would come from the paid claims select statement
not exists
(
select
1
from
dbo.INSTALLMENTSPLIT
inner join dbo.INSTALLMENTSPLITPAYMENT on INSTALLMENTSPLIT.ID = INSTALLMENTSPLITPAYMENT.INSTALLMENTSPLITID
inner join dbo.FINANCIALTRANSACTIONLINEITEM as MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT on INSTALLMENTSPLITPAYMENT.PAYMENTID = MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT.ID
inner join dbo.FINANCIALTRANSACTION as FT_MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT on MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT.FINANCIALTRANSACTIONID = FT_MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT.ID
where
INSTALLMENTSPLIT.REVENUESPLITID = FTLI.ID
and MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT.FINANCIALTRANSACTIONID = @REVENUEID
and (FT_MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT.TYPECODE in (0, 1, 2, 3, 4, 5, 6, 7, 8, 9))
and (MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT.DELETEDON is null)
and (MATCHINGGIFTCLAIMPAYMENTREVENUESPLIT.TYPECODE <> 1)
)
';
set @SQL = @SQL + N'
)
and
(
(CAST(FT.DATE AS datetime) between @STARTDATE and @ENDDATE)
or (CAST(FT1.DATE AS datetime) between @STARTDATE and @ENDDATE)
)
and
(c.ID = @CONSTITUENTID
-- AdamBu - Bug 16530 - Include matching gift claims from orgs to which the given constituent is a parent corp.
or c.ID in(
select ID
from ORGANIZATIONDATA
where PARENTCORPID = @CONSTITUENTID
)
)
and
(not exists (select RETVAL.REVENUESPLITID from @RETVAL as RETVAL where RETVAL.REVENUESPLITID = FTLI.ID))';
if len(@NAMECLAUSE) > 0
begin
set @SQL = @SQL + N'
and ( ' + @NAMECLAUSE + N' )';
end
set @SQL = @SQL + N'
order by
CONSTITUENT.KEYNAME,
CONSTITUENT.FIRSTNAME,
CONSTITUENT.MIDDLENAME,
CONSTITUENT.LOOKUPID,
FT1.DATE,
FT1.CALCULATEDUSERDEFINEDID,
FT.DATE,
FT.CALCULATEDUSERDEFINEDID;
insert into @RETVAL
select' + @TOPMAXROWSCLAUSE + N'
RS.REVENUESPLITID,
RS.APPLICATIONID,
RS.APPLICATIONREVENUESPLITID,
RS.CONSTITUENTID,
null as SORTCONSTITUENTNAME,
RS.CONSTITUENTLOOKUPID,
0 as APPLIED,
RS.CLAIMAMOUNT,
RS.DESIGNATIONID,
RS.DESIGNATIONNAME,
RS.DESIGNATIONUSERID,
RS.CLAIMLOOKUPID,
RS.CLAIMDATE,
RS.ORIGINALREVENUELOOKUPID,
RS.ORIGINALREVENUEDATE,
@TRANSACTIONCURRENCYID as TRANSACTIONCURRENCYID,
RS.CLAIMTRANSACTIONCURRENCYID
from
@FILTERED_REVENUESPLIT RS
';
--JamesWill WI180281 2012-01-03 Only return records to which the user has site access
if @ISSYSADMIN = 0
set @SQL = @SQL + N'
inner join
dbo.UFN_REVENUE_USERHASDESIGNATIONSITEACCESS_BULK(@CURRENTAPPUSERID) ALLOWED on ALLOWED.ID = RS.REVENUEID
';
set @SQL = @SQL + N'
inner join
dbo.UFN_INSTALLMENT_GETINSTALLMENTSPLITBALANCE_BULK() as INSTALLMENTSPLIT
on INSTALLMENTSPLIT.PLEDGEID = RS.REVENUEID and INSTALLMENTSPLIT.DESIGNATIONID = RS.DESIGNATIONID
where
';
if @ISSYSADMIN = 0
set @SQL = @SQL + N'
ALLOWED.ACCESSGRANTED = 1 and
';
set @SQL = @SQL + N'
INSTALLMENTSPLIT.BALANCE > 0;
set @FOUND = @FOUND + @@ROWCOUNT;
end
';
set @SQL = @SQL + @UPDATESORTCONSTITUENTNAMESQL;
end
set @SQL = @SQL + N'
select
REVENUESPLITID,
APPLICATIONID,
APPLICATIONREVENUESPLITID,
SORTCONSTITUENTNAME,
CONSTITUENTLOOKUPID,
APPLIED,
CLAIMAMOUNT,
DESIGNATIONID,
DESIGNATIONNAME,
DESIGNATIONUSERID,
CLAIMLOOKUPID,
CLAIMDATE,
ORIGINALREVENUEDATE,
TRANSACTIONCURRENCYID,
CLAIMTRANSACTIONCURRENCYID
from
@RETVAL
order by
SORTCONSTITUENTNAME,
CONSTITUENTLOOKUPID,
ORIGINALREVENUEDATE,
ORIGINALREVENUELOOKUPID,
CLAIMDATE,
CLAIMLOOKUPID;
';
exec sp_executesql
@SQL,
--Parameter definition
N'@MAXROWS int,
@CURRENTAPPUSERID uniqueidentifier,
@REVENUEID uniqueidentifier,
@KEYNAME nvarchar(150),
@FIRSTNAME nvarchar(150),
@FULLNAME nvarchar(150),
@STARTDATE datetime,
@ENDDATE datetime',
--Parameters
@MAXROWS,
@CURRENTAPPUSERID,
@REVENUEID,
@KEYNAME,
@FIRSTNAME,
@FULLNAME,
@STARTDATE,
@ENDDATE;
/**
* Cleanup temp tables created for records that have client-side edits
*/
if @HASNEWLYUNPAIDCLAIMS = 1
drop table #NEWLYUNPAIDCLAIMTABLE;
if @HASNEWLYPAIDCLAIMS = 1
drop table #NEWLYPAIDCLAIMTABLE;