Define a reconciliation process

Overview

Reconciliation consists in regularly comparing (every week or every month) accounts that are registered in two different systems (for instance, a subledger and a general ledger). In theory, the accounts should be the same, but some problems may have occurred in the information system. The reconciliation process highlights the possible differences. These differences should be analyzed by an accountant to find the origin of the problem.

Before launching a reconciliation process, some configuration steps are needed in Designer. Based on the configuration, the reconciliation formats are generated. Then, the configuration is deployed to the Repository. During the deployment, the intermediary formats and relationships that are used to navigate from the results to the original data and vice versa are generated automatically in the Repository.

After the deployment, you must create a reconciliation function in Datastore Client and associate it with a request.

Reconciliation configuration (Designer)

Configure the reconciliation

  1. Go to the Reconciliation tab of the Formats collection.
  2. Click the Create button to add a reconciliation definition.
  3. The Reconciliation definition page is displayed. All mandatory fields are marked with an asterisk (*).
  4. Fill in the reconciliation details:
  5. Field Contents
    Reconciliation name (*) Name of the reconciliation object
  6. Fill in the source 1 details:
  7. Field Contents
    Collection type name (*) Name of the collection type to be reconciled.
    Version (*) Version of the collection type to be reconciled. Mandatory.
    Object type name (*) Name of the object type to be reconciled.
    Version Version of the object type to be reconciled. If no version is given, all versions of the object type are taken into consideration.
    Keys (*)

    An ordered list of properties of object type to calculate the reconciliation key.

    At least one property is mandatory.

    Amount (*) A list of numeric properties of object type to be "aggregated" in the reconciliation amount result.
    Direction Name of the property of object type to be taken into consideration to sum up or subtract the Amount in the reconciliation amount result.
    Direction credit possible values List of possible values for the credit.
    Direction debit possible values List of possible values for the debit.
    Additional properties Additional properties, other than keys, amount or direction, in order to have more information in the reconciliation results
  8. Fill in the source 2 details (same fields as for source 1).
  9. Fill in the reconciliation common details:
  10. Field Contents
    Reconciliation property (*) Name of the property which represents the reconciliation id.
    Result name (*) Name of the reconciliation result.
  11. Click Save.

When saving the reconciliation configuration, some checks are carried out in Designer:

  • The reconciliation property for the reconciliation id must be common to the two collection sources and must have the same type.
  • The reconciliation property must be a string or a number.
  • At least one reconciliation key must be selected for each source.
  • At least one reconciliation amount must be selected for each source.
  • The reconciliation name should be unique (reconciliation object scope).
  • The keys and amounts properties must be present in the selected versions of the sources.
  • When selected, the direction properties must be present in all the selected versions of the sources.
  • All amount properties must be numbers.
  • Direction credit / debit values must be aligned with the direction property type definition.
  • A property can be either key, amount, direction, or additional property.
  • The key length of source1 must be equal to the key length of source2. If the key is represented by multiple properties, then the length is computed as the sum of the lengths of those properties.

Configure the reconciliation to use audit objects

  • A Collection Type is marked as auditable when the field Object types group is set to Audit.
  • An Object Type is marked as auditable when the field Audit tags has at least the Audit value. An Object Type marked as auditable will generate an Audit Object Type when deployed. The properties defined under the Audit properties tab will become the properties of the Audit Object Type.
  • When any auditable Collection Type is selected in the configuration screen (described above), the corresponding Object type name field will list all possible audit object types.

Generate the reconciliation formats

  1. From the reconciliation details, click the Generate reconciliation formats button.
  2. When the generation is finished, the Reconciliation result dialog box is displayed.
    Note: Read the information carefully and do not forget to associate an editable space to the reconciliation OT before deployment.
  3. Click OK.
  4. The generated reconciliation collection type and object type become available in the Collection Types and Object Types tabs.
  5. For a detailed description of the generated formats, read Generated reconciliation results

Associate an editable space to the reconciliation object type

  1. Open the reconciliation object type that has just been generated and in the Space drop-down list (Audit part), select an editable space.

Deploy the configuration to the Repository

To deploy the new configuration, select all the collections again and click the Deploy button.

The intermediary formats and relationships that are used to navigate from the results to the original data and vice versa are generated automatically in the Repository.

When deploying the reconciliation configuration, some additional checks are carried out:

  • The definition of the reconciliation object type should be aligned with the reconciliation definition.
  • The result formats (collection type + object type) should exist. The reconciliation cannot be deployed by itself, without generated formats.
  • The properties from the sources should be compatible with the ones from the generated object type (for instance, it is not allowed to have in source 1 an amount of length 5 and in result an amount of length 4).
  • The reconciliation object type must be associated to an editable space.

Reconciliation function (Datastore Client)

See Create a reconciliation function.

Use the Datastore Runtime command tool

You can use the Datastore Runtime command tool (dstools) to execute reconciliations. See Use the Datastore Runtime command tool.

Generated reconciliation results

During the generation process, the following are generated:

  • a reconciliation collection type
  • a reconciliation object

Also, two tags are created (if they do not exist) and added to the sources and reconciliation result objects:

  • RECONCILIATION_SOURCE (added to the sources)
  • RECONCILIATION_RESULT (for the result object)

The generated reconciliation collection type

For the generated reconciliation collection type, the following is available:

  • Name (Details tab): the name defined in Result name while configuring the reconciliation
  • Object type reference (Object Type references tab): the generated reconciliation object type
  • Properties (Properties tab):
    • The property selected in Reconciliation property while configuring the reconciliation
    • Reconciliation name: (RECONCILIATION_NAME): this property is used by Datastore during the Reconciliation Function execution and will store the name of the reconciliation definition on which is based the executed Reconciliation Function.
    • Reconciliation function name (REC_FUNCTION_NAME): this property is used by Datastore during the Reconciliation Function execution, to store the executed function name for easier sorting/filtering of the result.

The generated reconciliation object type

For the generated reconciliation object type, the following is displayed:

  • Name (Details tab): the name defined in Result name while configuring the reconciliation
  • Tag (Details tab): the RECONCILIATION_RESULT tag is set for the reconciliation object type. Do not remove it as is important to activate different navigation functions.
  • Properties (Properties tab):
    • The Key, Direction, and Amount properties defined for source 1 and 2
    • The additional properties for source 1 and 2
    • Reconciliation Amount: the reconciliation amount calculation = Amount source 1 – Amount source 2
    • Reconciliation Key: the value of the reconciliation key calculation

 

Related Links