V_QUERY_AUCTIONITEM
Provides the ability to query information about auction items.
Fields
| Field | Field Type | Null | Description |
|---|---|---|---|
| ID | uniqueidentifier | System record ID | |
| LOOKUPID | nvarchar(100) | yes | Lookup ID |
| NAME | nvarchar(100) | Name | |
| DESCRIPTION | nvarchar(255) | Description | |
| AUCTIONITEMCATEGORYID | uniqueidentifier | yes | Category ID |
| AUCTIONITEMCATEGORY | nvarchar(100) | yes | Category |
| AUCTIONITEMSUBCATEGORYID | uniqueidentifier | yes | Subcategory ID |
| AUCTIONITEMSUBCATEGORY | nvarchar(100) | yes | Subcategory |
| VALUE | money | yes | Value |
| MINIMUMBID | money | Minimum bid | |
| EXPIRATIONDATE | datetime | yes | Expiration date |
| REVENUEAUCTIONDONATIONID | uniqueidentifier | yes | Donation Revenue ID |
| DONORID | uniqueidentifier | yes | Donor |
| DONATIONDATE | datetime | yes | Donation date |
| EVENTAUCTIONID | uniqueidentifier | yes | Auction |
| PACKAGEID | uniqueidentifier | yes | Package ID |
| PACKAGE | nvarchar(100) | yes | Package |
| TYPECODE | tinyint | Is package | |
| COPIEDFROM | nvarchar(100) | yes | Copied from item |
| SITES | nvarchar(500) | yes | Sites |
| TOTALITEMS | int | yes | Total items |
| GIVENANONYMOUSLY | bit | yes | Given anonymously |
| PURCHASEPRICE | money | yes | Purchase price |
| GAINLOSS | money | yes | Gain/loss |
| ADDEDBYAPPLICATION | nvarchar(200) | yes | Added by application |
| ADDEDBYUSERNAME | nvarchar(128) | yes | Added by user name |
| CHANGEDBYAPPLICATION | nvarchar(200) | yes | Changed by application |
| CHANGEDBYUSERNAME | nvarchar(128) | yes | Changed by user name |
| TRANSACTIONVALUE | money | yes | Value (transaction currency) |
| TRANSACTIONMINIMUMBID | money | Minimum bid (transaction currency) | |
| TRANSACTIONGAINLOSS | money | yes | Gain/loss (transaction currency) |
| ORGANIZATIONVALUE | money | yes | Value (organization currency) |
| ORGANIZATIONMINIMUMBID | money | Minimum bid (organization currency) | |
| ORGANIZATIONGAINLOSS | money | yes | Gain/loss (organization currency) |
| BASECURRENCYID | uniqueidentifier | yes | Base currency ID |
| BASEEXCHANGERATEID | uniqueidentifier | yes | Base exchange rate |
| BASETOORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Base to organization exchange rate |
| ORIGINTOORGANIZATIONEXCHANGERATEID | uniqueidentifier | yes | Organization exchange rate |
| TRANSACTIONCURRENCYID | uniqueidentifier | yes | Transaction currency ID |
| ORGANIZATIONPURCHASEPRICE | money | yes | Purchase price (organization currency) |
| TRANSACTIONPURCHASEPRICE | money | yes | Purchase price (transaction currency) |
Definition
Copy
/*
Generated by Blackbaud AppFx Platform
Date: 3/19/2013 1:44:07 AM
Assembly Version: Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_AUCTIONITEM AS
select
AUCTIONITEM.ID,
AUCTIONITEM.LOOKUPID,
AUCTIONITEM.NAME,
AUCTIONITEM.DESCRIPTION,
AUCTIONITEM.AUCTIONITEMCATEGORYID,
AUCTIONITEMCATEGORY.NAME as AUCTIONITEMCATEGORY,
AUCTIONITEM.AUCTIONITEMSUBCATEGORYID,
AUCTIONITEMSUBCATEGORY.NAME as AUCTIONITEMSUBCATEGORY,
case
when AUCTIONITEM.TYPECODE = 0 then
AUCTIONITEM.VALUE
else
[ITEMVALUES].VALUE
end as VALUE,
AUCTIONITEM.MINIMUMBID as MINIMUMBID,
AUCTIONITEM.EXPIRATIONDATE,
AUCTIONITEM.REVENUEAUCTIONDONATIONID,
DONATIONREVENUE.CONSTITUENTID as DONORID,
DONATIONREVENUE.DATE as DONATIONDATE,
AUCTIONITEM.EVENTAUCTIONID,
PACKAGE.ID as PACKAGEID,
PACKAGE.NAME as PACKAGE,
AUCTIONITEM.TYPECODE,
COPIEDFROM.NAME as COPIEDFROM,
case
when AUCTIONITEM.TYPECODE = 0 then
SITE.NAME
else
dbo.UFN_AUCTIONPACKAGE_GETSITELIST(AUCTIONITEM.ID)
end as SITES,
case
when AUCTIONITEM.TYPECODE = 0 then
null
else
(select COUNT(ID) from dbo.AUCTIONITEM [ITEMCOUNT] where [ITEMCOUNT].PACKAGEID = AUCTIONITEM.ID)
end as TOTALITEMS,
[DONATIONREVENUE].GIVENANONYMOUSLY,
[PURCHASEPRICES].PURCHASEPRICE as PURCHASEPRICE,
([PURCHASEPRICES].PURCHASEPRICE - AUCTIONITEM.VALUE) as GAINLOSS,
ADDEDBY.APPLICATIONNAME as ADDEDBYAPPLICATION,
ADDEDBY.USERNAME as ADDEDBYUSERNAME,
CHANGEDBY.APPLICATIONNAME as CHANGEDBYAPPLICATION,
CHANGEDBY.USERNAME as CHANGEDBYUSERNAME,
case
when AUCTIONITEM.TYPECODE = 0 then
AUCTIONITEM.TRANSACTIONVALUE
else
[ITEMVALUES].TRANSACTIONVALUE
end as TRANSACTIONVALUE,
AUCTIONITEM.TRANSACTIONMINIMUMBID,
([PURCHASEPRICES].TRANSACTIONPURCHASEPRICE - AUCTIONITEM.TRANSACTIONVALUE) as TRANSACTIONGAINLOSS,
case
when AUCTIONITEM.TYPECODE = 0 then
AUCTIONITEM.ORGANIZATIONVALUE
else
[ITEMVALUES].ORGANIZATIONVALUE
end as ORGANIZATIONVALUE,
AUCTIONITEM.ORGANIZATIONMINIMUMBID,
([PURCHASEPRICES].ORGANIZATIONPURCHASEPRICE - AUCTIONITEM.ORGANIZATIONVALUE) as ORGANIZATIONGAINLOSS,
case
when AUCTIONITEM.TYPECODE = 0 then
AUCTIONITEM.BASECURRENCYID
else
(select top 1 [PACKAGEITEMS].BASECURRENCYID
from
dbo.AUCTIONITEM [PACKAGEITEMS]
where
[PACKAGEITEMS].PACKAGEID = AUCTIONITEM.ID)
end as BASECURRENCYID,
AUCTIONITEM.BASEEXCHANGERATEID,
AUCTIONITEM.BASETOORGANIZATIONEXCHANGERATEID,
AUCTIONITEM.ORIGINTOORGANIZATIONEXCHANGERATEID,
AUCTIONITEM.TRANSACTIONCURRENCYID,
[PURCHASEPRICES].ORGANIZATIONPURCHASEPRICE as ORGANIZATIONPURCHASEPRICE,
[PURCHASEPRICES].TRANSACTIONPURCHASEPRICE as TRANSACTIONPURCHASEPRICE
/*#EXTENSION*/
from
dbo.AUCTIONITEM
left join dbo.AUCTIONITEM [COPIEDFROM] on AUCTIONITEM.COPIEDFROMID = [COPIEDFROM].ID
left join dbo.AUCTIONITEM [PACKAGE] on AUCTIONITEM.PACKAGEID = [PACKAGE].ID
left join dbo.AUCTIONITEMCATEGORY on AUCTIONITEM.AUCTIONITEMCATEGORYID = AUCTIONITEMCATEGORY.ID
left join dbo.AUCTIONITEMSUBCATEGORY on AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = AUCTIONITEMSUBCATEGORY.ID
left join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
left join dbo.EVENT on AUCTIONITEM.EVENTAUCTIONID = EVENT.ID
left join dbo.REVENUE [DONATIONREVENUE] on AUCTIONITEM.REVENUEAUCTIONDONATIONID = DONATIONREVENUE.ID
left join dbo.REVENUESPLIT [PURCHASEREVENUESPLIT] on AUCTIONITEMPURCHASE.PURCHASEID = PURCHASEREVENUESPLIT.ID
left join dbo.REVENUE [PURCHASEREVENUE] on PURCHASEREVENUESPLIT.REVENUEID = PURCHASEREVENUE.ID
left join dbo.REVENUESPLIT [REVENUESPLITDONATION] on AUCTIONITEM.REVENUEAUCTIONDONATIONID = REVENUESPLITDONATION.REVENUEID
left join dbo.DESIGNATION on REVENUESPLITDONATION.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 dbo.SITE on coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID) = SITE.ID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = AUCTIONITEM.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = AUCTIONITEM.CHANGEDBYID
outer apply dbo.UFN_AUCTIONITEM_GETPURCHASEPRICES_INCURRENCY(AUCTIONITEM.ID) [PURCHASEPRICES]
outer apply dbo.UFN_AUCTIONITEM_GETVALUES_INCURRENCY(AUCTIONITEM.ID) [ITEMVALUES]