UFN_TRIBUTEACKNOWLEDGEMENT_GETNAMEFORMATS
This function returns a table of name formats for the specified tribute acknowledgement process.
Return
| Return Type |
|---|
| table |
Parameters
| Parameter | Parameter Type | Mode | Description |
|---|---|---|---|
| @TRIBUTEACKNOWLEDGEMENTPROCESSID | uniqueidentifier | IN |
Definition
Copy
create function dbo.UFN_TRIBUTEACKNOWLEDGEMENT_GETNAMEFORMATS
(
@TRIBUTEACKNOWLEDGEMENTPROCESSID uniqueidentifier
)
returns @NAMEFORMAT table
(
CONSTITUENTID uniqueidentifier not null,
ADDRESSEE nvarchar(700),
SALUTATION nvarchar(700),
CONTACT nvarchar(700),
POSITION nvarchar(50)
)
as
begin
declare @ADDRESSEEFORMATID uniqueidentifier;
declare @ALTADDRESSEEFORMATID uniqueidentifier;
declare @ADDRESSEEFUNCTIONID uniqueidentifier;
declare @SALUTATIONFORMATID uniqueidentifier;
declare @ALTSALUTATIONFORMATID uniqueidentifier;
declare @SALUTATIONFUNCTIONID uniqueidentifier;
declare @CONTACTADDRESSEEFORMATID uniqueidentifier;
declare @ALTCONTACTADDRESSEEFORMATID uniqueidentifier;
declare @CONTACTADDRESSEEFUNCTIONID uniqueidentifier;
declare @ORGSALUTATIONCODE tinyint;
declare @CONTACTSALUTATIONFORMATID uniqueidentifier;
declare @ALTCONTACTSALUTATIONFORMATID uniqueidentifier;
declare @CONTACTSALUTATIONFUNCTIONID uniqueidentifier;
declare @CONTACTSALUTATIONOPTIONCODE bit;
declare @CUSTOMNAME nvarchar(100);
if exists(select ID from dbo.TRIBUTEACKNOWLEDGEMENTPROCESSNAMEFORMAT where TRIBUTEACKNOWLEDGEMENTPROCESSID = @TRIBUTEACKNOWLEDGEMENTPROCESSID)
begin
select
@ADDRESSEEFORMATID = ADDRESSEEFORMATID,
@ALTADDRESSEEFORMATID = ALTADDRESSEEFORMATID,
@ADDRESSEEFUNCTIONID = ADDRESSEEFUNCTIONID,
@SALUTATIONFORMATID = SALUTATIONFORMATID,
@ALTSALUTATIONFORMATID = ALTSALUTATIONFORMATID,
@SALUTATIONFUNCTIONID = SALUTATIONFUNCTIONID,
@CONTACTADDRESSEEFORMATID = CONTACTADDRESSEEFORMATID,
@ALTCONTACTADDRESSEEFORMATID = ALTCONTACTADDRESSEEFORMATID,
@CONTACTADDRESSEEFUNCTIONID = CONTACTADDRESSEEFUNCTIONID,
@ORGSALUTATIONCODE = ORGSALUTATIONCODE,
@CONTACTSALUTATIONFORMATID = CONTACTSALUTATIONFORMATID,
@ALTCONTACTSALUTATIONFORMATID = ALTCONTACTSALUTATIONFORMATID,
@CONTACTSALUTATIONFUNCTIONID = CONTACTSALUTATIONFUNCTIONID,
@CONTACTSALUTATIONOPTIONCODE = CONTACTSALUTATIONOPTIONCODE,
@CUSTOMNAME = CUSTOMNAME
from
dbo.TRIBUTEACKNOWLEDGEMENTPROCESSNAMEFORMAT
where
TRIBUTEACKNOWLEDGEMENTPROCESSID = @TRIBUTEACKNOWLEDGEMENTPROCESSID;
end
else
begin
select
@ADDRESSEEFORMATID = ADDRESSEEFORMATID,
@ALTADDRESSEEFORMATID = ALTADDRESSEEFORMATID,
@ADDRESSEEFUNCTIONID = ADDRESSEEFUNCTIONID,
@SALUTATIONFORMATID = SALUTATIONFORMATID,
@ALTSALUTATIONFORMATID = ALTSALUTATIONFORMATID,
@SALUTATIONFUNCTIONID = SALUTATIONFUNCTIONID,
@CONTACTADDRESSEEFORMATID = CONTACTADDRESSEEFORMATID,
@ALTCONTACTADDRESSEEFORMATID = ALTCONTACTADDRESSEEFORMATID,
@CONTACTADDRESSEEFUNCTIONID = CONTACTADDRESSEEFUNCTIONID,
@ORGSALUTATIONCODE = ORGSALUTATIONCODE,
@CONTACTSALUTATIONFORMATID = CONTACTSALUTATIONFORMATID,
@ALTCONTACTSALUTATIONFORMATID = ALTCONTACTSALUTATIONFORMATID,
@CONTACTSALUTATIONFUNCTIONID = CONTACTSALUTATIONFUNCTIONID,
@CONTACTSALUTATIONOPTIONCODE = CONTACTSALUTATIONOPTIONCODE,
@CUSTOMNAME = CUSTOMNAME
from
dbo.NAMEFORMATPARAMETER
end
insert @NAMEFORMAT
select
CONSTITUENT.ID,
--ADDRESSEE
case when CONSTITUENT.ISORGANIZATION = 1 then
CONSTITUENT.NAME
else
case when @ADDRESSEEFORMATID is null then
dbo.UFN_NAMEFORMAT_FROMID(@ADDRESSEEFUNCTIONID, CONSTITUENT.ID)
else
coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ADDRESSEEFORMATID),
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTADDRESSEEFORMATID),
CONSTITUENT.NAME)
end
end,
--SALUTATION
case when CONSTITUENT.ISORGANIZATION = 1 then
--process salutation option
case when @ORGSALUTATIONCODE = 0 then --use organization name
CONSTITUENT.NAME
when @ORGSALUTATIONCODE = 1 then -- use contact name
coalesce((select top (1)
case when @CONTACTSALUTATIONFORMATID is null then
dbo.UFN_NAMEFORMAT_FROMID(@CONTACTSALUTATIONFUNCTIONID, CONTACT.ID)
else
coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @CONTACTSALUTATIONFORMATID),
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTCONTACTSALUTATIONFORMATID),
CONTACT.NAME)
end
from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID),
@CUSTOMNAME, CONSTITUENT.NAME)
else --use custom name
@CUSTOMNAME
end
else
case when @SALUTATIONFORMATID is null then
dbo.UFN_NAMEFORMAT_FROMID(@SALUTATIONFUNCTIONID, CONSTITUENT.ID)
else
coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @SALUTATIONFORMATID),
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONSTITUENT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTSALUTATIONFORMATID),
CONSTITUENT.NAME)
end
end,
--CONTACT
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top (1)
case when @CONTACTADDRESSEEFORMATID is null then
dbo.UFN_NAMEFORMAT_FROMID(@CONTACTADDRESSEEFUNCTIONID, CONTACT.ID)
else
coalesce((select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @CONTACTADDRESSEEFORMATID),
(select FORMATTEDNAME from dbo.NAMEFORMAT where NAMEFORMAT.CONSTITUENTID = CONTACT.ID and NAMEFORMAT.NAMEFORMATTYPECODEID = @ALTCONTACTADDRESSEEFORMATID),
CONTACT.NAME)
end
from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end,
--POSITION
case when CONSTITUENT.ISORGANIZATION = 1 then
(select top (1) RELATIONSHIP.POSITION
from dbo.RELATIONSHIP
inner join dbo.CONSTITUENT CONTACT on RELATIONSHIP.RECIPROCALCONSTITUENTID = CONTACT.ID
where RELATIONSHIP.ISPRIMARYCONTACT = 1
and RELATIONSHIP.RELATIONSHIPCONSTITUENTID = CONSTITUENT.ID)
else
null
end as POSITION
from dbo.CONSTITUENT;
return
end