5Using Rapid Implementation Spreadsheets

This chapter contains the following:

Create Chart of Accounts, Ledger, Legal Entities, and Business Units in Spreadsheets: Explained

Represent your enterprise structures in your chart of accounts, ledger, legal entities, and business unit configuration to track and report on financial objectives and meet reporting requirements. These components provide the underlying structure for organizing financial information and reporting.

The chart of accounts within the ledger facilitates:

  • Aggregating data from different operations, from within an operation, and from different business flows

  • Consistent definitions to your stakeholders in compliance with legislative and corporate reporting standards and aids in management decisions

Rapid implementation is a way to configure a financial enterprise and financial reporting structures quickly using sheets in a workbook that upload lists of:

  • Companies (legal entities)

  • Ledgers by country

  • Business units

  • Chart of accounts and segment values

  • Segment value hierarchies

  • Financial sequences

  • Required subledger accounts

Once the sheets have been uploaded, the application creates:

  • Chart of accounts structure and instance

  • Segment value hierarchies

  • Key accounts such as retained earnings

  • Required subledger accounts

  • Accounting calendar

  • Primary ledger for each country represented on the legal entities sheet

  • Legal entities and their locations

  • Business units

  • Document and journal sequencing

  • Set of Financial Reporting reports

  • Three account groups

The following figure illustrates the flow of the enterprise structure setup.

Legal entities (companies) incur transactions that are identified by business units with business functions. Transactions that are recorded in subledgers are transferred to the ledger. A ledger is characterized by a calendar, a currency, and a chart of accounts. A chart of accounts consists of segments, some of which are assigned segment labels, such as cost center, natural account, and primary balancing segment. Legal entities can be assigned primary balancing segment values.

This figure shows the flow of the enterprise structure
setup from the legal entities to the ledger.

Additional information for some of the common setup objects depicted in the figure follows:

  • Legal Entity: Identifies a recognized party with rights and responsibilities given by legislation, which has the right to own property and the responsibility to account for itself.

  • Business Units: Performs one or many business functions that can be rolled up in a management hierarchy. A business unit can process transactions on behalf of many legal entities. Usually a business unit has a manager, strategic objectives, a level of autonomy, and responsibility for its profit and loss. When created through the spreadsheet, all available business functions are automatically enabled for the business unit.

  • Ledger: Maintains records and is a required component in your configuration. The rapid implementation process:

    • Creates primary ledgers by combining the chart of accounts, calendar, and currency as well as other required options defined in the rapid implementation workbook.

    • Assigns the standard accrual subledger accounting method to the primary ledger. The subledger accounting method is used to group subledger journal entry rule sets together to define a consistent accounting treatment.

    • Creates a General Ledger balances cube for each ledger with a unique chart of accounts and calendar combination. Each segment is created as a dimension in the balances cube along with the standard cube dimensions.

  • Subledger: Captures detailed transactional information, such as supplier invoices, customer payments, and asset acquisitions. Uses subledger accounting to transfer transactional balances to the ledger where they are posted.

  • Chart of Accounts: Configures accounts that consist of components called segments. Accounts are used to record balances and organize financial information and reporting.

  • Segment: Identifies one of the components of a chart of accounts, which when combined with other segments, creates an account combination for recording transactions and journal entries. A segment is associated with a value set, which provides the set of values for that segment, along with the formatting and validation for those values.

  • Segment Label: Identifies certain segments in a chart of accounts and assigns special functionality to those segments.

    • Balancing Segment: Ensures that all journals balance for each balancing segment value or combination of multiple balancing segment values for financial processes and reports. The three balancing segment labels are: Primary Balancing Segment, Second Balancing Segment, and Third Balancing Segment.

    • Natural Account: Determines the account type (asset, liability, expense, revenue, or equity) and specific categorization of the financial activity. Facilitates General Ledger processes, such as closing of the income statement accounts to retained earnings at the beginning of a new fiscal year.

    • Cost Center: Facilitates grouping of natural accounts by functional cost types, accommodating tracking of specific business expenses across natural accounts.

With the rapid implementation workbook you can also:

  • Create more than one hierarchy for any of your chart of accounts segments during initial setup. You can also create additional hierarchies and hierarchy versions, as well as update existing hierarchy versions, after the initial setup is done by uploading the rapid implementation spreadsheet data.

  • Create sequences for each legal entity or ledger based on the predefined country defaults. Document sequences are created for: Payables invoices, Payments, Receivables invoices, Receivables credit memos, Receivables adjustment activities. Reporting and accounting journal sequences are created for subledger journals and General Ledger journals.

Create Charts of Accounts, Ledgers, Legal Entities, and Business Units in Spreadsheets: How It Works

The rapid implementation process for setting up the enterprise structure includes the following steps:

  1. Downloading the Rapid Implementation for General Ledger workbook.

  2. Entering data into the sheets.

  3. Verifying the entered data and resolving any errors.

  4. Uploading the chart of accounts file.

  5. After successful upload of the chart of accounts file, uploading the general ledger, legal entity, and business unit file with the rest of the configuration.

The rapid implementation enterprise structure configuration is meant to be used as a one-time initialization. To the extent that you want to make certain allowed modifications to the configuration, you generally have to make those changes directly in the applications. After initial upload of the ledger, legal entity, and business unit file, the fundamental accounting configuration framework is only created once and is permanently set. This framework includes the ledger and its assigned chart of accounts, calendar and currency assignment, and the associated definitions of those components.

Workbook Overview

Download the workbook from the Setup and Maintenance work area using the task Create Chart of Accounts, Ledger, Legal Entities, and Business Units in Spreadsheet.

The workbook includes the following sheets:

  • Instructions

  • Chart of Accounts, Calendar, and Ledger

  • Business Units

  • Companies and Legal Entities

  • Natural Accounts

  • Financial Sequences

New sheets for entering segment values and hierarchies for additional segments of your chart of accounts can be created automatically. After you enter the segments on the Chart of Accounts, Calendar, and Ledger sheet, click the Add Segment Sheets or Generate Additional Hierarchy buttons.

Instructions

Review the Instructions sheet for important information about how to use the workbook and submit the accounting configuration. The sheet includes data preparation requirements, setup object concepts, and best practices and recommendations. Instructions on how to create additional hierarchies or additional hierarchy versions are also included.

Use the sample completed workbook to familiarize yourself with how to enter data, preview the sample report, and generate the required upload files.

The following figure shows the section of the Instructions sheet called Rapid Implementation Template with Sample Data. This section includes the sample completed workbook, which you can download.

This figure shows the sample data template on the
Instructions sheet.

Chart of Accounts, Calendar, and Ledger

Enter the data to create your chart of accounts, calendar, and ledger.

The following figure shows an example of the Chart of Accounts, Calendar and Ledger sheet with sample values.

This figure shows the Chart of Accounts, Calendar
and Ledger sheet populated with values to create a ledger with its
three basic components: chart of accounts, calendar, and currency.

An explanation of each field on the sheet follows.

  • Name: Enter the name of your primary ledger.

    A primary ledger is created for each unique country that's entered in the Companies and Legal Entities sheet. A country code is appended to the name that you specify. For example, one legal entity is based in the United States and another in Canada. If you enter the ledger name of InFusion Ledger, two primary ledgers are automatically created, InFusion Ledger US and InFusion Ledger CA.

    All of the primary ledgers that are created use the same chart of accounts, account hierarchies, and accounting calendar. Legal entities and their primary balancing segment values are assigned to the primary ledger of their respective countries. If the addresses provided for the legal entities on the Companies and Legal Entities sheet are all in the same country, then only one primary ledger is created.

  • Currency: If you're not entering legal entities and only a single ledger should be created by the rapid implementation configuration, enter the ledger currency in which you want to maintain accounting for in that ledger. If you're entering legal entities, leave this field blank. The currency is automatically supplied based on the country.

  • Period Frequency: Select from among the list of available frequencies for the ledger calendar.

    Caution: For the accounting calendar created using the Rapid Implementation Enterprise Structure solution, the choices of patterns are limited to the period frequency and adjusting periods options that are available for selection in the spreadsheet. It is not possible to make alterations to the pattern or specified fiscal year start date once the calendar has already been created.

    The accounting periods of the calendar are automatically named using a preset format. If you want to change these period names, you have a limited window of time to make those changes. Use the Manage Accounting Calendar page in the application to make the changes before the accounting calendar is being used actively, such as when one of its accounting periods has been set to a status of Open.

  • Adjusting Periods: Select the number of periods used to segregate closing, auditing, or other adjustments in the General Ledger. The entries are tracked in the adjusting period and not in your monthly activity.

  • Fiscal Year Start Date: Enter the start date of the accounting calendar. The date can't be changed after the submission of the configuration.

    Caution: If you plan to run translations, enter a fiscal year start date for the entire accounting year that's before the first period for which you intend to run translations. You can't run translation in the first defined period of an accounting calendar. For example, if your fiscal year starts on January 1, and you want to start translations for the period of Mar-17, then you should select a fiscal year start date of January 1, 2016.

    Also when determining the fiscal year start date, you might want to consider whether you plan to load history.

  • Segment: Enter the names for your segments. The value sets are created from the segments.

  • Segment Label: Select segment labels to assign special functionality to segments.

    Segment labels specifying the segment's purpose, such as balancing, cost center and natural account, can only be assigned once to a chart of accounts segment. The Primary Balancing Segment and Natural Account Segment labels must be assigned, while the other segment labels are optional. Segments that are assigned these two particular labels cannot be assigned any other label. However, segments that are assigned the other remaining labels can also be assigned additional labels, provided they're not Primary Balancing Segment or Natural Account Segment.

    The Intercompany Segment label assignment is optional. If assigned, that segment reuses the value set that's created for the segment with the Primary Balancing Segment label. Using the same value set ensures that the values for both segments remain synchronized.

    Note: For the posting process to apply intercompany balancing, you must select the Enable intercompany accounting option on the Specify Ledger Options page.
    Caution: If you plan to implement segment value security rules for the segment that's assigned the Primary Balancing Segment label, then don't assign the Intercompany Segment label to a segment on this sheet. Segment value security rules are assigned at the value set level. Sharing the value set between the two segments causes security conflicts because segment value enforcement is simultaneously applied in the same way to both segments. For example, you define a segment value security rule for the Company segment where a user can only access company 01. Since the value set is shared, that user also can't transact with other companies in an intercompany transaction.

    Instead, follow these steps:

    1. Include the intercompany segment in the sheet, but don't assign it the Intercompany Segment label.

    2. Click the Add Segment Sheets button to add a sheet for the intercompany value set.

    3. Create the values for your intended intercompany segment on the new sheet. Assign the same values to the intercompany segment as you have for the primary balancing segment and maintain this consistency going forward.

    4. Complete the Upload Chart of Accounts task. Before starting the Upload Ledger, Legal Entities, and Business Units task, navigate to the Manage Chart of Accounts Structures task from the Functional Setup Manager.

    5. Assign the Intercompany Segment label to the intercompany segment of the chart of accounts on the Edit Key Flexfield Segment page.

    6. Redeploy the key flexfield.

  • Short Prompt: Enter a short name for the segment, which is used on applications pages.

  • Display Width: Enter the segment size. Select the size carefully and leave room for growth. For example, if you have 89 cost centers, enter 3 for the display length to allow for more than 100 cost centers in the future.

  • Add Segment Sheets: Select this button to create sheets for additional segments. Sheets are provided only for the Company and Natural Accounts segments.

    From the new segment sheet, you can click the Generate Additional Hierarchy button to create more than one hierarchy for any chart of account segment. A worksheet is then automatically created and populated with the data already entered for that segment. Change this data as required for the new hierarchy. You can create additional hierarchies during initial setup, or after the initial setup is done.

Caution: You can't change the chart of accounts, accounting calendar, or currency for your ledgers after the setup is created.

Business Units

Enter the name of your business units and related default legal entities.

The following figure shows an example of the Business Units sheet with sample values for the Name and Default Legal Entity Name fields.

This figure shows the Business Units sheet.

Business units are created with the names that you enter. You can enter more than one business unit per ledger. Based on the default legal entity specified for the business unit in the Business Units sheet, the business unit is assigned the primary ledger to which its default legal entity is assigned.

Companies and Legal Entities

Enter parent and child values for your Company segment, which is the segment that's assigned the Primary Balancing Segment label on the Chart of Accounts, Calendar, and Ledger sheet. You can create up to nine levels of parent values to roll up your companies to meet corporate and local reporting requirements.

Enter your legal entities for the child values with the address, registration number, and reporting unit registration number. The registration number identifies legal entities registered for your company and recognized by law for which you want to record and perform transactions. The reporting unit registration number identifies the lowest level component of a legal structure that requires registrations.

The following figure shows part of the Companies and Legal Entities sheet with sample values. The sheet includes columns for different levels of parent values, the child value, and company description. The Legal Entity columns include name, identifier, country, address information, and registration numbers.

This figure shows the Companies and Legal Entities
sheet populated with the companies and their addresses and registration
numbers.

To create additional hierarchies for the company segment for reporting or other purposes, click the Generate Additional Hierarchy button. A worksheet is automatically created and populated with the data already entered for that segment. Change this data as required for the new hierarchy. You can create additional hierarchies during initial setup, or after the initial setup is done.

When a new hierarchy sheet is created, the name for that sheet is derived by adding a counter to the sheet name. For example, when you click Generate Additional Hierarchy on the Companies and Legal Entities sheet, the new sheet is named Companies and Legal Entities 1. When you click Generate Additional Hierarchy again, another sheet is generated with the name Companies and Legal Entities 2.

Note: Adding legal entity information isn't supported on a new hierarchy sheet for the Company segment.

Natural Accounts

Enter account hierarchies, account values, and specify account types.

The following figure shows part of the Natural Accounts sheet with sample parent and child values, descriptions, and account type.

This figure shows the Natural Accounts Sheet with
values, account type, and financial category.
  • Parent: Enter parent account values to define hierarchies. Hierarchies are used for chart of accounts mappings, revaluations, data access sets, cross-validation rules, and segment value security rules. The balances cube and account hierarchies are also used for financial reporting, Smart View queries, and allocations.

  • Child: Enter child account values to define the postable accounts.

  • Description: Enter descriptions for the segment values.

  • Account Type: You must assign an account type to each account value. Account types are used in year-end close processes and to correctly categorize account balances for reporting. Select from among general account types and expanded account types. The general account types are: Asset, Liability, Owner's Equity, Revenue, Expense. Expanded account types provide specialized functionality and are used to:

    • Identify the intended usage of your natural account values to facilitate automation and enable completion of other required setup objects. For example, assign the Asset - Intercompany Receivable and Liability - Intercompany Payable expanded account types. The Rapid Implementation process then automatically creates a chart of accounts level intercompany balancing rule, which is a required setup for the application to perform intercompany balancing.

    • Automatically generate fully defined initial Financial Reporting reports and Account Groups based on your enterprise structure.

    Examples of expanded account types include:

    • Asset - Accounts Receivable: For Receivables receipt methods

    • Liability - Accounts Payable: For Payables common options

    • Owner's Equity - Retained Earnings: For General Ledger ledger options

    • Revenue - Top Revenues Parent Account: For sample reports and account groups

    • Expense - Top Operating Expenses Parent Account: For sample reports and account groups

    You must assign the Revenue - Top Revenues Parent Account and Expense - Top Operating Expenses Account account types to the parent accounts that are your highest level and comprehensive revenue and operating expenses accounts. You can optionally assign the account type of Expense - Top Cost of Sales Parent Account, if it's applicable for your scenario.

    The Generate Financial Reports and Account Groups process, which is automatically submitted when the accounting configuration is created in the application, generates a set of Financial Reporting reports and account groups according to the accounting configuration defined in the workbook. The top parent accounts are used as the basis for deriving the accounts referenced in the reports and in the Account Groups.

    The immediate descendants of the top parent accounts are used to define the rows on the reports. Depending on whether both the top operating expense and top cost of sales accounts are tagged, different variations of the income statements are generated. If the optional top cost of sales account is provided, the Financial Reporting reports that are income statements also include a gross margin section.

    Caution: Assign account types carefully. If you assign an incorrect account type to a natural account segment value, accounting entries are recorded incorrectly and financial statements are inaccurate. Misclassified accounts are also potentially handled incorrectly at year end, with actual balances either getting zeroed out to retained earnings, or accumulating into the next year.
  • Financial Category: Select a value to identify groups of accounts for reporting with Oracle Transactional Business Intelligence. Accounts that are tagged with expanded account types are automatically assigned a financial category. You can override the default category or leave it out.

  • Generate Additional Hierarchy: To create additional hierarchies for the natural account segment for reporting or for other purposes, click the Generate Additional Hierarchy button. A worksheet is automatically created and populated with the data already entered for that segment. Change this data as required for the new hierarchy. You can create additional hierarchies during initial setup or after the initial setup is done.

Financial Sequences

Enable document or journal sequences to assign unique numbers to transactions to meet legal requirements.

The following figure shows the Financial Sequences sheet with sample values for the Restart and Initial Value columns.

This figure shows the Financials Sequences sheet
where you set restart and initial values for financial document and
journal sequences.

Document sequences are created for these transactions: Payables invoices, Payments, Receivables invoices, Receivables credit memos, Receivables adjustment activities. Reporting and accounting journal sequences are created for Subledger journals and General Ledger journals.

For each transaction, you can provide values for the following fields:

  • Restart: Set when to restart the numbering: Annually, Monthly, Never.

  • Initial Value: Specify the beginning number in the sequence.

How The Worksheets Are Processed

After you complete the worksheets, proceed with validation, sample report preview, and file upload.

  1. On the Chart of Accounts, Calendar, and Ledger sheet, click the Step 1: Validate button.

    The validation checks the worksheets for missing or inappropriate setups. Errors are marked as actionable items in a validation report sheet that's dynamically generated. You can review the anomalies and make the corrections as indicated. The Field column on the validation report notes the issue. Click the text link to navigate to the appropriate field in the sheet that must be updated. When the validation is successful, a message appears with the option of previewing a sample of the reports that are automatically generated as part of the enterprise configuration.

    The following figure shows the message that appears after a successful validation.

    This figure shows the message for a successful
validation with the option to provide preview sample reports.

    If you select to preview the sample report, a new sheet is automatically created called Preview Report. The preview incorporates elements of the setup that you provided. The rows on the report are derived based on the top parent revenue and expense account values that you tagged on the Natural Accounts sheet. The preview also reflects the reporting hierarchy for your natural accounts.

    The following figure shows an example of the sample Financial Reporting report.

    This figure shows a sample rolling quarterly income
statement that appears when you accept the option to preview the sample
report. The report has four columns with quarterly amounts, followed
by a column with totals. The rows on the report list the revenue accounts
followed by a row for total revenues. The account for the cost of
goods sold follows, with a subsequent calculation of gross margin.
The next rows list the operating expense accounts, followed by a total.
The last row contains the net income. The next section on the report
is a graphical representation of the quarterly trends for Total Revenues,
Total Cost of Sales, Gross Margin, Total Operating Expenses and Net
Income.

    You can use the preview to validate whether the hierarchy setup aligns to your reporting needs. If the natural account hierarchy requires adjustments, this is your chance to make those corrections before actually creating the account hierarchies in the application. You can modify your enterprise structure setup, validate the spreadsheet, and preview the revised sample reports for as many times as you need. The account hierarchies are created when you finally submit the accounting configuration in the rapid implementation spreadsheet.

  2. Click Step 2: Generate Chart of Accounts File. The process generates a data file called ChartOfAccounts.xml with the entered chart of accounts and hierarchies setup data. Save the file to a network or local drive.

  3. Click Step 3: Generate Ledger, LE, and BU File. The process generates a data file called FinancialsCommonEntities.xml with the entered ledger, legal entities, and business unit setup data. Save the file to a network or local drive.

  4. In the Setup and Maintenance work area, search for the Upload Chart of Accounts task. The Upload Enterprise Structures and Hierarchies process is launched.

  5. Accept the default selection of the Upload Enterprise Structure option.

  6. Click Browse and select the first file that you saved called ChartOfAccounts.xml.

  7. Click Submit.

  8. Verify that the process completed without errors or warnings.

  9. In the Setup and Maintenance work area, search for the task Upload Ledger, Legal Entities, and Business Units. The Upload Enterprise Structures and Hierarchies process is launched.

  10. Accept the default selection of the Upload Enterprise Structure option.

  11. Click Browse and select the second file that you saved called FinancialsCommonEntities.xml.

  12. Click Submit.

  13. Verify that the process completed without errors or warnings.

An individual set of the following Financial Reporting reports is generated for each ledger that's defined within the rapid implementation accounting configuration. If multiple primary ledgers are created as part of your configuration, a set of Financial Reporting reports is generated for each ledger.

  • Income Statement

  • Consolidated Income Statement

  • Rolling Quarterly Income Statement

  • Rolling Monthly Income Statement

  • Trial Balances by Ledger Currency

  • Trial Balances by Entered Currency

The process also generates three account groups. These include two for the infolets, Revenues and Expenses, and one for the Close Monitor called Close Monitor Summary Income Statement. A set of these three account groups is generated for the balances cube, to be shared among all the ledgers that are part of that balances cube.

Additional Hierarchies After Initial Setup

To create additional hierarchies and hierarchy versions, or to update existing hierarchy versions after the initial setup:

  1. Click the Generate Additional Hierarchy button on the applicable segment sheet. A new worksheet is automatically created and populated with the data already entered for that segment. Change the data as required.

  2. Click the Generate File for This Hierarchy Only button. This generates a .zip file for the particular hierarchy.

  3. In the Setup and Maintenance work area, search for the Upload Chart of Accounts task. The Upload Enterprise Structures and Hierarchies process page opens.

  4. Select the Upload Hierarchy option.

  5. Select from among the following options and provide values for the required parameters:

    1. Create hierarchy: Select to create another account hierarchy. Specify the value set, tree code, and start date.

    2. Create version: Select to render a new version of an existing account hierarchy. Specify a value set, tree code, tree version, and start date.

    3. Update existing version: Select to edit an existing version of an account hierarchy. Specify a value set, tree code, and tree version.

  6. Click Choose File and select the .zip file that you saved earlier.

  7. Click Submit.

Create Hierarchies in a Spreadsheet: Example

Use the Hierarchies Only sheet to create new hierarchies or new versions of existing hierarchies after enterprise structures have been created using the Rapid Implementation spreadsheet.

Scenario

Plan your new hierarchies or new versions carefully, including the following requirements:

  • Compile the hierarchy in the specified format and save the file.

  • Use the Account Type attribute that is mandatory for the Natural Account segment.

  • Optionally, use the Financial Category attribute for the Natural Account segment.

Note: Only one Hierarchies Only sheet can be created at a time. After uploading a hierarchy, delete the data in the sheet and add new hierarchy data.

Use the following steps to create your hierarchy.

  1. Click the Create Only Hierarchies button on the COA, Calendar and Ledger sheet to generate the Hierarchies Only sheet.

  2. Compile the hierarchy in this sheet in the format provided.

  3. Click the Validate button. A Validation Report is generated if there are any errors. Correct these errors to proceed.

  4. Correct these errors and proceed.

  5. Click the Generate Hierarchy File button. The process generates a .zip file for the new hierarchy. Save this file.

  6. From the Setup and Maintenance page in the Functional Setup Manager, search for and select the Upload Chart of Accounts task.

  7. Click on the Upload Hierarchy radio button.

  8. Select Create New Hierarchy or Create New Version radio button

  9. Under the Create New Hierarchy option, select the Value Set, enter the Tree Code and Start Date.

  10. Click the Choose File button.

  11. Select the file you saved.

  12. Click Submit.

  13. Under the Create New Version option, select the Value Set and Tree Code.

  14. Enter the Tree Version Name and Start Date.

  15. Click the Choose File button and select the file you saved.

  16. Click Submit.

  17. Navigate to the Scheduled Processes page.

  18. Verify that the process completed without errors or warnings.

Cross-Validation Rules in General Ledger: Overview

You can use cross-validation rules to determine the valid account combinations that can be dynamically created as users enter transactions or journal entries. Once enabled, a cross-validation rule determines whether a selected value for a particular segment of an account combination can be combined with specific values in other segments to form a new account combination.

For example, your organization has determined that the company Operations can't use the cost center Marketing. You can define a cross-validation rule such that, if the company is Operations, then validate that the cost center isn't Marketing. New account combinations have to satisfy all of the cross-validation rules enabled for the chart of accounts before they can be created.

Entry and Maintenance

You can create cross-validation rules using the Create Cross Validation Rules in Spreadsheet task or the Manage Cross-Validations Rules task. Use the spreadsheet task to quickly enter large volumes of rules during implementation. Use the Manage Cross-Validation Rules task to add a one-off rule or edit existing rules.

To edit error messages for cross-validation rules, use the Manage Messages for General Ledger task.

Tip: When you export or import cross-validation rules to a new instance using an export or import project in the Functional Setup Manager, you must add the Manage Messages for General Ledger task before the Manage Cross-Validation Rules task. You must export or import the messages before exporting or importing the cross-validation rules.

Existing Account Combinations

If account combinations already exist that violate newly enabled cross-validation rules, those account combinations continue to be valid. Before disabling existing account combinations that violate your rules and that you no longer use, move the balances in those accounts to the correct accounts. Then disable the account combinations manually to prevent further posting. Best practice is to define and enable cross-validation rules before: account combinations are created, transactions or journal entries are imported or entered, balances are loaded.

Cross-Validation Rules Spreadsheet: Explained

The rapid implementation solution provides a template for defining cross-validation rules in a spreadsheet. Cross-validation rules determine whether a selected value for a particular segment of an account combination can be combined with specific values in the other segments to form a new account combination.

You can download the spreadsheet using the Create Cross Validation Rules in a Spreadsheet task. This task is included in the following task lists:

  • Define Common Financials Configuration for Rapid Implementation

  • Define Enterprise Structures Configuration for Rapid Implementation

Note: The spreadsheet can only create cross-validation rules. To update existing cross-validation rules, use the Manage Cross-Validation Rules task.

Spreadsheet Overview

The cross-validation rules spreadsheet includes two sheets. One sheet provides the template for creating cross-validation rules and the other sheet has instructions. The Instructions sheet includes:

  • An overview

  • An explanation of the template

  • Steps to fill in the template

  • An example

The following figure shows the Create Cross-Validation Rules sheet.

This figure shows the Create Cross-Validation Rules
sheet.

The following table describes each field and column on the sheet.

Field or Column Description

Worksheet Status

The upload results for the worksheet. The application updates this field when you submit the spreadsheet.

Chart of Accounts

The chart of accounts for which the cross-validation rules are defined.

Changed

The indicator that the row has been updated. The application updates this field.

Row Status

The upload results for the row. The application updates this field when you submit the spreadsheet.

Name

The name that uniquely identifies the cross-validation rules in a deployment.

Description

The purpose for the cross-validation rule.

Error Message

The explanation to users for why the attempted combination violates the cross-validation rule.

Condition Filter Segment

The segments of the chart of accounts that constitute the condition filter.

Condition Filter Values

The values of the condition filter segment that determine whether the cross-validation rule is evaluated.

Validation Filter Segment

The segments of the chart of accounts that constitute the validation filter.

Validation Filter Values

The values of the validation filter segment used to enforce a new account combination.

Note: Cross-validation rules created from the spreadsheet are automatically enabled and don't have a start or end date.

Steps to Use the Template

To use the spreadsheet template:

  1. Select the chart of accounts.

  2. Enter a suitable name, description, and error message in the respective columns.

  3. Select the condition filter segment. To add more than one segment to the condition filter, use the next row. Repeat the rule name and select the condition filter segment.

  4. Provide the segment values that constitute the condition filter in the Condition Filter Values column.

    • Enter detail values separated by commas for multiple detail values, for example: 5501,5502,5503.

    • Enter detail values separated by a hyphen for a range of values. You can enter multiple ranges using the comma as the range separator, for example: 3001-3030,3045-3200.

    • Enter a parent value to select all of the detail values that are descendants of the specified parent. You can enter multiple parent values using commas as the separator, for example: 1000,2000.

    • You could enter all of the previously listed values in the same cell, for example: 1000,2000,3001-3030,3045-3200,5501,5502,5503.

  5. Select the validation filter segment. To add more than one segment to the validation filter, use the next row. Repeat the rule name and select the validation filter segment.

  6. Provide the segment values that constitute the validation filter in the Validation Filter Values column in the same way as specified for the condition filter.

  7. Review the data that you entered and click Submit to publish the cross-validation rules.

  8. Review the upload results in the Worksheet Status and Row Status fields.

Cash Management Rapid Implementation: Overview

Use Microsoft Excel templates to rapidly implement the following setup objects:

  • Banks

  • Bank Branches

  • Bank Accounts

Functional Setup Manager Tasks

The following are the Functional Setup Manager tasks that are required to be performed to rapidly create the setup objects data:

  • Create Banks, Branches, and Accounts in Spreadsheet: Downloads the rapid implementation excel spreadsheet template. Enter the bank, branch, and bank account data in this spreadsheet, and generate the data file to be loaded.

  • Upload Banks, Branches, and Accounts: Launches the Upload Banks, Branches, and Accounts process with the data file to be uploaded as the parameter. You must upload the data file generated from the previous task.

Preparing Data

Prepare your bank, branch, and account information to enter into the spreadsheet template.

  • Bank information requires the country, name, and number.

  • Branch information requires name, number, BIC code, and alternate name.

  • Account information requires name, number, currency, legal entity, type, and IBAN.

After you finish preparing the data in the spreadsheet, click the Generate Banks, Branches, and Accounts File button. Save the generated XML file.

Loading Data

Use the following steps to load your data.

  • From Functional Setup Manager, search for and select Upload Banks, Branches, and Accounts task. This task launches the Upload Banks, Branches, and Accounts process.

  • Select the XML file you have saved earlier and submit the process.

  • Verify in the process monitor that the process completed successfully.

  • Review the banks, branches, and accounts created.

Best Practices

The following are recommended best practices:

  • Determine the Legal Entity for each bank account. The Legal Entity must be associated to a primary ledger.

  • Determine the use for each bank account: Payable, Receivable, or both.

  • Determine the Cash and Cash Clearing account for each bank account. Enter the entire account combination based on your chart of accounts, for example 01-000-1110-0000-000.

Tax Configuration Workbook: Explained

Use the Tax Configuration Workbook to upload all common tax setups into Oracle Fusion Tax. For example, create standard state, county, and city sales tax rates within the US using this workbook.

Tax Configuration Workbook Worksheets

The Tax Configuration Workbook is a Microsoft Excel spreadsheet template with six common tax setup worksheets:

Worksheet Predefined Data Content Setup Options

Manage Tax Regimes

Yes

Option 1: Use the tax regimes that are already included for 28 countries. You can modify or delete any of the predefined tax regimes where needed.

Option 2: Use tax partner content for the Tax Configuration Workbook.

Manage Taxes

Yes

Option 1: Use the taxes that are already included for 28 countries. You can modify or delete any of the predefined taxes where needed.

Option 2: Use tax partner content for the Tax Configuration Workbook.

Manage Tax Zones

No

Prepare the tax zones with the appropriate corresponding geographies.

Manage Rates

No

Option 1: Prepare the tax rates.

Option 2: Use tax partner content for the Tax Configuration Workbook.

Manage Tax Thresholds

No

Option 1: Prepare the tax thresholds or maximum taxes.

Option 2: Use tax partner content for the Tax Configuration Workbook.

Manage Tax Recovery Rates

No

Option 1: Prepare the tax recovery rates.

Option 2: Use tax partner content for the Tax Configuration Workbook.

Creating Tax Setup Using the Tax Configuration Workbook: Worked Example

This example shows how to create standard state, county, and city sales tax rates within the US using the Tax Configuration Workbook.

The following table summarizes key decisions for this scenario:

Decision to Consider In This Example

What tax setup are you creating?

Tax Rates

Do you have exception rules for calculating US sales tax on transactions?

No

Do you use tax partner content?

No

Creating Tax Setup

The steps to create tax rates in the Tax Configuration Workbook are:

  1. On the Manage Tax Regimes page, click Download Tax Configuration Workbook in the Rapid Setup Spreadsheets list in the Search Results table.

  2. Save the Tax Configuration Workbook in your local directory.

  3. Review the detailed Tax Configuration Workbook instructions tab.

  4. For the Manage Tax Regimes and Manage Taxes worksheets, use the predefined content for US sales tax. You can modify or delete the predefined content where needed.

  5. Using the instructions and the column help text, populate the required setups in the Manage Rates worksheet.

  6. After completing the Tax Rates worksheet, click the Generate CSV File button in the Instructions tab.

  7. A macro launches and saves the:

    1. Entire Tax Configuration Workbook data in a comma separated values (CSV) file.

    2. CSV file into a single zipped file attachment.

  8. Save the zipped file attachment in your local directory.

  9. On the Manage Tax Regimes page, click Upload Tax Configuration Workbook in the Rapid Setup Spreadsheets list in the Search Results table.

  10. Browse your local directory in which you saved the zipped file attachment and select the file.

  11. Click Open and then click Upload.

  12. Note the process ID and access the Monitor Upload and Download Processes tab.

  13. Click the Refresh icon and ensure that the process ID completes with a Succeeded status.

    • If the status of the upload process is Succeeded, you can view your setups using the search criteria in this page.

    • If the upload process failed with a status other than Succeeded, check the details in the corresponding error log, correct any file errors, and upload the file again.

Budget Uploads

Budget Uploads: Overview

In Oracle General Ledger, you can load budget data to perform variance reporting.

If you use a third-party budgeting application or don't use a budgeting application, there are two ways to load budgets into the GL Balances Cube.

  • Importing Budget Data from a Flat File: Export budget data from your budgeting application to a comma separated values .csv file. Use the Import General Ledger Budget Balances file-based data import to prepare and generate flat files in a .csv format. You can use Oracle Application Development Framework Desktop Integrator correction worksheets to correct validation errors, delete rows with errors, and resubmit the corrected error rows.

    Note: For more information about file-based data import, see the File-Based Data Import for Oracle Financials Cloud guide.
  • Importing Budget Data from a Spreadsheet: You can access the budget load spreadsheet from the General Accounting Dashboard. Enter, load, and correct budget data in the ADF Desktop Integrator spreadsheet tool. Use this tool to prepare and load budget data for multiple ledgers and periods with a common chart of accounts instance. The list of values and the web picker help you select valid values. This simplified data entry reduces errors and alerts you to errors as you enter the data in the spreadsheet. Error correction is done in the same spreadsheet.

The following figure shows the process flow for budget upload. Prepare your budget data, upload it using a spreadsheet or flat file, and report on the budget data.

This figure shows the process flow of preparing,
uploading, and reporting on a budget.
Caution: When the GL Balances Cube is rebuilt, the process retains the budget balances as well as the actual balances. Only the budget balances loaded using the spreadsheet or flat file through the GL Budget Balances interface table are retained.

Create reports in Smart View or Financial Reporting to verify that the budget data was loaded correctly.

Importing Budget Data from a Spreadsheet: Explained

You can use the Create Budgets spreadsheet to enter, load, and correct budget data. To open the spreadsheet, navigate to the General Accounting Dashboard and select the Create Budgets in Spreadsheet task.

Budget Import

The spreadsheet uses the Oracle ADF Desktop Integration Add-In for Excel, which is the same add-in used by the Create Journals spreadsheet. The spreadsheet uses an interface table called the GL_BUDGET_INTERFACE and requires the Budget Entry duty. The budget import uses the Accounting Scenario value set for the budget being loaded. The Run Name is used as an identifier for the imported data set.

The spreadsheet budget import:

  • Supports multiple ledgers but a single chart of accounts instance

  • Allows multiple calendars and periods

  • Supports entered currencies in addition to the ledger currency

  • Contains user-friendly lists of values

  • Performs most validations on the worksheet

  • Secures values by data access sets

Note: The spreadsheet includes a Row Status column that shows if the rows upload successfully or with errors. Use the spreadsheet where the data was entered to enter the corrections.

Importing Budget Data from a Flat File: Explained

Use the Upload Budgets processes to integrate budget information from other budgeting applications such as Oracle Hyperion Planning. You can load your budget amounts to the General Ledger balances cube by populating the GL_BUDGET_INTERFACE table and running the Validate and Upload Budgets process. You can load budgets for multiple periods and for multiple ledgers with the same chart of accounts in a single load process. Note that the budget data isn't loaded to the GL_BALANCES table and only loaded to the balances cube for variance reporting purposes.

Note: You can load data to interface tables using predefined templates and the Load Interface File for Import scheduled process, which are both part of the External Data Integration Services for Oracle Cloud. For more information about file-based data import, see the File Based Data Import guide for your cloud services.

Assigning Values for Columns in the GL_BUDGET_INTERFACE Table

For budget import to be successful, you must enter values in the columns of the interface table that require values.

The following table describes the columns that require values.

Name Value

RUN_NAME

Enter a name to identify the budget data set being imported.

STATUS

Enter the value NEW to indicate that you are loading new budget data.

LEDGER_ID

Enter the appropriate ledger ID value for the budget amount. You can view the ledger ID for your ledgers on the Manage Primary Ledgers page. The ledger ID column is hidden by default, but you can display it from the View Columns menu. If you enter multiple ledgers for the same run name, all of the ledgers must share the same chart of accounts.

BUDGET_NAME

Enter the appropriate budget name value for the budget line. You define the budget names in the Accounting Scenario value set.

PERIOD_NAME

Enter the period name that you are loading the budget data for. You can load budget data to Never Opened, Future Enterable, and Open periods only.

CURRENCY_CODE

Enter the currency for the budget.

SEGMENT1 to SEGMENT30

Enter valid and enabled account values for each segment in the chart of accounts.

BUDGET_AMOUNT

Enter the amount in the ledger currency for account types, expense and assets.

OBJECT_VERSION_NUMBER

For Oracle Cloud implementations, leave this field blank as the application automatically populates this when you load the data from the secure FTP server. For other implementations, you can set the column to a value of 1.

These columns remain blank because the budget import process either uses these columns for internal processing, or doesn't currently use them.

  • CHART_OF_ACCOUNTS_ID

  • CODE_COMBINATION_ID

  • ERROR_MESSAGE

  • CREATION_DATE

  • CREATED_BY

  • LAST_UPDATE_DATE

  • LAST_UPDATE_LOGIN

  • LAST_UPDATED_BY

  • REQUEST_ID

  • LOAD_REQUEST_ID

Loading Data to the Budget Interface Table: Explained

Load the budget amounts to the interface table by performing the following steps.

  1. Export budget data from your budgeting application to a comma separated values (.csv) file. The file-based data import for Import General Ledger Budget Balances has a template that you can use. For more information about file-based data import, see the File Based Data Import for Oracle Financials Cloud guide.

  2. Upload the zipped .csv file to the UCM directory fin/budgetBalance/import.

  3. Launch the scheduled process called Load Interface File for Import and select the following parameters:

    • Import process: Validate and Upload Budgets

    • Data file: Select the name of the zipped .csv file

  4. Run the Validate and Upload Budgets process to load the budget amounts to the General Ledger balances cube.

  5. Review the logs for validation errors. If there are validation errors, correct the data in the template and regenerate the .csv file. Then resubmit the data by repeating steps 3 and 4.