Loading Exported Journal Entries

In Transaction Matching, you can export adjustments or transactions as dual sided journal entries that can then be imported into your ERP system. Using Data Management, you can create a data source associated with an Accounts Reconciliation journal adjustments source system, and then load the entries to a custom target application. When the entries are exported in the Data Load Rule, all adjustments and transaction that have an open status and match any applied filter criteria are exported.

To load exported journals entries:

1. On the Setup tab, under Integration Setup, select Period Mapping.
2. Select the Application Mapping tab.
3. In Target Application, select Account Reconciliation Manager.
4. Click Add.
5. Select the Period Key.
6. Enter the following:
   1. Target Period Month
   2. Target Period Quarter
   3. Target Period Year
   4. Target Period Day
   5. Year Target
7. Select the Setup tab, and then under Register, select Target Application.
8. In Target Application, in the summary grid, click Add, and then select Data Source.
9. From Source System, select Account Reconciliations Journal Adjustments.
10. In Application Name, select the data source application name for the Accounts Reconciliation Journal Adjustment application.

    

11. In Prefix, optionally specify a prefix to make the application name unique.

    The prefix supports up to a maximum of ten characters.

12. Click OK.
13. Select the Dimension Detail tab.

    When you add an Account Reconciliation Journal Adjustment data source, the dimensions in application are populated automatically on the Dimension Detail tab. These dimensions correspond to the Transaction Matching journal entry column names in Account Reconciliation as shown below.

    

14. Map all dimension names in the Dimension Names column with the value Generic in the Target Dimension Class column and click Save.
15. Create the target application by clicking Add and then Local.
16. On the Select Application screen, from Type, select Custom Application.
17. Click OK.
18. In the Application Details section, in the Name field, specify the name of the custom application.
19. Select the Dimension Details tab.
20. Click Add.
21. In Dimension Name, enter the name of the Account dimension, and then specify Account in the Target Dimension Class. and Data Table Column Name fields.

    When you specify Account in the Target Dimension Class. the value Account prefills automatically in the Data Table Column Name field.

    This step enables you to create dimensions not defined in the target application. You must create an "Account" dimension name and associate it with an "Account" target dimension class, which is a property defined by the dimension type. You also associate it with an "Account" data table column name, which identifies the table column name of the column in the staging table. These definitions are required by Data Management but not the Account Reconciliation.

    You do not need to create an "Amount" dimension because it defaults on the Import Mapping screen.

    You can create other dimension names, such as UD1 through UD20 or "Entity" as needed.

    

22. Click Save.
23. On the Setup tab, under Integration Setup, select Import Format.
24. In Details and then in Name, specify the name of the import format.
25. In Source and Target, select the source and target for the journal adjustments.
26. In the Mapping section, map the Accounts Reconciliation Journal Adjustment source columns and the custom target application columns.

    For more information, see Working with Import Formats.

27. On the Setup tab, under Integration Setup, select Location.
28. Define the location associated with the import format.

    For more information, see Defining Locations.

29. On the Workflow tab, under Data Load, select Data Load Mapping.
30. Define the data load mapping to map the members from the source to target.

    Do not add mappings for the Data dimension.

    For more information, see: Creating Member Mappings.

31. On the Workflow tab, under Data Load, select Data Load Rule.
32. Specify the location.

    A location is the level at which a data load is executed in Data Management. Any import format associated with the location is populated automatically in the Import Format field. If multiple import formats have been associated with the location, you can browse for them.

33. Select the Source Filters tab, and complete any parameters based on the transaction matching type.

    Available parameters:

    • Type—Specify the type of reconciliation.

      Available types:

      • Transactions
      • Adjustments

    • Match Type—Specify the match type ID such as "Clearing."

      Match Types determine how the transaction matching process works for the accounts using that match type. They determine the structure of the data to be matched, as well as the rules used for matching. Additionally, match types are used to export adjustments back to an ERP system as journal entries in a text file.

    • Data Source—Specify the data source when the transaction matching transaction type is "Transactions."

      Leave this field blank when the transaction matching transaction type is "Adjustments."

      Names for the data sources that appear in Data Management are actually sourced from the Transaction Matching data sources. The convention used in the drop-down is Match Type Name: Data Source Name.

      For example, application choices might include:

      • InterCo3:AR
      • InterCo3:AP1 3
      • Bank BAI:BAI_Bank_File
      • Bank BAI:GL
      • INTERCO2:AR
      • INTERCO2:AP
      • INTERCO:AR 8
      • INTERCO:AP 9
      • CLEARING:CLEARING

    • Filter—If you choose Type as the Transaction, specify the filter name for transactions.

      The filters is defined in data source configuration in Account Reconciliation as shown below:

    If you choose Type as Adjustment, specify the filter value in JSON format.

    You can select specific transaction types and/or the accounting date while exporting the journal for Adjustments

    For example, you can select all transaction types except transaction types of tax until month end.

    To specify the filter for Adjustments, use the Filter field to select the following:

    • (Adjustment) Type—Specify the adjustment type available for the match type selected in the previous step. You can specify one or more values. If you don't select a value, the default used is All.

    • Adjustment Date—Specify the operand and date values (using the Date Picker to select the dates). The operands available for filtering are: EQUALS, BEFORE, BETWEEN, and AFTER.

      The date format must be YYYY-MM-DD. If you use EQUALS, BEFORE, and AFTER operands, use the JSON format: accountingDate and then specify the accounting date. If you select a BETWEEN operand, use the JSON format:

      • fromAccountingDate for the "from" Accounting Date
      • toAccountingDate for the "to" Accounting Date

      Here are some sample JSON formats:

      {"adjustmentTypes" : ["Invoice Dispute","Coding Error"],"operator" : "BETWEEN", "fromAccountingDate" : "2022-02-01", "toAccountingDate" : "2022-02-10"}

      {"adjustmentTypes" : ["Invoice Dispute","Coding Error"],"operator" : "EQUALS", "accountingDate" : "2022-02-01"}
      

      {"operator" : "AFTER", "accountingDate" : "2022-02-01"}

      {"adjustmentTypes" : ["Invoice Dispute","Coding Error"]}

34. In Data Load Workbench, test and validate the data by executing the data load rule to ensure that the data load rule is running properly, and your data looks correct. Data Management transforms the data and stages it for Account Reconciliation to use.

    See Using the Data Load Workbench.

    For information on running the data load rule using EPMAUTOMATE, see the rundatarule topic in the Working with EPM Automate for Oracle Enterprise Performance Management Cloud.