V_QUERY_CONSTITUENTNAMEFORMAT
This provides the ability to query for name format information.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| CONSTITUENTID | uniqueidentifier | Constituent ID | |
| NAMEFORMATTYPECODEID_TRANSLATION | nvarchar(100) | yes | Type |
| PROTOTYPE | nvarchar(700) | yes | Prototype |
| FORMATTED | nvarchar(700) | yes | Formatted name |
| ADDEDBY_APPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBY_USERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBY_APPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBY_USERNAME | nvarchar(128) | yes | Changed by user name |
| DATEADDED | datetime | Date added | |
| DATECHANGED | datetime | Date changed | |
| TSLONG | bigint | yes | Timestamp value |
| PRIMARYADDRESSEE | bit | Primary addressee | |
| PRIMARYSALUTATION | bit | Primary salutation | |
| CUSTOMNAME | nvarchar(700) | Custom name | |
| SITENAME | nvarchar(250) | yes | Site |
| NAME | nvarchar(700) | yes | Name |
| CUSTOMNAMEBOOLEAN | int | Is custom name | |
| SITEID | uniqueidentifier | yes | Site ID |
| NAMEFORMATTYPECODEID | uniqueidentifier | ||
| NAMEFORMATFUNCTIONID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:01:47 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTNAMEFORMAT AS
select
NAMEFORMAT.ID,
NAMEFORMAT.CONSTITUENTID,
(select DESCRIPTION from dbo.NAMEFORMATTYPECODE where ID = NAMEFORMAT.NAMEFORMATTYPECODEID) as [NAMEFORMATTYPECODEID_TRANSLATION],
dbo.UFN_NAMEFORMATFUNCTION_BUILDPROTOTYPE(NAMEFORMAT.NAMEFORMATFUNCTIONID) as [PROTOTYPE],
dbo.UFN_NAMEFORMAT_FROMID_INTERNAL(NAMEFORMAT.NAMEFORMATFUNCTIONID, NAMEFORMAT.CONSTITUENTID,0) as [FORMATTED],
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
NAMEFORMAT.DATEADDED,
NAMEFORMAT.DATECHANGED,
NAMEFORMAT.TSLONG,
NAMEFORMAT.PRIMARYADDRESSEE,
NAMEFORMAT.PRIMARYSALUTATION,
NAMEFORMAT.CUSTOMNAME,
coalesce((SELECT NAME FROM dbo.SITE WHERE ID = NAMEFORMAT.SITEID),'') AS SITENAME,
case len(NAMEFORMAT.CUSTOMNAME)
when 0 then
dbo.UFN_NAMEFORMAT_FROMID_INTERNAL(NAMEFORMAT.NAMEFORMATFUNCTIONID, NAMEFORMAT.CONSTITUENTID,0)
else NAMEFORMAT.CUSTOMNAME
end as [NAME],
case LEN(NAMEFORMAT.customname)
when 0 then 0
else 1
end as [CUSTOMNAMEBOOLEAN],
NAMEFORMAT.SITEID,
[NAMEFORMAT].[NAMEFORMATTYPECODEID],
[NAMEFORMAT].[NAMEFORMATFUNCTIONID]
from
dbo.NAMEFORMAT
left outer join
dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = NAMEFORMAT.ADDEDBYID
left outer join
dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = NAMEFORMAT.CHANGEDBYID
left outer join
dbo.CONSTITUENT as [C] on [C].ID = NAMEFORMAT.CONSTITUENTID;