V_QUERY_GIFTINKINDSALE

Provides the ability to query gift-in-kind sales.

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier		System record ID
REVENUEID	uniqueidentifier		Revenue ID
SALEDATE	datetime	yes	Date of sale
SALEAMOUNT	money		Sale amount
NUMBEROFUNITS	int		Units sold
SALEPOSTDATE	datetime	yes	GL post date
SALEPOSTSTATUS	nvarchar(11)	yes	GL post status
GAINLOSS	money	yes	Gain/loss
TRANSACTIONGAINLOSS	money	yes	Gain/loss (transaction currency)
ORGANIZATIONGAINLOSS	money	yes	Gain/loss (organization currency)
DATEADDED	datetime		Date added
DATECHANGED	datetime		Date changed
TSLONG	bigint	yes	Timestamp value
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
ITEMNAME	nvarchar(100)		Item name
UNSOLDAMOUNT	money	yes	Unsold amount
TRANSACTIONUNSOLDAMOUNT	money	yes	Unsold amount (transaction currency)
ORGANIZATIONUNSOLDAMOUNT	money	yes	Unsold amount (organization currency)
UNITSREMAINING	int	yes	Units remaining
TRANSACTIONSALEAMOUNT	money		Sale amount (transaction currency)
ORGANIZATIONSALEAMOUNT	money		Sale amount (organization currency)
FAIRMARKETVALUE	money		Fair market value
TRANSACTIONFAIRMARKETVALUE	money		Fair market value (transaction currency)
ORGANIZATIONFAIRMARKETVALUE	money		Fair market value (organization currency)
BASECURRENCYID	uniqueidentifier	yes	Base currency ID
BASEEXCHANGERATEID	uniqueidentifier	yes	Base exchange rate
ORGANIZATIONEXCHANGERATEID	uniqueidentifier	yes	Organization exchange rate
TRANSACTIONCURRENCYID	uniqueidentifier	yes	Transaction currency ID
APPRAISEDVALUE	money	yes	Appraised value
TRANSACTIONAPPRAISEDVALUE	money	yes	Appraised value (transaction currency)
ORGANIZATIONAPPRAISEDVALUE	money	yes	Appraised value (organization currency)

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  3/19/2013 1:42:20 AM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=3.0.504.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_GIFTINKINDSALE AS



select
  GIFTINKINDSALE.ID,
  REVENUEPAYMENTMETHOD.REVENUEID,
  GIFTINKINDSALE.SALEDATE,
  GIFTINKINDSALE.SALEAMOUNT,
  GIFTINKINDSALE.NUMBEROFUNITS,
  GIFTINKINDSALE.SALEPOSTDATE,
  GIFTINKINDSALE.SALEPOSTSTATUS,
  GIFTINKINDSALE.SALEAMOUNT - (GIFTINKINDPAYMENTMETHODDETAIL.FAIRMARKETVALUE * GIFTINKINDSALE.NUMBEROFUNITS) as GAINLOSS,
  GIFTINKINDSALE.SALEAMOUNT - (GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONFAIRMARKETVALUE * GIFTINKINDSALE.NUMBEROFUNITS) as TRANSACTIONGAINLOSS,
  GIFTINKINDSALE.SALEAMOUNT - (GIFTINKINDPAYMENTMETHODDETAIL.ORGANIZATIONFAIRMARKETVALUE * GIFTINKINDSALE.NUMBEROFUNITS) as ORGANIZATIONGAINLOSS,
  GIFTINKINDSALE.DATEADDED,
  GIFTINKINDSALE.DATECHANGED,
  GIFTINKINDSALE.TSLONG,
  ADDEDBY.APPLICATIONNAME as [ADDEDBY_APPLICATION],
  ADDEDBY.USERNAME as [ADDEDBY_USERNAME],
  CHANGEDBY.APPLICATIONNAME as [CHANGEDBY_APPLICATION],
  CHANGEDBY.USERNAME as [CHANGEDBY_USERNAME],
  GIFTINKINDPAYMENTMETHODDETAIL.ITEMNAME,

  (GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS - isnull((select sum(GIFTINKINDSALECUMULATIVE.NUMBEROFUNITS) 
                                                         from dbo.GIFTINKINDSALE as GIFTINKINDSALECUMULATIVE
                                                         --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

                                                         where GIFTINKINDSALECUMULATIVE.SALEDATE <= dateadd(ms, -003, dateadd(d, 1, cast(cast(GIFTINKINDSALE.SALEDATE as date) as datetime)))
                                                         and GIFTINKINDSALECUMULATIVE.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID)
                                                        , 0)) * GIFTINKINDPAYMENTMETHODDETAIL.FAIRMARKETVALUE as UNSOLDAMOUNT,

  (GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS - isnull((select sum(GIFTINKINDSALECUMULATIVE.NUMBEROFUNITS) 
                                                         from dbo.GIFTINKINDSALE as GIFTINKINDSALECUMULATIVE
                                                         --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

                                                         where GIFTINKINDSALECUMULATIVE.SALEDATE <= dateadd(ms, -003, dateadd(d, 1, cast(cast(GIFTINKINDSALE.SALEDATE as date) as datetime)))
                                                         and GIFTINKINDSALECUMULATIVE.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID)
                                                        , 0)) * GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONFAIRMARKETVALUE as TRANSACTIONUNSOLDAMOUNT,

  (GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS - isnull((select sum(GIFTINKINDSALECUMULATIVE.NUMBEROFUNITS) 
                                                         from dbo.GIFTINKINDSALE as GIFTINKINDSALECUMULATIVE
                                                         --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

                                                         where GIFTINKINDSALECUMULATIVE.SALEDATE <= dateadd(ms, -003, dateadd(d, 1, cast(cast(GIFTINKINDSALE.SALEDATE as date) as datetime)))
                                                         and GIFTINKINDSALECUMULATIVE.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID)
                                                        , 0)) * GIFTINKINDPAYMENTMETHODDETAIL.ORGANIZATIONFAIRMARKETVALUE as ORGANIZATIONUNSOLDAMOUNT,

  GIFTINKINDPAYMENTMETHODDETAIL.NUMBEROFUNITS - isnull((select sum(GIFTINKINDSALECUMULATIVE.NUMBEROFUNITS)
                                                        from dbo.GIFTINKINDSALE as GIFTINKINDSALECUMULATIVE
                                                        --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

                                                        where GIFTINKINDSALECUMULATIVE.SALEDATE <= dateadd(ms, -003, dateadd(d, 1, cast(cast(GIFTINKINDSALE.SALEDATE as date) as datetime)))
                                                        and GIFTINKINDSALECUMULATIVE.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID)
                                                       , 0) as UNITSREMAINING,

  GIFTINKINDSALE.TRANSACTIONSALEAMOUNT,
  GIFTINKINDSALE.ORGANIZATIONSALEAMOUNT,
  GIFTINKINDPAYMENTMETHODDETAIL.FAIRMARKETVALUE,
  GIFTINKINDPAYMENTMETHODDETAIL.TRANSACTIONFAIRMARKETVALUE,
  GIFTINKINDPAYMENTMETHODDETAIL.ORGANIZATIONFAIRMARKETVALUE,
  GIFTINKINDSALE.BASECURRENCYID,
  GIFTINKINDSALE.BASEEXCHANGERATEID,
  GIFTINKINDSALE.ORGANIZATIONEXCHANGERATEID,
  GIFTINKINDSALE.TRANSACTIONCURRENCYID,
  GIFTINKINDAPPRAISAL.AMOUNT as APPRAISEDVALUE,
  GIFTINKINDAPPRAISAL.TRANSACTIONAMOUNT as TRANSACTIONAPPRAISEDVALUE,
  GIFTINKINDAPPRAISAL.ORGANIZATIONAMOUNT as ORGANIZATIONAPPRAISEDVALUE
  /*#EXTENSION*/
from dbo.GIFTINKINDSALE
inner join dbo.GIFTINKINDPAYMENTMETHODDETAIL on GIFTINKINDSALE.GIFTINKINDPAYMENTMETHODDETAILID = GIFTINKINDPAYMENTMETHODDETAIL.ID
inner join dbo.REVENUEPAYMENTMETHOD on GIFTINKINDPAYMENTMETHODDETAIL.ID = REVENUEPAYMENTMETHOD.ID
left join dbo.GIFTINKINDAPPRAISAL on GIFTINKINDPAYMENTMETHODDETAIL.ID = GIFTINKINDAPPRAISAL.GIFTINKINDPAYMENTMETHODDETAILID
left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = GIFTINKINDSALE.ADDEDBYID
left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = GIFTINKINDSALE.CHANGEDBYID;