USP_STAFFRESOURCE_PROCESS

            CREATE procedure dbo.USP_STAFFRESOURCE_PROCESS
            (
                @STAFFRESOURCES xml output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CREATENEWRECORIDS bit,
                @UPDATEPERCAPACITYRESOURCEQUANTITY bit = 0,
                @CAPACITY int = 0
            )
            as begin
                set nocount on;

                -- Setup the save information

                if @CHANGEAGENTID is null  
                    exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output    

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

                -- First turn the xml into a temp table

                declare @STAFFRESOURCESTABLE table
                (
                    FILLEDBYCODE tinyint,
                    ID uniqueidentifier,
                    JOBDESCRIPTION nvarchar(255),
                    JOBID uniqueidentifier,
                    JOBNAME nvarchar(100),
                    QUANTITYNEEDED int,
                    VOLUNTEERTYPEID uniqueidentifier,
                    PRICE money,
                    PRICINGSTRUCTURECODE tinyint,
                    ORIGINALID uniqueidentifier
                )

                insert into @STAFFRESOURCESTABLE
                (
                    FILLEDBYCODE, 
                    ID, 
                    JOBDESCRIPTION, 
                    JOBID, 
                    JOBNAME, 
                    QUANTITYNEEDED, 
                    VOLUNTEERTYPEID, 
                    PRICE, 
                    PRICINGSTRUCTURECODE
                )
                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',
                    T.c.value('(QUANTITYNEEDED)[1]','int') AS 'QUANTITYNEEDED',
                    T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') AS 'VOLUNTEERTYPEID',
                    T.c.value('(PRICE)[1]','money') AS 'PRICE',
                    T.c.value('(PRICINGSTRUCTURECODE)[1]','tinyint') AS 'PRICINGSTRUCTURECODE'
                from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)

                /* find any jobs that match by name and volunteer type first */
                update @STAFFRESOURCESTABLE set 
                    JOBID = JOB.ID
                from dbo.JOB
                inner join @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE on 
                    STAFFRESOURCESTABLE.JOBNAME = JOB.NAME and 
                    STAFFRESOURCESTABLE.VOLUNTEERTYPEID = JOB.VOLUNTEERTYPEID
                where 
                    (JOBID is null or JOBID = '00000000-0000-0000-0000-000000000000') and 
                    FILLEDBYCODE = 0

                /* Finally check that there is only one new job that is going to be created per job name and volunteer type combo */
                update @STAFFRESOURCESTABLE set 
                    JOBDESCRIPTION = TOPJOBNAME.JOBDESCRIPTION
                from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                inner join 
                (
                    select 
                        DISTINCTJOBDESCRIPTION.JOBNAME, 
                        DISTINCTJOBDESCRIPTION.VOLUNTEERTYPEID,
                        (
                            select top 1 
                                TOPJOBDESCRIPTION.JOBDESCRIPTION
                            from @STAFFRESOURCESTABLE as TOPJOBDESCRIPTION
                            where 
                                TOPJOBDESCRIPTION.JOBNAME = DISTINCTJOBDESCRIPTION.JOBNAME and
                                TOPJOBDESCRIPTION.VOLUNTEERTYPEID = DISTINCTJOBDESCRIPTION.VOLUNTEERTYPEID
                        ) as JOBDESCRIPTION
                    from @STAFFRESOURCESTABLE as DISTINCTJOBDESCRIPTION
                ) as TOPJOBNAME on 
                    TOPJOBNAME.JOBNAME = STAFFRESOURCESTABLE.JOBNAME and 
                    TOPJOBNAME.VOLUNTEERTYPEID = STAFFRESOURCESTABLE.VOLUNTEERTYPEID
                where 
                    (
                        STAFFRESOURCESTABLE.JOBID is null or 
                        STAFFRESOURCESTABLE.JOBID = '00000000-0000-0000-0000-000000000000'
                    ) and 
                    STAFFRESOURCESTABLE.FILLEDBYCODE = 0

                declare @DISTINCTJOB table
                (
                    JOBID uniqueidentifier,
                    JOBNAME nvarchar(100),
                    JOBDESCRIPTION nvarchar(255)
                );

                insert into @DISTINCTJOB (JOBNAME, JOBDESCRIPTION)
                select distinct 
                    JOBNAME, 
                    JOBDESCRIPTION
                from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                where 
                    (
                        STAFFRESOURCESTABLE.JOBID is null or 
                        STAFFRESOURCESTABLE.JOBID = '00000000-0000-0000-0000-000000000000'
                    ) and 
                    STAFFRESOURCESTABLE.FILLEDBYCODE = 0

                update @DISTINCTJOB set JOBID = NEWID();

                update @STAFFRESOURCESTABLE set 
                    JOBID = DISTINCTJOB.JOBID
                from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                inner join @DISTINCTJOB as DISTINCTJOB on 
                    STAFFRESOURCESTABLE.JOBNAME = DISTINCTJOB.JOBNAME and 
                    STAFFRESOURCESTABLE.JOBDESCRIPTION = DISTINCTJOB.JOBDESCRIPTION;

                /* Now create the jobs needed */
                insert into dbo.JOB
                (
                    ID, 
                    NAME, 
                    DESCRIPTION, 
                    VOLUNTEERTYPEID, 
                    ADDEDBYID, 
                    CHANGEDBYID, 
                    DATEADDED, 
                    DATECHANGED
                )
                select distinct 
                    STAFFRESOURCESTABLE.JOBID, 
                    STAFFRESOURCESTABLE.JOBNAME, 
                    STAFFRESOURCESTABLE.JOBDESCRIPTION, 
                    STAFFRESOURCESTABLE.VOLUNTEERTYPEID, 
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @CURRENTDATE
                from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                left outer join dbo.JOB on STAFFRESOURCESTABLE.JOBID = JOB.ID 
                where 
                    JOB.ID is null and 
                    STAFFRESOURCESTABLE.FILLEDBYCODE = 0;

                /* Update any null pricing information fields with values pulled from the resource pricing tables */
                update @STAFFRESOURCESTABLE set 
                    PRICE = COALESCE(VOLUNTEERTYPEPRICING.PRICE,0),
                    PRICINGSTRUCTURECODE = COALESCE(VOLUNTEERTYPEPRICING.PRICINGSTRUCTURECODE,0)
                from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                left join VOLUNTEERTYPEPRICING on 
                    STAFFRESOURCESTABLE.VOLUNTEERTYPEID = VOLUNTEERTYPEPRICING.ID
                where 
                    STAFFRESOURCESTABLE.ID = '00000000-0000-0000-0000-000000000000' or 
                    STAFFRESOURCESTABLE.ID is null or 
                    STAFFRESOURCESTABLE.PRICE is null or 
                    STAFFRESOURCESTABLE.PRICINGSTRUCTURECODE is null or 
                    @CREATENEWRECORIDS = 1

                /* If asked to create new record IDs then wipe out the existing record IDs and replace them with new IDs */
                if (@CREATENEWRECORIDS = 1)
                begin
                    update @STAFFRESOURCESTABLE set ORIGINALID = ID;

                    update @STAFFRESOURCESTABLE set ID = newid();
                end

                /* If we need to update the per capacity resources do so here */
                if (@UPDATEPERCAPACITYRESOURCEQUANTITY = 1)
                begin
                    update @STAFFRESOURCESTABLE set 
                        QUANTITYNEEDED = ceiling(cast(@CAPACITY as decimal) / VOLUNTEERTYPE.CAPACITYPERRESOURCE)
                    from @STAFFRESOURCESTABLE as STAFFRESOURCESTABLE
                    inner join dbo.VOLUNTEERTYPE on 
                        STAFFRESOURCESTABLE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
                    where (VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0)
                end

                /* Now recreate the XML from the temp table and setup the return value */
                /* SERIALIZE */
                set @STAFFRESOURCES = 
                    (
                        select 
                            [FILLEDBYCODE], 
                            [ID], 
                            [JOBDESCRIPTION], 
                            [JOBID], 
                            [JOBNAME], 
                            [QUANTITYNEEDED], 
                            [VOLUNTEERTYPEID], 
                            [PRICE], 
                            [PRICINGSTRUCTURECODE], 
                            [ORIGINALID]
                        from @STAFFRESOURCESTABLE
                        for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),BINARY BASE64
                    )
            end