Child pages
  • Decimal Setting in HCPR on HANA View for Amount Values
Skip to end of metadata
Go to start of metadata

Internal format and External format for amount values

In an SAP system, amount values are always stored with 2 decimal in DB tables. The field is with data type dec(17,2). This is the internal format of the amount value.

Most currencies are with 2 decimal places. For currencies that are with decimal places other than 2, you have to set it in table TCURX. Here it sets the decimal place that a currency actually has. Based on this setting, system will shift the decimal place for the data stored in DB tables and display the value with decimal setting in TCURX. This is the external format of the amount value.

For example:
In TCURX, JPY is set with 0 decimal. This tells the system, although data is stored in table with 2 decimal, it actually means 0 decimal in business.
So in DB table, you will see 1.23 JPY. This is the internal format.
In a BW query, it is displayed with its real meaning: 123 JPY. This is the external format.

See also note 1240163 'Amount too high by factor of 100 for HUF, JPY currencies'

Internal / External format conversion

The conversion is performed in this way:

 N: decimal set in TCURX for the currency

external format amount value = (Internal format amount value) * 10 **( 2-N )

internal format amount value = (external format amount value) * 10 ** (N-2)

HCPR takes amount value in a HANA view as external format

Usually in a customer table, the amount value is stored with the exact value in its currency. That is, in external format. 123 JPY is stored as 123 JPY in the HANA view. If the external format data of  1.23 JPY is stored in the table. Since JPY can only have 0 decimal. In query result you will get 1 JPY. This is correct.

However if the HANA view is based on an SAP ERP table, the data is stored in internal format. That is, 123 JPY is stored as 1.23 JPY in the HANA view. In this case, additional setting is necessary on HANA view to make sure the value is transformed into external format. Otherwise HCPR can't interpret the data correctly.

Typical currencies that may run into such a problem are: JPY, HUF, KRW, COP...

Example

In TCURX table, JPY is set with 0 decimal. EUR doesn't exist in the table, that is, default to 2 decimal.

In the typical ERP demo table SFLIGHT, when option in Menu Settings→User Parameters →Respect Conversion Exit is switched off, the data is as below.  This means, 106136 JPY is stored in internal format: 1061.36 JPY.

A HANA view is build on table SFLIGHT. It is put into a union HCPR as below. Field PRICE is associated with key figure GLAMOUNT, its currency field CURRENCY is associated with 0CURRRENCY.

 

As in HCPR, data in HANA view is always interpreted as external format.  1061.36 JPY is shifted 2 decimal left to get its internal format: 10.61 JPY.

Therefore in transaction LISTCUBE, when read data from HCPR GLHCPRSF with option 'do not use any conversion', internal format data 10.61 JPY is displayed:

And in query result, external format of 1061 JPY is displayed (10.61 shift 2 decimal right):

Now, comparing the query result with the data in table SFLIGHT: 1061.63 JPY is truncated to 1061 JPY. But more important, actually in business the correct value is 106163 JPY, now it is shrinked to 1061 JPY, 100 times smaller.

To make the query result correct, set Semantic Type of field PRICE in the hana view as Amount with Currency Code, switch on Decimal shift as below:

Make sure in the property of the semantics node, default schema is set so that HANA view knows where to find TCURX info:

After that save and activate the HANA view. With this setting, the internal format is converted into external format in HANA view level:

Make sure the user has select privileges on currency conversion tables such as TCURX

And query also gets correct result:

 

 

  • No labels