USP_ORDERPAYMENT_UPDATECASHPAYMENTS

CREATE procedure dbo.USP_ORDERPAYMENT_UPDATECASHPAYMENTS
(
    @ID uniqueidentifier = null,  --Sales Order ID

    @CHANGEAGENTID uniqueidentifier = null,
    @AMOUNTTENDERED money = 0, --this is the amount you're adding in cash payments.

    @CURRENTDATE datetime = null,
    @CURRENTDATETIMEOFFSET datetimeoffset = null
)
as
    set nocount on;

    declare @AMOUNT money = 0
    declare @CHANGEDUE money = 0
    declare @ORDERBALANCE money = 0
    declare @CASHPAYMENTS money = 0
    declare @OTHERPAYMENTS money = 0
    declare @SALESORDERPAYMENTID uniqueidentifier = null
    declare @TRANSACTIONDATE date = null
    declare @CASHFINANCIALTRANSACTIONID uniqueidentifier = null;

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

    if @CURRENTDATE is null begin
        set @CURRENTDATE = getdate();
    end

    if @CURRENTDATETIMEOFFSET is null begin
        set @CURRENTDATETIMEOFFSET = dbo.UFN_TIMEZONEENTRY_GETSYSTEMDEFAULTDATEWITHTIMEOFFSET(getutcdate(), 1);
    end

    select @TRANSACTIONDATE = [TRANSACTIONDATE] 
    from dbo.[SALESORDER] 
    where [ID] = @ID

    if @TRANSACTIONDATE is not null
        set @TRANSACTIONDATE = @CURRENTDATETIMEOFFSET;

    select
        @CASHPAYMENTS = coalesce([SALESORDERPAYMENT].[AMOUNTTENDERED], 0),
        @SALESORDERPAYMENTID = [SALESORDERPAYMENT].[ID],
        @CASHFINANCIALTRANSACTIONID = SALESORDERPAYMENT.PAYMENTID
    from
        dbo.[SALESORDERPAYMENT]
    inner join dbo.[REVENUEPAYMENTMETHOD]
        on [REVENUEPAYMENTMETHOD].[REVENUEID] = [SALESORDERPAYMENT].[PAYMENTID]
    where [SALESORDERPAYMENT].[SALESORDERID] = @ID and
        [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] = 0

    if @SALESORDERPAYMENTID is not null begin
        select
            @OTHERPAYMENTS = coalesce(sum([SALESORDERPAYMENT].[AMOUNTTENDERED]), 0)
        from
            dbo.[SALESORDERPAYMENT]
        inner join dbo.[REVENUEPAYMENTMETHOD]
            on [REVENUEPAYMENTMETHOD].[REVENUEID] = [SALESORDERPAYMENT].[PAYMENTID]
        where [SALESORDERPAYMENT].[SALESORDERID] = @ID and
            [REVENUEPAYMENTMETHOD].[PAYMENTMETHODCODE] <> 0

        select @ORDERBALANCE = dbo.UFN_SALESORDER_TOTAL(@ID) - @OTHERPAYMENTS;

        set @AMOUNTTENDERED += @CASHPAYMENTS;
        set @AMOUNT = @AMOUNTTENDERED;

        if @AMOUNT > @ORDERBALANCE
            set @AMOUNT = @ORDERBALANCE

        set @CHANGEDUE = @AMOUNTTENDERED - @AMOUNT;

        if @CHANGEDUE < 0
            set @CHANGEDUE = 0

        -- update the credit payment record

        update dbo.[SALESORDERPAYMENT] with (rowlock) set
            [AMOUNTTENDERED] = @AMOUNTTENDERED,
            [AMOUNT] = @AMOUNT,
            [CHANGEDUE] = @CHANGEDUE,
            [PAYMENTDATEWITHTIMEOFFSET] = @CURRENTDATETIMEOFFSET,
            [CHANGEDBYID] = @CHANGEAGENTID,
            [DATECHANGED] = @CURRENTDATE
        where
            [ID] = @SALESORDERPAYMENTID
            and (
                AMOUNTTENDERED <> @AMOUNTTENDERED
                or AMOUNT <> @AMOUNT
                or CHANGEDUE <> @CHANGEDUE
            );

        if @@rowcount > 0 begin
            if exists(select * from dbo.[SALESORDERPAYMENT] where [AMOUNT] = 0 and [AMOUNTTENDERED] = [CHANGEDUE] and [ID] = @SALESORDERPAYMENTID) begin
                -- This cascade deletes the SALESORDERPAYMENT record linked to it

                delete from dbo.FINANCIALTRANSACTION where ID = @CASHFINANCIALTRANSACTIONID;
            end
            else begin
                declare @REVENUEID uniqueidentifier
                select @REVENUEID = [PAYMENTID] from dbo.[SALESORDERPAYMENT] where [ID] = @SALESORDERPAYMENTID

                declare @PDACCOUNTSYSTEMID uniqueidentifier;
                declare @ALLOWGLDISTRIBUTIONS bit;

                select
                    @PDACCOUNTSYSTEMID = ID,
                    @ALLOWGLDISTRIBUTIONS = ALLOWGLDISTRIBUTIONS
                from 
                    dbo.UFN_PDACCOUNTSYSTEM_DEFAULTORSYSTEM();

                -- update the revenue record

                update dbo.[FINANCIALTRANSACTION] set
                    [DATE] = @TRANSACTIONDATE,
                    [POSTDATE] = case when @ALLOWGLDISTRIBUTIONS=1 then @TRANSACTIONDATE else null end,
                    [TRANSACTIONAMOUNT] = @AMOUNT,
                    [BASEAMOUNT] = @AMOUNT,
                    [ORGAMOUNT] = @AMOUNT,
                    [CHANGEDBYID] = @CHANGEAGENTID,
                    [DATECHANGED] = @CURRENTDATE
                where
                    [ID] = @REVENUEID

                --update the origin amount

                exec dbo.USP_DATAFORMTEMPLATE_EDIT_PAYMENTORIGINALAMOUNT @REVENUEID, @CHANGEAGENTID, @AMOUNT
            end
        end
    end

    return 0;