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:
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.
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:
All rows in the relevant Account tables have already been transfer-priced or assigned an option cost.
All rows contain a valid rate in one of the following columns:
TRANSFER_RATE
TRAN_RATE_REM_TERM
CUR_STATIC_SPREAD
CUR_OAS
HISTORIC_STATIC_SPREAD
HISTORIC_OAS
Average Balance (financial element 140) information has been loaded into the Management Ledger table with dimensionality that matches the account table data being migrated.
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.
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:
170 Average Transfer Rate
172 Average Rem Term Transfer Rate
171 Historic Option Cost
173 Current Option Cost
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:
Aggregation of the corresponding average balance records from the Management Ledger table for each Org Unit/Line Item dimension combination
Multiplication of the average balance from the Management Ledger by the weighted average rates
Application of an accrual factor to de-annualize the amount
Oracle Transfer Pricing then writes the result as dollar charges/credits to the Management Ledger table using the following financial elements:
450 Charge/Credit
451 Historic Option Cost Charge/Credit
452 Charge/Credit Rem Term
453 Current Option Cost Charge/Credit
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:
Moving Averages
Spread from Interest Rate Code
Redemption Curve
Unpriced Account
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.
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.
The Management Ledger table supports variable data signs. You can load data into the Management Ledger table in the following three variations:
Absolute: All account types are positive, and all contra accounts are negative.
GAAP or Standard: The signs reflect standard accounting principles, with revenue, liability, and owners' equity as negative, and expenses and assets as positive.
Reverse of GAAP (Standard) or User Defined: Revenue, liability, and owners equity are positive, and expenses and assets are negative.
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.
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.
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).
Financial Elements 170/172 (WATR): If you select the Remaining Term calculation mode while defining the Transfer Pricing Process Rule, then the financial element generated is 172. Otherwise, it is 170. Only one 170/172 row should exist for a given 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.
Financial Elements 450/452 (Charges/Credits for Funds): If you select the Remaining Term calculation mode while defining the Transfer Pricing Process Rule, then the system generates financial element 452. If not, it would be Financial Element 450. Only one financial element, 450 or 452, should exist for a given 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.
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.
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:
Columns for mandatory dimensions, such as Line Item and Currency, and columns for other available Management Table, FEM_BALANCES, processing key dimensions, such as Company/Cost Center/Organization Unit, which uniquely identify each row
Balance and WATR columns to hold data accumulated from the Customer Account tables
Balance and WATR columns to hold data accumulated from Management Ledger table and Customer Account table calculations
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.
You need to configure the following transfer-pricing parameters:
Calendar Period : Must match the period for which you are trying to migrate transfer rates and option costs. The default calendar period is defined in user preferences.
Charge/Credit Accrual Factor: Select the Charge/Credit Accrual Factor on the Transfer Pricing Process Rule page or, alternatively, define the Accrual Factor as an attribute for each Line Item dimension member. In case no selection is made, an Accrual Factor of 30/360 is applied.
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.
Mandatory Dimensions:
LINE_ITEM_ID,
CURRENCY_CODE,
LEDGER_ID
CAL_PERIOD_ID
DATASET_CODE
Other Available Dimensions:
CHANNEL_ID
COMPANY_COST_CENTER_ORG_ID
NATURAL_ACCOUNT_ID
PRODUCT_ID
Up to 10 user-defined dimensions
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:
Your selection of the migration Currency Output on the Transfer Pricing Process Rule: Entered and Functional Currency or Functional Currency Only.
Entered Currency attribute of the Ledger dimension. If the attribute indicates No entered currency, output is only allowed for functional currency.
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.
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:
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.
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.
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.
Use the following algorithm for exchange rate access:
If exchange rate exists, use the rate for the last day of calendar period being processed.
If no exchange rate exists for last day of calendar period being processed, use the latest exchange rate available in the rates table for the period being processed.
If no exchange rate exists for the period being processed, use an exchange rate value of 1.
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.
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:
Conditions: Formerly known as Data Filter IDs, Conditions allow you to transfer price or migrate to ledger a subset of your portfolio.
Table Selection: This feature gives you the option of selecting the Account tables for ledger migration during a particular Transfer Pricing Process Rule run.
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.
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.
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:
The dimensions that you want to include in the ledger migration process.
The tables that are to undergo transfer pricing or option cost calculations.
Conditions that are to be applied to the rows in each table.
Transfer pricing or prepayment methodologies to be used.
Option cost calculation parameters.
Charge/credit accrual basis to be used.
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:
Transfer_Rate
Historic Option Cost (Historic_Static_Spread - Historic_OAS)
Consequently, the transfer pricing engine generates results for the following financial elements:
170 Average Transfer Rate
171 Historic Option Cost
450 Charge/Credit
451 Historic Option Cost Charge/Credit
If the calculation mode is set to Remaining Term, then migration process uses the following result columns:
Tran_Rate_Rem_Term
Current Option Cost (Cur_Static_Spread - CUR_OAS)
Consequently, the transfer pricing engine generates results for following financial elements:
172 Average Rem Term Transfer Rate
173 Current Option Cost
452 Charge/Credit Rem Term
453 Current Option Cost Charge/Credit
Ledger migration requires you to select, among others, the following options while creating and executing the Transfer Pricing Process Rule:
Select both the Account tables and the Management Ledger table as the tables to be processed.
Select both the transfer rate calculation and the ledger migration processing options. Selecting the transfer rate calculation option leads to the generation of transfer rates for all the records in the Customer Account tables and for those records in the Management Ledger table for which you define a transfer rate with a ledger source type. And, selecting the ledger migration processing option instructs the application to gather balances and transfer rates information, generate credits and charges for funds, and output the results to the Management Ledger table.
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.
| 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.
| 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:
Line Item IDs 3, 4, and 5 match in both tables. These Product IDs represent the simplest case of ledger migration.
Line Item ID 10 does not exist in the Customer Account tables. This example assumes that it is a ledger-only account that is transfer priced directly using an acceptable Management Ledger Table data source-only method (part of the assumption definition in the Transfer Pricing Rule).
Line Item ID 100 does not exist in the Account tables. This example assumes that it is a ledger-only account that will be transfer priced using Unpriced Account Methodology, based on Product IDs 4, 5, and 10. (This transfer pricing method is defined in the Transfer Pricing Rule.)
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:
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.
| 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 |
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.
| 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.
| 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 |
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.
| 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 |
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.
| 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.
| 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 |
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
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
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 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.
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.