Overview 

Currently xTupleERP supports a multi-company chart of accounts and consolidated financial reporting against that chart of accounts. However, the application in general does not support many complex requirements for real multi-company deployments such as privileges by company, sales account mappings by company and year end retained earnings roll over by company. To achieve company specific control in those areas the most practical thing to do is to simply create a separate database for each company. However, that arrangement can make consolidated reporting of financial data all but impossible.

The purpose of this functionality is to allow companies implemented on separate databases to report on a single consolidated financial statement. A utility that synchronizes trial balances across these databases will be developed for this purpose. Child databases will maintain company specific trial balance data and a parent database will receive copies of the data from each child. The new parent data will then be used to generate consolidated financial statements using the financial reporting engine.

Functional Requirements

Multi Company Consolidation will do the following:

• Set Up
  • Allow a company segment to be pointed to another database.
  • Check validity of database connection, chart of accounts and calendar.
  • Disallow any transactional or reporting activity on company segments that are pointers.
• Synchronization
  • Checks calendar alignment between child and parent
  • Imports chart of accounts from child
  • Imports trial balance from child for selected time period

All child databases must share the same base currency as the parent.

Multi-Company Financial Consolidation will only be available on xTupleERP Standard Edition or higher. It will synchronize between one xTuple database and another. Synchronization with third party financial systems is outside the scope of this specification.

A typical synchronization scheme is pictured below where the blocks on the left represent trial balances in separate databases being copied and consolidated together with another chart of accounts.

Multi Company Consolidation Financial Flow

New Terms and Definitions

Child Database

• A database where data is generated and maintained.

Parent Database

• A database where account and trial balance data is copied to but can not be altered.

Synchronize

• To update the parent database so its data set matches the child database.

Related Existing Functionality

The application currently supports a company segment on the chart of accounts. This segment can be leveraged by the Financial Reporting Engine (FRE) on any xTuple database.

Similar and Related Requests

There is a detailed discussion topic on potential ways to handle multi company issueshere.

The following Mantis issues are also related to this topic:

• 6788 Multi-Database Financial Report Engine Consolidation (The request driving this specification).

• 6200 Year end equity and other global accounts need to operate by company.

• 5401 Allow User Available Privileges to be set by company.

• 5353 Allow G/L Configuration accounts to be set up by company.

• 5239 G/L Module Configuration prevents having more than one company.

These two issues must be resolved in order to implement this feature:

• 5356 Duplicate Company Numbers are Possible.

• 6857 Changing the company code does not change chart of accounts

Conflicting Features

None anticipated

User-Level Functionality

Users will create charts of accounts using distinct company segment codes in each database they want to consolidate. One company database will be selected as the parent database. Company segments for external companies that reference the databases where the child data actually resides will be set up on the parent. Chart of accounts and trial balances for account numbers using the referenced company segment will be imported from the child database to the parent to enable consolidated financial reporting.

All UI files used for mock up screen shots are attached to this specification.

Window Changes

Configure General Ledger

A new check box option will be added to the company group in General Ledger configuration titled "Enable External Company Consolidation." Checking this box will set a database metric that allows users to set up and synchronize external company databases. This option will only be available on xTupleERP Standard Edition or higher.

Company Number

When Multi Company Consolidation is enabled, the set up window for company segments will be altered as pictured below:

Users will be able to check an "External Company" check box which requires server, port and database fields to be filled in when checked. If any accounts have been created using this company the External Option will no longer be editable. This means if the company is flagged as external, the "External Company" group will no longer be checkable; if the company is not external the entire External Company group will be disabled.

By default the Test button is disabled. Once users have filled in all connection data the Test button will be enabled. When clicked, the user will be prompted with the standard log on screen prompting authentication for the external database then a series of checks will be run, as described in the Algorithms section below, and the user will be given feedback on the validity of the connection.

Account Number

The Account Number window will need to be modified to only list company segments that are not flagged as external. Account numbers imported from external databases should be view only.

Synchronize Companies

A new window will be created called "Synchronize Companies." It will be found under Accounting > Utilities and will only be visible when external company consolidation is enabled. It also will only be enabled when users have appropriate permissions.

Users will be able to make multiple selections of periods and companies to synchronize. Only company segments flagged as external will be listed. When the user clicks update the system will synchronize the trial balances of the companies as described in the Algorithms section below. The user will have to authenticate against each server, port and database combination that exists; the program should automatically change server, port and database connection information automatically for each log in.

Report Changes

No changes are expected. The financial reporting engine should accommodate this feature as is.

Usability Considerations

The biggest potential usability issues are simply maintenance related. For accurate reporting users of this functionality would need to be well trained and implement strict procedures to ensure that company financial data is in a report-ready state before synchronizing, and to make sure that it is in fact synchronized. It is easy to envision a scenario where users notice a mistake on a consolidated statement, fix the problem on the child company database but forget to resynchronize the parent and wonder why the consolidated statement on the parent doesn't reflect the change.

Users must have a clear understanding of the FRE to take advantage of this feature.

Problems and Alternatives

One awkward problem is the question of what happens when a user changes a company segment code. This could easily break a synchronization scenario, especially if the fix for Issue 6857 was implemented. We could consider disallowing users to edit company segments for external accounts, but this could preclude situations where users need to be able to do this, for example if they acquired another company with a conflicting account scheme. Probably the best thing to do is give the user a warning that all child databases must be changed to match the parent if the company segment on the parent is changed. Since a child database is not aware of its relationship to the parent, it will not be able to give this warning. In any case this situation should be a rare exception.

It may be desirable to store user name and password information with the company information to make synchronizing databases less awkward. If we do this we should make it optional. A database encryption algorithm is beyond the scope of this specification, so if we did choose to implement the ability to save user name and password, we should warn the user that information will be stored in plain text on the database.

This specification explicitly states that multiple base currency synchronizations will not be supported, if only because that level of functionality is beyond the scope of the original request. Because xTupleERP does support multiple currencies, however, it should be possible to convert currency in future editions if that level of functionality is required.

Most other problems that would arise with this methodology would have to do with error trapping as described in the Error Handling section below.

Internal Design

Basic Algorithms

Testing the Connection

Launch the User log in window to receive user name and password
Attempt connection
If connection fails
  Display error message notification of connection failure
  Return
Else
  If child database is a different version
    Display error message notification of version incompatibility
    Return
  If child database has different base currency
    Display error message notification of base currency incompatibility
  If matching company segment does not exist on child database
    Display error message notification that company not found

Synchronizing Companies

This algorithm should be committed or rolled back as a single transaction to prevent the possibility of partial synchronizations.

For each company
Loop
  Execute Test Algorithm
  Store connection information
End Loop

For each company
Loop
  If periods selected on parent do not exist or exactly match on child database
    Display error message notification of calendar mismatch
    Rollback
    Return
  For each chart of accounts with matching company segment on child database
  Loop
    If account doesn't exist on parent create it
    If account exists, but doesn't match, update it
  Next Loop
  For each period selected
  Loop
    For each trial balance record on child company for an account with match company segment
    Loop
      If trial balance record doesn't exist on parent, create it
      If trial balance record exists, but doesn't match, update it
    End Loop
  End Loop
End Loop

Custom Widget Changes

The G/L Cluster widget should be modified so that accounts that use external company segments are not selectable or available on searches. Any transactional activity or reporting against an external company will not be allowed except via the FRE.

Schema Changes

This table displays fields that will need to be added to the company table in the public schema

Stored Procedure Changes

None anticipated. Since this functionality spans databases, the work will have to be done on the client.

Performance Considerations

Because trial balance records are course grained, there should be nominal load requirements even when synchronizing across a WAN.

Error Handling

The Basic Algorithms section specifies that for a synchronization to succeed, the following criteria need to be true

• The client must be able to connect to all child databases
• The child databases must be the same version
• The child databases must have matching periods
• The child databases must have accounts with matching company segments

If problems arise, users would have to identify and resolve these issues in order to synchronize.

We do not under any circumstances want G/L transactional data recorded against accounts with company segments flagged as external either by the xTuple UI, or an external interface. We should strongly consider implementing a trigger based check on the gltrans table to prevent G/L transactions from being made against external company accounts.

QA Considerations

Testing should be straight forward. Quality Assurance should make sure to test for errors listed above, and with a scenario that involves at least three companies.

Documentation Considerations

New documentation will need to be created. This specification could be used as the starting point for a wiki topic and the reference guide.

Release Considerations

This functionality is scheduled be completed for the 3.1 release of xTupleERP.

Attachment	Size
MultiCompanyUI.zip	6.12 KB