USP_ITINERARYITEM_STAFFRESOURCES_SALESORDERSYNC

        CREATE procedure dbo.USP_ITINERARYITEM_STAFFRESOURCES_SALESORDERSYNC
        (
            @ITINERARYITEMID uniqueidentifier,
            @SALESORDERID uniqueidentifier = null,
            @CHANGEAGENTID uniqueidentifier = null
        )
        as
        begin
            set nocount on;

            if @SALESORDERID is null
                select @SALESORDERID = ITINERARY.RESERVATIONID
                from dbo.ITINERARY
                inner join dbo.ITINERARYITEM on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                where ITINERARYITEM.ID = @ITINERARYITEMID

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

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

            declare @STARTDATETIME as datetime;
            declare @ENDDATETIME as datetime;

            select
                @STARTDATETIME = STARTDATETIME,
                @ENDDATETIME = ENDDATETIME
            from dbo.ITINERARYITEM
            where ID = @ITINERARYITEMID

            declare @DURATION as decimal(20,4)
            if @ENDDATETIME is null or @STARTDATETIME is null
                set @DURATION = 0
            else
                set @DURATION = datediff(s, @STARTDATETIME, @ENDDATETIME) / (3600.0)

            -- We only need to worry about updates and deletes here as the database integrity requires that there

            -- already are no sales order items which are no longer needed.


            declare @ORDERITEMS table
            (
                ID uniqueidentifier,
                ITINERARYITEMSTAFFRESOURCEID uniqueidentifier,
                DESCRIPTION nvarchar(255),
                QUANTITY decimal(20,4),
                PRICE money,
                PRICINGSTRUCTURECODE tinyint
            );

            insert into @ORDERITEMS
            (
                ID,
                ITINERARYITEMSTAFFRESOURCEID,
                DESCRIPTION,
                QUANTITY,
                PRICE,
                PRICINGSTRUCTURECODE
            )
            select
                SALESORDERITEM.ID as ID,
                ITINERARYITEMSTAFFRESOURCE.ID as ITINERARYITEMSTAFFRESOURCEID,
                ITINERARYITEM.NAME + ' - ' + VOLUNTEERTYPE.NAME as DESCRIPTION,
                case ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
                    when 2 then
                        @DURATION * ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED
                    else
                        ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED
                end as QUANTITY,
                case ITINERARYITEMSTAFFRESOURCE.PRICINGSTRUCTURECODE
                    when 0 then 0
                    else ITINERARYITEMSTAFFRESOURCE.PRICE
                end as PRICE,
                case RESERVATION.PRICINGCODE
                    when 1 then
                        case COALESCE(RESERVATIONRATESCALE.INCLUDEALLSTAFFRESOURCES, 0)
                            when 1 then 1
                            else
                                case when RESERVATIONRATESCALESTAFFRESOURCE.ID is null
                                    then 0
                                    else 1
                                end
                        end
                    else
                        case coalesce(SALESORDERITEM.PRICINGSTRUCTURECODE, 0)
                            when 2 then 2
                            else 0
                        end
                end as PRICINGSTRUCTURECODE
            from dbo.ITINERARYITEMSTAFFRESOURCE
            inner join dbo.VOLUNTEERTYPE on 
                ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID
            inner join dbo.ITINERARYITEM on 
                ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
            inner join dbo.ITINERARY on 
                ITINERARYITEM.ITINERARYID = ITINERARY.ID
            inner join dbo.RESERVATION on 
                ITINERARY.RESERVATIONID = RESERVATION.ID
            left join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE on 
                ITINERARYITEMSTAFFRESOURCE.ID = SALESORDERITEMITINERARYITEMSTAFFRESOURCE.ITINERARYITEMSTAFFRESOURCEID
            left join dbo.SALESORDERITEM on 
                SALESORDERITEMITINERARYITEMSTAFFRESOURCE.SALESORDERITEMID = SALESORDERITEM.ID
            left join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
            left join dbo.RESERVATIONRATESCALESTAFFRESOURCE on RESERVATIONRATESCALE.ID = RESERVATIONRATESCALESTAFFRESOURCE.RESERVATIONRATESCALEID and ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = RESERVATIONRATESCALESTAFFRESOURCE.VOLUNTEERTYPEID
            where 
                ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = @ITINERARYITEMID and
                ITINERARYITEM.INVALIDREASONCODE = 0

            update dbo.SALESORDERITEM set 
                SALESORDERITEM.DESCRIPTION = UPDATEVALUES.DESCRIPTION,
                SALESORDERITEM.QUANTITY = UPDATEVALUES.QUANTITY,
                SALESORDERITEM.PRICE = UPDATEVALUES.PRICE,
                SALESORDERITEM.PRICINGSTRUCTURECODE = UPDATEVALUES.PRICINGSTRUCTURECODE,
                SALESORDERITEM.CHANGEDBYID = @CHANGEAGENTID,
                SALESORDERITEM.DATECHANGED = @CURRENTDATE
            from @ORDERITEMS as UPDATEVALUES
            where
                SALESORDERITEM.ID = UPDATEVALUES.ID and
                (
                    SALESORDERITEM.DESCRIPTION <> UPDATEVALUES.DESCRIPTION or
                    SALESORDERITEM.QUANTITY <> UPDATEVALUES.QUANTITY or
                    SALESORDERITEM.PRICE <> UPDATEVALUES.PRICE or
                    SALESORDERITEM.PRICINGSTRUCTURECODE <> UPDATEVALUES.PRICINGSTRUCTURECODE
                )

            delete from @ORDERITEMS
            where ID is not null

            update @ORDERITEMS set ID = newid()

            insert into dbo.SALESORDERITEM
            (
                ID,
                SALESORDERID,
                TYPECODE,
                DESCRIPTION,
                QUANTITY,
                PRICE,
                PRICINGSTRUCTURECODE,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select 
                ORDERITEMS.ID,
                @SALESORDERID,
                11, --Itinerary Item Staffing Resource 

                ORDERITEMS.DESCRIPTION, 
                ORDERITEMS.QUANTITY, 
                ORDERITEMS.PRICE,
                ORDERITEMS.PRICINGSTRUCTURECODE,
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @ORDERITEMS ORDERITEMS

            insert into dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE
            (
                SALESORDERITEMID,
                ITINERARYITEMSTAFFRESOURCEID,
                VOLUNTEERTYPENAME,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select 
                ORDERITEMS.ID,
                ORDERITEMS.ITINERARYITEMSTAFFRESOURCEID,
                coalesce((
                    select [VOLUNTEERTYPE].[NAME]
                    from dbo.[ITINERARYITEMSTAFFRESOURCE] 
                    inner join dbo.[VOLUNTEERTYPE]
                        on [ITINERARYITEMSTAFFRESOURCE].[VOLUNTEERTYPEID] = [VOLUNTEERTYPE].[ID]
                    where [ITINERARYITEMSTAFFRESOURCE].[ID] = [ORDERITEMS].[ITINERARYITEMSTAFFRESOURCEID]
                ),''),
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @ORDERITEMS ORDERITEMS
        end