USP_RESERVATION_UPDATEFLATRATE

        CREATE procedure dbo.USP_RESERVATION_UPDATEFLATRATE
        (
            @ID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null,
            @CURRENTDATE datetime = null,
            @UPDATERATESCALE bit = 1,
            @CALCULATEFEESDISCOUNTSANDTAXES bit = 1
        )
        as
        begin

            set nocount on;

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

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

            declare @ISFLATRATE bit = 0
            select
                @ISFLATRATE = PRICINGCODE
            from dbo.RESERVATION
            where ID = @ID

            if @ISFLATRATE = 1
            begin
                declare @CURRENTRATESCALEPRICEID uniqueidentifier
                -- Included to allow user to ignore rate scale change on check in.

                if @UPDATERATESCALE = 0
                    select @CURRENTRATESCALEPRICEID = RESERVATIONRATESCALEPRICE.ID
                    from dbo.RESERVATIONRATESCALEPRICE
                    inner join dbo.RESERVATIONRATESCALE on
                        RESERVATIONRATESCALE.ID = RESERVATIONRATESCALEPRICE.RESERVATIONRATESCALEID
                    where
                        RESERVATIONRATESCALE.ID = @ID
                        and INUSE =1 
                else
                begin
                    set @CURRENTRATESCALEPRICEID = dbo.UFN_RESERVATION_GETRATESCALEPRICE(@ID)
                end

                if not exists(select top(1) 1 from dbo.RESERVATIONRATESCALEAPPLICATION where RESERVATIONRATESCALEID = @ID)
                begin
                    exec dbo.USP_RESERVATIONRATESCALE_CREATEAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE, @UPDATERATESCALE;
                end
                else
                begin
                    declare @INUSEPRICEID uniqueidentifier = null
                    declare @INUSEPRICE money = 0.0
                    select 
                        @INUSEPRICEID = RRSP.ID,
                        @INUSEPRICE = AMOUNT
                    from dbo.RESERVATIONRATESCALEPRICE RRSP
                    inner join dbo.RESERVATIONRATESCALE on
                        RESERVATIONRATESCALE.ID = RRSP.RESERVATIONRATESCALEID
                    where
                        INUSE = 1 and
                        RESERVATIONRATESCALE.ID = @ID

                    if @INUSEPRICEID is null or (@INUSEPRICE = 0.0 and @INUSEPRICEID <> @CURRENTRATESCALEPRICEID)
                    begin
                        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

                        select @e=@@error;
                        if @e<>0 return -456; --always return non-zero sp result if an error occurs    

                        exec dbo.USP_RESERVATIONRATESCALE_CREATEAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE, @UPDATERATESCALE;
                    end
                    else if @INUSEPRICEID <> @CURRENTRATESCALEPRICEID    
                    begin        
                        -- Only want to update rate scale applications if the total price is changing

                        -- Because the user can customize prices

                        exec dbo.USP_RESERVATIONRATESCALE_RECREATEAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE;        
                    end
                end

                -- In use is primarily used to see if there is a difference between

                -- rate scale prices for purposes of switching what is used based on group size.

                update dbo.RESERVATIONRATESCALEPRICE set
                    INUSE = case when RESERVATIONRATESCALEPRICE.ID = @CURRENTRATESCALEPRICEID then 1
                            else 0 end,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from dbo.RESERVATIONRATESCALE
                where 
                    RESERVATIONRATESCALE.ID = RESERVATIONRATESCALEPRICE.RESERVATIONRATESCALEID and
                    RESERVATIONRATESCALE.ID = @ID

                declare @INCLUDEALLPROGRAMS bit
                declare @INCLUDEALLFEES bit
                select 
                    @INCLUDEALLPROGRAMS = INCLUDEALLPROGRAMS,
                    @INCLUDEALLFEES = INCLUDEALLFEES
                from dbo.RESERVATIONRATESCALE
                where ID = @ID    

                -- Reset all sales order items to use normal pricing structure

                -- If they are not included in programs                

                if @INCLUDEALLPROGRAMS = 0
                begin
                    update dbo.SALESORDERITEM set
                        PRICINGSTRUCTURECODE = 0,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE                        
                    from dbo.SALESORDERITEMTICKET 
                    where 
                        SALESORDERITEMTICKET.ID = SALESORDERITEM.ID and
                        SALESORDERITEM.SALESORDERID = @ID and
                        SALESORDERITEM.TYPECODE = 0 and
                        PRICINGSTRUCTURECODE = 1 and
                        dbo.UFN_RESERVATIONRATESCALE_ISEVENTINCLUDED(@ID,SALESORDERITEMTICKET.EVENTID) = 0 and
                        dbo.UFN_RESERVATIONRATESCALE_ISPROGRAMINCLUDED(@ID,SALESORDERITEMTICKET.PROGRAMID) = 0
                end

                -- Update all sales order item prices included in flat rate to use a different pricing structure

                update dbo.SALESORDERITEM set
                    PRICINGSTRUCTURECODE = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from dbo.SALESORDERITEMTICKET
                where
                    SALESORDERITEMTICKET.ID = SALESORDERITEM.ID and
                    SALESORDERID = @ID and
                    TYPECODE = 0 and
                    PRICINGSTRUCTURECODE = 0 and -- Keep our date changed field in check

                    (
                        @INCLUDEALLPROGRAMS = 1 or
                        dbo.UFN_RESERVATIONRATESCALE_ISEVENTINCLUDED(@ID,SALESORDERITEMTICKET.EVENTID) = 1 or    
                        dbo.UFN_RESERVATIONRATESCALE_ISPROGRAMINCLUDED(@ID,SALESORDERITEMTICKET.PROGRAMID) = 1
                    )

                -- If this flat rate uses per-ticket pricing, we need to update that

                if @UPDATERATESCALE = 1
                    exec dbo.USP_RESERVATION_UPDATEFLATRATEPERTICKETPRICE @ID, @CURRENTRATESCALEPRICEID, @CHANGEAGENTID, @CURRENTDATE;

            end
            -- Regardless of whether we are looking at flat rate, we need to sync resources!


            -- update Sales order items for the resources

            exec dbo.USP_ITINERARY_RESOURCES_SALESORDERSYNC null, @ID, @CHANGEAGENTID
            exec dbo.USP_ITINERARY_STAFFRESOURCES_SALESORDERSYNC null, @ID, @CHANGEAGENTID

            -- call the bulk versions to update all itinerary item records

            exec dbo.USP_ITINERARYITEMS_RESOURCES_SALESORDERSYNC null, @ID, @CHANGEAGENTID
            exec dbo.USP_ITINERARYITEMS_STAFFRESOURCES_SALESORDERSYNC null, @ID, @CHANGEAGENTID

            if @ISFLATRATE = 1
            begin
                -- Distribute applications across sales order items that are included in the flat rate

                exec dbo.USP_RESERVATION_DISTRIBUTEAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE;

                if @CALCULATEFEESDISCOUNTSANDTAXES = 1
                    -- Calculate fees because some totals for sales order items may have changed

                    exec dbo.USP_SALESORDER_CALCULATEFEES @ID, @CHANGEAGENTID;

                -- If they are not included in fees

                if @INCLUDEALLFEES = 0
                begin
                    update dbo.SALESORDERITEM set
                        PRICINGSTRUCTURECODE = 0,
                        CHANGEDBYID = @CHANGEAGENTID,
                        DATECHANGED = @CURRENTDATE                        
                    from dbo.SALESORDERITEMFEE
                    where 
                        SALESORDERITEMFEE.ID = SALESORDERITEM.ID and
                        SALESORDERITEM.SALESORDERID = @ID and
                        SALESORDERITEM.TYPECODE = 3 and
                        PRICINGSTRUCTURECODE = 1 and
                        dbo.UFN_RESERVATIONRATESCALE_ISFEEINCLUDED(@ID,SALESORDERITEMFEE.FEEID) = 0 
                end

                update dbo.SALESORDERITEM set
                    PRICINGSTRUCTURECODE = 1,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                from dbo.SALESORDERITEMFEE
                where
                    SALESORDERITEMFEE.ID = SALESORDERITEM.ID and
                    SALESORDERID = @ID and
                    SALESORDERITEM.TYPECODE = 3 and
                    PRICINGSTRUCTURECODE = 0 and -- Keep our date changed field in check

                    (
                        @INCLUDEALLFEES = 1 or
                        dbo.UFN_RESERVATIONRATESCALE_ISFEEINCLUDED(@ID,SALESORDERITEMFEE.FEEID) = 1
                    )

                exec dbo.USP_RESERVATION_DISTRIBUTEFEEAPPLICATIONS @ID, @CHANGEAGENTID, @CURRENTDATE;    

                if @CALCULATEFEESDISCOUNTSANDTAXES = 1
                begin
                    --exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ID, @CHANGEAGENTID;

                    --exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ID, @CHANGEAGENTID;


                    -- Calculate taxes last

                    exec dbo.USP_SALESORDER_CALCULATETAXES @ID, @CHANGEAGENTID;
                    exec dbo.USP_RESERVATIONRATESCALE_UPDATETAXES @ID, @CHANGEAGENTID, @CURRENTDATE;
                end

            end
            else
            begin        
                update dbo.SALESORDERITEM set
                    PRICINGSTRUCTURECODE = 0,
                    CHANGEDBYID = @CHANGEAGENTID,
                    DATECHANGED = @CURRENTDATE
                where 
                    SALESORDERITEM.SALESORDERID = @ID and
                    (
                        SALESORDERITEM.TYPECODE = 0 or
                        SALESORDERITEM.TYPECODE = 3 or
                        SALESORDERITEM.TYPECODE = 4
                    ) and
                    PRICINGSTRUCTURECODE = 1

                if exists(select top 1 1 from dbo.RESERVATIONRATESCALE where ID = @ID)
                begin
                    exec dbo.USP_RESERVATIONRATESCALE_DELETEBYID_WITHCHANGEAGENTID @ID, @CHANGEAGENTID;
                end

                if @CALCULATEFEESDISCOUNTSANDTAXES = 1
                begin
                    exec dbo.USP_SALESORDER_CALCULATEFEES @ID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_APPLYITEMDISCOUNTS @ID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_APPLYORDERDISCOUNTS @ID, @CHANGEAGENTID;
                    exec dbo.USP_SALESORDER_CALCULATETAXES @ID, @CHANGEAGENTID;
                end
            end        
        end