USP_DATAFORMTEMPLATE_ADD_ITINERARYSTAFFRESOURCE

                    CREATE procedure dbo.USP_DATAFORMTEMPLATE_ADD_ITINERARYSTAFFRESOURCE
                    (
                        @ID uniqueidentifier = null output,
                        @ITINERARYID uniqueidentifier,
                        @CHANGEAGENTID uniqueidentifier = null,
                        @VOLUNTEERTYPEID uniqueidentifier,
                        @QUANTITYNEEDED int,
                        @FILLEDBYCODE tinyint = 0,
                        @JOBID uniqueidentifier = null,
                        @JOBNAME nvarchar(100) = null,
                        @JOBDESCRIPTION nvarchar(255) = null,
                        @IGNORECONFLICTS bit,
                        @VOLUNTEERS xml = null,
                        @ASSIGNMENTS xml = null
                    )
                    as
                        set nocount on;

                        if (@ID is null)
                            set @ID = newid();

                        if (@CHANGEAGENTID is null)
                            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

                        declare @CURRENTDATE datetime
                        set @CURRENTDATE = getdate()

                        declare @STATUSCODE tinyint;
                        select @STATUSCODE = SALESORDER.STATUSCODE
                        from dbo.ITINERARY
                        inner join dbo.SALESORDER on SALESORDER.ID = ITINERARY.RESERVATIONID
                        where ITINERARY.ID = @ITINERARYID

                        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

                            --  Check to see if a resource of this volunteer type already exists

                            --    for this itinerary, if so there is no need for a conflict check 

                            --    since the insert will fail


                            if not exists 
                            (
                                select 1 from dbo.ITINERARYSTAFFRESOURCE 
                                where ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = @VOLUNTEERTYPEID and 
                                ITINERARYSTAFFRESOURCE.ITINERARYID = @ITINERARYID
                            )
                            begin
                                declare @START datetime;
                                declare @END datetime;

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

                                /* 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
                        end

                        begin try
                            -- Need to work on creating the job

                            -- Use the JOBNAME and JOBDESCRIPTION fields

                            if ((@JOBID is null) and (@FILLEDBYCODE = 0))
                            begin
                                /* Update the null job fields with the corresponding jobs of any existing jobs with the same name as those we would create */                            
                                select @JOBID = JOB.ID
                                from dbo.JOB
                                where JOB.NAME = @JOBNAME and JOB.VOLUNTEERTYPEID = @VOLUNTEERTYPEID;

                                if (@JOBID is null)
                                begin
                                    set @JOBID = newid();

                                    insert into dbo.JOB
                                        (ID, 
                                        NAME, 
                                        DESCRIPTION, 
                                        VOLUNTEERTYPEID, 
                                        ADDEDBYID, 
                                        CHANGEDBYID, 
                                        DATEADDED, 
                                        DATECHANGED)
                                    values 
                                        (@JOBID, 
                                        @JOBNAME, 
                                        @JOBDESCRIPTION, 
                                        @VOLUNTEERTYPEID, 
                                        @CHANGEAGENTID, 
                                        @CHANGEAGENTID, 
                                        @CURRENTDATE, 
                                        @CURRENTDATE)
                                end
                            end

                            -- handle inserting the data

                            insert into dbo.ITINERARYSTAFFRESOURCE
                                (ID,
                                ITINERARYID,
                                VOLUNTEERTYPEID,
                                QUANTITYNEEDED,
                                FILLEDBYCODE,
                                JOBID,
                                PRICE,
                                PRICINGSTRUCTURECODE,
                                ADDEDBYID,
                                CHANGEDBYID,
                                DATEADDED,
                                DATECHANGED)
                            select
                                @ID,
                                @ITINERARYID,
                                @VOLUNTEERTYPEID,
                                @QUANTITYNEEDED,
                                @FILLEDBYCODE,
                                @JOBID,
                                COALESCE(VOLUNTEERTYPEPRICING.PRICE, 0),
                                COALESCE(VOLUNTEERTYPEPRICING.PRICINGSTRUCTURECODE,0),
                                @CHANGEAGENTID,
                                @CHANGEAGENTID,
                                @CURRENTDATE,
                                @CURRENTDATE
                            from dbo.VOLUNTEERTYPE
                                left join dbo.VOLUNTEERTYPEPRICING on VOLUNTEERTYPE.ID = VOLUNTEERTYPEPRICING.ID
                            where dbo.VOLUNTEERTYPE.ID = @VOLUNTEERTYPEID

                            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