This is an optional and billable feature available to companies who run more than one server and/or database instance of the software. In these cases, each database or server instance is typically considered a separate entity. This feature takes selected data from the individual databases and combines that data in a separately maintained "roll-up" or corporate database with its own application server instance. Individual databases don't have to exist on the same server, but must be accessible from the corporate server. For the most part, the corporate or "roll-up" database would contain limited data for inquiry, reporting, and dashboard use only. Except for any maintenance required for setup, this additional server instance would not be used for performing file maintenance (data changes to customers, items, etc.) or processing any types of transactions. Except for the production of certain financial statements, the use of the software will be minimal, and data would be accessed via other methods (custom reports, etc.).

Companies interested in implementing a corporate roll-up instance should contact our marketing department. This feature requires configuration and must be enabled by support or development personnel.

Before we continue, we will refer to a corporate server/database using the terms "corporate," "roll-up," and/or "consolidated." We will refer to databases from where the data is collected using terms such as "source," "location," or "member." The term "instance" is used to refer to any individual installation of the application on a server with an independent database.

Data changes made from the "consolidated" or "roll-up" server/database instance do not change/update any data on the source application instances from where the roll-up data was collected. We should point out that no effort has been made to block or otherwise prohibit such changes as this would require extensive changes to numerous programs and limited file maintenance is required for setup. Any users of a "roll-up" or consolidated instance should be made aware that database changes, except any specifically required for setup, are not suggested and may "break" linkages established between the "roll-up" data and the source databases. Additionally, the corporate or consolidated instance cannot also be used as a "live" system because ledger data is cleared and refreshed on a daily basis.

For summary (mapping) purposes, several new database tables and related data objects have been created that would only be used by the corporate instance.

Enable Multi-system SQL Table Consolidation

This check box indicates whether or not the current Spruce instance has consolidation currently enabled. This check box only provides functionality after support or development personnel have completed all required setup and configuration.

Product Category Definitions

This data grid is provided for defining product categories which each are to be later assigned to one or more groups to provide a more succinct summary of inventory sales if desired. Use of product categories is optional. The "Update" button is provided so that new Product Categories can be saved and made immediately available for assignment to product groups in the "Category Group Assignment" data grid (right). This makes entry easier by avoiding the need to choose Process (F12) after each addition. Product Categories can be removed or modified as long as they are not currently assigned (mapped) to any product groups (in the Category Group Assignment" data grid area). Deletion is done by selecting a row (using the selection button/block in the left most column) and pressing the Delete button on the keyboard or by choosing "Delete" from the alternate menu (usually accessed via a right-click).

Category Group Assignment

Once product groups have been defined from the Item Maintenance form in the corporate application instance, those groups can be mapped here in the data grid. Optionally, you can assign any previously defined product categories to the group. The same category can be assigned to multiple groups. Doing so provides an additional level of summary in addition to the one provided by the product groups themselves.

Corporate Account Selection

If your member (source) locations use corporation codes to link accounts together, corporations that you want to summarize in the corporate (consolidated) application instance would be indicated here. In order to select a corporation, the corporation code would need to first be defined (from the Account Maintenance form).

Vendor Code Selection

To summarize vendor information, vendors would need to be defined in the consolidated/corporate Spruce instance beforehand. After being added via Vendor Maintenance, those vendors can then be mapped here.

What data is collected and summarized?

The following data is collected and merged into the consolidated or "roll-up" Spruce database:

•Ledger Accounts (Chart of Accounts), Account Totals (Actuals), and Account Budget figures (if any).

Two (2) new tables are used for mapping the account totals and budget figures for each source Spruce instance being included in the roll-up. These match the original structure of the source tables with the one exception being that these table have an additional column for the source instance's identifier.

Consolidation of the ledger data requires that the "roll-up" or corporate database have ALL the ledger accounts used by the member or source instances -- and -- requires that every source instance's Chart of Accounts be structurally similar to both the corporate (roll-up) and all other source Chart of Accounts. By "structurally similar," we mean that all member locations should have the same fiscal year and accounts with the same number should be used for the same purpose in all locations and that the numeric ranges defined for each account type (assets, liabilities, expenses, etc.) match across all locations. It is possible for different locations (sources instances) to have different accounts to a degree; however, ALL accounts must have a matching account in the corporate/roll-up instance. We are assuming that in a typically deployment that the corporate entity is responsible for accounting at all locations. Any additions or other modifications made to a source or member location's Chart of Accounts should be duplicated in the corporate or "roll-up" instance.

We will assist with the initial population of data for the Chart of Accounts based on the source locations providing the data. Any later changes to the Chart of Accounts must be done manually in the corporate instance and is the responsibility of the end-user. The Chart of Accounts is defined from the Database menu in the General Ledger area.

On a daily basis, a scheduled job runs and retrieves data from all source locations. This data populates new consolidated tables. Once populated, existing data in the corporation instance's "live" ledger tables is cleared and replaced by summary records based on the data collected. Once the "live" data is populated, an income statement and balance sheet can be run. This is the primary reason that the consolidated or corporate Spruce instance cannot be used for other processing, etc.

•Product Groups (Inventory)

Product groups are codes that are required for inventory organization. Product groups are linked to a numeric integer value between 1 and 255. Within each group are between 1 and 255 section codes. Both group and section codes have an associated text description. Similar to the implementation used with General Ledger accounts, ALL of the product groups used by member/source instances must be comparable. When member/source instances use the same group, they should be used for the same type of product. Furthermore, items should be consistently assigned to the proper groups across the member/source locations so that a particular product's sales information is included in the same product group and category totals at the corporate level.

Groups and sections don't need to exist in all source instances; however, the corporate instance should minimally have all groups (each group requires at least one section) defined that the corporate entity wants the ability to view totals for. No section totals are provides, so adding all the section codes is not necessary. We will assist with the initial population of group and sections; however, later changes to group/section product codes is a manual process and the responsibility of the end-user. Group-Section Codes are defined from the Item Maintenance form (available from the Database menu in various areas such as Point of Sale, Inventory, and Purchasing).

Once product group (and section) codes have been defined in the corporate Spruce instance, you then link product groups to general categories. Categories only exist on the corporate side, not at each source location. Each category is comprised of a code and text description. Once added (from this form), the product categories are assigned to product groups (also from this form). Different product groups can be assigned to the same category or each category could be an independent group depending on how you want to view summary data.

Sales (includes returns) and cost of sales totals are collected on each member Spruce instance as a summary by product group. These figures are maintained as data for each location (by site identifier), year, month, category, and the group number. Section totals are not maintained. This data comes from the stock value tables in the member (source) locations. Individual item details are not maintained or provided and there is no need to define any items in the corporation instance.

•Customer Class and Corporation Totals

Similar to how inventory data is summarized by product group (and category), customer data is also summarized in a similar fashion. In this case, customer classes and corporation codes are used for the summary. Customer classes and corporation codes are both optional for customer accounts. In order to provide summaries of customer totals, customer accounts in the member or source locations would have to be assigned to either a class code, corporation code, or both. Accounts in the member (source) locations that are not assigned to a class code or corporation code would not be included in the corporation's summary totals.

Corporation Codes are used to link individual accounts together when those accounts belong to a larger company (corporation). For example, a company may have different development projects. Each development is a separate account and each lot within the development is a job linked to the account. The accounts are linked together by the corporation code. Corporation codes provide limited ability for inquiry and reporting in the member (source) locations.

Customer Class codes are optionally used for categorizing customers. These might be used to classify accounts owned by contractors vs. homeowners, for example. Class codes are not explicitly defined in the consolidated instance; however, they are associated with the data.

Both types of codes can be created (or assigned in member locations) from the Account Maintenance form accessible from the database menus from either the Point of Sale or Receivables areas.

Customer Classes don't need to be defined in corporate database. Corporation Codes would need to first be defined in order to be accessible in parameters for mapping. Corporation Codes are only summarized if they are mapped in (this form) Parameters. Individual customer account balances and totals are not maintained and customer accounts don't need to be added to the consolidated database.

•Vendor Totals

Vendor totals are summarized when the vendor has been added to the corporation database, the same vendor (identified by code) exists in the member (source) locations, and the vendor has been mapped in parameters for summary. The same vendor codes would need to be consistently used in all member (source) locations for the summary to be accurate. Vendors are used for Payables processing for purchases, installed sales 3rd party labor, and other expenses (utilities, etc.). Vendors can be defined using the Database menu in several areas including Purchasing and Payables. We will assist with the initial population of Vendor data in the consolidated/corporate instance; however, later file maintenance is the responsibility of the end-user.

Minimally, new vendors need a code and name as well as any required settings; however, the vendors are only used for selection. Vendor inquiries and other software areas that access vendor data won't reflect the consolidated totals.

How do I access this data?

Once data has been populated, limited data is accessible from the application. We envision corporate/consolidated data access to be primarily accomplished by using custom reporting and/or custom dashboard controls (the end-user's responsibility).

The only consolidated data that can be accessed directly from the application on the corporate system relates to General Ledger area. This is limited to running either a Balance Sheet or Income Statement financial statement. This includes the ability to use the budget comparison and other features available with these reports. Other types of statements that would rely on detailed data, such as a Trial Balance, drill down options, etc. will not function. We should also point out that account changes in any source location can affect the accuracy of the financial statements. The modification or additional of ledger accounts without corresponding changes on the corporate side can cause financial statements to not balance or reflect all account balances, etc. Any financial issues requiring research as well as attempts to tie-out numbers are the sole responsibility of your company. Any support or development assistance provided toward issues or questions regarding the financial data reported will be considered a billable service and we reserve the right to not provide assistance in such cases.

We should point out that although data is collected in the corporate or consolidated Spruce instance's database, existing reports, inquiries, and maintenance forms won't access or reflect this data. The only exception to this is limited General Ledger data as mentioned here.

Database Structure

There are ten (10) new SQL tables that are used for this feature. The listing below includes each table and the relevant data fields (columns) the tables contain. Certain general columns have been left out (optLockAllChg, AuditInfo, and AuditTime). These are found in all or most tables and are used for locking and audit information. In the listing below, the numbered items are the SQL table names and below each is a listing of the column names along with the data type and a short description of what type of data is maintained in the column. This information is provided to assist you with reporting or other SQL based inquiry.

1.ConsCategory

This table maintains the user-defined Categories that are for assignment to product groups. Click here to view additional details about the column layout of this table.

2.ConsCustomerCorporateTotals

This table maintains Customer Account Totals by Location, Corporation Identifier/Code, Year, Month, and Day of Month. Click here to view additional details about the column layout of this table.

3.ConsCustomerTotals

This table maintains Customer Account Totals by Location, Customer Class Code, Year, Month, and Day of Month. Click here to view additional details about the column layout of this table.

4.ConsGLAccountTotalsActual

Ledger Actual Posted Totals by Site Location, Account, and Fiscal Year. This table is used for maintaining the location actual figures that are later consolidated and then used to update the "live" corporate actual tables (which may be used for reporting). This table contains details by location for each ledger account with budget records. Click here to view additional details about the column layout of this table.

5.ConsGLAccountTotalsBudget

This table is used for maintaining the location budget figures that are later consolidated and then used to update the "live" corporate budget tables (which may be used for reporting). This table contains details by location for each ledger account with budget records. Click here to view additional details about the column layout of this table.

6.ConsGroupCategory

This table maintains the linkage between product groups and the Categories your company has created. Click here to view additional details about the column layout of this table.

7.ConsInventoryTotals

This table maintains inventory sales and cost of sales (COGS) totals for each location (Site) by category, group, year, month, and day of month. Click here to view additional details about the column layout of this table.

8.ConsSelectedCorporations

This table maintains the list of corporation codes that you want to maintain consolidated data for. A single column in this table is relevant.

 CorporationId varchar(12)

9.ConsSelectedVendors

This table maintains the list of vendor codes that you want to maintain consolidated data for. A single column in this table is relevant.

 VendorCode varchar(10)

10.ConsVendorTotals

This table maintains purchase and payables related totals for vendors that you have indicated you want to maintain consolidated data for. The vendors records are maintained by location (Site ID), vendor code, year, month, and day of month. Click here to view additional details about the column layout of this table.