|
How Tables are Consolidated |
  
|
|
How Tables are Consolidated |
|
When you set up Company Consolidation, you specify a primary company by entering it at the top of the list. When the consolidation process starts, the target company (the consolidated company) is effectively cleared out and the primary company is copied into its place. If you were to stop the process at that point, the consolidated company would look almost exactly like the primary company.
After the initial copy of the primary company, the program turns it attention, in order entered, to the other companies. It goes through all the tables in the list below and performs one of the operations listed below. The categories describe how Company Consolidation will treat records that have the same key. For example, if the consolidation company already has a customer number or a G/L account that is found in one of the other companies, how will that record be treated? There are four basic ways to handle a duplicate record:
| • | Combined – In this case, the record that already exists in the consolidated company will be kept. This consolidation company record might have come from the primary company, or it might have come from one of the other source companies prior to the one that is currently being processed. Either way, the first one to be written to the consolidation company is the one that is kept. However, in some cases there are fields which are analyzed individually and treated separately. |
| o | Summed – For some fields, such as CreditLimit in the AR_Customer table, the values in the source company are added to the value in the consolidated company. |
| o | Maximum – In some cases, such as the LastActivityDate or LastPaymentDate in the AR_Customer table, the most recent value is stored in the consolidated company. |
| • | Change to Create Unique Key – For some tables, it is important that each record remain unique so, the key value, or document ID, is converted. Sales orders, invoices, and purchase orders, commonly referred to a document, are examples of this. For these a new number is created by taking the source company code and adding a four-character sequence number to make it unique. Every time that document appears in any table, it is converted to the converted document number. |
| • | Increment Sequence – Many tables have records that must be unique, but they are made so with the use of sequence numbers in the key. In these cases, the consolidation program will simply increment the sequence number to make sure that the source record finds a unique place in the consolidated table. Keep in mind that often there is also a conversion happening before the record is being written, as in invoice transactions or invoice history. |
| • | G/L Account Combined – G/L Accounts deserve special mention because there is a special way they are identified. The formatted G/L account is actually “combined”, meaning that when two companies have the same formatted G/L account, the information from the first company encountered will appear in the consolidated company. However, the underlying unique key for the G/L account record is the Account Key. This is a nine-character code that is stored in all the various tables that reference G/L accounts. For example, when a sales order line shows a G/L account, what is actually stored in that detail line is not the formatted account, but a nine-character Account Key. For example, the formatted G/L Account may be '1400-001-002:100', but the internal Account Key may be '14A011234'. Even if two companies have the same formatted G/L account, the Account Key for that account is probably different between the two companies. When consolidlating, the program creates an Account Key conversion process to make sure that any reference to a specific formatted G/L account will use the same unique Account Key and new new account copied to the consolidated company will get a unique Account Key assigned. |
Below is a list of all the tables that are consolidated by ASI Company Consolidation. For each table we try to say how the duplicate records are handled using the four categories described above. For each table we will list the fields that are converted, incremented, summed, maximized or minimized.