Configuring Currency Precision
These topics provide an overview of currency precision and discuss how to:
Activate currency precision.
Report with currency precision.
Expand a database for currency precision.
According to the ISO standard, currency precision can range from no decimals to 3 decimals. For example, U.S. dollar amounts have 2 digits to the right of the decimal; Japanese yen have none. To support this dynamic currency precision, PeopleSoft delivers all of its currency-sensitive amount fields with a standard length of 13.2; that is, 13 digits to the left of the decimal and 2 digits to the right. Optionally, with some modification, you can expand these amount fields in your system to a maximum of 15.3 digits. There is a controlled currency on the same record to control the display and processing of such amount fields.
PeopleSoft rounds all currency-sensitive amount fields to the currency precision of the controlled currency during all online or background processes. For example, in a database containing amount fields with a length of 15.3, Japanese yen are rounded to 123.000 and U.S. dollars are rounded to 123.230. The system doesn't place a nonzero amount after the decimal for a Japanese amount or after the second digit to the right of the decimal for a U.S. amount.
PeopleSoft displays the amount fields on the online pages with the proper precision. For example, it displays Japanese yen as 123 and U.S. dollars as 123.23. When entering an amount, you can't enter more than the defined precision; if you do, the system treats the entry as an online error.
PeopleCode programs and background processes round all currency-sensitive amount fields to the currency precision of the controlled currency.
Currency precision is a PeopleTools option; selecting this option activates all of its features. With the option turned off, the system displays amount fields as defined in PeopleSoft Application Designer and rounds them to the number of decimals defined there.
Activate the PeopleTools multicurrency option on the PeopleTools Options page (PSOPTIONS) by selecting the Multi-Currency check box. PeopleTools will then use the precision you specify in the Currency Code page (CURRENCY_CD_TABLE ) for the currencies relating to monetary fields for which you have specified currency control fields. Some PeopleSoft applications are shipped with the Multi-Currency check box selected. You can change that default.
Note: Once you deselect the Multi-Currency check box, selecting it again does not automatically round the existing transaction amounts. If you deselect the Multi-Currency check box, PeopleSoft supports only the default amount field size of 13.2. It does not support the larger amount field size of 15.3.
Most PeopleSoft SQR reports display the currency-controlled amounts to the number of decimals that are defined by the associated currency. For example, a Japanese yen amount appears on a report as 123 and a U.S. dollar amount appears as 123.23.
For reporting with BI Publisher and PS/nVision (Excel), the amount appears as a 2-decimal number. You have to modify the reports if you want to show 3 decimals on these reports.
Our third-party reporting tools don't fully support numeric fields that are greater than 15 digits. PS/nVision (Excel) uses an 8-byte float for numeric fields. This causes truncation after the fifteenth digit. BI Publisher displays up to 16 digits correctly; for numbers that are greater than 16, BI Publisher starts to insert nonsensical numbers into the decimal positions; however, this is a problem only with very large numbers. For any of these reporting tools, you should have accurate results up to the following amounts:
Hundreds of trillions of yen (or lira): Precision = 0
Trillions of dollars: Precision = 2
Hundreds of billions of dinar: Precision = 3
For example, if you enter 2s into a 15.3 numeric database amount field, the following third-party reporting tools will display the value as:
Number of Digits |
Reports |
PS/nVision (Excel) |
SQR |
---|---|---|---|
16 |
2,222,222,222,222.222 |
2,222,222,222,222.220 |
2,222,222,222,222.222 |
17 |
22,222,222,222,222.219 |
22,222,222,222,222.200 |
22,222,222,222,222.220 |
18 |
222,222,222,222,222.188 |
222,222,222,222,222.000 |
222,222,222,222,222.200 |
With some PeopleSoft applications, you can expand your database from the original amount field size of 13.2 digits to 15.3 digits. For example, the PeopleSoft application supports the larger field size of 15.3 across the PeopleSoft Financials, Distribution, and Manufacturing applications.
To expand your database:
Back up your database (optional).
Although this step is optional, we highly recommend it. This way, you can recover your database if you experience any database integrity problems during the remaining steps.
Run Audit scripts.
Run DDDAudit and SYSAudit from PeopleSoft Process Scheduler to ensure that the database is clean. Select PeopleTools, Process Scheduler, System Process Requests and enter a Run Control ID. Click Run. Specify the Server name of your environment, and select DDDAUDIT (and then SYSAUDIT) from the list of processes. Click OK to run the processes.
Review the script output to ensure a clean database.
Locate the DDDAudit and SYSAudit reports in your environment, and review them to ensure that the database is clean.
Run FSINTLFD.DMS to populate the international field size table.
Once the database is clean, run the script FSINTLFD.DMS to populate the international field size table INTL_FLDSIZ_TBL. Use DataMover to run this script. The system populates the international field size table with all the amount fields that have the controlled currency that is specified in Application Designer. The system calculates the new length by adding 2 digits to the left of the decimal and 1 digit to the right of the decimal. Before the system inserts new fields into the international field size table, it deletes all existing fields from the table.
Verify the results by reviewing the International Field Size page.
Select the International Field Size page. The Field Size - International column displays 15.3 for most of its fields. When compared with the Current Field Size, the values in the Field Size - International column should be 2 digits greater to the left of the decimal and 1 digit greater to the right of the decimal. In other words, a Current Field Size value of 13.2 should correspond to a 15.3 value in the Field Size - International column.
Run the PeopleTools process TLSINST1.SQR to change the field size on PSDBFIELD.
Note: This SQR is also an application engine. You can access it on the page.
Using SQRW, run PeopleTools SQR TLSINST1.SQR to change the field size on the PeopleTools table PSDBFIELD. This changes the PeopleTools definition values, but not the database field size.
It also creates a report (TLSINST1.LIS) that lists all the pages that use Average or Maximum size page fields. As a standard of PeopleSoft Enterprise, all monetary amount fields use a Custom field size to avoid the overlapping of fields when the amount fields are expanded. Review the list of pages and make any adjustments that are necessary to preserve readability.
Review SQR.LOG for errors.
The SQR log is created in C:\TEMP, unless otherwise specified. View the log to ensure that the field size value has been changed to 15.3. Verify the result by randomly checking the field size, opening the various records containing amount fields shown in step 5. As you build the project, executing scripts to SQL alter tables, creating views, and indexing, closely monitor the execution of the scripts and review the error log.
Use SQL Alter all tables.
Select
Select
Save the Project Name as CURR_TBL.
Select Insert, Objects into Project.
Select Object Type: Record and Type: Table.
Click Insert under Record Name. The system displays a list of all the tables being populated.
Click Select All, and click Insert again; click Close to close the page.
Select Build, Project.
In the Build Options group box, select Create Indexes and Alter Tables.
Under Build Execute Options, be sure that the Build script file is selected.
Review the script file with your database administrator (DBA), and execute the file using your recommended SQL query tool.
Use SQL Create all views.
Select
Select
the Project Name as CURR_VW.Select Insert, Objects into Project. Select Object Type: Record and Type: View/Query View.
Click Insert and then click Select All.
Click Insert again; then click Close to close the page.
Select Build, Project.
In the Build Options group box, select Create Views.
Under Build Execute Options, select the Build script file.
Review the script file with your DBA, and execute it using your recommended SQL query tool.
Run Audit scripts again.
Run DDDAudit and SYSAudit again to make sure that the database is clean.
Manually change reports.
PeopleSoft has enhanced most SQR reports to accommodate 15.3 amount fields. However, some of them are too crowded, and their amount field sizes remain 13.2. If the last digits of the decimals are important to you, update the SQR to 12.3 to display 3 decimals on the report. Following is a list of reports that require manual changes by application to accommodate the third digit of the decimal:
Field or Control
Definition
General Ledger GLS1001, GLS1003, GLS7000, GLS7009, GLS7010, and GLS7012
Receivables AR30001 (9.2), AR30002 (9.2), AR30003 (9.2), and AR30004 (12.2)
See PeopleTools: Global Technology.