V_QUERY_AUCTIONITEMPURCHASES

Returns the .

Fields

Field	Field Type	Null	Description
ID	uniqueidentifier	yes	System record ID
AUCTIONITEMID	uniqueidentifier		Auction item ID
PURCHASEID	uniqueidentifier	yes	Purchase Revenue ID
PURCHASERID	uniqueidentifier	yes	Purchaser
PURCHASEDATE	datetime	yes	Date
PURCHASEAMOUNT	money	yes	Amount
RECEIPTAMOUNT	money	yes	Purchase gain/loss
TRANSACTIONPURCHASEAMOUNT	money	yes	Amount (transaction)
TRANSACTIONRECEIPTAMOUNT	money	yes	Purchase gain/loss (transaction)
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

Definition

Copy

/*
Generated by Blackbaud AppFx Platform
Date:  8/17/2011 2:35:23 PM
Assembly Version:  Blackbaud.AppFx.Platform.SqlClr, Version=2.91.1535.0, Culture=neutral, PublicKeyToken=null
Copyright Blackbaud
*/
CREATE VIEW dbo.V_QUERY_AUCTIONITEMPURCHASES AS



                select
                    AUCTIONITEMPURCHASE.ID,
                    AUCTIONITEM.ID as AUCTIONITEMID,
                    REVENUESPLIT.ID as PURCHASEID,
                    REVENUE.CONSTITUENTID as PURCHASERID,
                    REVENUE.DATE as PURCHASEDATE,

                    REVENUESPLIT.AMOUNT as PURCHASEAMOUNT,

                    (
                        select BASEAMOUNT
                        from dbo.UFN_CURRENCY_GETCURRENCYVALUES(    GAINLOSS.AUCTIONITEMGAINLOSSAMOUNT,
                                                                    getdate(),
                                                                    REVENUESPLIT.BASECURRENCYID,
                                                                    AUCTIONITEM.BASEEXCHANGERATEID,
                                                                    REVENUESPLIT.TRANSACTIONCURRENCYID
                                                               )
                    ) as RECEIPTAMOUNT,

                    REVENUESPLIT.TRANSACTIONAMOUNT as TRANSACTIONPURCHASEAMOUNT,
                    GAINLOSS.AUCTIONITEMGAINLOSSAMOUNT as TRANSACTIONRECEIPTAMOUNT,

                    AUCTIONITEM.BASECURRENCYID,
                    AUCTIONITEM.BASEEXCHANGERATEID,
                    AUCTIONITEM.BASETOORGANIZATIONEXCHANGERATEID,
                    AUCTIONITEM.ORIGINTOORGANIZATIONEXCHANGERATEID,
                    AUCTIONITEM.TRANSACTIONCURRENCYID
                from
                    dbo.AUCTIONITEM
                    left join dbo.AUCTIONITEMPURCHASE on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID
                    left join dbo.REVENUESPLIT on REVENUESPLIT.ID = AUCTIONITEMPURCHASE.PURCHASEID
                    left join dbo.REVENUE on REVENUESPLIT.REVENUEID = REVENUE.ID
                    left join dbo.CHANGEAGENT as ADDEDBY on ADDEDBY.ID = AUCTIONITEM.ADDEDBYID
                    left join dbo.CHANGEAGENT as CHANGEDBY on CHANGEDBY.ID = AUCTIONITEM.CHANGEDBYID
                    cross apply dbo.UFN_AUCTIONITEM_GETGAINLOSS(AUCTIONITEM.ID) GAINLOSS
                where
                    GAINLOSS.REVENUEID = REVENUE.ID
                    and
                    GAINLOSS.REVENUESPLITID = REVENUESPLIT.ID