USP_ITINERARY_CLEAR

            CREATE procedure dbo.USP_ITINERARY_CLEAR
            (
                @ID uniqueidentifier,
                @RESERVATIONID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier
            )
            as begin

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

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

                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;

                delete from dbo.ITINERARYITEMRESOURCE
                from ITINERARYITEMRESOURCE
                    inner join ITINERARYITEM on ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
                where ITINERARYITEM.ITINERARYID = @ID

                exec dbo.USP_ITINERARY_REMOVEFACILITIES @ID;

                delete dbo.ITINERARYITEM
                where ITINERARYITEM.ITINERARYID = @ID

                /* Only delete the resource that are NOT required */
                delete from dbo.ITINERARYRESOURCE
                where 
                    ITINERARYRESOURCE.ITINERARYID = @ID and
                    not exists 
                    (
                        select 1 from dbo.ITINERARY
                        inner join GROUPTYPEREQUIREDRESOURCE on 
                            ITINERARY.GROUPSALESGROUPTYPECODEID = GROUPTYPEREQUIREDRESOURCE.GROUPSALESGROUPTYPECODEID
                        where
                            ITINERARY.ID = ITINERARYRESOURCE.ITINERARYID and
                            GROUPTYPEREQUIREDRESOURCE.RESOURCEID = ITINERARYRESOURCE.RESOURCEID
                    )

                /* Only delete the staffing resource that are NOT required */
                delete from dbo.ITINERARYSTAFFRESOURCE
                where 
                    ITINERARYSTAFFRESOURCE.ITINERARYID = @ID and
                    not exists 
                    (
                        select 1 from dbo.ITINERARY
                        inner join GROUPTYPEREQUIREDSTAFFRESOURCE on 
                            ITINERARY.GROUPSALESGROUPTYPECODEID = GROUPTYPEREQUIREDSTAFFRESOURCE.GROUPSALESGROUPTYPECODEID 
                        where
                            ITINERARY.ID = ITINERARYSTAFFRESOURCE.ITINERARYID and
                            GROUPTYPEREQUIREDSTAFFRESOURCE.VOLUNTEERTYPEID = ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID and
                            GROUPTYPEREQUIREDSTAFFRESOURCE.FILLEDBYCODE = ITINERARYSTAFFRESOURCE.FILLEDBYCODE

                    )

                update dbo.ITINERARY set
                    TRACKID = null,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where ID = @ID

                exec dbo.USP_RESERVATION_UPDATEITEMQUANTITIES @RESERVATIONID, @CHANGEAGENTID;

                select @e=@@error;

                -- reset CONTEXT_INFO to previous value 

                if not @contextCache is null
                    set CONTEXT_INFO @contextCache;

                if @e <> 0
                    return 2;

                return 0;
            end