Accounting Ledger With Annual Tracking Tab Template

This chapter contains the following:

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

• How Charts of Accounts, Ledgers, Legal Entities, and Business Units Are Created Using Spreadsheets

• Overview of Cross-Validation Rules in General Ledger

• Cross-Validation Rules Spreadsheet

• Overview of Cash Management Rapid Implementation

• Tax Configuration Workbook

• Example of Creating Tax Setup Using the Tax Configuration Workbook

• Guidelines for Uploading Customer Data Using a Simplified Spreadsheet

• Examples of Validations in Customer Spreadsheet Upload Data

• Budget Uploads to General Ledger

• Budget Import to Budgetary Control

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

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.

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're 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.

How Charts of Accounts, Ledgers, Legal Entities, and Business Units Are Created Using Spreadsheets

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

You can download the workbook in one of two ways:

1. In the Setup and Maintenance work area, go to the Manage Chart of Accounts Configurations task in the Financial Reporting Structures functional area and click the Download Setup Template button.

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

• Best practices and recommendations

• Instructions on how to create additional hierarchies or hierarchy versions

• A completed workbook with sample data, which you can use to familiarize yourself with how to enter data, preview the sample report, and generate required upload files

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.

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 period options that are available for selection in the spreadsheet. It isn't 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 Calendars 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 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, an Intercompany sheet is automatically added to the workbook when you select the Add Segment Sheets button. Use the sheet to enter your intercompany values and hierarchies. When you upload the chart of accounts file, the application creates a new value set for the Intercompany segment with the values that you entered. If you want to assign the Intercompany segment the same values as the Primary Balancing segment, copy all the parent and child values from the Companies and Legal Entities sheet to the Intercompany sheet.

  If you enable segment value security on the primary balancing value set, the security enforcement won't conflict with the Intercompany segment because the value sets for the two segments will be different. The same holds true if you enable segment value security on the intercompany value set. Security enforcement won't conflict with the Primary Balancing segment.

  If you plan to use related value sets and create a relationship between the segments that involves your primary balancing segment or intercompany segments, there won't be a conflict of values because the value sets are different.

  Note: If you want to assign the Intercompany segment the same value set as the Primary Balancing segment, you can change the value set assignment from the Manage Chart of Accounts Configurations page after you upload the chart of accounts configuration and before you upload the financial structures for the chart of accounts. If you decide to go with the same value set for both segments, you won't be able to change the value set association later on, once the ledger is assigned to the chart of accounts.

  Note: For the posting process to apply intercompany balancing, you must select the Enable intercompany accounting option on the Specify Ledger Options page.

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

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.

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.

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

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

   

   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.

   

   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.

• Overview of Trees

• How Financial Reporting Reports and Account Groups Are Generated

• Manage Setup Using Implementation Projects

Overview of Cross-Validation Rules in General Ledger

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

• Considerations for Cross-Validation Rules

• Create Cross-Validation Rules in a Spreadsheet

• How Cross-Validation Rule Violations Are Managed

• Update Existing Setup Data

Cross-Validation Rules Spreadsheet

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.

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.

• Cross-Validation Rules

• Considerations for Cross-Validation Rules

• Create Cross-Validation Rules in a Spreadsheet

• Update Existing Setup Data

Overview of Cash Management Rapid Implementation

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.

• How You Process Electronic Bank Statements

Tax Configuration Workbook

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.

• Example of Creating Tax Setup Using Tax Partner Content in the Tax Configuration Workbook

Example of Creating Tax Setup Using the Tax Configuration Workbook

This example shows how you can create standard sales tax rates within the US using the Tax Configuration Workbook. You can create sales tax rates at state, county, and city levels using this method.

Here's a summary of key decisions you make in 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

Follow these steps to create tax rates in the Tax Configuration Workbook:

1. Navigate to the Manage Tax Regimes page.

2. Click the Rapid Setup Spreadsheets button and select Download Tax Configuration Workbook.

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

4. Review the details on the Instructions sheet of the workbook.

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. Use the instructions and the column help text to populate the required setups in the Manage Rates worksheet.

7. After completing the Tax Rates worksheet, go to Instructions sheet again.

8. Click Generate CSV File. It performs theses actions:

   • Saves the entire Tax Configuration Workbook data in a comma separated values (CSV) file.

   • Saves the CSV file into a single compressed file attachment.

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

10. Click the Rapid Setup Spreadsheets button and select Upload Tax Configuration Workbook.

11. Select the compressed file that you saved earlier.

12. Click Open and then click Upload.

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

14. Click Refresh 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 status of the upload process isn't Succeeded, your upload has failed. Check the details in the corresponding error log, correct any file errors, and reupload the file.

• Example of Creating Tax Setup Using Tax Partner Content in the Tax Configuration Workbook

Guidelines for Uploading Customer Data Using a Simplified Spreadsheet

Use the Upload Customers from Spreadsheet process to upload customer data using the simplified Customer Import FBDI (File-Based Data Import) template. 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.

Download the simplified Customer Import FBDI template and 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: Customers, Contacts, Reference Accounts, Customer Bank Accounts.

Note: You can also use the Customer Import process to download a Customer Import FBDI template, available from the FBDI Customer Data Model, to prepare and upload customer data into Receivables and the Trading Community Model registry.

Set 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 Oracle Tax.

• Descriptive flexfields.

Enter Data in the Spreadsheet Columns

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.

• To remove existing values from specific fields in an existing customer profile, enter the exclamation point character (!) in the corresponding column.

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

Examples of Validations in Customer Spreadsheet Upload Data

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 aren't 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

Overview of Budget Uploads

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 (FBDI) 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 FBDI, see the Oracle Financials Cloud File-Based Data Import (FBDI) for Financials 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.

Here are some points to consider when preparing your budget data.

• You can maintain budget amounts only for detail accounts. However, if you're also using budgetary control, you can configure your setup to maintain budget amounts for summary accounts.

  Note: For more information about budgetary control setup, refer to the Budgetary Control chapter, Enterprise Options section in the Using Financials for the Public Sector guide.

• If you already uploaded your budget and you perform another upload using the same criteria, the upload process overwrites the existing amounts with the amounts from the new upload.

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.

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.

• Overview

Import Budget Data from a Spreadsheet

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.

How General Ledger Budget Balance Import Data Is Processed

Use the Import General Ledger Budget Balances file-based data import (FBDI) 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 Oracle Financials Cloud File-Based Data Import (FBDI) for Financials guide.

2. In the table of contents, click General Ledger.

3. Click Import General Ledger Budget Balances.

4. In the File Links section, click 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.

• Don't change the order of the columns in the template.

• You can hide or skip the columns you don't use, but don't 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.

• Oracle Financials Cloud File-Based Data Import (FBDI) for Financials

Import Budget Data from a Flat File

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 (FBDI) 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're 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're 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

• Oracle Financials Cloud File-Based Data Import (FBDI) for Financials

• Overview of External Data Integration Services for Importing Data

Budget Import to Budgetary Control

Load Budgets

You can load your enterprise-wide budget, including revenues and expenses, to General Ledger for analysis and reporting. If you implement Budgetary Control, you need to also load your expense budget to Budgetary Control to validate your spending against the budget.

How budget can be loaded into Budgetary Control depends on the source budget type of the control budget. Budget loaded to certain control budgets can be synchronized with budget in General Ledger, reducing the need to separately loading it to General Ledger.

Budget Balance Sources

This table explains how budget can be loaded into Budgetary Control and whether they're synchronized with General Ledger based on the source budget type.

Source Budget Type	Methods of Loading Budget Balances	Synchronize Budget Balances from Budgetary to General Ledger
EPM Financials Module See Set Up Financials Cloud for Loading Budget from EPM.	Enter Budget in EPM using Planning and load using Data Exchange Revise budget in EPM using Budget Revisions and load it using the Funds Reservation action	Yes
Hyperion Planning Can be reclassified to EPM Financials module See Set Up Oracle Fusion Financials for Loading Budget from EPM	Enter budget in EPM using Planning and load it to using Data Exchange Load budget with File Based Data Interface (FBDI) Enter budget using Enter Budgets in Spreadsheet task Enter budget transfer using Budget Transfer form from the Review Budgetary Control Balances page	No (if loaded from EPM or use Budget Transfer) Yes (optional, if use FBDI or Enter Budgets in Spreadsheet)
Project Portfolio Management	Enter budget in Project Portfolio Management and load it during budget baseline	Not Applicable
Other Can be reclassified to EPM Financials Module	Enter budget in a third-party software and load it using FBDI Enter budget using Enter Budgets in Spreadsheet task Enter budget transfer using Budget Transfer form from the Review Budgetary Control Balances page	No (if use Budget Transfer) Yes (optional, if use FBDI or Enter Budgets in Spreadsheet)
Control Budget Control budget of this type is known as summary control budget and is linked to a control budget of one of the above source budget types	Automatically updated in the summary control budgets when the budget balances are imported to the source detail control budget.	Not Applicable
No Budget Used to track transaction spending, not spending against budget amounts	Budget can't be loaded	Not Applicable

Budget Balance Classifications

Budgetary Control can maintain and report budget balances separating initial budget from budget adjustments. This table explains how these two budget balance classifications are determined based on the methods of loading budget balances and whether the Budget Entry Classification for Initial or Adjustment Budget Balance using Spreadsheet and Budget Transfer feature is enabled.

Methods of Loading Budget Balances	Feature not Enabled	Feature Enabled
Load budget with File Based Data Interface (FBDI)	Initial budget (if budget period is available for budgeting) Budget adjustment (if budget period is open)	Initial budget (if you specify budget entry classification as initial budget) Budget adjustment (if you specify budget entry classification as budget revision)
Enter budget using Enter Budgets in Spreadsheet task	Initial budget (if budget period is available for budgeting) Budget adjustment (if budget period is open)	Initial budget (if you specify budget entry classification as initial budget) Budget adjustment (if you specify budget entry classification as budget revision)
Enter budget transfer using Budget Transfer form from the Review Budgetary Control Balances page	Initial budget (if budget period is available for budgeting) Budget adjustment (if budget period is open)	Budget adjustment (always)
Enter Budget in EPM using Planning and load using Data Exchange	Initial budget (if budget period is available for budgeting) Budget adjustment (if budget period is open)	Initial budget (if budget period is available for budgeting) Budget adjustment (if budget period is open)
Revise budget in EPM using Budget Revisions and load it using the Funds Reservation action	Budget adjustment (always)	Budget adjustment (always)
Enter budget in Project Portfolio Management and load it during budget baseline	Initial budget (if budget period is available for budgeting) Budget adjustment (if budget period is open)	Initial budget (if budget period is available for budgeting) Budget adjustment (if budget period is open)

• Project and Grants Management

• Clear Budgetary Control Funds Check or Failures Requests

• Overview

Accounting Ledger With Annual Tracking Tab Template

Source: https://docs.oracle.com/pls/topic/lookup?ctx=cloud132&id=FACSF1004386

Share this post