Understanding Currency Conversion Rules

Schema Rules

Schema rules specify on what table the currency conversion is performed, the source amount and currency code column, and target amount and currency code column that is populated by the conversion result. Schema rules also include the table name where the source and target columns are found. Essentially, the schema rules abstract the interrelationship between source and target columns for currency conversion. The schema rules also specify the granularity of data for the conversion process. This is done by specifying the relevant date/period dimension and business unit column for both resolving the exchange date and chunking the data set for currency conversion.

You set up the schema rules based on the schema definition,which is system data and is delivered as part of the PeopleSoft EPM product. The predefined schema definition associates source amounts and currency code fields to their target amount and currency code fields. Schema rules contain the list of all target columns for currency conversion, along with their associated information, such as the record name, Source Amount column, the Source Currency Code column, and the To Currency Code column for a particular fact table.

It is important to understand the difference between the schema definition and the schema rules. The schema definition only records the relationship of columns in delivered tables. A schema rule is customer-specific because it depends on what rate and date to use for a currency conversion. For example, the schema definition only indicates the currency code column for a particular amount column in a stated table.

Schema rules consist of:

• Table name.

• Business unit column.

  An MDW fact table may have multiple business unit surrogate ID (SID) columns, but only one is used to drive the currency conversion. Because not all MDW tables have a business unit SID, this column is optional.

• Business unit grain level.

  Although all of the data in the same MDW fact table has the same granularity level, different MDW facts can be at different business unit granularity levels, whether it is a source business unit, composite business unit, or performance business unit (PF BU.) The business unit grain level is required only if the Business Unit column exists.

• Exchange date/period column for the table.

  Only one date/period column per table is used to drive the exchange rate. Some MDW tables do not have a date/period SID column; therefore, the column is optional. When no date/period SID column is specified, the exchange date resolution always uses the current date/prespecified date in the conversion rule.

• The date/period dimension record for the exchange date/period column.

  This is required only if the exchange date/period column is specified.

• From amount column name.

• To amount column name.

• From currency code column name.

  The utility assumes that the from currency code column is always populated with the source currency code from the source table.

• To currency code column name.

  The to currency code is populated by the To Currency Code parameter when the currency conversion has completed.

Table F_AP_TRAN is an MDW fact table that has data granularity at day level. It also tracks its data with AP Business Unit, one type of business unit originating from the source database. Therefore, in the schema definition page for F_AP_TRAN table, we identify the Business Unit column (AP_BU_SID), as well as the business unit granularity (that is, the source). In addition, we also specify the Date column in the table (ACCT_DAY_SID), as well as the date/period granularity, by specifying the date/period dimension table, in this case, D_DAY.

Table F_AP_TRAN has several data columns that require currency conversion. Among others, they are ADJ_BASE_AMT and DILS_BASE_AMT. Therefore, we specify these columns on the Schema Source Columns page for F_AP_TRAN. You must associate the current currency code for each of the columns upon which currency conversion can be performed.

You must associate each reporting amount and currency code column to the source amount and currency column. You do this on the Target Schema Columns page for F_AP_TRAN.

See Setting Up Parameters for Tree and Recursive Hierarchy Processing.

Conversion Rules

Conversion rules specify how the currency conversion should be carried out, such as which rate and exchange date to use, or whether to perform the conversion for a subset of data (constrained by business unit, date range, or both) or for the entire table. Conversion rules specify the rate type, the target currency code, and the data that determines the effective rate to use for the currency conversion process. The rules also specify which specific amount columns in the table to convert.

Conversion rules are user-defined data and are not delivered as part of the EPM product. Because exchange rate rules are specific to user requirements, and therefore they are treated as user data, PeopleSoft provides only sample data with your EPM product.

Exchange rate rules consist of:

• Rate Type.

• To currency code.

• Conversion date.

  This rule is optional. The conversion date can be a specified date or current date. Otherwise, the exchange rate is determined by the date of the transaction.

  You must choose either:

  • To give the specific exchange date that the currency conversion process will use to identify the exchange rate.

  • To have the currency conversion process use the processing date.

  • To have the currency conversion process use the exchange date column as specified in the schema rule.

The AP_TRANS conversion rule tells the MDW currency conversion process what criteria to use to perform the currency conversion on the amount columns listed in the MDW Conversion Schema Rule.

From Example 1 in Section 3.1, we find that table F_AP_TRAN has a date/period column that is used to determine the exchange date. If you want to have the exchange date follow the transaction date, then you specify conversion date as "date column." This will force the currency conversion utility to take the date from the F_AP_TRAN table, as specified in the Schema Rules. In this example, Recent Date (Recent Dt.) is used for the conversion date. Therefore, you are overriding the exchange date used for currency conversion with the recent date (that is, processing date). You can also override the exchange date with a pre-specified date. This feature is useful when your company has a policy of using a particular date as a standard exchange date for a subset of data.

In this example, chunking rules are not specified. Therefore, you are telling the MDW Currency Conversion utility to perform currency conversion for all of the rows in F_AP_TRAN table.

Chunking Rules

Chunking rules are parameters that the currency conversion process uses to identify the subset of data in MDW tables that are affected by the conversion process. Chunking rules are considered part of the currency conversion rules. They are specific to user requirements and are treated as user data. Therefore, PeopleSoft provides only sample data with your EPM product.

Chunking rules consist of performance (PF) business unit, start date, and end date. These parameters are optional. If you do not provide business unit and chunking date parameters, the process performs conversion on the entire table.

Chunking rules identify the subset of data in MDW tables that experience currency conversion. These rules consist of:

• Business Unit: This rule is optional. If it is provided, only MDW fact data that belongs to this business unit will experience currency conversion. Otherwise, data for all business units experience currency conversion. The business unit in the currency conversion rule is always a PF business unit. If the MDW table has different business unit granularity levels (source or composite), refer to section 4.2 on how this PF business unit is translated into the equivalent source or composite business unit.

• Start Date: This rule is optional. If it is provided, any date greater than or equal to this parameter experiences currency conversion.

• End Date: This rule is optional. If it is provided, any date less than or equal to this parameter value experiences currency conversion.