Deferred revenue in foreign currency: closing the gap in D365

Deferred revenue in foreign currency: closing the gap in D365

The gap

In Microsoft Dynamics 365 for Finance, Deferred revenue in Subscription Billing works reliably as long as you stay within a single currency context. Once foreign currencies are involved, a design flaw starts to show its effects: the system recognises deferred revenue exclusively in accounting currency. The original transaction currency is disregarded, and the exchange rate used at invoice posting is not retained in the deferral logic.

This is consistent behaviour Revenue <-> COGS, but it creates a persistent issue: accounts stop balancing in currency, and this is even carried forward from year to year, at every FY closing.

At invoice posting, the exchange rate is effectively fixed, and from there, the deferral engine operates purely in accounting currency. This becomes visible when the deferred balance is fully recognised. In accounting currency, the balance reaches zero as expected. In transaction currency, however, it does not. Residual amounts remain not because of revaluation or fluctuating exchange rates, but because the postings were never aligned in the first place.

From a reconciliation point of view, this is difficult to justify. An account that is technically cleared still shows values in another currency view.

It is worth noting that this behaviour has been raised repeatedly: many customers and partners complained, the “Idea” Deferral schedule recognition to include transaction currency is at the top of complaints about the Subscription billing module.

Customisation: store the historical exchange rate, fix the recognition journal

Rather than working around the issue, the approach taken here was more straightforward: the Subscription Billing deferral logic was adjusted so that revenue recognition is posted in the original transaction currency, using the exchange rate from the invoice; in other words, the system was made to behave as one would expect.

Technically, this requires intervening in the deferral posting process. First, we add 3 fields – currency, amount, and rate to the Deferrals schedule table, and populate them:

				
					[ExtensionOf(classStr(SubBillDeferralScheduleCreate))]
final class SubBillDeferralScheduleCreate_Extension
{
    public static void initDeferralScheduleTable(
        SubBillDeferralAmountSource _subBillDeferralAmountSource,
        SubBillDeferralTransactionLineDeferral _deferralTable,
        SubBillDeferralScheduleTable _schedTable,
        boolean _usingShortTerm)
    {
        next initDeferralScheduleTable(_subBillDeferralAmountSource, _deferralTable, _schedTable, _usingShortTerm);
        if (_subBillDeferralAmountSource == SubBillDeferralAmountSource::DeferralAmount)
        {
            _schedTable.SubBillDeferralAmountCur = _deferralTable.SubBillDeferralAmountCur;
            _schedTable.ExchRate = _deferralTable.ExchRate;
            _schedTable.CurrencyCode = _deferralTable.CurrencyCode;
        }
    }
}

The form Subscription billing > Revenue and expense deferrals > Deferral schedules > All deferral schedules has been extended to reflect the new fields, too: 

Next, we make sure that the 3 values are carried through correctly as the invoice is posted and the final schedule lines are re-initialised from the posted invoice line:

				
					[ExtensionOf(tableStr(SubBillDeferralTransactionLineDeferral))]
final class SubBillDeferralTransactionLineDeferral_Extension
{
    public void updateFromTransaction()
    {
        next updateFromTransaction();

        if (! this.TransRecId)
        {
            return;
        }
        switch (this.SubBillDeferralSourceRecType)
        {
            case SubBillDeferralSourceRecType::SalesLine:
                if (this.SubBillDeferralTransactionType != SubBillDeferralTransactionType::SalesOrder)
                {
                    // Not supported
                    return;
                }
                SalesLine salesLine = SalesLine::findRecId(this.TransRecId);
                this.SubBillDeferralAmountCur = salesLine.calcGrossAmountExclTax(this.Qty);
                SalesFixedExchRate fixedExchRate = salesLine.salesTable().fixedExchRate();
                if (fixedExchRate != 0.0)
                {
                    this.ExchRate = fixedExchRate;
                }
                else
                {
                    ExchangeRateHelper helper = ExchangeRateHelper::construct();
                    helper.parmFromCurrency(salesLine.CurrencyCode);
                    helper.parmExchangeDate(this.TransDate);
                    helper.parmToCurrency(Ledger::accountingCurrency());
                    this.ExchRate = helper.getExchangeRate1();
                }
                this.CurrencyCode = salesLine.CurrencyCode;
                break;

            case SubBillDeferralSourceRecType::CustInvoiceTrans:
                CustInvoiceTrans custInvoiceTrans = CustInvoiceTrans::findRecId(this.TransRecId);
                this.SubBillDeferralAmountCur = custInvoiceTrans.LineAmount + custInvoiceTrans.SumLineDisc;
                this.ExchRate = custInvoiceTrans.exchRate();
                this.CurrencyCode = custInvoiceTrans.CurrencyCode;
                break;

            default:
                return;
        }

        this.SubBillDeferralAmountCur = abs(this.SubBillDeferralAmountCur);
    }
}

At last, these stored rates are used in the monthly revenue recognition (do not Summarise the journal lines!). This is the hardest part, because the class SubBillDeferralLedgerJournalCreate  is nearly impossible to extend in the D365 FO standard, as the developers either “privatised” or “internalised” all methods for no obvious reason. All.. but one. I’ll leave this adventurous Chinese hack uncommented:

				
					[ExtensionOf(tableStr(LedgerParameters))]
final class LedgerParameters_Extension
{
    public static boolean isChineseVoucher_CN()
    {
        boolean ret = next isChineseVoucher_CN();
        if (ret)
        {
            return ret;
        }

        str txt = con2Str(xSession::xppCallStack());
        if (strscan(txt, "SubBillDeferralLedgerJournalCreate", 1, 1000) > 0  &&
            strscan(txt, "assignChineseVoucherFromDefaultType_CN", 1, 1000) == 0)
        {
            ret = true;
        }
        return ret;
    }

}

The hack then ultimately triggers the actual update of the general journal line with the proper amount and rate:

				
					[ExtensionOf(classStr(SubBillDeferralLedgerJournalCreate))]
final class SubBillDeferralLedgerJournalCreate_Extension
{
    protected void assignChineseVoucherFromDefaultType_CN(LedgerJournalTrans _ledgerJournalTrans)
    {
        next assignChineseVoucherFromDefaultType_CN(_ledgerJournalTrans);

        SubBillDeferralScheduleNumber   schedNumber = subStr(_ledgerJournalTrans.Txt, 1, strFind(_ledgerJournalTrans.Txt, " ", 1, 20)-1);
        if (! schedNumber)
        {
            return;
        }
        SubBillDeferralScheduleTable deferralScheduleTable;
        deferralScheduleTable = SubBillDeferralScheduleTable::find(schedNumber);
        if (! schedNumber)
        {
            return;
        }
        if (deferralScheduleTable.ExchRate == 0.00 || deferralScheduleTable.ExchRate == 100.00)
        {
            return;
        }

        _ledgerJournalTrans.CurrencyCode = deferralScheduleTable.CurrencyCode;

        if (_ledgerJournalTrans.AmountCurDebit)
        {
            _ledgerJournalTrans.AmountCurDebit = CurrencyExchangeHelper::curAmount(
                _ledgerJournalTrans.AmountCurDebit,
                deferralScheduleTable.CurrencyCode,
                deferralScheduleTable.TransDate,
                UnknownNoYes::Unknown,
                deferralScheduleTable.ExchRate);
        }
        else
        {
            _ledgerJournalTrans.AmountCurCredit = CurrencyExchangeHelper::curAmount(
                _ledgerJournalTrans.AmountCurCredit,
                deferralScheduleTable.CurrencyCode,
                deferralScheduleTable.TransDate,
                UnknownNoYes::Unknown,
                deferralScheduleTable.ExchRate);
        }
        _ledgerJournalTrans.ExchRate = deferralScheduleTable.ExchRate;
    }
}

The result

…is simple: balances clear cleanly not only in accounting currency, but also in transaction currency. There are no residual amounts, no inconsistencies, and no need for explanations during reconciliation. A long-standing and widely discussed gap is effectively closed.

You may download the source code here: SubBillDeferral_with2currencies

Revenue recognition and deferred cost blog series

 

Further reading:

Revenue recognition with project budget shadow forecasts