USP_ITINERARYITEM_DELETE

                CREATE procedure dbo.USP_ITINERARYITEM_DELETE
                (
                    @ID uniqueidentifier,
                    @CHANGEAGENTID uniqueidentifier
                )
                as begin
                    if @CHANGEAGENTID is null  
                        exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

                    declare @PROGRAMID uniqueidentifier
                    declare @EVENTID uniqueidentifier
                    declare @ITINERARYID uniqueidentifier
                    declare @ORDERID uniqueidentifier
                    declare @STARTDATE datetime
                    declare @ISFLATRATE tinyint
                    declare @STATUSCODE tinyint
                    declare @ITEMTYPECODE tinyint

                    select 
                        @PROGRAMID = PROGRAMID,
                        @EVENTID = EVENTID,
                        @ITINERARYID = ITINERARYID,
                        @STARTDATE = STARTDATE,
                        @ITEMTYPECODE = ITEMTYPECODE
                    from dbo.ITINERARYITEM
                    where ITINERARYITEM.ID = @ID

                    select 
                        @ORDERID = RESERVATION.ID, 
                        @ISFLATRATE = PRICINGCODE,
                        @STATUSCODE = STATUSCODE
                    from dbo.RESERVATION
                    inner join dbo.ITINERARY on ITINERARY.RESERVATIONID = RESERVATION.ID
                    inner join dbo.SALESORDER on RESERVATION.ID = SALESORDER.ID
                    where ITINERARY.ID = @ITINERARYID

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

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

                    set @contextCache = CONTEXT_INFO();

                    if not @CHANGEAGENTID is null
                        set CONTEXT_INFO @CHANGEAGENTID

                    if 
                    (
                        select count(*)    from dbo.ITINERARYITEM
                        where 
                            ITINERARYITEM.ITINERARYID = @ITINERARYID and
                            (PROGRAMID = @PROGRAMID or EVENTID = @EVENTID) and 
                            @STARTDATE = STARTDATE
                    ) = 1 
                    begin
                        update dbo.[SALESORDERITEM]
                            set [SALESORDERITEM].[QUANTITY]= SALESORDERITEM.[QUANTITY] - ITINERARYATTENDEE.QUANTITY,
                                [SALESORDERITEM].CHANGEDBYID = @CHANGEAGENTID,
                                [SALESORDERITEM].DATECHANGED = @CURRENTDATE
                            from dbo.[SALESORDERITEM] 
                            inner join dbo.SALESORDERITEMTICKET as SOIT on [SALESORDERITEM].ID = SOIT.ID
                            inner join dbo.ITINERARYITEM on (SOIT.EVENTID = ITINERARYITEM.EVENTID and ITINERARYITEM.PROGRAMID is null) or (SOIT.PROGRAMID = ITINERARYITEM.PROGRAMID and ITINERARYITEM.EVENTID is null)
                            inner join dbo.ITINERARYATTENDEE on ITINERARYATTENDEE.PRICETYPECODEID = SOIT.PRICETYPECODEID
                            where SALESORDERITEM.SALESORDERID = @ORDERID and ITINERARYITEM.ID = @ID and
                                ITINERARYATTENDEE.ITINERARYID = @ITINERARYID and ITINERARYITEM.ITEMTYPECODE <> 3

                        declare @ORDERTICKETSTODELETE dbo.UDT_GENERICID;

                        insert into @ORDERTICKETSTODELETE (ID)
                        select
                            SALESORDERITEM.ID
                        from
                            dbo.SALESORDERITEM
                        inner join
                            dbo.SALESORDERITEMTICKET on SALESORDERITEMTICKET.ID = SALESORDERITEM.ID
                        where
                            @ITEMTYPECODE <> 3
                            and SALESORDERITEM.SALESORDERID = @ORDERID
                            and (@PROGRAMID is null or SALESORDERITEMTICKET.PROGRAMID = @PROGRAMID)
                            and (@EVENTID is null or SALESORDERITEMTICKET.EVENTID = @EVENTID)
                            and SALESORDERITEM.QUANTITY = 0;

                        if @@rowcount > 0
                        begin
                            delete from dbo.[SALESORDERITEM]
                            where ID in
                            (
                                select SALESORDERITEMFEE.ID
                                from @ORDERTICKETSTODELETE as ORDERTICKETSTODELETE
                                inner join dbo.SALESORDERITEMFEE on SALESORDERITEMFEE.SALESORDERITEMID = ORDERTICKETSTODELETE.ID
                            )

                            delete from dbo.SALESORDERITEMORDERDISCOUNTDETAIL 
                            where SALESORDERITEMID in (select ID from @ORDERTICKETSTODELETE)

                            delete dbo.SALESORDERITEM
                            where ID in (select ID from @ORDERTICKETSTODELETE)
                        end
                    end

                    -- Delete the Itinerary Item Resources to trigger the resource sync trigger

                    delete from ITINERARYITEMRESOURCE where ITINERARYITEMRESOURCE.ITINERARYITEMID = @ID

                    if not @contextCache is null
                        set CONTEXT_INFO @contextCache

                    select @e=@@error;

                    if @e<>0 return -456; --always return non-zero sp result if an error occurs    


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

                    if @EVENTID is not null or @PROGRAMID is not null
                    begin
                        exec dbo.USP_SALESORDER_CALCULATEFEES @ORDERID, @CHANGEAGENTID;
                        exec dbo.USP_RESERVATION_CALCULATEFLATRATEFEES @ORDERID, @CHANGEAGENTID;    
                    end

                    -- Delete the Itinerary Item Resources to trigger the resource sync trigger

                    delete from ITINERARYITEMRESOURCE where ITINERARYITEMRESOURCE.ITINERARYITEMID = @ID

                    declare @LOCATIONSALESORDERITEMID uniqueidentifier
                    if @ITEMTYPECODE = 2
                    begin
                        select
                            @LOCATIONSALESORDERITEMID = SALESORDERITEMID
                        from dbo.ITINERARYITEMLOCATION
                        where
                            ITINERARYITEMLOCATION.ID = @ID
                    end

                    exec USP_ITINERARYITEM_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID

                    if @ITEMTYPECODE = 2 and @LOCATIONSALESORDERITEMID is not null
                        exec dbo.USP_SALESORDERITEM_DELETEBYID_WITHCHANGEAGENTID @LOCATIONSALESORDERITEMID, @CHANGEAGENTID;

                    -- Recalculate as needed to handle changes in the itinerary's duration                

                    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 = 0
                    begin
                        exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ORDERID, @CHANGEAGENTID;
                        exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ORDERID, @CHANGEAGENTID;
                    end

                    -- Taxes must be calculated last (Items updated by sync may need updated tax values).

                    exec dbo.USP_SALESORDER_CALCULATETAXES @ORDERID, @CHANGEAGENTID;    

                    if @ISFLATRATE = 1
                        exec dbo.USP_RESERVATIONRATESCALE_UPDATETAXES @ORDERID, @CHANGEAGENTID, @CURRENTDATE;    


                    return 0;
                end