Introduction

In this tutorial, you learn how to create a custom attribute with a custom rule to stop carrying reconciliation compliance transactions into a period that the Close Date is in. This ends the carry-forward process for the transaction, so the user won't have to manually delete it in the period when it is no longer needed.

Background

In this tutorial, you perform the following tasks in Account Reconciliation:

  1. Create a custom attribute with a calculation
  2. Apply an attribute to a Format
  3. Create a custom rule on the Format

Prerequisites

Before starting this tutorial, you should have:

  • A working knowledge about Account Reconciliation.
  • Provisioning to the Administrator role

Creating an attribute

In Account Reconciliation, you can create a custom attribute and apply it to a rule by completing the following steps:

  1. From the Account Reconciliation home page, click the Application card and then select Configuration.
    2. Launch Match Types
  2. Click the Attributes tab.
    3. Click Group Attributes
  3. Click the New icon to create a custom attribute.
    4. Click New
  4. Give the attribute a name, select Number for Type, select Sum for Total, and select the Calculation option.
    5. Enter a Name
  5. In the Calculation tab, in the Calculation Definition field , type DATE_DIFF(EDATE({Close Date}, -1), {Period End Date}, 'DAYS'), and then click OK.
    6. Add member details

    Note:

    There are two functions in this formula, DATE_DIFF and EDATE.

    The Date Difference or DATE_DIFF function calculates the difference between two dates in days. In this example, between the Close Date entered on the transactions and the Period End Date.

    The Add Month or EDATE function adds or subtracts months from the starting date. Because the formula is used in the Copy Transactions from Prior Reconciliation rule that populates a current reconciliation with transactions from a previous reconciliation, the function uses a negative value for months “-1” that brings the Close Date back a month. This way, if a transaction has the Close Date in July, the calculation returns a June date instead so that the Copy Transactions from Prior Reconciliation rule won't copy the transaction into July.

    The two functions are then combined to return the date difference of the Close Date (minus one month) and the Period End Date in days.

Creating a Rule

In Account Reconciliation, you can create a rule by completing the following steps:

  1. From the Configuration page, click the Formats tab.
    2. Launch Match Types
  2. Create a format or select the existing format that will have the new rule applied.
    3. Select the required match type
  3. Click the Balance Explanations or Adjustments transaction tab that will have the new rule applied.
    4. Change status to Pending
  4. Click the Add icon to add the newly created custom attribute to the Transaction Detail.
    5. Click New Attribute
  5. Select the custom attribute that was created in the previous step (in this example, Close Date Drop Off), and then click the Add icon to assign roles that should not have view access to the attribute. Click OK.
    6. Select the required attribute
  6. Expand the Rules bar, and then click the Add icon to create a rule.
    7. Change status to Approved
  7. Select the rule Copy Transactions from Prior Reconciliation rule, and then click Create Condition.
    8. Change status to Approved
  8. Select the condition below, and then click OK.
    9. Change status to Approved

    Note:

    A transaction date with the same month as the period end date results in a negative value because the formula subtracts a month from the Close Date. For example, if a Close Date is 6/15/24 and the period end date is 6/30/24, then the date difference is calculated between 5/15/24 and 6/30/24. We want to copy forward only those transactions for which the formula returns a positive number because the Close Date has yet to occur.
  9. Save and Close the format. The rule will now be applied to all future reconciliations you create.
    10. Change status to Approved