Skip Headers

Oracle Financial Services Reference Guide
Release 12.1
Part Number E13528-03
Go to Table of Contents
Contents
Go to previous page
Previous
Go to next page
Next

The Ledger Migration Process

This chapter discusses the process for generating credits or charges, for funds provided or used, and their migration to the Management Ledger table. The chapter provides a detailed description of how the information required for generating these credits or charges originates through transfer rates and option cost processing from the Customer Account tables and how the results are inserted into the Management Ledger table.

This chapter covers the following topics:

Overview of the Ledger Migration Process

Ledger migration is the process of generating aggregated charges (expenses) and credits (revenues) for funds provided or used for a combination of dimensions. The information necessary to generate these charges and credits (through transfer rates and option cost processing) originates from the Customer Account tables and results are inserted into the Management Ledger table (FEM_BALANCES). Transfer pricing charge and credit information provides the basis for measuring net interest income contribution for a group of products, organization units, or a combination of other dimensions, and is available for use in further calculations of profitability and risk.

Note: The Management Ledger table is also known as the FEM_BALANCES and Customer Account tables are also known as Instrument tables.

Oracle Transfer Pricing provides great flexibility in the ledger migration process and in the generation of corresponding charges, credits, and option costs. Users can specify ledger migration for a combination of an extended list of dimensions, including up to 10 user-defined dimensions. This feature provides flexibility to users who are also using Oracle Profitability Manager for profitability reporting across organization, product, channel, geography, or other user-defined dimensions.

In addition, Oracle Transfer Pricing provides multi-currency support that allows you to generate charges or credits for funds based on entered and functional currency.

You can choose to migrate either the transfer rate or the option costs, or both, on the Transfer Pricing Process rule run page. See: Transfer Pricing Process Rules, Oracle Transfer Pricing User Guide.

Understanding Ledger Migration

To understand the process of creation of transfer rate, option cost, and charge/credit rows in the Management Ledger table (financial elements 170/172, 171/173, and 450/451/452/453, respectively), you need to make the following assumptions:

It is common for account table transfer pricing, option cost calculation, and transfer pricing (transfer rate and option cost) ledger migration to be executed consecutively in a single process.

This exposition describes the mechanics which occur just after the Account tables transfer pricing or option cost calculations have been completed successfully and just before transfer rate or option cost ledger migration starts. For example, the mechanics that occur just after Account tables have been populated with valid transfer rates and just before the weighted average transfer rate (WATR) and the Charge/Credit rows in the Management Ledger table have been updated.

The ledger migration of option costs works on the same lines as transfer rate migration. However, there are certain differences. See: Ledger Migration of Option Costs.

Transfer Rate and Option Cost Calculation

The Oracle Transfer Pricing engine calculates and writes balance-weighted average rates to the Management Ledger table using current balance, which is net of participations, to perform the weighting process. The financial elements that the engine uses to write the weighted rates to the FEM_BALANCES are as follows:

Charge/Credit Generation

In addition to the calculation of the weighted average rate values at the combination of the Organization Unit and the Line Item dimensions, charge/credit generation involves the following steps:

Oracle Transfer Pricing then writes the result as dollar charges/credits to the Management Ledger table using the following financial elements:

Direct Transfer Pricing of Ledger Balances

Oracle Transfer Pricing allows users to calculate transfer rates for ledger average balances that do not have corresponding Account table records using the following transfer pricing methodologies:

Oracle Transfer Pricing also generates records in the Management Ledger table which are posted to the organization unit designated as the Transfer Pricing Offset Unit (typically a special Treasury Unit). During this process, an offset charge or credit amount is calculated for each normal charge/credit posted at the intersection of Company/Cost Center, Line Item, and Currency dimensions in the processes outlined above.

The sum of the Organization Unit charges and credits at the Line Item/Currency level is multiplied by -1 and posted to the offset Organization Unit designated in the attribute setup for the Organization Unit dimension. After this processing is complete, the total entity level charges and credits net to zero.

Ledger Migration and the Management Ledger Table

A thorough understanding of the ledger migration process requires familiarity with the Management Ledger table standards such as data signage, editing standards, and WATR and charge/credit rows.

Data Signage

The Management Ledger table supports variable data signs. You can load data into the Management Ledger table in the following three variations:

Note: These options only reflect the way the data is held; they do not imply any difference in Transfer Pricing calculations. Ledger results for Transfer Pricing reflect the way the ledger's average balance records are held.

Management Ledger Table Editing Standards

You should be extremely careful while editing the Management Ledger table directly. If you ever get unexpected results in the Management Ledger table after ledger migration, then review the data you have entered.

WATR and Charge/Credit Rows

The weighted average transfer rate (WATR) and the resulting charge/credit for funds are represented in the Management Ledger table by financial elements 170 and 450 respectively (or 172 and 452 if transfer pricing is done on a remaining term basis).

Note: Only the Management Table, FEM_BALANCES, processing key dimensions are available for inclusion during the migration process. This is because Oracle Transfer Pricing displays only the processing key dimensions on the user interface.

Ledger Migration and the Virtual Memory Table

To calculate transfer rates at the Line Item dimension member level in the Management Ledger table, all rows in the Account tables must be accumulated to arrive at the weighted average transfer rate (WATR) for each member.

All data used in the ledger migration process passes through a table, called the Virtual Memory table (VMT), built in the memory. This table exists only during the ledger migration process and the information is never written to disk, and thus it cannot be examined for problem-solving purposes. Understanding the operation of the VMT, however, is crucial to understanding the ledger migration process.

The VMT comprises the following three types of columns:

Requirements for Successful Ledger Migration

Successful ledger migration of transfer pricing results requires correct configuration of the following parameters:

Together these parameters determine the way transfer rate and option cost calculations are carried out for every account in the organization.

Transfer Pricing Parameters

You need to configure the following transfer-pricing parameters:

Dimensions

To be eligible for inclusion in the ledger migration process, a dimension must exist and be actively populated with dimension values in both the Account tables and in the Management Ledger table.

Given below is a list of dimensions available for inclusion in the ledger migration process:

Note: Only the Management Table, FEM_BALANCES, processing key dimensions are available for inclusion during the migration process. This is because Oracle Transfer Pricing displays only the processing key dimensions on the user interface.

Entered and Functional Currency

Oracle Transfer Pricing provides you with the option of performing ledger migration and writing charges and credits in the entered and functional (local) currency, designated in the CURRENCY_CODE column, or in the functional currency only.

Whether you can perform ledger migration and write charges and credits in the entered or functional currency or in the functional currency only depends on two factors:

Source of Currency and Exchange Rate Information

Oracle Transfer Pricing shares currency and exchange rate information with the Oracle General Ledger. Currency rate information can be viewed or updated from the Oracle Transfer Pricing/General Ledger Currency Rates screen. Information on active and valid currencies (enabled currencies), functional currency, currency relationships, and exchange rates are defined at the Set of Books (LEDGER_ID) level.

Ledger migration should only be performed for currencies that have been activated or enabled.

If currency code values that have not been activated are discovered in the ledger migration process, users are presented with a warning stating the same, and the ledger migration process skips records with those values.

Note: The application uses the exchange rates only when your select functional currency only as the currency output on the Transfer Pricing Process rule to write charges and credits to a Functional & Entered Ledger.

Calculation of Functional Currency Values

Oracle Transfer Pricing calculates and writes charge/credit values expressed in functional currency to the Management Ledger table, a typical situation in multi-currency implementations, using the following procedure:

  1. Selects either Entered and Functional Currency or Functional Currency Only as the migration currency output, depending on the migration currency output you specified while defining the Transfer Pricing Process rule.

  2. Derives charge/credit amounts in the entered currency first and then applies the accrual factor to the base value (transfer rate or option cost result) already available in the functional currency in the ledger.

  3. Assume the last date associated with the calendar period as the basis for ledger migration, and generally use currency exchange rates corresponding to that date to perform conversions to functional currency for charges and credits written to the Management Ledger table.

  4. Use the following algorithm for exchange rate access:

Transfer Pricing Rule

The Transfer Pricing Rule is used to define the transfer pricing and option cost methodology for each product dimension. While defining transfer pricing methodologies, ensure that all required supporting data for the method actually exists.

For example, if the selected method is Spread from Interest Rate Code, ensure that the corresponding yield curve has been properly defined and has been populated with rates.

Conditions and Table Selection

Calculating and migrating transfer rates and option costs for an entire product portfolio can be a time-consuming process. Conditions and Table Selection allow you to reduce the ledger migration time as follows:

Ledger Migration and Line Item Dimension Set Up

The Line Item dimension contains an attribute, monthly accrual basis, that is used to designate the accrual factor for a particular product used in calculating the charge or credit for funds. This attribute should be defined for all products when the user wishes to base charge and credit calculations on product-specific accrual factors rather than a single process-specific accrual factor defined at the Transfer Pricing Process Rule level.

Offset Organization Unit

During ledger migration, Oracle Transfer Pricing generates records in the Management Ledger table that are posted to the Company/Cost Center/Organization Unit designated as the Transfer Pricing Offset Unit. During this process, an offset charge or credit amount is calculated for each normal charge/credit posted at the intersection of Company/Cost Center/Organization Unit and Line Item.

The Company/Cost Center/Organization dimension contains an attribute, Offset Organization, that is used to define the funding center that receives the offset entries from all charge and credit postings.

Transfer Pricing Process Rule

The Transfer Pricing Process Rule acts as a container for all the ledger migration parameters and submits them to the Transfer Pricing engine as a processing job. A Transfer Pricing Process rule typically contains the following ledger migration specifications:

Calculation Mode

The choice of calculation mode, on the Transfer Pricing Process Rule run page, not only affects the transfer rate and option cost calculation processes, but also the migration process. It determines the results that will be migrated to the Management Ledger table.

If the calculation mode is set to Standard then the following results are used in migration:

Consequently, the transfer pricing engine generates results for the following financial elements:

If the calculation mode is set to Remaining Term, then migration process uses the following result columns:

Consequently, the transfer pricing engine generates results for following financial elements:

Example of Transfer Rate Ledger Migration

Ledger migration requires you to select, among others, the following options while creating and executing the Transfer Pricing Process Rule:

Oracle Transfer Pricing allows you to include multiple dimensions in the ledger migration process. However, to keep the exposition simple, this example assumes that only two dimensions, the Company Cost Center/Org Unit dimension and the Line Item dimension, are selected to generate results.

The following table displays the Customer Account table data for this example.

Customer Account Tables (FEM_MORTGAGES)
COMPANY_COST_CENTER_ORG_ID LINE_ITEM_ID CUR_BOOK_BAL TRANSFER_RATE
1 3 100 4.00
1 4 125 4.50
1 5 200 3.00
1 3 200 3.00

The following table displays the pre-migration data in the Management Ledger table used in the example.

Management Ledger Table (FEM_BALANCES)
COMPANY_COST_CENTER_ORG_ID LINE_ITEM_ID FINANCIAL_ELEM_ID XTD_BALANCE_E
1 3 140 250.00
1 4 140 200.00
1 5 140 100.00
1 10 140 200.00
1 100 140 990.00

As you compare the Customer Account tables and the Management Ledger table data, notice the following:

The ledger migration process essentially comprises the following two broad phases:

However, this example with a view to illustrating the operation of the ledger migration process in general and that of the virtual memory table (VMT) in particular demonstrates the following possible variations of the ledger migration process and special cases:

Account Tables Accumulation

The first operation in the ledger migration process is to accumulate all individual detail rows from the Customer Account tables into a single row for each unique combination of mandatory dimensions, such as Line Item and Currency and for other available Management Table, FEM_BALANCES, processing key dimensions, such as Company/Cost Center/Organization Unit, in the Virtual Memory Table (VMT).

In this example, Bal_x_TfrRate for Line Item 3 is calculated as follows:

(100 * 4.00) + (200 * 3.00) = 1,000.00 = Bal_x_TfrRate

The following table represents the VMT after Account table accumulation has taken place.

VMT Post Account Table Accumulation
COMPANY_ COST CENTER_ ORG_ID LINE_ITEM_ID Bal Bal_x_TfrRate LSBal_x_TfrRate
1 3 300.00 1,000.00  
1 4 125.00 562.50  
1 5 200.00 600.00  

Management Ledger Table Processing

The first step in the ledger migration process with respect to the Management Ledger table is to clear all the information stored in the table with financial elements 170 and 450 (172 and 452 if remaining term pricing is being used) for the particular combination of dimensions being used in the process.

The next step is Management Ledger table accumulation: the Virtual Memory Table (VMT) is populated with the balance information stored in the Management Ledger Table.

The following table represents the VMT after Management Ledger Table accumulation has taken place.

VMT Post Management Ledger Table Accumulation
COMPANY_ COST CENTER_ ORG_ID LINE_ITEM__ID Bal Bal_x_TfrRate LSBal LSBal_x_TfrRate
1 3 300.00 1,000.00 250  
1 4 125.00 562.50 200  
1 5 200.00 600.00 100  

Management Ledger table processing involves the calculation of the weighted average transfer rate (WATR). The WATR is calculated by prorating the WATR by the ratio between the Account tables and the Management Ledger table balances as follows:

(Bal x TfrRate / Bal) * LSBal = LSBal x TfrRate

For example, the WATR for Line Item 3 is calculated as follows:

(1,000.00 / 300.00) * 250.00 = 833.33

The following table represents the VMT after WATR calculation has taken place.

VMT Post WATR Calculation
COMPANY_ COST CENTER_ ORG_ID LINE_ITEM__ID Bal Bal_x_TfrRate LSBal LSBal_x_TfrRate
1 3 300.00 1,000.00 250.00 833.33
1 4 125.00 562.50 200.00 900.00
1 5 200.00 600.00 100.00 300.00

Transfer Pricing Accounts with Ledger-Only Data Source

At this stage, all rows in the Management Ledger table that relate (directly or indirectly) to rows in the Customer Account tables are accumulated into the VMT. However, the accumulation process still needs to deal with account types that are transfer priced using Ledger as the data source (as specified in the Transfer Pricing Rule).

In this example, Line Item 10 is a Direct Transfer Price product with a Management Ledger balance of 200.00.

The following table represents a VMT with a direct transfer price product.

VMT with a Direct Transfer Price Product
COMPANY_ COST CENTER_ ORG_ID LINE_ITEM_ID Bal Bal_x_TfrRate LSBal LSBal_x_TfrRate
1 3 300.00 1,000.00 250.00 833.33
1 4 125.00 562.50 200.00 900.00
1 5 200.00 600.00 100.00 300.00
1 10     200.00 1,000.00

Transfer Pricing Unpriced Accounts

Accounts using the Unpriced Account method are a special case of direct transfer pricing in the Management Ledger table. The Unpriced Account transfer pricing methodology uses the WATR from other accounts to derive a WATR for the unpriced account. This is accomplished by averaging the WATR for the component accounts, weighted by their relative LS Balances.

In this example, Line Item 100 is an unpriced account that is transfer priced based on Line Item 4, 5, and 10. First, as shown in the following table, a new row is added to the VMT and populated with the balance stored in the Management Ledger table.

VMT with an New Row Displaying Management Ledger Table Balance
COMPANY_ COST CENTER_ ORG_ID LINE_ITEM_ID Bal Bal_x_TfrRate LSBal LSBal_x_TfrRate
1 3 300.00 1,000.00 250.00 833.33
1 4 125.00 562.50 200.00 900.00
1 5 200.00 600.00 100.00 300.00
1 10     200.00 1,000.00
1 100     990.00  

Then, the WATR for Line Item 100 is calculated by computing the weighted average of the WATRs of Line Items 4, 5, and 10. The WATR for Line Item 100 is calculated as follows:

(900 + 300 + 1,000)/(200 + 100 + 200)

= 4.4

The VMT is then updated with the standard form of WATR

(990.00 * 4.4) = 4,356.00 = LSBal_x_TfrRate

The following table represents the VMT after the unpriced account has been transfer priced.

VMT Displaying the WATR of Unpriced Account
COMPANY_ COST CENTER_ ORG_ID LINE_ITEM_ID CDBal CDBal_x_TfrRate LSBal LSBal_x_TfrRate
1 3 300.00 1,000.00 250.00 833.33
1 4 125.00 562.50 200.00 900.00
1 5 200.00 600.00 100.00 300.00
1 10 - - 200.00 1,000.00
1 100 - - 990.00 4,356.00

Calculation of Overall WATR (Financial Element 170)

Once all the Customer Account tables and the Management Ledger table information has been accumulated in the VMT, the overall WATR can be calculated for each Org Unit/Line Item dimension combination and posted to the Management Ledger table. The WATR is simply the sum of all component WATRs (represented in the VMT as LSBal x TfrRate).

For example, WATR is calculated as follows:

833.33 + 900.00 + 300.00 + 1,000.00 + 4,356.00 = 7,089.33= WATR

Generation of Charge/Credit for Funds (Financial Element 450)

Once the overall WATR is known, the charge/credit for funds in any period is given by the formula:

WATR * Balance * Accrual Factor = Charge/Credit for Funds

As Oracle Transfer Pricing stores WATR as WATR * Balance, this reduces to:

WATR * Accrual Factor = Charge/Credit for Funds

For example, Charge/Credit for Funds is calculated as follows:

7,089.33 * (30/360) = 590.77 = Charge/Credit for Funds

Ledger Migration of Transfer Rates Under Remaining Term Calculation Mode

The ledger migration process is identical under the Remaining Term calculation mode except that Financial Elements 452 and 172 are substituted for 450 and 170 respectively. Note that under the Remaining Term calculation mode, the transfer rate source in the Account tables is Tran_Rate_Rem_Term.

Ledger Migration of Option Costs

Ledger migration of option costs is similar to that of the transfer rate. However, there are no steps for calculating option costs directly on the Management Ledger Table, because the calculation of option cost is a cash-flow based method that requires the Customer Account table data.

Normally, option cost is represented in the Account table record as the difference between two columns, HISTORIC_ STATIC_SPREAD and HISTORIC_OAS (Option Adjusted Spread) and is expressed as a rate, in percent.

Option Cost = HISTORIC_STATIC_SPREAD - HISTORIC_OAS

If option cost ledger migration is specified in the Transfer Pricing Process Rule, option cost is accumulated in the Virtual Memory table (VMT) and written to the Management Ledger table as Financial Element 171, Average Historical Option Cost. The corresponding charge/credit for Funds is written as Financial Element 451, Historical Option Cost Charge/Credit.

Option Cost Ledger Migration under the Remaining Term Calculation Mode

The option cost ledger migration process is nearly identical under the Remaining Term calculation mode, except that Financial Elements 453 and 173 are substituted for 451 and 171, respectively.

Note that under the Remaining Term calculation mode, the option cost source in the Account tables is the difference between the Cur_Static_Spread and Cur_OAS columns.