Total Cost in ppa_wip_values coming out to 0 in some cases
search cancel

Total Cost in ppa_wip_values coming out to 0 in some cases


Article ID: 270859


Updated On:


Clarity PPM SaaS


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
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



Release : 16.1.1


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


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).

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))

 (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.
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:
If the setting was NOT enabled, the formula would be:
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.