Total Cost in ppa_wip_values coming out to 0 in some cases

Products

• Clarity PPM SaaS

Issue/Introduction

Total Cost in ppa_wip_values coming out to 0

Steps to Reproduce
1- Administration > Finance > Setup > "APPLY CURRENCY PRECISION FOR RATE/COST" is set to "Yes"
2- Create a Foreign Exchange Rate, for example:
From Currency:              KZT
TO Currency:                USD
Conversion Rate:             0.002236
Rate Calculation Method:     Multiply
Exhange Rate Type:            Average
Effective Date:                06/01/2023
3- Enable Currencies KZT and USD (if not already enabled)
4- Currency Precision in CMN_CURRENCIES
KZT and USD is 2
5- Do a Manual Transaction Entry
Voucher
Add a Transaction
Quantity:     11218573.000000
Cost:        1000    
Rate:        1

6- Post to WIP

7- Look at values in PPA_WIP_VALUES table, "totalcost" comes out to "0"

 

 

 

Expected Results: totalcost would be the value greater than 0, or the value returned from cmn_get_onlyconverted_val_fct function

Actual Results: In some cases, the value is 0

Workaround: 
none

Environment

Release : 16.1.1

Cause

The setting "Apply Currency Precision" was enabled, which rounded the number to "0" because the result was: 0.0022.

Resolution

Process flow for Currency Exchange

1- First, it looks at the From Currency Cost and if it's 0, stop processing and return 0.

2- Based on the transaction date, from currency, to currency, query for an exchange rate in clarity effective for that time frame (defined in Clarity Currency Exchange Rates)

This query gets the corresponding record from the exchange rate table where the rate is available for any given currency and to currency whose effective date falls within the given range after checking for the appropriate exchange rate type (SPOT,AVERAGE,FIXED).

Documentation on "Apply Currency Precision for Rate/Cost"

3- If the option "APPLY CURRENCY PRECISION FOR RATE/COST" is checked, then get the currency precision of the DESTINATION currency.

 ROUND((Cost in From Currency * Exchange Rate) currency_precision))

4- If the "APPLY CURRENCY PRECISION FOR RATE/COST" is NOT checked,
 

 (Cost in From Currency * Exchange rate)  

 

Examples (please find the attached sheet).

Also, some pasted below

From Currency	Transaction Currency Amount:	Transaction Currency Precision:	Project Planning Currency:	Project Planning Currency Precision:	Project Planning Currency Precision set to 4	Exchange Rate/Type:	Result with 16.1.2 with Apply currency precision for rate/cost:	Result with 16.1.2 WITHOUT Apply currency precision for rate/cost:	Result with 16.1.2 WITH "Apply currency precision for rate/cost" AND precision = 4
ISK	12345.12	0	USD	2	4	0.00732	90.37	90.3662784	90.37
RSD	12345.12	2	USD	2	4	0.009285	114.62	114.6244392	114.62
TRY	12345.12	2	USD	2	4	0.038459	474.78	474.7809701	474.78
UGX	12345.12	0	USD	2	4	0.000272	3.36	3.35787264	3.36
VND	12345.12	0	USD	2	4	0.000042	0.52	0.51849504	0.52
KZT	12345.12	2	USD	2	4	0.002202	27.18	27.18395424	27.18
KZT	1	2	USD	2	4	0.002202	0	0.002202	0.00

 _____________

Considering this use case:

When converting from a currency to another currency, the from currency amount and the to currency amount can be rounded as per the currency precision, however, the exchange rate must be taken in full without any rounding.

 

Response:  

The Exchange Rate is applied in full, regardless of the setting.  However, the result will be rounded or NOT rounded with the setting "Apply Currency Precision for Rate / Cost" being enabled or disabled.

For example, with the setting enabled, if this were Excel, the formula would be:

=round((B2*G2),F2)

If the setting was NOT enabled, the formula would be:

=(B2*G2)

We recommended setting the precision to "4" or more because of the situation of the KZT currency.

For example, check out the last row above.

The result of the Transaction Currency Amount value of "1" multiplied by the Exchange Rate of 0.002202 has a product value of 0.002202. However, since the KZT precision is set to "2", this becomes "0".

 

This explanation matches the times a zero totalcost was the result.