USP_RESERVATIONRATESCALE_UPDATEAUTOMATICPROGRAMAPPLICATIONS

        CREATE procedure dbo.USP_RESERVATIONRATESCALE_UPDATEAUTOMATICPROGRAMAPPLICATIONS
        (
            @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 @PROGRAMS table
            (
                PROGRAMID uniqueidentifier,
                INAPPLICATIONS bit
            )

            insert into @PROGRAMS
            select 
                SOIT.PROGRAMID,
                0
            from dbo.SALESORDERITEM SOI
                inner join dbo.SALESORDERITEMTICKET SOIT on SOI.ID = SOIT.ID
            where
                SOI.SALESORDERID = @ID and 
                SOI.PRICINGSTRUCTURECODE = 1
            group by SOIT.PROGRAMID

            insert into @PROGRAMS
            select 
                EVENT.PROGRAMID,
                0
            from dbo.SALESORDERITEM SOI
                inner join dbo.SALESORDERITEMTICKET SOIT on SOI.ID = SOIT.ID
                inner join dbo.EVENT with (nolock) on SOIT.EVENTID = EVENT.ID
            where
                SOI.SALESORDERID = @ID and 
                SOI.PRICINGSTRUCTURECODE = 1 and
                EVENT.PROGRAMID not in (select PROGRAMID from @PROGRAMS)
            group by EVENT.PROGRAMID

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

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

            select @e=@@error;

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

        end