USP_DATAFORMTEMPLATE_EDIT_ITINERARYSTAFFRESOURCE

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_EDIT_ITINERARYSTAFFRESOURCE
                    (
                        @ID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @QUANTITYNEEDED int,
                        @IGNORECONFLICTS bit,
                        @VOLUNTEERS xml,
                        @ASSIGNMENTS xml
                    )
                    as

                        set nocount on;

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

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        declare @VOLUNTEERTYPEID uniqueidentifier;
                        declare @JOBOCCURRENCEID uniqueidentifier;
                        declare @FILLEDBYCODE tinyint;
                        declare @STATUSCODE tinyint;

                        select @VOLUNTEERTYPEID = ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
                                @FILLEDBYCODE = ITINERARYSTAFFRESOURCE.FILLEDBYCODE,
                                @JOBOCCURRENCEID = ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID,
                                @STATUSCODE = STATUSCODE
                        from dbo.ITINERARYSTAFFRESOURCE
                        inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYSTAFFRESOURCE.ITINERARYID
                        inner join dbo.RESERVATION on RESERVATION.ID = ITINERARY.RESERVATIONID
                        inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
                        where ITINERARYSTAFFRESOURCE.ID = @ID

                        if @STATUSCODE in (1, 5)
                            raiserror('BBERR_INVALIDSTATUS', 13, 1);

                        /* Make sure we are checking for conflicts and filled by code must be
                            volunteer as that is currently the only kind of staff resource 
                            that can cause conflicts. */
                        if (@IGNORECONFLICTS = 0) and (@FILLEDBYCODE = 0)
                        begin
                            declare @START datetime;
                            declare @END datetime;
                            declare @ITINERARYID uniqueidentifier;

                            select 
                                @START = ITINERARY.STARTDATETIME,
                                @END = ITINERARY.ENDDATETIME,
                                @ITINERARYID = ITINERARY.ID
                            from dbo.ITINERARY
                            inner join dbo.ITINERARYSTAFFRESOURCE on 
                                ITINERARY.ID = ITINERARYSTAFFRESOURCE.ITINERARYID
                            where ITINERARYSTAFFRESOURCE.ID = @ID;

                            /* We only want conflicts for the staff resource we are adding so we pretend this is the only one */
                            declare @ITINERARYSTAFFRESOURCE xml;
                            set @ITINERARYSTAFFRESOURCE = '<STAFFRESOURCES><ITEM><VOLUNTEERTYPEID>' + convert(nvarchar(36), @VOLUNTEERTYPEID) + '</VOLUNTEERTYPEID><QUANTITYNEEDED>' + convert(nvarchar(20), @QUANTITYNEEDED) + '</QUANTITYNEEDED><FILLEDBYCODE>0</FILLEDBYCODE></ITEM></STAFFRESOURCES>'


                            if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                            (
                                @START, @END,
                                null, null,    @ITINERARYSTAFFRESOURCE,
                                null, @ITINERARYID, null,
                                0,
                                1,
                                0,
                                0
                            ) = 1
                            begin                    
                                raiserror('BBERR_CONFLICTSEXIST', 13, 1);
                                return 1;
                            end
                        end

                        begin try
                            -- handle updating the data

                            update dbo.ITINERARYSTAFFRESOURCE
                            set QUANTITYNEEDED = @QUANTITYNEEDED,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where ID = @ID;

                            update dbo.JOBOCCURRENCE
                            set VOLUNTEERSNEEDED = @QUANTITYNEEDED,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where ID = @JOBOCCURRENCEID;

                            declare @ORDERID uniqueidentifier
                            declare @ISFLATRATE bit = 0

                            select
                                @ORDERID = RESERVATION.ID,
                                @ISFLATRATE = case when PRICINGCODE = 1 then 1 else 0 end
                            from dbo.RESERVATION
                                inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = RESERVATION.ID
                            where ITINERARY.ID = @ITINERARYID

                            exec dbo.USP_ITINERARY_STAFFRESOURCES_SALESORDERSYNC @ITINERARYID, @ORDERID, @CHANGEAGENTID

                            if @ISFLATRATE = 1
                                exec dbo.USP_RESERVATION_DISTRIBUTEAPPLICATIONS @ORDERID, @CHANGEAGENTID, @CURRENTDATE;    

                            -- Generate taxes last

                            exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;

                            if @ISFLATRATE = 1
                                exec dbo.USP_RESERVATIONRATESCALE_UPDATETAXES @ORDERID, @CHANGEAGENTID, @CURRENTDATE;    

                        end try
                        begin catch
                            exec dbo.USP_RAISE_ERROR
                            return 1
                        end catch

                        return 0;