USP_DATALIST_COAUPDATESUBMITPROCESSOUTPUT
Returns a list of records for AddressFinder submit process output.
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @SELECTIONID | uniqueidentifier | IN | SelectionID |
| @INCLUDECONSTITUENTDNM | bit | IN | Include constituents marked as Do Not Mail |
| @ISPRIMARY | bit | IN | Is primary |
| @ADDRESSTYPECODEID | uniqueidentifier | IN | Address type |
| @INCLUDEADDRESSESDNM | bit | IN | Include addresses marked as Do Not Mail |
| @MAXROWS | int | IN | Input parameter indicating the maximum number of rows to return. |
| @OWNERID | uniqueidentifier | IN | |
| @SUBMITHOUSEHOLD | bit | 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_COAUPDATESUBMITPROCESSOUTPUT
(
@SELECTIONID uniqueidentifier = null,
@INCLUDECONSTITUENTDNM bit = 0,
@ISPRIMARY bit = 0,
@ADDRESSTYPECODEID uniqueidentifier = null,
@INCLUDEADDRESSESDNM bit = 0,
@MAXROWS int,
@OWNERID uniqueidentifier = null,
@SUBMITHOUSEHOLD bit = 0,
@CURRENTAPPUSERID uniqueidentifier = null,
@SECURITYFEATUREID uniqueidentifier = null,
@SECURITYFEATURETYPE tinyint = null
)
as
set nocount on;
declare @BYPASSSECURITY bit;
declare @BPID uniqueidentifier;
declare @BYPASSSITESECURITY bit;
set @BYPASSSECURITY = 0;
set @BPID = '113412a7-daa5-4ef6-9c0d-8aaa9024e06a';
set @CURRENTAPPUSERID = @OWNERID;
set @BYPASSSITESECURITY = 0;
if @OWNERID is not null
begin
select @BYPASSSECURITY = dbo.UFN_APPUSER_ISSYSADMIN(@OWNERID);
if @BYPASSSECURITY = 0
begin
select @BYPASSSECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONRACROLE(@OWNERID, @BPID);
select @BYPASSSITESECURITY = dbo.UFN_SECURITY_APPUSER_GRANTED_BUSINESSPROCESS_IN_NONSITEROLE(@OWNERID, @BPID);
end
else
set @BYPASSSITESECURITY = 1;
end
else
begin
set @BYPASSSECURITY = 1;
set @BYPASSSITESECURITY = 1;
end
if @SUBMITHOUSEHOLD = 1
begin
declare @MATCHING table(ADDRESSID uniqueidentifier, CONSTITUENTID uniqueidentifier, GROUPID uniqueidentifier, ISPRIMARY bit, ADDRESSBLOCK nvarchar(150), CITY nvarchar(100), POSTCODE nvarchar(12), STATEID uniqueidentifier, ADDRESSTYPECODEID uniqueidentifier);
insert into @MATCHING
(ADDRESSID, CONSTITUENTID, GROUPID, ISPRIMARY, ADDRESSBLOCK, CITY, POSTCODE, STATEID, ADDRESSTYPECODEID)
(select
ADDRESSID,
CONSTITUENTID,
GROUPID,
ISPRIMARY,
ADDRESSBLOCK,
CITY,
POSTCODE,
STATEID,
ADDRESSTYPECODEID
from dbo.UFN_COAUPDATE_GETMATCHINGHOUSEHOLDS(@INCLUDECONSTITUENTDNM, @ISPRIMARY, @ADDRESSTYPECODEID, @INCLUDEADDRESSESDNM, @SELECTIONID, @OWNERID));
end
declare @SELECTION table (CONSTITUENTID uniqueidentifier, ADDRESSID uniqueidentifier, LOOKUPID nvarchar(100), CONSTITUENTNAME nvarchar(154), ORGNAME nvarchar(154), ADDRESS nvarchar(150), CITY nvarchar(50), STATE nvarchar(100), POSTCODE nvarchar(12));
if @BYPASSSECURITY = 0 or @BYPASSSITESECURITY = 0
begin
if @SELECTIONID is not null
begin
insert into @SELECTION
(CONSTITUENTID, ADDRESSID, LOOKUPID, CONSTITUENTNAME, ORGNAME, ADDRESS, CITY, STATE, POSTCODE)
(select top(@MAXROWS) CONSTITUENTID,
ADDRESS.ID as ADDRESSID,
CONSTITUENT.LOOKUPID as LOOKUPID,
case when CONSTITUENT.ISORGANIZATION = 0 then CONSTITUENT.NAME end as CONSTITUENTNAME,
case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end as ORGNAME,
ADDRESS.ADDRESSBLOCK as ADDRESS,
ADDRESS.CITY as CITY,
dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
ADDRESS.POSTCODE as POSTCODE
from dbo.CONSTITUENT
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on CONSTITUENT.ID = SELECTION.ID and @SELECTIONID is not null
inner join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID and COUNTRY.USEFORCOAUPDATE = 1
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 0) and
(ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0) and
(ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null) and
(ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0) and
(CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT)) and
(@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null) and
(@BYPASSSITESECURITY = 1 or
exists(
select 1 from dbo.CONSTITUENT C
left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = C.ID
where
C.ID = CONSTITUENT.ID
and
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CONSTITUENTSITE].[SITEID] or (SITEID is null and [CONSTITUENTSITE].[SITEID] is null)))
)
)
);
end
else
begin
insert into @SELECTION
(CONSTITUENTID, ADDRESSID, LOOKUPID, CONSTITUENTNAME, ORGNAME, ADDRESS, CITY, STATE, POSTCODE)
(select top(@MAXROWS) CONSTITUENTID,
ADDRESS.ID as ADDRESSID,
CONSTITUENT.LOOKUPID as LOOKUPID,
case when CONSTITUENT.ISORGANIZATION = 0 then CONSTITUENT.NAME end as CONSTITUENTNAME,
case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end as ORGNAME,
ADDRESS.ADDRESSBLOCK as ADDRESS,
ADDRESS.CITY as CITY,
dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
ADDRESS.POSTCODE as POSTCODE
from dbo.CONSTITUENT
inner join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID and COUNTRY.USEFORCOAUPDATE = 1
left join dbo.UFN_SECURITY_APPUSER_GRANTED_CONSTITIDS_FORBUSINESSPROCESS(@OWNERID, @BPID) as CONSTIT_RACS on CONSTITUENT.ID = CONSTIT_RACS.ID
where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 0) and
(ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0) and
(ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null) and
(ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0) and
(CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT)) and
(@BYPASSSECURITY = 1 or CONSTIT_RACS.ID is not null) and
(@BYPASSSITESECURITY = 1 or
exists(
select 1 from dbo.CONSTITUENT C
left join dbo.CONSTITUENTSITE on CONSTITUENTSITE.CONSTITUENTID = C.ID
where
C.ID = CONSTITUENT.ID
and
(dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1 or exists (select 1 from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID,@SECURITYFEATUREID,@SECURITYFEATURETYPE) where SITEID=[CONSTITUENTSITE].[SITEID] or (SITEID is null and [CONSTITUENTSITE].[SITEID] is null)))
)
)
);
end
end
else
begin
if @SELECTIONID is not null
begin
insert into @SELECTION
(CONSTITUENTID, ADDRESSID, LOOKUPID, CONSTITUENTNAME, ORGNAME, ADDRESS, CITY, STATE, POSTCODE)
(select top(@MAXROWS) CONSTITUENTID,
ADDRESS.ID as ADDRESSID,
CONSTITUENT.LOOKUPID as LOOKUPID,
case when CONSTITUENT.ISORGANIZATION = 0 then CONSTITUENT.NAME end as CONSTITUENTNAME,
case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end as ORGNAME,
ADDRESS.ADDRESSBLOCK as ADDRESS,
ADDRESS.CITY as CITY,
dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
ADDRESS.POSTCODE as POSTCODE
from dbo.CONSTITUENT
inner join dbo.UFN_IDSETREADER_GETRESULTS_GUID(@SELECTIONID) SELECTION on CONSTITUENT.ID = SELECTION.ID and @SELECTIONID is not null
inner join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID and COUNTRY.USEFORCOAUPDATE = 1
where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 0) and
(ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0) and
(ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null) and
(ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0) and
(CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT)));
end
else
begin
insert into @SELECTION
(CONSTITUENTID, ADDRESSID, LOOKUPID, CONSTITUENTNAME, ORGNAME, ADDRESS, CITY, STATE, POSTCODE)
(select top(@MAXROWS) CONSTITUENTID,
ADDRESS.ID as ADDRESSID,
CONSTITUENT.LOOKUPID as LOOKUPID,
case when CONSTITUENT.ISORGANIZATION = 0 then CONSTITUENT.NAME end as CONSTITUENTNAME,
case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end as ORGNAME,
ADDRESS.ADDRESSBLOCK as ADDRESS,
ADDRESS.CITY as CITY,
dbo.UFN_STATE_GETDESCRIPTION(ADDRESS.STATEID) as STATE,
ADDRESS.POSTCODE as POSTCODE
from dbo.CONSTITUENT
inner join dbo.ADDRESS on ADDRESS.CONSTITUENTID = CONSTITUENT.ID
inner join dbo.COUNTRY on COUNTRY.ID = ADDRESS.COUNTRYID and COUNTRY.USEFORCOAUPDATE = 1
where (CONSTITUENT.DONOTMAIL = @INCLUDECONSTITUENTDNM or CONSTITUENT.DONOTMAIL = 0) and
(ADDRESS.ISPRIMARY = @ISPRIMARY or @ISPRIMARY = 0) and
(ADDRESS.ADDRESSTYPECODEID = @ADDRESSTYPECODEID or @ADDRESSTYPECODEID is null) and
(ADDRESS.DONOTMAIL = @INCLUDEADDRESSESDNM or ADDRESS.DONOTMAIL = 0) and
(CONSTITUENT.ID not in (select ID from dbo.DECEASEDCONSTITUENT)));
end
end
if @SUBMITHOUSEHOLD = 1
begin
select
CONSTITUENTID,
ADDRESSID,
LOOKUPID,
CONSTITUENTNAME,
ORGNAME,
ADDRESS,
CITY,
STATE,
POSTCODE
from @SELECTION
where
ADDRESSID not in (select ADDRESSID from @MATCHING)
union all
select distinct
ADDRESS.CONSTITUENTID,
ADDRESS.ID,
CONSTITUENT.LOOKUPID,
case when CONSTITUENT.ISORGANIZATION = 0 then (coalesce(CGM.NAME, CONSTITUENT.NAME)) end,
case when CONSTITUENT.ISORGANIZATION = 1 then CONSTITUENT.NAME end,
ADDRESS.ADDRESSBLOCK,
ADDRESS.CITY,
STATE.DESCRIPTION,
ADDRESS.POSTCODE
from dbo.CONSTITUENT as CONSTITUENT
inner join dbo.ADDRESS on
ADDRESS.CONSTITUENTID = CONSTITUENT.ID
inner join @MATCHING MATCHING on
ADDRESS.ID = MATCHING.ADDRESSID
left join dbo.GROUPMEMBER on
CONSTITUENT.ID = GROUPMEMBER.GROUPID
and GROUPMEMBER.ISPRIMARY = 1
left join dbo.CONSTITUENT CGM on
GROUPMEMBER.MEMBERID = CGM.ID
left join dbo.STATE on
MATCHING.STATEID = STATE.ID
where
CONSTITUENT.ISGROUP = 1;
end
else
begin
select
CONSTITUENTID,
ADDRESSID,
LOOKUPID,
CONSTITUENTNAME,
ORGNAME,
ADDRESS,
CITY,
STATE,
POSTCODE
from @SELECTION
end