UFN_CONFLICTCHECK_GETLOCATIONCONFLICTS

        CREATE function dbo.UFN_CONFLICTCHECK_GETLOCATIONCONFLICTS
        (
            @START datetime,
            @END datetime,
            @LOCATIONS xml,
            @SUPERRECORDID uniqueidentifier = null,
            @RECORDID uniqueidentifier = null,
            @SUBRECORDID uniqueidentifier = null,
            @IGNORESUPERRECORD bit = 1,
            @IGNORERECORD bit = 1,
            @IGNORESUBRECORD bit = 1,
            @IGNORERECORDSUBRECORDS bit = 1
        )
        returns @CONFLICTS TABLE
        (
            EVENTID uniqueidentifier,
            ITINERARYITEMID uniqueidentifier,
            LOCATIONID uniqueidentifier
        )
        as
        begin 
        --Build a table of the locations based on the xml passed in

        declare @LOCATIONSTABLE table
        (
            LOCATIONID uniqueidentifier
        )

        insert into @LOCATIONSTABLE (LOCATIONID)
        select T.c.value('(LOCATIONID)[1]','uniqueidentifier') 
        from @LOCATIONS.nodes('/LOCATIONS/ITEM') T(c)

        insert into @LOCATIONSTABLE (LOCATIONID)
        select T.c.value('(EVENTLOCATIONID)[1]','uniqueidentifier') 
        from @LOCATIONS.nodes('/LOCATIONS/ITEM') T(c)

        -- Check the events to see if we have any conflicts

        insert into @CONFLICTS
        (EVENTID, LOCATIONID)
        select 
            EVENT.ID, 
            coalesce(PROGRAMEVENTLOCATION.EVENTLOCATIONID, EVENT.EVENTLOCATIONID)
        from dbo.EVENT
        left outer join dbo.PROGRAMEVENTLOCATION on 
            EVENT.ID = PROGRAMEVENTLOCATION.EVENTID
        left outer join dbo.PROGRAM    on 
            EVENT.PROGRAMID = PROGRAM.ID
        inner join @LOCATIONSTABLE as CONFLICTPROGRAMEVENTLOCATION on 
            CONFLICTPROGRAMEVENTLOCATION.LOCATIONID = PROGRAMEVENTLOCATION.EVENTLOCATIONID or 
            EVENT.EVENTLOCATIONID = CONFLICTPROGRAMEVENTLOCATION.LOCATIONID
        where 
            (PROGRAM.ISACTIVE = 1 or EVENT.PROGRAMID is null) and
            (@IGNORERECORD = 0 or @RECORDID is null or EVENT.ID <> @RECORDID) and
            (
                (@START >= EVENT.STARTDATETIME and @START < EVENT.ENDDATETIME) or
                (@END > EVENT.STARTDATETIME and @END <= EVENT.ENDDATETIME) or
                (@START < EVENT.STARTDATETIME and @END > EVENT.ENDDATETIME)
            )

        -- Check the itinerary items to see if we have any conflicts

        insert into @CONFLICTS
        (
            ITINERARYITEMID, 
            LOCATIONID
        )
        select DISTINCT 
            ITINERARYITEM.ID, 
            ITINERARYITEM.EVENTLOCATIONID
        from dbo.ITINERARYITEM
        inner join dbo.ITINERARY on 
            ITINERARYITEM.ITINERARYID = ITINERARY.ID
        inner join dbo.SALESORDER on 
            SALESORDER.ID = ITINERARY.RESERVATIONID
        inner join @LOCATIONSTABLE as CONFLICTLOCATIONS    on 
            ITINERARYITEM.EVENTLOCATIONID = CONFLICTLOCATIONS.LOCATIONID
        where 
            SALESORDER.STATUSCODE <> 5 and  -- No cancelled reservation locations

            ITINERARYITEM.INVALIDREASONCODE = 0 and -- Make sure that the itinerary item does not have an invalid reason

            ITINERARYITEM.ITEMTYPECODE <> 3 and -- Make sure the itinerary item is showing as scheduled

            (@IGNORESUPERRECORD = 0 or @SUPERRECORDID is null or ITINERARY.RESERVATIONID <> @SUPERRECORDID) and
            (
                ITINERARYITEM.ITEMTYPECODE <> 2 or ITINERARYITEM.BLOCKEVENT = 1 --Make sure the item is either not a custom item, if it is a custom item only count it if the location is marked busy

            ) and 
            (
                not (ITINERARY.ID = @RECORDID and @IGNORERECORDSUBRECORDS = 1) and                
                (@IGNORESUBRECORD = 0 or @SUBRECORDID is null or ITINERARYITEM.ID <> @SUBRECORDID)
            ) and 
            (
                (
                    @START >= ITINERARYITEM.STARTDATETIME and 
                    @START < ITINERARYITEM.ENDDATETIME
                ) or
                (
                    @END > ITINERARYITEM.STARTDATETIME and 
                    @END <= ITINERARYITEM.ENDDATETIME
                ) or
                (
                    @START < ITINERARYITEM.STARTDATETIME and 
                    @END > ITINERARYITEM.ENDDATETIME
                )
            )

            return
        end