USP_RESERVATIONRATESCALE_UPDATEAUTOMATICSTAFFRESOURCEAPPLICATIONS

        create procedure dbo.USP_RESERVATIONRATESCALE_UPDATEAUTOMATICSTAFFRESOURCEAPPLICATIONS
        (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null
        )
        as
        begin

            set nocount on;

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

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

            declare @STAFFRESOURCES table
            (
                VOLUNTEERTYPEID uniqueidentifier,
                INAPPLICATIONS bit
            )

            insert into @STAFFRESOURCES
            select
                ITINERARYSTAFFRESOURCE.VOLUNTEERTYPEID,
                0
            from dbo.SALESORDERITEM SOI
            inner join dbo.SALESORDERITEMITINERARYSTAFFRESOURCE SOIIR on 
                SOI.ID = SOIIR.SALESORDERITEMID
            inner join dbo.ITINERARYSTAFFRESOURCE on
                SOIIR.ITINERARYSTAFFRESOURCEID = ITINERARYSTAFFRESOURCE.ID
            where
                SOI.SALESORDERID = @ID and 
                SOI.PRICINGSTRUCTURECODE = 1
            group by VOLUNTEERTYPEID

            insert into @STAFFRESOURCES
            select
                ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID,
                0
            from dbo.SALESORDERITEM SOI
            inner join dbo.SALESORDERITEMITINERARYITEMSTAFFRESOURCE SOIIIR on 
                SOI.ID = SOIIIR.SALESORDERITEMID
            inner join dbo.ITINERARYITEMSTAFFRESOURCE on
                SOIIIR.ITINERARYITEMSTAFFRESOURCEID = ITINERARYITEMSTAFFRESOURCE.ID
            where
                SOI.SALESORDERID = @ID and 
                SOI.PRICINGSTRUCTURECODE = 1 and
                ITINERARYITEMSTAFFRESOURCE.VOLUNTEERTYPEID not in
                ( select VOLUNTEERTYPEID from @STAFFRESOURCES )
            group by VOLUNTEERTYPEID

            update @STAFFRESOURCES set
                INAPPLICATIONS = 1
            where
                exists
                ( 
                    select 1 from dbo.RESERVATIONRATESCALEAPPLICATION RRSA
                    where
                        RRSA.RESERVATIONRATESCALEID = @ID and
                        RRSA.TYPECODE = 3 and
                        RRSA.VOLUNTEERTYPEID is not null and
                        RRSA.VOLUNTEERTYPEID = [@STAFFRESOURCES].VOLUNTEERTYPEID
                )

            insert into dbo.RESERVATIONRATESCALEAPPLICATION
            (
                ID, 
                RESERVATIONRATESCALEID,
                TYPECODE,
                VOLUNTEERTYPEID, 
                AMOUNT,
                ISADDEDAUTOMATICALLY,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                newid(), 
                @ID, 
                3,
                VOLUNTEERTYPEID, 
                0.0,
                1,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from @STAFFRESOURCES
            where INAPPLICATIONS = 0

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

            set @contextCache = CONTEXT_INFO();

            if not @CHANGEAGENTID is null
              set CONTEXT_INFO @CHANGEAGENTID

            delete from dbo.RESERVATIONRATESCALEAPPLICATION
            where
                RESERVATIONRATESCALEID = @ID and
                AMOUNT = 0 and
                ISADDEDAUTOMATICALLY = 1 and
                TYPECODE = 3 and
                not exists
                    (select VOLUNTEERTYPEID from @STAFFRESOURCES where RESERVATIONRATESCALEAPPLICATION.VOLUNTEERTYPEID = [@STAFFRESOURCES].VOLUNTEERTYPEID)

            select @e=@@error;

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

        end