USP_SALESORDERPAYMENT_DELETE

CREATE procedure dbo.USP_SALESORDERPAYMENT_DELETE
(
    @ID uniqueidentifier,
    @CHANGEAGENTID uniqueidentifier = null
)
as begin
    declare @REVENUEID uniqueidentifier;
    declare @STATUSCODE tinyint;
    declare @SALESORDERID uniqueidentifier;
    declare @SALESMETHOD tinyint;
    declare @CURRENTDATE datetime;
    set @CURRENTDATE = getdate();

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

    select 
        @STATUSCODE = [SALESORDER].[STATUSCODE],
        @REVENUEID = [SALESORDERPAYMENT].[PAYMENTID],
        @SALESORDERID = [SALESORDERPAYMENT].[SALESORDERID],
        @SALESMETHOD = [SALESORDER].[SALESMETHODTYPECODE]
    from dbo.[SALESORDER]
    inner join dbo.[SALESORDERPAYMENT] 
        on [SALESORDER].[ID] = [SALESORDERPAYMENT].[SALESORDERID]
    where [SALESORDERPAYMENT].[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

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

    exec dbo.USP_FINANCIALTRANSACTION_DELETEBYID_WITHCHANGEAGENTID @REVENUEID, @CHANGEAGENTID

    if @SALESMETHOD < 2
        begin
            -- recalculate any cash payments 

            -- only if it's daily/advance sales

            declare @AMOUNT money;
            declare @AMOUNTTENDERED money;

            select
                @AMOUNT = AMOUNTPAID,
                @AMOUNTTENDERED = AMOUNTTENDERED
            from
                dbo.UFN_SALESORDER_TOTALS(@SALESORDERID);

            if @AMOUNTTENDERED > @AMOUNT 
                begin

                declare @BALANCE money;
                select @BALANCE = dbo.UFN_SALESORDER_GETAMOUNTDUE(@SALESORDERID);

                select @REVENUEID = PAYMENTID
                from dbo.SALESORDERPAYMENT
                where SALESORDERID = @SALESORDERID
                    and AMOUNT < AMOUNTTENDERED;

                if @AMOUNTTENDERED <= @BALANCE
                    begin
                        update dbo.SALESORDERPAYMENT 
                            set AMOUNT = AMOUNTTENDERED, CHANGEDUE = 0,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where PAYMENTID = @REVENUEID;
                    end
                else
                    begin
                        -- Amount tendered is still greater than the balance, so we need to recalc

                        -- the cash amount 

                        declare @CHANGEDUE money;

                        select @CHANGEDUE = AMOUNTTENDERED - @BALANCE
                        from dbo.SALESORDERPAYMENT
                        where SALESORDERID = @SALESORDERID
                            and AMOUNT < AMOUNTTENDERED;

                        update dbo.SALESORDERPAYMENT
                            set AMOUNT = @BALANCE, 
                                CHANGEDUE = @CHANGEDUE,
                                CHANGEDBYID = @CHANGEAGENTID,
                                DATECHANGED = @CURRENTDATE
                            where PAYMENTID = @REVENUEID;
                    end

                -- make sure we aren't trying to modify revenue in a locked/closed deposit/bank

                exec dbo.USP_EDIT_REVENUE_VALIDATE_DEPOSIT_STATUS @REVENUEID;

                update REVENUE set
                    TRANSACTIONAMOUNT = SALESORDERPAYMENT.AMOUNT
                    ,BASEAMOUNT = SALESORDERPAYMENT.AMOUNT
                    ,ORGAMOUNT = SALESORDERPAYMENT.AMOUNT
                    ,CHANGEDBYID = @CHANGEAGENTID
                    ,DATECHANGED = @CURRENTDATE
                from dbo.FINANCIALTRANSACTION REVENUE
                inner join dbo.SALESORDERPAYMENT on REVENUE.ID = SALESORDERPAYMENT.PAYMENTID
                where REVENUE.ID = @REVENUEID; 

                end
        end

    return 0;

end