USP_ITINERARYITEMEVENT_ADD

        CREATE procedure dbo.USP_ITINERARYITEMEVENT_ADD
        (
            @ID uniqueidentifier = null output,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null,
            @ITINERARYID uniqueidentifier,
            @EVENTID uniqueidentifier = null,
            @ITINERARYITEMSTARTTIME dbo.UDT_HOURMINUTE = null,
            @ITINERARYITEMENDTIME dbo.UDT_HOURMINUTE = null,
            @ITINERARYITEMSTARTDATE datetime = null,
            @ITINERARYITEMENDDATE datetime = null,
            @BLOCKEVENT bit = 0,
            @NOTES nvarchar(500) = '',
            @RESOURCES xml = null,
            @IGNORECONFLICTS bit = 0,
            @STAFFRESOURCES xml = null
        )
        as
        begin
            set nocount on;

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

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

            if @CURRENTDATE is null
                set @CURRENTDATE = getdate();

            declare @ORDERID uniqueidentifier;
            declare @SALESMETHODID uniqueidentifier;
            declare @ARRIVALDATE datetime;
            declare @MAXDATE datetime;
            declare @ERRORMESSAGE nvarchar(500);
            declare @ITEMSTARTDATETIME datetime;
            declare @ITEMENDDATETIME datetime;
            declare @ISFLATRATE bit = 0;
            declare @STATUSCODE tinyint;

            set @ITEMSTARTDATETIME = dbo.UFN_DATE_ADDHOURMINUTE(dbo.UFN_DATE_GETEARLIESTTIME(@ITINERARYITEMSTARTDATE),@ITINERARYITEMSTARTTIME)
            set @ITEMENDDATETIME = dbo.UFN_DATE_ADDHOURMINUTE(dbo.UFN_DATE_GETEARLIESTTIME(@ITINERARYITEMENDDATE),@ITINERARYITEMENDTIME)

            if @IGNORECONFLICTS = 0
            begin
                declare @OLDITINERARYSTART datetime;
                declare @OLDITINERARYEND datetime;
                declare @ITINERARYSTART datetime;
                declare @ITINERARYEND datetime;

                declare @CHECKITINERARYCONFLICTS bit = 0;

                select
                    @OLDITINERARYSTART = ITINERARY.STARTDATETIME,
                    @OLDITINERARYEND   = ITINERARY.ENDDATETIME
                from dbo.ITINERARY
                where ID = @ITINERARYID

                select
                    @ITINERARYSTART = min(STARTDATETIME),
                    @ITINERARYEND = max(ENDDATETIME)
                from
                (
                    select 
                        @ITEMSTARTDATETIME as STARTDATETIME,
                        @ITEMENDDATETIME as ENDDATETIME

                    union all

                    select 
                        ITINERARYITEM.STARTDATETIME as STARTDATETIME,
                        ITINERARYITEM.ENDDATETIME as ENDDATETIME
                    from dbo.ITINERARYITEM
                    where 
                        ITINERARYID = @ITINERARYID and
                        ID <> @ID
                ) T

                if @OLDITINERARYSTART is null or @OLDITINERARYSTART <> @ITINERARYSTART or
                    @OLDITINERARYEND is null or @OLDITINERARYEND <> @ITINERARYEND
                    set @CHECKITINERARYCONFLICTS = 1

                if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                (
                    @ITEMSTARTDATETIME, @ITEMENDDATETIME, 
                    null, @RESOURCES, null,
                    null, @ITINERARYID, @ID,
                    0, -- Ignore Super Record 

                    0, -- Ignore Record

                    1, -- Ignore Sub Record

                    0 -- Ignore All Sub Records of Record

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

                if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                (
                    @ITEMSTARTDATETIME, @ITEMENDDATETIME, 
                    null, null, @STAFFRESOURCES,
                    null, @ITINERARYID, @ID,
                    0, -- Ignore Super Record 

                    0, -- Ignore Record

                    1, -- Ignore Sub Record

                    0 -- Ignore All Sub Records of Record

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

                -- If itinerary times changing, need to check on conflicts

                if @CHECKITINERARYCONFLICTS = 1
                begin

                    declare @ITINERARYRESOURCES xml
                    set @ITINERARYRESOURCES = dbo.UFN_ITINERARYRESOURCE_GETRESOURCES_TOITEMLISTXML(@ITINERARYID);

                    declare @ITINERARYSTAFFRESOURCES xml
                    set @ITINERARYSTAFFRESOURCES = dbo.UFN_ITINERARY_GETSTAFFRESOURCES_TOITEMLISTXML(@ITINERARYID);

                    if dbo.UFN_CONFLICTCHECK_CONFLICTSEXIST
                    (
                        @ITINERARYSTART, @ITINERARYEND, 
                        null, @ITINERARYRESOURCES, @ITINERARYSTAFFRESOURCES,
                        null, @ITINERARYID, @ID,
                        0, -- Ignore Super Record 

                        1, -- Ignore Record

                        1, -- Ignore Sub Record

                        0 -- Ignore All Sub Records of Record

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

            select
                @ORDERID = RESERVATION.ID,
                @ARRIVALDATE = RESERVATION.ARRIVALDATE,
                @ISFLATRATE = case when PRICINGCODE = 1 then 1 else 0 end,
                @STATUSCODE = SALESORDER.STATUSCODE
            from dbo.RESERVATION
                inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = RESERVATION.ID
                inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
            where ITINERARY.ID = @ITINERARYID

            if @STATUSCODE in (1, 5)
                raiserror('BBERR_INVALIDSTATUS', 13, 1);

            set @MAXDATE = dateadd(d, 4, dbo.UFN_DATE_GETLATESTTIME(@ARRIVALDATE))

            if @ITEMSTARTDATETIME > @MAXDATE or @ITEMENDDATETIME > @MAXDATE
                raiserror('BBERR_OUTSIDEDATERANGE', 13, 1)

            select @SALESMETHODID = SALESMETHOD.ID
            from dbo.SALESMETHOD
            where SALESMETHOD.TYPECODE = 3

            -- Need to check location block from custom item, also.

            if exists (
                select 1 from dbo.ITINERARYITEM 
                inner join dbo.ITINERARY on ITINERARY.ID = ITINERARYITEM.ITINERARYID
                inner join dbo.RESERVATION on RESERVATION.ID = ITINERARY.RESERVATIONID
                inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
                where 
                    ITEMTYPECODE = 0 
                    and BLOCKEVENT = 1 
                    and EVENTID = @EVENTID 
                    and @ORDERID <> ITINERARY.RESERVATIONID
                    and SALESORDER.STATUSCODE <> 5
            )
                raiserror('BBERR_EVENTEXISTSWITHBLOCK', 13, 1);

            if @BLOCKEVENT = 1 
            begin
                -- CR63788 MDC - Block event changes should apply globally to the reservation.

                -- Do not block events in the same reservation.

                if exists (
                    select 1 from dbo.ITINERARYITEM 
                    inner join dbo.ITINERARY on ITINERARYITEM.ITINERARYID = ITINERARY.ID
                    inner join dbo.RESERVATION on RESERVATION.ID = ITINERARY.RESERVATIONID
                    inner join dbo.SALESORDER on SALESORDER.ID = RESERVATION.ID
                    where ITEMTYPECODE = 0 
                        and EVENTID = @EVENTID 
                        and ITINERARY.RESERVATIONID <> @ORDERID
                        and SALESORDER.STATUSCODE <> 5
                )
                    raiserror('BBERR_CANNOTBLOCK', 13, 1);
            end

            update dbo.ITINERARYITEM
                set BLOCKEVENT = @BLOCKEVENT,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
            where 
                @EVENTID = EVENTID and 
                ITINERARYID in 
                (
                    select ID from dbo.ITINERARY
                    where ITINERARY.RESERVATIONID = @ORDERID
                ) and 
                ITEMTYPECODE = 0


            declare @ORDERITEMS table
            (
                ID uniqueidentifier,
                QUANTITY decimal(20,4),
                PRICETYPECODEID uniqueidentifier,
                PRICETYPE nvarchar(100),
                PRICE money,
                PRICINGSTRUCTURECODE tinyint
            );

            if exists (select 1 from dbo.PROGRAMEVENTPRICE where EVENTID = @EVENTID)
            begin

                insert into @ORDERITEMS
                select
                    newid(),
                    IT.QUANTITY,
                    PROGRAMEVENTPRICE.PRICETYPECODEID,
                    dbo.UFN_PRICETYPECODE_GETDESCRIPTION(PROGRAMEVENTPRICE.PRICETYPECODEID) as PRICETYPE,
                    PROGRAMEVENTPRICE.FACEPRICE,
                    0
                from dbo.ITINERARYATTENDEE IT
                inner join dbo.PROGRAMEVENTPRICE on @EVENTID = PROGRAMEVENTPRICE.EVENTID
                inner join dbo.PRICETYPECODE on PRICETYPECODE.ID = PROGRAMEVENTPRICE.PRICETYPECODEID
                where 
                    PROGRAMEVENTPRICE.EVENTID = @EVENTID and
                    IT.PRICETYPECODEID = PRICETYPECODE.ID and
                    IT.ITINERARYID = @ITINERARYID and
                    not exists (select 1 
                                from dbo.SALESMETHODEXCLUDEDPRICETYPE 
                                where SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID = @SALESMETHODID and
                                SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = PROGRAMEVENTPRICE.PRICETYPECODEID)

            end
            else
            begin
                insert into @ORDERITEMS
                select
                    newid(),
                    IT.QUANTITY,
                    PROGRAMPRICE.PRICETYPECODEID,
                    dbo.UFN_PRICETYPECODE_GETDESCRIPTION(PROGRAMPRICE.PRICETYPECODEID) as PRICETYPE,
                    PROGRAMPRICE.FACEPRICE,
                    0
                from dbo.ITINERARYATTENDEE IT
                inner join dbo.EVENT on EVENT.ID = @EVENTID
                inner join dbo.PROGRAMPRICE on PROGRAMPRICE.PROGRAMID = EVENT.PROGRAMID
                inner join dbo.PRICETYPECODE on PRICETYPECODE.ID = PROGRAMPRICE.PRICETYPECODEID
                where 
                    IT.PRICETYPECODEID = PRICETYPECODE.ID and
                    IT.ITINERARYID = @ITINERARYID and
                    not exists (select 1 
                                from dbo.SALESMETHODEXCLUDEDPRICETYPE 
                                where SALESMETHODEXCLUDEDPRICETYPE.SALESMETHODID = @SALESMETHODID and
                                SALESMETHODEXCLUDEDPRICETYPE.PRICETYPECODEID = PROGRAMPRICE.PRICETYPECODEID)
            end

            if exists
            (  
                select PRICETYPECODEID from dbo.ITINERARYATTENDEE
                where 
                    ITINERARYID = @ITINERARYID and
                    PRICETYPECODEID not in (select PRICETYPECODEID from @ORDERITEMS)
            )
                raiserror('BBERR_INVALIDPRICETYPE', 13, 1);

            begin try
                declare @DESCRIPTION nvarchar(100);
                declare @EVENTSTARTTIME UDT_HOURMINUTE;
                declare @EVENTENDTIME UDT_HOURMINUTE;
                declare @EVENTSTARTDATE datetime;
                declare @EVENTENDDATE datetime;

                select 
                    @DESCRIPTION = EVENT.NAME,
                    @EVENTSTARTTIME = EVENT.STARTTIME,
                    @EVENTENDTIME = EVENT.ENDTIME,
                    @EVENTSTARTDATE = EVENT.STARTDATETIME,
                    @EVENTENDDATE = EVENT.ENDDATETIME
                from dbo.EVENT 
                where EVENT.ID = @EVENTID;

                if @ITEMSTARTDATETIME not between @EVENTSTARTDATE and @EVENTENDDATE
                    raiserror('BBERR_INVALIDTIMES', 13, 1);

                if not exists ( 
                    select 1 from dbo.ITINERARYITEM 
                    where 
                        ITINERARYITEM.ITINERARYID = @ITINERARYID and 
                        ITEMTYPECODE = 0 and EVENTID = @EVENTID
                )                                
                begin
                    declare @QUANTITY integer;
                    declare @TICKETSAVAILABLE integer;

                    select @QUANTITY = SUM(OI.QUANTITY)
                    from @ORDERITEMS OI

                    set @TICKETSAVAILABLE = dbo.UFN_EVENT_GETAVAILABILITY(@EVENTID);

                    if @QUANTITY > @TICKETSAVAILABLE
                    begin
                        declare @DIFFERENCE integer;

                        set @DIFFERENCE = @QUANTITY - @TICKETSAVAILABLE

                        set @ERRORMESSAGE = @DESCRIPTION + ' does not have enough capacity for this group. You can either reduce the group size by ' + 
                                    convert(nvarchar(10), @DIFFERENCE) + ' or choose a different event.' 

                        raiserror(@ERRORMESSAGE, 13, 1)
                    end

                    update dbo.SALESORDERITEM set
                        SALESORDERITEM.QUANTITY = SALESORDERITEM.QUANTITY + ITEMS.QUANTITY,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE
                    from @ORDERITEMS items
                    where 
                        SALESORDERID = @ORDERID and
                        SALESORDERITEM.ID in
                        ( 
                            select ID from dbo.SALESORDERITEMTICKET 
                            where EVENTID = @EVENTID and 
                            SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID
                        )

                    -- Set items to be flat rate if the reservation is flat rate and the program is included

                    if @ISFLATRATE = 1
                    begin
                        update @ORDERITEMS set PRICINGSTRUCTURECODE = 1
                        where dbo.UFN_RESERVATIONRATESCALE_ISEVENTINCLUDED(@ORDERID,@EVENTID) = 1
                    end

                    insert into dbo.SALESORDERITEM
                        (ID, SALESORDERID, TYPECODE, DESCRIPTION, QUANTITY, PRICE, PRICINGSTRUCTURECODE, ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED)
                    select
                        ITEMS.ID, 
                        @ORDERID, 
                        0, 
                        @DESCRIPTION + ' - ' + ITEMS.PRICETYPE, 
                        ITEMS.QUANTITY, 
                        ITEMS.PRICE,
                        ITEMS.PRICINGSTRUCTURECODE,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE
                    from @ORDERITEMS ITEMS
                    where 
                        not exists
                        (
                            select 1 from dbo.SALESORDERITEM
                            inner join dbo.SALESORDERITEMTICKET on SALESORDERITEM.ID = SALESORDERITEMTICKET.ID
                            where 
                                SALESORDERID = @ORDERID and
                                EVENTID = @EVENTID and
                                SALESORDERITEMTICKET.PRICETYPECODEID = ITEMS.PRICETYPECODEID
                        );

                    insert into dbo.SALESORDERITEMTICKET
                    (
                        ID,
                        EVENTID,
                        PROGRAMID,
                        PROGRAMNAME,
                        PROGRAMCATEGORYNAME,
                        PRICETYPECODEID,
                        PRICE,
                        ADDEDBYID,
                        CHANGEDBYID,
                        DATEADDED, 
                        DATECHANGED
                    )
                    select
                        ITEMS.ID, 
                        @EVENTID, 
                        (select PROGRAMID from dbo.EVENT where ID = @EVENTID) as PROGRAMID,
                        coalesce((
                            select [PROGRAM].[NAME] from dbo.[EVENT]
                            inner join dbo.[PROGRAM] on 
                                [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                            where [EVENT].[ID] = @EVENTID
                        ),''),
                        coalesce((
                            select [PROGRAMCATEGORYCODE].[DESCRIPTION] from dbo.[EVENT]
                            inner join dbo.[PROGRAM] on 
                                [EVENT].[PROGRAMID] = [PROGRAM].[ID]
                            inner join dbo.[PROGRAMCATEGORYCODE] on 
                                [PROGRAM].[PROGRAMCATEGORYCODEID] = [PROGRAMCATEGORYCODE].[ID]
                            where [EVENT].[ID] = @EVENTID
                        ),''),
                        ITEMS.PRICETYPECODEID,
                        ITEMS.PRICE,
                        @CHANGEAGENTID, 
                        @CHANGEAGENTID, 
                        @CURRENTDATE, 
                        @CURRENTDATE
                    from @ORDERITEMS ITEMS
                    where
                        ITEMS.ID in
                        ( select ID from dbo.SALESORDERITEM where SALESORDERID = @ORDERID )

                end    

                insert into dbo.ITINERARYITEM
                (
                    ID,
                    STARTTIME,
                    ENDTIME,
                    NOTES,
                    EVENTID,
                    ITINERARYID,
                    NAME,
                    BLOCKEVENT,
                    STARTDATE,
                    ENDDATE,
                    ITEMTYPECODE,
                    ADDEDBYID,
                    CHANGEDBYID,
                    DATEADDED, 
                    DATECHANGED
                )
                values
                (
                    @ID,
                    @ITINERARYITEMSTARTTIME,
                    @ITINERARYITEMENDTIME,
                    @NOTES,
                    @EVENTID,
                    @ITINERARYID,
                    @DESCRIPTION,
                    @BLOCKEVENT,
                    @ITINERARYITEMSTARTDATE,
                    @ITINERARYITEMENDDATE,
                    0,
                    @CHANGEAGENTID, 
                    @CHANGEAGENTID, 
                    @CURRENTDATE, 
                    @CURRENTDATE
                )

                exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;
                exec dbo.USP_RESERVATION_CALCULATEFLATRATEFEES @ORDERID, @CHANGEAGENTID;

                -- Need to create the job, the job occurrence will be created using the trigger

                exec dbo.USP_RESOURCE_PROCESS @RESOURCES output, @CHANGEAGENTID, 0;
                exec dbo.USP_STAFFRESOURCE_PROCESS @STAFFRESOURCES output, @CHANGEAGENTID, 0, 0, 0;

                exec dbo.USP_ITINERARYITEMRESOURCE_GETRESOURCES_ADDFROMXML @ID, @RESOURCES, @CHANGEAGENTID;
                exec dbo.USP_ITINERARYITEM_GETSTAFFRESOURCES_ADDFROMXML @ID, @STAFFRESOURCES, @CHANGEAGENTID;

                exec dbo.USP_ITINERARY_CHECKPERTICKETRESOURCES @ITINERARYID;
                exec dbo.USP_ITINERARY_RESOURCES_SALESORDERSYNC @ITINERARYID, @ORDERID, @CHANGEAGENTID
                exec dbo.USP_ITINERARY_STAFFRESOURCES_SALESORDERSYNC @ITINERARYID, @ORDERID, @CHANGEAGENTID
                exec dbo.USP_ITINERARYITEM_RESOURCES_SALESORDERSYNC @ID, @ORDERID, @CHANGEAGENTID
                exec dbo.USP_ITINERARYITEM_STAFFRESOURCES_SALESORDERSYNC @ID, @ORDERID, @CHANGEAGENTID

                if @ISFLATRATE = 1
                begin
                    exec dbo.USP_RESERVATION_UPDATEFLATRATEPERTICKETPRICE @ORDERID, null, @CHANGEAGENTID, @CURRENTDATE;
                    exec dbo.USP_RESERVATION_DISTRIBUTEAPPLICATIONS @ORDERID, @CHANGEAGENTID, @CURRENTDATE;    
                end
                else
                begin
                    exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;
                end

                -- 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;
        end