USP_RESERVATIONRATESCALE_UPDATEAUTOMATICFEEAPPLICATIONS

Creates applications that don't exist for fees on reservations included in rate scale, deletes ones that no longer exist and were automatic.
Parameters

Parameter	Parameter Type	Mode
@ID	uniqueidentifier	IN
@CHANGEAGENTID	uniqueidentifier	IN
@CURRENTDATE	datetime	IN
Definition

Copy


        CREATE procedure dbo.USP_RESERVATIONRATESCALE_UPDATEAUTOMATICFEEAPPLICATIONS
        (
            @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 @FEES table
            (
                FEEID uniqueidentifier,
                INAPPLICATIONS bit
            )

            insert into @FEES
            select 
                SOIF.FEEID,
                0
            from dbo.SALESORDERITEM SOI
                inner join dbo.SALESORDERITEMFEE SOIF on SOI.ID = SOIF.ID
            where
                SOI.SALESORDERID = @ID and 
                SOI.PRICINGSTRUCTURECODE = 1
            group by SOIF.FEEID

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

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

            select @e=@@error;

            if not @contextCache is null
                set CONTEXT_INFO @contextCache;

            if @e <> 0
                return 2;

        end