USP_RESERVATIONSECURITYDEPOSITPAYMENT_DELETE

        CREATE procedure dbo.USP_RESERVATIONSECURITYDEPOSITPAYMENT_DELETE
            (
                @ID uniqueidentifier,
                @CHANGEAGENTID uniqueidentifier = null
            )
            as begin
                declare @REVENUEID uniqueidentifier;
                declare @STATUSCODE tinyint;
                declare @SALESORDERID uniqueidentifier;
                declare @SALESMETHOD tinyint;
                declare @PAYMENTAMOUNT money
                declare @CURRENTDATE datetime;
                declare @RECONCILIATIONID uniqueidentifier;

                set @CURRENTDATE = getdate();

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

                select 
                    @STATUSCODE = [SALESORDER].[STATUSCODE],
                    @REVENUEID = [RESERVATIONSECURITYDEPOSITPAYMENT].[PAYMENTID],
                    @SALESORDERID = [RESERVATIONSECURITYDEPOSITPAYMENT].[RESERVATIONID],
                    @SALESMETHOD = [SALESORDER].[SALESMETHODTYPECODE],
                    @PAYMENTAMOUNT = [RESERVATIONSECURITYDEPOSITPAYMENT].[AMOUNT],
                    @RECONCILIATIONID = [RESERVATIONSECURITYDEPOSITPAYMENT].RECONCILIATIONID
                from dbo.[SALESORDER]
                inner join dbo.[RESERVATIONSECURITYDEPOSITPAYMENT] 
                    on [SALESORDER].[ID] = [RESERVATIONSECURITYDEPOSITPAYMENT].[RESERVATIONID]
                where [RESERVATIONSECURITYDEPOSITPAYMENT].[ID] = @ID

                --check deletion rules, if any

                if @STATUSCODE = 1
                    begin
                        raiserror('Payments cannot be deleted from completed sales orders.', 13, 1);
                        return 0;                    
                    end

                if @RECONCILIATIONID is not null
                begin
                    raiserror('Reconciled payments cannot be deleted.', 13, 1);
                    return 0;                    
                end

                -- change security deposit payment status 

                if dbo.UFN_RESERVATION_GETSECURITYDEPOSITAMOUNTDUE(@SALESORDERID) + @PAYMENTAMOUNT > 0
                    update dbo.RESERVATION set SECURITYDEPOSITSTATUSCODE = 0 where RESERVATION.ID = @SALESORDERID;

                -- use the system generated delete routine to allow proper recording of the deleting agent

                exec dbo.USP_REVENUE_DELETE @REVENUEID, @CHANGEAGENTID

                return 0;

            end