V_QUERY_AUCTIONITEMSITE
Auction Item Site Query
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | yes | System record ID |
| SITENAME | nvarchar(250) | yes | Name |
| AUCTIONITEMID | uniqueidentifier | Auction item ID | |
| SITEDESCRIPTION | nvarchar(max) | yes | Description |
| SITESHORTNAME | nvarchar(100) | yes | Short name |
| SITESITEID | nvarchar(100) | yes | Site ID |
| SITEACRONYM | nvarchar(100) | yes | Acronym |
| SITETYPECODE | nvarchar(100) | yes | Site type |
| SITEPATH | nvarchar(1000) | yes | Site path |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 8/17/2011 2:27:20 PM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_AUCTIONITEMSITE AS
select
SITE.ID,
SITE.NAME as SITENAME,
AUCTIONITEM.ID as AUCTIONITEMID,
SITE.DESCRIPTION as SITEDESCRIPTION,
SITE.SHORTNAME as SITESHORTNAME,
SITE.SITEID as SITESITEID,
SITE.ACRONYM as SITEACRONYM,
SITETYPECODE.DESCRIPTION as SITETYPECODE,
SITE.SITEPATH
from
dbo.AUCTIONITEM
left join dbo.REVENUESPLIT on AUCTIONITEM.REVENUEAUCTIONDONATIONID = REVENUESPLIT.REVENUEID
left join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID = DESIGNATION.DESIGNATIONLEVEL1ID
left join dbo.DESIGNATIONLEVEL DL2 on DL2.ID = DESIGNATION.DESIGNATIONLEVEL2ID
left join dbo.DESIGNATIONLEVEL DL3 on DL3.ID = DESIGNATION.DESIGNATIONLEVEL3ID
left join dbo.DESIGNATIONLEVEL DL4 on DL4.ID = DESIGNATION.DESIGNATIONLEVEL4ID
left join dbo.DESIGNATIONLEVEL DL5 on DL5.ID = DESIGNATION.DESIGNATIONLEVEL5ID
left join
(
select
SITE.ID,
SITE.DESCRIPTION,
SITE.SHORTNAME,
SITE.SITEID,
SITE.ACRONYM,
SITE.NAME,
SITE.SITETYPECODEID,
UFN_SITE_GETPATH_BULK.SITEPATH
from dbo.SITE
cross apply dbo.UFN_SITE_GETPATH_BULK(SITE.ID)
where UFN_SITE_GETPATH_BULK.ID = SITE.ID
) as SITE on SITE.ID = coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID)
left join dbo.SITETYPECODE on SITE.SITETYPECODEID = SITETYPECODE.ID
where
AUCTIONITEM.TYPECODE = 0
union all
select
SITE.ID,
SITE.NAME as SITENAME,
AUCTIONITEM.ID as AUCTIONITEMID,
SITE.DESCRIPTION as SITEDESCRIPTION,
SITE.SHORTNAME as SITESHORTNAME,
SITE.SITEID as SITESITEID,
SITE.ACRONYM as SITEACRONYM,
SITETYPECODE.DESCRIPTION as SITETYPECODE,
dbo.UFN_SITE_GETPATH(SITE.ID) as SITEPATH
from
dbo.AUCTIONITEM
left join dbo.EVENTSITE on AUCTIONITEM.EVENTAUCTIONID = EVENTSITE.EVENTID
left join
(
select
SITE.ID,
SITE.DESCRIPTION,
SITE.SHORTNAME,
SITE.SITEID,
SITE.ACRONYM,
SITE.NAME,
SITE.SITETYPECODEID,
UFN_SITE_GETPATH_BULK.SITEPATH
from dbo.SITE
cross apply dbo.UFN_SITE_GETPATH_BULK(SITE.ID)
where UFN_SITE_GETPATH_BULK.ID = SITE.ID
) as SITE on EVENTSITE.SITEID = SITE.ID
left join dbo.SITETYPECODE on SITE.SITETYPECODEID = SITETYPECODE.ID
where
AUCTIONITEM.TYPECODE = 1