UFN_SITEID_MAPFROM_AUCTIONITEMID

             CREATE function dbo.UFN_SITEID_MAPFROM_AUCTIONITEMID
             (
                @AUCTIONITEMID nvarchar(100)
             )
             returns @SITE table
             (
                SITEID uniqueidentifier
             )
             as 
             begin

                --There are situations where we had to prefix the AUCTIONITEMID with codes

                --When we do this, we prefix the ID with something like "1|<auction item id>"

                --We needed to strip off these extra characters

                --There are also situations when adding auction items to packages where we overload the contextID

                --with the ID of the auction item followed by the ID of the auction package.

                --Ex: <auction item ID>;<auction package ID>


                declare @ITEMID uniqueidentifier;

                select @ITEMID = 
                case
                    when charindex(';',@AUCTIONITEMID) > 0 and len(@AUCTIONITEMID) > 36 then
                        cast(substring(@AUCTIONITEMID, 0, 37) as uniqueidentifier)
                    else
                        cast(RIGHT(LTRIM(RTRIM(@AUCTIONITEMID)),36) as uniqueidentifier)
                end

                insert into @SITE
                select distinct DESIGNATIONLEVEL.SITEID 
                from dbo.AUCTIONITEM
                inner join dbo.REVENUESPLIT on REVENUESPLIT.REVENUEID = AUCTIONITEM.REVENUEAUCTIONDONATIONID 
                inner join dbo.DESIGNATION on DESIGNATION.ID = REVENUESPLIT.DESIGNATIONID 
                inner join dbo.DESIGNATIONLEVEL on DESIGNATIONLEVEL.ID = DESIGNATION.DESIGNATIONLEVEL1ID 
                                                or DESIGNATIONLEVEL.ID = DESIGNATION.DESIGNATIONLEVEL2ID
                                                or DESIGNATIONLEVEL.ID = DESIGNATION.DESIGNATIONLEVEL3ID
                                                or DESIGNATIONLEVEL.ID = DESIGNATION.DESIGNATIONLEVEL4ID
                                                or DESIGNATIONLEVEL.ID = DESIGNATION.DESIGNATIONLEVEL5ID

                where AUCTIONITEM.ID = @ITEMID and AUCTIONITEM.TYPECODE = 0
                union
                select distinct EVENTSITE.SITEID
                from dbo.AUCTIONITEM 
                left join dbo.EVENTSITE on EVENTSITE.EVENTID = AUCTIONITEM.EVENTAUCTIONID --JamesWill 167145 2011-07-21 When there are no sites on the event, return null as the SITEID

                where AUCTIONITEM.ID = @ITEMID and AUCTIONITEM.TYPECODE = 1


                return

             end