UFN_ADDRESSPROCESS_ADDRESSES_CONSTITUENT_BULK
Returns addresses according to address processing rules for all constituents.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @ADDRESSPROCESSINGOPTIONID | uniqueidentifier | IN | |
| @MAILTYPE | tinyint | IN | |
| @PARAMETERSETID | uniqueidentifier | IN | |
| @DATE | datetime | IN | |
| @INCLUDEHOUSEHOLDPROCESSING | bit | IN | |
| @INDUSESEASONALADDRESS | bit | IN | |
| @ORGMAILINGPREFERENCE | tinyint | IN | |
| @INDALTADDRESS1TYPECODEID | uniqueidentifier | IN | |
| @INDALTADDRESS1ISPRIMARY | bit | IN | |
| @INDALTADDRESS2TYPECODEID | uniqueidentifier | IN | |
| @INDALTADDRESS2ISPRIMARY | bit | IN | |
| @ORGALTADDRESS1TYPECODEID | uniqueidentifier | IN | |
| @ORGALTADDRESS1ISPRIMARY | bit | IN | |
| @ORGALTADDRESS2TYPECODEID | uniqueidentifier | IN | |
| @ORGALTADDRESS2ISPRIMARY | bit | IN | |
| @INDINCLUDEWITHNOADDRESS | bit | IN | |
| @ORGINCLUDEWITHNOADDRESS | bit | IN | |
| @ORGINCLUDEWITHNOCONTACT | bit | IN | |
| @ORGSENDTOALLCONTACTS | bit | IN | |
| @INDUSECONSTITUENTPREFS | bit | IN | |
| @ORGUSECONSTITUENTPREFS | bit | IN | |
| @GROUPALTADDRESS1TYPECODEID | uniqueidentifier | IN | |
| @GROUPALTADDRESS1ISPRIMARY | bit | IN | |
| @GROUPALTADDRESS2TYPECODEID | uniqueidentifier | IN | |
| @GROUPALTADDRESS2ISPRIMARY | bit | IN | |
| @GROUPINCLUDEWITHNOADDRESS | bit | IN | |
| @GROUPUSECONSTITUENTPREFS | tinyint | IN |
Definition
Copy
CREATE function dbo.UFN_ADDRESSPROCESS_ADDRESSES_CONSTITUENT_BULK(
@ADDRESSPROCESSINGOPTIONID uniqueidentifier,
@MAILTYPE tinyint,
@PARAMETERSETID uniqueidentifier,
@DATE datetime,
@INCLUDEHOUSEHOLDPROCESSING bit,
@INDUSESEASONALADDRESS bit,
@ORGMAILINGPREFERENCE tinyint,
@INDALTADDRESS1TYPECODEID uniqueidentifier,
@INDALTADDRESS1ISPRIMARY bit,
@INDALTADDRESS2TYPECODEID uniqueidentifier,
@INDALTADDRESS2ISPRIMARY bit,
@ORGALTADDRESS1TYPECODEID uniqueidentifier,
@ORGALTADDRESS1ISPRIMARY bit,
@ORGALTADDRESS2TYPECODEID uniqueidentifier,
@ORGALTADDRESS2ISPRIMARY bit,
@INDINCLUDEWITHNOADDRESS bit,
@ORGINCLUDEWITHNOADDRESS bit,
@ORGINCLUDEWITHNOCONTACT bit,
@ORGSENDTOALLCONTACTS bit,
@INDUSECONSTITUENTPREFS bit,
@ORGUSECONSTITUENTPREFS bit,
@GROUPALTADDRESS1TYPECODEID uniqueidentifier,
@GROUPALTADDRESS1ISPRIMARY bit,
@GROUPALTADDRESS2TYPECODEID uniqueidentifier,
@GROUPALTADDRESS2ISPRIMARY bit,
@GROUPINCLUDEWITHNOADDRESS bit,
@GROUPUSECONSTITUENTPREFS tinyint
)
returns table
as
return (
select distinct
C.CONSTITUENTID as CONSTITUENTID,
coalesce(MP.ADDRESSID,SA.ADDRESSID,CA.ADDRESSID,A1.ADDRESSID,A2.ADDRESSID,O1.ADDRESSID,O2.ADDRESSID, G1.ADDRESSID, G2.ADDRESSID) as ADDRESSID,
coalesce(MP.CONTACTID,CA.CONTACTID) as CONTACTID,
coalesce(MP.POSITION,CA.POSITION) as POSITION,
C.HOUSEHOLDID,
C.RETURNEDASHOUSEHOLDMEMBER,
case
when (C.RETURNEDASHOUSEHOLDMEMBER = 1)
then coalesce(MP.GROUPCONTACTID, C.HOUSEHOLDID)
when (C.ISGROUP = 1 and C.HOUSEHOLDID is not null)
then coalesce(MP.GROUPCONTACTID, C.HOUSEHOLDID)
else null
end as GROUPCONTACTID
from dbo.UFN_ADDRESSPROCESS_GETCONSTITUENTS_BYCONSTITUENT_BULK(@DATE, @INCLUDEHOUSEHOLDPROCESSING) C
left join dbo.UFN_ADDRESSPROCESS_MAILPREFERENCES(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID,@DATE) as MP on C.ID = MP.CONSTITUENTID
left join dbo.UFN_ADDRESSPROCESS_SEASONALADDRESSES(@DATE) SA on C.ID = SA.CONSTITUENTID and @INDUSESEASONALADDRESS = 1
left join dbo.UFN_ADDRESSPROCESS_CONTACTADDRESSES(@ADDRESSPROCESSINGOPTIONID,@MAILTYPE,@PARAMETERSETID) CA on C.ID = CA.CONSTITUENTID and (@ORGMAILINGPREFERENCE = 0)
left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@INDALTADDRESS1TYPECODEID,@INDALTADDRESS1ISPRIMARY) as A1 on C.ID = A1.CONSTITUENTID AND C.ISORGANIZATION = 0 AND C.ISGROUP = 0
left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@INDALTADDRESS2TYPECODEID,@INDALTADDRESS2ISPRIMARY) as a2 on C.ID = a2.constituentid AND c.isorganization = 0 AND C.ISGROUP = 0
left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@ORGALTADDRESS1TYPECODEID,@ORGALTADDRESS1ISPRIMARY) as O1 on C.ID = O1.constituentid AND c.isorganization = 1 and (@ORGMAILINGPREFERENCE = 1)
left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@ORGALTADDRESS2TYPECODEID,@ORGALTADDRESS2ISPRIMARY) as O2 on C.ID = O2.constituentid AND c.isorganization = 1 and (@ORGMAILINGPREFERENCE = 1)
left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@GROUPALTADDRESS1TYPECODEID,@GROUPALTADDRESS1ISPRIMARY) as G1 on C.ID = G1.CONSTITUENTID AND C.ISGROUP = 1
left join dbo.UFN_ADDRESSPROCESS_ALTERNATEADDRESSES(@GROUPALTADDRESS2TYPECODEID,@GROUPALTADDRESS2ISPRIMARY ) as G2 on C.ID = G2.CONSTITUENTID AND C.ISGROUP = 1
where
(
(C.ISORGANIZATION = 1
and (
(@ORGMAILINGPREFERENCE = 0
and (
(@ORGINCLUDEWITHNOCONTACT = 0
and (MP.ADDRESSID is not null or CA.CONTACTID is not null)
)
or @ORGINCLUDEWITHNOCONTACT = 1
)
)
or (@ORGMAILINGPREFERENCE = 1
and (
(@ORGINCLUDEWITHNOADDRESS = 0
and coalesce(MP.ADDRESSID,CA.ADDRESSID,O1.ADDRESSID,O2.ADDRESSID) is not null
)
or @ORGINCLUDEWITHNOADDRESS = 1
)
)
)
)
or (C.ISORGANIZATION = 0
and C.ISGROUP = 0
and (
(@INDINCLUDEWITHNOADDRESS = 0
and coalesce(MP.ADDRESSID,SA.ADDRESSID,A1.ADDRESSID,A2.ADDRESSID) is not null
)
or @INDINCLUDEWITHNOADDRESS = 1
)
)
or (C.ISGROUP = 1
and (
(@GROUPINCLUDEWITHNOADDRESS = 0
and coalesce(MP.ADDRESSID,SA.ADDRESSID,G1.ADDRESSID,G2.ADDRESSID) is not null
)
or @GROUPINCLUDEWITHNOADDRESS = 1
)
)
)
and(
coalesce(
(
select SUPPRESSMAILING
from (
select top 1
case
when (SENDMAIL = 0
or (SENDMAIL = 1 and DELIVERYMETHODCODE = 1
and (
case
when exists(
select * from
dbo.[INSTALLEDPRODUCTLIST]
where [ID] = '42c15648-749e-4859-a56d-3a6474814cc7'
and ([EXPIREDATE] = ''
or [EXPIREDATE] >= getdate()
)
) then DONOTSENDOTHERCHANNEL
else 1
end
) = 1
)
) then 1
else 0
end as SUPPRESSMAILING,
case
when @MAILTYPE = 0 and ACKNOWLEDGEMENTID is not null
then 1 -- Revenue acknowledgements
when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null)
then 3 -- Appeals
when @MAILTYPE = 1 and (SITEID is not null and BUSINESSUNITCODEID is not null)
then 2 -- Appeals
when @MAILTYPE = 1 and (SITEID is not null and CATEGORYCODEID is not null)
then 2 -- Appeals
when @MAILTYPE = 1 and (BUSINESSUNITCODEID is not null and CATEGORYCODEID is not null)
then 2 -- Appeals
when @MAILTYPE = 1 and (SITEID is not null or BUSINESSUNITCODEID is not null or CATEGORYCODEID is not null)
then 1 -- Appeals
when @MAILTYPE = 2 and EVENTCATEGORYCODEID is not null and SITEID is not null
then 2 -- Events
when @MAILTYPE = 2 and (EVENTCATEGORYCODEID is not null or SITEID is not null)
then 1 -- Events
when @MAILTYPE = 3 and CORRESPONDENCECODEID is not null and CORRESPONDENCEID is not null
then 2 -- Correspondence
when @MAILTYPE = 3 and (CORRESPONDENCECODEID is not null or CORRESPONDENCEID is not null)
then 1 -- Correspondence
when @MAILTYPE = 4 and PLEDGEREMINDERID is not null
then 1 -- Reminders
when @MAILTYPE = 8 and PURPOSEID is not null
then 1 -- Stewardship
else 0
end as CRITERIAMATCHSCORE
from dbo.MAILPREFERENCE
where CONSTITUENTID = C.ID
and MAILTYPECODE = @MAILTYPE
and (
(C.ISORGANIZATION = 0
and C.ISGROUP = 0
and @INDUSECONSTITUENTPREFS = 1
)
or (C.ISORGANIZATION = 1
and @ORGUSECONSTITUENTPREFS = 1
)
or (C.ISGROUP = 1
and @GROUPUSECONSTITUENTPREFS = 1
)
)
and (
(@PARAMETERSETID =
case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
else null
end
)
or (
case @MAILTYPE
when 0 then ACKNOWLEDGEMENTID
when 3 then CORRESPONDENCEID
when 4 then PLEDGEREMINDERID
else null
end is null
)
)
and (CORRESPONDENCECODEID is null
or (@MAILTYPE = 3
and CORRESPONDENCECODEID = (
select CORRESPONDENCECODEID
from dbo.CORRESPONDENCEPROCESS
where ID = @PARAMETERSETID
)
)
)
and (EVENTCATEGORYCODEID is null
or (@MAILTYPE = 2
and EVENTCATEGORYCODEID = (
select E.EVENTCATEGORYCODEID
from dbo.EVENT E
inner join dbo.INVITATION I on E.ID = I.EVENTID
where I.ID = @PARAMETERSETID
)
)
)
and (SITEID is null
or (@MAILTYPE = 1
and SITEID = (
select SITEID
from dbo.APPEAL
where ID = @PARAMETERSETID
)
)
or (@MAILTYPE = 2
and SITEID in (
select E.SITEID
from dbo.EVENTSITE E
inner join dbo.INVITATION I on E.EVENTID = I.EVENTID
where I.ID = @PARAMETERSETID
)
)
)
and (PURPOSEID is null
or (@MAILTYPE = 8
and PURPOSEID = @PARAMETERSETID
)
)
and (BUSINESSUNITCODEID is null
or (@MAILTYPE = 1
and BUSINESSUNITCODEID in (
select BUSINESSUNITCODEID
from dbo.APPEALBUSINESSUNIT
where APPEALID = @PARAMETERSETID
)
)
)
and (CATEGORYCODEID is null
or (@MAILTYPE = 1
and CATEGORYCODEID = (
select APPEALCATEGORYCODEID
from dbo.APPEAL
where ID = @PARAMETERSETID
)
)
)
order by CRITERIAMATCHSCORE desc, SUPPRESSMAILING desc
) as MATCHEDPREFERENCES
)
, 0
) = 0 -- Verify that the comm prefs allow the user to receive mailings
)
)