Menu: Apliqo Settings / Data Integration / Data Processing / Automation

In previous sections we have set up our training thus far, we’ve established the Company, Cost Center, Customer, and Account dimensions . In this section we are going along with their rollups. These dimensions serve as the metadata for the Training Apliqo FPM model. We are ready to load the trial balance from and external source to the FIN General Ledger cube.Trial Balance from the external data source to the FIN General Ledger TB cube.

The Trail Balance represents the monthly movements of the natural accounts expressed as in debit and credits. Where the sum of the debits and credits in the Trial Balance always equals zero. Debit and Credit can be expressed in the data source in two different ways. Depending on the data source may influence how the Trial Balance is loaded.

1. As positive amounts in separate columns for debit and credit movements. The sum the debit column equals the sum of the credit column. In this case the debits can be loaded to the DR - Debit element and the credits can be loaded to the CR - Credit element in the FIN Consolidation Value Measure dimension. The CR - Credit element is given a weight of -1 in the TB - Trial Balance consolidation.

2. In a single column where, positive movements are debits and negative movements are credits. In This case the movements can be accumulated against the TB Load element which is a member of the TB - Trial Balance consolidation.

After the monthly Trial Balance is loaded for the company, the total of all accounts and the total of all business segments is check that it equals zero. Once the Trial Balance Check is passed, the trial balance is transformed for management reporting in the FIN General Ledger cube.

In the FIN General Ledger cube the sign of account changes for liability, revenue and expense accounts. In accounting movement in liability and revenue accounts are expressed in credits. A negative movement is an increase in these types of accounts. In Apliqo FPM we flip the sign so that an increase in these accounts is a positive movement. For expense account the accounting movement is a debit which is positive. In the FIN General Ledger cube the sign is flipped to a negative. Theses changes are driven by the TB_Mult attribute in the FIN OCoA dimension. To make up for this change of signage in the Balance Sheet a weight of -1 give to the Total Liabilities and Equity account. When reporting the Balance Sheet, all numbers are positive. When reporting the Profit or Loss, revenues are positive, and expenses are negative. An increase in revenues or decrease in expenses is an increase in net profit. This is considered intuitive to non-accountants.

The following topic will be covered in this section.

You can access the Chart of Accounts setup through the page: Administration > Chart of Accounts.

Creating a configuration to load the trial balance.

For this training we are using the demo.sqlite SQL-Lite database to load the trial balance. An ODBC connection (ApliqoFPM_ODS) has been setup to connect to the demo.sqlite SQL-Lite database. A turbo Integrator (TB) process has been written to load the trial balance into Apliqo FPM.

Add the TI process to Data Intergration

Add the process ‘TI) process: }APQ.C3.FIN.Cub.GeneralLedgerTB.Update.From.ODS_Training’ to the data Processing automation.

...

Copy a Configuration

Apliqo FPM come shipped with a number of predefined configurations. In this section we are going to copy an existing configuration and add the step created above to the configuration.

...

Run a Configuration

Demo has been written to load the trial balance the Apliqo_Demo sqlite database.

...

Running the Training - Load TB a configuration

In this section we are going run the configuration to load the Trial Balance for the 2022 financial year.

...

Exercise: Run the configuration process for 2023 financial year.

Checking the Trial Balance

Select the Actual Actuals tab on the Data Processing page.

Confirm the Cube (Measure) is Trial Balance (TB) and Show Accounts under is Total GL Accounts.

For the TB Check to pass the Total GL Account should be zero.

...

The [TB Check] popup should not be orange. If it is orange, then it will show the number of companies and months that are out balance.

...

For each company and month you can set a tolerance or override the difference. A global TB Tolerance can also be set. Click on [Check Again]

Trial Balance Report

The Total GL Account row for the Consolidation Measure TB should be zero. TB means the account movement have been loaded in Debits (+) and Credits (-). The 9999 Total - All Account TB will not balance here because the signs movements of the liabilities, revenue and expenses accounts have not changed. There is no data in the Process Data (GL) as we have not run the GL Update process.

...

TB Check popup.

The TB Check is an overview of the trial balance check for all companies for the financial year. If company fails the trial balance check, a warning message will show the number of months that failed. Click on the company to view the discrepancies for that company. You have the following resolve the discrepancies.

• You can resolve the discrepancy in the data source.

• You can accept the discrepancy for the month by selecting your username in the User Pass column.

• You can set a Tolerance for the Trial Balance Check. If the difference is between the +- of the tolerance the amount is accepted. This is a global setting that applies to all companies.

Click on Check Again to check the Trial Balance again.

...

Account Mapping

New account accounts can be created added by the Trial Balance load process. Any new accounts can mapped in the [Unmapped Accounts] the custom process that loads the Trial Balance. Here is a snippet of code to add an account to the rollup.

If( DIMIX( 'FIN OCoA', sElem ) = 0 & cCoaUnmapped @<> '');
  ## Add Element to FIN OCOA Staging Dimension
  ExecuteProcess('}APQ.C3.Dim.CoAMap.Element.Create',
     'pLogOutput', '1',
     'pCoAID', 'FIN OCoA',
     'pEle', [account code],
     'pType', 'N',
     'pPar', 'Total GL Accounts',
     'pWeight', '1',
     'pKPICoAID', 'FIN OCoA'
    );
  CellPutS( [account description], '}APQ C3 FIN CoA Map', 'FIN OCoA', [account code], 'Description', 'AttrValue');
  ## Add Element to FIN OCoA actual dimension
  DimensionElementInsertDirect( 'FIN OCoA', '', [account code], 'N' );
  DimensionElementComponentAddDirect(sDim, 'Total GL Accounts', [account code], 1 );
  AttrPutS( [account description], sDim, [account code], 'Description' );
  AttrPutS([account code] | ' - ' | [account description], sDim, [account code], 'Code and Description' );
EndIf;

Any new accounts can be mapped into the Management Balance Sheet rollup in the Unmapped Accounts popup.

...

On the left select the account that is to be mapped. On the right, select the node in the chart of accounts the selected account is to be mapped to. Click on the > icon to map add the selected accounts to the account node. Click on SYNC Sync to commit the changes to the FIN OCoA dimension.This can also be done in the Apliqo Settings / Chart of Account / Operational CoA.

Load the General Ledger

Now the Trial Balance, balances to zero we can load the General Ledger. Click on the Automation tab and check the following step in Training - Load TB configuration. Run the round trip from the financial year 2022 and 2023.

...

Checking for Errors

The configuration will stop when it encounters an error. Click on the info icon to see the error message.

...

To fix this error go to back the Operational Chart of Account map page. Select the Fixed Account, tab. Find the C3_EQ_RETAIN fixed account. Map it to the account 2360_N. Remember to Sync Sync the Operational CoA to apply the changes to the FIN OCoA dimension to apply the change. Once that is done re-run the Training - Load TB configuration. Re-run the configuration and confirm Confirm there are no steps in errorerrors.

Checking the General Ledger

Select the Actual tab on the Data Processing page.

Confirm the Cube (Measure) is General Ledger (OPV) and Show Accounts under is 9999 Total.

...

Exercise: Load financial year 2023

Load the Trial Balance for period 2023-Jan to 2023-Mar for Company 1000.

More Information

...