V_QUERY_SPONSORSHIPLOCATION
This provides the ability to query all sponsorship location fields.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| DESIGNATIONID | uniqueidentifier | yes | DESIGNATIONID |
| NAME | nvarchar(100) | Name | |
| STATUS | nvarchar(8) | yes | Status |
| COMMENT | nvarchar(255) | Comments | |
| DESCRIPTION | nvarchar(100) | Type | |
| REASON | nvarchar(100) | yes | Reason |
| 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 |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| FULLNAME | nvarchar(max) | yes | Full name |
| FIELDOFFICEID | uniqueidentifier | yes | Field office |
| DISPLAYONLINE | bit | Display online | |
| FIELDOFFICE | nvarchar(154) | yes | |
| SPONSORSHIPLOCATIONTYPECODEID | uniqueidentifier | ||
| SPONSORSHIPREASONID | uniqueidentifier | yes |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 4/13/2016 4:58:49 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=4.0.158.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_SPONSORSHIPLOCATION AS
with LOCATIONNAME(ID,HIERARCHYPATH,HIERARCHYLEVEL,LEVEL,NODESTRING) as
(
select
SPONSORSHIPLOCATION.ID,
SPONSORSHIPLOCATION.HIERARCHYPATH,
SPONSORSHIPLOCATION.HIERARCHYPATH.GetLevel() HIERARCHYLEVEL,
1 LEVEL,
cast('' as nvarchar(max)) NODESTRING
from dbo.SPONSORSHIPLOCATION
inner join dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATIONTYPECODE.ID = SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID
union all
select
LOCATIONNAME.ID,
SPONSORSHIPLOCATION.HIERARCHYPATH,
SPONSORSHIPLOCATION.HIERARCHYPATH.GetLevel() HIERARCHYLEVEL,
LOCATIONNAME.LEVEL + 1 LEVEL,
cast(' \ ' + LOCATIONNAME.NODESTRING as nvarchar(max)) NODESTRING
from dbo.SPONSORSHIPLOCATION
inner join dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATIONTYPECODE.ID = SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID
inner join LOCATIONNAME on LOCATIONNAME.HIERARCHYPATH.GetAncestor(1) = SPONSORSHIPLOCATION.HIERARCHYPATH
)
select
SPONSORSHIPLOCATION.ID,
SPONSORSHIPLOCATION.DESIGNATIONID,
SPONSORSHIPLOCATION.NAME,
SPONSORSHIPLOCATION.STATUS,
SPONSORSHIPLOCATION.COMMENT,
SPONSORSHIPLOCATIONTYPECODE.DESCRIPTION,
SPONSORSHIPREASON.REASON,
[ADDEDBY].APPLICATIONNAME as [ADDEDBY_APPLICATION],
[ADDEDBY].USERNAME as [ADDEDBY_USERNAME],
[CHANGEDBY].APPLICATIONNAME as [CHANGEDBY_APPLICATION],
[CHANGEDBY].USERNAME as [CHANGEDBY_USERNAME],
SPONSORSHIPLOCATION.DATEADDED,
SPONSORSHIPLOCATION.DATECHANGED,
SPONSORSHIPLOCATION.TSLONG,
SPONSORSHIPLOCATION.LOOKUPID,
(
select top 1
case
when HIERARCHYLEVEL > 1 then '... ' + NODESTRING
else NODESTRING
end
from LOCATIONNAME
where ID = SPONSORSHIPLOCATION.ID
order by LEVEL desc
) FULLNAME,
SPONSORSHIPLOCATION.FIELDOFFICEID,
SPONSORSHIPLOCATION.DISPLAYONLINE,
CONSTITUENT.NAME FIELDOFFICE,
SPONSORSHIPLOCATIONTYPECODE.ID as SPONSORSHIPLOCATIONTYPECODEID,
SPONSORSHIPREASON.ID as SPONSORSHIPREASONID
from dbo.SPONSORSHIPLOCATION
inner join dbo.SPONSORSHIPLOCATIONTYPECODE on SPONSORSHIPLOCATION.SPONSORSHIPLOCATIONTYPECODEID = SPONSORSHIPLOCATIONTYPECODE.ID
left join dbo.SPONSORSHIPREASON on SPONSORSHIPLOCATION.SPONSORSHIPREASONID = SPONSORSHIPREASON.ID
left join dbo.CHANGEAGENT as [ADDEDBY] on [ADDEDBY].ID = SPONSORSHIPLOCATION.ADDEDBYID
left join dbo.CHANGEAGENT as [CHANGEDBY] on [CHANGEDBY].ID = SPONSORSHIPLOCATION.CHANGEDBYID
left join dbo.CONSTITUENT on SPONSORSHIPLOCATION.FIELDOFFICEID = CONSTITUENT.ID