Automate ICO purchase invoice in Dynamics 365 Finance

On the current occasion that spoiled my Saturday: one legal entity posts an intercompany sales invoice, but the receiving entity does not automatically react. As a result, stock and intercompany balances start to diverge, because the sales side is already fully posted while the corresponding vendor invoice on the purchasing side is either missing or only created after manual intervention. In day-to-day operations, this leads to inconsistencies and unnecessary reconciliation effort. In the ICO direct delivery – “a chain of three” SO <- ICO PO <- ICO SO – this can be fully automated out of the box, but in the “chain of two” ICO SO -> ICO PO it cannot.

The standard process requires a user to actively navigate to the purchase order in the receiving company, click Intercompany tracing / Invoice journal (it switches the legal entities in the process), and manually trigger the “Generate intercompany invoice” function. Only after this step does the vendor invoice appear. The expectation is straightforward: once the sales invoice is posted, the corresponding intercompany vendor invoice should automatically be created in the receiving entity and appear there as a pending invoice, ready for review and approval. The system already contains all the necessary logic, but it simply does not execute it at the right moment.

The solution may be a surprisingly small extension at the right place. By hooking into the invoice posting process and calling the existing intercompany update logic immediately after posting, the missing step is automated:

				
					[ExtensionOf(classStr(SalesFormLetter_Invoice))]
internal final class SalesFormLetter_Invoice_Extension
{
    public void run()
    {
        next run();

        FormletterOutputContract outputContract = this.getOutputContract();

        if (outputContract)
        {
            CustInvoiceJour custInvoiceJour = outputContract.parmJournal() as CustInvoiceJour;
            if (custInvoiceJour && custInvoiceJour.InterCompanyCompanyId)
            {
                setPrefix("@SCM:IntercompanyGenerateInvoiceMenuCaption");
                custInvoiceJour.interCompanyUpdate();
            }
        }
    }
}

The key method is deliberately NOT wrapped into the same transaction as the main run(): a workflow or a closed period in the company of the recipient should not disrupt the seller’s operations.

Intercompany blog series

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

Filling PDF Forms with Electronic Reporting in Dynamics 365 for Finance

Dynamics 365 Electronic Reporting (ER) supports output into Excel, Word, and PDF formats. Excel and Word templates are well-covered in documentation and practice, but PDFs remain obscure – there’s only a short article Design ER configurations to fill in PDF templates – Finance & Operations | Dynamics 365 | Microsoft Learn from Microsoft.

The PDF/File component in ER is usable, it fills out static PDF forms. For example, one may take the VAT return or another tax declaration form, import into the ER configuration designer and get it filled out.

Technically, this resembles the old Dynamics Axapta approach using XFDF to inject data into form fields. That still seems to happen under the hood: you map values to fields, and the form gets filled in and flattened into a non-editable PDF. However, PDFs don’t support repeatable ranges like Excel – everything is fixed on the page. If there are – let’s say – several lines, then the subtotals below won’t slide on the PDF page. They’ll remain on the same page as placed on the PDF form, and remain on the exact place on the form.

To simulate Excel-like ranges, Microsoft introduced a naming trick. You create form fields named, e.g., LineNo1, LineNo2, …, LineNo10. In ER, you create a Field group containing a single LineNo field. ER fills LineNo1 with the first record, LineNo2 with the second, and so on. If only two records are present, only LineNo1 and LineNo2 get filled—the rest stay blank. You must not exceed the number of fields available in the form: 11 records will crash the report.

To prepare such a form, you need Adobe Acrobat (not the free Reader). Create a fillable form and assign field names. For repeated fields like headers or footers (e.g. Total_pages), it’s okay to copy-paste – Acrobat auto-numbers them visually (Total_pages#1, #2) but they’re still internally just one field, and ER will fill all copies identically. That is ideal for values repeated on every page.
For tables where every row must be unique, the fields must have unique names (LineNo1, LineNo2, etc.). Renaming manually gets tedious fast. A handy script Acrobat — Rename Duplicate Fields to Unique Fields | Try67 | Custom PDF tools (the script is commercial + Adobe Acrobat Pro is required) automates this: draw a rectangle over the fields, and it appends unique suffixes to overlapping fields across pages. You can prepare a single line of fields, copy-paste the line (do not shuffle but always keep adding them in the strict order from top to bottom: Acrobat keeps track of their numbers and sorts them internally), then start replicating blocks of lines until the page is full, copy-paste full pages in the Acrobat’s “Organize pages” tool, and then batch-rename everything.
Back in ER: in the Format mode, add a PDF/File object and point it to your PDF template. Use the button Import / Update from PDF once to pull in field definitions. Don’t use “Update from PDF” again – it can scramble your mapping. Grouping fields by suffix (e.g., LineNo1..10) doesn’t always work automatically, so you might have to manually create a field group and drop in the core field (LineNo) without suffix.
Apply ENUMERATE(Lines) to record lists to create a structure where @.Number gives you the index (1-based) and @.Value gives the record. Then, in the Mapping mode, set the Name property of the line field in the lower right corner to "LineNo"&TEXT(@.Number), then assign the value from @.Value.YourField as usual with the Bind button. By the way, the order of fields doesn’t matter – unlike Excel, where rows and columns are positional, the PDF is filled at once using a key-value approach (essentially, an FDF file).
Don’t exceed the number of fields in your form. There’s no pagination or overflow – ER won’t split lines across multiple copies of the form. You may limit the data source with the formula like WHERE(@.LinesEnumerated,@.LinesEnumerated.Number<=10) to harden the report. You may also design a custom warning if the number of lines exceeds a certain threshold: Validate Electronic Reporting on execution and show errors or warnings – Arcadi Burria.

In short, ER’s PDF support is usable and powerful enough but static. With proper naming and field repetition strategy, it’s good for things like tax forms, returns, invoices with known limits, etc. The main challenge is preparing the template, not the mapping itself.

Uncategorized

Automate ICO purchase invoice in Dynamics 365 Finance

Automate ICO purchase invoice in Dynamics 365 Finance

Intercompany blog series

Deferred revenue in foreign currency: closing the gap in D365

Deferred revenue in foreign currency: closing the gap in D365

The gap

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

The result

Filling PDF Forms with Electronic Reporting in Dynamics 365 for Finance

Filling PDF Forms with Electronic Reporting in Dynamics 365 for Finance

E-invoice and configurable business documents blog series