USP_DATALIST_ACKNOWLEDGEMENTFORMATTEDOUTPUTEXTENDED
Returns a list of records from the acknowledgement process with selected formatting applied in acknowledgement form.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SELECTIONID | uniqueidentifier | IN | SelectionID |
| @MAXROWS | int | IN | Input parameter indicating the maximum number of rows to return. |
| @LETTERCODEID | uniqueidentifier | IN | LetterCodeID |
| @STARTDATE | datetime | IN | |
| @EXCLUDEDECEASED | bit | IN | |
| @EXCLUDEINACTIVE | bit | IN | |
| @INCLUSIONS | xml | IN | |
| @EXCLUSIONS | xml | IN | |
| @NAMEFORMATPARAMETERID | uniqueidentifier | IN | Name format ID |
| @ADDRESSPROCESSINGOPTIONID | uniqueidentifier | IN | Address format |
| @PARAMETERSETID | uniqueidentifier | IN | Parameter set ID |
| @OWNERID | 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. |
Definition
Copy
CREATE procedure dbo.USP_DATALIST_ACKNOWLEDGEMENTFORMATTEDOUTPUTEXTENDED
(
@SELECTIONID uniqueidentifier = null,
@MAXROWS int,
@LETTERCODEID uniqueidentifier,
@STARTDATE datetime = null,
@EXCLUDEDECEASED bit = null,
@EXCLUDEINACTIVE bit = null,
@INCLUSIONS xml = null,
@EXCLUSIONS xml = null,
@NAMEFORMATPARAMETERID uniqueidentifier = null,
@ADDRESSPROCESSINGOPTIONID uniqueidentifier = null,
@PARAMETERSETID uniqueidentifier = null,
@OWNERID uniqueidentifier = null,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
/*
This is not really used anymore for preview but the metadata is for the BP
The Stored procedure has become to complex to be loaded anymore.
Ideally we should rewrite
declare @BYPASSSECURITY bit;
declare @BPID uniqueidentifier;
set @BPID = '02E8A918-FB90-4C3D-A70E-821DB5DA7A1F';
set @BYPASSSECURITY = 0;
set @CURRENTAPPUSERID = @OWNERID;
if @OWNERID is not null
begin
select @BYPASSSECURITY = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);
if @BYPASSSECURITY = 0
select @BYPASSSECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONRACROLE(@OWNERID, @BPID);
end
else
set @BYPASSSECURITY = 1;
declare @USEADDRESSEEFORMAT bit
declare @ADDRESSEEFORMATID uniqueidentifier
declare @ADDRESSEEFORMATISPRIMARY bit
declare @ALTADDRESSEEFORMATID uniqueidentifier
declare @ALTADDRESSEEFORMATISPRIMARY bit
declare @ADDRESSEEFUNCTIONID uniqueidentifier
declare @USESALUTATIONFORMAT bit
declare @SALUTATIONFORMATID uniqueidentifier
declare @SALUTATIONFORMATISPRIMARY bit
declare @ALTSALUTATIONFORMATID uniqueidentifier
declare @ALTSALUTATIONFORMATISPRIMARY bit
declare @SALUTATIONFUNCTIONID uniqueidentifier
declare @USECONTACTADDRESSEEFORMAT bit
declare @CONTACTADDRESSEEFORMATID uniqueidentifier
declare @CONTACTADDRESSEEFORMATISPRIMARY bit
declare @ALTCONTACTADDRESSEEFORMATID uniqueidentifier
declare @ALTCONTACTADDRESSEEFORMATISPRIMARY bit
declare @CONTACTADDRESSEEFUNCTIONID uniqueidentifier
declare @ORGSALUTATIONCODE tinyint
declare @USECONTACTSALUTATIONFORMAT bit
declare @CONTACTSALUTATIONFORMATID uniqueidentifier
declare @CONTACTSALUTATIONFORMATISPRIMARY bit
declare @ALTCONTACTSALUTATIONFORMATID uniqueidentifier
declare @ALTCONTACTSALUTATIONFORMATISPRIMARY bit
declare @CONTACTSALUTATIONFUNCTIONID uniqueidentifier
declare @CONTACTSALUTATIONOPTIONCODE tinyint
declare @CUSTOMNAME nvarchar(100)
declare @INDUSESEASONALADDRESS bit
declare @ORGMAILINGPREFERENCE tinyint
declare @INDALTADDRESS1TYPECODEID uniqueidentifier
declare @INDALTADDRESS1ISPRIMARY bit
declare @INDALTADDRESS2TYPECODEID uniqueidentifier
declare @INDALTADDRESS2ISPRIMARY bit
declare @ORGALTADDRESS1TYPECODEID uniqueidentifier
declare @ORGALTADDRESS1ISPRIMARY bit
declare @ORGALTADDRESS2TYPECODEID uniqueidentifier
declare @ORGALTADDRESS2ISPRIMARY bit
declare @INDINCLUDEWITHNOADDRESS bit
declare @ORGINCLUDEWITHNOADDRESS bit
declare @ORGINCLUDEWITHNOCONTACT bit
declare @INDUSECONSTITUENTPREFS bit
declare @ORGUSECONSTITUENTPREFS bit
declare @GROUPALTADDRESS1TYPECODEID uniqueidentifier
declare @GROUPALTADDRESS1ISPRIMARY bit
declare @GROUPALTADDRESS2TYPECODEID uniqueidentifier
declare @GROUPALTADDRESS2ISPRIMARY bit
declare @GROUPINCLUDEWITHNOADDRESS bit
declare @GROUPUSECONSTITUENTPREFS tinyint
declare @ORGSENDTOALLCONTACTS bit
select @USEADDRESSEEFORMAT=USEADDRESSEEFORMAT,
@ADDRESSEEFORMATID=ADDRESSEEFORMATID,
@ADDRESSEEFORMATISPRIMARY=ADDRESSEEFORMATISPRIMARY,
@ALTADDRESSEEFORMATID=ALTADDRESSEEFORMATID,
@ALTADDRESSEEFORMATISPRIMARY=ALTADDRESSEEFORMATISPRIMARY,
@ADDRESSEEFUNCTIONID=ADDRESSEEFUNCTIONID,
@USESALUTATIONFORMAT=USESALUTATIONFORMAT,
@SALUTATIONFORMATID=SALUTATIONFORMATID,
@SALUTATIONFORMATISPRIMARY=SALUTATIONFORMATISPRIMARY,
@ALTSALUTATIONFORMATID=ALTSALUTATIONFORMATID,
@ALTSALUTATIONFORMATISPRIMARY=ALTSALUTATIONFORMATISPRIMARY,
@SALUTATIONFUNCTIONID=SALUTATIONFUNCTIONID,
@USECONTACTADDRESSEEFORMAT=USECONTACTADDRESSEEFORMAT,
@CONTACTADDRESSEEFORMATID=CONTACTADDRESSEEFORMATID,
@CONTACTADDRESSEEFORMATISPRIMARY=CONTACTADDRESSEEFORMATISPRIMARY,
@ALTCONTACTADDRESSEEFORMATID=ALTCONTACTADDRESSEEFORMATID,
@ALTCONTACTADDRESSEEFORMATISPRIMARY=ALTCONTACTADDRESSEEFORMATISPRIMARY,
@CONTACTADDRESSEEFUNCTIONID=CONTACTADDRESSEEFUNCTIONID,
@ORGSALUTATIONCODE=ORGSALUTATIONCODE,
@USECONTACTSALUTATIONFORMAT=USECONTACTSALUTATIONFORMAT,
@CONTACTSALUTATIONFORMATID=CONTACTSALUTATIONFORMATID,
@CONTACTSALUTATIONFORMATISPRIMARY=CONTACTSALUTATIONFORMATISPRIMARY,
@ALTCONTACTSALUTATIONFORMATID=ALTCONTACTSALUTATIONFORMATID,
@ALTCONTACTSALUTATIONFORMATISPRIMARY=ALTCONTACTSALUTATIONFORMATISPRIMARY,
@CONTACTSALUTATIONFUNCTIONID=CONTACTSALUTATIONFUNCTIONID,
@CONTACTSALUTATIONOPTIONCODE=CONTACTSALUTATIONOPTIONCODE,
@CUSTOMNAME=CUSTOMNAME
from dbo.NAMEFORMATPARAMETER
where (ID = @NAMEFORMATPARAMETERID) or (@NAMEFORMATPARAMETERID is null and ISDEFAULT = 1)
select @INDUSESEASONALADDRESS=INDUSESEASONALADDRESS,
@ORGMAILINGPREFERENCE=ORGMAILINGPREFERENCE,
@INDALTADDRESS1TYPECODEID=INDALTADDRESS1TYPECODEID,
@INDALTADDRESS1ISPRIMARY=INDALTADDRESS1ISPRIMARY,
@INDALTADDRESS2TYPECODEID=INDALTADDRESS2TYPECODEID,
@INDALTADDRESS2ISPRIMARY=INDALTADDRESS2ISPRIMARY,
@ORGALTADDRESS1TYPECODEID=ORGALTADDRESS1TYPECODEID,
@ORGALTADDRESS1ISPRIMARY=ORGALTADDRESS1ISPRIMARY,
@ORGALTADDRESS2TYPECODEID=ORGALTADDRESS2TYPECODEID,
@ORGALTADDRESS2ISPRIMARY=ORGALTADDRESS2ISPRIMARY,
@INDINCLUDEWITHNOADDRESS=INDINCLUDEWITHNOADDRESS,
@ORGINCLUDEWITHNOADDRESS=ORGINCLUDEWITHNOADDRESS,
@ORGINCLUDEWITHNOCONTACT=ORGINCLUDEWITHNOCONTACT,
@ORGSENDTOALLCONTACTS=ORGSENDTOALLCONTACTS,
@INDUSECONSTITUENTPREFS=INDUSECONSTITUENTPREFS,
@ORGUSECONSTITUENTPREFS=ORGUSECONSTITUENTPREFS,
@GROUPALTADDRESS1TYPECODEID=GROUPALTADDRESS1TYPECODEID,
@GROUPALTADDRESS1ISPRIMARY=GROUPALTADDRESS1ISPRIMARY,
@GROUPALTADDRESS2TYPECODEID=GROUPALTADDRESS2TYPECODEID,
@GROUPALTADDRESS2ISPRIMARY=GROUPALTADDRESS2ISPRIMARY,
@GROUPINCLUDEWITHNOADDRESS=GROUPINCLUDEWITHNOADDRESS,
@GROUPUSECONSTITUENTPREFS=GROUPUSECONSTITUENTPREFS
from dbo.ADDRESSPROCESSINGOPTION
where ID = @ADDRESSPROCESSINGOPTIONID or (@ADDRESSPROCESSINGOPTIONID is null and ISDEFAULT = 1);
if @BYPASSSECURITY = 0
begin
if @SELECTIONID is null
begin
set nocount on;
with REVENUELETTER_CTE as (
select top (@MAXROWS)
REVENUELETTER.ID as REVENUELETTERID,
REVENUELETTER.ACKNOWLEDGEEID as ACKNOWLEDGEEID,
REVENUELETTER.REVENUEID
from
dbo.REVENUELETTER with (nolock)
inner join dbo.REVENUE on REVENUELETTER.REVENUEID = REVENUE.ID
inner join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on REVENUE.CONSTITUENTID = CONSTIT_RACS.ID
inner join dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUELETTER.ACKNOWLEDGEEID = EXC.ID
where
REVENUELETTER.LETTERCODEID = @LETTERCODEID
and REVENUELETTER.PROCESSDATE is null
and REVENUELETTER.ACKNOWLEDGEDATE is null
and REVENUE.DONOTACKNOWLEDGE = 0
and REVENUE.TRANSACTIONTYPECODE <> 3
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*
from
REVENUELETTER_CTE RL with (nolock)
cross apply dbo.UFN_QUERY_ACKNOWLEDGEMENTOUTPUTEXTENDED(RL.ACKNOWLEDGEEID, RL.REVENUELETTERID, RL.REVENUEID, @NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID, @PARAMETERSETID, @STARTDATE, @USEADDRESSEEFORMAT,@ADDRESSEEFORMATID, @ADDRESSEEFORMATISPRIMARY, @ALTADDRESSEEFORMATID, @ALTADDRESSEEFORMATISPRIMARY, @ADDRESSEEFUNCTIONID, @USESALUTATIONFORMAT, @SALUTATIONFORMATID, @SALUTATIONFORMATISPRIMARY, @ALTSALUTATIONFORMATID, @ALTSALUTATIONFORMATISPRIMARY, @SALUTATIONFUNCTIONID, @USECONTACTADDRESSEEFORMAT, @CONTACTADDRESSEEFORMATID, @CONTACTADDRESSEEFORMATISPRIMARY, @ALTCONTACTADDRESSEEFORMATID, @ALTCONTACTADDRESSEEFORMATISPRIMARY, @CONTACTADDRESSEEFUNCTIONID, @ORGSALUTATIONCODE , @USECONTACTSALUTATIONFORMAT, @CONTACTSALUTATIONFORMATID, @CONTACTSALUTATIONFORMATISPRIMARY, @ALTCONTACTSALUTATIONFORMATID, @ALTCONTACTSALUTATIONFORMATISPRIMARY, @CONTACTSALUTATIONFUNCTIONID, @CONTACTSALUTATIONOPTIONCODE, @CUSTOMNAME, @INDUSESEASONALADDRESS, @ORGMAILINGPREFERENCE, @INDALTADDRESS1TYPECODEID, @INDALTADDRESS1ISPRIMARY, @INDALTADDRESS2TYPECODEID, @INDALTADDRESS2ISPRIMARY, @ORGALTADDRESS1TYPECODEID, @ORGALTADDRESS1ISPRIMARY, @ORGALTADDRESS2TYPECODEID, @ORGALTADDRESS2ISPRIMARY, @INDINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOCONTACT, @ORGSENDTOALLCONTACTS, @INDUSECONSTITUENTPREFS, @ORGUSECONSTITUENTPREFS, @GROUPALTADDRESS1TYPECODEID, @GROUPALTADDRESS1ISPRIMARY, @GROUPALTADDRESS2TYPECODEID, @GROUPALTADDRESS2ISPRIMARY, @GROUPINCLUDEWITHNOADDRESS, @GROUPUSECONSTITUENTPREFS) STANDARDVIEW
--order by
-- STANDARDVIEW.CONSTITUENTNAME;
end
else
begin
set nocount on;
with REVENUELETTER_CTE as (
select top (@MAXROWS)
REVENUELETTER.ID as REVENUELETTERID,
REVENUELETTER.ACKNOWLEDGEEID as ACKNOWLEDGEEID,
REVENUELETTER.REVENUEID
from
dbo.REVENUELETTER with (nolock)
inner join dbo.REVENUE on REVENUELETTER.REVENUEID = REVENUE.ID
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on REVENUELETTER.REVENUEID = SELECTION.ID and @SELECTIONID is not null
inner join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on REVENUE.CONSTITUENTID = CONSTIT_RACS.ID
inner join dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUELETTER.ACKNOWLEDGEEID = EXC.ID
where
REVENUELETTER.LETTERCODEID = @LETTERCODEID
and REVENUELETTER.PROCESSDATE is null
and REVENUELETTER.ACKNOWLEDGEDATE is null
and REVENUE.DONOTACKNOWLEDGE = 0
and REVENUE.TRANSACTIONTYPECODE <> 3
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*
from
REVENUELETTER_CTE RL
cross apply dbo.UFN_QUERY_ACKNOWLEDGEMENTOUTPUTEXTENDED(RL.ACKNOWLEDGEEID, RL.REVENUELETTERID, RL.REVENUEID, @NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID, @PARAMETERSETID, @STARTDATE, @USEADDRESSEEFORMAT,@ADDRESSEEFORMATID, @ADDRESSEEFORMATISPRIMARY, @ALTADDRESSEEFORMATID, @ALTADDRESSEEFORMATISPRIMARY, @ADDRESSEEFUNCTIONID, @USESALUTATIONFORMAT, @SALUTATIONFORMATID, @SALUTATIONFORMATISPRIMARY, @ALTSALUTATIONFORMATID, @ALTSALUTATIONFORMATISPRIMARY, @SALUTATIONFUNCTIONID, @USECONTACTADDRESSEEFORMAT, @CONTACTADDRESSEEFORMATID, @CONTACTADDRESSEEFORMATISPRIMARY, @ALTCONTACTADDRESSEEFORMATID, @ALTCONTACTADDRESSEEFORMATISPRIMARY, @CONTACTADDRESSEEFUNCTIONID, @ORGSALUTATIONCODE , @USECONTACTSALUTATIONFORMAT, @CONTACTSALUTATIONFORMATID, @CONTACTSALUTATIONFORMATISPRIMARY, @ALTCONTACTSALUTATIONFORMATID, @ALTCONTACTSALUTATIONFORMATISPRIMARY, @CONTACTSALUTATIONFUNCTIONID, @CONTACTSALUTATIONOPTIONCODE, @CUSTOMNAME, @INDUSESEASONALADDRESS, @ORGMAILINGPREFERENCE, @INDALTADDRESS1TYPECODEID, @INDALTADDRESS1ISPRIMARY, @INDALTADDRESS2TYPECODEID, @INDALTADDRESS2ISPRIMARY, @ORGALTADDRESS1TYPECODEID, @ORGALTADDRESS1ISPRIMARY, @ORGALTADDRESS2TYPECODEID, @ORGALTADDRESS2ISPRIMARY, @INDINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOCONTACT, @ORGSENDTOALLCONTACTS, @INDUSECONSTITUENTPREFS, @ORGUSECONSTITUENTPREFS, @GROUPALTADDRESS1TYPECODEID, @GROUPALTADDRESS1ISPRIMARY, @GROUPALTADDRESS2TYPECODEID, @GROUPALTADDRESS2ISPRIMARY, @GROUPINCLUDEWITHNOADDRESS, @GROUPUSECONSTITUENTPREFS) STANDARDVIEW
--order by
-- STANDARDVIEW.CONSTITUENTNAME;
end
end
else
begin
if @SELECTIONID is null
begin
set nocount on;
with REVENUELETTER_CTE as (
select top (@MAXROWS)
REVENUELETTER.ID as REVENUELETTERID,
REVENUELETTER.ACKNOWLEDGEEID as ACKNOWLEDGEEID,
REVENUELETTER.REVENUEID
from
dbo.REVENUELETTER with (nolock)
inner join dbo.REVENUE on REVENUELETTER.REVENUEID = REVENUE.ID
inner join dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUELETTER.ACKNOWLEDGEEID = EXC.ID
where
REVENUELETTER.LETTERCODEID = @LETTERCODEID
and REVENUELETTER.PROCESSDATE is null
and REVENUELETTER.ACKNOWLEDGEDATE is null
and REVENUE.DONOTACKNOWLEDGE = 0
and REVENUE.TRANSACTIONTYPECODE <> 3
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*
from
REVENUELETTER_CTE RL with (nolock)
cross apply dbo.UFN_QUERY_ACKNOWLEDGEMENTOUTPUTEXTENDED(RL.ACKNOWLEDGEEID, RL.REVENUELETTERID, RL.REVENUEID, @NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID, @PARAMETERSETID, @STARTDATE, @USEADDRESSEEFORMAT,@ADDRESSEEFORMATID, @ADDRESSEEFORMATISPRIMARY, @ALTADDRESSEEFORMATID, @ALTADDRESSEEFORMATISPRIMARY, @ADDRESSEEFUNCTIONID, @USESALUTATIONFORMAT, @SALUTATIONFORMATID, @SALUTATIONFORMATISPRIMARY, @ALTSALUTATIONFORMATID, @ALTSALUTATIONFORMATISPRIMARY, @SALUTATIONFUNCTIONID, @USECONTACTADDRESSEEFORMAT, @CONTACTADDRESSEEFORMATID, @CONTACTADDRESSEEFORMATISPRIMARY, @ALTCONTACTADDRESSEEFORMATID, @ALTCONTACTADDRESSEEFORMATISPRIMARY, @CONTACTADDRESSEEFUNCTIONID, @ORGSALUTATIONCODE , @USECONTACTSALUTATIONFORMAT, @CONTACTSALUTATIONFORMATID, @CONTACTSALUTATIONFORMATISPRIMARY, @ALTCONTACTSALUTATIONFORMATID, @ALTCONTACTSALUTATIONFORMATISPRIMARY, @CONTACTSALUTATIONFUNCTIONID, @CONTACTSALUTATIONOPTIONCODE, @CUSTOMNAME, @INDUSESEASONALADDRESS, @ORGMAILINGPREFERENCE, @INDALTADDRESS1TYPECODEID, @INDALTADDRESS1ISPRIMARY, @INDALTADDRESS2TYPECODEID, @INDALTADDRESS2ISPRIMARY, @ORGALTADDRESS1TYPECODEID, @ORGALTADDRESS1ISPRIMARY, @ORGALTADDRESS2TYPECODEID, @ORGALTADDRESS2ISPRIMARY, @INDINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOCONTACT, @ORGSENDTOALLCONTACTS, @INDUSECONSTITUENTPREFS, @ORGUSECONSTITUENTPREFS, @GROUPALTADDRESS1TYPECODEID, @GROUPALTADDRESS1ISPRIMARY, @GROUPALTADDRESS2TYPECODEID, @GROUPALTADDRESS2ISPRIMARY, @GROUPINCLUDEWITHNOADDRESS, @GROUPUSECONSTITUENTPREFS) STANDARDVIEW
--order by
-- STANDARDVIEW.CONSTITUENTNAME;
end
else
begin
set nocount on;
with REVENUELETTER_CTE as (
select top (@MAXROWS)
REVENUELETTER.ID as REVENUELETTERID,
REVENUELETTER.ACKNOWLEDGEEID as ACKNOWLEDGEEID,
REVENUELETTER.REVENUEID
from
dbo.REVENUELETTER with (nolock)
inner join dbo.REVENUE on REVENUELETTER.REVENUEID = REVENUE.ID
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on REVENUELETTER.REVENUEID = SELECTION.ID and @SELECTIONID is not null
inner join dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUELETTER.ACKNOWLEDGEEID = EXC.ID
where
REVENUELETTER.LETTERCODEID = @LETTERCODEID
and REVENUELETTER.PROCESSDATE is null
and REVENUELETTER.ACKNOWLEDGEDATE is null
and REVENUE.DONOTACKNOWLEDGE = 0
and REVENUE.TRANSACTIONTYPECODE <> 3
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*
from
REVENUELETTER_CTE RL
cross apply dbo.UFN_QUERY_ACKNOWLEDGEMENTOUTPUTEXTENDED(RL.ACKNOWLEDGEEID, RL.REVENUELETTERID, RL.REVENUEID, @NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID, @PARAMETERSETID, @STARTDATE, @USEADDRESSEEFORMAT,@ADDRESSEEFORMATID, @ADDRESSEEFORMATISPRIMARY, @ALTADDRESSEEFORMATID, @ALTADDRESSEEFORMATISPRIMARY, @ADDRESSEEFUNCTIONID, @USESALUTATIONFORMAT, @SALUTATIONFORMATID, @SALUTATIONFORMATISPRIMARY, @ALTSALUTATIONFORMATID, @ALTSALUTATIONFORMATISPRIMARY, @SALUTATIONFUNCTIONID, @USECONTACTADDRESSEEFORMAT, @CONTACTADDRESSEEFORMATID, @CONTACTADDRESSEEFORMATISPRIMARY, @ALTCONTACTADDRESSEEFORMATID, @ALTCONTACTADDRESSEEFORMATISPRIMARY, @CONTACTADDRESSEEFUNCTIONID, @ORGSALUTATIONCODE , @USECONTACTSALUTATIONFORMAT, @CONTACTSALUTATIONFORMATID, @CONTACTSALUTATIONFORMATISPRIMARY, @ALTCONTACTSALUTATIONFORMATID, @ALTCONTACTSALUTATIONFORMATISPRIMARY, @CONTACTSALUTATIONFUNCTIONID, @CONTACTSALUTATIONOPTIONCODE, @CUSTOMNAME, @INDUSESEASONALADDRESS, @ORGMAILINGPREFERENCE, @INDALTADDRESS1TYPECODEID, @INDALTADDRESS1ISPRIMARY, @INDALTADDRESS2TYPECODEID, @INDALTADDRESS2ISPRIMARY, @ORGALTADDRESS1TYPECODEID, @ORGALTADDRESS1ISPRIMARY, @ORGALTADDRESS2TYPECODEID, @ORGALTADDRESS2ISPRIMARY, @INDINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOADDRESS, @ORGINCLUDEWITHNOCONTACT, @ORGSENDTOALLCONTACTS, @INDUSECONSTITUENTPREFS, @ORGUSECONSTITUENTPREFS, @GROUPALTADDRESS1TYPECODEID, @GROUPALTADDRESS1ISPRIMARY, @GROUPALTADDRESS2TYPECODEID, @GROUPALTADDRESS2ISPRIMARY, @GROUPINCLUDEWITHNOADDRESS, @GROUPUSECONSTITUENTPREFS) STANDARDVIEW
--order by
-- STANDARDVIEW.CONSTITUENTNAME;
end
end
*/
with REVENUELETTER_CTE as (
select top (@MAXROWS)
REVENUELETTER.ID as REVENUELETTERID,
REVENUELETTER.ACKNOWLEDGEEID as ACKNOWLEDGEEID,
REVENUELETTER.REVENUEID
from
dbo.REVENUELETTER with (nolock)
inner join dbo.REVENUE on REVENUELETTER.REVENUEID = REVENUE.ID
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on REVENUELETTER.REVENUEID = SELECTION.ID and @SELECTIONID is not null
inner join dbo.UFN_BUSINESSPROCESSCOMMPREF_GETINCLUDEDCONSTITUENT(@STARTDATE, @EXCLUDEDECEASED, @EXCLUDEINACTIVE, @INCLUSIONS, @EXCLUSIONS) AS EXC ON REVENUELETTER.ACKNOWLEDGEEID = EXC.ID
where
REVENUELETTER.LETTERCODEID = @LETTERCODEID
and REVENUELETTER.PROCESSDATE is null
and REVENUELETTER.ACKNOWLEDGEDATE is null
and REVENUE.DONOTACKNOWLEDGE = 0
and REVENUE.TRANSACTIONTYPECODE <> 3
and exists
(
select top 1 RSSUB.ID from dbo.REVENUESPLIT RSSUB
cross apply dbo.UFN_SITEID_MAPFROM_REVENUESPLITID(RSSUB.ID) REVSITES
where RSSUB.REVENUEID = REVENUE.ID
and (dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[REVSITES].[SITEID] or (SITEID is null and [REVSITES].[SITEID] is null)))
)
)
select top (@MAXROWS)
STANDARDVIEW.*
from
REVENUELETTER_CTE RL
inner join dbo.UFN_QUERY_ACKNOWLEDGEMENTOUTPUTEXTENDED(@NAMEFORMATPARAMETERID,@ADDRESSPROCESSINGOPTIONID, @PARAMETERSETID, @STARTDATE) STANDARDVIEW
on STANDARDVIEW.REVENUEID = RL.REVENUEID
and STANDARDVIEW.REVENUELETTERID = RL.REVENUELETTERID
and STANDARDVIEW.CONSTITUENTID = RL.ACKNOWLEDGEEID