USP_RESERVATION_UPDATESTATUSANDHISTORY

        CREATE procedure dbo.USP_RESERVATION_UPDATESTATUSANDHISTORY
        (
            @RESERVATIONID uniqueidentifier,
            @CHANGEAGENTID uniqueidentifier = null
        )
        as
        begin
            if @CHANGEAGENTID is null  
            exec dbo.USP_CHANGEAGENT_GETORCREATECHANGEAGENT @CHANGEAGENTID output;

            declare @CURRENTDATE datetime = getdate()

            declare @CURRENTSTATUSCODE tinyint = null;
            declare @PROJECTEDSTATUSCODE tinyint = 0;
            declare @DEPOSITREQUIRED bit = null;
            declare @DEPOSITRECEIVED bit = null;
            declare @DEPOSITAMOUNT money = 0;
            declare @CONTRACTREQUIRED bit = null;
            declare @CONTRACTRECEIVED bit = null;
            declare @CONTRACTSENT bit = null;
            declare @FINALCOUNTREQUIRED bit = null;
            declare @FINALCOUNTRECEIVED bit = null;

            select 
                @CURRENTSTATUSCODE = [SALESORDER].[STATUSCODE],
                @DEPOSITREQUIRED = [RESERVATION].[DEPOSITREQUIRED],
                @DEPOSITRECEIVED = dbo.[UFN_RESERVATION_ISDEPOSITPAID]([RESERVATION].[ID]),
                @DEPOSITAMOUNT = [RESERVATION].[DEPOSITAMOUNT],
                @CONTRACTREQUIRED = [RESERVATION].[CONTRACTREQUIRED],
                @CONTRACTSENT = [RESERVATION].[CONTRACTSENT],
                @CONTRACTRECEIVED = [RESERVATION].[CONTRACTRECEIVED],
                @FINALCOUNTREQUIRED = [RESERVATION].[FINALCOUNTREQUIRED],
                @FINALCOUNTRECEIVED = [RESERVATION].[FINALCOUNTRECEIVED]
            from dbo.[SALESORDER]
            inner join dbo.[RESERVATION] 
                on [SALESORDER].[ID] = [RESERVATION].[ID]
            where [SALESORDER].[ID] = @RESERVATIONID

            --Determine status code based on automated-status rules.

            --Deposit xor Contract required - Deposit: Tentative went set, Confirmed when Paid; Contract: Tentative when Sent, Confirmed when received.

            --Deposit and Contract required, Deposit set & Contract Sent = Tentative, Both Received = Confirmed;

            --Confirmed requirement met (or deposit and contract not required) + Final count required and received = Finalized



            if (@DEPOSITREQUIRED = 1) and (@CONTRACTREQUIRED = 1)
            begin
                if (@DEPOSITRECEIVED = 1 and @CONTRACTRECEIVED = 1)
                    set @PROJECTEDSTATUSCODE = 3
                else if @CONTRACTSENT = 1 and @DEPOSITAMOUNT <> 0
                    set @PROJECTEDSTATUSCODE = 2
            end
            else if (@DEPOSITREQUIRED = 1 and @DEPOSITRECEIVED = 1)
                set @PROJECTEDSTATUSCODE = 3
            --else if (@DEPOSITREQUIRED = 1 and @DEPOSITAMOUNT <> 0) Removing this per design

            --    set @PROJECTEDSTATUSCODE = 2

            else if (@CONTRACTREQUIRED = 1 and @CONTRACTRECEIVED = 1)
                set @PROJECTEDSTATUSCODE = 3
            else if (@CONTRACTREQUIRED = 1 and @CONTRACTSENT = 1)
                set @PROJECTEDSTATUSCODE = 2

            if (@FINALCOUNTREQUIRED = 1 and @FINALCOUNTRECEIVED = 1) and ((@PROJECTEDSTATUSCODE = 3) or (@DEPOSITREQUIRED = 0 and @CONTRACTREQUIRED = 0))
                set @PROJECTEDSTATUSCODE = 4

            --Automated status changes never downgrade

            if @CURRENTSTATUSCODE not in (1,5) and (@PROJECTEDSTATUSCODE > @CURRENTSTATUSCODE)
            begin try
                update dbo.[SALESORDER] 
                set
                    [STATUSCODE] = @PROJECTEDSTATUSCODE,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                where [ID] = @RESERVATIONID

                exec dbo.[USP_RESERVATIONSTATUSHISTORY_ADD] @RESERVATIONID, @CHANGEAGENTID, @PROJECTEDSTATUSCODE
            end try

            begin catch
                exec dbo.USP_RAISE_ERROR;
                return 1;
            end catch

            return 0;
        end