5Using Rapid Implementation Spreadsheets

This chapter contains the following:

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

Caution: Once you begin using your chart of accounts, calendar, and ledger, making changes to their fundamental attributes is neither recommended nor supported. This includes your chart of account segments, including the segment labels as well as other characteristics of those segments, and your calendar structure or pattern.

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.

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

In the Setup and Maintenance work area, create an implementation project that includes the Define Financials Configuration for Rapid Implementation task list. Download the workbook using the Create Chart of Accounts, Ledger, Legal Entities, and Business Units in Spreadsheet task.

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 Add Segment Sheets or Generate Additional Hierarchy.

Note: The rapid implementation process creates a standard ledger. You can convert a standard ledger to an average daily balance ledger before the first period is opened by selecting the Enable average balances check box on the Specify Ledger Options page.

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.

Caution: Once you begin using your chart of accounts, calendar, and ledger, making changes to their fundamental attributes is neither recommended nor supported. This includes your chart of account segments, including the segment labels as well as other characteristics of those segments, and your calendar structure or pattern.

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, in the Offerings work area, go to the following:

      • Offering: Financials

      • Functional Area: Financial Reporting Structures

      • Task: Manage Chart of Accounts Structures

    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. From your implementation project, go to 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. From your implementation project, go to the Upload Ledger, Legal Entities, and Business Units task. 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. From your implementation project, go to the Upload Chart of Accounts task. The Upload Enterprise Structures and Hierarchies process is launched.

  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.

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 in the Setup and Maintenance work area using the following tasks:

  • Offering: Financials

  • Functional Area: General Ledger

  • Task: Create Cross Validation Rules in Spreadsheet

  • Offering: Financials

  • Functional Area: Financial Reporting Structures

  • Task: Manage Cross-Validations Rules

Use the Create Cross Validation Rules in 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 to edit existing rules. To edit the error messages for cross-validation rules, use the following task in the Setup and Maintenance work area:

  • Offering: Financials

  • Functional Area: Financial Reporting Structures

  • Task: Manage Messages for General Ledger

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.

In the Setup and Maintenance work area, use the following:

  • Offering: Financials

  • Functional Area: General Ledger

  • Task: Create Cross Validation Rules in Spreadsheet

Note: The spreadsheet can only create cross-validation rules. To update existing cross-validation rules, use the Manage Cross-Validation Rules task in the Setup and Maintenance work area.

Spreadsheet Overview

The cross-validation rules spreadsheet includes two sheets. One sheet has instructions and the other sheet provides the template for creating the cross-validation rules. 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.

    • To select multiple detail values, enter the detail values separated by commas. For example: 5501,5502,5503.

    • To select a range, enter the detail values separated by hyphens. You can enter multiple ranges using the comma as the range separator. For example: 3001-3030,3045-3200.

    • To select all detail values that are descendants of a parent, enter the parent value. 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.

    • To specify that a detail value should not be selected, prefix the value with the less than and greater than symbols <>. These symbols represent the Does Not Equal operator. For example, <>5501 means the rule applies when the segment value isn't equal to 5501.

      • This operator can't be used for parent values or ranges.

      • This operator can't be used more than once for the same rule and segment.

  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.

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. To access these tasks, create an implementation project that includes the Define Financials Configuration for Rapid Implementation task list:

  • 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.

  • In the Setup and Maintenance work area, create an implementation project that includes the Define Financials Configuration for Rapid Implementation task list. From your implementation project, go to the 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.

Use the Tax Configuration Workbook to upload all common tax setups. 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

To create tax rates in the Tax Configuration Workbook, perform the following steps:

  1. Navigate to the Manage Tax Regimes page.

  2. From the Rapid Setup Spreadsheets list in the Search Results table, click Download Tax Configuration Workbook.

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

  4. Review the details on the Tax Configuration Workbook instructions tab.

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

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

  7. After completing the Tax Rates worksheet, click Generate CSV File.

  8. A macro launches and saves the:

    • Entire Tax Configuration Workbook data in a Comma Separated Values (CSV) file.

    • The CSV file into a single compressed file attachment.

  9. Save the compressed file attachment in your local directory.

  10. From the Rapid Setup Spreadsheets list in the Search Results table, click Upload Tax Configuration Workbook.

  11. Browse the local directory in which you saved the compressed file attachment and select the file.

  12. Click Open and then click Upload.

  13. Note the process ID and click the Monitor Upload and Download Processes tab.

  14. 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 on the 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.

Use the Upload Customers from Spreadsheet process to upload customer data in bulk. The single upload process performs all the operations of generating a batch, transferring the customer data in the spreadsheet template to the interface tables, and importing the data from the interface tables into Oracle Applications.

You can download a customer spreadsheet template to use to prepare your customer data. The template contains an instruction sheet and sample data to help guide you through the process of entering your customer information.

Setting Up Related Customer Information

Set up the business objects you need in advance of the customer data upload.

This can include:

  • Account address sets: Set up the reference sets you need for your customer account sites.

  • Customer profile classes: Set up one or more profile classes for your customer records.

  • Reference accounts: Set up general ledger accounts that you intend to use as reference accounts for customers.

  • Customer bank accounts: Set up banks and bank account information.

  • Tax information: Set up tax registration numbers and tax rate codes using Tax.

  • Descriptive flexfields.

Using the Spreadsheet Template

Enter data in the designated columns in each of the four worksheets: Customers, Contacts, Reference Accounts, Customer Bank Accounts.

These rules apply to entering data in columns:

  • Column labels with an asterisk (*) denote required columns.

  • Use the Show Extensible Attributes and Hide Extensible Attributes buttons to show or hide additional columns.

  • Don't move or delete existing columns, and don't insert new columns.

  • Enter data in the correct format. In most cases, the columns will format the data that you enter according to the requirements of the upload.

  • Each customer must have a unique combination of these values:

    • Customer number.

    • Customer account number.

    • Customer site number.

  • Each customer contact must have a unique person number.

During upload processing, the Upload Customers from Spreadsheet process checks for unique values in certain columns of the Customers worksheet and Contacts worksheet. If the values are unique, then the record is created. If the values are not unique, then the record fails with an upload error.

The columns with this validation are:

  • Customers worksheet:

    • Customer Number

    • Account Number

    • Site Number

  • Contacts worksheet:

    • Person Number

The following sections provide examples of the validation process. The assumption in these examples is that all records have the same Source System value.

Customers Worksheet: Customer Number Validation

The Customer Number validation looks for a unique combination of values across the Customer Number, Customer Source Reference, and Customer Name columns.

The records in the following table fail the uniqueness validation on the customer number, because, for the same customer name, there are two different customer numbers and customer source references.

Customer Number Customer Source Reference Customer Name

VCORP 256113

VCORP 256113

Vision Corporation

VCORP 256114

VCORP 256114

Vision Corporation

The records in the following table also fail the uniqueness validation on the customer number, because, for the same combination of customer number and customer source reference, there are two different customer names.

Customer Number Customer Source Reference Customer Name

VCORP 256113

VCORP 256113

Vision Corporation

VCORP 256113

VCORP 256113

Vision ABC Corporation

The records in the following table also fail the uniqueness validation on the customer number, because, for the same customer number, there are two different customer source references.

Customer Number Customer Source Reference Customer Name

VCORP 256113

VCORP 256113

Vision Corporation

VCORP 256113

VCORP 256114

Vision Corporation

In like manner, the Account Number validation looks for a unique combination of values across the Account Number, Account Source Reference, and Account Description columns. The Site Number validation looks for a unique combination of values across the Site Number, Site Source Reference, and Site Name columns.

Contacts Worksheet: Person Number Validation

The Person Number validation looks for a unique combination of values across the Person Number, Person Source Reference, and First Name and Last Name columns.

The records in the following table fail the uniqueness validation on the person number, because, for the same combination of person number and person source reference, there are two different first name and last name combinations.

Person Number Person Source Reference First Name Last Name

1000228801

1000228801

Rodney

Jones

1000228801

1000228801

John

Jones

The records in the following table also fail the uniqueness validation on person number, because, for the same person number, there are two different person source references.

Person Number Person Source Reference First Name Last Name

1000228801

1000228801

Rodney

Jones

1000228801

1000228802

Rodney

Jones

The records in the following table pass the uniqueness validation. The validation process allows a combination of two different person numbers and person source references with the same first name and last name combination. This is because two different people may have the same name.

Person Number Person Source Reference First Name Last Name

1000228801

1000228801

Rodney

Jones

1000228802

1000228802

Rodney

Jones

Budget Uploads to General Ledger

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.

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 GL_BUDGET_INTERFACE and requires the Budget Entry role. 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.

Import General Ledger Budget Balances: How Data Is Processed

Use the Import General Ledger Budget Balances file-based data import to load budget data from external sources for upload to the GL balances cube. You can download a budget spreadsheet template to use to prepare your budget data. The template contains an instruction sheet to help guide you through the process of entering your budget information.

To access the template, complete the following steps:

  1. Navigate to the File-Based Data Import for Oracle Financials Cloud guide.

  2. In the Table of Contents, click File-Based Data Imports.

  3. Click Import General Ledger Budget Balances.

  4. In the File Links section, click the link to the Excel template.

Follow these guidelines when preparing your data in the worksheet:

  • Enter the required information for each column. Refer to the tool tips on each column header for detailed instructions.

  • Do not change the order of the columns in the template.

  • You can hide or skip the columns you do not use, but do not delete them.

Settings That Affect the General Ledger Budget Balances Import Process

The Import General Ledger Budget Balances template contains an instructions tab and a tab that represents the table where the data is loaded.

The Instructions and CSV Generation tab contains information about:

  • Preparing the budget data.

  • Understanding the format of the template.

  • Entering budget data.

  • Loading the data into the interface table and the GL balances cube.

The GL_BUDGET_INTERFACE tab is where you enter information about the budget data that you adding, such as the ledger, budget name, periods, segment values, and amounts.

How General Ledger Budget Balance Import Data Is Processed

To load the data into the interface table:

  1. Click the Generate CSV File button on the instructions tab to create a CSV file in a .zip file format.

  2. Save the .zip file locally.

  3. Navigate to the Scheduled Processes work area.

  4. Select the Load Interface File for Import process.

  5. For the Import Process parameter, select Validate and Upload Budgets.

  6. For the Data File parameter, select the file that you saved in step 2.

To load the data from the interface table to the balances cube:

  1. Navigate to the Scheduled Processes work area.

  2. Select the Validate and Upload Budgets process.

  3. Enter values for the Run Name parameter.

  4. If the process ends in error or warning:

    1. Review the log and output files for details about the rows that caused the failure.

    2. Navigate to the General Accounting Dashboard work area.

    3. Select the Correct Budget Import Errors task to download the budget corrections worksheet.

    4. Correct the entries in the worksheet and resubmit the Validate and Upload Budgets process.

Use the upload budgets processes to integrate budget information from other budgeting applications such as Oracle Hyperion Planning. Use the Import General Ledger Budget Balances file-based data import to load budget data from external sources for upload to the GL balances cube. 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: Budget data isn't loaded to the GL_BALANCES table and only loaded to the balances cube for variance reporting purposes.

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

Budget Import to Budgetary Control

Budgetary Control Budget Amounts Import: How Data Is Processed

Use the Import Budget Amounts process to load budget amounts from external sources for creating or revising budget balances in Oracle Cloud Budgetary Control. You can download a budget import spreadsheet template to prepare your budget data. The template contains an instruction sheet that guides you through the process of entering your budget information.

Determine the following key aspects in preparing budget data before you generate the CSV file:

  • Control Budgets

  • Budget Segments and Segment Values

  • Budget Periods

  • Worksheet Format Preference

You can use the import process to load budget amounts using any one of the following interface tables:

  • XCC_BUDGET_INTERFACE

  • XCC_BUDGET_MULTI_PERIOD_INT

The following figure describes the flow of importing budget amounts into Oracle Cloud Budgetary Control.

The graphic describes the process of importing
budget amounts into Oracle Cloud Budgetary Control.

To access the template, complete the following steps:

  1. Navigate to the File-Based Data Import for Oracle Financials Cloud guide.

  2. In the Table of Contents, click File-Based Data Imports.

  3. Click Budgetary Control Budget Import.

  4. In the File Links section, click the link to the Excel template.

Follow these guidelines when preparing your data in the worksheet:

  • Enter the required information for each column. Refer to the tool tips on each column header for detailed instructions.

  • Do not change the order of the columns in the template.

  • You can hide or skip the columns you do not use, but do not delete them.

Settings That Affect The Budget Amounts Import Process

The Budget Import template contains an instructions tab, and two tabs that represent the interface tables that you can use to upload budget data. The following table lists the tabs and their descriptions.

Spreadsheet Tab Description

Instructions and CSV Generation

Read instruction information about preparing and loading data, the format of the template, submitting the Import Budget Amounts process, and correcting import errors.

XCC_BUDGET_INTERFACE

Enter information about the budget that you are adding, such as the source budget type, source budget name, budget entry name, line number, amount, currency code, period name, UOM code, and the segments in which the budget data will be added.

XCC_BUDGET_MULTI_PERIOD_INT

Enter information about the budget that you are adding, such as the source budget type, source budget name, budget entry name, line number, amount, currency code, period name, UOM code, and the segments to which the budget data will be added.

How Budget Amounts Import Data Is Processed

To process budget amounts, you must successfully load your data, and then submit the Import Budget Amounts process to import the data into the application tables and generate the CSV files.

Complete the following steps to load budget amounts for import:

  1. Click the Generate CSV button after populating the spreadsheet.

  2. Save the CSV and ZIP files in your local device.

  3. Specify the directory or rename the file. Do not rename the prefix portion of the file name to avoid any import errors.

  4. Navigate to the File Import and Export page, and click Upload. The Upload File dialog box appears.

  5. On the Upload File dialog box, select the ZIP file and specify the account fin//budgetaryControl/import.

  6. Navigate to the Scheduled Processes work area and submit the Load Interface File for Import process.

  7. Select Import Budget Amounts as the Import Process, and specify the ZIP file that you uploaded.

  8. Review the log and output files of the Load Files to Interface process to ensure that all rows are successfully loaded.

Complete the following steps to submit the Import Budget Amounts process:

  1. Navigate to the Scheduled Processes work area.

  2. Search for the Import Budget Amounts process. Ensure that the runtime parameters, as listed in the following table, are accurate:

    Parameter Description

    Source Budget Type

    Must match the Source Budget Type column in the template.

    Source Budget Name

    Must match the Source Budget Name column in the template.

    Budget Import Name

    Must match the Budget Import Name column in the template.

    Import Mode

    Select any of the following import modes:

    • Overwrite: For template amounts based on the originally established or revised budget amounts.

    • Increment: For template amounts based on the budget revision amounts.

    • Fail: To ensure that any existing budget amounts are not overwritten by the equivalent rows in the template.

  3. Click Submit.

  4. If the process succeeds, you can view the imported budget amounts on the Review Budget Balances page from the Budgetary Control dashboard.

  5. If the Import Budget Amounts process ends in error or warning, review the log file for details about the rows that caused the failure.

Complete the following steps to correct import errors:

  1. Prepare the budget amounts data in the spreadsheet again.

  2. Correct the corresponding rows in the appropriate worksheet of the template.

  3. Load the budget amounts for import again.

  4. Submit the Import Budget Amounts process again.

  5. Repeat these steps until all rows are imported successfully and the budget amounts are loaded into the application.