Produce the FEC (accounting entries file) with a modern BI solution

by October 27, 2020

Faced with the inability of some ERPs to automatically generate an FEC and its specifics, here is how it is possible using SAP tools to extract this file in accordance with government expectations.

What is the FEC?

Since 2014 the French government has required the Fichier des Ecritures Comptable (FEC) during a tax audit of a company. If the company is unable to provide it, then it is liable to a fine of €5,000 per year audited, as well as the rejection of the accounting records with a 10% increase in the duties charged to the taxpayer.

So what is the FEC?

The FEC is an electronic file containing all the accounting operations of a company over a fiscal year, this document must be composed of the following 18 fields:

  1. The Journal Code (JournalCode)
  2. Journal label (JournalLib)
  3. Accounting entry number (EcritureNum)
  4. Date of the accounting entry (EcritureDate)
  5. Account number (AccountNum)
  6. Account Name (LibAccount)
  7. Auxiliary Account Number (CompAuxNum)
  8. Auxiliary Account Name (CompAuxLib)
  9. Reference of the supporting document (PieceRef)
  10. Date of the supporting document (PieceDate)
  11. Entry label (EcritureLib)
  12. Amount Debit (Debit)
  13. Amount Credit
  14. Lettering of the writing (EcritureLet)
  15. Lettering date (DateLet)
  16. Delivery date (ValidDate)
  17. Currency amount (Currency amount)
  18. Currency identifier (Idevise)

NB: The fields must appear in the file as written in brackets above.

How to generate it?

There is a tool built into SAP ECC for extracting data called the Data Retention Tool (DART) which can be used to obtain the FEC.

In practice, the DART may encounter problems in extracting the FEC for the following reasons

  • Very high volume causing programme failure
  • FEC data not stored in database
  • Retained earnings not generated at the same granularity as the FEC

Also known as "Carry Forward", the carry forward is one of the specificities required for the validation of the FEC. The carry forward of a fiscal year is the whole of the accounting entries not balanced from the past years.

As DART cannot be used in our case, a hybrid model based on BW and HANA was chosen:

  • BW for its ability to retrieve the information needed to prepare the FEC using standard extractors
  • HANA for its computing power to generate retained earnings

In the rest of this article we will see the solution implemented by Bilink for a client to be able to generate its FEC every year.

Solution

Bilink was able to take advantage of the technologies provided by the client to implement a solution to extract the FEC.

 

Extraction of source data

To produce the FEC we need to use the statutory accounts, whose data are present in SAP ECC. Using the standard extractor "3FI_GL_XX_SI" we retrieve all the data for all the years present in SAP ECC in an Advanced DSO (aDSO) "General Ledger".

 

Data transformation

From there we decided to partition the data by fiscal year using HANA "Calculation Views" to generate these famous carry forward lines. Calculation Views were chosen for their ease of use as well as for the performance provided by HANA technologies.

The views created are very simple, only a projection pointing to the main DSO, the necessary filters (fiscal year, GL account) and a few calculated columns to define the default values to be put on the carry forward lines.

Two views can be used, one filtering the carry forward lines and another view for the current year's accounting lines.
These views are then attached and mapped to a Composite Provider. Each fiscal year has a dedicated ADSO.

Qlik cannot operate the Composite Provider as it needs to read physical tables. We load a dedicated ADSO for each fiscal year. Qlik will then access this ADSO.

Viewing and generating the FEC

Now that we have our objects containing the rows to be present in the FECs, we need to be able to extract them.
The major problem is the volume of data to be extracted: up to 20 million lines per year (.txt file of ~3GB)!

For this problem QlikSense is a tool of choice for its ability to handle large volumes.
In our case QlikSense played the role of extractor as well as the more traditional role of dashboard.

We have created two applications. The first one allows to extract data in the form of .txt files and to reload the QVD (Qlik proprietary files allowing to store the extracted data).
The second application is used to display the rows in a dashboard.
This allows the chief accountant to have a quick overview of the data in the FEC while checking that the accounts are balanced.

Here is an example of one of the pages of the "Dashboard" application (blurred data):

If your FEC has a very large number of lines (several million lines), then having an application to inspect your data becomes essential, as a simple spreadsheet will not allow you to process the entire document. You can also ensure that your file format is valid by running it through the government's free program (Link : Info.gouv). Please note that this application will not check the integrity of your data, but will validate the form of your file.

Conclusion

For a large number of companies, it is currently impossible to complete the FEC. Each case being different, it is necessary to know which solution is the best adapted to the various technical constraints.

Bilink can assist you in this process by investigating the most suitable options for your system landscape.

The following two tabs change content below.

Alexandre Chevé

Alexandre is a junior consultant specialising in Business Intelligence. Dynamic and rigorous, he has a solid knowledge of the SAP BI suite and SAP visualisation tools.
Share This