Sometimes you pay Reverse Charge in D365

Sometimes you pay Reverse Charge in D365

Under the “reverse charge system” it is the company receiving the service that is liable to pay the VAT, and not the company providing the service. Normally, the net amount of VAT to pay is offset by the same amount of the receivable VAT, and the tax burden remains 0. But not always: sometimes the tax office wins. These are 2 common cases:

    • A service was provided by an EU entrepreneur who had no EU VAT number. The invoice was VAT excluded, but I had to calculate the VAT payable (reporting code 1157 „Steuerschuldübergang bei Bezug gemäß § 19 Abs. 1 zweiter Satz, § 19 Abs. 1c“ in Austria, code 47 „Steuer, im Inland steuerpflichtige sonstige Leistung eines EU Unternehmers § 13b Abs. 1 UStG“ in Germany), while the VAT receivable was nil (otherwise reported as 1166 „Vorsteuer: Steuerschuldübergang gemäß §19, grenzüberschreitende Leistungen“ in Austria or code 67 „Vorsteuer aus Umsätzen nach §13b Abs. 2 Nr. 11“ in Germany).
    • In the case of the self-billing (Gutschriftverfahren) a foreign agency may pay money to an entrepreneur by issuing an invoice on her/his behalf. If the entrepreneur does not have a VAT registration, or it is a small business, the VAT receivable cannot be recognised and the entrepreneur becomes liable for tax. If you register the agency as a customer it is a no-brainer, but the problem is in the non-continuous invoice number issued by the agency.

Solution in Dynamics 365 for Finance

The common business process distils into 3 steps:
– One receives an invoice (or a self-billed credit note) from a supplier (vendor) over €100
– One pays €100 to the supplier (or becomes €100 from the supplier)
– One pays €20 (or €19 in Germany) to the tax office
 
To implement this in Dynamics 365 for Finance, we activate the Use tax in the VAT group (en-us: Sales tax group) as usual (see Minimalistic EU VAT configuration in D365):

On top of that, the special VAT code (here: ServEUnond) for the non-deductible Reverse Charge receives Non deductible % = 100% in the tax code value:
 
The use tax VAT from the supplier invoice calculated in this way “elongates” the original expense, the GL voucher reflects the fact that the service effectively becomes more expensive by 19% (in DE).
This may be considered right or wrong, but a different expense account such as 7xxx “VAT, non-deductible” is not possible in standard Dynamics 365. If needed, this tax expense may be reallocated to a different account manually at the end of the period.

The monthly/quarterly Tax > Declaration > VAT > Settle and post VAT operation does not find any VAT receivable to offset the VAT payable and transfers the liability into the Tax authority’s payable account:
Bingo!

In the case of self-billing, the VAT payable is subtracted from the revenue: the credit note is booked as a revenue via a specially prepared procurement category, for example. The VAT “contracts” the revenue, and in order to do so, the rate of the tax must be negative (!), i.e. -20% (or -19% in Germany). The Negative VAT percentage must be activated first at the tax code record. 

Cross-company data sharing vs. Duplication in Dynamics

Cross-company data sharing vs. Duplication in Dynamics

Cross-company data sharing

 
Cross-company data sharing in Dynamics 365 for Finance and SCM (see System administration > Setup > Configure cross-company data sharing) supposed to be a valid replacement of the virtual companies in Dynamics AX2012 and earlier. I don’t know how about you, but I get the error “Table … is a Main table as specified by the table group and may not be shared unless its Data Sharing Type is Duplicate” every time I wish to share something useful.

It looks for the TableGroup property in the D365 table metadata and practically rejects any table which is not a Group or does not have a simple primary key. What is left for sharing? Some little configuration tables such as the Delivery terms (INCOTERMS), Payment terms ans so on: only these have the TableGroup = Group.  This rules out the most interesting tables in Dynamics such as the InventTable (Product list), CustTable (Customers) or VendTable (Suppliers) (well, this is not entirely true: for the latter there is a dedicated Feature Customer and Vendor master data sharing available for activation). Namely, these have TableGroup = Main.

The TableGroup property of the standard tables is not extendable. The developers left a backdoor: a Main table with the property DataSharingType explicitly set to Single or Duplicate may be eligible too. CustTable is an example. Needless to say, the DataSharingType is not extendable either. There seems to be a yet another backdoor in the form of the SysDataSharingTypeTableConfiguration table where this DataSharingType is supposed to be configured. However, this feature is protected by the flight EnableSysDataSharingTypeTableConfiguration. As with every flight it is very promising and tempting but it remains hard-locked in any production environment.

Dead end? Not quite.

Recurring ‘Copy into legal entity’ data project 

 
In Data management, create a new data project of the Copy into legal entity kind, select the destination company(ies) and the appropriate entity or entities:

It should not grab unfinished work, though. In this example it is obvious to only consider an approved bill of materials. The BOM lines do not have an approved  flag, but if there is no BOMId, no line can be copied either, i.e. it is sufficient to only filter the BOM headers. In general, you should take care of the data consistency. In the particular case of the bills of materials, they should not contain any unknown items or refer to any local inventory dimension such as the site, warehouse or the warehouse location: ensure the default sites in all companies in question have the same code “STD” or “DEF”, and set the Resource consumption checkbox in all lines to make them warehouse-agnostic. The Approver is not a problem, since the employee list is technically shared across all legal entities.

Click Copy into legal entity in batch and set a Recurrence e.g. every weekday. The data project may be run over and over again in a cycle copying the same data, but it is not a problem as it works in the UPSERT mode in Dynamics. However, as the data grows the data project runs slower. We should restrict the set and take only the changes of the day, for example. If you are lucky, there will be a ModifiedDateTime column you can use in the Filter:

(here with a Join and an advanced moving date query).

Find and merge a DimensionAttributeValue

Find and merge a DimensionAttributeValue

Recently I was given an opportunity by an American partner to develop something around dimensions in Dynamics 365 for Finance. A few years ago, my blog Get a cost center in D365FO resonated quite a bit, this time it goes deeper.
The task is generally to enrich a source document with a dimension [segment] according to the custom business logic, and it is one of the most common customizations in D365FO.

In this particular case, I had to put the Intercompany dimension into the GL transaction every time the respective company was involved in an IC exchange. Naturally, the IC dimension should be an entity-backed one, it should be! A few times I had a debate why didn’t we make a custom [free text] dimension for the intercompany: some legal entities were not in the D365 implementation scope [yet]. “At the headquarters D365 will only be rolled out in 2028!” The argument is flawed, since it only takes 3-5 minutes to create a new legal entity in D365 with the given ID and name.

The task can be decomposed into the following steps:

  • Identify the dimension attribute in question.
  • For the given legal entity ID (DataAreaID), find the corresponding attribute value.
  • Find a DimensionAttributeValueSet record for the given value, where the IC is the only segment populated.
  • Merge it with the existing dimensions of a transaction or a source document.

Identify the dimension attribute

The below dimension is backed by the Legal entity table CompanyInfo, in the demo company USMF it bears the name LegalEntity:

We won’t look it up by the name though, but the constant BackingEntityType, which is the number of the table. The developers did not use the TableID of the CompanyInfo directly, they abstracted the backend table with a view DimAttribute* as they did for every other ‘back-able’ entity in D365FO (Customers, Suppliers, Assets etc.) to only return the Key=RecId and Value=the symbolic 4 letters code of every legal entity, here in pseudocode *):

CREATE VIEW DimAttributeCompanyInfo
AS SELECT RecId AS KEY, DataArea AS VALUE, …
FROM CompanyInfo

*) I call it pseudocode, since a CompanyInfo does not physically exist in the database. It’s rather a DirPartyTable record. This is probably the reason why the views were put on top: the DirPartyTable does not only hold legal entities but also cost centres, departments, other organisations.

The below snippet will return the RecId of the dimension LegalEntity in D365 demo database, its performance is reinforced with the global cache:

public class DimensionICHelper
{
private const GlobalObjectCacheScope sgocScope = classStr(DimensionICHelper);
private const int sgocKey = 1;
public static DimensionAttributeRecId getICAttributeRecId()
{
SysGlobalObjectCache sgoc = classFactory.globalObjectCache();
DimensionAttributeRecId ret;
[ret] = sgoc.find(sgocScope, [sgocKey]);
if (! ret)
{
ret = (select firstonly RecId from DimensionAttribute
where DimensionAttribute.BackingEntityType == tableNum(DimAttributeCompanyInfo)
&& DimensionAttribute.DimensionKeyColumnName /*active*/).RecId
;
sgoc.insert(sgocScope, [sgocKey], [ret]);
}
return ret;
}
}

See also Get a cost center in D365FO.

Find the corresponding attribute value

The financial dimensions do not relate to the original records in the backing table (aka EntityInstance; here: CompanyInfo) but the global DimensionAttributeValue records. These only store references to the “Entity Instances”. The below code returns a DimensionAttributeValue for the given DataAreaID legal entity symbol:


public static DimensionAttributeValue getICAttributeValueByDataAreaId(DataAreaId _value)
{
DimAttributeCompanyInfo dimAttributeCompanyInfo;
DimensionAttributeValue icAttributeValue;
select firstonly icAttributeValue
where icAttributeValue.DimensionAttribute == DimensionICHelper::getICAttributeRecId()

exists join dimAttributeCompanyInfo
where dimAttributeCompanyInfo.Key == icAttributeValue.EntityInstance
&& dimAttributeCompanyInfo.Value == _value;

if (! icAttributeValue)
{
icAttributeValue = DimensionAttributeValue::findByDimensionAttributeAndValue(
DimensionAttribute::find(DimensionICHelper::getICAttributeRecId()),
_value,
false,
true); // Create if needed

}
return icAttributeValue;
}

The DimensionAttributeValue::findByDimensionAttributeAndValue() call is for an improbable situation where the backing record exists but its DimensionAttributeValue replica has not been created yet. They only do it as the legal entity (in general, an Entity Instance) is used as a dimension for the first time somewhere.

Try it for yourselves: in the demo AxDB, only USMF, USRT, USSI and DEMF are present in the DimensionAttributeValue table, while the General ledger > Chart of accounts > Dimensions > Financial dimensions, Dimension values form shows all 25+ potential ‘candidates’. Weird.

Find or create a DimensionAttributeValueSet

Next, let’s convert the dimension value found into a dimension “set” of only one entry, e.g. {USMF}. This corresponds to a DefaultDimension (the one without the MainAccount segment inside) of this kind:

Again, this Set may not exist. For example, imagine a holding with 100 companies, but only 4 are in intercompany relations with each other. Then the number of the DefaultDimensions will be in the range 0..4. What, zero?! It depends if any historical business case between the companies X and Y has ever been recorded in this D365 instance.

This is why the code is what it is:

public static DimensionDefault getDefaultDimensionForCompany(DataAreaId _value)
{
DimensionAttributeValueSetStorage valueSetStorage = new DimensionAttributeValueSetStorage();
valueSetStorage.addItem(DimensionICHelper::getICAttributeValueByDataAreaId(_value));
return valueSetStorage.save();
}

Merge the value with the existing dimensions

Finally, at some extension point we will be using the above DimensionAttributeValueSet according to some custom business logic. There may be 3 options:

  • We use the ‘naked’ default dimension as is (very seldom)
  • We merge it with another Default dimension in a master data record or in a source document (example: a new customer representing the IC company immediately becomes this segment populated)
  • We merge it with a LedgerDimension i.e. with a dimension set where the Main Account segment is populated (example: a GL transaction or a GL journal line of the Ledger AC type)

The 3rd case is the hardest since we have to convert the DefaultDimension into a LedgerDimension first, then merge:

DimensionDefault icDimensionDefault = DimensionICHelper::getDefaultDimensionForCompany(curExt());
LedgerDimensionBase icLedgerDimension = LedgerDimensionFacade::serviceCreateLedgerDimForDefaultDim(icDimensionDefault, ledgerJournalTrans.LedgerDimension);
ledgerJournalTrans.LedgerDimension = LedgerDimensionFacade::serviceMergeLedgerDimensions(icLedgerDimension, ledgerJournalTrans.LedgerDimension);