USP_REPORT_AUCTIONDETAILS

            CREATE procedure dbo.USP_REPORT_AUCTIONDETAILS
            (
                @EVENTID uniqueidentifier = null,
                @STARTDATE datetime = null,
                @ENDDATE datetime = null,
                @INCLUDESUBEVENTS bit = 0,
                @CURRENCYCODE tinyint = 1
            )
            as
            set nocount on;

            begin try
                declare @AUCTIONIDS table (ID uniqueidentifier);
                declare @SELECTEDCURRENCYID uniqueidentifier;
                declare @DECIMALDIGITS tinyint;
                declare @ROUNDINGTYPECODE tinyint;
                declare @EVENTS table
                (
                    ID uniqueidentifier
                )

                declare @ORGANIZATIONCURRENCYID uniqueidentifier = dbo.UFN_CURRENCY_GETORGANIZATIONCURRENCY();

                if @CURRENCYCODE = 0
                    select @SELECTEDCURRENCYID = EVENT.BASECURRENCYID
                    from dbo.EVENT
                    where EVENT.ID = @EVENTID
                else
                    set @SELECTEDCURRENCYID = @ORGANIZATIONCURRENCYID;

                select
                    @DECIMALDIGITS = CURRENCY.DECIMALDIGITS,
                    @ROUNDINGTYPECODE = CURRENCY.ROUNDINGTYPECODE
                from dbo.CURRENCY
                where CURRENCY.ID = @SELECTEDCURRENCYID;

                if @INCLUDESUBEVENTS = 0
                begin
                    insert into @EVENTS
                        select @EVENTID
                end
                else begin
                    insert into @EVENTS
                        select RELATEDEVENT.ID
                        from dbo.EVENTHIERARCHY as RELATEDEVENT
                        inner join dbo.EVENTHIERARCHY as SOURCEEVENT on SOURCEEVENT.ID = @EVENTID
                        where RELATEDEVENT.HIERARCHYPATH.IsDescendantOf(SOURCEEVENT.HIERARCHYPATH) = 1
                end

                insert into @AUCTIONIDS
                    select EVENTAUCTION.ID
                    from dbo.EVENTAUCTION
                    where EVENTAUCTION.ID in (select ID from @EVENTS)


                --Made changes to handle multiple payments towards single items

                declare @RESULTS table
                (
                    EVENTID uniqueidentifier,
                    EVENTNAME nvarchar(100),
                    ITEMVALUE money,
                    PURCHASEPRICE money,
                    GAINLOSS money
                )

                declare @EVENTCURSOR_EVENTID uniqueidentifier;
                declare EVENTCURSOR cursor local fast_forward
                for select ID from @EVENTS
                open EVENTCURSOR
                fetch next from EVENTCURSOR into @EVENTCURSOR_EVENTID

                while @@FETCH_STATUS = 0
                begin

                    with [PURCHASEDITEMS]
                    as
                    (
                        select distinct AUCTIONITEM.ID
                        from dbo.AUCTIONITEM 
                        inner join dbo.AUCTIONITEMPURCHASE on AUCTIONITEMPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID 
                        where AUCTIONITEM.EVENTAUCTIONID = @EVENTID     
                    ),
                    [PURCHASESPLITS]
                    as
                    (
                        select distinct AUCTIONITEMPURCHASE.PURCHASEID 
                        from dbo.AUCTIONITEMPURCHASE 
                        inner join dbo.AUCTIONITEM on AUCTIONITEM.ID = AUCTIONITEMPURCHASE.AUCTIONITEMID 
                        where AUCTIONITEM.EVENTAUCTIONID = @EVENTID     
                    )
                    insert into @RESULTS (EVENTID, EVENTNAME, ITEMVALUE, PURCHASEPRICE)
                    select
                        EVENT.ID,
                        EVENT.NAME,
                        (
                            select sum    ([AUCTIONITEM].VALUEINCURRENCY)
                            from dbo.UFN_AUCTIONITEM_GETAMOUNTSINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE, @EVENTID) [AUCTIONITEM] 
                            inner join [PURCHASEDITEMS] on [PURCHASEDITEMS].ID = AUCTIONITEM.ID 

                        ),
                        (
                            select sum([SPLITVALUE].AMOUNTINCURRENCY)
                            from dbo.UFN_REVENUESPLIT_GETAMOUNTINCURRENCY_BULK(@SELECTEDCURRENCYID, @ORGANIZATIONCURRENCYID, @DECIMALDIGITS, @ROUNDINGTYPECODE) as [SPLITVALUE]
                            inner join [PURCHASESPLITS] on [PURCHASESPLITS].PURCHASEID = [SPLITVALUE].ID 
                        )

                    from dbo.EVENT 
                    where EVENT.ID = @EVENTID 

                    fetch next from EVENTCURSOR into @EVENTCURSOR_EVENTID
                end

                close EVENTCURSOR
                deallocate EVENTCURSOR

                update @RESULTS
                    set GAINLOSS = PURCHASEPRICE - ITEMVALUE

                select 
                    EVENTNAME as NAME,
                    ITEMVALUE,
                    PURCHASEPRICE,
                    GAINLOSS,
                    1 as NUMBEROFAUCTIONS,
                    CURRENCYPROPERTIES.ISO4217 [ISOCURRENCYCODE],
                    CURRENCYPROPERTIES.CURRENCYSYMBOL,
                    CURRENCYPROPERTIES.SYMBOLDISPLAYSETTINGCODE [CURRENCYSYMBOLDISPLAYSETTINGCODE],
                    CURRENCYPROPERTIES.DECIMALDIGITS
                from @RESULTS
                outer apply
                dbo.UFN_CURRENCY_GETPROPERTIES(@SELECTEDCURRENCYID) CURRENCYPROPERTIES

            end try
            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;