USP_RESERVATIONRATESCALE_RECREATEAPPLICATIONS

        CREATE procedure dbo.USP_RESERVATIONRATESCALE_RECREATEAPPLICATIONS
        (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null,
            @OLDTOTALAMOUNT money = null
        )
        as
        begin

            set nocount on;

            if exists(select top(1) 1 from dbo.RESERVATIONRATESCALE where ID = @ID)
            begin

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

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

                declare @ITINERARYATTENDEECOUNT smallint
                select 
                    @ITINERARYATTENDEECOUNT = isnull(sum(QUANTITY),0) 
                from dbo.ITINERARYATTENDEE
                inner join dbo.ITINERARY on
                    ITINERARY.ID = ITINERARYATTENDEE.ITINERARYID
                inner join dbo.RESERVATION on
                    RESERVATION.ID = ITINERARY.RESERVATIONID
                where
                    RESERVATION.ID = @ID

                declare @TOTALAMOUNT money
                select
                    @TOTALAMOUNT = RESERVATIONRATESCALEPRICE.AMOUNT
                from dbo.RESERVATIONRATESCALEPRICE
                inner join dbo.RESERVATIONRATESCALE on
                    RESERVATIONRATESCALE.ID = RESERVATIONRATESCALEPRICE.RESERVATIONRATESCALEID
                where
                    RESERVATIONRATESCALE.ID = @ID and
                    @ITINERARYATTENDEECOUNT >= GROUPMINIMUM and
                    (
                        @ITINERARYATTENDEECOUNT <= GROUPMAXIMUM or
                        GROUPMAXIMUM = -1
                    )

                if @OLDTOTALAMOUNT is null
                begin
                    select 
                        @OLDTOTALAMOUNT = AMOUNT 
                    from dbo.RESERVATIONRATESCALEPRICE RRSP
                    inner join dbo.RESERVATIONRATESCALE on
                        RESERVATIONRATESCALE.ID = RRSP.RESERVATIONRATESCALEID
                    where
                        INUSE = 1 and
                        RESERVATIONRATESCALE.ID = @ID
                end

                declare @RESERVATIONAPPLICATIONS table
                (
                    ID uniqueidentifier,
                    AMOUNT money
                )
                insert into @RESERVATIONAPPLICATIONS
                select
                    ID,
                    AMOUNT
                from dbo.RESERVATIONRATESCALEAPPLICATION
                where 
                    RESERVATIONRATESCALEAPPLICATION.RESERVATIONRATESCALEID = @ID

                if @OLDTOTALAMOUNT > 0  -- prevent div by 0

                    update @RESERVATIONAPPLICATIONS set
                        AMOUNT = round((AMOUNT / @OLDTOTALAMOUNT) * @TOTALAMOUNT,2)
                else
                begin  -- make an educated guess on application amounts


                    declare @APPLICATIONPCTMAP table (
                        RESERVATIONRATESCALEAPPLICATIONID uniqueidentifier,
                        PCT decimal
                    )

                    insert into @APPLICATIONPCTMAP
                    select RRA.ID, RA.[PERCENT]
                    from dbo.RATESCALE
                    inner join dbo.RESERVATIONRATESCALE on RESERVATIONRATESCALE.RATESCALEID = RATESCALE.ID
                    inner join dbo.RESERVATIONRATESCALEAPPLICATION as RRA on RRA.RESERVATIONRATESCALEID = RESERVATIONRATESCALE.ID
                    inner join dbo.RATESCALEAPPLICATION as RA on RA.RATESCALEID = RATESCALE.ID
                    where RESERVATIONRATESCALE.ID = @ID
                        and (RA.PROGRAMID = RRA.PROGRAMID
                        or RA.RESOURCEID = RRA.RESOURCEID
                        or RA.FEEID = RRA.FEEID
                        or RA.VOLUNTEERTYPEID = RRA.VOLUNTEERTYPEID)


                    update @RESERVATIONAPPLICATIONS set
                        AMOUNT = round(coalesce((select PCT / 100 from @APPLICATIONPCTMAP where RESERVATIONRATESCALEAPPLICATIONID = ID), 0) * @TOTALAMOUNT,2)


                end

                declare @TOTALAPPLICATIONSAMOUNT money
                select
                    @TOTALAPPLICATIONSAMOUNT = sum(AMOUNT)
                from @RESERVATIONAPPLICATIONS

                if @TOTALAPPLICATIONSAMOUNT <> @TOTALAMOUNT -- Sometimes percentages will not match up to exact dollar amounts

                begin
                    declare @AMOUNTDIFFERENCE money
                    set @AMOUNTDIFFERENCE = @TOTALAMOUNT - @TOTALAPPLICATIONSAMOUNT

                    if @AMOUNTDIFFERENCE <= 1.0 and @AMOUNTDIFFERENCE >= -1.0 -- We don't want to adjust for applications that went too far over initially

                    begin
                        declare @TOPAPPLICATIONID uniqueidentifier
                        select top(1) @TOPAPPLICATIONID = ID from @RESERVATIONAPPLICATIONS 
                        where AMOUNT <> 0.0 and AMOUNT + @AMOUNTDIFFERENCE >= 0.0

                        update @RESERVATIONAPPLICATIONS set 
                            AMOUNT = AMOUNT + @AMOUNTDIFFERENCE
                        where ID = @TOPAPPLICATIONID
                    end
                end

                update dbo.RESERVATIONRATESCALEAPPLICATION set
                    RESERVATIONRATESCALEAPPLICATION.AMOUNT = [@RESERVATIONAPPLICATIONS].AMOUNT,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from @RESERVATIONAPPLICATIONS
                where [@RESERVATIONAPPLICATIONS].ID = RESERVATIONRATESCALEAPPLICATION.ID
            end
        end