USP_PROGRAMEVENTSTAFFRESOURCES_INSERTSTAFFRESOURCES

            CREATE procedure dbo.USP_PROGRAMEVENTSTAFFRESOURCES_INSERTSTAFFRESOURCES
            (
                @STAFFRESOURCES 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 @PROGRAMNAME nvarchar(100);
                select @PROGRAMNAME = NAME
                from dbo.PROGRAM
                where ID = @PROGRAMID


                declare @JOBS table (JOBID uniqueidentifier, VOLUNTEERTYPEID uniqueidentifier, QUANTITYNEEDED integer, ISPERTICKETITEM integer,
                                    FILLEDBYCODE integer, PROGRAMID uniqueidentifier);
                insert into @JOBS
                select
                    newid(),
                    T.resources.value('(VOLUNTEERTYPEID)[1]', 'uniqueidentifier') as VOLUNTEERTYPEID,
                    T.resources.value('(QUANTITYNEEDED)[1]', 'integer') as QUANTITYNEEDED,
                    T.resources.value('(ISPERTICKETITEM)[1]', 'integer') as ISPERTICKETITEM,
                    T.resources.value('(FILLEDBYCODE)[1]', 'tinyint') as FILLEDBYCODE,
                    @PROGRAMID
                from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(resources)
                where ((T.resources.value('(JOBID)[1]', 'uniqueidentifier') is null) or 
                    (T.resources.value('(JOBID)[1]', 'uniqueidentifier') = '00000000-0000-0000-0000-000000000000'))
                    and not exists (select 1 from dbo.PROGRAMSTAFFRESOURCE
                                    where PROGRAMSTAFFRESOURCE.VOLUNTEERTYPEID = T.resources.value('(VOLUNTEERTYPEID)[1]', 'uniqueidentifier'))
                    and not exists (select 1 from dbo.JOB 
                                    inner join dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = T.resources.value('(VOLUNTEERTYPEID)[1]', 'uniqueidentifier')
                                    where JOB.NAME = (left(@PROGRAMNAME + ' - ' + VOLUNTEERTYPE.NAME, 100)))

                insert into dbo.JOB (ID, NAME, VOLUNTEERTYPEID, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                select
                    jobs.JOBID,
                    left(PROGRAM.NAME + ' - ' + VOLUNTEERTYPE.NAME, 100) as NAME,                        
                    jobs.VOLUNTEERTYPEID,
                    @CHANGEAGENTID as ADDEDBYID,
                    @CHANGEAGENTID as CHANGEDBYID,
                    @CURRENTDATE as DATEADDED,
                    @CURRENTDATE as DATECHANGED
                from @JOBS jobs
                inner join dbo.PROGRAM on jobs.PROGRAMID = PROGRAM.ID
                inner join dbo.VOLUNTEERTYPE on jobs.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID

                insert into @JOBS
                select
                    T.resources.value('(JOBID)[1]', 'uniqueidentifier') as JOBID,
                    T.resources.value('(VOLUNTEERTYPEID)[1]', 'uniqueidentifier') as VOLUNTEERTYPEID,
                    T.resources.value('(QUANTITYNEEDED)[1]', 'integer') as QUANTITYNEEDED,
                    T.resources.value('(ISPERTICKETITEM)[1]', 'integer') as ISPERTICKETITEM,
                    T.resources.value('(FILLEDBYCODE)[1]', 'tinyint') as FILLEDBYCODE,
                    @PROGRAMID
                from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(resources)
                where not exists (select 1 from @JOBS jobs where jobs.VOLUNTEERTYPEID = T.resources.value('(VOLUNTEERTYPEID)[1]', 'uniqueidentifier'))

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

                insert into dbo.EVENTSTAFFRESOURCE
                (
                    ID, 
                    EVENTID, 
                    VOLUNTEERTYPEID,
                    JOBID,
                    QUANTITYNEEDED, 
                    FILLEDBYCODE, 
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select
                    newid(),
                    EVENTID,
                    jobs.VOLUNTEERTYPEID,
                    jobs.JOBID,
                    jobs.QUANTITYNEEDED,
                    jobs.FILLEDBYCODE,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @JOBS jobs
                inner join openxml (@idoc,'/EVENTS/ITEM', 2)
                with
                    (EVENTID uniqueidentifier,
                    PROGRAMID uniqueidentifier,
                    ISCONFLICT bit) P
                 on  jobs.PROGRAMID = P.PROGRAMID
                where ISCONFLICT = 0;

                with STAFFCONFLICTS_CTE as
                (
                    select 
                        T.resources.value('(VOLUNTEERTYPEID)[1]', 'uniqueidentifier') as VOLUNTEERTYPEID,
                        T.resources.value('(QUANTITYNEEDED)[1]', 'integer') as QUANTITYNEEDED,
                        T.resources.value('(ISPERTICKETITEM)[1]', 'integer') as ISPERTICKETITEM,
                        T.resources.value('(FILLEDBYCODE)[1]', 'tinyint') as FILLEDBYCODE,
                        case when T.resources.value('(JOBID)[1]', 'uniqueidentifier') = '00000000-0000-0000-0000-000000000000'
                        then null
                        else T.resources.value('(JOBID)[1]', 'uniqueidentifier')
                        end as JOBID,
                        @PROGRAMID as PROGRAMID
                    from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(resources)
                )
                insert into dbo.EVENTSTAFFRESOURCE
                (
                    ID, 
                    EVENTCONFLICTID, 
                    VOLUNTEERTYPEID, 
                    QUANTITYNEEDED, 
                    FILLEDBYCODE, 
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select
                    newid(),
                    CONFLICTID,
                    SCTE.VOLUNTEERTYPEID,
                    SCTE.QUANTITYNEEDED,
                    SCTE.FILLEDBYCODE,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from STAFFCONFLICTS_CTE SCTE
                inner join openxml (@idoc,'/EVENTS/ITEM', 2)
                with
                    (CONFLICTID uniqueidentifier,
                    PROGRAMID uniqueidentifier,
                    ISCONFLICT bit) P
                 on  SCTE.PROGRAMID = P.PROGRAMID
                where ISCONFLICT = 1

                exec sp_xml_removedocument @idoc                

            end