Defining Data Sources

Once you specify the properties of the match type, define the data sources that contain the transactions to be matched. There is no limit to the number of data sources you may add, however, most reconciliations will contain two data sources: transactions from Source system will be matched with transactions from Sub System.

Certain types of reconciliations (such as Clearing accounts, Suspense accounts, or high volume accruals) require debit/credit matching within a single data source. The goal for both cases is to produce a reconciliation and the format depends on whether you are working with a two or more source process or a one source process.

When defining a data source, you define the "attributes" in the source, which are the columns of data that will be imported. This is also where you can enrich and normalize the data to get it ready for matching by using calculated attributes. For example, you may need to extract a string of text from the center of some field of text, and then match this string of text to some other attribute. Or, you may need to convert various text values into some "normalized" value, so that it can be matched. A library of calculated attributes can be used for data enrichment and normalization, so that you can achieve the greatest number of possible matches using the automated matching engine.

Note:

You are required to set up two fields at minimum for each Data Source: Amount and Date. An Amount attribute is needed to represent the transaction amount from the source or sub-system, which is defined as the "Balancing Amount" on the data source definition. An Amount should always be set as type Number. A Date attribute is needed to represent the Accounting Date which determines the accounting period that a transaction is reflected in. The amount and date attributes must be populated on each transaction since they will be used to perform period-end calculations.

An administrator can also allow users to delete transactions for a given data source. This is done by enabling the Allow transactions to be deleted check box. Note that this feature can be enabled for a given data source whether transactions are already loaded or not. For information about deleting transactions, see Deleting Transactions

An administrator can also allow users to edit transactions by enabling that feature per data source attribute. For information on Editing Transactions, see Editing Transactions

Here is an example of a match type showing two data sources, Accounts Payable and Accounts Receivable, with data source attributes for the Accounts Payable data source. Note that the required Date attribute has been created called AP_DDATE. Also a Balancing Attribute has been created called AP AMT.
Edit Match Type Data Sources

To define data sources:

  1. From Home, select Application.
  2. On the Match Types tab, select the match type you are creating.
  3. On the Data Sources tab, select New.
  4. Enter an ID for the data source and a descriptive name.

    Note:

    You cannot use the name, Reconciliation ID, as an ID since it is a reserved ID.
  5. Indicate whether the data source is a Source System or a Sub System data source.
  6. Decide whether you want to allow deletion of transactions by users for this data source. For information about deleting transactions, see Deleting Transactions
  7. Decide whether you want to allow split of transactions by users for this data source. Then, click Save. For information about splitting transactions, see Splitting Unmatched Transactions
  8. In Data Source Attributes, click + (New Attribute) to start adding attributes to describe how the data in this column should appear. Keep in mind that this is how you can use the powerful calculated attributes feature to normalize and enrich your data before using the matching engine in Transaction Matching.

    Note:

    You must create a Date type attribute that is required to represent the Accounting Date.

    Table 9-1 Attribute Details

    Field Description
    Type
    • Text- Used for large comments, descriptions, etc and can be up to 255 characters long.

      Note:

      When the value in a text string is already enclosed in double quotes at the beginning and ending of the string, use double quotes when you have comma inside that text. For example: "GM LLC - GMNA, formerly ""NAO"" ADMIN STAFF"
    • Date-The format when loading transactions should be DD-MMM-YYYY or DD-MMM-YY.

      Note:

      You may have multiple Date attributes, but one must be designated as Required and serve as the Accounting Date which represents the accounting period that the transaction is reflected in. This date assigned to each transaction will be used to perform all period-end calculations.
    • Number:

      Used for amount fields up to 15 digits in total and up to 12 digits after precision. Numbers are rounded to 2 decimal places for variance calculations. Numbers with up to total 15 digits are supported without loss of precision. For example, all of these examples are valid:

      • 1234567890123.45
      • 12345678901234.5
      • 123456789012.345
      • 1234.56789012345
      Integer: Used for non-decimal values up to 18 digits and can be positive or negative.
    • List:

      Used to configure a List of Values and upper and lowercase do not matter when importing.

    • Yes/No:

      Used for a Boolean field that can be left Blank, Yes (1,Yes,YES,Y,y,T,t,True,TRUE,True) or No (0,No,NO,N,n,F,f,False,FALSE,False)

    Default Value (optional) Enter a default value for this attribute.
    Key

    Select if you want to activate the duplicate transaction check process. The Key setting is a way to uniquely identify a record in the database. You can select one or more attributes as Keys for the duplicate check. The Transaction Import will not load a file if the file contains one or more transactions that match a Key that has already been loaded.

    Required Select if you want this attribute to be required.
    Accounting Date Select if you want the attribute to be set as the Accounting Date. The Accounting Date represents the accounting period that the transaction is reflected in. This date assigned to each transaction will be used to perform all period-end calculations. Only one attribute can be set as Accounting Date.
    Balancing Attribute Select if you want the attribute to be set as a Balancing Attribute. Only one attribute can be set as the Balancing Attribute.
    Calculation (optional) Check this box if you want the attribute data to be calculated. If you choose this option, a Calculation definition section is displayed.
    Calculation Type

    Choose whether you want the calculation to be based on a script, when certain conditions are met, or assign value to a list

    • Assign Value to List—if you want to assign a value to a list
    • Conditional—if you want the calculation to be performed when certain conditions are met
    • Scripted—if you want the calculation to be based on a script. Scripted is available for attributes of type Text, Date, Number, or Integer
    Calculation Definition Enter a calculation script in this area.
    Add Attribute Add an existing attribute to a calculation script by selecting from the list.
    Add Function

    Choose from the library of functions to help create a script:

    • Date Difference
    • Extract Text
    • If Then Else
    • Lowercase
    • Maximum
    • Minimum
    • Round
    • Text Location
    • Uppercase
    • Select Calculation

      Calculated attributes are read-only. Administrators can add attributes to the attributes sections in the Actions dialog boxes, and workflow users can view them in the actions dialog boxes and in transactions. Administrators can restrict access to certain roles by setting access to Do Not Display. For example, for calculated attribute XYZ, an administrator could add Viewer: Do Not Display access to it, so that XYZ would not be displayed to viewers.

      Any user role can add calculated attributes as columns in views and portlets. They can also be added as filterable attributes in the Filter Panel.

      Note:

      You cannot create calculated attributes that result in a cyclic dependency. For example, the following calculated attributes are not allowed where A and B are dependent on each other: A= {B}*(-1) and B= {A} + 100.create a cyclic dependency , since that causes a cyclic dependency which is not permitted and causes errors.
    • When you select Calculation, a Calculation definition section is displayed:

      • Calculation Type: The list of values is determined by the attribute type:

        • Assign Value to List—Assign a value to a List type attribute

        • Assign List To Value—Assign a List Value to the value of a different attribute. Only available for attributes of type List

        • Conditional—A conditional calculation (If – Then – Else)

        • Scripted—A free-form scripted calculation. Scripted is available for attributes of type Text, Number, or Integer

        The following table lists the calculation types that each attribute type can use when the Calculation option is chosen:

        Table 9-2 Calculation Types that Each Attribute Type Can Use When the Calculation Option is Chosen

        Attribute Type Assign Value to List Conditional Scripted Assign List to Value
        Date     X  
        Integer X X X  
        List        
        Number X X X  
        Text X X X  
        Yes/No   X    
      • When you select Scripted Calculation type, to enter a free-form calculation equation, use the Add Attribute and Add Function:

        Add Attribute—Select an attribute and click Add to insert the attribute into the Calculation Definition box at the location of the cursor. If the cursor is in the middle of a word or attribute, the word/attribute will be replaced in the definition. Any attribute that is added will have brackets {} around the name, according to the scripting format.

      • Add Function—Select a function and click Add to add the function to the Calculation Definition. The Function is added with placeholders for each parameter.

        Other scripted function examples:

        • Date Difference: Returns the difference in days, hours minutes, or seconds between two dates. For Date1 and Date 2, the values 'TODAY' and 'NOW' can be used, which denote the current date (with no time component) and date-time, respectively.

          DATE_DIFF(<Date1>, <Date2>, <Type>)

          Example: DATE_DIFF('TODAY', {Preparer End Date}, 'DAYS') or DATE_DIFF({Preparer End Date}, 'NOW', 'HOURS')

        • Extract Text: Returns the substring within the value, from the positions specified.

          SUBSTRING(<Value>, <Location>, <Length>)

          Example: SUBSTRING( {Name} , 5, 10)

        • If Then Else: Allows the user to insert a conditional calculation into the scripted calculation. IF_THEN_ELSE calculations can also be nested to support "ELSE IF" type calculations.

          IF_THEN_ELSE(<Condition>, <Value1>, <Value2>)

          Example:

          IF_THEN_ELSE( {Risk Rating} = 'Low', 'Good',
          IF_THEN_ELSE( {Risk Rating} = 'Medium', 'Better',
          IF_THEN_ELSE({Risk Rating} = 'High', 'Best','Bad')))
          
        • Length Takes a text value as a parameter and returns an integer which is the number of characters in the text.

          LENGTH('Value') returns 5, and LENGTH({Name}) would return the number of characters in the name of the object. If the value is empty/null, the calculation will return 0.

          Use the calculation with SUBSTRING to extract the last 4 characters of a text value.

          Example: SUBSTRING( {MyString}, LENGTH ({MyString}) - 4

        • Lowercase Returns the value in lower case.

          LOWERCASE(<Value>)

          Example: LOWERCASE( {Description} )

        • Maximum: Returns the maximum value from a list of attributes. There can be any number of parameters.

          MAX(<Value1>, <Value2>,<ValueN>)

          Example: MAX({Acccount1},{Account2},{Account3})

        • Minimum: Returns the minimum value from a list of attributes. There can be any number of parameters.

          MIN(<Value1>, <Value2>,<ValueN>)

          Example: MIN({Acccount1},{Account2},{Account3})

        • Round: Returns the value rounded to the decimal places specified.

          ROUND(<Value>, <Decimal Places>)

          Example: ROUND( ({Scripted Translate} /7), 4)

        • Text Location: Returns the index of the substring within the value, starting at 1 as the first position.

          INSTRING(<Value>, <Value To Search>)

          Example: INSTRING( UPPERCASE( {Name} ), 'TAX' )

        • Uppercase: Returns the value in upper case.

          UPPERCASE(<Value>)

          Example: UPPERCASE( {Name} )

  9. Click Save to continue adding the next attribute.

Attribute Examples

Here are some examples of common attributes:

Text
Text attribute type example

Scripted Calculation Type Example


calculated attribute of scripted type

Conditional Calculation Type Example
Conditional calculated attribute example

Creating Filters When Defining Data Sources

After you define a data source and set up the data source attributes, you can set up filters to limit the records included in the list. You can create many different filters depending on how you want to view the information.

To create a filter when defining data sources:

  1. Click the Filters tab from the Data Sources tab.
  2. Click the + (plus sign) to Add a Filter and display the Filter definition dialog.
    Filter condition dialog
  3. Enter the Name for the filter.
  4. Enter the filter criteria:
    1. Click Create Condition.
    2. Enter the conditions you want to use to create the filter definition expression (Conjunction, Attribute, Operand, Value).

Example of a Data Source Filter Using Age

An example of how a data source filter can help you is using the Age, calculated as Current Date minus the Accounting Date, to identify all unmatched transactions that are over 180 days old in order to write those off. Once you create that filter, you apply that filter to the Match Rule for Adjustments so that all the old unmatched transactions are automatically cleared or matched off as Adjustments. Similar to other adjustments created, you can then extract these as journal entries to the GL

Note:

The last used filter will be persisted (saved) for each user by match type, match process and data source.This means that you can log out and back in again, and if you open another reconciliation of the same match type, it shows the same filter.

Example of a Data Source Filter Using Status Attribute

Another example of how a data source filter can help you is using the Status attribute to filter. Note that the possible statuses are: Unmatched, Supported, Confirmed Adjust, Confirmed Match, Suggested Adjust or Suggested Match.
Data Source filter Using Status Attribute