BPC 10.0 Problem with Currency Conversion

[florian_gradwohl]

Hi experts,

i am experiencing some issues with currency conversion after upgrading to BPC 10.0.

In BPC 7.5 we used the following Script Logic:

*LOOKUP RATE
*DIM R_ACCT=ITEM.RATETYPE
*DIM R_ENTITY="GLOBAL"
*DIM KURS:INPUTCURRENCY=COMPANY.CURRENCY
*DIM MEASURES="PERIODIC"

*ENDLOOKUP
*WHEN ITEM
*IS *
*REC(EXPRESSION=%VALUE%*LOOKUP(KURS),CURRENCY=EUR)

*ENDWHEN

When starting this ScriptLogic via Run Package in BPC 10.0 all values will be appended in the target currency.

For an example to understand this issue better:

1. Data entry in LC (EUR) = 1.000
2. Target Currency is EUR with exchange rate of 1,0 (since LC = EUR).
3. when starting currency conversion the first time, the value is calculated correctly and i find 1.000 with LC and 1.000 with EUR
4. Starting the package again converts 1.000 in LC again and appends it to the value in EUR. -> 1.000 LC = 2.000 EUR.

I tried using target mode when modifying the package. The original package script is:

PROMPT(SELECTINPUT,,,,"%ENTITY_DIM%,%CATEGORY_DIM%,%CURRENCY_DIM%,%TIME_DIM%")
TASK(/CPMB/FX_RESTATMENT_LOGIC,SUSER,%USER%)
TASK(/CPMB/FX_RESTATMENT_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/FX_RESTATMENT_LOGIC,SAPP,%APP%)
TASK(/CPMB/FX_RESTATMENT_LOGIC,SELECTION,%SELECTION%)
TASK(/CPMB/FX_RESTATMENT_LOGIC,LOGICFILENAME,FXTRANS.LGF)

and i added targetmode:

PROMPT(SELECTINPUT,,,,"%ENTITY_DIM%,%CATEGORY_DIM%,%CURRENCY_DIM%,%TIME_DIM%")
PROMPT(RADIOBUTTON,%TARGETMODE%,"Handling of records in target",0,{"Append","Overwrite records with match key","Replace data in same data region of Entity, Category, Time and Audit ID"},{"0","1","2"})
TASK(/CPMB/FX_RESTATMENT_LOGIC,SUSER,%USER%)
TASK(/CPMB/FX_RESTATMENT_LOGIC,SAPPSET,%APPSET%)
TASK(/CPMB/FX_RESTATMENT_LOGIC,SAPP,%APP%)
TASK(/CPMB/FX_RESTATMENT_LOGIC,SELECTION,%SELECTION%)
TASK(/CPMB/FX_RESTATMENT_LOGIC,LOGICFILENAME,FXTRANS.LGF)
TASK(/CPMB/LOAD_IP,TARGETMODE,%TARGETMODE%)

This delivers the following error:

Failed to analyze instruction /CPMB/LOAD_IP,TARGETMODE,%TARGETMODE%

As another solution i tried using the original FXTRANS.lgf but in that case we get the error that no suitable category was found in our application, using the script:

*RUN_PROGRAM CURR_CONVERSION
CATEGORY = %VERSION_SET%
CURRENCY = EUR
TID_RA = %TIME_SET%
RATEENTITY = GLOBAL
*ENDRUN_PROGRAM

I hope someone can help me in this case, since currency conversion is essential and does not seem to work in the moment.

And acutally i am out of ideas.

Thx and regards,

Florian

[Former Member · Accepted solution]

Hi Florian,

Please add a line *XDIM_MEMBERSET CURRENCY=LC to your logic (after the line *ENDLOOKUP)

Hope this helps,

Alwin Berkhout

[florian_gradwohl · Accepted solution]

Hi Alwin,

Thanks alot for your help. This solved our problem and currency conversion now works smoothly.

Thank you and regards,

Florian

[former_member186338]

Hi Florian,

Alwin is correct, the reason is simple:

The first time you run the script you have records only in the LC. The script will generate records in EUR.

The second time you have records both in LC and EUR and the script will add both to EUR (with WHEN/ENDWHEN accumulation).

The proper scoping will solve the issue.

TASK(/CPMB/LOAD_IP,TARGETMODE,%TARGETMODE%) has nothing to do with the /CPMB/FX_RESTATMENT_LOGIC chain.

B.R. Vadim

[florian_gradwohl]

Hi Vadim,

thank you for that explanation. As you said Alwins answer was correct and solved this issue.

Somehow strange that it worked with BPC 7.5, but i am glad this issue could be solved easily.

Thanks and regards,

Florian

[florian_gradwohl]

Hi Vadim,

i just encountered another problem.

I include CURRENCYTRANS.LGF in my default logic using the code:

*INCLUDE CURRENCYTRANS.LGF

This mainly works and when saving values they are converted correctly to EUR, the only problem that occurs is, when for instance changing a value in LC from 1.000 to 0. The value in LC changes to 0, but the value in EUR still stays the same and does not change to 0.

Is there a way to handly 0-values using this script?

Thx and regards,

Florian

[former_member186338]

Hi Florian,

For the default.lgf the approach has to be different:

Instead of *XDIM_MEMBERSET CURRENCY=LC, please use a condition in *IS:

*LOOKUP RATE

*DIM R_ACCT=ITEM.RATETYPE
*DIM R_ENTITY="GLOBAL"
*DIM KURS:INPUTCURRENCY=COMPANY.CURRENCY
*DIM MEASURES="PERIODIC"

*ENDLOOKUP

*WHEN CURRENCY
*IS LC
*REC(EXPRESSION=%VALUE%*LOOKUP(KURS),CURRENCY=EUR)

*ENDWHEN

B.R. Vadim

[florian_gradwohl]

Hi Vadim,

Thx for your fast reply. I added this code directly in DEFAULT.LGF, but even with using a condition in *IS instead of *XDIM_MEMBERSET CURRENCY=LC it does not convert 0 values from LC to EUR.

This is the code i used in DEFAULT.LGF

*LOOKUP RATE
*DIM R_ACCT=ITEM.RATETYPE
*DIM R_ENTITY="GLOBAL"
*DIM KURS:INPUTCURRENCY=COMPANY.CURRENCY
*DIM MEASURES="PERIODIC"
*ENDLOOKUP
*WHEN CURRENCY
*IS LC
*REC(EXPRESSION=%VALUE%*LOOKUP(KURS),CURRENCY=EUR)
*ENDWHEN

Acutally i dont really understand why it does not work.

best regards,

Florian

[former_member186338]

Hi Florian,

Let's check:

You have some input schedule, where you have a cell with some value in LC, for example 1000.

You have another cell with the same ITEM but with EUR currency (read only) - the value also 1000 (rate is 1.0).

If you change the cell with LC to 500 the EUR cell after send and refresh will be also 500.

Then if you change LC cell from 500 to 0, then after send and refresh the EUR cell will remain 500?

Vadim

[Former Member]

Hi Florian,

You might try the following code:

*LOOKUP RATE

*DIM R_ACCT=ITEM.RATETYPE
*DIM R_ENTITY="GLOBAL"
*DIM KURS:INPUTCURRENCY=COMPANY.CURRENCY
*DIM MEASURES="PERIODIC"

*XDIM_MEMBERSET CURRENCY=LC,EUR

*ENDLOOKUP

*WHEN CURRENCY
*IS LC
*REC(EXPRESSION=%VALUE%*LOOKUP(KURS),CURRENCY=EUR)

*IS EUR

*REC(FACTOR=0,CURRENCY=EUR)

*ENDWHEN

another possibility is to use the currency conversion business rules. In my experience this is better for performance and delivers great flexibility.

Alwin Berkhout

[former_member186338]

Hi Alwin,

You proposed some strange code:

Independent of what currency value was changed by user (LC, EUR) the code will calculate EUR value based on LC value and also put zero in EUR...  What is the idea?

Vadim

[Former Member]

Hi Vadim,

The idea with this piece of code is first to fully clear the current EUR values and append this with LC*RATE values. This should solve the probem that Florian encounters.

(like the clear_destination in the MS version).

Alwin

[former_member186338]

Hi Alwin,

Your idea have to be implemented in two different WHEN/ENDWHEN loops like:

*LOOKUP RATE

*DIM R_ACCT=ITEM.RATETYPE
*DIM R_ENTITY="GLOBAL"
*DIM KURS:INPUTCURRENCY=COMPANY.CURRENCY
*DIM MEASURES="PERIODIC"

*ENDLOOKUP

*XDIM_MEMBERSET CURRENCY=EUR

*WHEN CURRENCY

*IS EUR

*REC(FACTOR=0,CURRENCY=EUR)

*ENDWHEN

*XDIM_MEMBERSET CURRENCY=LC

*WHEN CURRENCY
*IS LC
*REC(EXPRESSION=%VALUE%*LOOKUP(KURS),CURRENCY=EUR)

*ENDWHEN

In one loop you will have WHEN/ENDWHEN accumulation!

By the way, it's not a good practice to use *XDIM_MEMBERSET in default.lgf, the default.lgf have to work with the scope of changed members...

But I don't think that your solution is really necessary, my code have to work correctly, the issue is not here.

B.R. Vadim

[florian_gradwohl]

Hi Vadim,

i just reproduced this issue in a new input schedule and attached screenshots to show you what is happening when changing a value in LC to 0.

You see a schedule with 2 rows, first row in LC, second row in EUR (read only).

1. Screenshot:

I entered values in the LC column and saved data using a save button and the EPMExecuteAPI SaveAndRefreshWorksheetData. Therefor after saving the schedule refreshs and i see that the values are converted correctly to EUR:

2. Screenshot:

I changed the values in LC and saved data. Values are converted correctly to EUR:

3. Screenshot:

I deleted values in LC and saved data. Values in LC are zero but are not converted to EUR.

So even in the new schedule this issue occurs.

Florian

[Former Member]

Hi Vadim,

It now looks that I have a problem.... well, I don't haha, it is Florian that has an issue.

I am sorry, but I do not fully agree with you:

• you are right, the earlier code should work correctly, but Florian reported otherwise
• I still think that one end/endwhen routine should be enough, accumulation is ok here (have used these kind of code in the past frequently when the clear_destionation was not working in certain sp's for the ms version)
• I frequently do use xdim_membersets in the default logic, otherwise results might be incorrect. Main example here is the net result on the balancesheet, if you don't use xdim_memberset for account, only the changed P&L accounts will get posted to the net result line, which is ofcourse incorrect.
  • but I agree, it must be used wisely

But again, this is not my post, I don't have a problem

Alwin Berkhout

[former_member186338]

Hi Alwin,

The BPC NW script logic processing is very different from MS I am not an expert in MS, but I know how things are working in NW (I spend some time debugging in ABAP the script execution).

The code with one WHEN/ENDWHEN you proposed will do only one additional thing: if there is some value in the EUR and for some strange reason there is no record in LC at all, then your code will zero EUR.

About XDIM_MEMBERSET and default.lgf - it's always possible to write code without hard scoping, so that only values depending on the changed values will be recalculated. In theory it will increase performance...

And yes, it's not your issue, but just some knowledge sharing

Vadim

[Former Member]

Hi Vadim,

Thanks for the knowledge sharing, I like that.

Alwin

[former_member186338]

Hi Florian,

The issue is that you use API and not a standard Save button. Somehow API do not recognize the deletion of LC data.

Test with standard fresh template without API.

B.R. Vadim

[florian_gradwohl]

Hi Vadim,

this would be a big issue if the API would not be able to recognize deletion of LC data, since we use the API in every input template and dont want the user to use the EPM Save Button in the Ribbon.

But still i tested in a fresh template if saving 0 values in LC works with the stadard save button, but even then the same issue occurs as it happens when using the API.

BR Florian.

[Former Member]

Hi Vadim

Can u please explain me

* DIM KURS:INPUTCURRENCY=COMPANY.CURRENCY

Thanks in advance

George

[florian_gradwohl]

Hi Alwin,

interesting following your discussion. Since i am not an expert i am not sure if using xdim_membersets in the default logic would be wise for us.

I definitly should consider using currency conversion business rules, but actually never used it, so i hope it won´t be complicated.

Thx and regards,

Florian

[former_member186338]

Hi George,

*DIM KURS:INPUTCURRENCY=COMPANY.CURRENCY is a part of LOOKUP declaration, properly explained in the help about LOOKUP (the last example in help).

Vadim

[former_member186338]

Hi Florian,

When you clear the values in your example and press save - it looks like you don't get a message about 3 records to be saved in database. On the attached picture I don't see zeros in the cleared cells, that have to be shown after send and refresh...

Vadim

P.S. Try instead of clearing values to type 0 in the cells!

[florian_gradwohl]

Hi Vadim,

Even when i clear the values and do not type 0 in the cells, i get the message that 3 records will be saved to the system.

Additionally i checked listcube everytime i used the save button and i see the same issue directly in data warehouse.

The zeros are not shown because of the formatting of the cell. Standard format shows the following:

This really seems to be a strange issue.

Florian