Preparing Data for Consolidations

This chapter provides an overview of data preparation, lists prerequisites, and describes how to:

• Define ledger verification rules.

• Define calendar mapping rules.

• Define ChartField mapping rules.

• Define currency mapping rules.

• Define ledger preparation rules and groups.

• Run ledger preparation.

• Run currency adjustments.

Understanding Data Preparation

After your source ledger data resides in the PeopleSoft EPM database, you need to prepare the data before you can use it as input for any consolidation processes. Because the subsidiary ledgers may use different accounting calendars, account structures, and store amounts in different base currencies, they must be transformed to a common structure prior to consolidations. Data that has been transformed to this common structure resides in the consolidation ledger.

To prepare the subsidiary ledger data for the consolidation ledger, you define a series of mapping rules, add them to ledger preparation rules, and then run the ledger preparation process. After the ledger preparation processes are completed, the source ledger data is converted to the common structure, based on your mapping rules, and moved into the consolidation ledger. The data in the consolidation ledger is subsequently used as input for consolidation processes.

If your source subsidiary ledger data is in the same format as the consolidation ledger, when you define its ledger preparation rule, you can indicate that no preparation is required.

See Using the No Preparation Option.

Prerequisites

Before you can establish the mapping rules used for ledger preparation, you must define this supporting data:

• Calendars: detail and summary.

• Currency rates, currency codes, currency quotation methods.

• Market rate definitions, market rate indexes, market rate types, and market rates.

• Warehouse business unit definitions for subsidiary, elimination, and common consolidation business units.

• Ledger templates, source ledgers, and consolidation ledger definitions.

• Metadata and PeopleSoft Application Designer records for your subsidiary ledger records, source staging ledger records, mapping ledger records, and the consolidation ledger record.

• Tree metadata for the trees used in consolidations.

See Setting Up EPM Infrastructure, Business Rules, and Security.

See Setting Up the Operational Warehouse - Enriched for EPM Analytical Applications.

and

See Defining Your Organizational Structure.

Ledger Preparation Processing Phases

The ledger preparation processing phases are:

1. Ledger Verification.

   Verifies source data prior to running the Ledger Preparation process.

2. Calendar Mapping.

   Maps subsidiary source data to a common accounting calendar, removes unnecessary keys to improve processing times, and populates the source staging ledger.

3. Ledger Data Mapping.

   Maps ledger accounts to a common account structure and populates the mapping ledger.

4. Currency Mapping.

   Maps monetary amounts a common consolidation ledger currency and updates the mapping ledger

5. Loading the Consolidation Ledger.

   Moves data into the consolidation ledger.

These naming conventions identify the records used at each phase of ledger preparation processing; if customizing, replace XXX with the unique identifier that you have established for each record:

This diagram depicts the ledger preparation processing flow:

Ledger Preparation processes

The format of the consolidation ledger impacts ledger preparation processing:

• For trial balance format consolidation ledgers, the system processes both current period activity and year-to-date (YTD) activity (period end balances) from the source ledgers.

  The system converts any YTD amounts to current period activity.

• For financial statement format consolidation ledgers, the source ledger data must also be in financial statement format.

  No conversion occurs.

The consolidation ledger is balanced after currency conversion by using the balancing account type rule defined for the consolidation model.

See Also

Understanding Ledgers

Defining Balancing Account Type Rules

Defining Ledger Verification Rules

This section provides an overview of ledger verification rules and discusses how to establish ledger verification rules.

Understanding Ledger Verification Rules

After you load your ledgers you must verify that the content of the ledger data adheres to balancing rules that you specify, such as debits equal credits. You use the standard PeopleSoft EPM metadata tools to build these balance rules. After the balancing rules are defined, you then associate them with the ledger verification rules component that is delivered with Global Consolidations. The Ledger Verification application engine resolves these rules, and creates status log entries that you view with the Ledger Preparation Manager.

You run the ledger verification process at the beginning of the edger preparation process. The ledger verification process validates the data for specified source business units based on user-defined rules for checks and balances and error handling. If an error occurs during the verification, the Ledger Preparation engine will either continue the ledger preparation process for the valid business units only, or abort the entire run, (depending on what you specify for Verification Error Handling on the Ledger Preparation Run Group page). You can view the verification status results on the Ledger Preparation Manager page.

Note. You must run ledger verification for all source data.

See Understanding the Ledger Preparation Manager.

Defining Ledger Verification Rules

Ledger verification rules are based on balancing rule equations, such as debits equal credits. These equations are defined using metadata, including filters, constraints, and job total metadata, which is then processed through the reconciliation engine. You define a balancing rule equation and add it to a ledger verification rule by following these steps:

1. Define filters and constraints for each side of the balancing rule equation.

2. Use the constraints to define job total metadata for each side of the equation.

3. Use job total metadata to define the balancing rule metadata.

4. Add the job total metadata IDs to Global Consolidations Ledger Verification Rules.

After the ledger verification rules are defined, you then add them to the ledger preparation rules and run the ledger preparation process. .

See Defining Ledger Preparation Rules and Groups.

Delivered Sample Metadata

Global Consolidations delivers the following balancing rule sample data for standard balance rules that you can access and modify to assist you with building your own ledger verification rules:

• Total debits equal total credits.

• Current period retained earnings equals prior period retained earnings plus current period activity.

• Assets equal liabilities plus equity.

• Exclude future periods.

• Prior period source count equals prior period source staging ledger (SLED) count.

You should adjust the verification rules according to your data and use those rules that make sense for your business practices. In addition to the user-defined rules, the verification process can optionally perform basic ChartField validation against the ChartField Edit on the Ledger Template. You can also create additional rules for verifying your source ledger data.

Global Consolidations delivers the following sample metadata to support manual data entry:

• TableMaps

• DataMaps

• Filters and Constraints

• Job Total Metadata

• Balance Rule Metadata

Global Consolidations delivers the following sample TableMap metadata to support ledger verification for source manual ledgers:

Global Consolidations delivers the following sample DataMap metadata support ledger verification for source manual ledgers:

Global Consolidations delivers the following sample constraints and filters to support ledger verification for source manual ledgers:

Note. You will find the sample constraints and filters under the SHARE setID.

Global Consolidations delivers the following sample Job Total Metadata to support ledger verification for source manual ledgers:

Note. You will find the sample Job Total Metadata under the SHARE setID.

Global Consolidations delivers the following sample Balance Rules Metadata to support ledger verification for source manual ledgers:

Note. You will find the sample Balance Rules Metadata under the SHARE setID.

Example: Metadata Setup for Ledger Verification

The following example demonstrates how a standard balance rule for the source manual ledger GC_LEDMANL_F00 record is defined for debits must equal credits.

This balance rule forms an equation that contains two terms.

Total Debits = Total Credits

The system calculates each one of these terms using job total metadata.

Note. This example presumes that the record metadata, tablemap, and datamap are already setup.

The first step is to set up the filters and constraints for the balance rule equation. In this example, two filters were established, one for each side of the balance rule equation, as follows:

This illustrates the GCLEDMDEBIT filter criteria page, which calculates the left side of the equation and selects debit data:

This illustrates the GCLEDMCREDIT filter criteria page, which selects the right side of the balance rule equation, credit data:

Note. Unless you have modified the underlying fields and tables, there is no need to modify these filters.

The two previously described filters were then associated with constraints, as follows:

This illustrates the constraint that was established to use the debit filter.

This illustrates the constraint that was established to use the credit filter:

Note. There is no need to modify these constraints even if you modify the filters that are associated with them, unless you have changed the names of the filters.

With the filters and constraints in place, the next step is to establish Total IDs for both the credit and debit constraints on the Job Total Metadata page. The Total ID's are also associated with a Job ID on the Job Total Metadata page. Because the both the debit and credit calculations are used during the ledger verification process, they are established for the Global Consolidations Manual Ledger Verification Job ID, GCVERFLEDM.

This illustrates the Job Total Metadata page for the Total ID GC_DEBIT, that uses the GCLEDMDEBIT constraint:

This illustrates the Job Total Metadata page for the Total ID GC_CREDIT that uses the GCLEDMCREDIT constraint:

Note. There is no need to modify the job total metadata even if you modify the filters and constraints that are associated with them. The only reason to modify this metadata is if you have added new filters and constraints that you want to use.

The next step is to use the Job IDs and Total IDs to establish the Balance Rules Metadata, as follows.

This illustrates the balances rules metadata for debits = credits.

The final step is to associate the Job ID from the Balance Rules Metadata with a ledger verification rule. In this example, the balance rules metadata, GCVERFLEDM is associated with the ledger verification rule GC_VERF1.

The preceding example illustrates how EPM metadata is setup to verify that the debits equal the credit. . This is one of several basic accounting principle rules that are delivered as sample EPM metadata and that you can use or modify to suit your business practices.

Page Used to Define Ledger Verification Rules

Establishing Ledger Verification Rules

Access the Ledger Verification Rules page (Global Consolidations, Define Consolidations, Ledger Preparation, Ledger Verification Rule).

Job ID	Select the job total metadata Job ID you want to assign for the ledger verification rule
ChartFields Validation	Select to perform to perform basic ChartField (dimension) validation aginst the ChartField Edit on the ledger template during the ledger verification rule process.

Note. To perform ChartField validation, you must specify a TSE record for use on the Ledger Template Consolidation Variables setup page. The delivered TSE record for the manual data entry process is GC_TSE_MANL_FLD.

See Defining Spreadsheet/Manual Entry Ledger Templates.

See Defining Ledger Templates.

Period Usage

See Also

Understanding Data Preparation

Defining Calendar Mapping Rules

This section discusses how to establish calendar mapping rules.

Mapping to a Common Calendar

After the subsidiary ledgers are loaded into the PeopleSoft EPM database, you need to ensure that the fiscal year and accounting period for all of the ledgers is based on the same time frame (the same accounting calendar) before you can process consolidations. The system uses calendar mapping rules to convert all source subsidiary ledgers to a common calendar, ensuring that consolidation data is processed for the same fiscal years and periods.

You establish the calendar mapping rules by using the Calendar Map page, specifying the rules for mapping from each accounting calendar that subsidiaries use to the consolidation accounting calendar. You need to define calendar mapping rules for each unique accounting calendar used by the business units that you consolidate. However, if more than one subsidiary uses the same accounting calendar, you only need to define that particular calendar mapping once. You identify the specific calendar mapping rule that a specific ledger business unit uses when you define a ledger preparation rule.

Note. Specifically with trial-balanced-based ledgers, calendar mapping is used for period-to-date amounts, not for YTD amounts in the source ledger. For example, if your source data contains YTD amounts for a specific period, the system will not recognize them as YTD amounts, but will assume that they are period amounts. Therefore the YTD amount for the period should not be mapped in this situation. For example, the YTD balance in the source ledger for 2006 period 02 should not be mapped. Only the period balance should be mapped. . If calendar mapping is defined when the source ledger is identified to contain YTD amounts, you receive an error.

After calendar mapping rules are defined, you add them to ledger preparation rules.

Pages Used to Define Calendar Mapping Rules

Establishing Calendar Mapping Rules

Access the Calendar Map Rule page (Global Consolidations, Define Consolidations, Ledger Preparation, Calendar Map Rule).

If No Mapping Is Required

If a subsidiary ledger's accounting calendar matches the common consolidation business unit calendar, define a no mapping required rule. To define a no mapping rule, select the Match Fiscal Year and Period check box No further information needs to be defined on the Calendar Map Rule page when mapping is not required.

Note. Even if no mapping is required, a no map rule needs to be created and added to the ledger preparation rule.

Identifying the Source and Target Calendars

The selections within the Calendar Map grid are limited to values in your from and to calendars.

Completing the Calendar Map Grid

Insert rows as needed into the Calendar Map grid to define what each fiscal year and period in the source calendar maps to the fiscal year and period in the target calendar. The grid includes these fields:

Copying a Calendar Map

You can copy calendar map definitions from one or more fiscal years. This helps speed up data entry for this page, because it enables you to enter the map information once for a single year, then copy the information to additional years. Click Copy Map to activate the Copy Calendar Map page. Specify the year or years to copy from (which indicates the calendar data that you are copying) and the year or years to copy to (which indicates the year or years to which the data is copied), and then click OK.

See Also

Defining Ledger Preparation Rules and Groups

Defining ChartField Mapping Rules

This section discusses how to:

• Map to a common chart of accounts.

• Establish data mapper rule sets.

• Establish data mapper value mappings.

• Establish data mapper rule groups.

Note. Data Mapper moves only data that falls within the intersection of all the map rules.

Mapping to a Common Chart of Accounts (Data Mapper)

Before consolidation can take place, you must map all of the subsidiary ledgers requiring consolidation to a common chart of accounts. Potentially, each subsidiary ledger could have its own set of ChartFields and chart of accounts. To consolidate all these ledgers, you must map each of them to a common ChartField structure and common chart of accounts. The target consolidation structure can be completely different from one or all source structures.

Use the Data Mapper component to define how your subsidiary ledgers map to a common ChartField structure and common chart of accounts. You identify the datamaps that define the source data and the target table, and the method by which to map the data in a data mapping rule. When preparing data for Global Consolidations, the source data is a source staging ledger (SLED), and the target table is the corresponding mapping ledger (MLED).

Data mapping rules can be defined to:

• Use any source staging ledger (SLED) as the source datamap.

• Use any mapping ledger (MLED) as the target datamap.

• Map to a target column by using the value from a source column, a fixed value, a range of values, wildcards, or a defined map.

• Define maps with one or more source columns, mapped to one or more target columns.

• Map source data with a value or a tree node.

• Map to target columns with a value or a tree node.

• Preview mapping results.

The mapping can include as many fields as exist in the source and target. If you add new ChartFields to either the source or target records, you can extend the mapping rule to include these new fields. Data Mapper rules are effective-dated, enabling you to control when to use new fields.

In addition, you can use Data Mapper rules to publish consolidation journals back to their source ledgers.

See Publishing Journals.

Steps to Map to a Common Chart of Accounts Using Data Mapper

Follow these steps:

1. Define the source datamap, the target datamap, filters, constraints, and any other metadata that will be used to map the data.

2. Define a Data Mapper Rule Set.

   If the Data Mapper Rule Method is set to Map Rule:

   • On the Data Mapper Rule Set-Field Mapping Rule page, specify the source and target columns and how their values will be specified, and save the page.

   • Return to the Data Mapper Rule Set page, insert the field mapping rules that you defined, and then save the page again.

3. On the Data Mapper Value Mappings page, specify how to map the specific values within a ChartField from the source ledger to the target ChartField.

4. On the Data Mapper Rule Group page, combine the data mapper rule sets that you want to run together in the order in which they should be run.

5. On the Ledger Preparation Rule page, associate the data mapper rule group with the appropriate ledger business units.

Pages Used to Define ChartField Mapping Rules

Establishing Data Mapper Rule Sets

Access the Data Mapper Rule Set page (Global Consolidations, Define Consolidations, Ledger Preparation, Data Mapper, Rule Set).

Define the Source, Target, and Mapping Method

Select the Source Datamap, Source Constraint, and Target Datamap, to define your source and target data. For Global Consolidations ledger preparation, the source datamap should be based on your source subsidiary staging ledger records (SLEDs), and the target datamap should be based on your mapping ledger record (MLED).

For ledger preparation, the source constraint must be defined on the source datamap, and limits which data is used from the source—for example, base currency amounts only. It must be based on a source staging ledger record (SLED).

In the Rule Set Columns grid, for each target column listed, specify the map method. The rule set columns are determined by the target datamap. These are the columns to which your source data will be mapped.

The Map Method column specifies how to map the data from source to target. Each map method requires an additional parameter, as described.

Define the Field Mapping Rules

Access the Data Mapper Rule Set - Field Mapping Rule page (Global Consolidations, Define Consolidations, Ledger Preparation, Data Mapper, Rule Set, Field Mapping Rule).

This page establishes the map rule IDs that are used on the Data Mapper Rule Set page when you select the Map Rule mapping method. Insert and define as many field mapping rules as needed to use with your data mapper rule set. For each map rule, complete the fields within the Target Columns and Source Columns grids to identify which columns you want to map and whether to use a value, tree, wild card, or range to specify which values to map. (You identify those values on the Data Mapper Value Mapping page.) Both source and target grids contains these fields:

These rules control which dimensions you can specify values for when mapping. If you select tree, then on the Data Mapper Value Mappings page, you can use tree nodes to specify the values. If you select value, then you select from a list of dimension or ChartField values. If you select range, then you select from a range of dimension or ChartField values. If you select wild card, then you select from a list of dimension or ChartField values based on your wild card designator. This is how the system determines which source values map to which target values.

Viewing the SQL

Access the SQL page to view the SQL statements that result from the rule.

Establishing Data Mapper Value Mappings

Access the Data Mapper Value Mappings page (Global Consolidations, Define Consolidations, Ledger Preparation, Data Mapper, Value Mappings).

Within the Map Values group box, specify which source column values map to target column values. The above example maps source column 101250 to target account 100250 and affiliate 11210. By adding rows to the target columns, you can map source amounts to multiple affiliates.

Establishing Data Mapper Rule Groups

Access the Data Mapper Rule Group page (Global Consolidations, Define Consolidations, Ledger Preparation, Data Mapper, Rule Group).

In the Rule Sets in Rule Group group box, insert rows as needed to add all the data mapper rule sets that comprise this rule group. The rules that you include must define all the data in your subsidiary ledgers that need to be mapped to a consolidation ledger, because only one rule group is added to the consolidation model and processed during data preparation.

Enter the Use Order and specify the Rule Set ID for each inserted row. The rules are used in ascending order based on the value that appears in the Use Order field. If the same source data is mapped with more than one rule set, only the first one encountered is used. Each data row is processed only one time by the system. This means that if the first rule in a sequence handles a specific row, then the next rule excludes that row from its processing. The same rules, arranged in a different use order, may yield different results, so it is very important to set up the use order sequence exactly as you want the rules to be processed.

Defining Currency Mapping Rules

This section discusses how to:

• Establish currency rules

• Establish currency groups

Note. The system does not support multiple base currencies for a consolidation ledger. To process consolidations in multiple base currencies, create additional scenarios for each base currency.

Converting to a Common Base Currency

PeopleSoft Global Consolidations can consolidate data from source ledgers that use currencies other than the consolidation currency. Currency mapping rules translate amounts from source ledgers into a common consolidation reporting currency. You can also consolidate in more than one reporting currency by creating a scenario for each currency.

Subsidiary ledgers are converted to a base consolidation currency prior to the consolidation process as part of ledger preparation processing. This occurs after the ledgers are converted to the common consolidation calendar and the common consolidation chart of accounts. This means that you can set up currency conversion rules that are based on the common consolidation chart of accounts. The mapped ledger (MLED) stores ledger amounts in multiple currencies, so you can audit and track the translated account balances; the original account balance and currency are preserved. The source ledger's base currency amounts are converted to the consolidation currency using the defined currency mapping rules and exchange rates.

Currency conversion is computed by using this method:

• The system obtains the number of decimal positions for the consolidation base currency from the Currency Code table (CURRENCY_CD_TBL).

  This is used for rounding.

• The Source Base Currency Amount is multiplied by the RATE_MULT amount from the Market Rate table (RT_RATE_TBL).

• The result of the multiplication is divided by the RATE_DIV amount from the Market Rate table.

• The result is rounded to the number of decimals defined for the currency in the Currency Code table.

Potentially, as a result of currency conversion, the ledger may be out of balance due to rounding or due to using different exchange rates for balance sheet accounts versus equity accounts. If so, the system generates the appropriate translation adjustments automatically. To balance the ledger, the system sums together all the account balances for a specific ledger business unit combination. If the result is not zero, then an adjustment is recorded to the account specified in the associated currency mapping rule.

Each ledger business unit is balanced independently of the other ledger business units that comprise the consolidation ledger. Each ledger business unit is adjusted separately as needed. The adjustment is recorded directly into the consolidation ledger with a source code of (02) Currency Conversion Adjustment for source data (01). The adjustment is recorded with source code of (2A) Currency Adjust - Manual Entry for manual source data (1A),

This table lists required fields for currency conversion. The system uses these fields to generate an audit trail and to navigate to the source data:

Regardless of how many transaction currencies the source ledger contains, the system moves only two currencies into the consolidation ledger after the ledger preparation process is complete:

• The consolidation ledger's base currency and its corresponding amount.

• The source ledger's base currency and its corresponding amount.

This table lists the fields for currency conversion for the consolidation ledger:

The system requires that the structure of all source ledgers follow these standards:

• Debits are stored as positive numbers (+) and credits are stored as negative numbers (–).

• Specifically for trial balance format source ledgers, there should not be an equity account that stores YTD retained earnings.

  Instead, the system derives YTD retained earnings on the balance sheet from the total of the revenue and expense accounts.

• Period 0 (beginning balances) must be supplied for trial balance format source ledgers.

  When ledger preparation is run for the common consolidation business unit for accounting period 1, period 0 (beginning balances) is also prepared and loaded into the consolidation ledger. The currency conversion and balancing of period 1 is done separately from period 0. For other accounting periods, only that specific accounting period is processed.

Currency Mapping Example

In this example, the subsidiary ledger contains multiple currencies—including entries for the consolidation currency—but the base currency of the source ledger is not the same as the consolidation currency. The consolidation currency is USD. The base currency of the source ledger is EUR. The fields within the source ledger, mapped ledger, and consolidation ledger appear in these tables.

This table depicts a trial balance-based source ledger (SLED):

First, ledger mapping takes place, and these fields are mapped:

• The source Foreign Currency Amount field maps to Source Tran Amount.

• The source Foreign Currency Code field maps to Source Tran Currency.

• The source Base Currency Amount field maps to Source Base Amount.

• The source Base Currency field maps to Source Currency.

Note. The Foreign Currency Amount is from the POSTED_TRAN_AMOUNT field, and the Base Currency Amount is from the POSTED_BASE_AMT field.

Next, currency conversion converts the source base amount to the consolidation currency, and stores these details for each row in the mapped ledger (MLED):

• Consolidation currency.

• Consolidation base amount.

• Rate type, rate multiplier, and rate divisor used to convert.

The source base amount is converted even when the Source Tran Currency field value is the same as the consolidation currency.

This table depicts the mapped ledger (MLED):

During the consolidation ledger load phase, these actions occur:

• An aggregation process creates the consolidation ledger. This process summarizes on these fields: ChartFields, source base currency, and consolidation currency.

• The source ledger's account balances are identified in the consolidation ledger with the addition of a unique source code.

• The source ledger's base currency is recorded as follows for each account or ChartField:

  • Currency Code contains the source ledger's base currency code.

  • Base Currency contains the consolidation base currency code.

  • Posted Tran Amt is the sum of all Source Base Amount values.

  • Posted Base Amt is the sum of all Cons Base Amount values.

  • Posted Total Amt is the sum of all Source Base Amount values.

• The consolidation base currency is recorded in a unique row for each account or ChartField, only if the consolidation base currency is different from the source ledger's base currency.

  • Currency Code and Base Currency both contain the consolidation base currency code.

  • Posted Tran Amt is zero, and Posted Base Amt is zero.

  • Posted Total Amt is the sum of all Cons Base Amount values.

This table depicts the rows that are added to the consolidation ledger (CLED):

Pages Used to Define Currency Mapping Rules

Establishing Currency Rules

Access the Currency Rule page (Global Consolidations, Define Consolidations, Ledger Preparation, Currency Conversion Rule, Currency Rule).

Ledger Template

Select the ledger template structure. This must be the same template used for your consolidation ledger. This value controls which ChartFields are available. The ledger that you select must be defined on the Ledger Template page with the EPM Ledger Type field set to Consolidation Ledger. The ledger format option, which is established on the Ledger Template - Consolidation Variables page (either Trial Balance based or Financial Statement based) determines which fields are available for entry in the close rule process pages. The Ledger Template defines the data base records and the processing methods for your consolidation model. See Understanding Ledger Templates.

Consolidation Dimension

Select the dimension by which you are consolidating, for example, Ledger Business Unit. See Understanding Business Units.

Insert rows as needed into the Conversion Rule grid to define the exchange rate type used for a particular set of ChartField values (accounts) during currency conversion. For each row, complete these fields:

The rate type selections allow you to track flows for both cash flow reporting and footnote disclosures. For example, you might record fixed asset acquisitions at a current rate for footnote disclosure purposes and at an average rate for cash flow. Global Consolidations records the flow amount at both rates for reporting purposes.

See Defining and Managing Flows.

Click the Edit/View ChartField Value Set link to review or modify the associated ChartField value set definition.

Specifying Targets for Translation Adjustments

Establishing Currency Groups

Access the Currency Group page (Global Consolidations, Define Consolidations, Ledger Preparation, Currency Conversion Group, Currency Group).

Ledger Template	Select the ledger template structure. This must be the same template used for your consolidation ledger. This value controls which ChartFields are available. The ledger that you select must be defined on the Ledger Template page with the EPM Ledger Type field set to Consolidation Ledger. The ledger format option, which is established on the Ledger Template - Consolidation Variables page (either Trial Balance based or Financial Statement based) determines which fields are available for entry in the close rule process pages. The Ledger Template defines the data base records and the processing methods for your consolidation model. See Understanding Ledger Templates.
Generate Adjustment	Select to require the system to balance each ledger business unit during currency conversion, generating an adjustment entry when needed. Note. There can be situations where the ledger business units do not balance after conversion due to multiple exchange rates in use by different ChartField values.
Currency Rules	Specify all the rules to include in this currency group, adding additional rows as needed. Include as many rules as needed to identify every ChartField value to convert, because only one currency group is used in a consolidation model. The rules are processed in ascending order based on the value that appears in Use Order. If the same ChartField value exists in more than one of the currency rules in the rule group, the first rule processed (based on the use order) determines how that ChartField converts; any subsequent rules that use that ChartField value are not used.
Edit/View Conversion Rule	Click to review or modify the associated currency conversion rule definition.

Specifying Where to Record Adjustments

Access the Currency Group - Adjustment page (Global Consolidations, Define Consolidations, Ledger Preparation, Currency Conversion Group, Adjustment).

If you select the Generate Adjustment option on the Currency Group page, use the fields on this page to indicate where to record any adjustments. Each business unit-ledger combination is balanced independently of the other business unit ledgers that are being consolidated, and each ledger business unit receives a separate adjustment as needed.

The conversion gain and conversion loss accounts specified on the Currency Group page are really used as a way to capture all out-of-balance entries. The various situations that could case entries into the accounts specified on the Currency Group - Adjustment page include:

• True translation gain/loss.

  Per FASB52 requirements, some accounts are translated at the average rate, some at the closing rate, and some at an historic rate. Because of this, an adjustment is needed to balance the ledger after translation.

• Rounding.

  Theoretically, even if all accounts were translated at the same rate, there could be a small rounding adjustment needed to balance the final ledger.

• Out of balance.

  If the ledger is out of balance to begin with, or if the currency rules do not pick up all accounts, then the resulting ledger may need an adjusting entry to bring it into balance. This could occur even if no currency translation takes place (for example, the source base currency and the consolidation currency are the same).

• Any combination of the above.

Defining Ledger Preparation Rules and Groups

This section provides an overview of ledger preparation rules and groups and discusses how to:

• Understand ledger preparation rules and groups.

• Pages used to establish ledger preparation rules and groups.

• Establish ledger preparation rules.

• Establish ledger preparation groups.

Understanding Ledger Preparation Rules and Groups

After defining individual mapping rules, you establish ledger preparation rules to identify which mapping rules a ledger business unit uses to convert its data to the consolidation ledger. Ledger preparation rules identify the specific ledger verification, calendar, currency, and ChartField mapping rules (data mapper rules) to use for each subsidiary ledger business unit during ledger preparation processing. You can include one or more ledger business units in a single ledger preparation rule, depending on your requirements.

Note. If you want to include elimination units in your source ledger data, the definition for the elimination unit should indicate that it is a processing unit, and you should include the elimination units in your ledger preparation rules. The elimination units will then appear in the Ledger Preparation Manager

When you define a ledger preparation rule, you can also select the no preparation option for specific business units. This option by-passes calendar, currency, and ChartField mapping rules and transfers verified source ledger data directly to the consolidation ledger. You can use this option if the source ledger data has already been prepared, or when it is already in the format of the consolidation ledger. The data for the business unit must come from a single source ledger.

See Using the No Preparation Option.

One or more ledger preparation rules are associated with a ledger preparation group. The group should encompass all the rules needed to describe the mapping for each ledger business unit in your consolidation. A single ledger preparation group is associated with a consolidation model. The ledger preparation process uses the ledger preparation group specified in the consolidation model as input.

See Also

Rules, Rule Sets, and Rule Groups

Pages Used to Define Ledger Preparation Rules and Groups

Establishing Ledger Preparation Rules

Access the Ledger Preparation Rule page (Global Consolidations, Define Consolidations, Ledger Preparation, Ledger Preparation Rule, Preparation Rule).

Ledger Template

Select the ledger template structure. This must be the same template used for your consolidation ledger. This value controls which ChartFields are available. The ledger that you select must be defined on the Ledger Template page with the EPM Ledger Type field set to Consolidation Ledger. The ledger format option, which is established on the Ledger Template - Consolidation Variables page (either Trial Balance based or Financial Statement based) determines which fields are available for entry in the close rule process pages. The Ledger Template defines the data base records and the processing methods for your consolidation model. See Understanding Ledger Templates.

Insert rows as needed within the Ledger Preparation Rule grid to add business units to this rule, and define the specific ledger verification, account mapping, calendar mapping, and currency conversion rules to use for each ledger business unit during ledger preparation. For each row, complete these fields:

You can select only mapping rules that use the same ledger template as the preparation rule.

Updating Multiple Rules

Expand and use the Update Rules group box to help you complete the Ledger Preparation Rule grid. Select the business unit tree name and a tree node that contains the business units for which you want to assign mapping rules. Then click Load Business Units to insert all the business units within that node that are not currently in the Ledger Preparation Rule grid. Similarly, you can populate one or more of the fields within Update Rules Ledger ID, Calendar Map, Data Mapper, and Verification Rule ) or select No Preparation is Required, then click the Update Rules button to insert those field values into rows within Ledger Preparation Rule that do not currently contain any values for those fields.

To clear one or all rules, select the rule type from the Clear Rule drop-down list box, then click Update Rule.

To apply currency mapping to all of the business units in the Ledger Preparation Rule grid, select the Currency Mapping is Required check box and click Update Rules.

Copying Rules

To copy a rule, click the Copy Rule button. The Copy Preparation Rule page appears.

Specify the setID, effective date, new preparation rule ID, and description for the rule that you are creating by copying this rule, and then click OK.

Validating Rules

Click Validate Rule to have the system check that your ledger preparation rules are valid. This is especially important if you create a rule by copying another rule. If there is an issue with any rule, an error message appears indicating the specific problem. If no error message is displayed, then your rules are valid.

Establishing Ledger Preparation Groups

Access the Ledger Preparation Group page (Global Consolidations, Define Consolidations, Ledger Preparation, Ledger Preparation Group, Preparation Group).

The ledger template must be the same template that is used for the consolidation ledger.

In the Ledger Preparation Rules grid, insert rows as needed to associate one or more preparation rules to this preparation group.

The preparation rules are processed in ascending order based on the value that appears in the Use Order field.

Running Ledger Preparation

This section discusses:

• Understanding the ledger preparation process.

• Prerequisites

• Defining a ledger preparation run group.

• Running ledger preparation.

See Also

Streamlining Processing with Jobstreams

Understanding the Ledger Preparation Process

After you have set up all the mapping rules, and established ledger preparation rules, a ledger preparation group, and a consolidation model which uses the ledger preparation group, you can run the ledger preparation process. When ledger preparation is run, source data for the specified run group or business unit is processed using the mapping rules that you associated with the business unit on the Ledger Preparation Rule page. You can run all the ledger preparation processes at one time, or you can specify which process to run. After all the ledger preparation processes are run, data from the source ledgers is transformed to a common chart of accounts, currency, and calendar and is moved to the consolidation ledger.

You can also establish ledger preparation run groups to indicate which ledger business units should be processed at one time. You can define these groups in a way so that data that you expect to receive together can be grouped into one run group, since it is unlikely that all your ledger business unit data will be available for ledger preparation processing simultaneously. This can also help if you plan to process consolidations on a distributed basis; each run group could be managed by individuals, for example, who would be responsible for the ledger preparation process for their sets of data. On the Run Group page, you can also establish ledger verification error handling options.

You can initiate and monitor ledger preparation processing for each node on the consolidation tree by using the Ledger Preparation Manager.

See Understanding the Ledger Preparation Manager, Ledger Enrichment Manager, and Consolidation Manager Pages.

Prerequisites

Prior to running ledger preparation, complete these tasks:

• Define a ledger preparation group that includes ledger preparation rules for each business unit to be consolidated, and add it to the consolidation model.

• Define the consolidation model.

  See Establishing Consolidation Models.

• Run the Consolidation Validation application engine.

  This engine verifies that the ledger preparation mapping rules are set up correctly.

  See Validating the Consolidation Setup.

Pages Used to Run Ledger Preparation

Defining a Ledger Preparation Run Group

Access the Run Group page (Global Consolidations, Define Consolidations, Common Definitions, Run Group).

The Consolidation Models Defined grid displays the common consolidation business unit, and the business unit tree used by the consolidation model.

You can indicate which business units to include by either identifying a tree node or listing the specific business units.

To use a tree node to specify which business units to include, select the tree ID and tree node. Click the Tree button to view the tree, and select the node that contains the business units to include.

To list which business units to include, insert one or more rows within the Ledger Business Unit grid, and select the specific ledger business units.

Specifying Verification Error Handling

You can choose how to handle data processing for invalid entries while running the ledger group process. Select either Prevent all BU(s) to continue to stop all ledger group processing when ledger verification errors occur or Allow Valid BU(s) to continue to allow the data with valid business units to continue processing after ledger verification errors occurs.

Running Ledger Preparation

Access the Run Ledger Preparation page (Global Consolidations, Prepare Data for Consolidation, Run Ledger Preparation, Ledger Preparation).

Specifying the Run Parameters

Select one or more of these options to indicate which preparation steps to process. There may be cases in which you want to run a specific step by itself (for example, to troubleshoot a particular problem with your data or a rule), or you want to run all the mapping steps, but wait until a later time to move the data to the consolidation ledger. You need to determine what is best for your particular implementation.

The ledger preparation process uses the calendar mapping rules to determine which data to select from the individual ledgers. For example, assume that the run parameters specified are for 2006 period 4, and include BU1 and BU2. BU1's calendar mapping rule is defined to match the fiscal year and period (its calendar is the same as the consolidation calendar), so the system uses the data from BU1's ledger for 2006 period 04. BU2's calendar mapping does have rules. During processing, the system matches the to year and period of the BU2 calendar mapping rule to the run control date (2006/04) and, using the calendar mapping rule, determines the from year and period of BU2 to which that corresponds (perhaps 2006/01). Therefore, the system uses BU2 data from 2006/01 as input for ledger preparation processing.

Warning! If your source ledger data contains YTD balances, rather than balances by period, when processing ledger preparation, you must process each period in sequence, or the calculated period activity amounts will be incorrect.

Because many jobs may be spawned when you run ledger preparation, if you need to cancel processing, you should cancel spawned jobs first (the child jobs), then the parent job. If you are using the delivered jobstreams for processing, you can identify parent jobs by the process name PF_JOBSTREAM. The process name for ledger preparation spawned jobs is GC_PREP.

See Also

Understanding Jobstreams

Reconciliation for Ledger Preparation and Consolidation Processes

Using the No Preparation Option

No Preparation is an option for the ledger preparation process. By selecting this option, you can bypass calendar, ChartField, and currency mapping for the specified business unit and load the ledger data directly to the consolidation ledger.

You can select this option if the ledger data for the business unit is already prepared when you receive it, or is already in the format of the consolidation ledger. The data for the business unit must come from a single source ledger. The system does not aggregate the data when it populates the consolidation ledger. The field names in the source ledger and the consolidation ledger must also be identical. For example, in the consolidation ledger, the affiliate field is named GC_AFFILIATE. The affiliate field in the data warehouse source ledger table must also be named GC_AFFILIATE.

The No Preparation option is specified on the Ledger Preparation Rule page. Before you can use this option you must set up specific metadata. You must also specify the following to be used for the no preparation option:

• A ledger template and detail ledger.

• Specify the No Preparation option in a ledger preparation rule.

• Add the ledger preparation rule to the ledger preparation group that is used in the consolidation model.

See On My Oracle Support, access the red paper “Global Consolidations No Preparation Implementation Guide”

Sample Data for the No Preparation Option

The following sample data is delivered to be used with the no preparation option:

You can modify this sample data to fit your business needs.

Defining a Ledger Template and Detail Ledger for the No Preparation Option

When you define a ledger template to be used with the No Preparation option, you should select the EPM Ledger Type Performance Measurement Ledger because the ledger view is mapped from the general ledger and is conformed to the performance ledger format. You should also select the Used in Consolidations check box. The Ledger Record should be the view over the source ledger.

When you define a detail ledger to be used with the No Preparation option, you base it on the no preparation ledger template.

Specifying No Preparation in the Ledger Preparation Rule and Ledger Preparation Group

When you define the ledger preparation rule, for each business unit you can specify whether or not to use the no preparation option. When you select the No Preparation check box for a business unit, you must select a verification rule, but the Calendar Map, Data Mapper, and Currency Map fields should have no values.

The Ledger Preparation Group can include both rules that use the No Preparation option, and those that do not. Be sure to include the correct Use Order for the rules.

The ledger preparation group should be attached to the consolidation model, as shown:

Running Ledger Preparation for No Preparation

When you run the ledger preparation process, if all the source business units in the run group use the no preparation option, select only the Load Consolidation Ledger check box. You must also select the Perform Ledger Verification check box if verification has not been previously run for the source business units. You do not need to run any of the other processes.

If some business units in the run group use the No Preparation option and some do not, check all boxes. For those business units using the No Preparation option, the system skips calendar mapping, account mapping, and currency conversion and performs only ledger verification and consolidation ledger loading.

Running Currency Adjustments

This section discusses:

• Understanding currency adjustments.

• Running currency adjustments.

Understanding Currency Adjustments

You run currency adjustments to adjust beginning balances for a period by:

• Adjusting prior period source ledger activity to the current rate.

• Adjusting prior period foreign currency manual journal entries to the current rate.

Do not confuse currency adjustments with the currency processing that is completed when running the ledger preparation processes. Ledger preparation currency processes translate amounts from source ledgers for the current period activity. Currency adjustment processing generates beginning balances for the current period, using prior period data that already resides in the consolidation ledger. Because the currency adjustment engine processes prior period source ledger activity, the currency adjustment is run after the ledger preparation process and is tracked through the ledger enrichment manager.

See Managing Ledger Enrichment Processing.

Note. For consolidation ledgers that use the financial statement method, the currency adjustments target only the balance sheet accounts. Roll forward entries with a GC Source of 9A and 9B will be revalued. When the currency adjustment engine produces the revaluation flows for the roll forward entries, the flow code used will be FXAJ1 with a translate value of R for GC_FLSRC_TYPE.

Page Used to Run Currency Adjustments

Running Currency Adjustments

Access the Run Currency Adjustment page (Global Consolidations, Prepare Data for Consolidation, Run Ledger Preparation, Currency Adjustment).

Specifying the Run Parameters