USP_RESERVATIONRATESCALE_UPDATEAUTOMATICRESOURCEAPPLICATIONS

        create procedure dbo.USP_RESERVATIONRATESCALE_UPDATEAUTOMATICRESOURCEAPPLICATIONS
        (
            @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 @RESOURCES table
            (
                RESOURCEID uniqueidentifier,
                INAPPLICATIONS bit
            )

            insert into @RESOURCES
            select
                ITINERARYRESOURCE.RESOURCEID,
                0
            from dbo.SALESORDERITEM SOI
            inner join dbo.SALESORDERITEMITINERARYRESOURCE SOIIR on 
                SOI.ID = SOIIR.SALESORDERITEMID
            inner join dbo.ITINERARYRESOURCE on
                SOIIR.ITINERARYRESOURCEID = ITINERARYRESOURCE.ID
            where
                SOI.SALESORDERID = @ID and 
                SOI.PRICINGSTRUCTURECODE = 1
            group by RESOURCEID

            insert into @RESOURCES
            select
                ITINERARYITEMRESOURCE.RESOURCEID,
                0
            from dbo.SALESORDERITEM SOI
            inner join dbo.SALESORDERITEMITINERARYITEMRESOURCE SOIIIR on 
                SOI.ID = SOIIIR.SALESORDERITEMID
            inner join dbo.ITINERARYITEMRESOURCE on
                SOIIIR.ITINERARYITEMRESOURCEID = ITINERARYITEMRESOURCE.ID
            where
                SOI.SALESORDERID = @ID and 
                SOI.PRICINGSTRUCTURECODE = 1 and
                ITINERARYITEMRESOURCE.RESOURCEID not in
                ( select RESOURCEID from @RESOURCES )
            group by RESOURCEID

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

            insert into dbo.RESERVATIONRATESCALEAPPLICATION
            (
                ID, 
                RESERVATIONRATESCALEID,
                TYPECODE,
                RESOURCEID, 
                AMOUNT,
                ISADDEDAUTOMATICALLY,
                ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
            )
            select
                newid(), 
                @ID, 
                2,
                RESOURCEID, 
                0.0,
                1,
                @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
            from @RESOURCES
            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 = 2 and
                not exists
                    (select RESOURCEID from @RESOURCES where RESERVATIONRATESCALEAPPLICATION.RESOURCEID = [@RESOURCES].RESOURCEID)

            select @e=@@error;

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

        end