UFN_DESIGNATIONLEVEL_SITEVALIDFORLINKEDAUCTIONITEMS

            CREATE function dbo.UFN_DESIGNATIONLEVEL_SITEVALIDFORLINKEDAUCTIONITEMS
            (
                @DESIGNATIONLEVELID uniqueidentifier,
                @SITEID uniqueidentifier
            )
            returns bit
            as begin
                declare @VALID bit = 1;

                select 
                    @VALID = 0
                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
                where 
                        (
                            DL1.ID = @DESIGNATIONLEVELID or DL2.ID = @DESIGNATIONLEVELID or DL3.ID = @DESIGNATIONLEVELID
                            or DL4.ID = @DESIGNATIONLEVELID or DL5.ID = @DESIGNATIONLEVELID
                        )
                    and
                        AUCTIONITEM.EVENTAUCTIONID is not null
                    and
                        (
                            (@SITEID is null and exists(select 1 from dbo.EVENTSITE where EVENTSITE.EVENTID = AUCTIONITEM.EVENTAUCTIONID))
                            or
                            (@SITEID is not null and not exists(select 1 from dbo.EVENTSITE where EVENTSITE.EVENTID = AUCTIONITEM.EVENTAUCTIONID and EVENTSITE.SITEID = @SITEID))
                        )


                return @VALID;
            end