USP_RESERVATIONRATESCALE_CREATEAPPLICATIONS

        CREATE procedure dbo.USP_RESERVATIONRATESCALE_CREATEAPPLICATIONS
        (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null,
            @UPDATERATESCALE bit = 1
        )
        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 @TOTALAMOUNT money
                declare @RATESCALEID uniqueidentifier

                if @UPDATERATESCALE = 0
                begin
                    select
                        @TOTALAMOUNT = RESERVATIONRATESCALEPRICE.AMOUNT,
                        @RATESCALEID = RESERVATIONRATESCALE.RATESCALEID
                    from dbo.RESERVATIONRATESCALEPRICE
                    inner join dbo.RESERVATIONRATESCALE on
                        RESERVATIONRATESCALE.ID = RESERVATIONRATESCALEPRICE.RESERVATIONRATESCALEID
                    where
                        RESERVATIONRATESCALE.ID = @ID
                        and RESERVATIONRATESCALEPRICE.INUSE = 1
                end
                else
                begin
                    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

                    select
                        @TOTALAMOUNT = RESERVATIONRATESCALEPRICE.AMOUNT,
                        @RATESCALEID = RESERVATIONRATESCALE.RATESCALEID
                    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
                        )
                end


                declare @RESERVATIONAPPLICATIONS table
                (
                    ID uniqueidentifier,
                    TYPECODE tinyint,
                    ITEMPERCENT decimal(5,2),
                    AMOUNT money,
                    PROGRAMID uniqueidentifier,
                    RESOURCEID uniqueidentifier,
                    VOLUNTEERTYPEID uniqueidentifier,
                    FEEID uniqueidentifier
                )
                insert into @RESERVATIONAPPLICATIONS
                select
                    newid(),
                    TYPECODE,
                    [PERCENT],
                    0.0,
                    PROGRAMID,
                    RESOURCEID,
                    VOLUNTEERTYPEID,
                    FEEID
                from dbo.RATESCALEAPPLICATION
                where 
                    RATESCALEAPPLICATION.RATESCALEID = @RATESCALEID

                update @RESERVATIONAPPLICATIONS set
                    AMOUNT = round((ITEMPERCENT * @TOTALAMOUNT)/100,2)

                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

                    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

                insert into dbo.RESERVATIONRATESCALEAPPLICATION
                ( 
                    ID, 
                    RESERVATIONRATESCALEID,
                    TYPECODE, 
                    AMOUNT, 
                    PROGRAMID, 
                    FEEID, 
                    RESOURCEID,
                    VOLUNTEERTYPEID,
                    ADDEDBYID, CHANGEDBYID, DATEADDED, DATECHANGED
                )
                select
                    ID,
                    @ID,
                    TYPECODE,
                    AMOUNT,
                    PROGRAMID,
                    FEEID,
                    RESOURCEID,
                    VOLUNTEERTYPEID,
                    @CHANGEAGENTID, @CHANGEAGENTID, @CURRENTDATE, @CURRENTDATE
                from @RESERVATIONAPPLICATIONS    

            end
        end