About the Avalara VAT Reporting Mappings

Before your financial data is sent to Avalara VAT Reporting, it is converted to the appropriate XML format using the predefined mappings. The mappings are defined in a Foundations Configuration Item custom metadata type record named Avalara VAT Reporting Integration.

For more information, see Avalara VAT Reporting Mappings Table.

The following table details the fields available on the Avalara VAT Reporting Integration record. For more information on the Foundations Configuration Item custom metadata type, see Foundations Configuration Item Custom Metadata Type Fields.

Avalara VAT Reporting Integration Fields

Field

Description
Label Label of the record. When creating a new record, enter Avalara VAT Reporting Integration followed by the value of Small Data. For example, if Small Data is 2, enter Avalara VAT Reporting 2.
Foundations Configuration Item Name API name of the record. When creating a new record, automatically populates from the label.
Process

Name of the process that the record relates to.

Warning:

When creating a new record, you must enter fdn:avalara:integration
Small Data Number of the record. The Avalara VAT Reporting Integration record with the highest number is used when generating the XML document. When you create a new record, increment this number by one. For example, if Small Data of the original record is 1, enter 2 for the new record.
Large Data JSON containing the field mappings. For more details, see Large Data JSON Keys.
Object Type Not used for the Avalara VAT Reporting integration. When creating a new record, leave this blank.
Object Field Not used for the Avalara VAT Reporting integration. When creating a new record, leave this blank.
Identifier

Identifier for the group of records.

Warning:

When creating a new record, you must enter FFA
Enabled Indicates that the record is enabled. When creating a new record, select this.

The Large Data field contains an array of JSON objects with keys and values that define the field mapping, as well as the countries and document types it applies to. The following table details the structure.

Large Data JSON Keys

Key

 

Description
SourceField  

API name of the Accounting field used to supply the value. This field is always on the Transaction or Transaction Line Items objects, or an object related to the Transaction object. For example, c2g__codaTransaction__c.c2g__TransactionDate__c. The related objects are the following documents:

  • Sales Invoice
  • Sales Credit Note
  • Payable Invoice
  • Payable Credit Note

The supported field types are the following:

  • Number
  • Text
XMLPath  

Name of the XML element the value is mapped to when generating the XML document. For example, TransactionDate.

If the element is a child element, the value also includes the parent node followed by a forward slash. For example, InvoiceProperties/InvoiceNumber.

Warning:

The XML path was defined using an XML schema provided by Avalara VAT Reporting and you must not change it.
DocumentType  

Type of document the mapping applies to. If omitted, the mapping is used for all document types.

You can enter one of the following:

  • SI for the Sales Invoice object
  • SCN for the Sales Credit Note object
  • PI for the Payable Invoice object
  • PCN for the Payable Credit Note object
  • BD for the Billing Document object

If the mapping applies to multiple objects, you can enter multiple values separated by commas. For example, if a mapping applies to sales invoices, sales credit notes, and billing documents, enter SI,SCN,BD.
Country  

ISO code of the country the mapping applies to. If omitted, the mapping is used for all countries.

If the mapping applies to multiple countries, you can enter multiple values separated by commas. For example, if a mapping applies to France and Spain, enter FR,ES.
ChildRelationship  

If the source field is on an object that is a child of the Transaction object, this specifies the API name of the relationship on the Transaction object.

Typically, this occurs when the source field is on the Transaction Line Item object. For example, c2g__TransactionLineItems__r. In this case the source field starts from the child object. For example, c2g__codaTransactionLineItem__c.c2g__HomeTaxableValue__c.
ValueTransformation  

When the source field is a picklist, this defines the mappings between picklist values and the values accepted by Avalara VAT Reporting. The structure is an array of key-value pairs, where the key is the Accounting picklist value and the value is the Avalara VAT Reporting value. For example:

Copy 
{
  "SourceField": "c2g__codaTransaction__c.c2g__TransactionType__c",  
  "XMLPath": "InvoiceProperties/DocumentType",
  "ValueTransformation": { 
    "Invoice": "0",
    "Purchase Invoice": "1",
    "Credit Note": "2",
    "Purchase Credit Note": "3"
  }
},
Required  

If set to true, the source field for the specified document types and countries cannot be blank. When an Avalara VAT Reporting background process is run, transactions with a blank required field are not sent. Their Avalara VAT Reporting Status is set to "Validation Error".

Note:

There is no need to set this for a field you are already validating when posting a transaction.
ParentObject  

If the source field is from an object that is a child of another object, this specifies the API name of the Master-Detail or Lookup object that the child object relates to.

This typically occurs when the source field is from a document object, for example Sales Invoice (c2g__codaInvoice__c). In this case the source field starts from the child object. For example, c2g__codaInvoiceLineItem__c.SCMFFA__Item_Name__r.SCMC__Commodity_Code__c.
Conditions  

If conditions are defined, and all conditions are satisfied, the XML element is created when generating the XML document. If that is not the case, the XML element is skipped. For example:

Copy 
{
    "SourceField": "c2g__codaInvoiceLineItem__c.SCMFFA__SCM_Invoice_Line__r.SCMC__Sales_Order_Line_Item__r.SCMC__Supplier_Site__r.SCMC__Mailing_Country__c",
    "XMLPath": "InvoiceProperties/IntrastatDocumentData/CountryDispatch",
    "DocumentType": "SI",
    "ChildRelationship": "c2g__InvoiceLineItems__r",
    "ParentObject": "c2g__codaInvoice__c",
    "Conditions":[
        {
            "SourceField":"c2g__codaInvoiceLineItem__c.SCMFFA__SCM_Invoice_Line__r.SCMC__Sales_Order_Line_Item__r.RecordType.Name",
            "ChildRelationship": "c2g__InvoiceLineItems__r",
            "ParentObject":"c2g__codaInvoice__c",
            "Operator":"equals",
            "Value":"Drop Ship Item"
        }
    ]
}
SourceField

See SourceField. The supported field types are the following:

  • Checkbox
  • Number
  • Text

If the field is of type "Checkbox", you must enter either true or false without quotation marks. For example:

Copy 
{
    "SourceField": "c2g__codaTransactionLineItem__c.c2g__HomeValue__c",
    "XMLPath": "InvoiceProperties/InvoiceLines[]/ValueTaxableBasis",
    "ChildRelationship": "c2g__TransactionLineItems__r",
    "Conditions":[
        {
            "SourceField":"c2g__codaTransactionLineItem__c.MyCustomCheckboxField__c",
            "ChildRelationship": "c2g__TransactionLineItems__r",
            "Operator":"equals",
            "Value": false
        }
    ]
}
ChildRelationship See ChildRelationship.
ParentObject See ParentObject.
Operator

The following conditional operators are supported:

  • equals
  • not equals
Value The value the source field value is compared to.

Customizing the Default Mappings

You can customize the default mappings by creating a new Avalara VAT Reporting Integration record. This might be useful if you want to map a custom field rather an Accounting field.

Warning:

We recommend that you contact Certinia Customer Support before modifying the mappings.

To create a new mappings record:

  1. From Setup, click Custom Code | Custom Metadata Types.
  2. Next to Foundations Configuration Item, click Manage. All the existing records display.
  3. Click Avalara VAT Reporting Integration. If there are multiple records with the same label, open the one that has the highest value in the Small Data field.
  4. Click Clone.
  5. Leave the default value for the Process and Identifier fields.
    Note:

    If you change these values, the record won't be used by the integration.

  6. Increment the Small Data value by one. For example, if it is set to 1, enter 2.
    Note:

    The process for generating the XML document only uses the record with the highest Small Data value.

  7. In the Label field, add the Small Data to the end of the existing label. For example, Avalara VAT Reporting Integration 2. This makes the record easier to identify.
  8. In the Large Data field, edit the required mappings.
    Note:

    The JSON must contain all the mappings. Only edit the mappings that you want to change. Don't remove other mappings.

    Tip:

    Copy the JSON to a text editor with syntax highlighting to edit it more easily.

  9. Make sure that Enabled is selected.
  10. Leave the default value for the remaining fields.
  11. Click Save.
  12. [Optional] To verify that the mappings are correct, perform step 3 in the Avalara VAT Reporting Integration Feature Console feature. If the step is already performed, revert it and perform it again.

Special Mappings

When generating the XML document, the following mappings are processed slightly differently to standard mappings.

Last Invoice Number

The Last Invoice Number indicates the document number of the last invoice in a group of recurring sales invoices. You must not modify the source field or the XML path for this mapping. Otherwise, the XML might not be generated properly.

Reverse VAT

The Avalara VAT Reporting integration supports reporting a reverse charge. When any of the analysis transaction line items have the Reverse Charge Tax Code field populated, this is the tax code that is sent to Avalara, together with the Home Tax Value field.

Custom Fields and Source Field Mapping

Some source field values in the mapping are empty. This is because they do not exist in Supply Chain Management. To create an accurate Intrastat report, you must follow the steps below and create your own custom fields in Supply Chain Management for those XML paths.

Note: This is especially useful if you need to distinguish between Tax Number and VAT Number. You can do this by adding a custom field for the VAT Number. After creating the VAT Number custom field, you must include that custom field in the relevant JSON.

In the example below, a custom field called Mode of Transport has been created in Supply Chain Management. You must do this for all lines in the Avalara VAT Reporting Mappings Table with no value in the Source Field column:

  1. Create the custom field in Supply Chain Management. The object you choose is dependent on the mapping. For example, if you are mapping ModeOfTransport (see Avalara VAT Reporting Mappings Table) the custom field must be created in the Sales Order Line Item object for both invoice and credit documents.
  2. Create the full field path from the Accounting object to the custom field created in Supply Chain Management. The mapping source field must start from the corresponding Accounting object and navigate to the object relationships in order to retrieve the value from the new custom field. For example:
    c2g__codaInvoiceLineItem__c.SCMFFA__SCM_Invoice_Line__r.SCMC__Sales_Order_Line_Item__r.Mode_of_Transport__c
    In this example, because the mapping is generated for each line, it begins from the Sales Invoice Line Item object API Name, and includes all object relationships necessary to reach the new custom field.
  3. Clone the Avalara VAT Reporting Integration custom metadata.
  4. Edit the existing empty mapping to add the source field built in step 2. For example, for ModeOfTransport the final mapping would look like this:
    5. Copy 
    {
       "SourceField": "c2g__codaInvoiceLineItem__c.SCMFFA__SCM_Invoice_Line__r.SCMC__Sales_Order_Line_Item__r.Mode_of_Transport__c",
       "XMLPath": "InvoiceProperties/InvoiceLines[]/IntrastatData/ModeOfTransport",
       "ValueTransformation": {
           "Sea Transport": "1",
           "Railroad": "2",
           "Road": "3",
           "Air": "4",
           "Postal Delivery": "5",
           "Networks": "7",
           "Internal Water": "8",
           "With Own Means": "9"
       },
       "Required": "true",
       "ChildRelationship": "c2g__InvoiceLineItems__r",
       "ParentObject": "c2g__codaInvoice__c",
       "DocumentType": "SI"
    }
  5. Repeat these steps for each empty source field value in the mapping.