InfoCube Aggregation - Deleted data records in the R/3 cause a change of sign for the exchange rate in the transformation between DSO and Cube

[Former Member]

Hi experts,

my error scenario is as follows:

In the R/3 system the sales order item 3202024/10 was created on March 12, 2013

In the DSO for the "Sales Document Item Data" exists the following data record:

- Net value of the order item in document currency (0NET_VALUE): 100.000
- Document Currency (0DOC_CURRCY): CHF
- Exchange rate for statistics: 1,2475

On the same day, the sales order item was completely deleted in the R/3 system.

The consequence of the initial creation and the deletion of the order item is that two data records were generated in the Cube. The first record (positive sign) and the second record (negative sign).

One day later, on March 13, 2013 the sales order item 3202024/10 was created again in the R/3 system.

Consequently, the third data record was created in the cube (positive sign).

So, my problem is that the aggregated Net value in statistics currency (0NET_VAL_S) is wrong.

Cause:

1. record: 100.000 CHF / 1,2475 = 80.160,32 EUR (correct)
2. record: 100.000 CHF * 1,2475 = -124.750,00 EUR (change of sign, so the exchange rate is wrongly multiplied. -80.160,32 EUR would be correct)
3. record: 100.000 CHF / 1,2475 = 80.160,32 EUR (correct)

As result of the cube aggregation, I get the incorrect Net value in statistics currency of 35.570,64 EUR. So the report/query result is incorrect. The correct value would be 80.160,32 EUR.

By the way, my data model is the migrated data flow of the standard Cube "Sales Overview" 0SD_C03 (migration from 3.x to 7.x)

Any ideas?
Thanks in advance

Best regards,
Michael

[Former Member]

Hi experts,

I have deleting the content of the InfoCube and reloaded the Data from DSO into InfoCube (Full Load).

After the deletion of the InfoCube content and the refilled I become one single data record in the InfoCube for  this sales document item and the correct net value of 80.160,32 EUR.

But the daily deletion with the process chain and the daily full load of the huge Cube cannot be the best solution. 

Are there any proposals of my scenario?

Many thanks in advance

Best regards,
Michael

[former_member213043]

HI Michael

I don't see why the calculation changed (multiplication instead of division)....Where is that calculation done : in ECC, at the extraction level (user exit in the extractor), in the transformations going to the DSO, or in the transformation between the DSO and the cube ?

PY

[Former Member]

Hi Pierre-Yves,

thanks for your response to my issue.

The currency translation is implemented in the Transformation between the sales document item DSO and the InfoCube (migrated standard InfoCube 0SD_C03 into 7.x Dataflow). The function module CONVERT_TO_STAT_CURRENCY will be called up in the transformation routine.

The conversion rate /1,2475 is supplied in the document item DSO (see in the screenshot above).

The first data record (with date 12.03.2013) in the Cube is correct and the currency conversion was exectued with the correct conversion rate /1,2475 (divided by).

But the second data record in the Cube is incorrect as a result of the minus posting in the Cube. As a consequence of the minus posting, the conversion rate is changing from /1,2475 to *1,2475 (multiplied by).

So the result of second data record is 100.000 CHF * 1,2475 = 124.750 EUR. Thats incorrect! It would have been correct that the second data record is -80.160,32 EUR.

Thanks and regards,

Michael

[former_member213043]

Hi Michael

Can you send the Routine details ?

If I were you, I would try to get an item cancellation in a qa environment and try to debug the delta load (you can easily put a break point in the function module) to find what piece of code generate that error.

I also found the following note : Note 445021 - BW Cont. SD: Values from invoices incorrectly converted that could be helpful.

It looks like the BC was not correct at the beginning, so, if you activated a long time ago and did not update it, you might have the problem...

Here is the proposed correction :

     EXPORTING

              DATE                = COMM_STRUCTURE-ST_UP_DTE

              DOCUMENT_AMOUNT      = COMM_STRUCTURE-NETVAL_INV

              DOCUMENT_CURRENCY    = COMM_STRUCTURE-DOC_CURRCY

              LOCAL_CURRENCY      = COMM_STRUCTURE-LOC_CURRCY

              STAT_CURRENCY        = COMM_STRUCTURE-STAT_CURR

*>>>> START OF DELETION <<<<<

              LOCAL_RATE          = COMM_STRUCTURE-EXCHG_RATE

*>>>> END OF DELETION <<<<<<<

*>>>> START OF INSERTION <<<<

              LOCAL_RATE          = COMM_STRUCTURE-EXRATE_ACC

*>>>> END OF INSERTION <<<<<<

              STAT_RATE            = COMM_STRUCTURE-EXCHG_STAT

              LOCAL_TYPE_OF_RATE  = US_RATE_TYPE

              STAT_TYPE_OF_RATE    = US_RATE_TYPE

         IMPORTING

              STATISTICAL_AMOUNT   = VALUE

         EXCEPTIONS

              LOCAL_RATE_NOT_FOUND = 1

              STAT_RATE_NOT_FOUND  = 2.

...

Hope it helps!