V_QUERY_CONSTITUENTPHONE

This provides the ability to query for phone information.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
CONSTITUENTID	uniqueidentifier		Constituent ID
PHONETYPECODEID_TRANSLATION	nvarchar(100)	yes	Phone type
NUMBER	nvarchar(100)		Number
ISPRIMARY	bit		Primary phone number
DONOTCALL	bit		Do not call
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
STARTTIME	UDT_HOURMINUTE		Call after
ENDTIME	UDT_HOURMINUTE		Call before
INFOSOURCECODEID_TRANSLATION	nvarchar(100)	yes	Information source
INFOSOURCECOMMENTS	nvarchar(256)		Information source comments
COUNTRY	nvarchar(100)	yes	Country
STARTDATE	date	yes	Start date
ENDDATE	date	yes	End date
DONOTCALLREASONCODEID_TRANSLATION	nvarchar(100)	yes	Do not call reason
ISCONFIDENTIAL	bit		Confidential
INTERNATIONALNUMBER	nvarchar(110)	yes	International number
RELATIONSHIPID	uniqueidentifier	yes	Relationship ID
ORIGIN	nvarchar(9)	yes	Origin
ORIGININFO	nvarchar(100)	yes	Origin information
SEASONALSTARTDATE	UDT_MONTHDAY		Seasonal start date
SEASONALENDDATE	UDT_MONTHDAY		Seasonal end date
PHONETYPECODEID	uniqueidentifier	yes
INFOSOURCECODEID	uniqueidentifier	yes
DONOTCALLREASONCODEID	uniqueidentifier	yes
COUNTRYID	uniqueidentifier	yes
DONOTTEXT	bit

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  5/3/2024 2:17:15 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3700.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_CONSTITUENTPHONE AS



        select
          PHONE.ID,
          PHONE.CONSTITUENTID,
          PHONETYPECODE.DESCRIPTION as [PHONETYPECODEID_TRANSLATION],
          PHONE.NUMBER,
          PHONE.ISPRIMARY,
          PHONE.DONOTCALL,
          [ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
          [ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
          [CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
          [CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
          PHONE.DATEADDED,
          PHONE.DATECHANGED,
          PHONE.TSLONG,
          PHONE.STARTTIME,
          PHONE.ENDTIME,
          INFOSOURCECODE.DESCRIPTION as [INFOSOURCECODEID_TRANSLATION],
          PHONE.INFOSOURCECOMMENTS,
          dbo.UFN_COUNTRY_GETDESCRIPTION(PHONE.COUNTRYID) as COUNTRY,
          PHONE.STARTDATE,
          PHONE.ENDDATE,
          DONOTCALLREASONCODE.DESCRIPTION as [DONOTCALLREASONCODEID_TRANSLATION],
          PHONE.ISCONFIDENTIAL,
          dbo.UFN_PHONE_GETINTERNATIONALNUMBER(PHONE.COUNTRYID, PHONE.NUMBER) as INTERNATIONALNUMBER,
          PHONE.RELATIONSHIPID,
          PHONE.ORIGIN,
          case 
            when [ORIGINCODE] <> 0 then 
              dbo.UFN_PHONE_ORIGINCODE_GETDESCRIPTION([ORIGINCODE]) 
            else INFOSOURCECODE.DESCRIPTION 
          end as [ORIGININFO],
          PHONE.SEASONALSTARTDATE,
          PHONE.SEASONALENDDATE,
          [PHONE].[PHONETYPECODEID],
          [PHONE].[INFOSOURCECODEID],
          [PHONE].[DONOTCALLREASONCODEID],
          [PHONE].[COUNTRYID],
          PHONE.DONOTTEXT
          /*#EXTENSION*/

        from dbo.PHONE
        left join dbo.PHONETYPECODE on PHONETYPECODE.ID = PHONE.PHONETYPECODEID
        left join dbo.INFOSOURCECODE on INFOSOURCECODE.ID = PHONE.INFOSOURCECODEID
        left join dbo.DONOTCALLREASONCODE on DONOTCALLREASONCODE.ID = PHONE.DONOTCALLREASONCODEID
        left join dbo.CHANGEAGENT as [ADDEDBY] on ADDEDBY.ID = PHONE.ADDEDBYID
        left join dbo.CHANGEAGENT as [CHANGEDBY] on CHANGEDBY.ID = PHONE.CHANGEDBYID