V_SEARCH_DESIGNATION
Search screen for finding a node of the giving hierarchy.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | ||
| USERID | nvarchar(512) | ||
| DESIGNATION | nvarchar(512) | ||
| VANITYNAME | nvarchar(512) | ||
| ISACTIVE | bit | ||
| SITEID | uniqueidentifier | yes | |
| VSECATEGORY | nvarchar(70) | yes | |
| ALTERNATELOOKUPIDS | nvarchar(max) | yes | |
| VSESUBCATEGORY | nvarchar(100) | yes | |
| ISREVENUEDESIGNATION | bit | ||
| DESIGNATIONREPORTCODE1 | nvarchar(100) | yes | |
| DESIGNATIONREPORTCODE2 | nvarchar(100) | yes | |
| DESIGNATIONUSECODE | nvarchar(100) | yes | |
| DESIGNATIONLEVELTYPE | nvarchar(100) | yes | |
| DESIGNATIONLEVELCATEGORYCODE | nvarchar(100) | yes | |
| SITE | nvarchar(1024) | yes | |
| PURPOSEREPORTCODE1 | nvarchar(100) | yes | |
| PURPOSEREPORTCODE2 | nvarchar(100) | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 9/1/2024 11:54:11 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.3800.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_SEARCH_DESIGNATION AS
select DESIGNATION.ID,
DESIGNATION.USERID,
DESIGNATION.NAME [DESIGNATION],
DESIGNATION.VANITYNAME,
DESIGNATION.ISACTIVE,
case
when DL5.SITEID is not null then DL5.SITEID
else
case
when DL4.SITEID is not null then DL4.SITEID
else
case
when DL3.SITEID is not null then DL3.SITEID
else
case
when DL2.SITEID is not null then DL2.SITEID
else
DL1.SITEID
end
end
end
end as SITEID,
VSECATEGORY.VSECATEGORY,
(
select dbo.UDA_BUILDLIST(ALTLOOKUPID + ' (' + DESIGNATIONALTLOOKUPIDTYPECODE.DESCRIPTION + ')')
from dbo.DESIGNATIONALTLOOKUPID
inner join dbo.DESIGNATIONALTLOOKUPIDTYPECODE on DESIGNATIONALTLOOKUPIDTYPECODE.ID = DESIGNATIONALTLOOKUPID.ALTLOOKUPIDTYPECODEID
where DESIGNATIONALTLOOKUPID.DESIGNATIONID = DESIGNATION.ID
) as [ALTERNATELOOKUPIDS],
VSESUBCATEGORY.NAME [VSESUBCATEGORY],
DESIGNATION.ISREVENUEDESIGNATION,
DESIGNATIONREPORT1CODE.DESCRIPTION [DESIGNATIONREPORTCODE1],
DESIGNATIONREPORT2CODE.DESCRIPTION [DESIGNATIONREPORTCODE2],
dbo.UFN_DESIGNATIONUSECODE_GETDESCRIPTION(DESIGNATION.DESIGNATIONUSECODEID) [DESIGNATIONUSECODE],
dbo.UFN_DESIGNATIONLEVELTYPE_GETDESCRIPTION(coalesce(DL5.DESIGNATIONLEVELTYPEID, DL4.DESIGNATIONLEVELTYPEID, DL3.DESIGNATIONLEVELTYPEID, DL2.DESIGNATIONLEVELTYPEID, DL1.DESIGNATIONLEVELTYPEID)) [DESIGNATIONLEVELTYPE],
dbo.UFN_DESIGNATIONLEVELCATEGORYCODE_GETDESCRIPTION(coalesce(DL5.DESIGNATIONLEVELCATEGORYCODEID, DL4.DESIGNATIONLEVELCATEGORYCODEID, DL3.DESIGNATIONLEVELCATEGORYCODEID, DL2.DESIGNATIONLEVELCATEGORYCODEID, DL1.DESIGNATIONLEVELCATEGORYCODEID)) [DESIGNATIONLEVELCATEGORYCODE],
dbo.UFN_TRANSLATIONFUNCTION_SITE_GETNAME(coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID)) [SITE],
dbo.UFN_DESIGNATIONREPORT1CODE_GETDESCRIPTION(coalesce(DL5.DESIGNATIONREPORT1CODEID, DL4.DESIGNATIONREPORT1CODEID, DL3.DESIGNATIONREPORT1CODEID, DL2.DESIGNATIONREPORT1CODEID, DL1.DESIGNATIONREPORT1CODEID)) [PURPOSEREPORTCODE1],
dbo.UFN_DESIGNATIONREPORT2CODE_GETDESCRIPTION(coalesce(DL5.DESIGNATIONREPORT2CODEID, DL4.DESIGNATIONREPORT2CODEID, DL3.DESIGNATIONREPORT2CODEID, DL2.DESIGNATIONREPORT2CODEID, DL1.DESIGNATIONREPORT2CODEID)) [PURPOSEREPORTCODE2]
from dbo.DESIGNATION
left join dbo.DESIGNATIONREPORT1CODE on DESIGNATIONREPORT1CODE.ID = DESIGNATION.DESIGNATIONREPORT1CODEID
left join dbo.DESIGNATIONREPORT2CODE on DESIGNATIONREPORT2CODE.ID = DESIGNATION.DESIGNATIONREPORT2CODEID
left join dbo.VSECATEGORY on VSECATEGORY.ID = DESIGNATION.VSECATEGORYID
left join dbo.VSESUBCATEGORY on VSESUBCATEGORY.VSECATEGORYID = VSECATEGORY.ID
left join dbo.DESIGNATIONLEVEL DL1 on DESIGNATION.DESIGNATIONLEVEL1ID = DL1.ID
left join dbo.DESIGNATIONLEVEL DL2 on DESIGNATION.DESIGNATIONLEVEL2ID = DL2.ID
left join dbo.DESIGNATIONLEVEL DL3 on DESIGNATION.DESIGNATIONLEVEL3ID = DL3.ID
left join dbo.DESIGNATIONLEVEL DL4 on DESIGNATION.DESIGNATIONLEVEL4ID = DL4.ID
left join dbo.DESIGNATIONLEVEL DL5 on DESIGNATION.DESIGNATIONLEVEL5ID = DL5.ID;