UFN_EVENTCONFLICT_GETRESOURCESINCONFLICT

Get all of the resources that are in conflict within the supplied information.

Return

Return Type
table

Parameters

Parameter	Parameter Type	Mode
@START	datetime	IN
@END	datetime	IN
@RESOURCES	xml	IN
@IGNORERECORDID	uniqueidentifier	IN
@IGNORESUBRECORDID	uniqueidentifier	IN

Definition

Copy



              CREATE function dbo.UFN_EVENTCONFLICT_GETRESOURCESINCONFLICT
                (
                  @START datetime,
                  @END datetime,
                  @RESOURCES xml,
                  @IGNORERECORDID uniqueidentifier = null,
                  @IGNORESUBRECORDID uniqueidentifier = null
                )
              returns @CONFLICTS TABLE
                    (
                        RESOURCEID uniqueidentifier,
                        QUANTITYNEEDED int
                    )
              as
              begin 

                -- Take the resource xml passed in and generate a table of resources

                declare @RESOURCESTABLE table
                (
                  RESOURCEID uniqueidentifier,
                  QUANTITYNEEDED int
                )

                insert into @RESOURCESTABLE
                  (RESOURCEID, QUANTITYNEEDED)
                  select T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
                         T.c.value('(QUANTITYNEEDED)[1]','int')
                  from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)

                insert into @RESOURCESTABLE
                  (RESOURCEID, QUANTITYNEEDED)
                  select T.c.value('(RESOURCEID)[1]','uniqueidentifier'),
                         T.c.value('(QUANTITYNEEDED)[1]','int')
                  from @RESOURCES.nodes('/EVENTRESOURCES/ITEM') T(c)

                -- Use the detail version with distinct select to get only the events, itineraries, and itinerary items.

                insert into @CONFLICTS
                    (RESOURCEID, QUANTITYNEEDED)
                select distinct RESOURCECONFLICTDETAILS.RESOURCEID, RESOURCES.QUANTITYNEEDED
                from dbo.UFN_EVENTCONFLICT_GETRESOURCECONFLICTDETAILS(@START, @END, @RESOURCES, @IGNORERECORDID, @IGNORESUBRECORDID, null, 1, 1, 1, null) as RESOURCECONFLICTDETAILS
                    inner join @RESOURCESTABLE as RESOURCES
                        on RESOURCECONFLICTDETAILS.RESOURCEID = RESOURCES.RESOURCEID

                    return;
                end