Technical Guide: Learn How to Leverage Calculated Attributes in Oracle Account Reconciliations

by Katie McCorkle – Senior Director, EPM

Attributes are extremely useful to ensure reconciliations meet expectations of quality, follow accounting policies and procedures, and provide meaningful information to all parties when viewing reconciliations within the Oracle Account Reconciliations module.   

Attributes can be used to: 

• Facilitate reporting. 

• Ease user adoption. 

• Add additional information to reconciliations or transactions. 

• Provide balances in a centralized currency. 

• Require users to input certain information. 

In addition to attributes that contain informational data, calculated attributes can be created to add information to reconciliations based on values or parameters. These calculated attributes can then be used in rules to provide more flexibility to the system. 

 

Calculated Attributes 

Calculated attributes are read-only and can be assigned by administrators based on roles. For example, a calculated attribute can be added by an administrator with Viewer: Do Not Display access so that the attribute would not be displayed to viewers. Calculated attributes can be added as columns to any of the list views and can be used as filters. 

Calculation Types include: 

• 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 (only available for attributes of Date, Text, Number, or Integer). 

The type of calculated attribute is dependent on the Attribute Type as shown in the table below: 

 

Scripted Calculations 

Scripted calculations allow for the insertion of Functions and Attributes to help in the creation of these calculations.  Using the Add Function and Add Attribute options makes scripting far easier. 

There are quite a few options for scripted calculations, but this post is going to focus on a few options that are used most often when configuring the system.   

The full listing of options can be found in the Setting Up and Configuring Account Reconciliation guide. 

Note: Parameters with an asterisk, *, are required if the value is a balance-type attribute. When creating the script for all other types, the * parameters can be deleted. 

Note: Rate Type must be a valid rate type set up in your system, or use the rate type assigned to the reconciliation using ‘REC’ instead. The Rate Period parameter must be either be the current period rate (‘CURRENT’) or the prior period rate (‘PRIOR’). 

 

Examples of Useful Calculated Attributes 

The examples that follow include both Conditional and Scripted calculated attributes. 

All Auto Reconciliation Methods 

Oracle Account Reconciliation provides out-of-the-box auto reconciliation methods that are available for every profile.  These auto reconciliation methods can be applied to meet most auto reconciliation requirements.  If the out-of-the-box rules aren’t quite what is needed, rules can be applied to customize the auto reconciliation rules.  Using Auto Submit and Auto Approve rules can create tailored auto reconciliation rules to meet company specific demands. 

When Auto Submit and Auto Approve rules are used, all reconciliations that are ‘auto reconciled’ are not included in the Auto Reconciled bucket for reporting.  In this case, a calculated attribute can be created to capture all reconciliations that are Auto Reconciled and Auto Submitted. 

To create this calculated attribute: 

1. Create a new attribute called All Auto Recon Methods. 
2. Select a Type of Yes/No. 
3. Check the Calculation box. 
4. On the Calculation tab, select a Calculation Type of Conditional. 
5. In the Calculation Definition section, add rows to set the value to Yes for Auto Reconciled and Auto Submitted.  The Else would be No. 
6. Save the attribute by clicking OK. 

This attribute can now be displayed on any of the list views (Reconciliation, Reconciliation Balance, Detail Balance), added to the reconciliation, or can be added to rules where needed. 

Active Reconciliation Status 

Another example of a calculated attribute breaks the status of the reconciliations into useful categories of Reviewed, Rejected, Auto Reconciled, and Opened.  This attribute will allow for more insightful reporting on how reconciliations were closed in the current period. 

While all these statuses could be pulled into list views using separate columns, the calculated attribute allows for a single column to provide the needed detailed information. 

To create this calculated attribute: 

1. Create a new attribute called Active Reconciliation Status. 
2. Select a Type of Text. 
3. Check the Calculation box. 
4. On the Calculation tab, select a Calculation Type of Conditional. 
5. In the Calculation Definition section, add rows to set the values based on the specific statuses.  The Else would be Open. 
   1. Note: For Reviewed and Auto Certified, rule groupings are needed to ensure the proper results. 
6. Save the attribute by clicking OK. 

Translated Source System Balance 

Translate is one of the most commonly used calculations.  It is required in other calculations to get amounts into a single currency before other functions can be applied.  Translated balances can be used to write rules and for reporting purposes.   

Before the translated balances functionality was added to Oracle Account Reconciliation, this was the method used to report balances that were not translated at a ledger or source system level. 

To create this calculated attribute: 

1. Create a new attribute called Source System (Functional) USD. 
2. Select a Type of Number. 
3. Check the Calculation box. 
4. On the Calculation tab, select a Calculation Type of Scripted. 
5. Using the Add Function selector, select Translate and click the + icon. 
6. The formula will display as TRANSLATE(<Value>, <To Currency>, <Rate Type>), click within <Value> and use the Add Attribute selector to select the balance to translate.  Click the + icon to add the attribute to the formula. 
   1. Note: Attributes are split between three categories – Reconciliation, Balance, and Transaction – scroll to the Balance section to select the appropriate balance attribute. 
7. Add the currency code and rate type from your system using apostrophes. 
8. Save the attribute by clicking OK. 
   1. Note: If any of the parameters are not correct, an error message will be shown. 

Absolute Value Source System Balance 

Absolute Value calculations build on the Translated formula to provide a translated absolute value in the system.  These translated values can then be used in reporting or for rules. 

To create this calculated attribute: 

1. Create a new attribute called Source System (Functional) Absolute Value.
2. Select a Type of Number.
3. Check the Calculation box.
4. On the Calculation tab, select a Calculation Type of Scripted.
5. Using the Add Function selector, select Translate and click the + icon.
6. The formula will display as TRANSLATE(<Value>, <To Currency>, <Rate Type>), click within <Value> and use the Add Attribute selector to select the balance to translate.  Click the + icon to add the attribute to the formula.
7. Add the currency code and rate type from your system using apostrophes.
8. Update the formula to include ABS and parenthesis on either end of the equation.
9. Save the attribute by clicking OK.

Translated Unexplained Difference + Rule 

Companies may have a requirement that all reconciliations must have an Unexplained Difference within a certain threshold USD. For example, all reconciliations must have an Unexplained Difference between -$1 and $1. If all reconciliations are in USD, this is easy to do with auto reconciliation rules using a threshold. 

What happens if a company has multiple buckets enabled or uses multi-currency?  The out-of-the-box auto reconciliation rules won’t work.  In this situation, the easiest solution is to create a calculated attribute for Translated Unexplained Difference and then write a rule to Prevent Reconciliation Submission if that rule isn’t met. 

To create this calculated attribute: 

1. Create a new attribute called Translated Unexplained Difference (Functional). 
2. Select a Type of Number. 
3. Check the Calculation box. 
4. On the Calculation tab, select a Calculation Type of Scripted. 
5. Using the Add Function selector, select Translate and click the + icon. 
6. The formula will display as TRANSLATE(<Value>, <To Currency>, <Rate Type>), click within <Value> and use the Add Attribute selector to select the balance to translate.  Click the + icon to add the attribute to the formula. Note: Attributes are split between three categories – Reconciliation, Balance, and Transaction – scroll to the Balance section to select the appropriate balance attribute. 
7. Add the currency code and rate type from your system using apostrophes. 
8. Save the attribute by clicking OK. 

In order to ease the maintenance and setup burden, first create a filter for Translated Unexplained Difference (Functional). 

1. Navigate to Application > Configuration > Filters. 
2. Click the + icon to add a new Filter. 
3. In the Name box, enter Translated Unexplained Difference Threshold. 
4. Select a Type of Reconciliation. 
5. Click Create Condition. 
6. Select Source=Reconciliation, Attribute=Translated Unexplained Difference (Functional), Operator=Greater Than, and Value=1. 
7. Click Create Condition. 
8. Select Conjunction=Or, Source=Reconciliation, Attribute=Translated Unexplained Difference (Functional), Operator=Less Than, and Value=-1. 
9. Save the filter by clicking OK. 

To create the associated rule: 

1. Navigate to Application > Configuration > Formats. 
2. Open the Format you want to add the rule to. 
3. Click on the Rules tab. 
4. Click the + icon to add a new rule. 
5. Select Prevent Reconciliation Submission. 
6. Enter a Description. 
7. Enter a Message to Preparer – this is what the end user will see if the criteria is not met. 
8. Select Use Saved Filter. 
9. Select the Translated Unexplained Difference Threshold filter. 
10. Click OK to save the rule. 

Prior Period Status 

Prior Period Status can be used to bring in various types of data into a current period reconciliation.  This can be useful to bring in balances, variances, or textual information.  This is especially useful to add to a reconciliation to have prior period data easily accessible without having to navigate to another period’s reconciliation. 

An example of a Prior Period Status use case is to calculate the percent variance between the current and prior period balances. 

To create this calculated attribute: 

1. Create a new attribute called All Auto Recon Methods.
2. Select a Type of Yes/No.
3. Check the Calculation box.
4. On the Calculation tab, select a Calculation Type of Conditional.
5. In the Calculation Definition section, add rows to set the value to Yes for Auto Reconciled and Auto Submitted.  The Else would be No.
6. Save the attribute by clicking OK.

Summary 

Calculated attributes can be used for a variety of situations within your application.  These attributes can be added to the reconciliations, transactions, or even action plans.  They can assist with rules or can simply be used to facilitate reporting needs.  While they may seem intimidating at first, once you are able to test a few they really are quite simple to set up.  The examples provided are just the tip of the iceberg on what can be done with calculated attributes. 

Ready to Transform Your Account Reconciliation Processes?

Centroid is ready to help you optimize your account reconciliation and financial planning processes. Connect with our team of Oracle Cloud EPM experts and begin streamlining your account reonciliation workflows today. Contact us or the author to learn more or schedule a complimentary strategy session!

Katie McCorkle

Email

LinkedIn