USP_PROGRAMEVENTRESOURCES_INSERTRESOURCES

            CREATE procedure dbo.USP_PROGRAMEVENTRESOURCES_INSERTRESOURCES
            (
                @RESOURCES xml,
                @EVENTS xml,
                @PROGRAMID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null,
                @CURRENTDATE datetime = null
            )
            as
            begin
                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                if @CURRENTDATE is null
                    set @CURRENTDATE = getdate();

                declare @idoc int;
                exec sp_xml_preparedocument @idoc OUTPUT, @EVENTS;                

                with RESOURCES_CTE as 
                (
                    select 
                        T.resources.value('(RESOURCEID)[1]', 'uniqueidentifier') as RESOURCEID,
                        T.resources.value('(QUANTITYNEEDED)[1]', 'integer') as QUANTITYNEEDED,
                        T.resources.value('(ISPERTICKETITEM)[1]', 'integer') as ISPERTICKETITEM,
                        RESOURCE.PERTICKETQUANTITY as PERTICKETQUANTITY,
                        @PROGRAMID as PROGRAMID
                    from @RESOURCES.nodes('/RESOURCES/ITEM') T(resources)
                    inner join dbo.RESOURCE 
                        on T.resources.value('(RESOURCEID)[1]', 'uniqueidentifier') = RESOURCE.ID

                )
                insert into dbo.EVENTRESOURCE
                (
                    ID, 
                    EVENTID, 
                    RESOURCEID, 
                    QUANTITYNEEDED, 
                    PERTICKETQUANTITY, 
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select
                    newid(),
                    EVENTID,
                    RCTE.RESOURCEID,
                    RCTE.QUANTITYNEEDED,
                    RCTE.PERTICKETQUANTITY,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from RESOURCES_CTE RCTE
                inner join openxml (@idoc,'/EVENTS/ITEM', 2)
                with
                    (EVENTID uniqueidentifier,
                    PROGRAMID uniqueidentifier,
                    ISCONFLICT bit) P
                 on  RCTE.PROGRAMID = P.PROGRAMID
                where ISCONFLICT = 0

                declare @COUNT int;
                select @COUNT = count(1)
                from @EVENTS.nodes('/EVENTS/ITEM') T(events);

                with RESOURCESCOUNTS_CTE as
                (
                    select
                        sum(QUANTITYNEEDED) as QUANTITIES,
                        RESOURCEID as RESID
                    from dbo.EVENTRESOURCE
                    inner join @EVENTS.nodes('/EVENTS/ITEM') T(events)
                        on T.events.value('(EVENTID)[1]', 'uniqueidentifier') = EVENTRESOURCE.EVENTID
                    group by RESOURCEID
                )

                update dbo.RESOURCE 
                    set QUANTITY = RESOURCE.QUANTITY - RCCTE.QUANTITIES,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from dbo.RESOURCE
                inner join RESOURCESCOUNTS_CTE RCCTE 
                    on RCCTE.RESID = RESOURCE.ID
                where  RESOURCE.ISPERTICKETITEM = 0
                    and RESOURCE.TYPECODE = 1;


                -- insert conflicts now

                with RESOURCES_CTE as 
                (
                    select 
                        T.resources.value('(RESOURCEID)[1]', 'uniqueidentifier') as RESOURCEID,
                        T.resources.value('(QUANTITYNEEDED)[1]', 'integer') as QUANTITYNEEDED,
                        T.resources.value('(ISPERTICKETITEM)[1]', 'integer') as ISPERTICKETITEM,
                        RESOURCE.PERTICKETQUANTITY as PERTICKETQUANTITY,
                        @PROGRAMID as PROGRAMID
                    from @RESOURCES.nodes('/RESOURCES/ITEM') T(resources)
                    inner join dbo.RESOURCE 
                        on T.resources.value('(RESOURCEID)[1]', 'uniqueidentifier') = RESOURCE.ID

                )
                insert into dbo.EVENTRESOURCE
                (
                    ID, 
                    EVENTCONFLICTID, 
                    RESOURCEID, 
                    QUANTITYNEEDED, 
                    PERTICKETQUANTITY, 
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select
                    newid(),
                    CONFLICTID,
                    RCTE.RESOURCEID,
                    RCTE.QUANTITYNEEDED,
                    RCTE.PERTICKETQUANTITY,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from RESOURCES_CTE RCTE
                inner join openxml (@idoc,'/EVENTS/ITEM', 2)
                with
                    (CONFLICTID uniqueidentifier,
                    PROGRAMID uniqueidentifier,
                    ISCONFLICT bit) P
                 on  RCTE.PROGRAMID = P.PROGRAMID
                where ISCONFLICT = 1

                exec sp_xml_removedocument @idoc


            end