USP_TRACK_APPLYTRACKTOITINERARY

        CREATE procedure dbo.USP_TRACK_APPLYTRACKTOITINERARY
        (
            @ID uniqueidentifier,
            @TRACKID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier
        )
        as
        begin
            declare @RESERVATIONDATE datetime
            declare @RESERVATIONID uniqueidentifier

            select
                @RESERVATIONDATE = ARRIVALDATE,
                @RESERVATIONID = RESERVATION.ID
            from dbo.RESERVATION
            inner join dbo.ITINERARY on
                ITINERARY.RESERVATIONID = RESERVATION.ID
            where
                ITINERARY.ID = @ID


            /* Fix up the change agent ID incase we were passed null. */
            if @CHANGEAGENTID is null
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output        

            /* Setup the current date */
            declare @CURRENTDATE datetime;
            set @CURRENTDATE = getdate();

            /* First clear the itinerary */
            exec dbo.USP_ITINERARY_CLEAR @ID, @RESERVATIONID, @CHANGEAGENTID;

            /* Update existing required resources with the max of the existing qty and the new track qty */
            update dbo.ITINERARYRESOURCE set 
                QUANTITYNEEDED = case 
                                    when (TRACKRESOURCE.QUANTITYNEEDED > ITINERARYRESOURCE.QUANTITYNEEDED) then 
                                        TRACKRESOURCE.QUANTITYNEEDED
                                    else ITINERARYRESOURCE.QUANTITYNEEDED
                                end, 
                PRICE = COALESCE(RESOURCEPRICING.PRICE, 0),
                PRICINGSTRUCTURECODE = COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0),
                CHANGEDBYID = @CHANGEAGENTID, 
                DATECHANGED = @CURRENTDATE
            from dbo.ITINERARYRESOURCE
            inner join dbo.TRACKRESOURCE on ITINERARYRESOURCE.RESOURCEID = TRACKRESOURCE.RESOURCEID
            left join dbo.RESOURCEPRICING on TRACKRESOURCE.RESOURCEID = RESOURCEPRICING.ID
            where 
                TRACKRESOURCE.TRACKID = @TRACKID and 
                ITINERARYRESOURCE.ITINERARYID = @ID

            /* Now add the new itinerary resources */
            insert into dbo.ITINERARYRESOURCE
            (
                ITINERARYID, 
                RESOURCEID, 
                QUANTITYNEEDED, 
                PRICE,
                PRICINGSTRUCTURECODE,
                PERTICKETQUANTITY,
                PERTICKETDIVISOR,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select 
                @ID, 
                TRACKRESOURCE.RESOURCEID, 
                TRACKRESOURCE.QUANTITYNEEDED,
                COALESCE(RESOURCEPRICING.PRICE, 0),
                COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0),
                RESOURCE.PERTICKETQUANTITY,
                RESOURCE.PERTICKETDIVISOR,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from dbo.TRACKRESOURCE
                inner join RESOURCE on TRACKRESOURCE.RESOURCEID = RESOURCE.ID
                left join dbo.RESOURCEPRICING on TRACKRESOURCE.RESOURCEID = RESOURCEPRICING.ID
                left join dbo.ITINERARYRESOURCE EXISTING_ITINERARYRESOURCE on EXISTING_ITINERARYRESOURCE.RESOURCEID = TRACKRESOURCE.RESOURCEID and EXISTING_ITINERARYRESOURCE.ITINERARYID = @ID
            where 
                TRACKRESOURCE.TRACKID = @TRACKID and 
                EXISTING_ITINERARYRESOURCE.ID is null

            /* Now add the new itinerary staff resources */

            /* For these we have to preprocess them to create jobs.  So first get the track version. */
            declare @STAFFRESOURCES xml;
            set @STAFFRESOURCES = dbo.UFN_TRACK_GETSTAFFRESOURCES_TOITEMLISTXML(@TRACKID);

            /* Get the total attendees for the itinerary */
            declare @ATTENDEECOUNT decimal;
            select @ATTENDEECOUNT = sum(ITINERARYATTENDEE.QUANTITY)
            from dbo.ITINERARYATTENDEE
            where ITINERARYATTENDEE.ITINERARYID = @ID;

            /* Process the staff resources and add them to the itinerary */
            exec dbo.USP_TRACK_STAFFRESOURCES_PROCESS @STAFFRESOURCES output, @CHANGEAGENTID, @ATTENDEECOUNT;

            /* For everything to work, we need to integrate the existing itinerary staffing resources after jobs are configured so we can match up */
            set @STAFFRESOURCES = 
            (
                select 
                    [FILLEDBYCODE], 
                    [ID], 
                    [JOBID], 
                    [QUANTITYNEEDED], 
                    [VOLUNTEERTYPEID], 
                    [PRICE], 
                    [PRICINGSTRUCTURECODE]
                from 
                (
                    select 
                        T.c.value('(FILLEDBYCODE)[1]','tinyint') AS 'FILLEDBYCODE',
                        COALESCE(ITINERARYSTAFFRESOURCE.ID, T.c.value('(ID)[1]','uniqueidentifier')) AS 'ID',
                        T.c.value('(JOBID)[1]','uniqueidentifier') AS 'JOBID',
                        case 
                            when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then 
                                ceiling(@ATTENDEECOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE)
                            else
                                case when ITINERARYSTAFFRESOURCE.QUANTITYNEEDED > T.c.value('(QUANTITYNEEDED)[1]','integer') then
                                    ITINERARYSTAFFRESOURCE.QUANTITYNEEDED
                                else
                                    T.c.value('(QUANTITYNEEDED)[1]','integer')
                                end
                        end as 'QUANTITYNEEDED',
                        T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') AS 'VOLUNTEERTYPEID',
                        T.c.value('(PRICE)[1]','money') AS 'PRICE',
                        T.c.value('(PRICINGSTRUCTURECODE)[1]','tinyint') AS 'PRICINGSTRUCTURECODE'
                    from @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c)
                    inner join dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier')
                    left join dbo.VOLUNTEERTYPEPRICING on VOLUNTEERTYPE.ID = VOLUNTEERTYPEPRICING.ID
                    left join ITINERARYSTAFFRESOURCE on 
                        ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') and 
                        (
                            (ITINERARYSTAFFRESOURCE.JOBID is null and T.c.value('(JOBID)[1]','uniqueidentifier') is null) or 
                            ITINERARYSTAFFRESOURCE.JOBID = T.c.value('(JOBID)[1]','uniqueidentifier')
                        ) and 
                        ITINERARYSTAFFRESOURCE.ITINERARYID = @ID

                    union all

                    select 
                        FILLEDBYCODE, ID, JOBID, QUANTITYNEEDED, VOLUNTEERTYPEID, PRICE, PRICINGSTRUCTURECODE
                    from ITINERARYSTAFFRESOURCE
                    left join @STAFFRESOURCES.nodes('/STAFFRESOURCES/ITEM') T(c) on 
                        ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID = T.c.value('(VOLUNTEERTYPEID)[1]','uniqueidentifier') and 
                        (
                            (ITINERARYSTAFFRESOURCE.JOBID is null and T.c.value('(JOBID)[1]','uniqueidentifier') is null) or 
                            ITINERARYSTAFFRESOURCE.JOBID = T.c.value('(JOBID)[1]','uniqueidentifier')
                        )
                    where ITINERARYSTAFFRESOURCE.ITINERARYID = @id
                        and T.c.value('(ID)[1]','uniqueidentifier') is null
                ) S
                for xml raw('ITEM'),type,elements,root('STAFFRESOURCES'),BINARY BASE64
            )

            exec dbo.USP_ITINERARY_GETSTAFFRESOURCES_UPDATEFROMXML @ID, @STAFFRESOURCES, @CHANGEAGENTID, @CURRENTDATE;

            /* Now start working on inserting the itinerary items */
            declare @ITEMS table
            (
                TRACKITEMID uniqueidentifier,
                EVENTID uniqueidentifier,
                PROGRAMID uniqueidentifier,
                NAME nvarchar(100),
                NOTES nvarchar(500),
                BLOCKEVENT bit,
                STARTTIME dbo.UDT_HOURMINUTE,
                ENDTIME dbo.UDT_HOURMINUTE,
                STARTDATE datetime,
                ENDDATE datetime,
                EVENTLOCATIONID uniqueidentifier,
                ITEMTYPECODE tinyint
            )

            insert into @ITEMS
            select
                ID,
                null,
                PROGRAMID,
                NAME,
                NOTES,
                BLOCKEVENT,
                STARTTIME,
                ENDTIME,
                dateadd(day,(STARTDAY),@RESERVATIONDATE),
                dateadd(day,(ENDDAY),@RESERVATIONDATE),
                EVENTLOCATIONID,
                TYPECODE
            from dbo.TRACKITEM
            where TRACKID = @TRACKID

            declare @ITINERARYITEMS xml

            set @ITINERARYITEMS = 
                (
                    select
                        TRACKITEMID,
                        EVENTID,
                        PROGRAMID,
                        NAME,
                        NOTES,
                        BLOCKEVENT,
                        STARTTIME,
                        ENDTIME,
                        STARTDATE,
                        ENDDATE,
                        EVENTLOCATIONID,
                        ITEMTYPECODE
                    from @ITEMS
                    for xml raw ('ITEM'), type, elements, root('ITINERARYITEMS'), BINARY BASE64
                )

            exec dbo.USP_ITINERARY_INSERTITEMS @ID, @ITINERARYITEMS, @CHANGEAGENTID, 0;
        end