USP_ITINERARY_RESOURCES_SALESORDERSYNC

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

            -- If we aren't given anything to sync, just abort.

            if @ITINERARYID is null and @SALESORDERID is null
                return 0;

            if @SALESORDERID is null
                select @SALESORDERID = RESERVATIONID from dbo.ITINERARY where ID = @ITINERARYID;

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

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

            -- 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,
                ITINERARYRESOURCEID uniqueidentifier,
                DESCRIPTION nvarchar(255),
                QUANTITY decimal(20,4),
                PRICE money,
                PRICINGSTRUCTURECODE tinyint
            );    

            insert into @ORDERITEMS
            (
                ID,
                ITINERARYRESOURCEID,
                DESCRIPTION,
                QUANTITY,
                PRICE,
                PRICINGSTRUCTURECODE
            )
            select
                SALESORDERITEM.ID as ID,
                ITINERARYRESOURCE.ID as ITINERARYRESOURCEID,
                RESOURCE.NAME as DESCRIPTION,
                case RESOURCE.ISPERTICKETITEM
                    -- Not a per ticket quantity item

                    when 0 then 
                        case ITINERARYRESOURCE.PRICINGSTRUCTURECODE
                            -- Hourly rate per resources

                            when 2 then
                                (
                                    coalesce
                                    (
                                        datediff
                                        (
                                            s, 
                                            dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYRESOURCE.ITINERARYID), 
                                            dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYRESOURCE.ITINERARYID)
                                        ),0
                                    ) / (3600.0)
                                )
                                * ITINERARYRESOURCE.QUANTITYNEEDED
                            else ITINERARYRESOURCE.QUANTITYNEEDED
                        end
                    -- Per ticket quantity item

                    else
                        dbo.UFN_RESOURCE_CALCULATEPERTICKETQUANTITY_2
                        (
                            (
                                coalesce
                                (
                                    (
                                        select sum(ITINERARYATTENDEE.QUANTITY) from dbo.ITINERARYATTENDEE 
                                        where ITINERARYATTENDEE.ITINERARYID = ITINERARYRESOURCE.ITINERARYID
                                    ),0
                                ) * ITINERARYRESOURCE.PERTICKETQUANTITY
                            ), 
                            ITINERARYRESOURCE.PERTICKETDIVISOR
                        ) *
                        case ITINERARYRESOURCE.PRICINGSTRUCTURECODE
                            -- Hourly rate per resources

                            when 2 then 
                            (
                                coalesce
                                (
                                    datediff
                                    (
                                        s, 
                                        dbo.UFN_ITINERARY_STARTDATETIME(ITINERARYRESOURCE.ITINERARYID), 
                                        dbo.UFN_ITINERARY_ENDDATETIME(ITINERARYRESOURCE.ITINERARYID)
                                    ),0
                                ) / (3600.0)
                            )
                            else 1
                        end
                end as QUANTITY,
                case ITINERARYRESOURCE.PRICINGSTRUCTURECODE
                    when 0 then 0
                    else ITINERARYRESOURCE.PRICE
                end as PRICE,
                case RESERVATION.PRICINGCODE
                    when 1 then
                        case coalesce(SALESORDERITEM.PRICINGSTRUCTURECODE,0)
                            when 2 then 2
                            else 
                                case coalesce(RESERVATIONRATESCALE.INCLUDEALLRESOURCES, 0)
                                    when 1 then 1
                                    else
                                        case 
                                            when RESERVATIONRATESCALERESOURCE.ID is null then 0
                                            else 1
                                        end
                                end
                        end        
                    else
                        case coalesce(SALESORDERITEM.PRICINGSTRUCTURECODE,0)
                            when 2 then 2
                            else 0
                        end
                end as PRICINGSTRUCTURECODE
            from dbo.ITINERARYRESOURCE
            inner join dbo.RESOURCE on ITINERARYRESOURCE.RESOURCEID = RESOURCE.ID
            inner join dbo.ITINERARY on ITINERARYRESOURCE.ITINERARYID = ITINERARY.ID
            inner join dbo.RESERVATION on ITINERARY.RESERVATIONID = RESERVATION.ID
            left join dbo.SALESORDERITEMITINERARYRESOURCE on 
                ITINERARYRESOURCE.ID = SALESORDERITEMITINERARYRESOURCE.ITINERARYRESOURCEID
            left join dbo.SALESORDERITEM on 
                SALESORDERITEMITINERARYRESOURCE.SALESORDERITEMID = SALESORDERITEM.ID
            left join dbo.RESERVATIONRATESCALE on RESERVATION.ID = RESERVATIONRATESCALE.ID
            left join dbo.RESERVATIONRATESCALERESOURCE on 
                RESERVATIONRATESCALE.ID = RESERVATIONRATESCALERESOURCE.RESERVATIONRATESCALEID and 
                RESOURCE.ID = RESERVATIONRATESCALERESOURCE.RESOURCEID
            where 
                RESERVATION.ID = @SALESORDERID and
                (
                    @ITINERARYID is null or 
                    ITINERARYRESOURCE.ITINERARYID = @ITINERARYID
                )

            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,
                8, --Itinerary Supply/Equipment Resource 

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

            insert into dbo.SALESORDERITEMITINERARYRESOURCE
            (
                SALESORDERITEMID,
                ITINERARYRESOURCEID,
                RESOURCENAME,
                RESOURCECATEGORYNAME,
                ADDEDBYID,
                CHANGEDBYID,
                DATEADDED,
                DATECHANGED
            )
            select 
                ORDERITEMS.ID,
                ORDERITEMS.ITINERARYRESOURCEID,
                coalesce((
                    select [RESOURCE].[NAME]
                    from dbo.[ITINERARYRESOURCE]
                    inner join dbo.[RESOURCE]
                        on [ITINERARYRESOURCE].[RESOURCEID] = [RESOURCE].[ID]
                    where [ITINERARYRESOURCE].[ID] = [ORDERITEMS].[ITINERARYRESOURCEID]
                ),''),
                coalesce((
                    select [RESOURCECATEGORYCODE].[DESCRIPTION]
                    from dbo.[ITINERARYRESOURCE]
                    inner join dbo.[RESOURCE]
                        on [ITINERARYRESOURCE].[RESOURCEID] = [RESOURCE].[ID]
                    inner join dbo.[RESOURCECATEGORYCODE]
                        on [RESOURCE].[RESOURCECATEGORYCODEID] = [RESOURCECATEGORYCODE].[ID]
                    where [ITINERARYRESOURCE].[ID] = [ORDERITEMS].[ITINERARYRESOURCEID]
                ),''),
                @CHANGEAGENTID, 
                @CHANGEAGENTID, 
                @CURRENTDATE, 
                @CURRENTDATE
            from @ORDERITEMS ORDERITEMS
        end