USP_RESOURCE_PROCESS

            CREATE procedure dbo.USP_RESOURCE_PROCESS
            (
                @RESOURCES xml output,
                @CHANGEAGENTID uniqueidentifier = null,
                @CREATENEWRECORIDS bit = 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 @RESOURCESTABLE table
                (
                    ID uniqueidentifier,
                    RESOURCEID uniqueidentifier,
                    QUANTITYNEEDED int,
                    ISPERTICKETITEM bit,
                    PERTICKETQUANTITY int,
                    PRICE money,
                    PRICINGSTRUCTURECODE tinyint,
                    ORIGINALID uniqueidentifier,
                    PERTICKETDIVISOR int
                )

                insert into @RESOURCESTABLE
                (
                    ID,
                    RESOURCEID,
                    QUANTITYNEEDED,
                    ISPERTICKETITEM,
                    PERTICKETQUANTITY,
                    PRICE,
                    PRICINGSTRUCTURECODE,
                    PERTICKETDIVISOR
                )
                select 
                    T.c.value('(ID)[1]','uniqueidentifier') AS 'ID',
                    T.c.value('(RESOURCEID)[1]','uniqueidentifier') AS 'RESOURCEID',
                    COALESCE(T.c.value('(QUANTITYNEEDED)[1]','int'), 0) AS 'QUANTITYNEEDED',
                    T.c.value('(ISPERTICKETITEM)[1]','bit') AS 'ISPERTICKETITEM',
                    0 AS 'PERTICKETQUANTITY',
                    T.c.value('(PRICE)[1]','money') AS 'PRICE',
                    T.c.value('(PRICINGSTRUCTURECODE)[1]','tinyint') AS 'PRICINGSTRUCTURECODE',
                    0 AS 'PERTICKETDIVISOR'
                from @RESOURCES.nodes('/RESOURCES/ITEM') T(c)

                -- Update any null pricing information fields with values pulled from the resource pricing tables

                update @RESOURCESTABLE set 
                    PRICE = COALESCE(RESOURCEPRICING.PRICE,0),
                    PRICINGSTRUCTURECODE = COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0)
                from @RESOURCESTABLE as RESOURCESTABLE
                left join RESOURCEPRICING on RESOURCESTABLE.RESOURCEID = RESOURCEPRICING.ID
                where 
                    RESOURCESTABLE.ID = '00000000-0000-0000-0000-000000000000' or 
                    RESOURCESTABLE.ID is null or 
                    RESOURCESTABLE.PRICE is null or 
                    RESOURCESTABLE.PRICINGSTRUCTURECODE is null or 
                    (
                        RESOURCESTABLE.PRICINGSTRUCTURECODE = 0 and 
                        RESOURCESTABLE.PRICINGSTRUCTURECODE <> COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0)
                    ) or 
                    @CREATENEWRECORIDS = 1

                -- Update the per-ticket quantity for per-ticket resources

                update @RESOURCESTABLE set
                    QUANTITYNEEDED = 0,
                    PERTICKETQUANTITY = RESOURCE.PERTICKETQUANTITY,
                    PERTICKETDIVISOR = RESOURCE.PERTICKETDIVISOR
                from @RESOURCESTABLE as RESOURCESTABLE
                inner join RESOURCE on RESOURCESTABLE.RESOURCEID = RESOURCE.ID
                where RESOURCE.ISPERTICKETITEM <> 0 

                if (@CREATENEWRECORIDS = 1)
                begin
                    update @RESOURCESTABLE set ORIGINALID = ID;

                    update @RESOURCESTABLE set ID = newid();
                end

                -- Now recreate the XML from the temp table and setup the return value

                set @RESOURCES = 
                    (
                        select 
                            [ID], 
                            [RESOURCEID], 
                            [QUANTITYNEEDED], 
                            [ISPERTICKETITEM], 
                            [PERTICKETQUANTITY], 
                            [PRICE], 
                            [PRICINGSTRUCTURECODE], 
                            [ORIGINALID],
                            [PERTICKETDIVISOR]
                        from @RESOURCESTABLE
                        for xml raw('ITEM'),type,elements,root('RESOURCES'),BINARY BASE64
                    )
            end