USP_RESERVATION_CANCELRESOURCES

        create procedure dbo.USP_RESERVATION_CANCELRESOURCES
        (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier,
            @CURRENTDATE datetime
        )
        as
        begin
            if @CHANGEAGENTID is null  
                exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output

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

            -- Update per-event consumable itinerary resources

            update dbo.RESOURCE    set 
                QUANTITY = RESOURCE.QUANTITY + RETURNRESOURCES.QUANTITYNEEDED,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from
            (
                select 
                    ITINERARYRESOURCE.RESOURCEID as ID,
                    sum(ITINERARYRESOURCE.QUANTITYNEEDED) as QUANTITYNEEDED
                from  dbo.ITINERARYRESOURCE
                inner join dbo.ITINERARY on
                    ITINERARY.ID = ITINERARYRESOURCE.ITINERARYID
                where 
                    ITINERARY.RESERVATIONID = @ID
                group by ITINERARYRESOURCE.RESOURCEID
            ) as RETURNRESOURCES
            where
                RESOURCE.ID = RETURNRESOURCES.ID and
                RESOURCE.ISPERTICKETITEM = 0 and
                RESOURCE.TYPECODE = 1

            -- Update per-event consumable itinerary item resources

            update dbo.RESOURCE    set 
                QUANTITY = RESOURCE.QUANTITY + RETURNRESOURCES.QUANTITYNEEDED,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from 
            (
                select
                    ITINERARYITEMRESOURCE.RESOURCEID as ID,
                    sum(ITINERARYITEMRESOURCE.QUANTITYNEEDED) as QUANTITYNEEDED
                from dbo.ITINERARYITEMRESOURCE
                inner join dbo.ITINERARYITEM on
                    ITINERARYITEM.ID = ITINERARYITEMRESOURCE.ITINERARYITEMID
                inner join dbo.ITINERARY on
                    ITINERARY.ID = ITINERARYITEM.ITINERARYID
                where 
                    ITINERARY.RESERVATIONID = @ID
                group by ITINERARYITEMRESOURCE.RESOURCEID
            ) as RETURNRESOURCES
            where
                RESOURCE.ID = RETURNRESOURCES.ID and
                RESOURCE.ISPERTICKETITEM = 0 and
                RESOURCE.TYPECODE = 1

            -- Update per-ticket consumable itinerary resources

            update dbo.RESOURCE set 
                QUANTITY = RESOURCE.QUANTITY + RETURNRESOURCE.QUANTITYNEEDED,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from
            (
                select
                    ITINERARYRESOURCE.RESOURCEID as ID,
                    sum(ITINERARYRESOURCE.PERTICKETQUANTITY * ITINERARYATTENDEE.QUANTITY) as QUANTITYNEEDED
                from dbo.ITINERARYRESOURCE
                inner join dbo.ITINERARY on
                    ITINERARY.ID = ITINERARYRESOURCE.ITINERARYID
                inner join dbo.ITINERARYATTENDEE on 
                    ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                where 
                    ITINERARY.RESERVATIONID = @ID
                group by ITINERARYRESOURCE.RESOURCEID
            ) as RETURNRESOURCE
            where
                RETURNRESOURCE.ID = RESOURCE.ID and
                RESOURCE.ISPERTICKETITEM <> 0 and 
                RESOURCE.TYPECODE = 1


            -- Update per-ticket consumable itinerary item resources

            update dbo.RESOURCE set 
                QUANTITY = RESOURCE.QUANTITY + RETURNRESOURCE.QUANTITYNEEDED,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from
            (
                select
                    ITINERARYITEMRESOURCE.RESOURCEID as ID,
                    sum(ITINERARYITEMRESOURCE.PERTICKETQUANTITY * ITINERARYATTENDEE.QUANTITY) as QUANTITYNEEDED
                from dbo.ITINERARYITEMRESOURCE
                inner join dbo.ITINERARYITEM on 
                    ITINERARYITEMRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
                inner join dbo.ITINERARY on
                    ITINERARY.ID = ITINERARYITEM.ITINERARYID
                inner join dbo.ITINERARYATTENDEE on 
                    ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                where 
                    ITINERARY.RESERVATIONID = @ID
                group by ITINERARYITEMRESOURCE.RESOURCEID
            ) as RETURNRESOURCE
            where
                RETURNRESOURCE.ID = RESOURCE.ID and
                RESOURCE.ISPERTICKETITEM <> 0 and 
                RESOURCE.TYPECODE = 1

            update dbo.JOBOCCURRENCE set
                ISACTIVE = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.ITINERARYSTAFFRESOURCE
            inner join dbo.ITINERARY on
                ITINERARY.ID = ITINERARYSTAFFRESOURCE.ITINERARYID
            where 
                ITINERARYSTAFFRESOURCE.JOBOCCURRENCEID = JOBOCCURRENCE.ID and
                ITINERARY.RESERVATIONID = @ID 

            update dbo.JOBOCCURRENCE set
                ISACTIVE = 0,
                CHANGEDBYID = @CHANGEAGENTID,
                DATECHANGED = @CURRENTDATE
            from dbo.ITINERARYITEMSTAFFRESOURCE
            inner join dbo.ITINERARYITEM on 
                ITINERARYITEMSTAFFRESOURCE.ITINERARYITEMID = ITINERARYITEM.ID
            inner join dbo.ITINERARY on
                ITINERARY.ID = ITINERARYITEM.ITINERARYID
            where 
                ITINERARYITEMSTAFFRESOURCE.JOBOCCURRENCEID = JOBOCCURRENCE.ID and
                ITINERARY.RESERVATIONID = @ID 

        end