V_QUERY_DESIGNATION
This provides the ability to query all designation fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| DESIGNATIONLEVEL1ID | uniqueidentifier | Level 1 System ID | |
| DESIGNATIONLEVEL2ID | uniqueidentifier | yes | Level 2 System ID |
| DESIGNATIONLEVEL3ID | uniqueidentifier | yes | Level 3 System ID |
| DESIGNATIONLEVEL4ID | uniqueidentifier | yes | Level 4 System ID |
| DESIGNATIONLEVEL5ID | uniqueidentifier | yes | Level 5 System ID |
| TERMINALDESIGNATIONLEVELID | uniqueidentifier | yes | Terminal Level System ID |
| DESIGNATIONNAME | nvarchar(512) | yes | Path |
| USERID | nvarchar(512) | Lookup ID | |
| VANITYNAME | nvarchar(512) | Public name | |
| REPORTCODE1NAME | nvarchar(100) | yes | Report code 1 |
| REPORTCODE2NAME | nvarchar(100) | yes | Report code 2 |
| ACCOUNTNUMBER | nvarchar(100) | Account number | |
| PROJECTCODE | nvarchar(100) | Project code | |
| 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 |
| ISACTIVE | bit | Is active | |
| VSECATEGORY | nvarchar(70) | yes | VSE category |
| STARTDATE | datetime | yes | Start date |
| ENDDATE | datetime | yes | End date |
| USECODENAME | nvarchar(100) | yes | Use code |
| VSESUBCATEGORY | nvarchar(100) | yes | VSE subcategory |
| ISREVENUEDESIGNATION | bit | Revenue designation | |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| DETAILSURL | UDT_WEBADDRESS | yes | Details page URL |
| DESIGNATIONREPORT1CODEID | uniqueidentifier | yes | |
| DESIGNATIONREPORT2CODEID | uniqueidentifier | yes | |
| VSECATEGORYID | uniqueidentifier | yes | |
| DESIGNATIONUSECODEID | uniqueidentifier | yes | |
| VSESUBCATEGORYID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/30/2015 1:00:15 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.153.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_DESIGNATION AS
select DESIGNATION.ID
,DESIGNATION.DESIGNATIONLEVEL1ID
,DESIGNATION.DESIGNATIONLEVEL2ID
,DESIGNATION.DESIGNATIONLEVEL3ID
,DESIGNATION.DESIGNATIONLEVEL4ID
,DESIGNATION.DESIGNATIONLEVEL5ID
,coalesce(DESIGNATION.DESIGNATIONLEVEL5ID, DESIGNATION.DESIGNATIONLEVEL4ID, DESIGNATION.DESIGNATIONLEVEL3ID, DESIGNATION.DESIGNATIONLEVEL2ID, DESIGNATION.DESIGNATIONLEVEL1ID) as TERMINALDESIGNATIONLEVELID
,DESIGNATIONS.[NAME] as [DESIGNATIONNAME]
,DESIGNATION.USERID
,DESIGNATION.VANITYNAME
,DESIGNATIONREPORT1CODE.DESCRIPTION as [REPORTCODE1NAME]
,DESIGNATIONREPORT2CODE.DESCRIPTION as [REPORTCODE2NAME]
,DESIGNATION.ACCOUNTNUMBER
,DESIGNATION.PROJECTCODE
,[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION]
,[ADDEDBY].USERNAME as [ADDEDBY_USERNAME]
,[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION]
,[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME]
,DESIGNATION.DATEADDED
,DESIGNATION.DATECHANGED
,DESIGNATION.TSLONG
,DESIGNATION.ISACTIVE
,VSECATEGORY.VSECATEGORY
,DESIGNATION.STARTDATE
,DESIGNATION.ENDDATE
,DESIGNATIONUSECODE.DESCRIPTION as [USECODENAME]
,VSESUBCATEGORY.[NAME] [VSESUBCATEGORY]
,DESIGNATION.ISREVENUEDESIGNATION
,DESIGNATION.BASECURRENCYID
,DESIGNATIONDETAILSLINK.DETAILSURL,
[DESIGNATIONREPORT1CODE].[ID] as DESIGNATIONREPORT1CODEID,
[DESIGNATIONREPORT2CODE].[ID] as DESIGNATIONREPORT2CODEID,
[VSECATEGORY].[ID] as [VSECATEGORYID],
[DESIGNATIONUSECODE].[ID] as [DESIGNATIONUSECODEID],
[VSESUBCATEGORY].[ID] as [VSESUBCATEGORYID]
/*#EXTENSION*/
from dbo.DESIGNATION
inner join dbo.UFN_DESIGNATION_BUILDNAME_BULK() DESIGNATIONS on DESIGNATION.ID = DESIGNATIONS.ID
left join dbo.DESIGNATIONREPORT1CODE on DESIGNATION.DESIGNATIONREPORT1CODEID = DESIGNATIONREPORT1CODE.ID
left join dbo.DESIGNATIONREPORT2CODE on DESIGNATION.DESIGNATIONREPORT2CODEID = DESIGNATIONREPORT2CODE.ID
left join dbo.GLDEPARTMENT on DESIGNATION.GLDEPARTMENTID = GLDEPARTMENT.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = DESIGNATION.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = DESIGNATION.CHANGEDBYID
left join dbo.VSECATEGORY on VSECATEGORY.ID = DESIGNATION.VSECATEGORYID
left join dbo.DESIGNATIONUSECODE on DESIGNATION.DESIGNATIONUSECODEID = DESIGNATIONUSECODE.ID
left join dbo.VSESUBCATEGORY on VSESUBCATEGORY.ID = DESIGNATION.VSESUBCATEGORYID
left join dbo.DESIGNATIONDETAILSLINK on DESIGNATIONDETAILSLINK.DESIGNATIONID = DESIGNATION.ID
where DESIGNATION.SYSTEMGENERATED = 0