USP_RESERVATION_RECREATEITINERARIES

CREATE procedure dbo.USP_RESERVATION_RECREATEITINERARIES
(
    @ID uniqueidentifier,
    @OLDARRIVALDATE datetime,
    @RESERVATIONDATE datetime,
    @IGNORECONFLICTS bit = 0,
    @CHANGEAGENTID uniqueidentifier = null,
    @CURRENTDATE datetime = null
)
as
begin

    /* 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 */
    set @CURRENTDATE = getdate();

    declare @ITINERARYATTENDEES table
    (
        ITINERARYID uniqueidentifier,
        ATTENDEEDECIMALCOUNT decimal,
        ATTENDEEINTCOUNT int
    )
    insert into @ITINERARYATTENDEES
    select
        ITINERARY.ID,
        coalesce(sum(ITINERARYATTENDEE.QUANTITY),0),
        coalesce(sum(ITINERARYATTENDEE.QUANTITY),0)
    from dbo.ITINERARY 
    left join dbo.ITINERARYATTENDEE on ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
    where ITINERARY.RESERVATIONID = @ID
    group by ITINERARY.ID

    declare @ITEMS table
    (
        ID uniqueidentifier,
        ITINERARYID uniqueidentifier,
        COPYITINERARYITEMID 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,
        INVALIDREASON tinyint,
        STARTDATETIME datetime,
        ENDDATETIME datetime
    )

    insert into @ITEMS
    (
        ID,
        ITINERARYID,
        COPYITINERARYITEMID,
        EVENTID,
        PROGRAMID,
        NAME,
        NOTES,
        BLOCKEVENT,
        STARTTIME,
        ENDTIME,
        STARTDATE,
        ENDDATE,
        EVENTLOCATIONID,
        ITEMTYPECODE,
        INVALIDREASON
    )
    select
        newid(),
        ITINERARY.ID,
        ITINERARYITEM.ID,
        null,
        case
            when ITINERARYITEM.PROGRAMID is null then EVENT.PROGRAMID
            else ITINERARYITEM.PROGRAMID
        end,
        ITINERARYITEM.NAME,
        ITINERARYITEM.NOTES,
        ITINERARYITEM.BLOCKEVENT,
        ITINERARYITEM.STARTTIME,
        ITINERARYITEM.ENDTIME,
        dateadd(day,datediff(day,@OLDARRIVALDATE,ITINERARYITEM.STARTDATE),@RESERVATIONDATE),
        dateadd(day,datediff(day,@OLDARRIVALDATE,ITINERARYITEM.ENDDATE),@RESERVATIONDATE),
        ITINERARYITEM.EVENTLOCATIONID,
        case ITINERARYITEM.ITEMTYPECODE
            when 3 then
                case 
                    when 
                        ITINERARYITEM.INVALIDREASONCODE = 2 or 
                        ITINERARYITEM.INVALIDREASONCODE = 3 or 
                        ITINERARYITEM.INVALIDREASONCODE = 4 or 
                        ITINERARYITEM.INVALIDREASONCODE = 5 or 
                        ITINERARYITEM.INVALIDREASONCODE = 8 
                        then 0 --Scheduled event

                    when 
                        ITINERARYITEM.INVALIDREASONCODE = 6 or 
                        ITINERARYITEM.INVALIDREASONCODE = 9 or 
                        ITINERARYITEM.INVALIDREASONCODE = 11 then 1 --Daily admission program

                    when 
                        ITINERARYITEM.INVALIDREASONCODE = 1 or 
                        ITINERARYITEM.INVALIDREASONCODE = 10 then --Daily admission or Event or Location

                        case 
                            when ITINERARYITEM.EVENTLOCATIONID is not null or (ITINERARYITEM.PROGRAMID is null and ITINERARYITEM.EVENTID is null) then 2
                            else
                                case
                                    when PROGRAM.ISDAILYADMISSION = 1 then 1
                                    else 0
                                end
                        end
                    else 3
                end
            else ITINERARYITEM.ITEMTYPECODE
        end,
        0
    from dbo.ITINERARYITEM
    inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
    left join dbo.EVENT on EVENT.ID = ITINERARYITEM.EVENTID
    left join dbo.PROGRAM on 
        PROGRAM.ID = ITINERARYITEM.PROGRAMID or
        PROGRAM.ID = EVENT.PROGRAMID
    where ITINERARY.RESERVATIONID = @ID

    update @ITEMS set
        STARTDATETIME = dbo.UFN_DATE_ADDHOURMINUTE([@ITEMS].STARTDATE,[@ITEMS].STARTTIME),
        ENDDATETIME = dbo.UFN_DATE_ADDHOURMINUTE([@ITEMS].ENDDATE,[@ITEMS].ENDTIME)

    declare @VALIDEVENTSFORITEMTIME table
    (
        EVENTID uniqueidentifier,
        ITEMID uniqueidentifier,
        QUANTITY int
    )
    insert into @VALIDEVENTSFORITEMTIME
    select
        EVENT.ID,
        ITEMS.ID,
        0
    from dbo.EVENT
    inner join @ITEMS ITEMS on ITEMS.PROGRAMID = EVENT.PROGRAMID
    where
        (ITEMS.STARTDATETIME >= EVENT.STARTDATETIME and ITEMS.STARTDATETIME < EVENT.ENDDATETIME) or
        (ITEMS.ENDDATETIME > EVENT.STARTDATETIME and ITEMS.ENDDATETIME <= EVENT.ENDDATETIME) or
        (ITEMS.STARTDATETIME < EVENT.STARTDATETIME and ITEMS.ENDDATETIME > EVENT.ENDDATETIME)

    declare @ITINERARYRESOURCES table
    (
        ITINERARYID uniqueidentifier,
        RESOURCEID uniqueidentifier,
        QUANTITYNEEDED int,
        PRICE money,
        PRICINGSTRUCTURECODE tinyint,
        PERTICKETQUANTITY int,
        PERTICKETDIVISOR int
    )

    -- Now add the itinerary resources 

    insert into @ITINERARYRESOURCES
    select 
        [@ITINERARYATTENDEES].ITINERARYID,
        RESOURCE.RESOURCEID,
        RESOURCE.QUANTITYNEEDED,
        COALESCE(RESOURCEPRICING.PRICE, 0),
        COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0),
        PERTICKETQUANTITY,
        PERTICKETDIVISOR
    from dbo.ITINERARYRESOURCE RESOURCE
    inner join @ITINERARYATTENDEES on [@ITINERARYATTENDEES].ITINERARYID = RESOURCE.ITINERARYID
    left join dbo.RESOURCEPRICING on RESOURCE.RESOURCEID = RESOURCEPRICING.ID

    declare @ITINERARYSTAFFRESOURCES table
    (
        ITINERARYID uniqueidentifier,
        VOLUNTEERTYPEID uniqueidentifier,
        QUANTITYNEEDED int,
        FILLEDBYCODE tinyint,
        JOBID uniqueidentifier,
        PRICE money,
        PRICINGSTRUCTURECODE tinyint
    )

    -- Now add the itinerary staff resources 

    insert into @ITINERARYSTAFFRESOURCES
    select 
        [@ITINERARYATTENDEES].ITINERARYID,
        STAFFRESOURCE.VOLUNTEERTYPEID,
        case 
            when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then 
                ceiling([@ITINERARYATTENDEES].ATTENDEEDECIMALCOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE) 
            else STAFFRESOURCE.QUANTITYNEEDED 
        end, 
        STAFFRESOURCE.FILLEDBYCODE,
        STAFFRESOURCE.JOBID,
        COALESCE(VOLUNTEERTYPEPRICING.PRICE, 0),
        COALESCE(VOLUNTEERTYPEPRICING.PRICINGSTRUCTURECODE,0)
    from dbo.ITINERARYSTAFFRESOURCE STAFFRESOURCE
    left join dbo.VOLUNTEERTYPEPRICING on STAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPEPRICING.ID
    inner join dbo.VOLUNTEERTYPE on VOLUNTEERTYPE.ID = STAFFRESOURCE.VOLUNTEERTYPEID
    inner join @ITINERARYATTENDEES on [@ITINERARYATTENDEES].ITINERARYID = STAFFRESOURCE.ITINERARYID

    declare @ITINERARYITEMRESOURCES table
    (
        ITINERARYID uniqueidentifier,
        ITINERARYITEMID uniqueidentifier,
        RESOURCEID uniqueidentifier,
        QUANTITYNEEDED int,
        PRICE money,
        PRICINGSTRUCTURECODE tinyint,
        PERTICKETQUANTITY int,
        PERTICKETDIVISOR int
    )

    insert into @ITINERARYITEMRESOURCES
    select
        ITEMS.ITINERARYID,
        ITEMS.ID,
        ITINERARYITEMRESOURCE.RESOURCEID,
        ITINERARYITEMRESOURCE.QUANTITYNEEDED,
        COALESCE(RESOURCEPRICING.PRICE, 0),
        COALESCE(RESOURCEPRICING.PRICINGSTRUCTURECODE,0),
        PERTICKETQUANTITY,
        PERTICKETDIVISOR
    from @ITEMS as ITEMS
    inner join dbo.ITINERARYITEMRESOURCE on ITEMS.COPYITINERARYITEMID = ITINERARYITEMRESOURCE.ITINERARYITEMID
    left join dbo.RESOURCEPRICING on ITINERARYITEMRESOURCE.RESOURCEID = RESOURCEPRICING.ID;

    declare @ITINERARYITEMSTAFFRESOURCES table
    (
        ITINERARYID uniqueidentifier,
        ITINERARYITEMID uniqueidentifier,
        VOLUNTEERTYPEID uniqueidentifier,
        QUANTITYNEEDED int,
        JOBID uniqueidentifier,
        FILLEDBYCODE tinyint,
        PRICE money,
        PRICINGSTRUCTURECODE tinyint
    )

    -- Now add staff resources

    insert into @ITINERARYITEMSTAFFRESOURCES
    select 
        ITEMS.ITINERARYID,
        ITEMS.ID,
        ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
        case 
            when VOLUNTEERTYPE.CAPACITYPERRESOURCE > 0 then 
                ceiling([@ITINERARYATTENDEES].ATTENDEEDECIMALCOUNT / VOLUNTEERTYPE.CAPACITYPERRESOURCE) 
            else ITINERARYITEMSTAFFRESOURCE.QUANTITYNEEDED 
        end, 
        ITINERARYITEMSTAFFRESOURCE.JOBID,
        ITINERARYITEMSTAFFRESOURCE.FILLEDBYCODE,
        COALESCE(VOLUNTEERTYPEPRICING.PRICE, 0),
        COALESCE(VOLUNTEERTYPEPRICING.PRICINGSTRUCTURECODE,0)
    from @ITEMS as ITEMS
    inner join @ITINERARYATTENDEES on
        ITEMS.ITINERARYID = [@ITINERARYATTENDEES].ITINERARYID
    inner join ITINERARYITEMSTAFFRESOURCE on ITEMS.COPYITINERARYITEMID = ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID
    left join dbo.VOLUNTEERTYPEPRICING on ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPEPRICING.ID
    inner join dbo.VOLUNTEERTYPE on ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID = VOLUNTEERTYPE.ID

    -- Handle removal of locations


    -- First store existing itinerary item locations

    declare @ITINERARYITEMLOCATIONS table
    (
        ITINERARYID uniqueidentifier,
        ITINERARYITEMID uniqueidentifier,
        EVENTLOCATIONID uniqueidentifier,
        PRICE money,
        SALESORDERITEMID uniqueidentifier,
        NAME nvarchar(100)
    )
    insert into @ITINERARYITEMLOCATIONS
    ( 
        ITINERARYID,
        ITINERARYITEMID, 
        EVENTLOCATIONID, 
        PRICE, 
        SALESORDERITEMID, 
        NAME
    )
    select
        ITEMS.ITINERARYID,
        ITEMS.ID,
        ITEMS.EVENTLOCATIONID,
        [SALESORDERITEM].[TOTAL],
        newid(),
        ITEMS.NAME
    from @ITEMS ITEMS
    inner join dbo.ITINERARYITEMLOCATION on
        ITEMS.COPYITINERARYITEMID = ITINERARYITEMLOCATION.ID
    inner join dbo.SALESORDERITEM on
        SALESORDERITEM.ID = ITINERARYITEMLOCATION.SALESORDERITEMID

    -- Now clear all facilities from reservation

    exec dbo.USP_RESERVATION_REMOVEFACILITIES @ID;    

    -- Clear Reservation of all itinerary items

    declare @contextCache varbinary(128);
    declare @e int;

    set @contextCache = CONTEXT_INFO();

    /* set CONTEXT_INFO to @CHANGEAGENTID */
    if not @CHANGEAGENTID is null
        set CONTEXT_INFO @CHANGEAGENTID;

    -- Set ignores capacity so we can skip triggers that would result in errors

    -- We want job occurrences recreated so we're deleting and readding all resources

    update dbo.ITINERARYRESOURCE set
        IGNORESQUANTITYFORCAPACITY = 1,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from @ITINERARYATTENDEES IA
    where IA.ITINERARYID = ITINERARYRESOURCE.ITINERARYID

    update dbo.ITINERARYSTAFFRESOURCE set
        IGNORESQUANTITYFORCAPACITY = 1,
        CHANGEDBYID = @CHANGEAGENTID,
        DATECHANGED = @CURRENTDATE
    from @ITINERARYATTENDEES IA
    where IA.ITINERARYID = ITINERARYSTAFFRESOURCE.ITINERARYID

    delete from dbo.ITINERARYITEM
    from @ITINERARYATTENDEES
    where 
        ITINERARYITEM.ITINERARYID = [@ITINERARYATTENDEES].ITINERARYID

    /* Only delete the resource that are NOT required */
    delete from dbo.ITINERARYRESOURCE
    from @ITINERARYATTENDEES
    where ITINERARYRESOURCE.ITINERARYID = [@ITINERARYATTENDEES].ITINERARYID

    /* Only delete the staffing resource that are NOT required */
    delete from dbo.ITINERARYSTAFFRESOURCE
    from @ITINERARYATTENDEES
    where ITINERARYSTAFFRESOURCE.ITINERARYID = [@ITINERARYATTENDEES].ITINERARYID

    select @e=@@error;

    -- reset CONTEXT_INFO to previous value 

    if not @contextCache is null
        set CONTEXT_INFO @contextCache;

    if @e <> 0
        return 2;

    exec dbo.USP_RESERVATION_UPDATEITEMQUANTITIES @ID, @CHANGEAGENTID;

    declare @START datetime
    declare @END datetime
    declare @SOURCEITINERARYID uniqueidentifier
    declare @QUANTITY int        

    declare ITINERARIES_CURSOR cursor local fast_forward for
    select ITINERARYID, ATTENDEEINTCOUNT from @ITINERARYATTENDEES

    open ITINERARIES_CURSOR;

    fetch next from ITINERARIES_CURSOR
    into @SOURCEITINERARYID, @QUANTITY;

    while @@FETCH_STATUS = 0
    begin        

        update @VALIDEVENTSFORITEMTIME set
            QUANTITY = EVENTAVAILABILITY.QUANTITY
        from dbo.UFN_EVENT_GETAVAILABILITYWITHEVENTS() EVENTAVAILABILITY 
        where
            EVENTAVAILABILITY.EVENTID = [@VALIDEVENTSFORITEMTIME].EVENTID

        -- Get valid events for the date of this itinerary

        update @ITEMS set
            EVENTID = EVENT.ID,
            PROGRAMID = null
        from dbo.EVENT
        inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
        inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
        inner join dbo.UFN_EVENT_GETONSALEINFOWITHEVENTS() EVENTONSALE on
            EVENTONSALE.EVENTID = EVENT.ID
        where
            [@ITEMS].ITINERARYID = @SOURCEITINERARYID and 
            [@ITEMS].ITEMTYPECODE = 0 and
            [@ITEMS].PROGRAMID = PROGRAM.ID and
            PROGRAM.ISACTIVE = 1 and
            EVENT.ISACTIVE = 1 and
            [@ITEMS].ID = VALIDEVENTS.ITEMID and
            VALIDEVENTS.QUANTITY >= @QUANTITY and
            not exists (select 1 from dbo.ITINERARYITEM with (nolock) where ITINERARYITEM.BLOCKEVENT = 1 and ITINERARYITEM.EVENTID = EVENT.ID) and
            not exists
            (
                select 1 from dbo.ITINERARYATTENDEE
                where
                    ITINERARYATTENDEE.ITINERARYID = @SOURCEITINERARYID and
                    not exists
                    (
                        select 1 from dbo.PROGRAMEVENTPRICE 
                        where PROGRAMEVENTPRICE.EVENTID = EVENT.ID and
                        PROGRAMEVENTPRICE.PRICETYPECODEID = ITINERARYATTENDEE.PRICETYPECODEID
                    )
                    and
                    not exists
                    (
                        select 1 from dbo.PROGRAMPRICE 
                        where
                            PROGRAMPRICE.PROGRAMID = PROGRAM.ID and
                            ITINERARYATTENDEE.PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID and
                            not exists 
                            (
                                select 1 from dbo.PROGRAMEVENTPRICE
                                where PROGRAMEVENTPRICE.EVENTID = EVENT.ID
                            )
                    )
            ) and 
            (EVENTONSALE.SALESMETHODTYPECODE = 3 and @CURRENTDATE >= EVENTONSALE.ONSALEDATETIME)

        -- Remove events that do not have a valid time

        update @ITEMS set
            ITEMTYPECODE = 3
        where
            ITINERARYID = @SOURCEITINERARYID and
            ITEMTYPECODE = 0 and
            EVENTID is null

        -- Retrieve reasons why the event is invalid for invalid items

        -- Validate items on all other reasons and remove on condition to narrow

        -- the reason why an event is invalid.

        -- Once we remove a condition, that condition can remain removed

        -- because it is no longer needed to check against whether an item is invalid


        if exists (select 1 from @ITEMS where ITINERARYID = @SOURCEITINERARYID and ITEMTYPECODE = 3)
        begin

            -- No events at the time of the item

            update @ITEMS set
                INVALIDREASON = 5
            where
                [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
                [@ITEMS].ITEMTYPECODE = 3 and
                not exists
                (
                    select 1 from @VALIDEVENTSFORITEMTIME
                    where [@VALIDEVENTSFORITEMTIME].ITEMID = [@ITEMS].ID
                )

            -- Invalid price types, because all other validation passed

            update @ITEMS set
                INVALIDREASON = 4 
            from dbo.EVENT
            inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
            inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
            inner join dbo.UFN_EVENT_GETONSALEINFOWITHEVENTS() EVENTONSALE on
                EVENTONSALE.EVENTID = EVENT.ID
            where
                [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
                [@ITEMS].ITEMTYPECODE = 3 and
                [@ITEMS].INVALIDREASON = 0 and
                [@ITEMS].PROGRAMID = PROGRAM.ID and
                PROGRAM.ISACTIVE = 1 and
                EVENT.ISACTIVE = 1 and
                [@ITEMS].ID = VALIDEVENTS.ITEMID and
                VALIDEVENTS.QUANTITY >= @QUANTITY and    
                not exists (select 1 from dbo.ITINERARYITEM with (nolock) where ITINERARYITEM.BLOCKEVENT = 1 and ITINERARYITEM.EVENTID = EVENT.ID) and 
                (EVENTONSALE.SALESMETHODTYPECODE = 3 and @CURRENTDATE >= EVENTONSALE.ONSALEDATETIME)

            -- Event is blocked, because all other validation passed

            update @ITEMS set
                INVALIDREASON = 3 
            from dbo.EVENT
            inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
            inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
            inner join dbo.UFN_EVENT_GETONSALEINFOWITHEVENTS() EVENTONSALE on
                EVENTONSALE.EVENTID = EVENT.ID
            where
                [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
                [@ITEMS].ITEMTYPECODE = 3 and
                [@ITEMS].INVALIDREASON = 0 and
                [@ITEMS].PROGRAMID = PROGRAM.ID and
                PROGRAM.ISACTIVE = 1 and
                EVENT.ISACTIVE = 1 and        
                [@ITEMS].ID = VALIDEVENTS.ITEMID and
                VALIDEVENTS.QUANTITY >= @QUANTITY and 
                (EVENTONSALE.SALESMETHODTYPECODE = 3 and @CURRENTDATE >= EVENTONSALE.ONSALEDATETIME)

            -- Invalid quantity, because all other validation passed    

            update @ITEMS set
                INVALIDREASON = 2 
            from dbo.EVENT
            inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
            inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
            inner join dbo.UFN_EVENT_GETONSALEINFOWITHEVENTS() EVENTONSALE on
                EVENTONSALE.EVENTID = EVENT.ID
            where
                [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
                [@ITEMS].ITEMTYPECODE = 3 and
                [@ITEMS].INVALIDREASON = 0 and
                [@ITEMS].PROGRAMID = PROGRAM.ID and
                PROGRAM.ISACTIVE = 1 and
                EVENT.ISACTIVE = 1 and        
                [@ITEMS].ID = VALIDEVENTS.ITEMID and
                VALIDEVENTS.QUANTITY < @QUANTITY and 
                (EVENTONSALE.SALESMETHODTYPECODE = 3 and @CURRENTDATE >= EVENTONSALE.ONSALEDATETIME)

            -- Event is not on sale

            update @ITEMS set
                INVALIDREASON = 8 
            from dbo.EVENT
            inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
            inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on VALIDEVENTS.EVENTID = EVENT.ID
            where
                [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
                [@ITEMS].ITEMTYPECODE = 3 and
                [@ITEMS].INVALIDREASON = 0 and
                [@ITEMS].PROGRAMID = PROGRAM.ID and
                PROGRAM.ISACTIVE = 1 and
                EVENT.ISACTIVE = 1 and
                [@ITEMS].ID = VALIDEVENTS.ITEMID

            -- Inactive program/event, because all other validation passed

            update @ITEMS set
                INVALIDREASON = 1 
            from dbo.EVENT
            inner join dbo.PROGRAM on EVENT.PROGRAMID = PROGRAM.ID
            inner join @VALIDEVENTSFORITEMTIME as VALIDEVENTS on
                VALIDEVENTS.EVENTID = EVENT.ID
            where
                [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
                [@ITEMS].ITEMTYPECODE = 3 and
                [@ITEMS].INVALIDREASON = 0 and
                [@ITEMS].PROGRAMID = PROGRAM.ID and
                [@ITEMS].ID = VALIDEVENTS.ITEMID

        end

        -- Remove inactive daily admission programs

        update @ITEMS set
            ITEMTYPECODE = 3,
            INVALIDREASON = 6
        from dbo.PROGRAM
        where
            [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
            [@ITEMS].ITEMTYPECODE = 1 and
            [@ITEMS].PROGRAMID = PROGRAM.ID and
            PROGRAM.ISACTIVE = 0

        -- Remove daily admission programs that are not on sale

        update @ITEMS set
            ITEMTYPECODE = 3,
            INVALIDREASON = 9
        where
            [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
            ITEMTYPECODE = 1 and
            dbo.UFN_PROGRAMDAILYADMISSION_ISONSALE(PROGRAMID,@CURRENTDATE,3) = 0

        -- Remove blocks on items/locations that cannot block

        update @ITEMS set
            BLOCKEVENT = 0
        where
            [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
            (
                (
                    [@ITEMS].BLOCKEVENT = 1 and
                    [@ITEMS].ITEMTYPECODE = 0 and
                    exists (select 1 from dbo.ITINERARYITEM with (nolock) where ITINERARYITEM.EVENTID = [@ITEMS].EVENTID)
                )
                or
                (
                    [@ITEMS].ITEMTYPECODE = 2 and
                    dbo.UFN_ITINERARYITEM_CANBLOCK([@ITEMS].EVENTLOCATIONID,[@ITEMS].STARTDATE,[@ITEMS].ENDDATE,[@ITEMS].STARTTIME,[@ITEMS].ENDTIME) = 0
                )
            )

        -- Remove programs that don't have price types that are in the itinerary

        update @ITEMS set
            ITEMTYPECODE = 3,
            INVALIDREASON = 11
        from dbo.ITINERARYATTENDEE
        where
            [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
            ITINERARYATTENDEE.ITINERARYID = @SOURCEITINERARYID and
            ITEMTYPECODE = 1 and
            not exists
                (select 1 from dbo.PROGRAMPRICE 
                where PROGRAMPRICE.PROGRAMID = [@ITEMS].PROGRAMID and
                ITINERARYATTENDEE.PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID)

        -- Get name for events that are now scheduled

        update @ITEMS set
            NAME = dbo.UFN_TRANSLATIONFUNCTION_EVENT_GETNAME(EVENTID)
        where
            ITINERARYID = @SOURCEITINERARYID and
            (
                ITEMTYPECODE = 0 or
                (ITEMTYPECODE = 3 and EVENTID is not null)
            )

        -- insert new itinerary items

        insert into dbo.ITINERARYITEM
        (
            ID,
            EVENTID,
            PROGRAMID,
            NAME,
            NOTES,
            STARTTIME,
            ENDTIME,
            STARTDATE,
            ENDDATE,
            BLOCKEVENT,
            EVENTLOCATIONID,
            ITEMTYPECODE,
            INVALIDREASONCODE,
            ITINERARYID,
            ADDEDBYID, CHANGEDBYID,    DATEADDED, DATECHANGED
        )
        select
            ID,
            EVENTID,
            PROGRAMID,
            NAME,
            NOTES,
            STARTTIME,
            ENDTIME,
            STARTDATE,
            ENDDATE,
            BLOCKEVENT,
            EVENTLOCATIONID,
            ITEMTYPECODE,
            INVALIDREASON,
            @SOURCEITINERARYID, 
            @CHANGEAGENTID,    @CHANGEAGENTID,    @CURRENTDATE, @CURRENTDATE
        from @ITEMS ITEMS
        where ITEMS.ITINERARYID = @SOURCEITINERARYID

        -- Now add the itinerary resources 

        insert into dbo.ITINERARYRESOURCE
        (
            ITINERARYID,
            RESOURCEID,
            QUANTITYNEEDED,
            PRICE,
            PRICINGSTRUCTURECODE,
            PERTICKETQUANTITY,
            PERTICKETDIVISOR,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select 
            ITINERARYID,
            RESOURCEID,
            QUANTITYNEEDED,
            PRICE,
            PRICINGSTRUCTURECODE,
            PERTICKETQUANTITY,
            PERTICKETDIVISOR,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @ITINERARYRESOURCES
        where ITINERARYID = @SOURCEITINERARYID

        insert into ITINERARYSTAFFRESOURCE
        (
            ITINERARYID,
            VOLUNTEERTYPEID,
            QUANTITYNEEDED,
            FILLEDBYCODE,
            JOBID,
            PRICE,
            PRICINGSTRUCTURECODE,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select 
            ITINERARYID,
            VOLUNTEERTYPEID,
            QUANTITYNEEDED, 
            FILLEDBYCODE,
            JOBID,
            PRICE,
            PRICINGSTRUCTURECODE,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @ITINERARYSTAFFRESOURCES
        where ITINERARYID = @SOURCEITINERARYID

        -- Now add the itinerary item resources

        insert into dbo.ITINERARYITEMRESOURCE
        (
            ITINERARYITEMID,
            RESOURCEID,
            QUANTITYNEEDED,
            PRICE,
            PRICINGSTRUCTURECODE, 
            PERTICKETQUANTITY,
            PERTICKETDIVISOR,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select
            ITINERARYITEMID,
            RESOURCEID,
            QUANTITYNEEDED,
            PRICE,
            PRICINGSTRUCTURECODE,
            PERTICKETQUANTITY,
            PERTICKETDIVISOR,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @ITINERARYITEMRESOURCES
        where ITINERARYID = @SOURCEITINERARYID

        -- Now add staff resources

        insert into dbo.ITINERARYITEMSTAFFRESOURCE
        (
            ITINERARYITEMID,
            VOLUNTEERTYPEID,
            QUANTITYNEEDED,
            JOBID,
            FILLEDBYCODE,
            PRICE,
            PRICINGSTRUCTURECODE,
            ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
        )
        select 
            ITINERARYITEMID,
            VOLUNTEERTYPEID,
            QUANTITYNEEDED,
            JOBID,
            FILLEDBYCODE,
            PRICE,
            PRICINGSTRUCTURECODE,
            @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
        from @ITINERARYITEMSTAFFRESOURCES
        where ITINERARYID = @SOURCEITINERARYID

        -- Doing conflict checking before Update on Itinerary Item table for some deadlock prevention

        delete from @ITEMS
        where
            [@ITEMS].ITINERARYID = @SOURCEITINERARYID and
            not exists
            (
                select 1 from dbo.ITINERARYITEM with (nolock)
                where 
                    ITINERARYITEM.ITINERARYID = @SOURCEITINERARYID and
                    ITINERARYITEM.ID = [@ITEMS].ID and
                    ITINERARYITEM.INVALIDREASONCODE = 0 and
                    dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                    (
                        [@ITEMS].STARTDATETIME, 
                        [@ITEMS].ENDDATETIME, 
                        case 
                            when ITINERARYITEM.EVENTLOCATIONID is null then ''
                            else '<LOCATIONS><ITEM><EVENTLOCATIONID>' + cast(ITINERARYITEM.EVENTLOCATIONID as nvarchar(36)) + '</EVENTLOCATIONID></ITEM></LOCATIONS>'
                        end,
                        dbo.UFN_ITINERARYITEMRESOURCE_GETRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID),
                        dbo.UFN_ITINERARYITEM_GETSTAFFRESOURCES_TOITEMLISTXML(ITINERARYITEM.ID),
                        null,
                        ITINERARYITEM.ITINERARYID,
                        ITINERARYITEM.ID,
                        0, -- Do not ignore super record

                        0, -- Ignore record

                        1, -- Ignore sub record

                        0 -- Do not ignore subrecords of record

                    ) = 1
            )

        update dbo.ITINERARYITEM set 
            INVALIDREASONCODE = 10, -- Invalid reason 10 for conflicts

            ITEMTYPECODE = 3, --Make sure the event is marked as unscheduled

            CHANGEDBYID = @CHANGEAGENTID,
            DATECHANGED = @CURRENTDATE
        from @ITEMS as ITEMS
        where 
            ITINERARYITEM.ITINERARYID = @SOURCEITINERARYID and
            ITEMS.ID = ITINERARYITEM.ID

        -- Insert sales order items for locations that have a price and are not unscheduled

        if (select count(1) from @ITINERARYITEMLOCATIONS where ITINERARYID = @SOURCEITINERARYID) > 0
        begin
            -- Insert sales order items for locations that have a price and are not unscheduled

            delete from @ITINERARYITEMLOCATIONS
            from dbo.ITINERARYITEM
            where
                ITINERARYITEM.ID = [@ITINERARYITEMLOCATIONS].ITINERARYITEMID and
                ITINERARYITEM.ITINERARYID = @SOURCEITINERARYID and
                ITINERARYITEM.ITEMTYPECODE = 3

            insert into dbo.SALESORDERITEM
            (
                ID, 
                SALESORDERID, 
                TYPECODE, 
                DESCRIPTION, 
                QUANTITY, 
                FLATRATEPRICE, 
                PRICINGSTRUCTURECODE, 
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                SALESORDERITEMID, 
                @ID, 
                7, 
                LOCATIONS.NAME + ' - ' + coalesce(EVENTLOCATION.NAME, ''), 
                1, 
                LOCATIONS.PRICE,
                2,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from @ITINERARYITEMLOCATIONS LOCATIONS
            inner join dbo.EVENTLOCATION on
                LOCATIONS.EVENTLOCATIONID = EVENTLOCATION.ID
            where
                LOCATIONS.ITINERARYID = @SOURCEITINERARYID

            insert into dbo.SALESORDERITEMFACILITY
            (
                ID, 
                EVENTLOCATIONID, 
                EVENTLOCATIONNAME,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                [@ITINERARYITEMLOCATIONS].SALESORDERITEMID, 
                [@ITINERARYITEMLOCATIONS].EVENTLOCATIONID,
                EVENTLOCATION.NAME,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from @ITINERARYITEMLOCATIONS
            inner join dbo.EVENTLOCATION on
                [@ITINERARYITEMLOCATIONS].EVENTLOCATIONID = EVENTLOCATION.ID
            where
                ITINERARYID = @SOURCEITINERARYID

            insert into dbo.ITINERARYITEMLOCATION
            (
                ID,
                SALESORDERITEMID, 
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                ITINERARYITEMID,
                SALESORDERITEMID,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from @ITINERARYITEMLOCATIONS
            where
                ITINERARYID = @SOURCEITINERARYID
        end

        -- Now remove remaining items with resource conflicts    

        delete from @ITEMS
        where [@ITEMS].ITINERARYID = @SOURCEITINERARYID

        -- update Sales order items for the resources

        exec dbo.USP_ITINERARY_RESOURCES_SALESORDERSYNC @SOURCEITINERARYID, @ID, @CHANGEAGENTID
        exec dbo.USP_ITINERARY_STAFFRESOURCES_SALESORDERSYNC @SOURCEITINERARYID, @ID, @CHANGEAGENTID

        -- call the bulk versions to update all itinerary item records

        exec dbo.USP_ITINERARYITEMS_RESOURCES_SALESORDERSYNC @SOURCEITINERARYID, @ID, @CHANGEAGENTID
        exec dbo.USP_ITINERARYITEMS_STAFFRESOURCES_SALESORDERSYNC @SOURCEITINERARYID, @ID, @CHANGEAGENTID

        -- update Sales order Items for Itinerary items (Includes taxes for resource line items, flat rate calculations, etc)

        exec dbo.USP_ITINERARY_UPDATEITEMQUANTITIES @SOURCEITINERARYID, @CHANGEAGENTID, @CURRENTDATE;

        if @IGNORECONFLICTS = 0 and exists (select 1 from dbo.ITINERARYITEM as ITEMS where ITEMS.ITINERARYID = @SOURCEITINERARYID)
        begin
            select
                @START = STARTDATETIME,
                @END = ENDDATETIME
            from dbo.ITINERARY
            where ID = @SOURCEITINERARYID

            if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
            (
                @START, @END, 
                null, 
                dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(@SOURCEITINERARYID), 
                dbo.UFN_ITINERARY_GETSTAFFRESOURCES_TOITEMLISTXML(@SOURCEITINERARYID), 
                null, @SOURCEITINERARYID, null,
                0,
                1, -- Ignore itinerary resources

                0,
                0
            ) = 1 
            begin
                raiserror('BBERR_CONFLICTSEXIST', 13, 1);
                return 1;
            end    
        end

        fetch next from ITINERARIES_CURSOR
        into @SOURCEITINERARYID, @QUANTITY;
    end

    close ITINERARIES_CURSOR;
    deallocate ITINERARIES_CURSOR;    

end