USP_DATALIST_EVENTAUCTIONITEM

                CREATE procedure dbo.USP_DATALIST_EVENTAUCTIONITEM
                (
                    @EVENTAUCTIONID uniqueidentifier,
                    @AUCTIONITEMCATEGORYID uniqueidentifier = null,
                    @AUCTIONITEMSUBCATEGORYID uniqueidentifier = null
                )
                as
                    set nocount on;

                    select distinct
                           AUCTIONITEM.ID,
                           AUCTIONITEM.NAME,
                           AUCTIONITEMCATEGORY.NAME CATEGORY,
                           AUCTIONITEMSUBCATEGORY.NAME as SUBCATEGORY,
                           case
                            when AUCTIONITEM.TYPECODE = 0 then AUCTIONITEM.TRANSACTIONVALUE
                            else coalesce((select sum(TRANSACTIONVALUE) from dbo.AUCTIONITEM [ITEM] where [ITEM].PACKAGEID = AUCTIONITEM.ID),0)
                           end as VALUE,
                           AUCTIONITEM.TRANSACTIONMINIMUMBID,
                           dbo.UFN_CONSTITUENT_BUILDNAME(REVENUE.CONSTITUENTID) as DONOR,
                           coalesce(PURCHASEPRICES.TRANSACTIONPURCHASEPRICE, AUCTIONITEMRESERVATION.PURCHASEAMOUNT) as PURCHASEPRICE,
                           AUCTIONITEM.TYPECODE as ISPACKAGE,
                           AUCTIONITEM.TYPECODE,
                           AUCTIONITEM.PACKAGEID,
                           case 
                              when AUCTIONITEM.TYPECODE = 0 and AUCTIONITEM.PACKAGEID is null then 1 
                              else 0 
                           end as [CANREMOVE],
                                       case
                            when REVENUEPOSTED.ID is null then
                                0
                            else
                                1
                           end as ISPOSTED,
                           case
                               when AUCTIONITEMRESERVATION.ID is not null then 1
                               when PARENTRESERVATION.ID is not null then 1
                               else 0
                           end as ISPENDING,
                           AUCTIONITEM.BASECURRENCYID,
                           AUCTIONITEM.TRANSACTIONCURRENCYID,
                           case
                               when AUCTIONITEMREVENUEPURCHASE.ID is not null then 1
                               else 0
                           end as ISSOLD
                    from 
                        dbo.AUCTIONITEM
                        left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID = AUCTIONITEM.ID
                        left join dbo.AUCTIONITEMCATEGORY on AUCTIONITEMCATEGORY.ID = AUCTIONITEM.AUCTIONITEMCATEGORYID
                        left join dbo.AUCTIONITEMSUBCATEGORY on AUCTIONITEMSUBCATEGORY.ID = AUCTIONITEM.AUCTIONITEMSUBCATEGORYID
                        left join dbo.REVENUE on REVENUE.ID = AUCTIONITEM.REVENUEAUCTIONDONATIONID
                        left join dbo.REVENUEPOSTED on REVENUEPOSTED.ID = AUCTIONITEM.REVENUEAUCTIONDONATIONID
                        left join dbo.AUCTIONITEMRESERVATION on AUCTIONITEMRESERVATION.AUCTIONITEMID = AUCTIONITEM.ID
                        left join dbo.AUCTIONITEM PARENT on PARENT.ID = AUCTIONITEM.PACKAGEID
                        left join dbo.AUCTIONITEMRESERVATION PARENTRESERVATION on PARENTRESERVATION.AUCTIONITEMID = PARENT.ID
                        outer apply dbo.UFN_AUCTIONITEM_GETPURCHASEPRICES_INCURRENCY(AUCTIONITEM.ID) [PURCHASEPRICES]
                    where 
                        AUCTIONITEM.EVENTAUCTIONID = @EVENTAUCTIONID
                        and (AUCTIONITEM.AUCTIONITEMCATEGORYID = @AUCTIONITEMCATEGORYID or @AUCTIONITEMCATEGORYID is null)
                        and (AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = @AUCTIONITEMSUBCATEGORYID or @AUCTIONITEMSUBCATEGORYID is null)
                    order by AUCTIONITEM.NAME