USP_REPORT_AUCTIONSUMMARY

            CREATE procedure dbo.USP_REPORT_AUCTIONSUMMARY
            (
                @AUCTIONID uniqueidentifier,
                @SHOWONLYSOLD bit = 0,
                @CURRENCYCODE smallint = null
            )
            as
            begin

                with PURCHASE_CTE(ITEMID, TRANSACTIONAMOUNT, ORGANIZATIONAMOUNT, TRANSACTIONGAINLOSS, ORGANIZATIONGAINLOSS)
                as
                (
                    select
                        AUCTIONITEM.ID,
                        sum(REVENUESPLIT.TRANSACTIONAMOUNT),
                        sum(REVENUESPLIT.ORGANIZATIONAMOUNT),
                        sum(REVENUESPLIT.TRANSACTIONAMOUNT) - max(AUCTIONITEM.TRANSACTIONVALUE),
                        sum(REVENUESPLIT.ORGANIZATIONAMOUNT) - max(AUCTIONITEM.ORGANIZATIONVALUE)
                    from
                        dbo.AUCTIONITEM
                        inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                        inner join dbo.REVENUESPLIT on REVENUESPLIT.ID = AUCTIONITEMPURCHASE.PURCHASEID
                    group by
                        AUCTIONITEM.ID
                )
                select
                    AUCTIONITEM.ID as ITEMID,
                    AUCTIONITEM.NAME as ITEMNAME,
                    case @CURRENCYCODE 
                        when 2 then AUCTIONITEM.TRANSACTIONVALUE 
                        else AUCTIONITEM.ORGANIZATIONVALUE 
                    end as ITEMVALUE,
                    AUCTIONITEMCATEGORY.ID as CATEGORYID,
                    AUCTIONITEMCATEGORY.NAME as CATEGORYNAME,
                    AUCTIONITEMSUBCATEGORY.ID as SUBCATEGORYID,
                    AUCTIONITEMSUBCATEGORY.NAME as SUBCATEGORYNAME,
                    EVENT.NAME as AUCTIONNAME,
                    case @CURRENCYCODE 
                        when 2 then PURCHASE_CTE.TRANSACTIONAMOUNT 
                        else  PURCHASE_CTE.ORGANIZATIONAMOUNT
                    end as PURCHASEPRICE,
                    case @CURRENCYCODE 
                        when 2 then PURCHASE_CTE.TRANSACTIONGAINLOSS
                        else PURCHASE_CTE.ORGANIZATIONGAINLOSS
                    end as PROFITLOSS,
                    AUCTIONITEM.PACKAGEID as PACKAGEID,
                    CURRENCYPROPERTIES.ISO4217 as ISOCURRENCYCODE,
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE,
                    CURRENCYPROPERTIES.DECIMALDIGITS
                from
                    dbo.AUCTIONITEM
                    left join dbo.AUCTIONITEMCATEGORY on AUCTIONITEM.AUCTIONITEMCATEGORYID = AUCTIONITEMCATEGORY.ID
                    left join dbo.AUCTIONITEMSUBCATEGORY on AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = AUCTIONITEMSUBCATEGORY.ID
                    left join dbo.EVENT on AUCTIONITEM.EVENTAUCTIONID = EVENT.ID
                    left join PURCHASE_CTE on PURCHASE_CTE.ITEMID = AUCTIONITEM.ID
                    outer apply dbo.UFN_CURRENCY_GETPROPERTIES(case @CURRENCYCODE when 2 then AUCTIONITEM.TRANSACTIONCURRENCYID else dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY() end) CURRENCYPROPERTIES
                where
                    AUCTIONITEM.EVENTAUCTIONID is not null
                    and
                    AUCTIONITEM.EVENTAUCTIONID = @AUCTIONID
                    and
                    AUCTIONITEM.TYPECODE = 0
                    and
                    (@SHOWONLYSOLD = 0 or PURCHASE_CTE.ITEMID is not null)
            end