USP_SEARCHLIST_AUCTIONITEM_AMPROIMPORT

                CREATE procedure dbo.USP_SEARCHLIST_AUCTIONITEM_AMPROIMPORT
                (
                    @MAXROWS smallint = 500,
                    @CURRENTAPPUSERID uniqueidentifier,
                    @SECURITYFEATUREID uniqueidentifier = null,
                    @SECURITYFEATURETYPE tinyint = null,
                    @NAME nvarchar(100) = null,
                    @AUCTIONITEMCATEGORYID uniqueidentifier = null,
                    @AUCTIONITEMSUBCATEGORYID uniqueidentifier = null,
                    @SITEID uniqueidentifier = null,
                    @AUCTIONID uniqueidentifier = null,
                    @ONLYSHOWUNSOLD bit = 0,
                    @ONLYSHOWAVAILABLEFORAUCTION uniqueidentifier = null,
                    @AMPROIMPORTBATCHID uniqueidentifier = null
                )
                as
                    set @NAME = COALESCE(@NAME,'') + '%' ;

                    declare @SITESFORAUCTION table
                    (
                        SITEID uniqueidentifier
                    )
                    declare @SITESFORAUCTIONCOUNT int = 0;

                    if @ONLYSHOWAVAILABLEFORAUCTION is not null
                    begin
                        insert into @SITESFORAUCTION(SITEID) (select SITEID from dbo.EVENTSITE where EVENTSITE.EVENTID = @ONLYSHOWAVAILABLEFORAUCTION);
                        select @SITESFORAUCTIONCOUNT = count(SITEID) from @SITESFORAUCTION;
                    end;

                    declare @CATEGORYNAME nvarchar(100) = '';
                    declare @SUBCATEGORYNAME nvarchar(100) = '';

                    if not @AUCTIONITEMCATEGORYID is null
                        select top 1 @CATEGORYNAME = NAME from dbo.AUCTIONITEMCATEGORY where ID = @AUCTIONITEMCATEGORYID;
                    if not @AUCTIONITEMSUBCATEGORYID is null
                        select top 1 @SUBCATEGORYNAME = NAME from dbo.AUCTIONITEMSUBCATEGORY where ID = @AUCTIONITEMSUBCATEGORYID;


                    with AUCTIONITEM_SITE_CTE(AUCTIONITEMID, SITENAME) as
                    (
                        select
                            AUCTIONITEM.ID,
                            SITE.NAME
                        from
                            dbo.AUCTIONITEM
                            left join dbo.REVENUESPLIT on AUCTIONITEM.REVENUEAUCTIONDONATIONID = REVENUESPLIT.REVENUEID
                            left join dbo.DESIGNATION on REVENUESPLIT.DESIGNATIONID = DESIGNATION.ID
                            left join dbo.DESIGNATIONLEVEL DL1 on DL1.ID = DESIGNATION.DESIGNATIONLEVEL1ID
                            left join dbo.DESIGNATIONLEVEL DL2 on DL2.ID = DESIGNATION.DESIGNATIONLEVEL2ID
                            left join dbo.DESIGNATIONLEVEL DL3 on DL3.ID = DESIGNATION.DESIGNATIONLEVEL3ID
                            left join dbo.DESIGNATIONLEVEL DL4 on DL4.ID = DESIGNATION.DESIGNATIONLEVEL4ID
                            left join dbo.DESIGNATIONLEVEL DL5 on DL5.ID = DESIGNATION.DESIGNATIONLEVEL5ID
                            left join dbo.SITE on coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID) = SITE.ID
                        where
                            AUCTIONITEM.TYPECODE = 0
                            and
                            (@SITEID is null or @SITEID = coalesce(DL5.SITEID, DL4.SITEID, DL3.SITEID, DL2.SITEID, DL1.SITEID))
                            and
                            (@ONLYSHOWAVAILABLEFORAUCTION is null or (@ONLYSHOWAVAILABLEFORAUCTION is not null and SITE.ID is null and @SITESFORAUCTIONCOUNT = 0) or (exists(select 1 from @SITESFORAUCTION where SITEID = SITE.ID)))
                            and
                            (
                                dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                                or
                                exists(
                                        select 1 
                                        from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE) 
                                        where 
                                            SITEID=SITE.ID
                                            or
                                            (
                                                SITEID is null and SITE.ID is null
                                            )
                                      )
                            )

                        union all

                        select distinct
                            AUCTIONITEM.ID,
                            dbo.UFN_AUCTIONPACKAGE_GETSITELIST(AUCTIONITEM.ID) as SITELIST
                        from
                            dbo.AUCTIONITEM
                            left join dbo.EVENTSITE on AUCTIONITEM.EVENTAUCTIONID = EVENTSITE.EVENTID
                            left join dbo.SITE on EVENTSITE.SITEID = SITE.ID
                        where
                            AUCTIONITEM.TYPECODE = 1
                            and
                            (@SITEID is null or @SITEID = EVENTSITE.SITEID)
                            and
                            (@ONLYSHOWAVAILABLEFORAUCTION is null or (@ONLYSHOWAVAILABLEFORAUCTION is not null and SITE.ID is null and @SITESFORAUCTIONCOUNT = 0) or (exists(select 1 from @SITESFORAUCTION where SITEID = SITE.ID)))
                            and
                            (
                                dbo.UFN_APPUSER_ISSYSADMIN(@CURRENTAPPUSERID) = 1
                                or
                                exists(
                                        select 1
                                        from dbo.UFN_SITESFORUSERONFEATURE(@CURRENTAPPUSERID, @SECURITYFEATUREID, @SECURITYFEATURETYPE)
                                        where
                                            SITEID=EVENTSITE.SITEID
                                            or
                                            (
                                                SITEID is null and EVENTSITE.SITEID is null
                                            )
                                      )
                            )
                    )
                    select distinct top(@MAXROWS)
                        AUCTIONITEM.ID,
                        AUCTIONITEM.NAME as NAME,
                        AUCTIONITEM_SITE_CTE.SITENAME as SITE,
                        EVENT.NAME as AUCTIONNAME,
                        AUCTIONITEMCATEGORY.NAME as CATEGORY,
                        AUCTIONITEMSUBCATEGORY.NAME as SUBCATEGORY,
                        case
                            when AUCTIONITEM.TYPECODE = 0 then AUCTIONITEM.VALUE
                            else coalesce((select sum(VALUE) from dbo.AUCTIONITEM [ITEM] where [ITEM].PACKAGEID = AUCTIONITEM.ID),0)
                        end as VALUE,
                        AUCTIONITEM.TRANSACTIONCURRENCYID
                    from 
                        dbo.AUCTIONITEM
                        inner join AUCTIONITEM_SITE_CTE on AUCTIONITEM.ID = AUCTIONITEM_SITE_CTE.AUCTIONITEMID
                        left join dbo.AUCTIONITEMREVENUEPURCHASE on AUCTIONITEM.ID = AUCTIONITEMREVENUEPURCHASE.AUCTIONITEMID
                        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 dbo.AUCTIONITEMRESERVATION on AUCTIONITEM.ID = AUCTIONITEMRESERVATION.AUCTIONITEMID
                        left join dbo.AUCTIONITEMRESERVATION [RESERVEDPACKAGEITEM] on AUCTIONITEM.PACKAGEID = [RESERVEDPACKAGEITEM].AUCTIONITEMID
                    where
                        (@AUCTIONID is null or AUCTIONITEM.EVENTAUCTIONID = @AUCTIONID)
                        and
                        (AUCTIONITEM.NAME like @NAME + '%')
                        and 
                        (@AUCTIONITEMCATEGORYID is null or AUCTIONITEM.AUCTIONITEMCATEGORYID = @AUCTIONITEMCATEGORYID)
                        and
                        (@AUCTIONITEMSUBCATEGORYID is null or AUCTIONITEM.AUCTIONITEMSUBCATEGORYID = @AUCTIONITEMSUBCATEGORYID)
                        and
                        (@ONLYSHOWAVAILABLEFORAUCTION is null or AUCTIONITEM.EVENTAUCTIONID is null)
                        and
                        (@ONLYSHOWUNSOLD = 0 or (AUCTIONITEMREVENUEPURCHASE.REVENUEPURCHASEID is null and [RESERVEDPACKAGEITEM].ID is null and AUCTIONITEMRESERVATION.ID is null)) 

                    union all 
                    select top(@MAXROWS)
                        BATCHAMPROIMPORTITEM.ID,
                        BATCHAMPROIMPORTITEM.AUCTIONITEM_UPDATEDNAME as NAME,
                        '' as SITE,
                        EVENT.NAME as AUCTIONNAME,
                        BATCHAMPROIMPORTITEM.AUCTIONITEM_UPDATEDCATEGORY as CATEGORY,
                        BATCHAMPROIMPORTITEM.AUCTIONITEM_UPDATEDSUBCATEGORY as SUBCATEGORY,
                        BATCHAMPROIMPORTITEM.AUCTIONITEM_UPDATEDVALUE as VALUE,
                        EVENT.BASECURRENCYID as TRANSACTIONCURRENCYID

                    from dbo.BATCHAMPROIMPORTITEM
                    inner join dbo.BATCHAMPROIMPORT on BATCHAMPROIMPORT.ID = BATCHAMPROIMPORTITEM.ID
                    inner join dbo.BATCHAMPRODATA on BATCHAMPRODATA.ID = BATCHAMPROIMPORT.BATCHID
                    inner join dbo.EVENT on EVENT.ID = BATCHAMPRODATA.EVENTAUCTIONID
                    where
                        BATCHAMPROIMPORT.BATCHID = @AMPROIMPORTBATCHID
                    and
                        (
                            BATCHAMPROIMPORTITEM.AUCTIONITEM_UPDATEDNAME like @NAME + '%'
                            and (len(@CATEGORYNAME) = 0 or BATCHAMPROIMPORTITEM.AUCTIONITEM_UPDATEDCATEGORY like @CATEGORYNAME + '%')
                            and (len(@SUBCATEGORYNAME) = 0 or BATCHAMPROIMPORTITEM.AUCTIONITEM_UPDATEDSUBCATEGORY like @SUBCATEGORYNAME + '%')
                            and (@ONLYSHOWAVAILABLEFORAUCTION is null or @ONLYSHOWAVAILABLEFORAUCTION = EVENT.ID)
                        )

                    order by 
                        NAME asc