USP_TRACK_STAFFRESOURCES_PROCESS

            create procedure dbo.USP_TRACK_STAFFRESOURCES_PROCESS
            (
                @STAFFRESOURCES xml output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CAPACITY int = 0
            )
            as
            begin
                set nocount on;

                /*
                    What we are passed is a set of track staff resources possibly without jobs.
                    what we want to produce is a set of itinerary staff resources all with jobs,
                        also we update the track staff resources to have the new jobs. 
                */

                /* Setup save information */
                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;    

                declare @CURRENTDATE datetime;
                set @CURRENTDATE = getdate();            

                /* We have an existing function for processing a set of staff resources and creating jobs */
                exec dbo.USP_STAFFRESOURCE_PROCESS @STAFFRESOURCES output, @CHANGEAGENTID, 1, 1, @CAPACITY;

                /* DESERIALIZE */
                declare @STAFFRESOURCESTABLE table
                (
                    FILLEDBYCODE tinyint,
                    ID uniqueidentifier,
                    JOBDESCRIPTION nvarchar(255),
                    JOBID uniqueidentifier,
                    JOBNAME nvarchar(100)
                );

                insert into @STAFFRESOURCESTABLE
                    (FILLEDBYCODE, ID, JOBDESCRIPTION, JOBID, JOBNAME)
                select T.c.value('(FILLEDBYCODE)[1]','tinyint') AS 'FILLEDBYCODE',
                        T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                        T.c.value('(JOBDESCRIPTION)[1]','nvarchar(255)') AS 'JOBDESCRIPTION',
                        T.c.value('(JOBID)[1]','uniqueidentifier') AS 'JOBID',
                        T.c.value('(JOBNAME)[1]','nvarchar(100)') AS 'JOBNAME'
                from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c);

                /* Update the TRACKSTAFFRESOURCE table with the new job information. */
                update dbo.TRACKSTAFFRESOURCE
                    set JOBID = STAFFRESOURCESTABLE.JOBID,
                        JOBNAME = STAFFRESOURCESTABLE.JOBNAME,
                        JOBDESCRIPTION = STAFFRESOURCESTABLE.JOBDESCRIPTION,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                from dbo.TRACKSTAFFRESOURCE
                    inner join @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                        on STAFFRESOURCESTABLE.ID = TRACKSTAFFRESOURCE.ID
                where (not STAFFRESOURCESTABLE.JOBID is null) and (STAFFRESOURCESTABLE.FILLEDBYCODE = 0); /* Filled by = Volunteer */
            end