UFN_EVENTCONFLICT_GETLOCATIONCONFLICTDETAILS

            CREATE function dbo.UFN_EVENTCONFLICT_GETLOCATIONCONFLICTDETAILS
            (
                @START datetime,
                @END datetime,
                @LOCATIONS xml,
                @IGNORERECORDID uniqueidentifier = null,
                @IGNORESUBRECORDID uniqueidentifier = null,
                @IGNORESUPERRECORDID uniqueidentifier = null,
                @INCLUDEEVENTCONFLICTS bit = 1,
                @INCLUDEITINERARYITEMCONFLICTS 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

            if (@INCLUDEEVENTCONFLICTS = 1)
                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
                    (
                        EVENT.ID <> @IGNORERECORDID or 
                        ( @IGNORERECORDID is null and EVENT.ID is not null )
                    ) 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

            if (@INCLUDEITINERARYITEMCONFLICTS = 1)
                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 
                    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

                    (
                        ITINERARY.RESERVATIONID <> @IGNORESUPERRECORDID or @IGNORESUPERRECORDID is null
                    ) 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 
                    (
                        ITINERARYITEM.ITINERARYID <> @IGNORERECORDID or 
                        ( @IGNORERECORDID is null and ITINERARYITEM.ITINERARYID is not null ) or 
                        ITINERARYITEM.ID <> @IGNORESUBRECORDID or 
                        ( @IGNORESUBRECORDID is null and ITINERARYITEM.ID is not null )
                    ) 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